Batch Processor : Checkpoint restart capability in existing DDL for Batch Processor
search cancel

Batch Processor : Checkpoint restart capability in existing DDL for Batch Processor


Article ID: 54278


Updated On:


RC Compare for DB2 for z/OS RC/Migrator for DB2 for z/OS RC/Update for DB2 for z/OS


How do I 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.


Release: R20
Component: RBP


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.

  1. 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.

  2. 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.

  3. 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.

  4. 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.

    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. 




.SYNC 5         'DROP INDEX authid.GRMODPM0'

-- **************************************************************
-- *                                                            *
-- * INDEX CREATE AND ALTER STATEMENTS                          *
-- *                                                            *
-- **************************************************************


    SET CURRENT SQLID = 'authid';

         ON authid.GRMODPMD
        (FX_PROCESO           ASC
        ,CO_PROVINCIA_GR      ASC
        ,NU_RESP_COBRO        ASC
        ,FX_COBRO             ASC
        ,CO_DOCUMENTO         ASC
        ,NU_DOCUMENTO_GR      ASC
        ,IN_AVISO_PAGO        ASC
        ,IN_DOCUMENTO         ASC
                   USING STOGROUP SYSDEFLT
                                  PRIQTY 273600
                                  SECQTY 27360
                                  ERASE NO
                   FREEPAGE 5
                   PCTFREE 10
         CLOSE NO
         PIECESIZE 2G

.SYNC 10        'CREATE INDEX authid.GRMODPM0'


Additional Information

Batch Processor Commands