Modes for combination SQL statements in ETL command tags

Combination SQL statements can be run in either a cursor mode or by using the DB2 export, import, and load utilities, with incremental commits.

Run under cursor mode

You can run SELECT-INSERT and SELECT-UPDATE types of combination SQL statements in cursor mode with incremental commit statements. For the SELECT-INSERT mode, an additional UPDATE of the source table with the key value takes place while the INSERT is being done with the cursor.

CursorInsert

<!ELEMENT CursorInsert (CommitRows, UpdateSource*)>
<!ELEMENT UpdateSource (SourceTable, SourceKeys, SourceSet*, SourceCondition*)>

An example of cursor insert with update source might be:

--L-- <CursorInsert>
--L--   <CommitRows>1000</CommitRows>
--L--   <UpdateSource>
--L--      <SourceTable>iwh.test_r</SourceTable>
--L--      <SourceKeys>p1</SourceKeys>
--L--   </UpdateSource>
--L-- </CursorInsert>
insert into wca.test (p1, a1, a2, a3)
(select
    p1, a1, a2, a3
 from
    iwh.test_r
);

CursorUpdate

<!ELEMENT CursorUpdate (CommitRows, PrimaryKeys, UpdateType, 
                           SelectPrimaryKeys*, UpdateCondition*)>

An example of a composite cursor update where the select primary key has the same column name as the update primary key:

--L--   <CursorUpdate>
--L--      <CommitRows>1000</CommitRows>
--L--      <PrimaryKeys>p1</PrimaryKeys>
--L--      <UpdateType>composite</UpdateType>
--L--   </CursorUpdate>
update wca.test t1 set (a1, a2) =
(select 
   a1,a2 
from
   wca.test2 t2
 where
   t1.p1=t2.p1 and
   t2.p1>2
)
where t1.p1 in (select p1 from wca.test2 where p1>2);

Run under export, import, or load mode

Use the DB2 export, import, and load utilities to move large amounts of data. The ETL pre-processor enables you to run combination SQL statements (such as SELECT-INSERT and SELECT-UPDATE) and to perform incremental commits using these DB2 utilities.

The SQL statement is broken up into a SELECT SQL statement and an INSERT SQL statement. The SELECT SQL statement part is run with the export command, which exports the selected result into an external file. The INSERT SQL part is run by the import or load command, based on the specified option.

Following are example command sets that are defined for the export, import, and load types of operations:

LoadInsert

Insert with export or load (LoadInsert):

<!ELEMENT LoadInsert (LoadOption, LoadParam*)>

An example of a load insert using the load command with options:

--L--  <LoadInsert>
--L--      <LoadOption>load</LoadOption>
--L--      <LoadParam>modified by forcein</LoadParam>
--L--  </LoadInsert>
insert into wca.test2 (p1,a1,a2,a3) 
(select
    p1, a1, a2, a3
 from
    wca.test3
);

LoadUpdate

Update with export or load (LoadUpdate):

<!ELEMENT LoadUpdate (LoadOption, PrimaryKeys, UpdateType, 
                         SelectPrimarysKeys*, UpdateCondition*, LoadParam*)>

An example of a composite load update:

--L--  <LoadUpdate>
--L--      <LoadOption>import</LoadOption>
--L--      <PrimaryKeys>p1</PrimaryKeys>
--L--      <UpdateType>simple</UpdateType>
--L--  </LoadUpdate>
update wca.test2 set (a2,a3)=(100,'t2a3') where p1<>6;

Related concepts

• Extract, Transform and Load (ETL) customization

Related reference

• Supported ETL command tags

(C) Copyright IBM Corporation 1996, 2006. All Rights Reserved.