JR37067: Add support for handling invalid input Date fields

APAR status

• Closed as program error.

Error description

• Oracle Connector accepts invalid Date fields on the input link
  and inserts them as 0000-00-00 date values into the database.
  Instead the connector should throw an error in that case and it
  should also provide an option for the user to specify that NULL
  value should be inserted in this case.
  

Local fix

Problem summary

• ****************************************************************
  USERS AFFECTED:
  DataStage users with jobs that use Oracle Connector stage and
  have one or more of Date, Time and Timestamp columns on the
  input link for the stage.
  ****************************************************************
  PROBLEM DESCRIPTION:
  Oracle Connector stage is used to insert rows to a table in the
  database. One of the fields in the input link is defined to be
  of Date type. This field is initialized from text values in an
  upstream Transformer stage. For text values that contain invalid
  dates, the Transformer stage issues a warning for the
  conversion. If a reject link is not configured on the
  Transformer stage to accept this record, the Transformer stage
  sends this record with the invalid Date field to its output
  link. The Oracle Connector does not recognize the field as
  invalid and inserts date value to the database with zeros used
  for the year, month and day portions.
  
  Instead the connector should stop the job when an invalid Date
  field arrives on its input link. It should also allow the user
  to specify that NULL value should be inserted to the database in
  this case.
  ****************************************************************
  RECOMMENDATION:
  Install patch for APAR JR37067
  ****************************************************************
  

Problem conclusion

• The Oracle Connector was enhanced to support new environment
  variable CC_ORA_INVALID_DATETIME_ACTION. When this environment
  variable is set to value NULL (the value is not case
  sensitive), the connector inserts NULL value to the database
  for invalid Date, Time or Timestamp fields on its input link.
  If this environment variable is set to another value, or if it
  is not set at all, the connector stops the job in this
  situation and logs a fatal error message indicating that the
  internal variable bInvalidDateTime is set to value 1. This
  message serves as an indicator that an invalid date/time field
  arrived on the input link of the stage.
  
  The same logic applies also when performing bulk load operation.
  
  Note: Currently this support is limited to Date and Timestamp
  fields. The support for Time fields is planned to be
  incorporated in the future.
  

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