gtpa3m0mApplication Requester User's Guide

SQL Considerations

When writing TPFAR application programs in C or assembler, you should keep the following SQL considerations in mind.

Note:
You should have a basic knowledge of the SQL programming language.

Length of Time Field

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.

Request Unit Size Considerations

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.

Number of Cursors

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.

Protect Key

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.

Addressing Mode

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.

Registers

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

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.

Collection Identifiers

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.

TPFAR Working Storage Blocks

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.

Note:
There are also C versions of the DBSAC and DBSDC macros. See TPF C/C++ Language Support User's Guide for information about these functions.

Synchronizing Updates

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:

C Language Header Files

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.


Footnotes:

5
The SQL FETCH command retrieves the next requested row in an answer set table. See the IBM DATABASE 2 SQL Reference for additional information on this command.