dostats with ANSI logged database?
A user found Art Kagel's dostats utility (v4.12) failed on ANSI-logged databases, dying with "Cannot FETCH table data. SQLCODE = -400" on a PeopleSoft database where tables had various owners. Jonathan Leffler noted that ANSI mode requires owner names to be prepended to all table references. Art investigated, confirmed a further bug involving a FETCH on a cursor that was never closed, fixed it, and sent Roy a patched dostats (to appear in the next utils2_ak release). Roy confirmed it then ran fine over 10,000+ tables.
Auto-generated by DrWatson from the posts below — may be imperfect; read the full thread.
I am able to use dostats with any database as long as it is not ANSI logged. OS = AIX 5.2 IDS = 9.40.FC4 CSDK = 2.81.FC2 dostats = Features Version 4.12, Source Revision: 1.97 Command that fails dostats -h idssoc -d dbname -f file.sql Writing commands to: file.sql Working on database: dbname. psdbowner: Cannot FETCH table data. SQLCODE = -400, ISAM = 0. I compiled dostats as follows: esql dostats.ec
On Thu, 09 Sep 2004 11:47:39 -0400, Roy Mercer wrote: Roy, I'm looking at this. Looks like I have to prepend the owner name to all tablename references in the code and the generated SQL to properly handle ANSI databases. I'll get back to you. Art S. Kagel > I am able to use dostats with any database as long as it is not ANSI logged. > > OS = AIX 5.2 > IDS = 9.40.FC4 > CSDK = 2.81.FC2 > dostats = Features Version 4.12, Source Revision: 1.97 > > Command that fails > dostats -h idssoc -d dbname -f file.sql > > Writing commands to: file.sql > > Working on database: dbname. > psdbowner: > Cannot FETCH table data. SQLCODE = -400, ISAM = 0. > > > > I compiled dostats as follows: > esql dostats.ec
Art S. Kagel wrote: > On Thu, 09 Sep 2004 11:47:39 -0400, Roy Mercer wrote: > Roy, I'm looking at this. Looks like I have to prepend the owner name to all > tablename references in the code and the generated SQL to properly handle > ANSI databases. You do indeed. Just as you have to prepend 'informix' to the system catalog tables, you have to prepend the table owner to all other tables. >>I am able to use dostats with any database as long as it is not ANSI logged. [...] >>Working on database: dbname. >>psdbowner: >>Cannot FETCH table data. SQLCODE = -400, ISAM = 0. -- Jonathan Leffler #include <disclaimer.h> Email: jleffler@earthlink.net, jleffler@us.ibm.com Guardian of DBD::Informix v2003.04 -- http://dbi.perl.org/
On Thu, 09 Sep 2004 23:51:59 -0400, Jonathan Leffler wrote: > Art S. Kagel wrote: >> On Thu, 09 Sep 2004 11:47:39 -0400, Roy Mercer wrote: Roy, I'm looking at >> this. Looks like I have to prepend the owner name to all tablename >> references in the code and the generated SQL to properly handle ANSI >> databases. > > You do indeed. Just as you have to prepend 'informix' to the system catalog > tables, you have to prepend the table owner to all other tables. Yes, however, it looks like Roy had already done that in his copy, 'cause now I'm getting the same -400 error on the second FETCH of an open cursor that's never closed! I was getting -206 errors in my test because of the owner name problem. With that fixed, I get what Roy gets. Art S. Kagel >>>I am able to use dostats with any database as long as it is not ANSI >>>logged. > [...] >>>Working on database: dbname. >>>psdbowner: >>>Cannot FETCH table data. SQLCODE = -400, ISAM = 0. > >
On Thu, 09 Sep 2004 11:47:39 -0400, Roy Mercer wrote: OK, I have located the problem and sent an update to Roy. Anyone else who needs a copy of dostats that will work well with mode ANSO databases should either watch for the next release of the utils2_ak package or contact me directly for an update. Art S. Kagel > I am able to use dostats with any database as long as it is not ANSI logged. > > OS = AIX 5.2 > IDS = 9.40.FC4 > CSDK = 2.81.FC2 > dostats = Features Version 4.12, Source Revision: 1.97 > > Command that fails > dostats -h idssoc -d dbname -f file.sql > > Writing commands to: file.sql > > Working on database: dbname. > psdbowner: > Cannot FETCH table data. SQLCODE = -400, ISAM = 0. > > > > I compiled dostats as follows: > esql dostats.ec
Art, I did not change anything from your source. I just compiled it straight from the IIUG. I run this as "informix" but the tables are all owned by sysadm. The first table it stops on "psdbowner" has an owner of "PS". I tried on just one table that is owned by "sysadm", see below. dostats9_64 -h hrprodsoc -d hrprod -t ps_a_eeo_jobcodes -f hrprod.sql Writing commands to: hrprod.sql Working on database: hrprod. ps_a_eeo_jobcodes: jobcode: Cannot FETCH table data. SQLCODE = -400, ISAM = 0 Thanks for your help. Roy Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<zx90d.11864$w%6.3986@newsread1.news.pas.earthlink.net>... > Art S. Kagel wrote: > > On Thu, 09 Sep 2004 11:47:39 -0400, Roy Mercer wrote: > > Roy, I'm looking at this. Looks like I have to prepend the owner name to all > > tablename references in the code and the generated SQL to properly handle > > ANSI databases. > > You do indeed. Just as you have to prepend 'informix' to the system > catalog tables, you have to prepend the table owner to all other tables. > > > >>I am able to use dostats with any database as long as it is not ANSI logged. > [...] > >>Working on database: dbname. > >>psdbowner: > >>Cannot FETCH table data. SQLCODE = -400, ISAM = 0.
Art, The new version of dostats works great. I was able to run it against our ANSI logged Peoplesoft database with over 10,000 tables. I will definitely be able to use the myschema when you get it updated. Thanks for all your help. Roy Jonathan Leffler <jleffler@earthlink.net> wrote in message news:<zx90d.11864$w%6.3986@newsread1.news.pas.earthlink.net>... > Art S. Kagel wrote: > > On Thu, 09 Sep 2004 11:47:39 -0400, Roy Mercer wrote: > > Roy, I'm looking at this. Looks like I have to prepend the owner name to all > > tablename references in the code and the generated SQL to properly handle > > ANSI databases. > > You do indeed. Just as you have to prepend 'informix' to the system > catalog tables, you have to prepend the table owner to all other tables. > > > >>I am able to use dostats with any database as long as it is not ANSI logged. > [...] > >>Working on database: dbname. > >>psdbowner: > >>Cannot FETCH table data. SQLCODE = -400, ISAM = 0.