IBM Support

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

Subscribe

You can track all active APARs for this component.

 

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 number

    JR36517

  • Reported component name

    WIS DATASTAGE

  • Reported component ID

    5724Q36DS

  • Reported release

    810

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt

  • Submitted date

    2010-05-06

  • Closed date

    2010-05-26

  • Last modified date

    2010-05-26

  • 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

[{"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SSVSEF","label":"InfoSphere DataStage"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"8.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
12 October 2021