JR36517: THE DTS CONNECTOR KEEPS ORACLE CONNECTIONS OPEN ON THE CONDUCTOR NODE

Download Fix Pack 2 for InfoSphere Information Server Version 8.1

APAR status

• Closed as program error.

Error description

• The DTS Connector keeps Oracle connections open on the conductor
  node.
  
  The DTS Connector opens Oracle connection for each input link
  during the job setup phase, and keeps these connections open
  throughout the job. The DTS Connector will increase the number
  of Oracle connections by a factor of 10-20 based on the current
  job designs at customer, and the current level is already
  considered problematic by the DBA teams.
  

Local fix

Problem summary

• ****************************************************************
  USERS AFFECTED:
  DataStage users who are using Oracle Connector stage in their
  jobs.
  ****************************************************************
  PROBLEM DESCRIPTION:
  When DTS Connector stage has multiple input links configured for
  writing data to the Oracle database, and the job is running
  idle waiting for a new MQ message to arrive on the source
  queue, the job keeps multiple connections open to Oracle
  server. In addition to the connection established from MQ queue
  manager and connection established by the connector for writing
  records to the database, there are as many as N additional
  connections, where N is the number of Oracle Connector input
  links configured for the DTS Connector stage. These connections
  should be closed while the job is running so that they don't
  unnecessarily consume system resources.
  
  The following problem was also addressed under this same APAR:
  When the Oracle Connector is configured in sparse lookup mode,
  and a subrecord field is present on the input link of the Lookup
  stage, the connector reports error and stops the job. The
  reported error is: "Unsupported type code: 21". The connector
  should be able to ignore this field and allow the job to
  proceed.
  
  ****************************************************************
  RECOMMENDATION:
  Install the patch for this APAR (JR36517)
  ****************************************************************
  

Problem conclusion

• The Oracle Connector was enhanced to support new environment
  variable CC_ORA_OPTIMIZE_CONNECTIONS. When this environment
  variable is set to value TRUE (the value is case insensitive),
  the connector disconnects from Oracle during the job setup phase
  (conductor node) after completing any Table action or Before SQL
  operations. Then at the end of the job it connects to Oracle
  again if necessary, such as if it needs to complete any After
  SQL operation or post bulk load operation. This is done for
  every Oracle connector input link in case of DTS Connector stage
  scenario. If the environment variable is not set (default
  behavior) or if it is set to a value other than TRUE (case
  insensitive), the connector keeps all the setup phase Oracle
  connections open throughout the job lifetime.
  
  The Oracle Connector was also fixed to ignore subrecord fields
  that do not participate as bind parameters in the SELECT
  statement specified for sparse lookup.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

Fix information

• Fixed component name

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• R810 PSN

     UP