I have a problem with a SQL linked server connected to a DB2 database on an AS400 IBM iSeries.
I configured an ODBC connection from the ODBC Data Source utility. Once configured the ODBC, i created the LinkedServer and start using it for querying the DB2 database using
SELECT * FROM OPENQUERY([LINKED_SERVER], 'SELECT * FROM [TABLE] FOR READ ONLY').
Once the query is executed, the DB2 table is locked and when the query ends the lock is not released. I temporarily solved the issue setting the ODBC connection type as read only and not as default.
Also all the DB2 tables that are used by the linked server are locked at every query even if they are not involved in the query.
Can someone help me figuring out how to not lock all the tables and set the linked server to read/write and not in read only mode. Like in the image, i executed a query only on SHPAV20F but locks are taken also for TTB03ACF and TTB0301P that are not involved in the query.
ODBC settings for commit mode is *CHG, PREFETCH for query data is enabled and LAZYCLOSE is disabled.