DB2 Date Column - Displays Incorrect in view Data

nandha · Post by **nandha** » Thu Sep 01, 2005 2:28 pm

Hi:
I imported the metadata from DB2 tables in mainframe using the Plug IN DSDB2. I have a date field defined as
LAST_MAINT_DATE DATE,
in DB2.

Metadata also shows as
LAST_MAINT_DATE DATE, 10

When I view data , I am seeing values as "10767 (1997-06-23)" .

If I change the SQL Type to Char , then the data is displayed in YYYY-MM-DD format.

1. Should i define he metadata as Char , though it is date field ?
2. Is there any setting I am missing ?.

Searched the forum & manuals couldn't get help.

Thanks for your responses in advance.

Rgds,
Nandha

gradkarthik · Post by **gradkarthik** » Thu Sep 01, 2005 3:22 pm

Hi Nandha,
From what i saw,
10767 is the internal representation of the date 1997-06-23 which datastage calculates when you use the iconv function.
check your code and see if you are using iconv anywhere to perform some conversion of your LAST_MAINT_DATE field.

gpatton · Post by **gpatton** » Thu Sep 01, 2005 5:50 pm

One system I worked on with DB2 used char(10) as the definition for all date fields and Varchar(26) for timestamps with a format of YYYY-MM-DD hh:mm:ss.sssss

This works fine and is readily visible for debugging, but you must be consistent throughout the processing.

jdmiceli · Post by **jdmiceli** » Tue Sep 12, 2006 11:15 am

Howdy!

My understanding of the DB2 drivers are that they only display dates in the internal format when using View Data. If you want to see it differently then you would need to convert it using ICONV (I think). Also, you won't be able to do this in View Data of the driver, but you could place a derivation that will do it for you.

Bestest!

kcbland · Post by **kcbland** » Tue Sep 12, 2006 11:27 am

Ahhh, the DB2 plugin. It works differently from other stages like Oracle and ODBC. If the datatype is DATE, it automatically uses ICONV inside the stage. The only way to prevent this is to switch the metadata to CHAR, which relies on the NLS setting of the database for the format, or TIMESTAMP, which makes it look like an ISO date.

You'll have this same issue with the Informix and Sybase stages, both reading and writing.

Make sure when loading you change the metadata as well. I recommend always toggling the DATE to TIMESTAMP.