JR58927: Converting certain data from CHAR or VARCHAR to DATE or TIMESTAMP type the server job insert fails

Download the mod pack for IBM InfoSphere Information Server 11.7.1.0 In-Place Upgrade

APAR status

• Closed as program error.

Error description

• When the design schema has char/varchar & external schema has
  date/time/timestamp, the year value gets inserted incorrectly.
  
  For example:
  
  If the date is 2017-01-01, one will be added to the year upon
  insert into the DB2 table..and the data will become 2018-01-01.
  
  If the date is 9999-01-01, one will be added to the year upon
  insert into the DB2 table...and the data will become
  10000-01-01 and give SQL error because 10000 is not a valid
  year.
  

Local fix

• Define the output column as DATE and TIMESTAMP respectively.
  
  1) Since DATE is an internal format you'll also need to specify
  the transformation function TAG.TO.DATE(Hard_Coded_Flat.TSP).
  https://www.ibm.com/support/knowledgecenter/en/SSZJPZ_11.3.0/com
  .ibm.swg.im.iis.ds.serverjob.dev.doc/topics/r_dsvjbref_Date_Tran
  sforms.html
  
  So the two changes you'll need to make to this job is:
  
  - Change the SQL Type of the output column to "Date" and
  
  - Add TAG.TO.DATE(Hard_Coded_Flat.TSP) in the transformer.
  
  2) The format expected of TIMESTAMP is YYYY-MM-DD HH:MM:SS.SSS.
  So the two changes you'll need to make to this job is:
  
  - Change the SQL Type of the output column to "Timestamp" and
  
  - Change the the formatting on your input sequential file to
  YYYY-MM-DD HH:MM:SS.SSS. as an example: 1975-02-16
  13:00:00.000000
  Currently this is the format you have instead:
  1975-02-16-13.00.00.000000
  

Problem summary

• Converting certain data from CHAR or VARCHAR to DATE or
  TIMESTAMP type the server job insert fails
  

Problem conclusion

• We made some code changes in server connectors framework to
  correct the year format so that connector sends the correct
  information to external resources.
  

Temporary fix

• The issue was not seen if the same datatype exists at both
  design & external schema
  

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

• RB50 PSY

     UP

• RB70 PSY

     UP