Issue with Write-Back to Legacy System via ODBC Connection in Azure synapse

Question

Issue with Write-Back to Legacy System via ODBC Connection in Azure synapse

Harikrishnan Arayakeel 0

Hi, I'm attempting to perform a write-back operation to a table in a legacy system using an ODBC connection. The DSN is configured in ODBC mode, which supports both read and write operations. We’re able to preview the data successfully using the write-back function.

This same ODBC connection is also used to pull data into synapse DigiPool for transformations, which works fine.

However, we’re encountering an error during the write-back process. I’ve attaching a error code here for reference.

Has anyone faced a similar issue or have suggestions on how to resolve this?

Thanks in advance!


{ "errorCode" : "2200"
	,"message" : "Failure happened on 'Sink' side. ErrorCode=UserErrorOdbcOperationFailed,'Type=Microsoft.DataTransfer.Common.Shared.HybridDeliveryException,Message=ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0913 - Row or object <table_name> in <library_name> type *FILE in use.,Source=Microsoft.DataTransfer.ClientLibrary.Odbc.OdbcConnector,''Type=Microsoft.DataTransfer.ClientLibrary.Odbc.Exceptions.OdbcException,Message=ERROR [HY000] [IBM][System i Access ODBC Driver][DB2 for i5/OS]SQL0913 - Row or object <table_name> in <library_name> type *FILE in use.,Source=CWBODBC.DLL,'"
	,"failureType" : "UserError"
	,"target" : "Writeback data <target_name>"
	,"details" : [] }

1 answer

Your answer

Answer 1

Hi Harikrishnan Arayakeel

Thanks for posting your query on QnA!

It looks like you're experiencing a write-back issue to a legacy system via an ODBC connection in Azure Synapse. The error message you're seeing, SQL0913 - Row or object <table_name> in <library_name> type *FILE in use, suggests that the table you're trying to write to is currently locked or in use by another process, which may be causing the write-back operation to fail.

DB2 for i5/OS enforces object-level locks. The error SQL0913 means that another job or uncommitted transaction holds an exclusive lock on the target file (table), preventing concurrent write access from the Synapse sink.

Here are a few things you might can check:

Check for Open Transactions: Ensure that there are no open transactions on the table that might be locking it. You can do this by checking the session list on your DB2 instance.
Concurrent Access: Make sure that there are no other applications or users that might be accessing the same table at the same time. If possible, try running your write operation during a period of low activity.
Connection Pooling: Sometimes, connection pooling can lead to unexpected conflicts. Ensure that each connection is properly closed after its usage to avoid unforeseen locks.
Driver Version: Confirm that you're using the latest ODBC driver for the DB2 connection. An outdated driver may cause compatibility issues.
Alternative Methods: If possible, try using a different method to perform the write operation, such as a batch update or inserting records in smaller chunks, to see if the problem persists.

Verify DB2 Locks on the i5/OS Side

Ask your DB2 administrator to run the IBM i command:

WRKOBJLCK OBJ(library_name/table_name) OBJTYPE(*FILE)

Review the output for any job holding *EXCL (exclusive) or *SHR (shared) locks.

Alternatively, run SQL on the IBM i:

SELECT JOB_NAME, JOB_USER, LOCK_STATE, OBJECT_LIBRARY, OBJECT_NAME FROM QSYS2.LOCK_INFO WHERE OBJECT_LIBRARY = 'library_name' AND OBJECT_NAME = 'table_name';

Look for LOCK_STATE = ‘EXCL’.

If you're still running into issues after checking these points, here are a few follow-up questions to gather more details:

What version of the ODBC driver are you using to connect to the legacy system?
Are there any other operations happening on the legacy database at the time you're trying to perform the write-back?
Have you tried performing the write operation from a different client or application to see if the issue is specific to Azure Synapse?
Could you provide any additional context about the legacy system and its current load or state?

If the external write test succeeds but Synapse still errors:

• Verify the IR node where the DSN is installed matches the same driver version and .ini settings.

• Review ODBC trace logs for intermittent SQL0913 entries. - If external write also fails, the issue is purely DB2-side; continue to clear locks or adjust DB2 job schedules.

Feel free to share more details, and we can continue troubleshooting! Hope this helps!

Please "Accept as Answer" if the answer provided is useful, so that you can help others in the community looking for remediation for similar issues.

Thanks

Pratyush

Pratyush Vashistha 1,525 Reputation points Microsoft External Staff Moderator

2025-08-25T02:01:29.3833333+00:00

Hi Harikrishnan Arayakeel

Just checking to see if you have a chance to check my previous response and helped, do let me know if you have any further questions on this.

Please "Accept as Answer" if the answer provided is useful, so that you can help others in the community looking for remediation for similar issues.

Thanks

Pratyush
Pratyush Vashistha 1,525 Reputation points Microsoft External Staff Moderator

2025-08-26T03:19:53.8033333+00:00

Hi Harikrishnan Arayakeel

We haven’t heard from you on the last response and was just checking back to see if you have a resolution yet. In case if you have any resolution please do share that same with the community as it can be helpful to others. Otherwise, will respond with more details and we will try to help.

Please "Accept as Answer" if the answer provided is useful, so that you can help others in the community looking for remediation for similar issues.

Thanks

Pratyush
Harikrishnan Arayakeel 0 Reputation points

2025-08-26T11:46:17.9766667+00:00

Hi Prathyush,

Thanks for sharing the info. Just to confirm—do you know if this is a Synapse issue? And does Synapse support write-back via the ODBC driver?
Pratyush Vashistha 1,525 Reputation points Microsoft External Staff Moderator

2025-08-26T12:22:35.36+00:00

Hello Harikrishnan Arayakeel, Thanks for your response!

No, this is not a Synapse issue. The error message you're seeing is being passed through Synapse from the target system, but it is not originating from Synapse.

SQL0913: This is a well-known, specific error code from the IBM DB2 database system. It means that a resource (in this case, your target table, which DB2 sees as a *FILE object) is locked by another process and cannot be modified.

The error proves the write action reaches DB2, but DB2 is blocking it due to locked resources—not due to any inherent Synapse or ODBC limitation.

Also, Azure Synapse Analytics do support write-back operations using ODBC connectors for supported sink systems, assuming the driver and destination database allow DML operations (INSERT, UPDATE, DELETE). The successful data preview and the ability to read data via the same ODBC DSN further prove that the connection and driver setup are correct for both read and write operations.

Conclusion: Synapse is correctly executing the ODBC operation - the error occurs when the ODBC driver attempts to write to the locked DB2 table.

Your successful data preview confirms Synapse-ODBC connectivity is working properly.

Official Documentation for reference: https://learn.microsoft.com/en-us/azure/data-factory/connector-odbc?tabs=data-factory

Please "Accept as Answer" if the answer provided is useful, so that you can help others in the community looking for remediation for similar issues.

Thanks

Pratyush
Pratyush Vashistha 1,525 Reputation points Microsoft External Staff Moderator

2025-08-27T06:02:41.57+00:00

Hello Harikrishnan Arayakeel,

Following up to see if you have a chance to check my previous response and helped, do let me know if you have any further questions on this. Please accept as Yes if the response is helpful so that it can help others in the community.

Thanks

Pratyush
Harikrishnan Arayakeel 0 Reputation points

2025-08-27T07:34:54.93+00:00

Hi Pratyush

Thanks for sharing the information. I'm currently reaching out to our database team to look into this. There might be a slight delay, but I’ll get back to you with the result.

Share via

Issue with Write-Back to Legacy System via ODBC Connection in Azure synapse

1 answer

Your answer