gtpa3m10 | Application Requester User's Guide |
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 ,