RE: Looking at temp tables

Art S. Kagel wrote: > > > Bob W Maccione wrote: > > > > Has anyone found a way to allow one process the ability to see a temp = table > > that was/is created by another process. I know that they don't exist = in the > > sys* tables but I would think that there is some hidden hook I could = get at. > > I'm really just interested in the number of rows in the table (to tell = that the > > process is really adding data to the temp table). > > Sorry this is not possible. > > Art S. Kagel --- Art: Actually, there is a way to get most of this info. In the IIUG News (July = 97), there is some SQL written by John Miller III, that generates this = info from the sysmaster database. SELECT HEX(i.ti_partnum) partition, TRIM(n.dbsname) ||":"|| TRIM(n.owner) ||":"|| TRIM(n.tabname) = table, i.ti_nptotal allocated_pages FROM systabnames n, systabinfo i WHERE BITVAL(i.ti_flags, "0x0020") =3D 1 AND i.ti_partnum =3D n.partnum; It uses undocumented features/tables, with the caveat that it may change = with newer versions, but I have tested it with v7.14 and v7.23, and it = works in both of those OK. Of course, this shows the growth of allocated pages, which is not quite = the same as the number of rows. Still, it's an indication of activity and = relative size. Even if this doesn't work, the alternative is to use onperf/xtree, which = does represent the passing of rows from thread to thread. This is a good = way of checking if a query is actually generating data. cheers RET +--------------------------------------------------------------------------= --+ | Richard Thomas (DBA) richard_thomas@yes.optus.com.au +61 2 9342 = 7188 | +--------------------------------------------------------------------------= --+