HP 3000 Manuals HP 3000 Manuals
ISQL UNLOAD/LOAD Use the ISQL UNLOAD command to unload master DBEnvironment tables, one at a time, and store them in individual files for transfer to a slave DBEnvironment. Files can be unloaded in an external ASCII format, or in an internal ALLBASE/SQL format. The internal format, which does not convert files into ASCII format, is preferable because it is more efficient. In order to use UNLOAD, you must ensure that no users are writing to the DBEnvironment tables you want to unload. To do this, either issue the statement, LOCK TABLE IN SHARE MODE, for the tables you are unloading or stop the DBEnvironment and restart it in single-user mode until the unload is complete. The LOCK TABLE statement has the advantage that read-only users can still continue to access the DBEnvironment.
WARNING * In each partition you must lock all the tables you wish to unload at once, before unloading any tables in the partition. If you do not, you will lose transactions due to incorrect SCR information. * If multiple tables are associated with a partition to be hard resynchronized, you must execute a LOCK TABLE statement for every table associated with the partition. * All tables associated with that partition must be hard resynchronized. * If these conditions are not met, transactions may be lost for every table that is not hard resynchronized (after soft resynchronization is resumed).
The UNLOAD command does not obtain the SCR information you need to transfer to the slave DBEnvironment. The easiest way to get this information is to use the SQLAudit tool to store an image of the SCR information. To use SQLAudit, the DBEnvironment must be in MULTI USER MODE. Using LOCK TABLE, rather than placing the DBEnvironment in single-user mode, facilitates obtaining the SCR information. Lock all your tables ahead of time, unload all of them before committing work, and obtain the SCR information only once, just before you commit work. Unloading Tables Using ISQL UNLOAD For example, if you need to hard resynchronize the tables PurchDB.SupplyPrice and PurchDB.Vendors (the only two tables in partition 10), the steps for unloading the master could be as follows: [REV BEG] 1. Warn users that the tables are going to be locked so they can temporarily discontinue accessing the tables you are planning to unload.[REV END] 2. Open a window on the master and go to the group where you normally invoke ISQL to connect to the master DBEnvironment. This is window A. 3. Open another window on the master and go to the group that contains the DBECon file for the master DBEnvironment. This is window B. 4. From window B, terminate the master ALLBASE/Replicate soft resynchronization application for the partition containing the parts and inventory tables. Use the BREAK or ABORTJOB commands to do this. This will automatically stop the related slave application. _________________________________________________________________ NOTE If you used one ALLBASE/Replicate application to soft resynchronize all partitions, you may want to continue soft resynchronization with the partitions not being hard resynchronized. (This will help prevent the slave falling too far behind the master.) In this case, you may restart ALLBASE/Replicate on both the slave and the master, omitting the specification for the partitions being hard resynchronized. However, for this example, assume soft resynchronization will be halted until the hard resynchronization is complete. _________________________________________________________________ 5. From window A, invoke ISQL and connect to the master: :ISQL isql=> CONNECT TO 'PARTSDBE'; 6. From window A, lock the tables to be UNLOADED: isql=> LOCK TABLE PURCHDB.SUPPLYPRICE IN SHARE MODE; isql=> LOCK TABLE PURCHDB.VENDORS IN SHARE MODE; 7. From window A, unload the tables: isql=> UNLOAD TO INTERNAL SPOUT FROM PURCHDB.SUPPLYPRICE; isql=> UNLOAD TO INTERNAL VOUT FROM PURCHDB.VENDORS; _________________________________________________________________ WARNING DO NOT COMMIT WORK AT THIS TIME! You must hold the locks on the unloaded tables until you have obtained the SCR information. _________________________________________________________________ 8. From window B, use SQLAudit to obtain the SCR information from the master: : SQLAUDIT SQLAudit >> SET DBEN PARTSDBE SQLAudit >> GET AUDITPOINT Audit Point File >> PCHSCR1 Lock Log for Current Audit Point (n/y) >> N Display Current Audit Point Information (n/y) >> N SQLAudit >> SET DBEN OFF SQLAudit >> EXIT This series of commands gets the SCR information and places it in a file named PCHSCR1. You must remember to obtain the SCR information just before committing work after unloading all the tables in the partition. 9. From window A, commit work. isql=> COMMIT WORK; COMMIT WORK will release the locks placed on the tables with the LOCK TABLE statement. Users will again be able to write to the tables. Committed transactions against the table will again create audit log records. 10. If necessary, transfer the files containing the unloaded data and the SCR information to the slave system. Store to tape or transfer them by other appropriate means. After the target tables are loaded from this unload, you can resume soft resynchronization between master and slave. The slave will catch up with any transactions that were committed against the master since the tables were unloaded. If you expect the reload of the slave to take a long time, you can keep the master tables locked until the slave is loaded. This will prevent the need for another hard resynchronization. [REV BEG] 11. Notify users that the tables are again available for use.[REV END] Loading Tables Using ISQL LOAD Sample steps for loading the data from the master onto the slave using the ISQL LOAD command could be as follows: 1. Open a window on the slave and go to the group where you normally invoke ISQL to connect to the slave DBEnvironment. This is window X. 2. Open another window and go to the group that contains the DBECon file for the slave DBEnvironment. This is window Y. 3. From window Y, verify that the ALLBASE/Replicate application is not executing against the source partitions that supply transactions for the target tables being loaded. When the ALLBASE/Replicate application was terminated on the master, the related application should have automatically terminated on the slave. Place the files containing the unloaded source tables in the group where you invoke ISQL (if you have not already done so). Place the files containing the SCR information in the group containing the DBECon file. 4. From Window X, invoke ISQL and connect to the DBEnvironment. Disable audit logging so that audit log records will not be generated for the hard resynchronization activities. Lock the tables to be loaded in exclusive mode. : ISQL isql=> CONNECT TO 'PARTSDBE'; isql=> DISABLE AUDIT LOGGING; isql=> LOCK TABLE PURCHDB.SUPPLYPRICE IN EXCLUSIVE MODE; isql=> LOCK TABLE PURCHDB.VENDORS IN EXCLUSIVE MODE; 5. From window X, remove current data from tables using the SQL TRUNCATE TABLE statement. This has the same effect as deleting all the rows in the tables, but it reduces logging overhead and is more efficient. isql=> TRUNCATE TABLE PURCHDB.SUPPLYPRICE; isql=> TRUNCATE TABLE PURCHDB.VENDORS; From window X, load the tables from the files transferred from the master DBEnvironment, and reenable audit logging. isql=> LOAD FROM INTERNAL SPOUT TO PURCHDB.SUPPLYPRICE; isql=> LOAD FROM INTERNAL VOUT TO PURCHDB.VENDORS; isql=> ENABLE AUDIT LOGGING; Additional ISQL commands may be used at load time to increase the loading efficiency for large loads. See the description of the ISQL LOAD command in the ISQL Reference Manual for ALLBASE/SQL and IMAGE/SQL for more details. _________________________________________________________________ WARNING DO NOT COMMIT WORK AT THIS TIME! You must modify the SCR information for the partition being hard resynchronized before you commit work. _________________________________________________________________ 6. From window Y, use SQLAudit to restore the appropriate SCR information on the slave. For this example, assume that the tables are being loaded from tables that were in partition 10 on the master DBEnvironment. :SQLAUDIT SQLAudit >> SET DBEN PARTSDBE SQLAudit >> MODIFY AUDITPOINT Audit Point File >> PCHSCR1 System Partitions are DEF[INITION], AUTH[ORIZATION], STOR[AGE], or SECT[ION]. Please enter Partition Numbers or System Partitions. Type @ for all, ? for a list of Partitions, or RETURN to quit. Partition Number >> 10 Partition Number >> Return Modify Current Audit Point Information (n/y) >> Y Current audit point information has been modified. SQLAudit >> SET DBEN OFF SQLAudit >> EXIT This series of commands takes the SCR information stored from the master and places it in the SCR array on the slave for the partitions specified. 7. From window X, commit work. isql=> COMMIT WORK; Starting the Application on the Slave 1. From window Y, start the ALLBASE/Replicate application on the slave. (If you have chosen to let the slave application run until this point, terminate it now, before starting a new instance of the application.) For this example, assume you will replicate partitions 1 (DEFAULT), 2 (COMMENT), 10, and the partitions that replicate DDL activities from the master to the slave. Start the slave application with the following commands: :RUN REPSLAVE.PUB.SYS Enter Slave DBE Name >> PARTSDBE Enter Partition Number (RETURN to finish): 1 Enter Partition Number (RETURN to finish): 2 Enter Partition Number (RETURN to finish): 10 Enter Partition Number (RETURN to finish): -2 Enter Partition Number (RETURN to finish): -3 Enter Partition Number (RETURN to finish): -4 Enter Partition Number (RETURN to finish): -5 Enter Partition Number (RETURN to finish): Enter the Socket Name you wish to use (max 16 chars) >> MA1TOSL1 _________________________________________________________________ NOTE You could also create a file (call it STSLDDL) that contains data similar to the following: PARTSDBE 1 2 10 -2 -3 -4 -5 MA1TOSL1 You could then start the slave application using the command: : REPSLAVE.PUB.SYS;STDIN = STSLDDL _________________________________________________________________ Starting the Application on the Master 1. From window B, start the ALLBASE/Replicate application on the Master. Do not supply the master application with any information regarding the partitions to be replicated. It is the slave's responsibility to notify the master what partitions will be replicated to the slave. Start the application with the following commands: :RUN REPMAST.PUB.SYS Enter Master DBE Name >> PARTSDBE Enter the Socket Name you wish to use >> MA1TOSL1 Enter the Node Name of the Slave DBE >> CALVIN _________________________________________________________________ NOTE You could create a file called "STMA." containing data similar to the following: PARTSDBE MA1TOSL1 CALVIN You could then start the master replicate application with the following command: :RUN REPMAST.PUB.SYS;STDIN = STMA _________________________________________________________________ Testing the Applications 1. From window A on the master, and window X on the slave, test the ALLBASE/Replicate environment to verify that transactions committed on the master are replicated on the slave. Be sure to commit work. 2. From window A on the master, and window X on the slave, remove the test transactions from the master and verify that they have been removed from the slave, if you do not want them to become permanent. Be sure to commit work.