RDBMS Input

The RDBMS Input tool is used to read data from RDBMS tables and queries, as well as any "file" data sources for which a provider or driver is available. For example, you can obtain OLE DB providers for ISAM and VSAM files. With the RDBMS Input tool, you can:

• Read the contents of a table.

• Execute an SQL query and read its results.

• Query a table dynamically using key values obtained from an upstream connection.

RDBMS Input tool configuration parameters

The RDBMS Input tool has five sets of configuration parameters in addition to the standard execution options: Source, Options, Input, Dynamic Query, and Connection Order.

Note that in some cases you may need to select Override to view all parameters.

Source

The Source tab defines the connection to the RDBMS data source:

Options

The Options tab controls behavior details of the database operations. See Configuring options.

Input

The Input tab defines the record input parameters:

Dynamic Query

The Dynamic Query tab optionally defines dynamic query parameters:

For each dynamic key you define, specify:

Connection Order

See Configuring connections for details.

Configure the RDBMS Input tool

1. Select the RDBMS Input tool, and then go to the Source tab.

2. Configure a database connection, or select Use data connection and then select a data source from the list.

3. Select the Input tab, and then select either Direct table read or SQL query to specify a record source.

   • If you select Direct table read, you can optionally select Schema and select a schema. If you specify a schema, the Table list (below) will only display tables associated with that schema.

   • If you select Direct table read, select a Table from the list. You may need to select Refresh tables

     to see available tables. If you did not specify a schema, and you have tables with the same name associated with different schemas, the table list will contain multiple elements with the same name.

   • If you select SQL query, type an SQL query in the box.
     

     

4. Optionally, select the Options tab and configure database options.

5. Optionally, you can:

   • Select filter and choose which types of RDBMS objects are displayed on the Table list.

   • Limit the number of records read by selecting Limit records and typing a number in the Process only the first box. When the query itself has a limit (for example, a TOP 100 clause) the actual limit will be the lesser of Process only the first and the limit in the query.
     Because databases use different methods to limit records, selecting this option does not necessarily limit the number of rows retrieved from a database. If you are using the RDBMS Input tool with a long-running query and want limit the number of rows returned by the query, you should construct an SQL query using the appropriate clause from your database product's SQL dialect instead.

6. Select the Dynamic Query tab and specify a dynamic key.

7. If you specified a dynamic key with multiple inputs, you may optionally select the Connection Order tab and adjust the order of input connections.

8. Optionally, go to the Execution tab and Enable trigger input, configure reporting options, or set Web service options.

Dynamic key query

The RDBMS Input tool supports the ability to query records from a database using other data as parameters for the query. There are several situations in which you might want to do this. For example:

• You want to match a small number of customer records to a master database. To avoid excess database activity, you want to read records out of the database only if they are match candidates.

• You want to join records between different databases, or between databases and files.

• You have a list of record IDs from some other source, and want to query only those records.

In these cases, the dynamic key query will extract only the records from an RDBMS table that meet criteria specified by key values available from an "upstream" data source.

Note that if you’re attempting to select rows from a table with a composite primary key, the default dynamic query behavior generates SELECT statements for each key and returns a UNION of their results. Consequently, the dynamic query will return all rows that match any of the keys. If instead you wish to return only those rows that match all of the keys, follow the instructions for Creating custom dynamic key queries and change the UNION operator to INTERSECT.

To configure the RDBMS Input tool for dynamic key query:

1. Connect and configure one or more input tools to the RDBMS tool. These inputs will supply the sets of key values from which the dynamic query will be constructed in the RDBMS input tool.

2. Select the Dynamic Query tab.

3. In the Use temp table box, select how you want to use temporary tables to assist the dynamic key query:

   • Never

   • Batch only

   • Always

When temporary tables are used, the keys are first written to a temporary table in the database, and then a special query is constructed to retrieve records using a join against the temporary table. When temporary tables are not used, a query is constructed by building a WHERE clause consisting of OR terms for each key value. Using temporary tables lets you process a larger number of keys. Usually you will want to accept the default Batch only setting, in which temporary tables are used during batch execution but not in real-time services.

4. Select rows in the Dynamic key grid and then specify a Key configuration for each dynamic key:

   • Input: the input tool supplying keys to the RDBMS Input tool.

   • Field: the name of the field containing the key value.

   • SQL field: the name of the database field that you want to match to the input keys. For example, you may have an input key field named PHONE, while in the database it is named PHN_NBR.

   • Temp table: if you selected Always or Batch only in step 3, type a name for the temporary table in the Temp table box. Each dynamic key must have a unique temporary table; they cannot be shared.

   • If the specified temp table doesn't already exist, Data Management will create it at run time. If the user running the project may not have permissions to create the table, select Create table to create the temporary table now.
     

     

5. Optionally, check Drop temp table(s) when finished to create a new table each time the project is run. If you select this option, each database login that will run the project must have create/drop table permissions.

Custom dynamic key queries

When you configure the RDBMS Input tool for dynamic key query, Data Management constructs a dynamic SQL query that meets your dynamic key specifications. This happens automatically when you select Direct table read on the Input tab of the RDBMS Input tool. The query that Data Management constructs selects all columns from the table. Under most circumstances, this is a good approach. However, there are times when you may want to custom-tailor this query to meet your needs, for example:

• Selecting a subset of the table's columns to improve performance.

• Handcrafting an SQL query that calls SQL functions to tailor the result data.

To create a custom SQL query that interacts with the dynamic-key specification, you must follow specific conventions, inserting placeholders for Data Management to fill in as needed.

To create a custom dynamic key SQL query:

1. Start with an RDBMS Input tool configured for Direct table read, and an SQL query that expresses what you want, but with no dynamic-key components.

2. Change the SELECT clause to qualify the column selection with the configured table name.

3. If you don't have a WHERE clause, add WHERE %KEYSET% to the end of the query.

4. If you have a WHERE clause, add AND %KEYSET% to the WHERE clause.

5. Add a %DYNTABLE% placeholder immediately before the WHERE.

6. If you want to specify more than one dynamic key, copy the resulting query once for each additional dynamic key, and separate the queries with UNION, resulting in a query performing an implicit logical OR between multiple keys.

Note that if you’re attempting to select rows from a table with a composite primary key, the default dynamic query behavior generates SELECT statements for each key and returns a UNION of their results. Consequently, the dynamic query will return all rows that match any of the keys. If instead you wish to return only those rows that match all of the keys, follow the above instructions, and in step 6 change the UNION operator to INTERSECT.

Examples:

If you start with

select * from test1

For one dynamic key, change it to:

SELECT test1.* FROM test1 %DYNTABLE% WHERE %KEYSET%

For two dynamic keys, change it to:

SELECT test1.* FROM test1 %DYNTABLE% WHERE %KEYSET%
UNION
SELECT test1.* FROM test1 %DYNTABLE% WHERE %KEYSET%