DSXchange

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

Timestamps have a minimum of 19 characters.

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

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.

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.

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

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

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]

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.

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.

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