Loading Tables Faster: The MODIFY Fastload Facility

In this section:

The 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.

For Oracle and DB2, you should issue the INSERTSIZE command in conjunction with Fastload to take advantage of blocked inserts. For more information, see DB2 and 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 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.
Top of page
x
DB2 and Oracle Array Blocking for INSERT Requests

How to:

The Adapters for DB2 and Oracle support buffered insertion of rows into 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.
x
Syntax: How to Set INSERTSIZE for DB2 and Oracle
SQL [sqlengine] SET INSERTSIZE n

where:

sqlengine

Indicates the Adapter. Valid values are DB2 and SQLORA. 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:
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)

Information Builders