JR30896: ORAOCI9 STAGE (INPUT) WHEN SQL TYPE IS Timestamp AND THE SCALE = 0 OR EMPTYTHE GENERATED SQL IS TO_TIMESTAMP INSTEAD OF TO_DATE.

APAR status

• Closed as program error.

Error description

• In DataStage 7.5.3 for Input with the ORAOCI9 stage when the
  SQLTYPE is Timestamp and the scale = 0 or empty, the generated
  SQL is TO_TIMESTAMP where in previous version it was TO_DATE.
  
  This can cause very serious performance downgrade due
  to Oracle indexes not being able to handle this change.
  
  
  Important impact on DataStage migrations.
  

Local fix

• N/A
  

Problem summary

• ****************************************************************
  USERS AFFECTED:
  For DS 7.5.3, a job containing an input ORAOCI9 stage with SQL
  type Timestamp with scale=0 or empty.
  ****************************************************************
  PROBLEM DESCRIPTION:
  When an input ORAOCI9 stage has SQL type of Timestamp and
  scale=0 or empty, the SQL generated is TO_TIMESTAMP.
  ****************************************************************
  RECOMMENDATION:
  Apply patch JR30896.
  This change is included in 8.1 Fix Pack 1.
  ****************************************************************
  

Problem conclusion

• For DS 7.5.3, when an input ORAOCI9 stage has SQL type of
  Timestamp and scale=0 or empty, the SQL generated is
  TO_TIMESTAMP with this fix, TO_DATE will be generated instead.
  TO_TIMESTAMP will be generated when the scale>0. Resolution is
  to add a check for the scale entered in InLinkWrap.cpp module.
  The only thing the customer needs to do to resolve the problem
  is to install the patch.
  

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:

Modules/Macros

• CLIENT
  

Fix information

• Fixed component name

  WIS DATASTAGE

• Fixed component ID

  5724Q36DS

Applicable component levels

• R753 PSY

     UP

• R801 PSY

     UP