JR37031: ORAOCI9 STAGE (INPUT) WHEN SQL TYPE IS TIMESTAMP AND THE SCALE =0, GENERATED SQL IS TO_DATE BUT RUNTIME IS TO_TIMESTAMP.

APAR status

• Closed as program error.

Error description

• On IS 8.1.0.1 (FP1), Oraoci9 Stage (Input) When SQL TYPE IS
  Timestamp and the scale = 0 OR empty the generated sql is
  TO_DATE but runtime is TO_TIMESTAMP.
  
  This causes very serious performance issue due to Oracle indexes
  not being able to handle this change.
  
  Important impact on DataStage migrations.
  

Local fix

• Change to User-Defined Sql
  

Problem summary

• ****************************************************************
  USERS AFFECTED:
  OCI plugin
  ****************************************************************
  PROBLEM DESCRIPTION:
  In DataStage 7.5.1A and 7.5.2 GA. Oracle OCI plugin generates
  SQL using TO_DATE(...) for field type TimeStamp. However, when
  they migrated to 8.1, OCI plugin generates SQL using
  TO_TIMESTAMP(...) for field type TimeStamp. To catch the gap,
  we introduces the enviroment variable
  DS_TIMESTAMP_ZERO_SCALE_TO_DATE. When this variable is set as 1
  and the field scale is 0, OCI plugin generates SQL using
  TO_DATE(...) for field type TimeStamp.
  ****************************************************************
  RECOMMENDATION:
  Apply patch JR37031 and set DS_TIMESTAMP_ZERO_SCALE_TO_DATE as 1
  ****************************************************************
  

Problem conclusion

• if enviroment variable DS_TIMESTAMP_ZERO_SCALE_TO_DATE is 1 and
  the field scale is 0, OCI plugin generates SQL using
  TO_DATE(...) for field type TimeStamp.
  

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

  INFO SRVR PLATF

• Fixed component ID

  5724Q3612

Applicable component levels

• R810 PSN

     UP