ETL command tags

Global command tags

The global command applies to all SQL statements that are defined in the specified .sql file. Global commands are always prefixed with --G--.

There are two supported global commands: CommitStatement and RunStats.

CommitStatement

By default, the ETL does not issue commits after each SQL statement, but only after all SQL statements in a particular batch file have been run. The CommitStatement global command tag can be used to force the ETL to issue a commit statement after each SQL statement is run. Note that some local commands force the driver to perform commits regardless of this setting.

The syntax of this command is as follows:

<CommitStatement>commit_option</CommitStatement>

Where commit_option is either true or false. For example:

--G--  <CommitStatement>true</CommitStatement>

RunStats

The ETL processing parameters can run the RunStats DB2 command as part of a global command. IBM recommends, however, that the user place the proRunStats command in the DB2 batch script as a statement instead of using the global command tag. Placing the proRunStats command in the DB2 batch script allows the user to control the exact syntax of the pro RunStats command as well as when the proRunStats command runs within this file.

For example, the following command informs ETL to compose a proRunStats command against the table_name_r target table and then run the pro RunStats command either before or after running other SQL statements in the script file.

--G--<RunStats>
--G--<TargetTable>table_name_r</TargetTable>
--G--<RunPosition>position </RunPosition>
--G--</RunStats>

Where table_name_r indicates the name of the table to which the RunStats command is targeted, for example, iwh.users_r, and where position carries the value of before or after.

 

Local commands

Local command tags affect only the first statement following the local tag. The SQL statement always ends with a semicolon ( ; ) delimiter character. If you decide to comment out a locally tagged SQL statement, you should also comment out that statement's local command tags or delete the tags altogether. Otherwise those tags are applied to the next uncommented SQL statement and can lead to unexpected results.

Local command lines are prefixed with --L--.

You can perform these types of operations using the following local command tags:

where CommitRows, UpdateSource, LoadOption, LoadParam, PrimaryKeys, Updatetype, SelectPrimaryKeys are explained in the information later in this section.

See Modes for combination SQL statements in ETL command tags for examples of CursorInsert, CursorUpdate, LoadInsert, and LoadUpdate operations.

CommitRows

CommitRows command is applied to all INSERT and UPDATE cases using the cursor option.

<!ELEMENT CursorInsert (CommitRows, UpdateSource*, LoadOption*, LoadParam*)>
or: 
<!ELEMENT CursorUpdate (CommitRows, PrimaryKeys, UpdateType, SelectPrimaryKeys*, UpdateCondition*)>
--L-- <CommitRows>num_of_rows</CommitRows>
Where num_of_rows is the number of rows that should be processed before the incremental commit is performed.

UpdateSource

The UpdateSource tag is a special tag that is used to update the source information with the CursorInsert tag. It contains the tags of SourceTable, SourceKeys, and optional tags of SourceSet and SourceCondition.

<!ELEMENT CursorInsert (CommitRows, UpdateSource*, LoadOption*, LoadParam*)>
<!ELEMENT UpdateSource (SourceTable, SourceKeys, TargetTable*, TargetKeys*)>
--L-- <UpdateSource></UpdateSource>

SourceTable

Use the SourceTable command in the UpdateSource tag for the CursorInsert statement as part of the UpdateSource tag. 
<!ELEMENT CursorInsert (CommitRows, UpdateSource*, LoadOption*, LoadParam*)>
<!ELEMENT UpdateSource (SourceTable, SourceKeys, TargetTable*, TargetKeys*)>
--L--  <SourceTable>src_table_name</SourceTable>
Where src_table_name is the name of the table where the source information is to be updated.

Only one source table can be specified.

SourceKeys

Use the SourceKeys command in CursorInsert for the INSERT SQL statement as part of the UpdateSource tag.

<!ELEMENT CursorInsert (CommitRows, UpdateSource*, LoadOption*, LoadParam*)>
<!ELEMENT UpdateSource (SourceTable, SourceKeys, TargetTable*, TargetKeys*)>
--L--  <SourceKeys>srckey1, srckey2, ...... , srckeym</SourceKeys>

Where srckey1, srckey2, ...... , srckeym are a list of comma-delimited column names that can uniquely identify the rows in the source table that is to be updated.

TargetTable

Use the TargetTable command in the UpdateSource tag. The TargetTable command is a convenient method used to specify the target table name to the ETL Driver. The table name should be of the format SCHEMA.TABLENAME. This parameter is optional, as ETL can automatically determine the target table name from the INSERT statement. 
<ELEMENT CursorInsert (CommitRows, UpdateSource*, LoadOption*, LoadParam*)>
<ELEMENT UpdateSource (SourceTable, SourceKeys, TargetTable*, TargetKeys*)>
--L--<TargetTable>tgt_table_name<TargetTable>

