Attempting to update DB2 from ADF using pre-copy script of copy activity

Question

Attempting to update DB2 from ADF using pre-copy script of copy activity

MahderNegash-7441 1

The objective is to be able to update Phoenix data from ADF using ODBC connector.

I am considering to use Pre-Copy script feature in Azure Data factory Copy activity.

Using this feature I am able to update the records I wanted but the copy activity shows failure with the below error.

Error code2200
Failure typeUser configuration issue
DetailsFailure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [23000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0803 - Duplicate key value specified.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [23000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0803 - Duplicate key value specified.,Source=CWBODBC.DLL,'

The query I used is
UPDATE
"CMDDB"."NCC_DETAIL_CREDIT"
SET
NCC_STATUS = 'y'
WHERE NCC_TYPE = 'SIP'

I am assuming this error is application error from DB2, and there is some key (created by unique sequence number generator) being reset whenever the copy activity is run. How can make this work what am I missing?

2 answers

Your answer

Answer 1

Hi anonymous user,

Thanks for using Microsoft Q&A forum and posting your query.

From the error message shared, the issue seems to be more of DB2 side. My understanding is that one of your field/column has the primary key constraint and if you try to update that field/column with the value which is already present in the table then it will give you such error message

I would recommend you to please refer to these related thread for troubleshooting:

https://www.ibm.com/support/pages/sql0803-duplicate-key-value-specified

In case if you further need assistance in troubleshooting this, I would recommend you to please reach out in IBM community forum for better assistance as it is more related to DB2.
IBM community forum: https://www.ibm.com/mysupport/s/forumshome

Hope this info helps.

----------

Please don't forget to click on and upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-12-22T00:25:44.31+00:00

Hi anonymous user,

Just checking in to see if the above suggestion was helpful. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.
MahderNegash-7441 1 Reputation point

2021-12-22T17:00:12.517+00:00

Thank you for responding but I would live to know if the way I am following is the best choice when it comes to updating records in DB2 from ADF. If I run the above update statement directly in the environment, Data Grip, it won't throw this error. This error is in copy activity only. What is the best option out there for the requirement I have - updating existing records in DB2, no copy or insert is needed. Thanks

Answer 2

KranthiPakala-MSFT 46,642 Microsoft Employee Moderator

Hi anonymous user, Thanks for clarifying about requirement. Your requirement is only to update the records and no copy/insert of records is needed - which is why you are just using the Pre-copy script in ADF copy activity. Please correct if I misunderstood your requirement.

I don't think copy activity is appropriate in this use case. Copy activity is intended to copy/insert the data from a source to a destination.
But as per your requirement, in order to just update the DB2 records, I would recommend you to use Lookup activity in ADF which would help achieve your requirement. Please make sure that your query returns one and exact one result set. Otherwise, Lookup activity fails. Lookup activity expects a response from the query executed.

Below are the supported capabilities of Lookup activity:

Hope this info helps. Do let us know if you have further query.

----------

Please don't forget to click on and upvote button whenever the information provided helps you. Original posters help the community find answers faster by identifying the correct answer. Here is how
Want a reminder to come back and check responses? Here is how to subscribe to a notification
If you are interested in joining the VM program and help shape the future of Q&A: Here is how you can be part of Q&A Volunteer Moderators

KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-12-24T10:02:03.923+00:00

Hi anonymous user,

Just checking in to see if the above suggestion was helpful. If it answers your query, please do click “Accept Answer” and/or Up-Vote, as it might be beneficial to other community members reading this thread. And, if you have any further query do let us know.
MahderNegash-7441 1 Reputation point

2021-12-27T17:06:58.837+00:00

I was initially trying to use LookUp activity. but it was consistently failing with the below error, but it was updating the records I wanted to update. I never seen success status on activity run. That's why I am trying to use copy activity now.

Error code2100

Failure typeUser configuration issue

Details Failure happened on 'Source' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [42000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token ; was not valid. Valid tokens: <END-OF-STATEMENT>.,Source=Microsoft.DataTransfer.Runtime.GenericOdbcConnectors,''Type=System.Data.Odbc.OdbcException,Message=ERROR [42000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0104 - Token ; was not valid. Valid tokens: <END-OF-STATEMENT>.,Source=CWBODBC.DLL,'

Also what do you mean by "Please make sure that your query returns one and exact one result set" ?
I am running this simple update statement inside the lookup activity.
UPDATE
"CMDDB"."NCC_DETAIL_CREDIT"
SET
NCC_STATUS = 'Y'
WHERE NCC_ID = 2;
Thanks for helping.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2021-12-29T19:28:21.557+00:00
Hi, thanks for getting back. The reason for Lookup activity failure is that it is expecting a response from your update query. If your update query doesn't return any value, then the update takes place in database table but in ADF pipeline the lookup activity fails as there is no response from the query execution, and hence the pipeline fails. You may have to modify your query to return a value to make the lookup activity successful.

For example you may have to have a query like below (note this is an example SQL query):

UPDATE tbl_user SET amount = @amount := amount-'$amount' WHERE id='$id' LIMIT 1; SELECT @amount;

Hope this clarifies. Do let us know if you have further query.
MahderNegash-7441 1 Reputation point

2021-12-30T16:15:44.36+00:00

Okay. That gives me the error below, which I don't understand.
Message=ERROR [42000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0199 - Keyword SELECT not expected. Valid tokens: USE SKIP WAIT WITH FETCH LIMIT ORDER OFFSET.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-01-03T17:53:00.89+00:00

Hi anonymous user,

Thanks for getting back. Could you please confirm if you are getting this error when you execute the query through ADF only or event your are getting the same error when you execute the query directly in the environment? If you see this error even when you execute in environment then there is something wrong with your query and would recommend to reach out in IBM DB2 community forum for further assistance. But if you only experience this error in ADF copy activity, please let us know.
KranthiPakala-MSFT 46,642 Reputation points Microsoft Employee Moderator

2022-01-05T00:06:37.49+00:00

Hello anonymous user,

Following up to see if you have got a chance to confirm the above testing? If so could you please share your findings?

Thanks

Share via

Attempting to update DB2 from ADF using pre-copy script of copy activity

2 answers

Your answer