DB2LOAD Stored Procedure

转自： http://www-01.ibm.com/support/docview.wss?uid=swg21201292     Abstract

You can access the LOAD utility via a stored procedure in DB2 UDB V8.2 for Linux, Unix and Windows.

Content

Overview Starting with DB2 UDB V8.2 IBM is shipping another interface to the LOAD utility. The LOAD stored procedure, SYSPROC.DB2LOAD, is implemented on the server and it allows any application able to connect to DB2 UDB server and execute the SQL CALL statement to run the DB2 LOAD utility. The stored procedure is automatically cataloged in the system tables (SYSCAT.PROCEDURES) at database creation time. One of the input parameters to the stored procedure is the CLP syntax of the load command. A cursor definition must be supplied as an input parameter in order to run a LOAD from CURSOR. The benefits of using DB2LOAD stored procedure to access the utility are: Simpler programmable interface than the db2Load API. Utility is accessible to applications that cannot utilize DB2 UDB administrative APIs but can execute the SQL CALL statement. Stored procedure definition CREATE PROCEDURE SYSPROC.DB2LOAD  (      IN version_number INTEGER,     IN cursor_statement VARCHAR(32672),     IN load_command VARCHAR(32672),     OUT sqlcode INTEGER,     INOUT sqlmessage VARCHAR(2048),     OUT rows_read BIGINT,     OUT rows_skipped BIGINT,     OUT rows_loaded BIGINT,     OUT rows_rejected BIGINT,     OUT rows_deleted BIGINT,     OUT rows_committed BIGINT,     OUT rows_part_read BIGINT,     OUT rows_part_rejected BIGINT,     OUT rows_part_partitioned BIGINT,     INOUT mpp_load_summary VARCHAR(32672) )  SPECIFIC DB2LOAD  LANGUAGE C  PARAMETER STYLE DB2SQL  NOT FENCED  MODIFIES SQL DATA  PROGRAM TYPE MAIN  EXTERNAL NAME 'system_routine~db2load!db2load' Parameter Description Parameter Description version_number Version number of the DB2LOAD stored procedure. Current version number is 1. cursor_statement Declaration of the cursor which is to be used during a LOAD from CURSOR operation. This parameter must be specified when loading from CURSOR via DB2LOAD stored procedure. load_command LOAD command to be executed. sqlcode SQLCODE returned by the LOAD utility. If a nonzero value is returned by the LOAD utility, an SQLCA populated with SQL0462W will be returned by the SQL CALL statement, with the third token indicating either a 'LOAD WARNING' or a 'LOAD ERROR'. sqlmessage SQL message associated with the above LOAD return SQLCODE. rows_read Numbers of rows processed by the LOAD utility, including the MPP specific processing done by the partitioning agent. These values correspond to the contents of db2LoadOut API structure and to a subset of the contents of db2PartLoadOutAPI structures. rows_skipped rows_loaded rows_rejected rows_deleted rows_committed rows_part_read rows_part_rejected rows_part_partitioned mpp_load_summary Abbreviated MPP LOAD summary. Displays a subset of information otherwise contained in thedb2LoadAgentInfo API structure in a character format. Current restrictions CLIENT option is not supported. Hence all file and directory paths specified as a part of the load command have to refer to existing paths on the DB2 server. When called by a DRDA client following a two phase commitprotocol, the stored procedure can not commit data under the transaction manager control. A call to DB2LOAD stored procedure will fail with SQL30090 (Reason Code 2) if any of the following is true: Current transaction has executed any SQL statement that writes a log record. Current transaction has executed an explicit table lock statement. Current transaction has opened an with hold cursor. Current transaction has accessed a federated source. Only SQL types can be used as stored procedure input parameters, hence the amount of information returned to the caller is somewhat limited. Output (numbers of rows) can be truncated since the db2Load API uses 64 bit unsigned integers, but the SP uses SQL type BIGINT which is 64 bit signed. At most 1000 individual sub-agent entries will be displayed in the MPP LOAD summary. Message files are not moved to the client. We strongly recommend a connection be recycled explicitly before invoking the LOAD stored procedure. Invocation examples using the sample database CLP example connect to sample; create table staff2 like staff; call db2load(1,'','load from /fully/qualified/path/staff.del of del replace into staff2',?,'',?,?,?,?,?,?,?,?,?,NULL); call db2load(1,'','load from /fully/qualified/path/staff.asc of asc method l (4 6, 8 20, 22 23, 25 33, 35 36, 39 46, 50 56) messages /fully/qualified/path/staff.400.1 insert into staff2 partitioned db config partitioning_dbpartnums(2) output_dbpartnums(3)',?,'',?,?,?,?,?,?,?,?,?,'');