Tables in dbspacetemp

Does anyone here know of a way to identify what tables are in any given temporary dbspace? I have looked into some of the systables, but wasn't able to see anything that jumped out at me. I contacted Informix Tech support, but the engineer I got didn't know of any script or code to do this. Any help would be appreciated. Carrie Mueller DBA - HCIA

onstat -g sql <session-id> shows temporary tables / session. If you want to get all of the temporary tables, you have to execute pervious command for all of current sessions. Informix doesn't write any info about temporary files to the system tables. Best regards, Olli cmuel@hcia.com kirjoitti artikkelissa <77vdv8$1i1$1@news.xmission.com>... > > Does anyone here know of a way to identify what tables are in > any given temporary dbspace? I have looked into some of the > systables, but wasn't able to see anything that jumped out at me. > I contacted Informix Tech support, but the engineer I got didn't > know of any script or code to do this. > > Any help would be appreciated. > > Carrie Mueller > DBA - HCIA > > >

There is actually a very simple query you can run provided you know the dbspace numbers of your temporary dbspaces. Example: Let's suppose you have 3 temp dbspaces (temp1dbs, temp2dbs, and temp3dbs). From an onstat -d output you can easily find the dbs number for each dbspace. For this example: ... Dbspaces address number flags fchunk nchunks flags owner name 400380ec 1 2 1 1 M informix rootdbs 40039f20 2 2 2 1 M informix logdbs 40039f8c 3 2 3 1 M informix physdbs 4005c028 4 2001 4 1 N T informix temp1dbs 4005c094 5 2001 5 1 N T informix temp2dbs 4005c100 6 2001 6 1 N T informix temp3dbs ... From the 2nd column, your dbs numbers are 4,5,and 6 for temp1dbs, temp2dbs, and temp3dbs respectively. As you probably already know, a table is represented by it's partnum. A partnum for a given table will be represented by the dbs number and 6 trailing digits (for dbs 4 --> 4??????, for dbs 5 --> 5??????). If you want to know what temp tables exist in temp1dbs, you could run the following query while connected to sysmaster: SELECT tabname, partnum, hex(partnum) hexrep FROM systabnames, systabinfo where partnum = ti_partnum and partnum between 4000000 and 4999999; If you want to see what's in all temp dbspaces just change the last line to: "and partnum between 4000000 and 6999999;" I have included the hex representation of the partnum in the query because you will need it if you want to find the "open" temp tables at any given time. Just compare you're "hexrep" values to those in an 'onstat -t' output. NOTE: At a minimum you will always see the TBLSpace tablespace for each temp dbspace listed. Hope this helps. Bob -------------- In article <77vdv8$1i1$1@news.xmission.com>, cmuel@hcia.com wrote: > > Does anyone here know of a way to identify what tables are in > any given temporary dbspace? I have looked into some of the > systables, but wasn't able to see anything that jumped out at me. > I contacted Informix Tech support, but the engineer I got didn't > know of any script or code to do this. > > Any help would be appreciated. > > Carrie Mueller > DBA - HCIA > > -----------== Posted via Deja News, The Discussion Network ==---------- http://www.dejanews.com/ Search, Read, Discuss, or Start Your Own

cmuel@hcia.com wrote: > > Does anyone here know of a way to identify what tables are in > any given temporary dbspace? I have looked into some of the > systables, but wasn't able to see anything that jumped out at me. > I contacted Informix Tech support, but the engineer I got didn't > know of any script or code to do this. There is a package in the IIUG Software Repository called temptab that contains a 4GL program to do just this. Even if you do not have 4GL you will be able to extract the sysmaster SQL query that retrieves the information. Art S. Kagel

Carrie Mueller cmuel@hcia.com wrote: > Does anyone here know of a way to identify what tables are in > any given temporary dbspace? I have looked into some of the > systables, but wasn't able to see anything that jumped out at me. > I contacted Informix Tech support, but the engineer I got didn't > know of any script or code to do this. Carrie, the following SQL will display every table in the single temp dbspace of my current server. (The commented out column was for debugging.) --------------------------------------------- select dbsname, tabname, --hex(partnum) partition, trunc(partnum/1048576) dbsnum_e, name dbspace from sysmaster:sysptprof t, sysmaster:sysdbspaces d where d.dbsnum = trunc(partnum/1048576) and name in ("tmpdbs1") order by dbsname, tabname;--------------------------------------------- When I ran it, I came up with this: dbsname tabname dbsnum_e dbspace garpacv2 browse 6 tmpdbs1 garpacv2 browse 6 tmpdbs1 garpacv2 browse 6 tmpdbs1 garpacv2 browse 6 tmpdbs1 garpacv2 browse 6 tmpdbs1 garpacv2 t1rmessg 6 tmpdbs1 garpacv2 tmprollr 6 tmpdbs1 garpacv2 zzehistw 6 tmpdbs1 garpacv2 zzehistw 6 tmpdbs1 tmpdbs1 TBLSpace 6 tmpdbs1 --------------------------------------------- I think that TBLSpace is the same fake-out that shows up in systables. But I believe this should give you the answer you seek. BTW, if you need the owner of the temp tables, you need to join the above with sysmaster:systabnames. Note the IN list. This is in case your DBSPACETEMP contains the names of multiple dbspaces. Give us a shout if it works. -- -- Jake (Retrospectively realizes there is no future in hindsight) +------------------------------------------------------------+ | The expedient performance of a task with excessive concern | | regarding its duration-to-completion engenders a virtual | | certainty of diminished benefit therefrom. | | -- Benjamin Franklin (but he said it in 3 words) | +------------------------------------------------------------+