gtpa3m10Application Requester User's Guide

Assembler Program QXRL

Segment QXRL does the SQL work of the assembler package. It takes as input the parameters that were parsed in the QXRK and does the requested inserts.

The commit log is used to indicate how far the program has gone, if a problem occurs. By inserting this record into the LOG_DRIVER table, you can be sure that, if the next issued SQL COMMIT command works, all the inserts done up to that point have been placed in the tables. For example, you want to insert 10,000 records into the table. You would issue an SQL COMMIT command at a given interval to prevent the entire table from locking for the whole duration. If, after inserting 5,000 records, a problem occurred on TPF or the remote AS or the link between the two, when the problem was resolved, you would restart the program from that point, rather than from the beginning. By looking at the LOG_DRIVER table, you would be able to tell what record was last committed, and you could then restart your program from this point. Because this program is just an example, the recovery logic is not included but must be considered when writing this type of application.

This program issues a find and wait for the number of times that was asked for in the num_find parameter. Because this program is only locating a randomly chosen record (RRT record 0), actual performance can differ.

Figure 29. TPF Program to Insert Multiple Records into a Table

         PRINT NOGEN
**********************************************************************
*         THIS PRODUCT CONTAINS "RESTRICTED MATERIALS OF IBM "
*         COPYRIGHT = 5748-T13 (C) COPYRIGHT IBM CORP 1979,1989
*             LICENSED MATERIAL - PROGRAM PROPERTY OF IBM
*             REFER TO COPYRIGHT INSTRUCTIONS FORM NUMBER G120-2083
**********************************************************************
         BEGIN NAME=QXRL,VERSION=ZZ,IBM=YES
*
***************************************************************
*                                                             *
* MODULE NAME..... QXRL                                       *
* RELATED MODULE.. None                                       *
* DOCUMENT NAME... None                                       *
* DESCRIPTION..... Issues inserts into a remote database      *
* LEVEL........... VERSION 1  MODIFICATION 0                  *
*                                                             *
* FUNCTION... This driver issues inserts into a remote        *
*             database.  Depending on the input values, this  *
*             could be used to simulate a real environment.   *
*                                                             *
* MODULE ATTRIBUTES..                                         *
*   TYPE.......... 'E' (ECB CONTROLLED)                       *
*                                                             *
* ECB-CONVENTIONS.......NONE USED                             *
*                                                             *
***************************************************************
* INTERFACE REQUIREMENTS:                                     *
*                                                             *
* DEPENDENCIES... This will only work with the TPFAR function *
* RESTRICTIONS... NONE                                        *
*                                                             *
*  ECB          * INPUT..              * OUTPUT..             *
* *************************************************************
* WORK AREA.... *                      *                      *
* DATA LEVELS.. * D1, D2, and DF free  * D1, D2, and DF free  *
* REGISTERS.... * R6 POINT TO INPUT    * R6 = 0 - GOOD RETURN *
*                 PARAMETER            * R6 != 0 - BAD RETURN *
***************************************************************
*                                                             *
* EXTERNAL-REFERENCES..                                       *
*      ROUTINES....  NONE                                     *
*      DATA AREAS..  NONE                                     *
*                                                             *
* ACRONYMS...                                                 *
*                                                             *
* EXIT-NORMAL.. R6 = 0                                        *
*     -ERROR... R6 != 0                                       *
*                                                             *
* INTERFACE REGISTER R6 -                                     *
*                                                             *
*    R6 MUST POINT TO A AREA THAT LOOKS LIKE THE FOLLOWING:   *
*                                                             *
*  F    - The number of inserts this call is to do            *
*  F    - The number of find and waits to be issued           *
*         before an insert.                                   *
*  F    - The number of inserts to do before a log record is  *
*         inserted into the log table and a commit issued     *
*  CL8  - A 8 byte token used to identify different inserts   *
*         into the table.  This is included on both the       *
*         inserts and the log records.                        *
*  CL16 - A 16 byte RDBNAME used to connect to.               *
*                                                             *
***************************************************************
*                                                             *
*  Pseudo Code                                                *
*                                                             *
* 1. Issue the SQL WHENEVER command for SQLERROR and          *
*    SQLWARING to go to the error handler on any bad return.  *
* 2. Set up addressability to the SQL area.                   *
* 3. Set up addressability to the save area and save          *
*    the calling programs R0 through R7.                      *
* 4. Set up addressability to the SQLCA area.                 *
* 5. Save the ECB address and the CPUID in the save area.     *
*      (These are needed in a local variable for SQL)         *
* 6. Issue the SQL CONNECT to the RDB name passed in the      *
*    parameter area.                                          *
* 7. Clear the total number of inserts completed.             *
* 8. Set up the total number of inserts to do from the        *
*    parameter area.                                          *
* 9. While the total number of inserts to do is > 0           *
*    A. Set the number of inserts completed since the last    *
*       commit to 0.                                          *
*    B. While the total number of inserts to do is > 0 and    *
*       (Number of inserts before logging is 0 or             *
*        the number of inserts completed since the last       *
*        commit is < the number of inserts to do before       *
*        logging)                                             *
*       1. Set up the number of find and waits before         *
*          an insert from the parameter area.                 *
*       2. While the number of find and waits before an insert*
*          is > 0                                             *
*          a. Find RRT record 0 on level F                    *
*          b. Release the record on level F                   *
*          c. Decrement the number of find and waits before   *
*             an insert.                                      *
*       3. Issue the SQL INSERT to insert a record into the   *
*          INSERT_DRIVER table.                               *
*       4. Decrement the total number of inserts to do.       *
*       5. Increment the number of inserts completed since    *
*          the last commit.                                   *
*    C. If the number of inserts before logging is NOT 0      *
*       1. Issue the SQL INSERT to insert a record into the   *
*          LOG_DRIVER table.                                  *
*    D. Issue the SQL COMMIT command to commit all inserts.   *
* 10.Restore the calling programs R6 and R7.                  *
* 11.Set R6 to 0 to indicate that this is a good return.      *
* 12.Release the Blocks used for the SQL area and the         *
*    save/SQLCA area.                                         *
* 13.Return to the calling application.                       *
*                                                             *
* On any error from a SQL call, the following will be done:   *
*                                                             *
* 1. Issue the SQL WHENEVER command for SQLERROR and          *
*    SQLWARING to continue on any bad return.  This will      *
*    stop an infinite loop if the SQL ROLLBACK we are         *
*    about to issue fails.                                    *
* 2. Issue a SQL ROLLBACK command to rollback any work that   *
*    has been completed.                                      *
* 3. Restore the calling programs R6 and R7.                  *
* 4. Set R6 to 4 to indicate that this is a error return.     *
* 5. Release the Blocks used for the SQL area and the         *
*    save/SQLCA area.                                         *
* 6. Return to the calling application.                       *
*                                                             *
***************************************************************
         EJECT
