DB2 Insert using DB2 Plug DOES NOT WORK

lagrangeusa · Post by **lagrangeusa** » Tue Jul 26, 2005 4:15 pm

Greetings,

I am trying to insert a value into a DB2 table that has a column property of DATE. I have reviewed all the posts on this message board and tried the ones that were appropriate but it continues to fail with a NULL error.

I have tried to make this as basic as possible, by hardcoding the value as a string "2005-07-26", but I still get the error. I have tried "2005/07/26" Fails. I have tried "07-26-2005" Fails. I have tried "07/26/2005". Fails

Example of the error message:

LoadDateDimension..TBPTS01_DATE: DATE_KEY_ID = 25890 DATE_DT = NULL JULIAN_DT = 7 WEEK_DAY_TX = Thursday MONTH_TX = January YEAR_NB = 2010 QUARTER_NB = 1 DATE_DAY_NB = 7 DATE_TX = Jan 07, 2010 YEAR_MONTH_NB = 1 MONTH_YEAR_TX = 201001 DTCC_HOLIDAY_IN = N CRT_TS = 2005-7-26 18:4:14.0

This should be an incredibly simple thing to do. What format does BD2 want?

Help...anyone!

Dean

pnchowdary · Post by **pnchowdary** » Tue Jul 26, 2005 4:39 pm

Hi,

Check the datatype for the DATE_DT column in the DB2 Table. Sometimes, though its called a DATE column, it is of timestamp format.

lagrangeusa · Post by **lagrangeusa** » Tue Jul 26, 2005 5:20 pm

I think the problem is related to the date being a string and DB2 wanting a data format. I get the following error:

LoadDateDimension..XfrmDate: At row 1, link "TBPTS01_DATE", while processing column "DATE_DT"
Value treated as NULL
Attempt to convert String value "2005-7-28" to Date type unsuccessful

How can I get ANY value in a date format?

Dean

diamondabhi · Post by **diamondabhi** » Tue Jul 26, 2005 7:29 pm

Hi lagrangeusa,
Use Tag.To.Date and I think it would sove ur problem.

Thanks,
Abhi.

Sainath.Srinivasan · Post by **Sainath.Srinivasan** » Wed Jul 27, 2005 4:18 am

Look for IConv.

elavenil · Post by **elavenil** » Wed Jul 27, 2005 6:28 am

Date can be loaded into a DB2 table if the column's data type is defined as char(10) and it required to be the same format as defined in the database.

HTWH.

Regards
Saravanan

lagrangeusa · Post by **lagrangeusa** » Wed Jul 27, 2005 7:09 am

Well, I finally solved the problem after much fumbling around. It turns out that using Data Stage, DB2 does not want the date in a normal date format such as YYYY-MM-DD (2005-07-27). It actually wants the ICONV value of the date.

When we first encountered the problem, a logical first step was to manually insert a row into the DB2 table using SQL (we did this through Rapid-SQL). When we entered the value for the date, we used '2005-07-26'. Simple. The row loaded perfectly.

As I continued to try and load the data through Data Stage, I would continually get an error from DB2 that it could not convert a string to a date. I was trying to pass the same format for the date, that is, '2005-07-26'.

I did find something interesting though which led me to the final solution. Using the single row that I had manually inserted, and using the DB2 plugin stage, I went to view the data through the target plug-in stage. When I saw the data that was being displayed, the value in the date field was shown as 13722 (2005-07-26). When I would review the data through Rapid-SQL or even directly through SQL on UNIX, it would only show the date, '2005-07-26'.

There was a post on this message board that suggested just sending the value from ICONV and not the actual date. I initially tried this but it failed because of the value that I was using as a date. The incoming date value was from a Sybase table and the format of that date data was:

'2005-07-25 00.00.00.000'

When I had first tried the solution I just did this:

ICONV(Sybase.date_field,"D-YMD[4,2,2]")

This failed. As you know, Data Stage does not really care about data types, so I applied a Left function to the date value, so I would only take the first 10 characters and this solved the problem. The final solution for me was:

ICONV(Left(Sybase.date_field,10),"D-YMD[4,2,2]")

SOme of the postings on this board had suggested doing a ICONV then an OCONV to finally format the date in the correct format. I cannot see how this would work, as I tried this option many times (and in many ways), but possibly they were using Data Stage EE or had their DB2 environments setup differently.

Ultimately, the manual insertion of the row outside of Data Stage led to my solution of this problem. If I never would have entered the row and viewed it through the DB2 plugin stage, I think I would have spent significantly more time on this issue.

Thanks to everyone who posted suggestions. I hope my final result helps someone else.

Dean La Grange

chulett · Post by **chulett** » Wed Jul 27, 2005 8:01 am

Yah, it's been posted here more than once that the DB2 plugin is different from any other DB plugin in that it wants dates in internal rather than external format for some reason. As to the reason, you'd have to ask whomever wrote the darn thing.

Sorry you had to go through all that.

gpatton · Post by **gpatton** » Wed Jul 27, 2005 8:07 am

If you use char(10) for dates and varchar(26) for timestamps for both reading and writing to DB2 through the plug in it works well.

This works with versions up to 6. I haven't yet tested it in version 7.