Creating a restart table
The installation procedures for the High-speed Apply Engine create a restart table that is used if you do not include the TableName parameter in the [Restart] section of the configuration.
Use the procedures in this section to create additional restart tables that High-speed Apply Engine can use to track apply processing.
Before you begin
By default, High-speed Apply Engine uses a single restart table for all apply requests. (This table can be the restart table created during installation, or one that you designate.) In normal use, this practice works well. However, if you run a large number of apply requests simultaneously on the same target database, you might begin to experience resource contention for the single restart table.
If you experience contention, create a restart table for each user that runs apply requests simultaneously on the target database. This practice eliminates contention for the restart table.
High-speed Apply Engine provides sample data definition language (DDL) files to help you create the restart table as follows:
- For a Db2 mainframe target, the HLQ.DBSAMP library contains a member named APTREST. HLQ represents a high-level qualifier that you specified when you installed High-speed Apply Engine.
- For Db2 LUW targets, the examples/db2udb subdirectory in the installation directory contains a file named CrtUDBRestartTbl.sql. The installation directory is the UNIX or Windows path that you specified for High-speed Apply Engine during installation.
- For Oracle targets, the examples/oracle subdirectory in the installation directory contains a file named CrtOraRestartTbl.sql. The installation directory is the UNIX or Windows path that you specified for High-speed Apply Engine during installation.
To create a personal restart table for Db2 on a mainframe target
Create a DDL file with the statements to create a restart table.One method of doing this is to use the sample DDL file located in a member named APTREST in HLQ.DBSAMP library. If you use this file as a template, copy the file, renaming it according to your site requirements. Edit the file with any standard editor.
Regardless of how you create the file, when you have finished, the DDL statements should resemble those shown in figure "Sample DDL to create a restart table: Db2."
- Edit the DDL statements as follows:
- Change the database name and table space name to values that are appropriate for your site.The table space that you specify must be located in the same Db2 subsystem as the target tables for the apply request.
- Change the table name and index name so that they are unique.
(Optional) Define the alias (BMCAPT_APTRSCL) so that it uses the name of the personal restart table. If an earlier version of High-speed Apply Engine is already installed, you must drop and re-create the alias.Do not redefine this alias unless you want all apply requests that do not specify the TableName parameter in the [Restart] section to use the personal restart table. The alias is the default value for this TableName parameter. If an apply request does not specify a restart table name, High-speed Apply Engine writes restart information to the table that the alias represents.
When you have finished editing your DDL file, the statements in it should resemble those in the following figure. The databaseName, tableSpaceName, owner, restartTableName, and indexName variables represent unique values that are appropriate for your environment.
//APTREPOS JOB STATEMENT
//STEP01 EXEC PGM=IKJEFT01,DYNAMNBR=20
//STEPLIB DD DSN=SYS2.DB2V71M.DSNLOAD,DISP=SHR
//SYSTSPRT DD SYSOUT=*
//SYSPRINT DD SYSOUT=*
//SYSTSIN DD *
DSN SYSTEM(DBAT)
RUN PROGRAM(DSNTEP2)
END
//SYSIN DD *
*--------------------------------------------------------------------
*- DEFINE RESTART DB,TABLESPACE,TABLE,INDEX AND SYNONYM
*--------------------------------------------------------------------
* DROP DATABASE databaseName ;
* COMMIT ;
CREATE DATABASE databaseName
STOGROUP SYSDEFLT
CCSID EBCDIC;
COMMIT;
CREATE TABLESPACE tableSpaceName
IN databaseName
USING STOGROUP SYSDEFLT
PRIQTY 240
SECQTY 48
CLOSE NO
FREEPAGE 0
PCTFREE 0
SEGSIZE 4;
COMMIT;
CREATE TABLE owner.restartTableName
( RESTART_ID CHAR(16) NOT NULL,
REQUEST_ID CHAR(4) NOT NULL,
RECORD_TYPE CHAR NOT NULL,
AGENT_ID SMALLINT NOT NULL,
RECORD_SEQUENCE SMALLINT NOT NULL,
REQUEST_TIMESTAMP TIMESTAMP NOT NULL,
RECORD_TIMESTAMP TIMESTAMP NOT NULL,
TOTAL_LENGTH INTEGER NOT NULL,
DATA LONG VARCHAR NOT NULL
) IN databaseName.tableSpaceName;
COMMIT;
CREATE UNIQUE INDEX owner.indexName
ON owner.restartTableName
( RESTART_ID,
RECORD_TYPE,
AGENT_ID,
RECORD_SEQUENCE
) USING STOGROUP SYSDEFLT;
COMMIT;
CREATE ALIAS BMCAPT_APTRSCL FOR owner.restartTableName;
GRANT ALL ON TABLE owner.restartTableName TO PUBLIC;
COMMIT;
- Save your DDL member, and execute it to create the restart table.
To create a personal restart table for Db2 LUW
Create a data definition language (DDL) file with the statements to create a restart table.One method of doing this is to use the sample DDL file named CrtUDBRestartTbl.sql in the examples/db2udb subdirectory as a template. If you use this file as a template, copy the file, renaming it according to your site requirements. Edit the file with any standard editor.
Regardless of how you create the file, when you have finished, the DDL statements should resemble those shown in the figure "Sample DDL to create a restart table: Db2 LUW."
- Edit the DDL statements as follows:
- Edit the table name to a value that is appropriate for your site.The table name that you specify must be located in the same Db2 LUW database as the target tables for the apply request. Note this name so that you can specify it in the configuration for the apply request.
- Edit the index name so that it is unique.
- (Optional) Define the alias (BMCAPE_APERSCL) so that it uses the name of the personal restart table. If an earlier version of High-speed Apply Engine is already installed, you must drop and re-create the alias.Do not redefine this alias unless you want all apply requests that do not specify the TableName parameter in the [Restart] section to use the personal restart table. The alias is the default value for this TableName parameter. If an apply request does not specify a restart table name, High-speed Apply Engine writes restart information to the table that the alias represents.
- Edit the GRANT statement if necessary. For personal restart tables, you can restrict access if necessary.
Add a CREATE TABLESPACE statement if this is the standard for your site.When you have finished editing your DDL file, the statements in it should resemble those in the following figure. The restartTableName, and indexName variables represent unique values appropriate for your environment.
CREATE TABLE restartTableName
( RESTART_ID CHAR(16) NOT NULL,
REQUEST_ID CHAR(4) NOT NULL,
RECORD_TYPE CHAR NOT NULL,
AGENT_ID SMALLINT NOT NULL,
RECORD_SEQUENCE SMALLINT NOT NULL,
REQUEST_TIMESTAMP TIMESTAMP NOT NULL,
RECORD_TIMESTAMP TIMESTAMP NOT NULL,
TOTAL_LENGTH INTEGER NOT NULL,
DATA LONG VARCHAR NOT NULL
);
CREATE UNIQUE INDEX indexName ON restartTableName
( RESTART_ID,
RECORD_TYPE,
AGENT_ID,
RECORD_SEQUENCE
);
GRANT ALL ON TABLE restartTableName TO PUBLIC;
CREATE ALIAS BMCAPE_APERSCL FOR restartTableName;
COMMIT;
- Save the edited DDL file.
Run the DDL file to create the restart table.
For Windows, type db2cmd from a command prompt to get to the Db2 command level prompt (CLP); then type the following commands:
db2 connect to <database>
db2 -t -f CrtUDBRestartTbl
db2 disconnect
For UNIX, type the following commands:
db2 connect to <database>
db2 -t -f CrtUDBRestartTbl
db2 disconnect
To create a personal restart table for Oracle
Create a data definition language (DDL) file with the statements to create a restart table.One method of doing this is to use the sample DDL file named CrtOraRestartTbl.sql in the examples/oracle subdirectory as a template. If you use this file as a template, copy the file, renaming it according to your site requirements. Edit the file with any standard editor.
Regardless of how you create the file, when you have finished the DDL statements should resemble those shown in the figure "Sample DDL to create a restart table: Oracle."
Edit the DDL statements as follows:
- Edit the table name to a value that is appropriate for your site.The table name that you specify must be located in the same Oracle database as the target tables for the apply request. Note this name so that you can specify it in the configuration for the apply request.
- Edit the index name so that it is unique.
- (Optional) Define the public synonym (BMCAPE_APERSCL) so that it uses the name of the personal restart table. If an earlier version of High-speed Apply Engine is already installed, you must drop and re-create the public synonym.Do not redefine this synonym unless you want all apply requests that do not specify the TableName parameter in the [Restart] section to use the personal restart table. The synonym is the default value for this TableName parameter. If an apply request does not specify a restart table name, High-speed Apply Engine writes restart information to the table that the public synonym represents.
- Edit the GRANT statement if necessary. For personal restart tables, you can restrict access if necessary.
When you have finished editing your DDL file, the statements in it should resemble those in the following figure. The restartTableName and indexName variables represent unique values appropriate for your environment.
CREATE TABLE restartTableName
( RESTART_ID CHAR(16) NOT NULL,
REQUEST_ID CHAR(4) NOT NULL,
RECORD_TYPE CHAR NOT NULL,
AGENT_ID SMALLINT NOT NULL,
RECORD_SEQUENCE SMALLINT NOT NULL,
REQUEST_TIMESTAMP DATE NOT NULL,
RECORD_TIMESTAMP DATE NOT NULL,
TOTAL_LENGTH INTEGER NOT NULL,
DATA LONG VARCHAR NOT NULL
) ;
CREATE UNIQUE INDEX indexName ON restartTableName
( RESTART_ID,
RECORD_TYPE,
AGENT_ID,
RECORD_SEQUENCE
) ;
CREATE PUBLIC SYNONYM BMCAPE_APERSCL FOR restartTableName;
GRANT SELECT, UPDATE, DELETE, INSERT ON restartTableName
TO PUBLIC;
EXIT- Save the edited DDL file.
Run the DLL file to create the restart table.
From SQL*Plus, issue the following command:
@CrtOraRestartTbl
From a UNIX or Windows command prompt, issue the following command:
sqlplus userID / password @CrtOraRestartTbl
If you are not already running SQL*Plus, you must supply your user ID and password to start the utility.
Where to go from here
With the restart table in place, you can specify the restart parameters in the configuration for the apply request. For more information about the parameters, see Specifying-restart-parameters. For more information about restarting an apply request, see Restarting-an-apply-request.
Related topics