Purpose
/DBINPUT defines a database table as a source of records.
/DBDATA defines a database table as an auxiliary source such as a lookup source.
Format
/DBINPUT database {table_extract|sql_extract}
/DBDATA database {table_extract|sql_extract}
where
| table_extract | = | TABLE dbtable [ALIAS alias] [COLUMNS column [,column...]] [parameters] |
| sql_extract | = | SQL sql_text ALIAS alias [parameters] |
| parameters | = | PARAMETERS parameter [,parameter...] |
| parameter | = | parameter_name parameter_value |
Arguments
| database | The name or alias of a database defined through /DBCONNECTION. If you defined an alias, it must be used in place of the name. The name can be in the form of a string or an identifier. |
| dbtable | The name of the table or view in database that is the source of records. The name can be in the form of a string or identifier. |
| column | The name of a column to be extracted from the table. The name can be in the form of a string or identifier. |
| sql_text |
An SQL statement in the form of a string. You can provide the string in any valid format. For a summary of valid naming and formatting conventions for identifiers and constants, see Syntax reference in the Connect help. The result of executing the statement should be the set of records that defines the database table source. If the name of a column contains special characters and you want to reference it in other options, you must define a column alias in the SQL statement. |
| alias |
A name you assign to the database table. You can use this alias name to reference the table from other options. The name must adhere to the rules described for an identifier. For a summary of valid naming and formatting conventions for identifiers and constants, see syntax reference in the Connect help. When you do not assign an alias, use the dbtable argument to reference the table from other options. An alias is mandatory when the source of records is defined via an SQL statement (i.e., SQL sql_text). An alias is optional when the source is defined via a table (i.e., TABLE dbtable). When you define an alias for the source, reference the source only through the alias; you can no longer reference the table name directly in other options. |
| parameter_name | The name of the parameter |
| parameter_value | The value of the parameter |
Location
The /DBINPUT option can be listed anywhere in the task definition for the following task types: copy, sort, merge, and aggregates.
For a join task, the /DBINPUT option must be listed as follows:
- Left side – Before the /JOINKEYS options that defines the left side.
- Right side - After the /JOINKEYS option that defines the left side and before the /JOINKEYS option that defines the right side.
Notes
The /DBINPUT option allows you to define a database table as a source of records. For sort tasks only, a source table can also be used as a target table.
Number of sources
If you have a source database table in an aggregate task, it must be the only source.
Defining the source via SQL
You can define the source in terms of an SQL query that returns a set of rows for processing (e.g. a SELECT statement). An SQL statement that does not return a set of records is invalid.
Determining which columns are extracted
By default, dmexpress extracts all columns from the table. To extract only a subset of the columns in the table, for example to improve the performance of the task, specify the list of columns to be extracted via the COLUMNS clause.
You can reference the columns from the source table in other options where you can reference field names from a record layout. For a summary of valid naming and formatting conventions for identifiers and constants, see Syntax reference in the Connect help.
The format of extracted columns
dmexpress extracts columns into fields with types and lengths that are appropriate to the columns' native formats in the database. When you do not specify the extraction type and length explicitly, DMEpress extracts a column with a default type and length as listed in the appropriate topic under Data types reference/DBMS data types in the Connect help. Note that the default type and length may change in a future release of Connect. Hence, depending on your task, it may be preferable to specify an explicit type and length for the extraction through a /DBCOLUMNSEXTRACT option.
You can also convert the data to a specific format explicitly via the /VALUE option and include the named value definition in a /REFORMAT option.
Character encoding
If the character encoding is not set when extracting a text column from a database table, Connect ETL attempts to extract the text column in the database server’s character encoding. When Connect does not support the database server’s character encoding, Connect ETL extracts the column in locale encoding. A column extracted in locale encoding could be extracted differently when Connect ETL expands the encoding support in a future release.
To prevent the possibility of output being changed in a future Connect release, define the character encoding for the target or for each extracted text column.
Compatibility of Default Database Client Character Set with Platform Locale
Correct character encoding conversion requires that the character encoding of the platform locale is compatible with the default character set of the database client or driver through which Connect ETL accesses the database. To ensure correct character encoding conversion when the platform locale is not compatible with the default character set of the database client or driver, exchange the data with the database in one of the Unicode encodings.
Record layout
When a record originating from a database is output to a file, buffer, or procedure, the default record layout is defined as a concatenation of the extracted columns specified in the /DBINPUT option. Connect ETL automatically aligns a column following a bit column at the next available byte boundary. Connect ETL fills the unused bits in the extracted bit column up to the byte boundary with ‘0’ bits. Use the /REFORMAT option if you want to store multiple bit columns into the same byte.
Nullable columns
When data is extracted from a nullable database column, the null indicator information is also retrieved.
Using a source database table in a program
You cannot use /DBINPUT when running dmexpress from a program.
Example
/DBINPUT transaction_database table client_facts/DBINPUT transaction_database table client_facts columns client_name/DBINPUT transaction_database SQL "select * from client_facts where amount > 10000" alias clients_above_10K/DBINPUT transaction_database SQL "select count(*) as no_of_transactions from client_facts" alias transaction_count/DBINPUT transaction_database SQL "select sum(amount) as total_amount from client_facts" alias transaction_totalThe option specifies that source records come from the specified SQL query. The query is executed against the database transaction_database and the set of rows returned is available for processing. The result of executing sum(amount) on the set of rows is given a column alias total_amount that can be used to reference the column in other options. Likewise, the source is given an alias transaction_total that can be used to reference it in other options.