7 Replies Latest reply on Aug 15, 2019 8:20 AM by Nico Heinze

    inserting a string into a TIME column in DB2 z/OS

    Nico Heinze Guru

      Hi all,


      PowerCenter 10.1.1 HF1 on Windows, using PowerExchange for DB2 z/OS (DB2 11, if I'm right).
      (Please don't ask why.) I'm trying to insert strings into DB2 z/OS tables, to be precise into attributes of types DATE, TIME, TIMESTAMP, and TIMESTAMP(9).

      In other words. the target definition in PowerCenter has a VARCHAR(50) while the target tables in Db2 have attributes of types DATE, TIME, TIMESTAMP, and TIMESTAMP(9).

      Writing data to attributes of types DATE, TIMESTAMP, and TIMESTAMP(9) works fine, using strings in ISO formats: '2019-08-13' for DATE, '2019-08-13 14:25:52' for TIMESTAMP, and '2019-08-13 14:25:52.123456789' for TIMESTAMP(9). Works like a charm.

      But writing to a TIME attribute this way simply doesn't work. No matter what format I use for the time values, I always get an error message that the format is invalid. I've tried 14:25:52, I've tried 2:25 PM, I've tried 14.25.52 (dots instead of colons). No way.


      Can anyone tell me in what format I have to deliver the TIME strings to Db2 z/OS?


      Right now I'm using a Date/Time port, but I am trying to insert both invalid and valid strings (like 14:25:52 as a valid example or 14:25:67 as an invalid example), so I can't use a Date/Time port for the time value 14:25:67.


      Thanks and regards,