TargetKeys

Use the TargetKeys command in the UpdateSource tag. The TargetKeys command is necessary when the column names specified in the SourceKeys command are not the same as the column names in the target table. If this command is used, there must be one column specified in the TargetKeys command per column specified in the SourceKeys command.

<ELEMENT CursorInsert (CommitRows,UpdateSource*,LoadOption*,LoadParam*)>
<ELEMENT UpdateSource (SourceTable,SourceKeys, TargetTable*, TargetKeys*)>
-L--<TargetKeys>tgtkey1,tgtkey2,......,tgtkeym<TargetKeys>

LoadOption

The LoadOption command is shared between the CursorInsert tag and the LoadInsert tag. When included in a CursorInsert tag, the LoadOption is only used when ETL determines it can use the Export or Load option instead of the CursorInsert. This scenario occurs when the target table does not already have any rows. After that, the behavior is the same whether it is included in a LoadInsert or CursorInsert tag.

<ELEMENT LoadInsert (LoadOption,LoadParam*)>
<ELEMENT CursorInsert (CommitRows,UpdateSource*,LoadOption*,LoadParam*)>
--L--<LoadOption>load_option</LoadOption>

Where load_option can only be one of the following values:

import

The SQL statement breaks up into a SELECT SQL statement and an INSERT SQL statement. The SELECT SQL statement part is run using the export command, which exports the selected result into an external file with the delimited del format.

load

The INSERT SQL statement part is run using the import or load command, based on the specified option.

LoadParam

Exact parameters vary based on whether the import or load command is used in the insert.

<ELEMENT LoadInsert (LoadOption,LoadParam*)>
--L--<LoadParam>load_parameters<LoadParam>

Where load_parameters are parameters set by using the modified by command in the import or load commands.

Primary Keys

Use the PrimaryKeys command for UPDATE SQL statements. Omit this command if the list of the keys are the same as those listed in the PrimaryKey list.

<!ELEMENT CursorUpdate (CommitRows, PrimaryKeys, UpdateType,
                           SelectPrimaryKeys*, UpdateCondition*)>
<Primary Keys>key1, key2, ......, keym</Primary Keys>

Where key1, key2, ......, keym are a list of comma-delimited columns names that can uniquely identify the rows in the table to be updated.

SelectPrimaryKeys

Use the SelectPrimaryKeys command for UPDATE SQL statements. Omit this command if the list of the keys are the same as those listed in the PrimaryKey list.

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

--L--  <SelectPrimaryKeys>skey1, skey2,...,skeym</SelectPrimaryKeys>

Where skey1, skey2,......,skeym are a list of comma-delimited column names that can unique identify the rows in the table where SELECT is run.

UpdateType

The UpdateType command is for UPDATE SQL statements only. There are two types of updates: simple or composite.

<!ELEMENT CursorUpdate (CommitRows, PrimaryKeys, UpdateType,
                           SelectPrimaryKeys*, UpdateCondition*)>
--L--  <UpdateType>type_of_update_sql</UpdateType>

Where type_of_update_sql can only be one of the following types:

simple

Indicates that the update is done on its own table without a SELECT statement. For example: 
update table_name set (column_a,column_b) =
                             ( 'value_a','value_b')
       where column_c='xxx' and column_d is not null;

composite

Indicates that the update is done with a selection from one table and the update is done on another table. For example: 
update table_name tb set (column_a, ....,column_n) =
       (select col_a, ....., col_n from table_b_name tb
          where  some_conditions
          group by some_columns
       )
       where
          some_other_conditions

UpdateCondition

The UpdateCondition command is for UPDATE SQL statements only. Only use this command for special conditions. The conditions listed are appended to the regular conditions.

<!ELEMENT CursorUpdate (CommitRows, PrimaryKeys, UpdateType,
                           SelectPrimaryKeys*, UpdateCondition*)>
--L--  <UpdateCondition>special_upd_conditions</UpdateCondition>

Where special_upd_conditions are additional conditions that must be specified on the UPDATE SQL statement after the statement is decomposed. The regular equal conditions on the key columns listed in PrimaryKeys are added automatically.

LoadInsert

A LoadInsert tag indicates that ETL should always use the Export/Load or Export/Import option. The preferred method is to tag a query with the CursorInsert tag and let ETL determine whether to use a cursor insert or load method. However, it is still supported to always perform a load. See LoadOption and LoadParam.

Related concepts

Related reference

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