SQL Set

Overview

The SQLSetWorkEntryExecutor class generates and executes SQL statements from template statements specified in the Code-Block. The template statements have placeholders for source-to-target mappings that are substituted at runtime with appropriate values from the mappings.

Use this executable for source to TableSet target mappings where a Java transformer is not required.

Select a Target Level mapping and Target Types table set, and create an appropriate INSERT INTO … SELECT FROM … template statement for this executable class. Any SQL statement can be templatized, including PL/SQL anonymous blocks. For example,

begin
     ria_dih.truncate_table('{targetTable}');
end;

In this example, the SQL template calls a stored procedure to truncate a table. At runtime, the table name will replace the {targetable} placeholder. See Bindable methods below for a list of valid placeholders.

Parameters

See Common executable features below for details on other variables and syntax that can be used.

Bindable methods

The SQL set-based executable Java class has a list of bindable getter methods that can be linked to an SQL template. The bindable methods are declared within the SQLSetWorkEntryExecutor class. They are used to input values for the relevant mappings and other configuration elements into an SQL template when a statement is generated.

Bindable methods are enclosed in {…} with the get part of the method name dropped, and the first letter changed to lowercase.

A bindable method that has no parameters can be specified with or without (). For example, both {targetTable} and {targetTable()} are valid. Some bindable methods must be specified with an argument. For example, {sourcePKName(0)} would return the first occurrence (index 0) of the primary key column names on the source table or query.

For example, the following INSERT...SELECT SQL statement template for table-to-table mapping uses several bindable methods. During runtime, these embedded bindable methods will be replaced with the relevant values according to the source, target, and column mappings.

INSERT INTO {targetTable}
  ({targetColumns})
SELECT
  {targetMappingDetails}
FROM {source} {sourceId}
{predicate}
{threadingClause}

To generate the complete SQL statement, the example above instructs the DIH tool to invoke the following get methods:

  • getTargetTable() method to get the target table name.
  • getTargetColumns() method to get the column names of the target table.
  • getTargetMappingDetails() method for the mapping expressions as defined on the target.
  • getSource() and getSourceId() methods to get the source from where the mapping is based.
  • getPredicate() method to get any specified predicates for the mapping.
  • getThreadingClause() method to insert the condition that will select the source records for each thread in a multi-threaded task.

The following is the complete list of available bindable methods within the SQLSetWorkEntryExecutor class:

Bindable Method Injected Value
targetTable() Table name of the target mapping.
targetColumns() Comma-delimited list of the target table’s column names.
targetColumns(String alias)

Comma-delimited list of the target table’s column names with each column name prefixed by the supplied alias argument and a period.

An example of this is in the executable SQL_MERGE_K, which is a MERGE statement that requires columns to be referred to by the alias.
targetMappingDetails() Mapping expressions for each mapping sequence.
targetPrimaryKeyColumn0()

Name of the first column of the target table’s primary key.

Note: In the future, this will be replaced with the targetPKName (Integer index) method.
targetPKName(Integer index)

Primary key column for the specified index of the target table. Index 0 will return the same name as targetPrimaryKeyColumn0 above.

An example of this is in the executable SQL_DELETE_RANGE_MANUAL.
targetPKLength(Integer index)

Column length of the target table’s primary key column for the specified index.

An example of this is in the executable SQL_DELETE_RANGE_MANUAL.
source()

Id or query of the source table, depending on the type of source. A source query will be enclosed in (…) so that it can be used in the FROM part of the statement template.

An example of this is in the executable SQL_COPY.
sourceId()

Source Id. This is used to give the source an alias.

An example of this is in the executable SQL_COPY.
sourcePrimaryKeyColumn_0()

Name of the first column of the
source table or query’s primary key.

Note: In the future, this will be replaced with the sourcePKName (Integer index) method.
sourcePKName(Integer index)

Source table or query’s primary key column for the specified index. Index 0 will return the same name as sourcePrimaryKeyColumn_0 above.

An example of this is in the executable SQL_COPY_RANGE_MANUAL.
sourcePKLength(Integer index)

Column length of the source table or query’s primary key column for the specified index.

An example of this is in the executable SQL_COPY_RANGE_MANUAL.
predicate()

Predicate (WHERE condition) from the target mapping. This will be blank if no predicate is specified.

An example of this is in executable SQL_COPY.
andPredicate()

Predicate (WHERE condition) from the target mapping with an AND prefix so that it can be appended to a hard-coded WHERE clause in the template.

An example of this is in the executable SQL_COPY_RANGE_MANUAL.
threadingClause()

Condition WHERE ORA_HASH(…) = n to divide the selected data into a bucket per thread. This only injects the condition if the thread count on the task is greater than 1.

An example of this is in the executable SQL_COPY.

Note: The ORA_HASH function works well for moderate volumes and a relatively low number of threads, but it can cause a lot of table scans. Threading on an indexed key column is more efficient. See the rangeThreadingClause below for that alternative.
andThreadingClause()

Same as the above threadingClause method, except it prefixes the ORA_HASH condition by AND so that it can be appended to a hard-coded WHERE clause in the template.

An example of this is in the executable SQL_DELETE_K.
rangeThreadingClause()

Condition WHERE primary key id BETWEEN low and high value, where the value is based on the primary key of the source and the thread number and count. This only injects the condition if the thread count on the task is greater than 1.

An example of this is in the executable SQL_COPY_RANGE_AUTO.
andRangeThreadingClause() Same as the above rangeThreadingClause, except it prefixes the condition by AND so that it can be appended to a hard-coded WHERE clause in the template.