Db2 Data Map Connector

Back Next

Db2 Data Map source properties in mappings

When you create a mapping, you configure a Source transformation for a source and set some source properties for the Source transformation.

Click the Source transformation box on the mapping canvas. Then configure the source properties in the

Source Properties

panel below the mapping canvas.

If you have multiple Source transformations in a mapping, configure source properties for each source. If you use a specific connection instead of a connection parameter, enter the same connection for all sources.

The

Source Properties

panel contains the following tabs on which you can enter information:

General

tab. Specify a user-defined name and optional description for the Db2 Data Map source.

Source

tab. Configure the source properties under

Details

and under

Advanced

. The advanced properties are available only if you enter a specific connection for the source. If you enter a parameter for the source connection, the advanced properties are not displayed. However, you can define these properties in the associated mapping task after you select a specific source connection for the task.

The Filter and Sort options under

Query Options

are not supported.

The following table describes the Db2 Data Map source properties under

Details

Property	Description
Connection	Required . Select a source connection of the type Db2 Data Map , or click New Connection to create one. Alternatively, you can define a connection parameter in the mapping and enter a specific connection in each mapping task that is associated with the mapping. If you want to use a connection parameter with a specific source object, you must first select a specific connection so that you can access the source to select the tables from the Select Source Tables dialog. After you select the source tables, define the connection parameter. To define a connection parameter, click New Parameter next to the Connection property and enter the following information: Enter a parameter name that begins with a letter and is up to 200 characters in length, including @, #, _, and alphanumeric characters. In the Type property, verify that connection is selected. Leave the Connection Type property blank. After you click OK , the parameter appears in the Connection property.
Source Type	Required . Select one of the following options: Single Object lets you select a Db2 Data Map source object in the Object property. Parameter selects an existing parameter for the source object, or allows you to define a new parameter for the mapping. The parameter defines a specific source object in each mapping task associated with the mapping.
Parameter	Parameter to use for the source object. This field only appears when you select Parameter as the source type. To parameterize a source object, click New Parameter . Enter the parameter name and then select the type data object . From the New Input Parameter dialog box, you can add a description, default value, and other information for the parameter.
Object	If you selected Single Object as the source type, you can select a Db2 Data Map source object. Click Select. Then in the Select Source Object dialog box, under Packages , click a listed value. This value is the schema name. The right pane lists the tables in the selected schema. The table metadata is stored in the Informatica Intelligent Cloud Services repository in the cloud. The Preview Data feature is not supported.

The following table describes the optional source properties under

Advanced

Advanced Property	Description
Db2 Subsystem ID	For the DB2 Data Maps source type, overrides the DB2 subsystem ID in the PowerExchange data map.
Db2 Table Name	For the DB2 Data Maps source type, overrides the DB2 table name in the PowerExchange data map.
Filter Overrides	One or more conditions that filter the source records that the PWX Bulk Reader retrieves from the PowerExchange Listener. Based on the filter conditions, a WHERE clause is appended to the default SQL SELECT query that the PWX Bulk Reader uses to read the records. Db2 Data Map Connector supports two forms of filter condition syntax. For single record source definitions such as single record nonrelational data maps, specify a single filter condition statement. You can specify a single filter condition or join numerous filter conditions by using the conditional operands that PowerExchange supports for NRDB SQL statements. For example: column1 is NULL and column2=’A’ For multi-record nonrelational source definitions, you can also use the following syntax: group_name1=filter_condition;group_name2=filter_condition;... Use the group_name form to specify filter conditions for one or more record types in a multi-record source definition. To apply a filter condition to all records in a multi-record source definition, use a single filter condition without group_name. You cannot combine single filter conditions with group_name conditions. You must use the nonrelational SQL syntax that PowerExchange supports for comparison and LIKE operators. For more information, see the "PowerExchange Nonrelational SQL" chapter in the PowerExchange Reference Manual . If the filter includes a date or timestamp column, ensure that the range of years in the column data is within the range of years for PowerExchange data filtering, as controlled by the DATERANGE statement in the DBMOVER configuration file. The default range is 1800 to 2200. For example, to select records in which the TYPE column has a value of A or D, specify the following condition: TYPE='A' or TYPE='D' Maximum length of the entire SELECT statement that PowerExchange builds, including the WHERE clause with the filter conditions, is 8192 bytes. If you specify the Filter Overrides attribute and also specify an SQL Query Override attribute value that contains a filtering WHERE clause, the resulting SELECT statement contains a WHERE clause that uses the AND operator to associate the Filter Overrides filter conditions with the SQL Query Override conditions. For example: SELECT * from `schema`.`table` WHERE `Filter_Overrides_conditions` AND `SQL_Query_Override_conditions`
Map Name Override	Overrides the data map name of the source PowerExchange data map.
Map Schema Override	Overrides the schema name in the source PowerExchange data map.
SQL Query Override	An SQL statement that overrides the default SQL query that the PWX Bulk Reader uses to retrieve records from PowerExchange. The connector replaces the default SQL query with the SQL statement that you enter and passes the SQL statement to Cloud Data Integration for processing. You must use the nonrelational SQL syntax that PowerExchange supports. For more information, see the "PowerExchange Nonrelational SQL" chapter in the PowerExchange Reference Manual . If the override statement includes a date or timestamp column, ensure that the range of years in the column data is within the range of years for PowerExchange data checking and filtering, as controlled by the DATERANGE statement in the DBMOVER configuration file. The default range is 1800 to 2200. For example, to select records for the USER source table when the TYPE column has the value of A or D, specify the following query: SELECT ID, NAME from USER where TYPE=‘A’ or TYPE=‘D’; Maximum length of the SELECT query override statement is 8192 bytes. If you specify an SQL Query Override attribute value that contains a filtering WHERE clause and also specify the Filter Overrides attribute, the resulting SELECT statement contains a WHERE clause that uses the AND operator to associate the SQL Query Override conditions with the Filter Overrides filter conditions. For example: SELECT * from `schema`.`table` WHERE `Filter_Overrides_conditions` AND `SQL_Query_Override_conditions` For a multiple-record source, use the following syntax: group_name1=sql_query_override1; group_name2=sql_query_override2;... For example, you can select only records with ID column values that contain DBA for a multi-record source with two records called USER1 and USER2 by specifying the following SQL query override: USER1=Select ID, NAME from USER1 where ID='DBA'; USER2=Select ID, NAME from USER2 where ID='DBA';
Tracing Level	The level of detail that appears in the session log for a Source transformation. Select one of the following options, which are listed in order of increasing detail: Terse Normal Verbose Initialization Verbose Data Default is Normal .