IBM Support

JR36303: THE ORACLE CONNECTOR STOPS THE JOB WHEN IT FETCHES TRUNCATED DATA FROM THE DATABASE

Subscribe

You can track all active APARs for this component.

 

APAR status

  • Closed as program error.

Error description

  • This APAR encompasses multiple issues related to the Oracle
    Connector:
    
    When the connector reads data from the database and the data is
    truncated, the job aborts instead of just logging a warning.
    
    The Distributed Transaction Stage should provide an option to
    stop the job when the update or delete statement on Oracle
    database does not result in any actual updates or deletes.
    
    The connector stops the job when Record count property value is
    not a multiple of the Array size property value. It should
    automatically adjust the Array size value and continue.
    
    When the connector is configured to insert rows in the table,
    and the input link has more fields than there are columns in the
    target table, the connector reports error: "Memory allocation
    failed for 0 bytes.". The connector should be able to handle
    unmatched fields from the input link.
    
    When the tnsnames.ora Oracle configuration file contains line
    comments or service descriptor aliases in lowercase characters,
    the connector in some cases fails to detect and display all
    service descriptor aliases.
    
    The connector crashes when configured to run in sparse lookup
    mode and the specified SELECT statement does not have any
    parameters.
    
    When a PL/SQL anonymous block is specified in the connector and
    DataStage syntax is used for the bind parameters (in form
    ORCHESTRATE.parameter_name), the connector stops the job. It
    should be able to accept DataStage syntax for parameters in
    addition to the Oracle native syntax.
    
    When the connector is used in sparse lookup mode, and the
    specified SELECT statement has embedded job parameters, if the
    lookup operation fails, the job stops but the statement with
    resolved job parameters is not displayed in the job log. It
    should be written to the job log so that it is clear for which
    actual statement the job failed.
    

Local fix

Problem summary

  • ****************************************************************
    USERS AFFECTED:
    DataStage users with jobs that utilize Oracle Connector and
    Distributed Transaction Stage (DTS) stages.
    ****************************************************************
    PROBLEM DESCRIPTION:
    [PROD00207719]
    When the length of a text field on the output link of the
    connector is not long enough to accept data from the source
    database table, this results in Oracle reporting truncation for
    the fetched data. The connector in turn reports a fatal error
    and stops the job. The connector should provide an option to
    only issue a warning in this case and resume the job.
    
    [PROD00151560]
    When the Distributed Transaction Stage is configured to update
    or to delete rows in the target database table, and the update
    (or delete) operation for some input record does not result in
    any data updated (or deleted) in the target table, the
    connector resumes the job. The connector should provide an
    option to stop the job when this happens.
    
    [PROD00173659]
    When the Array size property in the connector is set to a value
    that is not a factor of the provided Record count property
    value, the connector reports an error and stops the job.
    Instead, the connector should automatically adjust the Array
    size value so that it is close to the user-specified value and
    at the same time a factor of the user-specified Record count
    value.
    
    [PROD00191885]
    When the connector is configured to write data to the database
    and the input link of the stage contains columns that do not
    exist in the target database table, the connector reports the
    error: "Memory allocation failed for 0 bytes.". Instead the
    connector should be able to automatically handle the unmatched
    fields from the input link.
    
    [PROD00202454]
    When the tnsnames.ora Oracle configuration file contains line
    comments or service descriptor aliases in lowercase, in some
    case the connector fails to detect and report all service
    descriptor aliases defined in this configuration file.
    
    [PROD00206773]
    When the connector stage is configured to perform lookup
    operations in the sparse lookup mode, and the specified SELECT
    statement does not have any bind parameters in its WHERE clause
    (or does not have WHERE clause), the connector in some cases
    caused the job to stop and reports that an unknown error has
    occured. Instead, the connector should run the provided
    statement for each input record even if the statement does not
    have any bind parameters for which the values would be to be
    populated from the key column fields on the reference link.
    
    [PROD00207148]
    When a PL/SQL anonymous block is specified in the connector and
    DataStage syntax is used for the bind parameters (in form
    ORCHESTRATE.parameter_name), the connector stops the job. To be
    able to run the job it is necessary to replace the parameters in
    DataStage syntax with the parameters in Oracle syntax
    (:parameter position or :parameter_name). The connector should
    also support DataStage parameter syntax in this case.
    
    [PROD00208873]
    When the connector is used in sparse lookup mode, and the
    specified SELECT statement uses job parameters (for example to
    provide table name when the job runs), if the statement fails
    for a particular record, the job stops but the statement text
    does not appear in the job log. The statement text should appear
    in the jog log in this case so that the user can see exactly
    which statement failed to run.
    
    ****************************************************************
    RECOMMENDATION:
    Install the patch JR36303
    ****************************************************************
    

Problem conclusion

  • [PROD00207719]
    New property "Fail for data truncation" was added to the
    connector to specify the action for the connector to take when
    the data that it fetched came back as truncated. When set to
    "Yes" (the default behavior) the connector will stop the job.
    When set to "No", it will log a warning message and resume
    the job in this case.
    
    [PROD00151560]
    Two new properties "Fail if no rows updated" and "Fail if no
    rows deleted" were added to the connector. When "Fail if no
    rows updated" is set to "No" (the default behavior) the job
    will resume when the input row did not result in any updates in
    the target table for "Update" and "Insert then update" write
    modes. Otherwise, when set to "Yes", the job will stop in
    this case. wHen "Fail if no rows deleted" is set to "No"
    (the default behavior) the job will resume when the input row
    did not result in any deletes in the target table for "Delete"
    write mode. Otherwise, when set to "Yes", the job will stop
    in this case.
    
    [PROD00173659]
    The connector code was updated to automatically calculate
    acceptable array size value when the user-specified value is not
    a factor of the Record count value. The connector will attempt
    to find Array size that is very close to the user-specified
    value and at the same time meets the requirement to be a factor
    of the Record count value. The connector will not modify the
    Record count value. If the connector modifies the Array size
    value, it will log the new Array size value in the job log.
    
    [PROD00191885]
    The connector code was updated to ignore fields on the input
    link that could not be bound to any parameters in the provided
    SQL statement, when the connector is configured to write data
    to the database. Additionally, a new property "Drop unmatched
    fields" was added to control whether to ignore (drop) the
    unmatched fields. When set to "Yes" the connector will only
    log the unmatched field in the job log and will resume the job.
    When set to "No", the connector will stop the job in this
    case.
    
    [PROD00202454]
    The connector code for extracting information from the
    tnsnames.ora Oracle configuration file was fixed to correctly
    interpret line comments and lowercase service descriptor aliases
    specified in the file.
    
    [PROD00206773]
    The connector code was fixed to work correctly with the
    non-parameterized SQL SELECT statements when configured to run
    in sparse lookup mode. The same statement will be invoked for
    each input record.
    
    [PROD00207148]
    The connector code was enhanced to support DataStage parameter
    syntax (ORCHESTRATE.parameter_name) for user-defined parameters
    in the specified PL/SQL anonymous block.
    
    [PROD00208873]
    The connector code was enhanced to always log the user-specified
    SQL statement or PL/SQL anonymous block used for reading and
    writing data in the database. The logged statements are
    displayed with their job parameters (if any) resolved.
    

Temporary fix

Comments

APAR Information

  • APAR number

    JR36303

  • 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-04-19

  • Closed date

    2010-05-13

  • Last modified date

    2010-12-14

  • 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