gtpa3m0m | Application Requester User's Guide |
When writing TPFAR application programs in C or assembler, you should keep the following SQL considerations in mind.
TPFAR requires that you use at least 8 numeric characters when specifying a time field. If a value of 5 to 7 characters is specified, truncation occurs, and SQLWARN is set in the structured query language communications area (SQLCA). The SQLCA contains information about the execution of SQL commands. If a value of 0 to 4 characters is specified, an SQLCODE and SQLSTATE is set indicating an error. SQLSTATE is a system-independent SQL return code field for the outcome of the last executed SQL command. SQLCODE is a system-dependent SQL return code.
There are different LU 6.2 request unit (RU) (also known as datastream structure within DRDA) size requirements depending on the flow of data. For the outbound flow (information passed to DB2), TPF/APPC has a MAXRU of 3840 bytes.
When calculating the size of an RU, the following overhead needs to be taken into account. For each command, a 10-byte base overhead is needed, as well as a 3-byte per column overhead, and a 1-byte per null field overhead for the indicator variable. To better understand this, look at Figure 13 and Table 1. To calculate the size of the outbound data, add up the different column sizes: 17 + 8 + 1 + 4 + 5 + 12 + 2 + 26= 75. Added to this is a 10-byte base overhead, plus 3 bytes for each column (3 × 8 = 24), plus 1 byte for each null column (4). This means that 113 total bytes are needed. Figure 14 illustrates this calculation.
Figure 14. Example of Calculating RU Size
LAST_NAME 17 FIRST_NAME 8 MIDDLE_INITIAL 1 COUNTRY_CODE 4 CITY_CODE 5 PHONE_NUMBER 12 EMPLOYEE_NUMBER 2 TIME_STAMP + 26 ------ Total number of bytes needed for the data: 75 10 bytes base overhead 10 8 columns times 3 bytes per column: 24 4 nullable columns + 4 ------ Total bytes needed for this RU: 113
When receiving data from DB2, the same size calculations described above can be used to figure out the size of the data returned. The one case that deviates from this is when a cursor is used.
If you opened a cursor and issued an SQL FETCH command, as long as block fetch is being used, the application server (AS) sends back the data blocked into the query block size (QRYBLKSZ) that is used by TPFAR.5 The QRYBLKSZ value used by TPFAR is 3800 bytes. Therefore, the first PIU returned from the AS returns 33 rows, if there were 33 rows in the answer set (3800 ÷ 113 = 33). If the answer set is less than 33 rows, the entire answer set is returned in the first PIU.
To force a block fetch to be used by DB2, you can open a cursor with the FOR FETCH ONLY option. No subsequent SQL commands can issue an UPDATE or DELETE WHERE CURRENT OF for this cursor. In addition, DB2 may decide to use block fetch even if the FOR FETCH ONLY option is not specified, depending upon the other SQL commands that are working on this same cursor. See the IBM DATABASE 2 Application Programming and SQL Guide for more detailed information on block fetch.
TPFAR keeps track of all the rows in the query block and returns only the first row to the application. Subsequent fetches for rows made by the application can then be accomplished with no further calls to the AS; instead, only local processing is performed by TPFAR. This greatly cuts down on the response time. In addition, while TPFAR and the application are working with this first set of data, the AS can be working on the next set to return; this too reduces response time.
See the Distributed Data Management Architecture Reference for more information about how to calculate how much storage TPFAR uses.
The maximum number of cursors that an application program can have open at one time is 10. Over the lifetime of the application there can be as many opened cursors as desired, but at any one time, there can only be 10 open cursors.
When an SQL call returns to the application, its protect key is reset to application protect key 1. Therefore, any required protect keys have to be reestablished on return from the SQL call.
When writing a TPFAR application in assembler, the TPFAR system code always returns to the application in the mode that the application call was made, either 24-bit or 31-bit mode.
When writing a TPFAR application in assembler, no application registers are saved over the SQL call. Therefore, the application must save all the application registers needed by the TPFAR assembler application after the SQL call.
Dynamic SQL is supported by the TPFAR feature with the PREPARE, EXECUTE, EXECUTE IMMEDIATE, and DESCRIBE verbs. See SQL Commands Supported by TPFAR for a list of the SQL commands that are supported and not supported by the TPF system.
A collection identifier specifies a group of packages. It is used in SQL applications to provide additional detail for the package identifier. See Preparing an Application for more information about packages.
Between application SQL calls, TPFAR holds on to a number of storage areas for its own use. During this time, these areas may need to be freed up. For instance, if the application is returning the data to a terminal, quite a bit of time could pass before the terminal operator asks for more data. The DBSDC and DBSAC macros or functions can be used to file the storage areas out to DASD using TPF 4K short term pool. When the terminal asks for more data, the file records containing the TPFAR storage areas can be retrieved from DASD and reattached to the user's ECB in order for more SQL commands to be issued. For more information about the holding and release of malloc blocks, see the information on heap storage in the TPF Main Supervisor Reference.
You need to be aware of the effect these macros have on the TPF and DB2 systems. Use attach (DBSAC) relatively quickly after the detach (DBSDC) because:
See the DBSAC and DBSDC macros in TPF General Macros for more information about the use of these macros.
Synchronizing the data is very important. The application program is responsible for synchronizing the updates on TPF with those on the remote RDB. The synchronizing is not done using LU 6.2 Sync_Level = CONFIRM or SYNC_PT.
An application program must consider the following when attempting to synchronize data on DB2 and TPF:
If a zero SQLSTATE is not returned on the SQL COMMIT command, the changes made have not been reflected in the DB2 database, and the database looks the same as it did before the changes, even though the change itself may have had a zero SQLSTATE returned. For example, an SQL INSERT command may complete with a zero SQLSTATE, but if the SQL COMMIT command completes with a nonzero SQLSTATE, the change will not be reflected in the database.
Another example would be the case where the SQL COMMIT command makes it through DB2, but on the way back through VTAM, the connection between TPF and VTAM goes down and the response is lost. The data is out on DB2, but the TPF application does not receive the confirmation. One method of addressing this situation is to create another table to hold log records. Before the application program issues an SQL COMMIT command, an SQL INSERT command is issued to the table to log the progress of the application's processing. The same scenario occurs in assembler language; see Using Assembler Language with TPFAR for the assembler example.
If the SQL COMMIT command fails, and the connection can be reestablished, the log table can be interrogated to determine the last committed change by DB2. The application can then start from this point.
When writing a TPFAR program in C, the header file tpfarapi.h must be included in every C program with SQL instructions. This header file contains the linkage for the SQL calls. For additional information on tpfarapi.h, see the TPF C/C++ Language Support User's Guide.