Stored Procedure stage - General tab

The tab has the following fields:

• Database vendor. The type of relational database. With this release, the options are Oracle, DB2®, Sybase, Teradata, and MSSQL Server. Database vendor is required.
• Database source. The name of the database alias.

Oracle

• For Oracle, enter the name you created using the Oracle Configuration Assistant.

DB2

• For DB2, enter the connection name, the data source name, or the database name.

SQL Server

• For SQL Server, enter an ODBC DSN name that describes driver and server information.
  • User name. The user name to use to connect to the database. This user must have sufficient privileges to access the specified database and source and target tables. This field is required. There is no default.
  • Password. The password that is associated with the specified user name. This field is required. There is no default.
  • Database name. The database on the specified server.

DB2

• This field is not available for DB2.
  Note: If you select DB2 as the Database vendor, and click OK, when you return to the Stage page, Database name is not active.

SQL Server

• This field is not available for SQL Server.
• Transaction ISO. The transaction isolation level (ISO level). The ISO level provides the necessary consistency and concurrency control between transactions in the job and other transactions for optimal performance. Use Transaction ISO to set the transaction isolation level for input and output links by choosing a value in the list provided. ISO Level is database specific, and each database might not support all the generic choices presented. For more information on using these levels, see your database documentation. Use one of the following transaction isolation levels:
  • None. The default.
  • Read-only. Takes no write locks. This is the most efficient ISO level allowing for optimal performance.
  • Read Uncommitted. Takes exclusive locks on modified data. These locks are held until a commit or rollback is executed. However, other transactions can still read but not modify the uncommitted changes. No other locks are taken.
  • Read Committed. Takes exclusive locks on modified data and sharable locks on all other data. This is the default. Exclusive locks are held until a commit or rollback is executed. Uncommitted changes are not readable by other transactions. Shared locks are released immediately after the data has been processed, allowing other transactions to modify it.
  • Repeatable Read. Identical to serializable except that phantom rows might be seen.
  • Serializable. Takes exclusive locks on modified data and sharable locks on all data. All locks are held until a commit or rollback is executed, preventing other transactions from modifying any data that has been referenced during the transaction.

Oracle

Select from among None, Read-only, Read Committed, and Serializable for Oracle.

DB2

Select from among None, Read Uncommitted, Read Committed, Repeatable Read, and Serializable for DB2.

Sybase

Select from among None, Read Uncommitted, Read Committed, and Serializable for Sybase.

SQL Server

Select from among None, Read Uncommitted, Read Committed, Repeatable Read, and Serializable for SQL Server.

Teradata

Transaction ISO is inactive when the Database vendor is Teradata. The Teradata RDBMS supports a LOCKING modifier on some SQL statements to allow control over transaction isolation levels, but CALL and EXECUTE statements do not support the LOCKING modifier. To use a particular locking method, specify the LOCKING modifier in the SQL statements in the body of the macro or procedure.

• Transaction mode. The mode of connection to the Teradata server. This field is active only when the selected Database vendor is Teradata or a job parameter.
  • If Database vendor is Teradata, the options for Transaction mode are ANSI, the default, and Teradata.

    This field controls whether the Stored Procedure stage connects to the Teradata server in ANSI or Teradata transaction mode. A connection in ANSI transaction mode cannot call stored procedures that were compiled in Teradata transaction mode, and a connection in Teradata transaction mode cannot call stored procedures that were compiled in ANSI transaction mode.

  • If Database vendor is a job parameter, the options for Transaction mode are ANSI and Native. Native transaction mode is equivalent to Teradata transaction mode if the database vendor at runtime is Teradata. The Transaction mode field has no effect for other vendors.
    Note: If an error occurs in Teradata transaction mode, Teradata automatically rolls back the current transaction. In ANSI transaction mode, an error will not affect the current transaction.
• Client character set. The Teradata client character set to use when connecting to the Teradata server. This field is active only when Database vendor is Teradata or a job parameter. The default value is Default.
• Description. An optional description of the Stored Procedures stage.