; Note: comment lines in .INI files always start with a semicolon [Template] Description=Use this template to create database replication job that can replicate data accross different database systems using two way replication [Variables] ; Key values that have their name enclosed in % signs will be used for ; template wizard questionnaire and substitution variables ; such key values should consist of 2 comma separated parts: ; 1. Field Edit Style (EDIT, YES/NO, FILE BROWSE, ; DIR BROWSE, PROCESS BROWSE, ; FTP BROWSE, MAIL PROFILE LIST, ; REMOTE FILE BROWSE, REMOTE DIR BROWSE, ; REMOTE AGENT LIST, DB PROFILE LIST) ; 2. Prompt ; ; Example: %VAR%=EDIT,What is the name of the service that you want to monitor? ; ; Key values that don't have their name enclosed in % signs will be used for ; job properties (See online help on "Job property names for use with JDL command" ; topic for more details). ; ; Example: DAY_NUMBER=1 %DB_PROFILE_1%=DB PROFILE LIST,Which database is the primary database. In case of data change conflict, data from this database will be taken as a master? %DB_PROFILE_2%=DB PROFILE LIST,Which database is the secondary database? %TABLE_1%=EDIT,Primary database table? %TABLE_2%=EDIT,Secondary database table? &KEY_1%=EDIT,Which key columns in the primary table can be used to compare data against the secondary table (separate multiple columns by comma, for example: acct_no,trans_dt): &KEY_2%=EDIT,Which key columns in the secondary table can be used to compare data against the primary table (separate multiple columns by comma, for example: account,transaction_date): %RECIPIENT%=EDIT,To whom do you want to sent the email alert when the replication fail? %EMAIL_PROFILE%=MAIL PROFILE LIST,If you use MAPI email interface, then which email profile do you want to use? If you use Lotus Notes or SMTP email interfaces, enter User ID required for logging to your email system. %EMAIL_PASSWORD%=EDIT,If you are required to login to your email system, what is your password? JOB_TYPE=S SCHEDULE_TYPE=D ASYNC=Y SKIP=N RETRY_ON_ERROR=Y RETRY_INTERVAL=600 NUMBER_OF_RETRIES=2 MSG_ERROR=Y MSG_EMAIL=Y MSG_ACCOUNT=%EMAIL_PROFILE% MSG_PASSWORD=%EMAIL_PASSWORD% MSG_RECIPIENT=%RECIPIENT% DESCRIPTION=This job performs two way replication between 2 database tables residing in different database systems. It will send an email alert to the specified recipient if the replication fails. ; Notes: The script bellow can include substitution variables. ; Substitution variables must be specified in %VAR% format ; where VAR is the variable name. ; ; Everything after the next line will be used for the template script. ;======================================================================================== [Body] Dim rows, number Dim message, string Dim SQL, string Dim done, boolean Dim column_list, string Dim column_name, string ////////////////////////////////////////////////////////////// // // The replication process consists of the following steps: // -- Load data from the primary table to the staging table // in the secondary database // -- Update the secondary table using data from the staging // table using key match method: Update matching rows, insert // new rows // -- Load data from the secondary table to the staging table // in the primary database // -- Load new data from the staging table to the primary table // /////////////////////////////////////////////////////////////// // 1.0 Connect to the secondary database DatabaseConnect "%DB_PROFILE_2%" // 1.1 Create staging table whose design will match desing of // the secondary table DatabaseSetSQLSelect "SELECT * FROM %TABLE_2%" DatabaseSave "C:\TEMP\%TABLE_2%_STAGE", "SQL", rows FileReadAll "C:\TEMP\%TABLE_2%_STAGE", SQL FileDelete "C:\TEMP\%TABLE_2%_STAGE" DatabaseExecute SQL, rows Concat message, "1.1 Created staging table %TABLE_2%_STAGE\r\n", message // 1.2 Load data from the primary table DatabasePipe "%DB_PROFILE_1%", "%DB_PROFILE_2%", & "SELECT * FROM %TABLE_1%", "%TABLE_2%_STAGE", & "INSERT", rows_copied ConcatEx message, "1.2 Loaded ", rows_copied, & " rows into staging table from the primary table %TABLE_1%\r\n", message // 1.3 Build index on the staging table (this is done for performance resons) DatabaseExecute "CREATE UNIQUE INDEX %TABLE_2%_ISTAGE ON %TABLE_2%_STAGE (%KEY_2%)", rows Concat message, "1.3 Built unique index on staging table\r\n", message // 1.4 Update secondary date using data from the primary table // Only matching rows are updated DatabasePipe "%DB_PROFILE_2%", "%DB_PROFILE_2%", & "SELECT * FROM %TABLE_2%_STAGE", "%TABLE_2%", & "UPDATE", rows_copied // 1.5 Delete duplicate rows from the stating area // 1.5.1 Build WHERE clause Dim SQL_WHERE, string Set column_list, "&KEY_2%" LoopUntil done, END_WHERE_LOOP GetToken ",", column_list, column_name Trim column_name, column_name ConcatEx SQL_WHERE, " AND %TABLE_2%_STAGE.", column_name, & " AND %TABLE_2%.", column_name, SQL_WHERE Trim column_list, column_list isEqual column_list, "", done END_WHERE_LOOP: Mid SQL_WHERE, 5, 99999, SQL_WHERE // 1.5.2 Build DELETE SQL Concat "DELETE FROM %TABLE_2%_STAGE WHERE exists (SELECT 1 FROM %TABLE_2% WHERE ", & SQL_WHERE, ")", SQL // 1.5.3 Delete duplicates DatabaseExecute SQL, rows ConcatEx message, "1.4 - 1.5 Updated rows in the secondary table %TABLE_2% ", & "using key matching method. ", rows, " rows updated.\r\n", message // 1.6 Insert new rows DatabasePipe "%DB_PROFILE_2%", "%DB_PROFILE_2%", & "SELECT * FROM %TABLE_2%_STAGE", "%TABLE_2%", & "INSERT", rows_copied ConcatEx message, "1.6 Inserted ", rows_copied, & " new rows into the secondary table %TABLE_2%.\r\n", message // 1.7 Clean up DatabaseExecute "DROP TABLE %TABLE_2%_STAGE", rows DatabaseDisconnect // 2.0 Connect to the primary database DatabaseConnect "%DB_PROFILE_1%" // 2.1 Create staging table whose design will match desing of // the primary table DatabaseSetSQLSelect "SELECT * FROM %TABLE_1%" DatabaseSave "C:\TEMP\%TABLE_1%_STAGE", "SQL", rows FileReadAll "C:\TEMP\%TABLE_1%_STAGE", SQL FileDelete "C:\TEMP\%TABLE_1%_STAGE" DatabaseExecute SQL, rows Concat message, "2.1 Created staging table %TABLE_1%_STAGE\r\n", message // 2.2 Load data from the secondary table DatabasePipe "%DB_PROFILE_2%", "%DB_PROFILE_1%", & "SELECT * FROM %TABLE_2%", "%TABLE_1%_STAGE", & "INSERT", rows_copied ConcatEx message, "2.2 Loaded ", rows_copied, & " rows into staging table from the secondary table %TABLE_2%\r\n", message // 2.3 Build index on the staging table (this is done for performance resons) DatabaseExecute "CREATE UNIQUE INDEX %TABLE_1%_ISTAGE ON %TABLE_1%_STAGE (%KEY_1%)", rows Concat message, "2.3 Built unique index on staging table\r\n", message // 2.4 Delete duplicate rows from the stating area // 2.4.1 Build WHERE clause Set SQL_WHERE, "" Set column_list, "&KEY_1%" LoopUntil done, END_WHERE_LOOP GetToken ",", column_list, column_name Trim column_name, column_name ConcatEx SQL_WHERE, " AND %TABLE_1%_STAGE.", column_name, & " AND %TABLE_1%.", column_name, SQL_WHERE Trim column_list, column_list isEqual column_list, "", done END_WHERE_LOOP: Mid SQL_WHERE, 5, 99999, SQL_WHERE // 2.4.2 Build DELETE SQL Concat "DELETE FROM %TABLE_1%_STAGE WHERE exists (SELECT 1 FROM %TABLE_1% WHERE ", & SQL_WHERE, ")", SQL // 2.4.3 Delete duplicates DatabaseExecute SQL, rows // 2.5 Insert new rows DatabasePipe "%DB_PROFILE_1%", "%DB_PROFILE_1%", & "SELECT * FROM %TABLE_1%_STAGE", "%TABLE_1%", & "INSERT", rows_copied ConcatEx message, "2.4 - 2.5 Inserted ", rows_copied, & " new rows into the primary table %TABLE_1%.\r\n", message // 2.6 Clean up DatabaseExecute "DROP TABLE %TABLE_1%_STAGE", rows DatabaseDisconnect // send notification ConcatEx "Table %TABLE_1% (%DB_PROFILE_1%) to table %TABLE_2% (%DB_PROFILE_2%) ", & "two-way replication process completed succesfully.\r\n\r\nHere is the replication log:\r\n", & message, message MailSend "%EMAIL_PROFILE%", "%EMAIL_PASSWORD%", "%RECIPIENT%", "Database Replication Status", message