My Java application performs an SQL query including all rows from a table that's sometimes being modified at the time of the query executing.
Thus, I'm experiencing SQL Exceptions because the default behaviour in Dynamic Server is "LOCK MODE SET TO NOT WAIT".
I have set the lock mode for the table to "LOCK MODE ROW", to minimize the lockng scope.
What i need now is to set "SET LOCK MODE TO WAIT 5" on each Connection created against the database. Now my questions are fairly simple.

1. Is it necessary to execute an SQL call for every single JDBC connection against the database?
2. Is there a way to make all connections to that specifix database obey the "WAIT 5" rule?

Please note that I cannot set the "WAIT 5" on the entire dbms instance (even if it was possible) since there are other databases not needing this setting.

TIA!

Regards
Thor

Hi Thor,

I believe it has to be issued for each connection. As far as I'm aware there is no ONCONFIG or environment variable that can turn it on for all connections to either a particular db or informix server.

Yes, as far as I am awre each session must have lock mode set.

There is an environment variable IFX_TABLE_LOCKMODE which sounds like it might be in this league, however I cannot find any information on it other than a brief reference to it in the SQL Syntax Guide under the SET LOCK MODE section.

The IFX_TABLE_LOCKMODE env. variable and DEF_TABLE_LOCKMODE ONCONFIG parameter unfortunately are not what you are looking for. They deal with setting the default locking mode for data.

Informix can lock either a specific row on a page or all rows on a page that the row being locked resides on. This can improve performance if there is a high probablility you want locked access to many rows, but obviously decreases concurrency. The manual mentions them in conjunction with SET LOCK MODE as they can influence how SET LOCK MODE works - page level locking produces a higher probability that a connection with SET LOCK MODE enabled will run into a locked page. Hope this helps.