HP 3000 Manuals HP 3000 Manuals
SQLUtil Static STORE and RESTORE
The SQLUtil STORE command stores the complete image of the master
DBEnvironment along with the DBECon file. Because the DBECon file
contains the SCR information, you do not need to explicitly obtain the
SCR information when using STORE.
SQLUtil STORE is usually used only for a static store with non-archive
mode logging. It is also used with archive mode logging on MPE/iX when
TURBO STORE is not installed on your system.
Otherwise, SQLUtil STOREONLINE, which uses TURBO STORE, is preferred
because you can keep the master DBEnvironment in service while doing a
dynamic store with STOREONLINE. (Storing the master DBEnvironment with
STOREONLINE is discussed in a later section of this chapter.)
Storing the Entire Master DBEnvironment Using SQLUtil STORE
In order to use SQLUtil STORE, the DBEnvironment must be completely shut
down. Notify users of your intent to shut down so they can complete all
current transactions and stop originating new ones. Terminate the
ALLBASE/Replicate application executing against the master DBEnvironment
and issue a STOP DBE statement. This will terminate all operations and
roll back any uncommitted transactions active against the DBEnvironment.
This should also cause the ALLBASE/Replicate application running on the
slave to stop. Restart the DBEnvironment in single-user mode to insure
it is consistent. Only if you are doing archive mode logging, issue the
BEGIN ARCHIVE/COMMIT ARCHIVE commands to write current log information
into the DBECon file. Then shut down the DBEnvironment, immediately
invoke SQLUtil, and begin the STORE.
If you need to hard resynchronize the entire PartsDBE DBEnvironment from
a master to a slave, sample steps for the store could be as follows:
1. Notify all users of your intent to shut down the DBEnvironment.
2. Open a window on the master and go to the group where you normally
invoke ISQL to connect to the master. This is window A.
3. Open another window 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. This will automatically cause the
related slave application to stop. You may experience a short
wait until the slave application realizes that the master
application has stopped. (When you issue the STOP DBE statement
in step 6, this will automatically cause the ALLBASE/Replicate
application on the master to stop, or you can use the BREAK or
ABORTJOB commands to do this.)
5. From window A, invoke ISQL, and connect to the master:
:ISQL
isql=> CONNECT TO 'PARTSDBE';
6. From window A, issue the following SELECT statement to ensure that
all users have exited the DBEnvironment. If all users are off,
stop the DBEnvironment.
isql=> SELECT * FROM SYSTEM.USER;
:
E (to exit from the browser)
isql=> STOP DBE;
7. From window B, use the SQLUtil ADDLOG command to add at least one
more log file-- if you are doing a static store, are using archive
logging, and have only one log file. Archive logging requires a
minimum of two log files.
: SQLUTIL
>> ADDLOG
:
8. From window B, use the SQLUtil ALTDBE command to set AUTOSTART to
OFF if you need to guarantee that no other users or applications
can access the DBEnvironment while you do the store. (You set
AUTOSTART OFF because, when AUTOSTART is OFF, no one except the
DBA can access the DBEnvironment. The DBA must explicitly issue a
START DBE statement to restart the DBEnvironment in single-user
mode to gain access. Even applications with DBA authority
cannot connect because they are usually coded to use the
CONNECT statement, rather than START DBE, to connect to the
DBEnvironment.)
9. From window A, restart the DBEnvironment in single-user mode.
This brings the DBEnvironment to a consistent state. All
uncommitted transactions are rolled back. All committed
transactions are flushed from the log buffers and data buffers to
files on disk.
: ISQL
isql=> START DBE 'PARTSDBE';
10. From window A, only if you are doing a static backup of a
DBEnvironment that is using archive mode logging, or if you are
switching to archive mode logging, execute the BEGIN ARCHIVE and
COMMIT ARCHIVE statements. This changes to a new log file and
writes log information into the DBECon file in preparation for a
static backup.
isql=> BEGIN ARCHIVE;
isql=> COMMIT ARCHIVE;
11. From window A, exit from the DBEnvironment again by using the
command:
isql=> EXIT;
The DBEnvironment is now in a consistent state and ready for
backup.
12. Mount the appropriate tape on the tape drive.
13. From window B, invoke SQLUtil and store the DBEnvironment.
: SQLUTIL
>> STORE
WARNING: If you are using STORE to support Rollforward Recovery
through archive logging, you must precede this command
with the BEGIN ARCHIVE and COMMIT archive commands in
ISQL. (See the ALLBASE/SQL DBA Guide under Backup and
Recovery for details.)
Do you wish to proceed (y/n)? y
DBEnvironment Name: PARTSDBE
Maintenance Word: Return
To File Name: TAPE
:
DBEnvironment stored
>> EXIT
14. From window B, use ALTDBE to set AUTOSTART ON again -- if you
previously used the SQLUtil command ALTDBE to set AUTOSTART OFF.
15. From window A, start the DBEnvironment again in multiuser mode for
production users.
: ISQL
isql=> START DBE 'PARTSDBE' MULTI;
Restoring to the Slave Using SQLUtil RESTORE
After the slave DBEnvironment is restored, you can resume soft
resynchronization between master and slave, and the slave will catch up
with any transactions that were committed against the master after the
DBEnvironment was stored. If the restore on the slave will take a long
time, keep the master out of service until the slave is restored to
prevent the need for another hard resynchronization.
The steps for restoring the DBEnvironment from the master onto the slave
using the SQLUtil RESTORE 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 on the slave, verify that the ALLBASE/Replicate
application is not executing against the master partitions that
supply transactions for the slave tables being loaded. When the
ALLBASE/Replicate application was terminated on the master, the
related application should have automatically terminated on the
slave.
4. From window X, verify that the slave DBEnvironment is not in
operation.
5. From window Y, use the SQLUtil PURGEALL command to remove the old
DBEnvironment and its associated log files. Because this is the
transfer of an entire DBEnvironment image to the slave, the old
DBEnvironment must first be removed. If the DBECon file is not
accessible, PURGEALL will not work, and you must instead use the
PURGEFILE command to remove each file individually.
From window Y, execute the following:
: SQLUTIL
>> PURGEALL
DBEnvironment Name: PARTSDBE
Purge DBEnvironment and Log Files (y/n)?Y
DBEnvironment and Log Files purged.
>> EXIT
_________________________________________________________________
NOTE PURGEALL will not remove any log files that were in
existence prior to the last START DBE NEWLOG because they
are no longer recorded in the DBECon file. They must be
removed using PURGEFILE SM capability.
_________________________________________________________________
6. Mount the tape or transfer the file to the group that contains the
DBECon file by other appropriate means.
7. Restore the DBEnvironment from the backup using the SQLUtil
RESTORE command. For this example, assume that you are restoring
a DBEnvironment that uses non-archive logging.
If you instead did a static store of a DBEnvironment that uses
archive mode logging, the restoration process is the same as that
used when the store is done with a dynamic STOREONLINE. (Refer to
the topic, "Restoring the Slave Using Dynamic Restore Commands,"
later in this chapter.)
From window Y, issue the following commands:
: SQLUTIL
>> RESTORE
DBEnvironment Name: PARTSDBE
From File Name: TAPE
:
DBEnvironment restored.
>> EXIT
This series of commands has restored the master image of the
DBEnvironment to the slave. The SCR information is contained in
the DBECon file that was automatically copied onto the slave by
RESTORE.
8. From window X, use the ISQL command START DBE NEWLOG to start the
DBEnvironment with a new log file, a unique AUDIT NAME, and the
AUDIT LOG option. This will preserve the SCR information. You
may want to change the AUDIT ELEMENTS at this time. If you plan
to modify the association between partitions and tables, start in
single-user mode. If you plan to retain the partition scheme from
the master, you can start in multiuser mode. For this example,
assume the retention of the master partition scheme. Execute the
following:
:ISQL
isql=> START DBE 'PARTSDBE' MULTI NEWLOG
> DUAL LOG,
> AUDIT LOG,
> AUDIT NAME = 'SLDBE1',
> CHECKPOINT COMMENT DATA AUDIT ELEMENTS,
> LOG DBEFILE PTSLGA1 AND PTSLGB1
> WITH PAGES = 1000,
> NAME = 'PTSLGA1' AND 'PTSLGB1';
9. From window Y, add at least one additional log file using the
SQLUtil ADDLOG command.
: SQLUTIL
>> ADDLOG
:
10. From window X, use the following steps to turn AUTOSTART ON, if
you desire. (If you used the SQLUtil ALTDBE command to set
AUTOSTART OFF before you stored the DBEnvironment on the master,
AUTOSTART will still be off in the restored copy of the
DBEnvironment.)
_________________________________________________________________
WARNING Issue the STOP DBE statement before you exit. If you do
not, the SQLUtil ALTDBE command will not be able to
connect to set AUTOSTART ON.
_________________________________________________________________
From window X, issue the following:
isql=> STOP DBE;
isql=> EXIT;
From window X, invoke SQLUtil and set AUTOSTART back on.
11. From window Y, verify that the DBEnvironment is properly
configured using the SQLUtil SHOWDBE ALL and SHOWLOG commands.
>> SHOWDBE
DBEnvironment Name: PARTSDBE
Maintenance Word: Return
Output File Name (opt): Return
>ALL
:
>//
>> SHOWLOG
DBEnvironment Name: PARTSDBE
Maintenance Word: Return
Connect (y/n) opt? Y
:
Continue (y/n) opt? Y
>> EXIT
If the DBEnvironment is properly configured, proceed with the
steps to restart the slave and master ALLBASE/Replicate
applications. If the hard resynchronization has not taken too
long, the slave will be able to catch up on the transactions
committed on the master while hard resynchronization has taken
place on the slave.
Starting the Application on the Slave
1. From window Y, start the ALLBASE/Replicate application on the
slave. For this example, assume you wish to replicate partitions
1 (DEFAULT), 2 (COMMENT), 10, and the partitions which replicate
DDL operations. The slave application is started first with the
following command:
: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
Starting the Application on the Master
1. From window B, start the ALLBASE/Replicate application on the
Master by executing the following:
: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
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. Remember to
commit work.
2. 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.