gtpa3m0y | Application Requester User's Guide |
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.
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.
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);