***********************************************************************
* If any error occurs, go to the bad return routine to issue a        *
* rollback.                                                           *
***********************************************************************
         EXEC SQL WHENEVER SQLERROR GO TO QXRL_BAD
         EXEC SQL WHENEVER SQLWARNING GO TO QXRL_BAD
         SPACE 1
***********************************************************************
* Get and set up a block for the SQL area                             *
***********************************************************************
         GETCC     D1,L4             Get a 4k block on level 1.
         L         R2,CE1CR1         Set up addressability.
         LA        R3,4095           Load the length of the block
         XR        R5,R5             Clear length so the MVCL will
*                                    clear storage.
         MVCL      R2,R4             Clear the work block.
         L         R2,CE1CR1         Re-establish addressability.
         USING     SQLDSECT,R2
         SPACE 1
***********************************************************************
* Get and set up a block for the SQLCA and a save area.  The save     *
* area will be the first 100 bytes.                                   *
***********************************************************************
         GETCC     D2,L4             Get a 4K block on level 2.
         USING     SAVEAREA,R1
         L         R1,CE1CR2         Addressability to the block.
         LA        R3,100(,R1)       Bump past the save area.
         STM       R0,R7,REG_SAVE1   Save the registers
         ST        R9,ECB_ADDR       Put the ECB address in the save
*                                    area.
         MVC       CPU_ID,CE1CPD     Move the CPUID into the save area
         USING     DSQLCA,R3         Access to the SQLCA
         USING     PARAM,R6          Access to the parameter area
         SPACE 1
***********************************************************************
* Connect to the remote database.  The RDBNAME is passed as a         *
* parameter.                                                          *
***********************************************************************
         STM       R0,R7,REG_SAVE    Save registers over SQL call
         EXEC SQL CONNECT TO :RDBNAME
         L         R1,CE1CR2         Reload save area
         LM        R0,R7,REG_SAVE    Reload registers
         SPACE 1
