Example 1: SQL code handling with generated High-speed Apply JCL in the online interface

Based on the responses that you define, the product generates a separate High-speed Apply JCL generation step in your work ID. This example:

• Operates on three tables
• Assumes an existing Log Master job that generates SQL statements (for migrating data to a set of parallel tables)
• Contains a separate High-speed Apply job that executes the SQL against the target tables. The High-speed Apply job:
  • Defines a separate conflict file, where deferred statements are written for subsequent processing, examination, or research
  • Defines special handling for SQL code -803 to write any SQL statements that encounter a duplicate row into a conflict file for subsequent processing
  • Defines special handling for SQL code +100 to write any statements that do not affect any rows in the target table into the conflict file for examination and research

To define SQL code responses using generated High-speed Apply JCL

1. On the Main Menu panel, select Generate High-speed Apply JCL and press Enter.
2. On the High-speed Apply JCL Generation panel, complete the following steps:
   1. Under Startup Parameters, specify the following field values as shown in the following figure:
      • Input Type: SQL
      • SSID: DGA1
      • FileName field: DB2DBA.DEV.OUTSQ1
   2. Under ConflictFile Parameters, type DB2DBA.DEFER.D&DATE..T&TIME in the FileName field.
   3. At the bottom of the panel, type E in the Edit Conflict Rules field.

   4. Press Enter.

                        High-speed Apply JCL Generation     
      Command ===>                                                       
                                                                        
          StartUp Parameters                DistributionTuning Parameters
               InputType: SQL                PartitionClustering: N (Y=Yes, N=No)
                    SSID: DGA1                      RIClustering: Y (Y=Yes, N=No)
                FileName: DB2DBA.DEV.OUTSQ1                              
                                                                        
          Conflict Parameters               BindTuning Parameters        
               RetryFail: _________               StatementCount: _____  
              RetryLimit: _____                      MaxPackages: _____  
              RetryValue: _____                       Syncronous: N (Y=Yes, N=No)
                                                                        
          Bind Parameters                   LogicalLog Parameters        
                  AuthId: ________                       SqlType: _______
               BindOwner: ________                                       
            CollectionId: ________
                                           
          ConflictFile Parameters                                        
                FileName: DB2DBA.DEFER.D&DATE..T&TIME.                   
              SingleFile: Y (Y=Yes, N=No)
                                                                        
          CommitTriggers Parameters         Agent Parameters             
          StatementCount: _____                        MaxAgents: _____  
                                                                        
      Edit Conflict Rules: E   (E=Edit)  Edit Object Mapping Rules:   (E=Edit)
      --------------------------------------------------------------------------------
      Press PF3 to save the workid or to generate JCL. Press PF12 to cancel.

3. On the High-speed Apply Conflict Specification panel, complete the following steps:

   1. In the ACT field, type I and press Enter.

   2. Specify the following field values, as shown in the following figure:

      • CODE field: -803
      • ACTION 1 field: DeferStatement
      • ACTION 2 field: Continue

                           High-speed Apply Conflict Specification                  
      Command ===>                                                  Scroll ===> HALF
                                                                                   
      Specify values for the High-speed Apply AnyConflict Parameters.               
      Displayed rules are either product defaults or previously entered rules.      
      Edit displayed rules or type an action code (ACT) and press Enter.            
                                                                                    
      ACT - I= Insert, D=Delete, R=Repeat                                           
      ACT CODE         ACTION 1           ACTION 2                               
      _    NEGATIVE     ABORT               _________________                      
      _    POSITIVE     WARN                _________________                      
      _    MULTIPLEROWS WARN                _________________                      
      _    NOROWS       WARN                _________________                      
      _    TIMEOUT      RETRY               _________________                      
      _    RICONFLICT   ABORT               _________________                      
      _    -803         DEFERSTATEMENT      CONTINUE              
      _    100          DEFERSTATEMENT      CONTINUE              
      *********************** End Of List ***********************
   3. Press Enter.
   4. In the ACT field of the newly entered rule, type I and press Enter.
   5. In the blank row, specify the following field values:
      • CODE field: 100
      • ACTION 1 field: DeferStatement
      • ACTION 2 field: Continue
   6. Press Enter.
   7. Press F3 until you return to the Work ID File Menu.

   Important

   Placing new rules at the bottom of the list ensures that they override the product’s default conflict resolution rules.

   This combination of actions causes the product to write any statement that encounters the -803 SQL code into the conflict file, and continue processing with the next statement in the input file.

4. On the Work ID File Menu panel:

   1. Select Save As.
   2. Select Generate JCL.

   For an example, see Creating-a-batch-job-from-a-work-ID.