Retrieve column—%GETCOL (Dynamic SQL)

Use this function to perform a secondary table column lookup based on the specified secondary columns.

Note: This topic contains information about the %GETCOL function supported by any CDC Replication replication engines except for the CDC Replication Engine for Db2® for i, CDC Replication Engine for BigQuery, and the CDC Replication Engine for Netezza® Technology.

The %GETCOL function retrieves data from secondary table columns. To use this function before replication, you must add a derived column to the primary table and enter an expression for that column that uses the %GETCOL function. You can also use this function when entering an expression for a target column.

To use this function, the secondary tables must have WHERE clause columns. The WHERE clause columns must be specified by name and you must provide a value expression for every WHERE clause column. Any column in the replicating table can be referenced by value expressions, and the captured column value is substituted in the expression.

If the secondary columns have different data types, then you must convert them to appropriate data types using conversion column functions such as %TOCHAR.

If you are using the CDC Replication Engine for Db2 for z/OS®, then see %GETCOL and %SELECT Function Calls and Processing Efficiency topic in your CDC for Db2 for z/OS documentation for information about performance considerations when using the %GETCOL or %SELECT column functions.

This function gives you the option of specifying the encoding to be used for a column in a secondary table. You should only specify a value for ENCODING if you want to override or change the default encoding of the column as set in your database and detected by CDC Replication. When retrieving data from a secondary table, CDC Replication will use the following criteria (in the order specified) to determine the encoding for a column:

The ENCODING specification for a column in a secondary table that is explicitly identified in %GETCOL.
The ENCODING specification for a column in your subscription if the secondary table is specified in the subscription. This encoding value is specified on the Encoding tab in the Mapping Details view of Management Console
The default encoding of the column in your database.

Long syntax format—reads from database

%GETCOL(secondary_column_name, table_name, [default_value],
secondary_column_name1, key_value1, [secondary_column_name2
, key_value2, [...], secondary_column_namen, 
key_valuen])

This function reads a table and returns the value of the column specified, based on the secondary column names and values that are identified. If you specify a table or column name that contains spaces, then you must enclose that name in square brackets. For example, enter [EMP NY] to reference a table named EMP NY.

Long syntax format with optional `ENCODING` specification

%GETCOL(secondary_column_name [ENCODING encoding], table_name, 
[default_value], secondary_column_name1 [ENCODING encoding1],
key_value1, [secondary_column_name2 [ENCODING encoding2], key_value2, 
[...], secondary_column_namen [ENCODING encodingn], key_valuen])

Short syntax format—reads from existing &GETCOL result

%GETCOL(secondary_column_name, table_name, default_value)

This function returns the value of a the specified column from a row retrieved by a previous %GETCOL function invocation. The short syntax lets you retrieve more than one column from a table (that was read previously using the %GETCOL function), without reading the table again. You must use the long syntax format of this function to specify an encoding.

The table_name parameter specified in the short format of the %GETCOL function invocation must match the table_name parameter specified in the long format of the %GETCOL function invocation. If the read is unsuccessful or a previous %GETCOL function invocation was not performed, then CDC Replication generates an error message and sets the values of the derived column in which the %GETCOL function is used to default values, based on the data type of the derived column.

Parameters

secondary_column_name

Specifies the name of a column in a secondary table. You cannot specify an expression for this parameter. If the specified column does not exist in the secondary table, then CDC Replication generates an error message when verifying the expression that contains the %GETCOL function. If you want to override or change the default encoding for a column as set by your database and detected by CDC Replication, you have the option of using ENCODING <encoding> to specify the ISO/IANA name for the encoding of the column. ENCODING <encoding> is not supported for the CDC Replication Engine for Db2 for z/OS. This option is only supported when using the long syntax format.

If you are using the CDC Replication Engine for Informix®, the values you specify here must be in lower case.

table_name

Specifies the name of a table. Note the following when specifying this parameter:

For the CDC Replication Engine for Oracle databases, you can specify the table name in the format 'OWNER_NAME.TABLE_NAME', where the owner and table are in uppercase. If you omit the owner name, then CDC Replication assumes that the owner is the user that installed CDC Replication. If ownership of the table cannot be determined, then CDC Replication assumes the default value of PUBLIC for the owner name. If you installed the CDC Replication Engine for Microsoft SQL Server or the CDC Replication Engine for Sybase databases, you must specify the table name in the format database_name.owner_name.table_name.
For the CDC Replication Engine for Db2 for z/OS, you must specify the table name in the format owner_name.table_name or 'owner_name.table_name'.
For the CDC Replication Engine for Db2 Database, you must specify the table name in the format [owner_name].table_name or '[owner_name].table_name'.

default_value

Specifies a default value to return when no row can be found in the secondary table using the key value that matches the value of the referring (foreign) secondary column in the primary table row. If the corresponding row cannot be found in the secondary table, then the default value populates the derived column for the row sent to the target. If you specify NULL as the default value, then the column must be nullable. For certain CDC Replication replication engines, this is a required parameter. CDC Replication generates an error if a default value is required, but is not specified in a %GETCOL function invocation. If you omit this parameter, then you must enter two consecutive commas (for the long syntax) or a comma (for the short syntax) prior to the right parenthesis to indicate its position in the parameter list. In this case, the %GETCOL function returns a default value according to the data type of the column specified.

secondary_column_name1, secondary_column_name2, ...secondary_column_namen

Identifies the secondary column name in the secondary table used to retrieve the secondary column specified by secondary_column_name.

The DB2® UDB for z/OS API requires that the column specified in secondary_column_name is not nullable. The CDC Replication Engine for Db2 for z/OS generates an error message if it encounters a NULL key value when processing the %GETCOL function. To ensure that the %GETCOL function is invoked only for records with non-NULL key values, use the %IF function.

If you are using the CDC Replication Engine for Informix, the values you specify here must be in lower case.

key_value1, key_value2, ...key_valuen

Specifies an associated key value for each secondary column name. The key value can be any expression. If you specify a primary column name, then CDC Replication uses the after image value of that column as the key_value for the secondary column in the secondary table to locate the corresponding row in the secondary table. You can specify multiple secondary column names and key value pairs.

If you are using the CDC Replication Engine for Informix, the values you specify here must be in lower case.

Note: For the CDC Replication Engine for Db2 for z/OS, you can specify up to nine secondary_column_name/key_value pairs.

Result data type

The data type of the column retrieved (secondary_column_name). The %GETCOL function always returns data that matches the data type of the column. For character (String) data, it returns a UTF-8 string.

Examples

For examples and scenarios for the %GETCOL function, see the related topics list.