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.

on one hand I'm relieved that you understand the doc's exactly as I did.

On the other hand that doesn't help to resolve this issue, so please forgive me my follow-up question:

if I understand the doc right, there's a certain parameter in the Db2 instance setup which controls the string format for TIME attribute values. Do you happen to know which parameter this is? I would like to have our DBA look it up so that I can adapt the string value accordingly, but he doesn't know off the top of his head which parameter to look for.

yes, I understand that, and indeed I do not care for the internal format. I just hope (according to the documentation link that you have posted) that this format will tell me how I have to format a string so that I can write it to a CHAR attribute.

In PowerCenter you can enter so-called "environment SQL" statements for relational connections to be executed when a DB session is established. In Oracle, for example, you could set some NLS parameters that way for a particular DB session.

Is it possible to set the TIME FORMAT (which, BTW, is set to "ISO" for the affected Db2 instance) to some specific value using such an environment SQL?

For example, let's suppose I would send the following SQL script to a Db2 instance using a CLI client (the target table DEF under TLQ ABC has three columns of which the third is defined as a TIME attribute):

SET CURRENT_TIME_FORMAT='ISO';

INSERT INTO abc.def VALUES( 1, 1, '08.37.50');

INSERT INTO abc.def VALUES( 1, 2, '14:58:23');

If I want to use a "standard" PowerCenter mapping, I would send the two records to a simple relational Db2 target and would insert the SET CURRENT_TIME_FORMAT='ISO' statement as the "Environment SQL" of the respective Db2 connection.

Is that possible? Does such a statement exist? If so, do you happen to know the correct syntax?

Our DBA is really trying to be helpful, but this is outside his area of expertise. Also he just knows that two software packets PowerCenter and PowerExchange are used here, but he doesn't know anything about these software products. :-)