Example 1: SQL code handling with EXECSQL in the online interface

Using EXECSQL, you can define responses to SQL codes in the BMC AMI Log Master online interface. Based on the responses that you define, the product generates an SQLCODES DD statement, and includes it in the JCL that it generates from your work ID.

For this example, assume that you are the database administrator for a corporation. You have just discovered that all of the invoices that were recorded for the last week were accidentally deleted.

Related topic

Handling-SQL-codes-with-EXECSQL

 However, customers have continued to call in with changes to their invoices, and you know that several operators have created new records with the updates. To recover from the deletions, you must perform an UNDO process, which includes generating UNDO SQL. However, because the operators have already reinserted certain rows, some of the INSERT statements in the UNDO SQL (reversing the accidental deletions) will fail with an SQL code of -803. You need to define special handling for the -803 code.

You decide to take the following actions:

  1. Generate UNDO SQL statements to reverse the accidental deletions.

  2. Generate a Backout Integrity report and a Detail report.

    The reports provide the information needed to resolve any discrepancies that you might discover later. You can perform this step at the same time that you generate the UNDO SQL statements.

  3. In the BMC AMI Log Master online interface, define a response to the -803 SQL code as shown in the following figure. When the product encounters duplicate records, it issues a warning instead of aborting the job.

    SQLCODES             SQL Execution Codes Handler Maintenance       Line 1 of 2
    Command ===> ________________________________________________ Scroll ===> HALF
                                                                       SSID : DBAN
                                                                                   
    Specify the Default Codes:  (I=Ignore, W=Warn, T=Term, R=Rollback, A=Abort)    
      Negative SQL code . . . . . . . . A     SQL Warning  . . . . . . . . . . . W
      Positive SQL code . . . . . . . . W     Multiple Update/Delete . . . . . . W
                                                                                  
    Specify Additional SQL Handlers . . Y  (Y=Yes, N=No)                           
                                                                                   
    Type one or more codes. Then press Enter.                                      
      ACT . . . . . (I=Insert, D=Delete, R=Repeat)                                 
      UPD TYPE  . . (A=All, I=Insert, U=Update, D=Delete, L=DDL)                   
      CONDITION . . (NEG, POS, MULT, +nnn, -nnn, Wx where x = 0-9,A)               
      RESPONSE  . . (I=Ignore, W=Warn, T=Term, R=Rollback, A=Abort)                
    ACT  UPD TYPE  CONDITION  RESPONSE                                             
    _    I         -803       W                                                    
    *********** End Of List ************
  4. Execute the generated UNDO SQL statements, using the special SQL code handling.

  5. Use the information in the Backout Integrity and Detail reports to verify each warning that Db2 generates for duplicate INSERT statements.

    You can use the Detail report information to update the record to the correct status.

 

Tip: For faster searching, add an asterisk to the end of your partial query. Example: cert*