***********************************************************************
* Set up R4 for the total number of inserts completed.                *
***********************************************************************
         SPACE 1
         XC        TOT_COMPLETE,TOT_COMPLETE  None completed so far.
         L         R4,NUM_INSERT     Number of inserts to do
***********************************************************************
* Do while # of inserts is > 0                                        *
***********************************************************************
QXRLWH1  DS    0H
         LTR   R4,R4                 Are there more inserts to do?
         BZ    QXRLWH1D              No all finished.
***********************************************************************
* R0 will contain the current number of inserts completed since the   *
* last commit.  So far we haven't done any.                           *
***********************************************************************
         SR        R7,R7             No inserts completed yet.
***********************************************************************
* Do while # of inserts is > 0 and                                    *
*          (Number to log = 0 or # of inserts done < number to log)   *
***********************************************************************
QXRLWH2  DS        0H
         LTR       R4,R4             Number of inserts left to do
         BZ        QXRLWH2D          None left, log if necessary
         OC        NUM_TO_LOG,NUM_TO_LOG  Are we logging?
         BZ        QXRLWH2C          No, keep inserting.
         C         R7,NUM_TO_LOG     Numb. of inserts done since last
*                                    commit < number to log?
         BNL       QXRLWH2D          Yes, log and commit.
QXRLWH2C DS        0H
***********************************************************************
* While # of finds is > 0, issues the find and waits.                 *
*   This code will issue a find and wait for the number of times      *
*   that was passed in the parameter of the RRT ordinal # 0 record.   *
***********************************************************************
         L         R5,NUM_FINDS      Number of finds to do
         LTR       R5,R5             Are there any?
         BZ        QXRLWH3D          No, go do the insert.
         STM       R0,R7,REG_SAVE    Save registers over find code
         CRUSA     S0=F              Insure level F is clear
QXRLWH3  DS        0H
         SR        R0,R0             Set ordinal number to zero
         LA        R6,=CL8'#RRTRI'   Record type of RRT
         LA        R7,CE1FAF         Location for storage of addr.
         ENTRC     FACS              Get file addr of RRT.
         LTR       R0,R0             Has an error been detected?
         BZ        QXRLFERR          Yes, process the error.
         XC        CE1FAF,CE1FAF     Clear RCC and check field
         LH        R2,QXRLRRTI       Set up record id for find
         STH       R2,CE1FAF         in CE1FAF.
         FINWC     DF,QXRLFERR       Initiate record retrieval
*                                    Handle error if it occurs
         RELCC     DF                Just wanted to do the find
*                                    so we can release the block.
         BCT       R5,QXRLWH3        If there are more finds to do
*                                    then continue.
         L         R1,CE1CR2         Restore base of save area.
         LM        R0,R7,REG_SAVE    Reload registers.
***********************************************************************
* Save the number left and the number done into the host variables    *
* for the SQL INSERT command, and then issue the command.             *
***********************************************************************
QXRLWH3D DS        0H
         ST        R4,NUM_LEFT       The number left and the number
         ST        R7,NUM_DONE       done must be saved in host
*                                    variables for use by SQL.
         STM       R0,R7,REG_SAVE    Save regs over SQL call.
         EXEC SQL INSERT INTO INSERT_DRIVER                            X
                (IDENTIFIER,TIME_STAMP,ECB_ADDR,CPU_ID,                X
                 NUM_LEFT,NUM_DONE,NUM_INSERT,NUM_FINDS,NUM_TO_LOG)    X
                  VALUES                                               X
                (:IDENTIFIER,CURRENT TIMESTAMP,:ECB_ADDR,:CPU_ID,      X
                 :NUM_LEFT,:NUM_DONE,:NUM_INSERT,:NUM_FINDS,           X
                 :NUM_TO_LOG)
         L         R1,CE1CR2         Reload base of save area.
         LM        R0,R7,REG_SAVE    Restore regs.
***********************************************************************
* Decrement the number of inserts left to do.                         *
* Remember that the SQL WHENEVER statements at the top will cause     *
* Control to be switched to the error routine whenever an error occurs*
***********************************************************************
         BCTR      R4,0              Another insert completed
*                                    successfully.
***********************************************************************
* Increment the number of inserts that have completed                 *
***********************************************************************
         LA        R7,1(,R7)         Increment # done
***********************************************************************
* Increment the total number of inserts that have completed           *
***********************************************************************
         L         R5,TOT_COMPLETE   Total number of inserts completed.
         LA        R5,1(,R5)         Add one.
         ST        R5,TOT_COMPLETE   Save this value.
         B         QXRLWH2           GO AND CHECK FOR MORE INSERTS.
QXRLWH2D DS        0H
***********************************************************************
* We have left the inner while loop.  This means that it is either    *
* time to log or we are at the end of our inserts.  We must now check *
* to see if logging is to occur.                                      *
***********************************************************************
         OC        NUM_TO_LOG,NUM_TO_LOG  Are we logging?
         BZ        QXLRIFN           No, go commit.
         STM       R0,R7,REG_SAVE    Save regs over SQL call.
         EXEC SQL INSERT INTO LOG_DRIVER                               X
               (IDENTIFIER,TIME_STAMP,ECB_ADDR,CPU_ID,NUM_DONE) VALUES X
               (:IDENTIFIER,CURRENT TIMESTAMP,:ECB_ADDR,:CPU_ID,       X
               :TOT_COMPLETE)
         L         R1,CE1CR2         Restore save area base.
         LM        R0,R7,REG_SAVE    Restore registers.
QXLRIFN  DS        0H
***********************************************************************
* Commit the work completed                                           *
***********************************************************************
         STM       R0,R7,REG_SAVE    Save regs over SQL call.
         EXEC SQL COMMIT
         L         R1,CE1CR2         Restore save area base.
         LM        R0,R7,REG_SAVE    Restore registers.
         B         QXRLWH1           Go back and check for more to do.
**********************************************************************
*  We have now completed the number of inserts asked.  Restore the   *
*  callers registers, setting R6 to 0 to indicate that this          *
*  is a good return.                                                 *
**********************************************************************
QXRLWH1D DS        0H
         LM        R0,R7,REG_SAVE1   Restore callers registers.
         LA        R6,0              Indicate good return.
QXRLRET  DS        0H
         RELCC     D1                Release used blocks on levels
         RELCC     D2                D1 and D2.
         BACKC
         EJECT
**********************************************************************
* An error was returned on a SQL command.  Whenever this occurs,     *
* we want to rollback the work completed so far.  Before doing this, *
* we have to make sure that if another error occurs on the rollback  *
* we have set the SQL WHENEVER to continue so we do not get into an  *
* infinite loop.                                                     *
* On entry to this loop, the registers need to be restored from the  *
* save area, as they were saved there before the SQL call.           *
**********************************************************************
QXRLFERR DS        0H
         L         R1,CE1CR2         Restore save area base.
         LM        R0,R7,REG_SAVE    Restore registers.
QXRL_BAD DS        0H
         EXEC SQL WHENEVER SQLERROR CONTINUE
         EXEC SQL WHENEVER SQLWARNING CONTINUE
         EXEC SQL ROLLBACK
         L         R1,CE1CR2        Restore save area base.
         LM        R0,R7,REG_SAVE1  Restore callers registers.
         LA        R6,4             Indicate error.
         B         QXRLRET          Return to caller.
         EJECT
**********************************************************************
* The SQLCA dsect must be included in all assembler programs with SQL*
* The actual storage is on data level 1.  This is used just to       *
* include the dsect.                                                 *
**********************************************************************
DSQLCA   DSECT
         EXEC SQL INCLUDE SQLCA
$IS$     CSECT
         SPACE 5
**********************************************************************
* The PARAM dsect maps to the parameter area passed by the calling   *
* application.                                                       *
**********************************************************************
PARAM    DSECT
NUM_INSERT    DS   F                Number of inserts to do.
NUM_FINDS     DS   F                Number of finds before insert.
NUM_TO_LOG    DS   F                Number of inserts before logging.
IDENTIFIER    DS   CL8              Identifier inserted into rows.
RDBNAME       DS   CL16             RDBNAME to connect to.
$IS$     CSECT
         SPACE 2
**********************************************************************
* The SAVEAREA dsect maps to the save area on level 2.               *
**********************************************************************
SAVEAREA DSECT
REG_SAVE1     DS   8F               Save area for users R0 through R7.
REG_SAVE      DS   8F               Reg save area over SQL calls.
NUM_LEFT      DS   F                # of inserts left to do.
NUM_DONE      DS   F                # of inserts completed since last
*                                   commit.
ECB_ADDR      DS   F                Address of ECB (used for insert)
CPU_ID        DS   CL1              CPUID (Used for inserts)
TOT_COMPLETE  DS   F                Total number inserts completed.
$IS$     CSECT
         SPACE 5
QXRLRRTI      DC   CL2'S2'          Record ID of RRT for finds.
         LTORG ,
         FINIS ,
         END   ,