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:
- Performs a COMMIT at that point
- establishes a checkpoint where the job committed successfully.
- 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, 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 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.
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 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 attempted again.
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.
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 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. Th job is finished and restart records are cleared.
DROP INDEX authid.GRMODPM0;
.SYNC 5 'DROP INDEX authid.GRMODPM0'
-- * *
-- * INDEX CREATE AND ALTER STATEMENTS *
-- * *
-- authid.GRMODPM0 WILL BE CREATED VIA THESE NATIVE DB2 COMMANDS.
SET CURRENT SQLID = 'authid';
CREATE UNIQUE INDEX authid.GRMODPM0
USING STOGROUP SYSDEFLT
.SYNC 10 'CREATE INDEX authid.GRMODPM0'