hidden tables - not listed in dbschema

Hi, we're having a curious problem with a database that's used as a kind of temporary storage (only the information stored in a few tables, the rest is stored indefinitely, but these are precisely the relevant tables). The curious thing is that when we run a query like: database sysmaster; select dbsname, tabname, count(*) num_of_extents, sum( pe_size ) total_size from systabnames, sysptnext where partnum = pe_partnum group by 1, 2 order by 3 desc, 4 desc; (to gather information about extents), some tables are listed that don't exist in the dbschema output, neither are they in the initial schema file used to create the database and it's tables. The tables that are listed via the above query are, for example: dbsname rhapsody tabname trackingauditeventparameter_eventid num_of_extents 134 total_size 24042 dbsname rhapsody tabname trackingauditevent_trackingid num_of_extents 118 total_size 12120 dbsname rhapsody tabname trackingstate_messageid num_of_extents 94 total_size 3794 (...) But, when I try to get the scheme of those tables, I get something like: castclu2-[PROD]-(db1):~ $ dbschema -d rhapsody -t trackingstate_messageid DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.FC9 Copyright IBM Corporation 1996, 2004 All rights reserved Software Serial Number AAA#B000000 No table or view trackingstate_messageid. Does anybody know why there are tables that are not listed in the dbschema output and maybe someone can give us a hint how to calculate how big they are? Thanks in advance, Gerardo

Could these be synonyms to tables in other databases? Check tabtype field in systables, maybe that would help. (S=synonym I believe) -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of GERARDO PADIERNA Sent: Wednesday, July 08, 2009 4:36 AM To: ids@iiug.org Subject: hidden tables - not listed in dbschema [16254] Hi, we're having a curious problem with a database that's used as a kind of temporary storage (only the information stored in a few tables, the rest is stored indefinitely, but these are precisely the relevant tables). The curious thing is that when we run a query like: database sysmaster; select dbsname, tabname, count(*) num_of_extents, sum( pe_size ) total_size from systabnames, sysptnext where partnum = pe_partnum group by 1, 2 order by 3 desc, 4 desc; (to gather information about extents), some tables are listed that don't exist in the dbschema output, neither are they in the initial schema file used to create the database and it's tables. The tables that are listed via the above query are, for example: dbsname rhapsody tabname trackingauditeventparameter_eventid num_of_extents 134 total_size 24042 dbsname rhapsody tabname trackingauditevent_trackingid num_of_extents 118 total_size 12120 dbsname rhapsody tabname trackingstate_messageid num_of_extents 94 total_size 3794 (...) But, when I try to get the scheme of those tables, I get something like: castclu2-[PROD]-(db1):~ $ dbschema -d rhapsody -t trackingstate_messageid DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.FC9 Copyright IBM Corporation 1996, 2004 All rights reserved Software Serial Number AAA#B000000 No table or view trackingstate_messageid. Does anybody know why there are tables that are not listed in the dbschemaoutput and maybe someone can give us a hint how to calculate how big they are? Thanks in advance, Gerardo ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

I ran that same query against one of our servers and it returned information for temp tables that existed. Temp tables do not show up in dbschema output in the version of IDS that we are using (10.00.UC8). Mike

Mike, Possibly auditing or event tracking temp tables? -ScottM -----Original Message----- From: ids-bounces@iiug.org [mailto:ids-bounces@iiug.org] On Behalf Of MIKE MAGIE Sent: Wednesday, July 08, 2009 8:40 AM To: ids@iiug.org Subject: Re: hidden tables - not listed in dbschema [16261] I ran that same query against one of our servers and it returned information for temp tables that existed. Temp tables do not show up in dbschema output in the version of IDS that we are using (10.00.UC8). Mike ************************************************************************ ******* Forum Note: Use "Reply" to post a response in the discussion forum.

Hi, Gerardo Dbschema will not print System Monitoring Interface (SMI) tables/pseudo-tables which point directly to the shared memory structures or temp tables. In your case, looks like those trackingauditeventparameter* tables were temp tables. Regards, -Ping --- On Wed, 7/8/09, GERARDO PADIERNA <g.padierna@gmail.com> wrote: From: GERARDO PADIERNA <g.padierna@gmail.com> Subject: hidden tables - not listed in dbschema [16254] To: ids@iiug.org Date: Wednesday, July 8, 2009, 4:35 AM Hi, we're having a curious problem with a database that's used as a kind of temporary storage (only the information stored in a few tables, the rest is stored indefinitely, but these are precisely the relevant tables). The curious thing is that when we run a query like: database sysmaster; select dbsname, tabname, count(*) num_of_extents, sum( pe_size ) total_size from systabnames, sysptnext where partnum = pe_partnum group by 1, 2 order by 3 desc, 4 desc; (to gather information about extents), some tables are listed that don't exist in the dbschema output, neither are they in the initial schema file used to create the database and it's tables. The tables that are listed via the above query are, for example: dbsname rhapsody tabname trackingauditeventparameter_eventid num_of_extents 134 total_size 24042 dbsname rhapsody tabname trackingauditevent_trackingid num_of_extents 118 total_size 12120 dbsname rhapsody tabname trackingstate_messageid num_of_extents 94 total_size 3794 (...) But, when I try to get the scheme of those tables, I get something like: castclu2-[PROD]-(db1):~ $ dbschema -d rhapsody -t trackingstate_messageid DBSCHEMA Schema Utility INFORMIX-SQL Version 9.40.FC9 Copyright IBM Corporation 1996, 2004 All rights reserved Software Serial Number AAA#B000000 No table or view trackingstate_messageid. Does anybody know why there are tables that are not listed in the dbschema output and maybe someone can give us a hint how to calculate how big they are? Thanks in advance, Gerardo ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Ok, thank you for your suggestions! But I was a bit stupid in not realizing what this objects really are: they're indexes! In fact there are in the dbscheme output, but of course not in a 'create table' line. And besides, there was some mix up of uppercase and lowercase in the original scheme file.... forget it. I finally found out, but I got confused about all this because I didn't imagine that a query like the one mentioned above would separately list the indexes too under a column called 'tabname', but it does. Well, now things are clear, nothing strange is going on. Thank you all. Greets, Gerardo