Linked server to DB2 table locks

Question

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.

enter image description here enter image description here 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.

• mustaccio: How exactly do you determine that "all the DB2 tables that are used by the linked server are locked"? Also, make sure you issue a commit at the end to release whatever lock have been held.
• Riccardo Francescato: @mustaccio i added some screenshot from AS400 consolle,i do not understand how to commit since i use OPENQUERY ad i perform only a select, without opening a transaction or distributed transaction.

Answer 1

If by "table lock" you mean a "shared for read lock" (*SHRRD)... then it's likely that the cursor has been pseudo closed

Pseudo closed cursors are a key part of a performance optimization feature of IBM DB2 for i SQL. When an application closes a cursor, DB2 for i normally closes the cursor and closes the file, deleting the ODP (Open Data Path). If the application runs the same statement multiple times, each new execution requires a full open of the target file. The idea behind pseudo closed cursors is to not fully close the cursor and file but rather to cache the cursor for potential future use. The cursor is left in a soft closed (or pseudo closed) state. When the cursor is pseudo closed, the underlying file and ODP are left open. All record locks are released; however, a shared lock still appears on the file. DB2 for i can then reuse the cursor as needed without the overhead of a full open of the file. DB2 for i can also decide to hard close the pseudo closed cursor when needed.

They should not cause a problem unless you have a process that needs exclusive access, such as one that uses Clear Physical File Member (CLRPFM)

Such process should be avoided now-a-days, given the 24/7 nature of most business.

If you have to have an exclusive process, modify the process to request the locks to be released if needed... ALCOBJ OBJ((MYFILE *FILE *EXCL)) CONFLICT(*RQSRLS)

• Riccardo Francescato: thanks for your response, i added some more specific info in the question about locks and ODBC configuration. I asked to te AS guy to add the row that you suggested to the program.
• Charles: @RiccardoFrancescato likely connection pooling explains the other "locked" tables. This is normal with pseudo-closed cursors. Why do you want to stop it?
• Riccardo Francescato: pseudo closed cursors are a big problem since they do not release the shared locks and RPG procedures are not able to delete/update rows in the tables. In this specific chase the RPG procedure is designed to delete all old data. Since the table is locked the procedure do not delete data and the table is growing indefinitely.
• Charles: Either use the alcobj command before the clrpfm as mentioned above or use an sql delete
• Riccardo Francescato: I asked to the AS400 guy to modify the procedure by adding the command you suggested and at the moment is working correctly. Thanks.