How To Specify SQL Codes Processing for Log Apply

Various SQL errors that occur during the Log Apply processing can cause unnecessary terminations. For example, when you replicate a production table to a test table, insertions or updates to the test table may fail because of duplicate rows (SQL code -803). You can avoid such unnecessary terminations by customizing how Log Apply responds to the SQL codes for insert, delete, and update operations.
caladb2
Various SQL errors that occur during the Log Apply processing can cause unnecessary terminations. For example, when you replicate a production table to a test table, insertions or updates to the test table may fail because of duplicate rows (SQL code -803). You can avoid such unnecessary terminations by customizing how Log Apply responds to the SQL codes for insert, delete, and update operations.
Specify processing of the SQL codes as follows:
Bypassing SQL errors can harm your data. Use caution when specifying to skip any SQL error.
Allow or Restrict SQL Errors Globally
To allow or restrict any SQL errors (negative SQL code) in processing, specify the Batch Processor .OPTION command as follows:
.OPTION SQLERRORS|NOSQLERRORS
  • SQLERRORS
    Skips all SQL errors. Processing continues when SQL errors are encountered.
  • NOSQLERRORS
    Restricts SQL errors. Processing terminates when an SQL error is encountered. This option is the default.
Specify Log Apply Action for SQL Codes
To customize the Log Apply action, specify the required action in the SQL codes data set. The SQL code actions are evaluated in the order they are specified in the data set and any later specification of the same SQL code overrides the previous setting. Job output messages LAE90135 and LAE90136 display the setting for SQL code processing.
This setting overrides the SQL error processing that is specified using the Batch Processor .OPTIONS command.
Log Apply always writes the SQL error text in the error report (ERRDDN ddname) and discards failed records to the discard data set (DISCARDDN ddname), regardless of the action that you specify for an SQL code.
Follow these steps:
  1. Create a sequential input data set with the following parameters:
    LRECL=80,RECFM=FB
  2. Enter a record for each SQL code action in the following format:
    type(code)=action [comment]
    • type
      Specifies the SQL operation. The following values are available:
      • INSERT
      • UPDATE
      • DELETE
      • ALL -- Indicates all of the above operations.
    • code
      Specifies the SQL code. The following values are available:
      • SQL code
        Specifies a particular SQL code.
      • POS
        Specifies all SQL codes higher than 0.
      • NEG
        Specifies all SQL codes less than 0.
    • action
      Specifies the action that Log Apply performs. The following values are available:
      • CONTINUE
        Continues processing without changing the return code.
      • WARNING
        Continues processing and sets the return code of 4.
      • TERMINATE
        Terminates processing and sets return code of 16.
    • comment
      (Optional) Specifies additional information about the SQL code and its processing.
  3. Add the following DD statement to the Log Apply JCL, specifying the input data set created in step 1:
    //sqlcodesdd DD DISP=SHR,DSN=dsname
  4. Add the SQLCODES control statement to the LOGAPPLY control statement:
    SQLCODES sqlcodesdd
Example: Terminate on All SQL Errors Except -803 on Insertions
To terminate processing on all SQL errors but the -803 code on insertions, specify the following:
  1. Restrict all SQL errors globally by specifying NOSQLERRORS in the Batch Processor .OPTIONS command.
  2. Specify the required action for the SQL code -803 in the Log Apply job as follows:
    INSERT(-803)=CONTINUE
Example: Issue a Warning on All SQL Errors, Terminate after -532 on Deletions
To issue a warning and continue processing for any SQL error but -523 on deletions, specify the following:
  1. Restrict all SQL errors globally by specifying NOSQLERRORS in the Batch Processor .OPTIONS command.
  2. Specify the required actions in the Log Apply job as follows:
    ALL(NEG)=WARNING
    DELETE(-532)=TERMINATE
    Specify the SQL code processing setting in the shown order. The later specification overrides the previous setting.
Example: Skip All SQL Errors but -803 on Insertions and Updates, Issue a Warning for Missing Data (+100) on Updates
To skip all SQL errors but terminate processing after the -803 code on insertions and updates, and to issue a warning on the SQL code +100 on updates, specify the following:
  1. Allow all SQL errors globally by specifying SQLERRORS in the Batch Processor .OPTIONS command.
  2. Specify the required actions for the SQL codes in the Log Apply job as follows:
    INSERT(-803)=TERMINATE
    UPDATE(-803)=TERMINATE
    UPDATE(+100)=WARNING