DATABASE Stages:

ODBC Stage:

ODBC Stage “Stage” Page:

You can use an ODBC stage to extract, write, or aggregate data. Each ODBC stage can have any number of inputs or outputs. Input links specify the data you are writing. Output links specify the data you are extracting and any aggregations required. You can specify the data on an input link using an SQL statement constructed by DataStage, a generated query, a stored procedure, or a user-defined SQL query.

  • GetSQLInfo: is used to get quote character and schema delimiters of your data source. Optionally specify the quote character used by the data source. By default, this is set to ” (double quotes). You can also click the Get SQLInfo button to connect to the data source and retrieve the Quote character it uses. An entry of 000 (three zeroes) specifies that no quote character should be used. Optionally specify the schema delimiter used by the data source. By default this is set to. (period) but you can specify a different schema delimiter, or multiple schema delimiters. So, for example, identifiers have the form Node:Schema.Owner;TableName you would enter :.; into this field. You can also click the Get SQLInfo button to connect to the data source and retrieve the Schema delimiter it uses.
  • NLS tab: You can define a character set map for an ODBC stage using the NLS tab of the ODBC Stage
  • The ODBC stage can handle the following SQL Server data types:
  • GUID
  • Timestamp
  • SmallDateTime

ODBC Stage “Input” Page:

  • Update action. Specifies how the data is written. Choose the option you want from the drop-down list box:
    • Clear the table, then insert rows. Deletes the contents of the table and adds the new rows.
    • Insert rows without clearing. Inserts the new rows in the table.
    • Insert new or update existing rows. New rows are added or, if the insert fails, the existing rows are updated.
    • Replace existing rows completely. Deletes the existing rows, then adds the new rows to the table.
    • Update existing rows only. Updates the existing data rows. If a row with the supplied key does not exist in the table then the table is not updated but a warning is logged.
    • Update existing or insert new rows. The existing data rows are updated or, if this fails, new rows are added.
    • Call stored procedure. Writes the data using a stored procedure. When you select this option, the Procedure name field appears.
    • User-defined SQL. Writes the data using a user-defined SQL statement. When you select this option, the View SQL tab is replaced by the Enter SQL tab.
  • Create table in target database. Select this check box if you want to automatically create a table in the target database at run time. A table is created based on the defined column set for this stage. If you select this option, an additional tab, Edit DDL, appears. This shows the SQL CREATE statement to be used for table generation.
  • Transaction Handling. This page allows you to specify the transaction handling features of the stage as it writes to the ODBC data source. You can choose whether to use transaction grouping or not, specify an isolation level, the number of rows written before each commit, and the number of rows written in each operation.
  • Isolation Levels: Read Uncommitted, Read Committed, Repeatable Read, Serializable, Versioning, and Auto-Commit.
  • Rows per transaction field. This is the number of rows written before the data is committed to the data table. The default value is 0, that is, all the rows are written before being committed to the data table.
  • Parameter array size field. This is the number of rows written at a time. The default is 1, that is, each row is written in a separate operation.
ODBC Stage “Output” Page:

 

Leave a Reply