odbcread: syntax and options

Syntax and options of the odbcread operator.

The syntax for the odbcread operator follows. The optional values that you provide are shown in italic typeface. When your value contains a space or a tab character, you must enclose it in single quotes.


odbcread
  -query sql_query
  |
  -tablename table_name 
    [-filter where_predicate] 
    [-list select_predicate]
  -datasourcename data_source_name
  [-username user_name]
  [-password password]
  [-close close_command]
  [-db_cs icu_code_page [-use_strings]]
  [-open open_command]
  [-partitionCol PartitionColumnName]
  [-arraysize size]
  [-isolation_level read_committed | read_uncommitted | repeatable_read | serializable]

You must specify either the -query or -tablename option. You must also specify the -datasourcename, -username, and -password options.

Table 1. odbcread Operator Options
Option Use
-query -query sql_query

Specify an SQL query to read from one or more tables.

Note: The -query option is mutually exclusive with the -table option.
-table -tablename table_name

Specify the table to be read from. It might be fully qualified. This option has two suboptions:

  • -filter where_predicate: Optionally specify the rows of the table to exclude from the read operation. This predicate is appended to the where clause of the SQL statement to be executed.
  • -list select_predicate: Optionally specify the list of column names that appear in the select clause of the SQL statement to be executed.
    Note: This option is mutually exclusive with the -query option.
-datasource -datasourcename data_source_name

Specify the data source to be used for all database connections. This option is required.
-user -username user_name

Specify the user name used to connect to the data source. This option might or might not be required depending on the data source.
-password -password password

Specify the password used to connect to the data source. This option might or might not be required depending on the data source.
-open -opencommand open_command

Optionally specify an SQL statement to be executed before the insert array is processed. The statements are executed only once on the conductor node.
-close -closecommand close_command

Optionally specify an SQL statement to be executed after the insert array is processed. You cannot commit work using this option. The statements are executed only once on the conductor node.
-arraysize -arraysize n

Specify the number of rows to retrieve during each fetch operation. The default number of rows is 1.
-isolation_level --isolation_level

read_uncommitted | read_committed | repeatable_read | serializable

Optionally specify the transaction level for accessing data. The default transaction level is decided by the database or possibly specified in the data source.
-db_cs -db_cs character_set

Optionally specify the ICU code page which represents the database character set in use. The default is ISO-8859-1.
-use_strings -use_strings

If the -use_strings option is set, strings instead of ustrings are generated in the InfoSphere® DataStage® schema.
-partitioncol -partition_column

Use this option to run the ODBC read operator in parallel mode. The default execution mode is sequential. Specify the key column; the data type of this column must be integer. The column should preferably have the Monoatomically Increasing Order property.

If you use the -partitioncol option, then you must follow the sample OSH scripts below to specify your -query and -table options.

  • Sample OSH for -query option:

odbcread -data_source SQLServer -user sa 
-password asas -query 'select * from SampleTable where 
Col1 =2 and %orchmodColumn% ' -partitionCol Col1  
>| OutDataSet.ds
  • Sample OSH for -table option:

odbcread -data_source SQLServer -user sa 
-password asas -table SampleTable 
-partitioncol Col1 >| 
OutDataset.ds