DB2 Loader and Null indicator

[surfsup]

Howdy all,

We have the following problem with Datastage and DB2.

Using DB2/Enterprise targets, data needs to be loaded with the DB2 loader with ASCII delimited format = false (aka fixed length). The problem is that, for null values of timestamp columns, the Datastage outputs the data as a string of 26 asterix characters (25 is the timestamp mask length and 1 is the null indicator character).

However, the DB2 does not seem to use the same character for nulls and, when attempting to load data it would treat it as any other character, thus failing the timestamp mask validation.

We figure we need to either change the default DB2 loader null indicator or change the null indicator from DataStage.

The official Datastage guides do specify a place where to configure the null characters: the mainframe tab. However, that is enabled only for mainframe jobs, and we do not use them.

Any thoughts?



$ L3191N The field in row "1", column "407" which begins with
"**************************" does not match the user specified DATEFORMAT,
L3191N The field in row "1", column "407" which begins with
"**************************" does not match the user specified DATEFORMAT,
TIMEFORMAT, or TIMESTAMPFORMAT. The row will be rejected.


Kind regards,
Adi.

[nirdesh2]

Before loading the data into DB, use SetNull() function to set the null values if date/timestamp is null or if it is not valid date.

[surfsup]

Before loading the data into DB, use SetNull() function to set the null values if date/timestamp is null or if it is not valid date.

We already tried this.

The value gets passed to the DB2 Enterprise stage as null and it is this stage that writes the null indicator character to the file, but fails to specify to the DB2 loader what this character is.

[sima79]

You can add the environment variable APT_STRING_PADCHAR with the ASCII value for null (0x00) to the job or set it in DataStage Administrator so that the loader will pad the string with null as opposed to space

[surfsup]

You can add the environment variable APT_STRING_PADCHAR with the ASCII value for null (0x00) to the job or set it in DataStage Administrator so that the loader will pad the string with null as opposed to space

Hello,

The APT_STRING_PADCHAR is already set to 0x00. However, according to DS documentation, it is used when writing varchar values to char columns in DB2 and we confirmed it is used in that case (i.e. we wrote some varchar values to char columns). However, it is not also used for timestamps.

Remember, I think there are two characters here: the indicator that says the column value is null (which i hope is the solution here) and the one that DS uses to pad the line (so that the next column value starts at the same position for every row).