dbaccess Table -- Info reason for -211?

All, Our application today reported a SQL error of -211 (Can not read system catalog). This error apparently results from an inability to aquire a lock on, or read from, systables. I was surprised when our DBA suggested this may be caused by a dbaccess or isql user using the Table -- Info option. The DBA suggested that as long as the information is _viewed_ on the screen a lock is held against systables. Can this be true? I suggested it seemed reasonble that dbaccess and isql might acquire locks _while_ it selected from systables, but holding a lock for as long as the user viewed the data seemed like a weak design. The DBA assured me this was true, and in fact was required in order for dbaccess and isql to hold to SQL standards. Now it is entirely possible I may have misunderstood something, and perhaps there are concerns about the Table -- Info option I am not aware of. As it stands right now, our user base will use Table -- Info whenever they might require column definitions. I shutter to think this benign looking behavior has actually been putting our application at risk. Kind regards, -Randy Galbraith

On Feb 6, 8:53 pm, "RandyG271" <randyg...@yahoo.com> wrote: > All, > > Our application today reported a SQL error of -211 (Can not read > system catalog). This error apparently results from an inability to > aquire a lock on, or read from, systables. I was surprised when our > DBA suggested this may be caused by a dbaccess or isql user using the > Table -- Info option. The DBA suggested that as long as the > information is _viewed_ on the screen a lock is held against > systables. Can this be true? I suggested it seemed reasonble that > dbaccess and isql might acquire locks _while_ it selected from > systables, but holding a lock for as long as the user viewed the data > seemed like a weak design. The DBA assured me this was true, and in > fact was required in order for dbaccess and isql to hold to SQL > standards. > > Now it is entirely possible I may have misunderstood something, and > perhaps there are concerns about the Table -- Info option I am not > aware of. As it stands right now, our user base will use Table -- > Info whenever they might require column definitions. I shutter to > think this benign looking behavior has actually been putting our > application at risk. > > Kind regards, > -Randy Galbraith Yes, have the users execute i "set isolation to dirty read;" in dbaccess before looking at the tables with Table Info option. Informix will now execute the information queries without locking the system tables. You will need to do in the same run of dbaccess or the isolation level will change.

On Feb 6, 7:53 pm, "RandyG271" <randyg...@yahoo.com> wrote: > All, > > Our application today reported a SQL error of -211 (Can not read > system catalog). This error apparently results from an inability to > aquire a lock on, or read from, systables. I was surprised when our > DBA suggested this may be caused by a dbaccess or isql user using the > Table -- Info option. The DBA suggested that as long as the > information is _viewed_ on the screen a lock is held against > systables. Can this be true? I suggested it seemed reasonble that > dbaccess and isql might acquire locks _while_ it selected from > systables, but holding a lock for as long as the user viewed the data > seemed like a weak design. The DBA assured me this was true, and in > fact was required in order for dbaccess and isql to hold to SQL > standards. > > Now it is entirely possible I may have misunderstood something, and > perhaps there are concerns about the Table -- Info option I am not > aware of. As it stands right now, our user base will use Table -- > Info whenever they might require column definitions. I shutter to > think this benign looking behavior has actually been putting our > application at risk. > > Kind regards, > -Randy Galbraith I just did some testing and the only time I saw any locks held when doing a table info from dbaccess was when the databases logging mode was set to mode ansi. In that case, then yes it would be required for SQL standards to place share locks on rows that are being read. If your database is not log mode ansi then table info would/should not be holding locks while the information is sitting there on the screen.

On Feb 7, 12:15 pm, jpren...@yahoo.com wrote: > On Feb 6, 7:53 pm, "RandyG271" <randyg...@yahoo.com> wrote: > > > > > All, > > > Our application today reported a SQL error of -211 (Can not read > > system catalog). This error apparently results from an inability to > > aquire a lock on, or read from, systables. I was surprised when our > > DBA suggested this may be caused by a dbaccess or isql user using the > > Table -- Info option. The DBA suggested that as long as the > > information is _viewed_ on the screen a lock is held against > > systables. Can this be true? I suggested it seemed reasonble that > > dbaccess and isql might acquire locks _while_ it selected from > > systables, but holding a lock for as long as the user viewed the data > > seemed like a weak design. The DBA assured me this was true, and in > > fact was required in order for dbaccess and isql to hold to SQL > > standards. > > > Now it is entirely possible I may have misunderstood something, and > > perhaps there are concerns about the Table -- Info option I am not > > aware of. As it stands right now, our user base will use Table -- > > Info whenever they might require column definitions. I shutter to > > think this benign looking behavior has actually been putting our > > application at risk. > > > Kind regards, > > -Randy Galbraith > > I just did some testing and the only time I saw any locks held when > doing a table info from dbaccess was when the databases logging mode > was set to mode ansi. In that case, then yes it would be required for > SQL standards to place share locks on rows that are being read. If > your database is not log mode ansi then table info would/should not be > holding locks while the information is sitting there on the screen. Interesting.