Optional: Creating generic plan tables

Optionally, you can create generic plan tables by using Tools Customizer. Tools Customizer will create the necessary JCL, but you must manually complete some steps before and after you submit the customization job.

Before you begin

Ensure that you selected the optional task to create generic plan tables on the Product Parameters panel in Tools Customizer and that Tools Customizer generated the customization job.

About this task

Before you can create generic plan tables, you must create secondary authorization identifiers (authorization IDs) that will act as the owners of the generic SQL Performance Analyzer plan tables. These secondary authorization IDs allow SQL Performance Analyzer to switch any user from their current authorization ID to one of the special SQL Performance Analyzer secondary authorization IDs, which are shared by all Db2 users.

Before you submit the customization job, you must complete the following steps manually.
Note: The support for the authorization exit program DSN3@SGN will be deprecated in 2024.

Procedure

  1. Define the secondary authorization IDs in the DSN3@ATH exit.
    The sample exit code for the DSN3@ATH exit is distributed with Db2 as member DSN3SATH in the DSNxxx.SDSNSAMP sample library.
    1. Obtain the most current copy of DSN3SATH and copy it to hlq.SANLSAMP. Many sites customize this exit by adding their own code to the Db2 sample program.
    2. Copy the SQL Performance Analyzer modifications from hlq.SANLSAMP(ANLAUTH), and paste them into the DSN3@ATH exit immediately after the SATH090 label. The following example shows the code with the secondary authorization IDs after the SATH090 label: 
      SATH090  DS    0H
* ===================================================================*
*  APPEND THE ANL SECONDARY AUTHIDS TO THE END OF THE SECONDARY LIST  *
* =================================================================== *
* THERE ARE 245 POSSIBLE ENTRIES ON THE SECONDARY AUTHID LIST. INSERT *
* AS MANY ANL IDS AS WILL FIT ON THE END, WITHOUT EXCEEDING 245 LIMIT *
* THE NUMBER OF ENTRIES AND THE ANL USER NAMES MUST CORRESPOND TO THE *
* ENTRIES IN ANL REGISTRY TABLE. THE NUMBER OF IDS (10) IS ARBITRARY. *
* =================================================================== *
         LA   R4,AIDLSEC               R4 ADDRESS OF SECONDARY AREA
         L    R0,AIDLSCNT              R0 NUMBER OF 2NDARY SLOTS USED
         LA   R3,=X'F5'                R3 CONSTANT 245 MAXIMUM SLOTS
         SR   R3,R0                    R3 HOLDS REMAINING SLOTS UNUSED
         BC   2,ANLOK                  OK IF >0 RESULT (CONDITION = 2)
         B    ENDANL                   ELSE NO SLOTS, CANNOT INSERT IDS
ANLOK    MH   R0,=X'0008'              MULTIPLY SLOTS * 8 = BYTES USED
         AR   R0,R4                    R0 STORE STOPPING ADDRESS HERE
ANLSEC   CLC  0(8,R4),=CL8'  '         IS THIS A BLANK SLOT?
         BE   ADDANL1                  GOOD, ADD SQL/PA SECONDARIES
         LA   R4,8(R4)                 BUMP UP AND CONTINUE
         CR   R4,R0                    HAVE WE EXHAUSTED THE LIST?
         BNL  ENDANL                   YES, NO MORE ROOM AVAILABLE
         B    ANLSEC                   NO, TRY NEXT SLOT
ADDANL1  DS   0H
         MVC  0(8,R4),=CL8'ANLUSER1'   ADD SECONDARY ID 1
         LA   R4,8(R4)                 BUMP TO NEXT
         BCT  R3,ADDANL2               YES, ROOM FOR ANOTHER
         B    ENDANL                   NO, WE ARE DONE
ADDANL2  MVC  0(8,R4),=CL8'ANLUSER2'   ADD SECONDARY ID 2
         LA   R4,8(R4)                 BUMP TO NEXT
         BCT  R3,ADDANL3               YES, ROOM FOR ANOTHER
         B    ENDANL                   NO, WE ARE DONE
ADDANL3  MVC  0(8,R4),=CL8'ANLUSER3'   ADD SECONDARY ID 3
         LA   R4,8(R4)                 BUMP TO NEXT
         BCT  R3,ADDANL4               YES, ROOM FOR ANOTHER
         B    ENDANL                   NO, WE ARE DONE
