Updating DATE and TIMESTAMP columns in DB2

Daddy Doma · Post by **Daddy Doma** » Sun Jul 10, 2005 10:40 pm

G'day

A really weird one, this. I have a number of existing DataStage jobs that update a timestamp field in a DB2 database using a simple update statement, e.g.

UPDATE
#DB2#.TABLE
SET
END_TSP = ORCHESTRATE.END_TSP
WHERE
KEY = ORCHESTRATE.KEY

The metadata of the updates is set in a preceeding Transformer stage.

The Transformer and DB2 Update stages in my new job is identical to my existing jobs, but I cannot get the update to occur.

I have used a Lookup stage to confirm that the records I want to update exist in the target and match my data.

The only way I can get the records to update is to delete all metadata related to DATE and TIMESTAMP columns in the update SQL. But then three fairly important columns miss out...

Has anyone had any experience with a problem of this ilk before?

Zac.

ArndW · Post by **ArndW** » Mon Jul 11, 2005 1:12 am

Zac,

Do you have column propagation enabled? Exactly what error are you getting?

Daddy Doma · Post by **Daddy Doma** » Mon Jul 11, 2005 1:44 am

Actually, I no longer get the problem - we can chalk this one up to human error. My data was corrupt.

Unbeknownst to me, the source data column was NULL. It was coming from a database that does not allow nullable values in that column, but during unit testing, the value in the staging dataset had been set to this.

The target column does allow nulls. But the preceeding Transformer stage, whilst trying to execute a StringToDate function, had nothing to work with and the resulting update fell over.

Unbelievable that I missed something as simple as this for so long - a lot of time wasted. Must have been gremlins in the system or the effects of too long a weekend...