Warning While updating a DB2 table
Moderators: chulett, rschirm, roy
Warning While updating a DB2 table
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
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
somaraju
-
- Participant
- Posts: 54607
- Joined: Wed Oct 23, 2002 10:52 pm
- Location: Sydney, Australia
- Contact:
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
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
somaraju
-
- Participant
- Posts: 3593
- Joined: Thu Jan 23, 2003 5:25 pm
- Location: Australia, Melbourne
- Contact:
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.
Make sure you have a reject link off your DB2 stage to catch and rejected rows as per the FAQ.
Certus Solutions
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
Blog: Tooling Around in the InfoSphere
Twitter: @vmcburney
LinkedIn:Vincent McBurney LinkedIn
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
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
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
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
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]
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]
Regards
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
Siva
Listening to the Learned
"The most precious wealth is the wealth acquired by the ear Indeed, of all wealth that wealth is the crown." - Thirukural By Thiruvalluvar
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.
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.
-craig
"You can never have too many knives" -- Logan Nine Fingers
"You can never have too many knives" -- Logan Nine Fingers
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.
Creativity is allowing yourself to make mistakes. Art is knowing which ones to keep.
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
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
somaraju