gtpa3m0yApplication Requester User's Guide

Offloading Data from the TPF System

Another type of application using TPFAR is a back end program to move the data from TPF to DB2. A back end program is most appropriately used when many different ECBs are inserting single rows into a DB2 table that does not need to be current with the TPF database. An example of this type of application is an application server table (AS) containing flight reservations used for statistical purposes only. (The example is shown coded in assembler language).

Instead of issuing SQL CONNECT, INSERT, and COMMIT commands every time a request comes in, the application queues the data in a file-chained area. The back end application then issues the SQL CONNECT command, issues any number of SQL INSERT commands, and then issues an SQL COMMIT command. Because an SQL CONNECT and COMMIT command are not issued for each SQL INSERT command, processing is significantly more efficient.

Note:
This type of back end processing can be used only when the data on the remote AS does not need to be current with the TPF data.

Another benefit of offloading is that if the connection to the AS is lost, the TPF applications can continue to queue the messages and, when the connection is brought back, the data can be offloaded.

Setting Up the Application Server

For the assembler examples to work, two tables, INSERT_DRIVER and LOG_DRIVER, must be created on the remote DB2 subsystem. You can do this in the DB2 system through the use of interactive SQL. On MVS, the mechanism used is called SQL Processor Using File Input (SPUFI) application. SPUFI is a way to execute SQL commands from a TSO terminal. See the DB2 Application Programming and SQL Guide for more information on SPUFI. Figure 26 shows the two SQL CREATE TABLE commands needed to create these tables.

Figure 26. SQL CREATE TABLE Commands to Create the INSERT_DRIVER and LOG_DRIVER Tables

     CREATE TABLE TPFNET.INSERT_DRIVER
       (IDENTIFIER    CHAR(8),
        TIME_STAMP    TIMESTAMP NOT NULL,
        ECB_ADDR      INTEGER NOT NULL,
        CPU_ID        CHAR(1),
        NUM_LEFT      INTEGER NOT NULL,
        NUM_DONE      INTEGER,
        NUM_INSERT    INTEGER,
        NUM_FINDS     INTEGER,
        NUM_TO_LOG    INTEGER,
        PRIMARY KEY(ECB_ADDR, NUM_LEFT, TIME_STAMP));
 
 
     CREATE TABLE TPFNET.LOG_DRIVER
       (IDENTIFIER    CHAR(8) NOT NULL,
        TIME_STAMP    TIMESTAMP NOT NULL,
        ECB_ADDR      INTEGER NOT NULL,
        CPU_ID        CHAR(1) NOT NULL,
        NUM_DONE      INTEGER NOT NULL,
        PRIMARY KEY(ECB_ADDR, NUM_DONE, TIME_STAMP));

These table commands have a primary key. Every primary key requires a primary index. These indexes must be created before the table definitions are complete. A primary key identifies a specific column or columns in a table that uniquely define each row in the table. Each row in the table must have a primary key value that is unique and not null. A primary index on a table allows access to a specific row of data without having to read every row in the table each time.

Figure 27 shows an example of the two SQL CREATE INDEX commands that need to be run for the CREATE TABLE definitions to be complete.

Figure 27. Example of SQL CREATE INDEX Commands to Create a Primary Index

     CREATE UNIQUE INDEX TPFNET.INSERT_D_INDEX ON TPFNET.INSERT_DRIVER
       (ECB_ADDR ASC,
        NUM_LEFT DESC,
        TIME_STAMP DESC);
 
     CREATE UNIQUE INDEX TPFNET.LOG_D_INDEX ON TPFNET.LOG_DRIVER
       (ECB_ADDR ASC,
        NUM_DONE DESC,
        TIME_STAMP DESC);