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

Download Fix Pack 2 for InfoSphere Information Server Version 8.1

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 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