Warning While updating a DB2 table

somu_june · Post by **somu_june** » Wed Mar 15, 2006 3:02 pm

Hi,

Iam facing a problem when iam updating a record with a current time stamp in a columm. Iam getting a waring like this . Iam using DB2 api stage

ZWV_FACTOR_RC_UPDATE,1: Warning: FEMS_REQUEST_CHANGE_UPDATE_DELETE.ZWV_FACTOR_RC_UPDATE: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001

I checked columms in my database there is a columm (SHAD_UPDATE) with sql (timestamp) and length (10) but when I gave expression as currenttimestamp() in transformer it will give more than length (10) . How to over come this problem

Thanks,
Somaraju

ray.wurlod · Post by **ray.wurlod** » Wed Mar 15, 2006 3:45 pm

Timestamps have a minimum of 19 characters.

somu_june · Post by **somu_june** » Wed Mar 15, 2006 4:41 pm

Hi Ray,

Is this error due to timestamp or because 4 rows are going to DB2 table and they are not updating . I gave user define sql as update emp set empno=?,ename=? WHERE deptno=? and salary=?; there is a primary composite key. I gave only two of them in where clause or do if I gave all the columms it is saying row rejected warning. How to update a columm in db2

Thanks,
Somaraju

vmcburney · Post by **vmcburney** » Wed Mar 15, 2006 4:50 pm

DB2 timestamp columns do not have a length, they are simply of type Timestamp. The DB2 plugin table importer should bring them into DataStage with a length of 26 and a scale of 6. Try increasing your length and scale in your DB2 stage for that field.

Make sure you have a reject link off your DB2 stage to catch and rejected rows as per the FAQ.

somu_june · Post by **somu_june** » Wed Mar 15, 2006 4:59 pm

Hi Burney,

I tried with larger number but I am getting same error and the rows are rejecting. I had a timestamp of 10 , I gave it as 40 but there is still a problem

Thanks,
Somaraju.

somu_june · Post by **somu_june** » Wed Mar 15, 2006 8:25 pm

Hi,
Removed that columm and Iam getting the same error.

I gave user defined sql in DB2 api stage like this

UPDATE #tgtschema#.ZWV SET DATBI=?,SHAD=? WHERE CUST in ('1','2') AND CHOPT in ('O','R') butin columms in db2 /API stage .I gave only DATBI and SHAD and I didnot provide CUST AND CHOPT in columms metadata . Iam getting this warning because DATBI is a key columm and Iam updating it and Iam not mentioning the key columm in WHERE clause . I think that is the problem for me. Please correct me if iam wrong can I have DATBI same columm in DB2 / api stage so that I can map the value directly from transformer so that I can give in WHERE clauses . My question can have columms with same name in target DB2 .Plz help me

ZWV_FACTOR_RC_UPDATE,3: Warning: FEMS_REQUEST_CHANGE_UPDATE_DELETE.ZWV_FACTOR_RC_UPDATE: [IBM][CLI Driver] CLI0109E String data right truncation. SQLSTATE=22001

Thanks,
Somaraju

somu_june · Post by **somu_june** » Thu Mar 16, 2006 4:13 pm

Hi,

Please correct me if Iam wrong . Can I Update a key columm by using a datastage my key columm is DATBI as I already mentioned above.
I have already said my above problem . DATBI is a key columm and Iam getting this error with this columm I gave in DB2 as user defined sql like this

UPDATE #tgtschema#.ABC SET IND=?,TS=?,DATBI=? WHERE CUSTTYPE=? AND CHOPT=? AND DATBI ='99991231'; HERE DATBI is a key columm in DB2 table . I tried by making a key and not a key in transformer and in DB2 stage but I had the same error as I mentioned above. Can I update a key columm with out mentioning in Where clause. Iam getting output by sql but I am not getting an output from datastage. Please help me

Thanks
somaraju

rasi · Post by **rasi** » Thu Mar 16, 2006 9:44 pm

Somu

You cannot update a key column which is in SET and in WHERE clause.

Having said that it is possible to do an update without key column eg.
" UPDATE #tgtschema#.ABC SET IND=?,TS=?,DATBI=? WHERE CUSTTYPE=? AND CHOPT=? "

But you have to make sure that each row you update will not violate unique constaint.[/quote]

chulett · Post by **chulett** » Fri Mar 17, 2006 6:20 am

Why custom sql? It should hardly ever be needed, especially on the target side of a job. All it's going to do is get you into trouble, more so when you are new at this and unsure of what you are doing.

Let the stage generate the sql for you. For something simple like needing a constant of '99991231' in one field of the where clause, add a extra key column to the stage and set it to that value in the derivation.

DSguru2B · Post by **DSguru2B** » Fri Mar 17, 2006 6:49 am

Possibly there could be constraints on the table that you are trying to load. Make sure that you meet all of them and use a modify stage to convert the datatype, atleast you will be able to keep a track of the datatypes you are changing.

somu_june · Post by **somu_june** » Fri Mar 17, 2006 10:55 am

Hi,

Thank you every one my problem was solved. you people are really helping me to come out from my troubles in quick time. I made the columm as non key in stage and updated that key columm and in where clause I gave another columm value as a key columm and my target was achieved.

Thanks,

Somaraju