ADDANL4  MVC  0(8,R4),=CL8'ANLUSER4'   ADD SECONDARY ID 4
         LA   R4,8(R4)                 BUMP TO NEXT
         BCT  R3,ADDANL5               YES, ROOM FOR ANOTHER
         B    ENDANL                   NO, WE ARE DONE
ADDANL5  MVC  0(8,R4),=CL8'ANLUSER5'   ADD SECONDARY ID 5
         LA   R4,8(R4)                 BUMP TO NEXT
         BCT  R3,ADDANL6               YES, ROOM FOR ANOTHER
         B    ENDANL                   NO, WE ARE DONE
ADDANL6  MVC  0(8,R4),=CL8'ANLUSER6'   ADD SECONDARY ID 6
         LA   R4,8(R4)                 BUMP TO NEXT
         BCT  R3,ADDANL7               YES, ROOM FOR ANOTHER
         B    ENDANL                   NO, WE ARE DONE
ADDANL7  MVC  0(8,R4),=CL8'ANLUSER7'   ADD SECONDARY ID 7
         LA   R4,8(R4)                 BUMP TO NEXT
         BCT  R3,ADDANL8               YES, ROOM FOR ANOTHER
         B    ENDANL                   NO, WE ARE DONE
ADDANL8  MVC  0(8,R4),=CL8'ANLUSER8'   ADD SECONDARY ID 8
         LA   R4,8(R4)                 BUMP TO NEXT
         BCT  R3,ADDANL9               YES, ROOM FOR ANOTHER
         B    ENDANL                   NO, WE ARE DONE
ADDANL9  MVC  0(8,R4),=CL8'ANLUSER9'   ADD SECONDARY ID 9
         LA   R4,8(R4)                 BUMP TO NEXT
         BCT  R3,ADDANLA               YES, ROOM FOR ANOTHER
         B    ENDANL                   NO, WE ARE DONE
ADDANLA  MVC  0(8,R4),=CL8'ANLUSERA'   ADD SECONDARY ID 10
ENDANL   DS   0H
*                                                                     *
* =================================================================== *
*                                                                     *
  2. Optional: Define the secondary authorization IDs in the DSN3@SGN exit.
    Consider modifying the DSN3@SGN exit with the same set of IDs that you added to the DSN3@ATH exit. Even though the DSN3@SGN exit is used only for IMS and CICS sign-on authorizations, keeping these two exits consistent is a best practice. This step is identical to step 1 except that you add the secondary authorization IDs to the DSN3@SGN exit immediately after the SSGN090 label.
  3. Submit the customization job.
  4. Test the modified DSN3@ATH and DSN3@SGN authorization exits in isolation before putting the exits into production. To facilitate testing in isolation, load the DSN3@ATH and DSN3@SGN exits into the SYS1.DSNEXIT library or a similar stand-alone library so that they can be copied into SYS1.DSNLINK or SYS1.DSNLOAD during a test window and verified.
    1. Assemble and link the modules into SYS1.DSNEXIT or an equivalent library that does not currently contain the production exits.
      Attention: If the library is already on the link list, users have immediate access.
    2. During a test window or on a test system, rename the current exits (if they exist) and copy the new modules into SYS1.DSNLINK or SYS1.DSNLOAD.
    3. From a TSO session, enter the Db2I command. Select an empty file for input and enter the following Db2 statement: SET CURRENT SQLID = 'ANLUSER1'. If the authorization exits have been correctly modified, an SQLCODE of 0 is returned. If no valid secondary authorization ID by that name exists, Db2 returns an SQLCODE of -553, which means that the exit modifications are not working. If additional coding errors are present, the exits might abend. If you encounter a problem, correct the error and retest the exits after you rename the old production exits to their proper names.
    4. If the SQLCODE is 0, set all of the SQL PA secondary authorization IDs that you defined. This step verifies that all the secondary authorization IDs are spelled correctly.
    5. Migrate the DSN3@ATH and DSN3@SGN modules to a production library on the link list (refer to SYS1.PARMLIB member LNKLSTxx), such as SYS1.DSNLINK or SYS1.DSNLOAD, for access by all users.