Date/TimeStamp Format compatibility Db2 & DataStage

DB2 stores Date in some internal format which is not a string but some integer. When you pass a date datatype field to a DB2plugin stage(to load it into DB) then the plugin will silently do an ICONV coming and OCONV going.[The DB2 plugin works differently than Oracle or ODBC stages.]

The problem comes when the data type is not of type Date. If using datastage you directly pass a char/varchar type column containing dates to the DB2 date field then it will give an error. Db2 will not understand the date format and will insert NULL values.

There are two ways to go around this problem.
1) Convert the DB2 datatype from date to TimeStamp. Timestamp can take string type inputs(just check if date is suffixed with a space followed by 00:00:00 for time values).

2) Convert the string type date into DB2 understandable format. The following code will do this:

ICONV(InLink3.birthdate,"D-YMD")

This code assumes that input column(InLink3.birthdate) is of type 'yyyy-mm-dd'. Below are some examples for different input formats:

Conversion Expression Internal Value X = Iconv("31 DEC 1967", "D") X = 0 X = Iconv("27 MAY 97", "D2") X = 10740 X = Iconv("05/27/97", "D2/") X = 10740 X = Iconv("27/05/1997", "D/E") X = 10740 X = Iconv("1997 5 27", "D YMD") X = 10740 X = Iconv("27 MAY 97", "D DMY[,A3,2]") X = 10740 X = Iconv("5/27/97", "D/MDY[Z,Z,2]") X = 10740 X = Iconv("27 MAY 1997", "D DMY[,A,]") X = 10740 X = Iconv("97 05 27", "DYMD[2,2,2]") X = 10740

TimeStamps

Regarding TimeStamps, timestamps are nothing but string equivalents of the form “YYYY-MM-DD HH:MM:SS:ssssss”

So it does not matter whether the input field is of timestamp format or char/varchar type as long as it follows the above structure. If the input contains only the date part then the time part is automatically taken as 00:00:00(not sure about DB2).

Extracting Date from TimeStamp:

- Just keep the first field of the input.

Field(InLink.Timestamp, " ", 1, 1)

Or Just keep the first 10 characters – that include dashes(‘-‘)

Left(InLink.TheTimestamp, 10)

Similarly if you want to convert a date into timestamp then following code would do –

Oconv(Date() , "D4-YMD") : " " : Oconv(time(), "MTS")

Date() & Time() return current date and time values. The above code convert internal date into “yyyy-mm-dd” format and time into “hh:mm:ss” and inserts a space(“ “) in between. Other usage of OConv is given at the end of this article.

"0000-00-00 00:00:00" as a TimeStamp

Another interesting point to note is that some databases like MySQL support "0000-00-00 00:00:00" as a valid time stamp value or "0000-00-00" as a valid date value. DB2 doesn’t.

So while loading data from such databases to DB2, special care should be taken to handle such type of data. Whenever using Date datatype attributes do check for zero values.

if DSLink3.hire_date MATCHES "0000-00-00 00:00:00" then Oconv(Date() , "D4-YMD") : " " : Oconv(time(), "MTS") else DSLink3.hire_date

The above code inserts current date/time values for zeros if required or use “” for NULL values.
Similarly for date use:

if DSLink3.birth_date MATCHES "0000-00-00" then "" else ICONV (DSLink3.birth_date, "D-YMD")

Other usage of OConv

Conversion Expression External Value X = Oconv(0, "D") X = "31 DEC 1967" X = Oconv(10740, "D2") X = "27 MAY 97" X = Oconv(10740, "D2/") X = "05/27/97" X = Oconv(10740, "D/E") X = "27/05/1997" X = Oconv(10740, "D-YJ") X = "1997-147" X = Oconv(10740, "D2*JY") X = "147*97" X = Oconv(10740, "D YMD") X = "1997 5 27" X = Oconv(10740, "D MY[A,2]") X = "MAY 97" X = Oconv(10740, "D DMY[,A3,2]") X = "27 MAY 97" X = Oconv(10740, "D/MDY[Z,Z,2]") X = "5/27/97" X = Oconv(10740, "D DMY[,A,]") X = "27 MAY 1997" X = Oconv(10740, "DYMD[2,2,2]") X = "97 05 27" X = Oconv(10740, "DQ") X = "2" X = Oconv(10740, "DMA") X = "MAY" X = Oconv(10740, "DW") X = "2" X = Oconv(10740, "DWA") X = "TUESDAY"

See also

Fmt (string, format)- command too for formatting data for output.
FmtDP (string, format [, mapname] ) - NLS mode, formats data in display positions rather than by character length.