DB2 Server for VSE & VM: Application Programming

Imposing Usage Restrictions on the SQLDSECT Area

There are two performance considerations about the SQLDSECT area that you should be aware of:

• Acquire and clear the SQLDSECT area only once.

  The DB2 Server for VSE examples shown in Figure 100 and Figure 102 assume that the TESTNAME is entered once.

  The DB2 Server for VM example shown in Figure 101 assumes that the TESTNAME is entered once. If TESTNAME is a subroutine of a mainline module, and if TESTNAME is invoked many times, you should acquire the SQLDSECT in the mainline module. The following is an example of how this may be done:

  1. In TESTNAME add an entry card as follows:

        ENTRY SQLDSIZ
     

     This allows the field containing the size information for the SQLDSECT area to be accessed externally.

  2. The mainline module can now access the size information using the following sequence:

     For DB2 Server for VM

        L  regy,=V(SQLDSIZ)        GET POINTER TO FIELD CONTAINING SIZE
        LA 0,7(0,0)                ROUND UP FOR DOUBLEWORDS
        A  0,0(,regy)              SET LENGTH + 7
        SRL 0,3                    CONVERT BYTES TO DOUBLEWORDS
        CMSSTOR OBTAIN,DWORDS=(0)  GET STORAGE
        LR regy,1                  SAVE POINTER TO SQLDSECT
       (Zero the SQLDSECT area.)
     

     For DB2 Server for VSE

       L  regy,=V(SQLDSIZ)  GET POINTER TO FIELD CONTAINING SIZE
       L  0,0(,regy)        SET LENGTH
       GETVIS ADDRESS=(1),LENGTH=(0)
       LR regy,1            SAVE POINTER TO SQLDSECT
       (Zero the SQLDSECT area.)
     

  3. When the mainline module calls TESTNAME, it should pass the pointer to the SQLDSECT. Assuming that regy still contains the pointer, TESTNAME simply issues the appropriate USING statement as follows:

        TESTNAME CSECT
                 STM   14,12,12(13)
                 BALR  regx,0
                 USING SQLDSECT,regy
                    .
                    .
     

     Depending on how many times TESTNAME is invoked, the above could be an important performance consideration. Using the technique reduces the path length because you only need to get, clear, and free storage once. Further, the cleared SQLDSECT area serves as a "first pass" flag for the batch/ICCF and CMS resource adapters. Thus, by letting the mainline module initialize the SQLDSECT area only once, you further avoid significant resource adapter "first pass" processing.

• Provide only one SQLDSECT area.

  If you structure an application so that the mainline module invokes several modules that each contain SQL commands, you need to provide only one SQLDSECT area. The area that you provide must be the largest SQLDSECT area. For example, suppose the mainline module invokes MODA and MODB, each of which contains SQL commands, but which have different SQLDSECT area requirements. The mainline module must satisfy the larger of the two requirements.

  By inserting the following into MODA and MODB, you could allow the mainline module to calculate the SQLDSECT area requirement:

     INTO MODA:                       INTO MODB:
   
   
     MODADSIZ DC A(SQLDSIZ)           MODBDSIZ DC A(SQLDSIZ)
              ENTRY MODADSIZ                   ENTRY MODBDSIZ
               .                                .
               .                                .
  

  The mainline module could reference the above entries and provide for the maximum SQLDSECT area. The following example shows how the mainline module could determine the requirement of MODA:

     L  regy,=V(MODADSIZ)  GET POINTER TO POINTER FIELD
     L  regy,0(,regy)      GET POINTER TO FIELD CONTAINING SIZE
     L  0,0(,regy)         SET LENGTH.
  

  The same technique could be used to access the SQLDSIZ of MODB. Given the two SQLDSIZ values, the mainline module should provide for a SQLDSECT area equal in size to the greater SQLDSIZ value.

  By using only one SQLDSECT area for your application, you reduce the storage requirement and minimize the first pass processing.