Loading Tables Faster: The MODIFY Fastload Facility

The data adapter Fastload facility increases the speed of loading data into tables with MODIFY. It is especially effective when using FIXFORM to load large volumes of data into a table.

In this section: Oracle Array Blocking for INSERT Requests How to: Set INSERTSIZE for Oracle Example: Sample Oracle Session Using INSERTSIZE

For Oracle, you should issue the INSERTSIZE command in conjunction with Fastload to take advantage of blocked inserts. For more information, see Oracle Array Blocking for INSERT Requests.

Use the following command to invoke the Fastload option from a MODIFY procedure:

SQL SET LOADONLY

For example:

MODIFY FILE table
SQL SET LOADONLY
      .
      .
      .
MATCH key1
    ON NOMATCH INCLUDE
    ON MATCH REJECT
      .
      .
      .
END

A standard MODIFY procedure uses a MATCH command to test for the existence of a single row whose primary key value matches that supplied by the input transaction. When the underlying table is an RDBMS table, the data adapter uses an SQL SELECT statement to perform this test. After examining the return code resulting from the SELECT, FOCUS determines whether or not the row exists and directs MODIFY processing to the appropriate MATCH logic.

Fastload eliminates this SELECT operation. Its sole responsibility is to load rows into the RDBMS. Fastload does so without first determining whether the row already exists within the table. The RDBMS ensures the uniqueness of stored rows using its unique index. However, FOCUS messages about the existence of duplicates display online or go into a LOG file if one exists.

Fastload can only insert (ON NOMATCH INCLUDE) rows into a table. Any attempt to use other MODIFY maintenance functions produces a FOC1404 error message, and the procedure terminates.

Note: In the DB2 for VM Data Adapter, LOADONLY employs the DB2 for VM blocked-insert statement, PUT. A non-zero return code from a PUT operation (such as a unique index violation) may result in failure to input more than one row. DB2 for VM Data Adapter applications using LOADONLY should test FOCERROR after every INCLUDE. If a non-zero code is returned, in all cases, applications should ROLLBACK and terminate the MODIFY procedure.

Oracle Array Blocking for INSERT Requests

The Oracle Data Adapter supports buffered insertion of rows into Oracle tables This technique substantially reduces network traffic and CPU utilization.

High INSERTSIZE values increase the efficiency of requests involving many rows, at the cost of higher virtual storage requirements. A value higher than 100 is not recommended because the increased efficiency it would provide is generally negligible.

Syntax: How to Set INSERTSIZE for Oracle

SQL [SQLORA] SET INSERTSIZE n

where:

SQLORA

Indicates the Oracle Data Adapter. You can omit this value if you previously issued the SET SQLENGINE command.

n

Is the number of rows to be transmitted to the RDBMS at once. Accepted values are 1 to 5000. The default is 1.

Note: The INSERTSIZE parameter is only functional for consecutive executions of INSERT statements that are identical to each other (except for the values to be inserted). No other intervening SQL statements are allowed, including COMMIT WORK. If a statement is issued that in any way (other than the inserted values) differs from the current blocked INSERT statement in effect, the block is immediately transmitted to the RDBMS, even if the buffer is not full. This restriction has several ramifications:

• To use INSERTSIZE in a MODIFY request, you must use the SQL SET LOADONLY command. Without LOADONLY, the data adapter does not insert a row without first issuing a SELECT statement to check that the row does not already exist.

Note:

• To use INSERTSIZE with Direct SQL Passthru, the INSERT statement must be parameterized and use the PREPARE, BIND, and EXECUTE command set within a BEGIN SESSION/END SESSION command pair. In this case, the BIND is not optional. See Direct SQL Passthru, for a discussion of parameterized Passthru.

Example: Sample Oracle Session Using INSERTSIZE

SET SQLENGINE = SQLORA
SQL SET CONNECTION_ATTRIBUTES /USER1,PASS1
SQL SET AUTOCOMMIT ON COMMAND
SQL SET INSERTSIZE 3
SQL BEGIN SESSION
SQL PREPARE ABC FOR INSERT INTO USER1.PAYROLL
  VALUES (:001,:002);
END
 
SQL BIND ABC USING CHAR(5), DECIMAL(11,2);
END
 
SQL EXECUTE ABC USING '11111', 50000; (Row is buffered)
END
 
SQL EXECUTE ABC USING '22222', 65000; (Row is buffered)
END

SQL EXECUTE ABC USING '33333', 30000; (Row is buffered, block transmitted)
END

SQL END SESSION                       (LUW is committed)