Checkpoint restart capability in existing DDL for Batch Processor
Article ID: 54278
Updated On: 10-27-2023
Products
Issue/Introduction
How to introduce the Batch Processor checkpoint restart capability into my existing DDL? This DDL may have been produced by other products.
The problem with performing standard DDL is that when an error of some type occurs during the execution, the whole execution in DB2 rolls back to the last commit point. Normally, users insert COMMIT statements at various places in the DDL in order to avoid a rollback back to the start of the execution. Executions of DDL can often take hours to complete, so they can also take hours to roll back if allowed to go back to the beginning.
The problem is that a COMMIT statement does not provide for a positioned restart to occur. In other words, the user must identify the location where the error occurred, fix the problem and then locate the last COMMIT statement. When this is done, the DDL previous to the last commit statement must be removed before the DDL is resubmitted otherwise the execution would try to redo the DDL that has already been committed to the database.
If there are multiple locations in the DDL where a statement has an error then the same procedure must be repeated to remove those statements already processed. Alternatively the user must create a new DDL dataset containing the remaining DDL to be processed. This can be time consuming and may introduce errors.
Resolution
To solve this situation, use Batch Processor for DB2 for z/OS to have checkpoint restart capability in the DDL. This can be accomplished with the following steps.
- Edit your DDL with ISPF edit.
Add in .SYNC nnnn lines into strategic places in the DDL.
eg: .SYNC 100
The number "nnnn" should be an increasing incremental number. Increment them by 5 or 10 or more so that additional .SYNC lines can be inserted in between existing ones if required.
eg: .SYNC 200
Good places are:
- after the create of large tablespaces /Indexspaces
- after the create of databases
- after tables that will have RI later in the run
- after the creation of RI
.SYNC nnnn statements in your SQL provide these benefits:
Each one:
- Performs a COMMIT at that point
- establishes a checkpoint where the job committed successfully. This checkpoint record is stored in a BATCH PROCESSOR DB2 table....See Batch Processor Objects
- Are able to be inserted manually into any SQL
- Must be numbered incrementally
- Can be referred to by number in the Batch Processor .RESTART card
- Can be overridden by the Batch Processor .RESTART card
- The last sync number is recorded in the Batch Processor Log table
Any points that are considered good restart points or where a COMMIT would normally be used should have a ".SYNC nnnn" line. No COMMIT statement should be used when .SYNC cards are used. - Job Submission
Get into the ISPF interface for the Database Management Tools for DB2 for z/OS and either submit the DDL using Batch Processor via the BP command or from the Value Pack Menu, use the submission screen. Add in the dataset name(member) where the DDL can be found. Take careful note of the RESTART and also the EDIT DATASET field. Then submit the DDL either online or batch as desired via the EXECUTION MODE field ensuring that the DB2 SSID is the correct one. - Job Execution
When a .SYNC statement is encountered and is processed, a rollback to that point will occur if an error occurs after that point in the execution. The job will end with a report from Batch Processor stating the last SYNC point that was processed successfully. - Job Restart
To restart the job submit the DDL via the same Batch Processor screen specifying RESTART "Y" which will generate a RESTART(SYNC) card in your JCL. Use the EDIT DATA SET field to edit the DDL if the error requires that the offending DDL be changed, then submit the DDL again using the EXECUTION MODE field as before. Do not change or remove anything except to correct the problem DDL statement.
The DDL only needs to be edited if there is a logical or syntax error in it. If the problem is lack of DASD then no edit is required at all. Just resubmit with RESTART "Y" as before when DASD is found. The Batch Processor will begin processing from the last successful SYNC point automatically and, will show in the audit report all the DDL lines that have been bypassed previous to the restart point. There is no limit on the number of times the job may be restarted.
The restart information is recorded on the Batch Processors DB2 Product tables so the restart does not have to happen during the current session. It can be restarted at some later date as the restart information does not expire.
Restart Override
The user may override this with an OVERRIDE or specific sync point number in the RESTART field. Read about the options in the RESTART field in the Batch Processor User Guide, Restart Batch Processor Jobs or by using a ? in the RESTART field and then pressing PF1 to obtain the online help text for the RESTART field. A specific sync point number can be entered into the RESTART field which will prompt the execution to start from that point regardless of where it started from before. Care must be taken with OVERRIDE in case work already committed is accidentally attempted again.
Additional Benefits
DDL can be executed online with Batch Processor unlike SPUFI and DSNTEP2. Other types of SQL can also be executed such as DML and DCL in this way also.
Warning
The .SYNC statements in the DDL are not recognized by other utilities that execute DDL such as SPUFI or DSNTEP2. If this DDL is executed with these utilities syntax errors will be produced by these tools. These .SYNC statements can be changed to "-- SYNC" with an ISPF change command to make them comments and changed back to .SYNC when required.
Sample workflow showing Batch Processor :
1. Connecting to ssid
2. Dropping an index
3. Sync point 5 does a commit and saves a restart point to the restart table
4 Creates the index
5 Sets a new restart point and commit at Sync point 10
6. The job is finished and restart records are cleared.
.CONNECT ssid
DROP INDEX authid.ixname;
.SYNC 5 'DROP INDEX authid.ixname'
-- **************************************************************
-- * *
-- * INDEX CREATE AND ALTER STATEMENTS *
-- * *
-- **************************************************************
-- authid.ixname WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.
SET CURRENT SQLID = 'authid';
CREATE UNIQUE INDEX authid.ixname
ON authid.table
(xx_xxxxxxx ASC
,xx_xxxxxxxxx_xx ASC
,xx_xxxx_xxxxx ASC
,xx_xxxxx ASC
,xx_xxxxxxxxx ASC
,xx_xxxxxxxxx_xx ASC
,xx_xxxxx_xxxx ASC
,xx_xxxxxxxxxx ASC
,xx_xxxxx_xxxxxxxxx ASC
)
USING STOGROUP stogroup
PRIQTY nnnnnn
SECQTY nnnnn
ERASE NO
FREEPAGE 5
PCTFREE 10
CLUSTER
BUFFERPOOL BP0
CLOSE NO
PIECESIZE 2G
;
.SYNC 10 'CREATE INDEX authid.ixname'
Additional Information
Feedback
