Problem accessing records that are not committed

When I try to access records on an Informix database that are not yet committed, I'm getting an error ("[Error Code: -245, SQL State: IX000] Could not position within a file via an index. 2) [Error Code: -144, SQL State: IX000] ISAM error: key value locked"). 

I know I need to set the isolation mode and so doing this (in SQL Commander) works - e.g. this is OK:

set isolation to dirty read;

select * from sp_cn_chg where chg_code = 'POST';

However, I have actually entered "set isolation to dirty read;" into "Database Connection Hooks", "Run SQL at Connect" setting on the properties tab for the connection and so I don't understand why I need to explicitly add it into the SQL Commander session. 

I can see in the logs that the the "set isolation" command is actually run:

2016-05-25 17:26:41.553 FINE 697 [ExecutorRunner-pool-2-thread-1 - AbstractFacade.executeCommand] Executing 'SET ISOLATION TO DIRTY READ'

2016-05-25 17:26:41.553 FINE 697 [pool-3-thread-1 - B.?] DefaultEditor21: IfxStatement.execute("SET ISOLATION TO DIRTY READ")

...but the SQL executed fails:

2016-05-25 17:26:41.570 FINE 697 [pool-3-thread-1 - B.?] DefaultEditor21: IfxStatement.execute("select * from sp_cn_chg where chg_code = 'POST'")

2016-05-25 17:26:41.580 FINE 697 [pool-3-thread-1 - B.?] DefaultEditor21: EXCEPTION -> java.sql.SQLException: Could not position within a file via an index.

NB: This behaviour also means I can't look at the records in the "Data" tab for table that currently has uncommitted data (which is actually the biggest issue I face) . It's no real problem just adding "set isolation to dirty read;" to my SQL Commander session, but I mainly use the "Data" tab to browse table data...

Hi,

Try setting the isolation level in the Properties tab for the connection, in the Transaction category.

Best Regards,

Hans

Hans,

I tried that, but I got the same result...

Hi Scott,

Odd. Is Auto Commit enabled or disabled for the SQL Commander (and/or for the connection as such in the Connection tab)? Does flipping it make a difference?

Best Regards,

Hans

Hans,

Today SQL Commander seems to be working fine (regardless of the Auto Commit setting at either the connection level or the SQL commander levels).  I'm not sure what's changed to make it work yet, but I have attached the log file from yesterday (when it didn't work) just in case there's something there that looks odd to you...

Regardless of SQL Commander working ok or not, the Auto Commit setting for the connection makes no difference when I access the records for a table - I still can't view data on the "Data" tab and nor can I get a count on the "Row Count" tab.

Hans,

FYI, when I'm testing, I'm locking the table complete externally to DbVis (using the Informix dbaccess tool).  In dbacesss I'll enter a command like:

BEGIN;

DELETE FROM TABLE xxxxx;

and then I go an test in DbVis...

Hi Scott,

Okay, then Single Shared Physical Connection will not help. I'm afraid I'm out of ideas, so please see if you can get help in an Informix forum. If you find a solution, please let us know.

Best Regards,

Hans

Hans,

My working theory at the moment is that I think that when a connection has an issue - e.g.:

2016-05-28 06:07:37.098 INFO 663 [ExecutorRunner-pool-3-thread-1 - L.?] Error executing: database sysadmin for: UADB1 Prod3

java.sql.SQLException: No connect permission.

...that somehow at that point the isolation mode gets lost/forgotten for that database connection. 

Hence when DbVis runs the select to access my table, I see this sort of behaviour:
2016-05-28 06:08:24.765 FINE 663 [pool-4-thread-1 - B.?] RootConnection: IfxStatement.execute("SELECT * FROM informix.autochgtran")

2016-05-28 06:08:24.766 FINE 663 [pool-4-thread-1 - B.?] RootConnection: EXCEPTION -> java.sql.SQLException: Could not do a physical-order read to fetch next row.

No real proof of this yet, but I noticed in the logs of sessions that failed, there's always an error in the log (between the point of connecting to the database and the point of selecting from the table I'm trying to access).  When my SQL is successfully executed  (e.g. using SQL commander with individual connections), I don't see any errors...

So perhaps there is an issue with the JDBC driver???  Even so, I'm wondering why DbVis is trying to accessing the databases it does when trying to access the Data tab for a table (it's these unexpected accesses that are leading to the "No Connect Permission" errors).

e.g. I'm accessing the AutoChgTran table in database BBS, but in the logs I'm seeing a failed attempt to access the sysadmin database  (see the first log snippet above).  Why is DbVis accessing that table?

I've attached the full log FYI...

Hi Scott,

Since you reported this in the Support forum, I thought you were getting this with DbVisualizer 9.2.15, but I now see that you are using the Beta version for this test. In the Beta, we try to connect to all databases to get the schema names. This is why you see the failed attempt to connect to sysadmin. You may be on to something here...

We do not do this in the DbVisualizer 9.2.15, so please test this with that version so we can verify if the problem you see is related to the new Beta behavior.

Best Regards,

Hans

Hans,

Using version 9.2 worked as expected.  So does the 9.5 beta, provided I enable the "Show Only Default Database/Schema" option.  As soon as 2 or more databases are enabled for a particular connection (in the Beta version) the issue starts...

FYI, I've attached the logs for both the 9.2 and 9.5 Beta versions with both a single database and with multiples visible for the connection.  

NB: I've added extra blank lines in the logs (to make it easier for me to compare them to each other) 

Hi Scott,

Thanks for all your help with this! You're right, it is a bug. The changes we made in the latest Beta made it more obvious but the same issue has been there for a very long time, and can be seen if you set the isolation level and then expand a database node that is not the default database. This resets the isolation level and any settings done via a connection hook.

I've opened a ticket for looking into a solution for this. For now, your work-around with "Show Only Default Database/Schema" should be fine.

Best Regards,

Hans

Thanks Roger - I can confirm that 95.2559 worked as expected...

Hi Scott,

Great, thanks for confirming.

Best Regards,

Hans