Skip Ribbon Commands
Skip to main content
Navigate Up
Sign In

Quick Launch

Average Rating:

facebook Twitter
Email
Print Bookmark Alert me when this article is updated

Feedback

HOW TO: Configure database resiliency for a SQL transformation?
Solution

Database resilience for SQL transformations is available starting with PowerCenter 8.6.  The databases supported are Oracle, DB2, SQL Server, Sybase and Teradata. ODBC connections are not supported.

The SQLT resiliency feature in PowerCenter 8.6 uses the connect_errors.act error action file which is in the <INFA_Home>/server/bin directory.  This file contains pre-configured error codes for individual databases. SQLT resiliency is triggered when any of these error codes are encountered.

  1. Create a copy of the error action file, and add any additional specific database error codes to this file (A sample file is attached to this article).
  2. In the Administration Console select the Integration Service
  3. Go to the Custom Properties section under the Processes tab.
  4. Enter the absolute path for the new file as below.

    Name - ConnectionErrorCodesFile
    Value - <Absolute path to the modified error action>

    Integration Service  - Processes tab

  5. In the Workflow Manager, edit the relational connection used by the SQL transformation and set the Connection Retry Period to a non-zero value such as 20.  This is the resilience retry period.
  6. Restart the Integration Service.
More Information

DB2 Errors

An error code as well as a SQL code must be added for DB2 errors. The error code (such as SQL1224N)  should be added to the Db2ConnFailCodes section, and the sqlstate codes (such as 55032) should be added to the DB2ConnErrorSqlStates section. The error codes should be added with a '-' followed by the number. Example - For SQL1224N, the code added would be -1224

In case of the following DB2 database error, the error action file would be modified as shown below:

[IBM][CLI Driver] SQL1224N  A database agent could not be started to service a request, or was terminated as a result of a database system shutdown or a force command.  SQLSTATE=55032   sqlstate = 40003

Db2ConnFailCodes=-30081,-99999,-1224
DB2ConnErrorSqlStates=08003,40003,55032,08001

The Integration Service will attempt to retry the database connection when it encounters this error, and not fail the session.

Reference

PowerCenter 8.6 Workflow Administration Guide > "Chapter 2 Managing Connection Objects"

Applies To
Product: PowerCenter
Problem Type:
User Type: Administrator
Project Phase:
Product Version: PowerCenter 8.6; PowerCenter 8.6 SP1
Database:
Operating System:
Other Software:
Attachments
Last Modified Date:11/13/2008 3:44 PMID:101096
People who viewed this also viewed

Feedback

Did this KB document help you?



What can we do to improve this information (2000 or fewer characters)