Re: How to identify temp tables from catalog or sysmaster/SMI

Jacob, Thanks for testing and posting followup. I just tried to point that, if you have standard dbspace listed in your DBSPACETEMP, then 'yutz_r' table will be in that dbspace if you're working with database with logging. Then you (probably :) will not worry about "with no log" clause your developers missing some time. If I understood your original concern correctly. Regards Vardan Jacob Salomon wrote: > > In article <7pce83$gbq$1@nnrp1.deja.com>, > JSalomon@bn.com wrote: > -- SNIP -- > > Now my query for abuses of temp tables is: > > select t.dbsname, t.tabname, > > hex(p.partnum) partition, hex(p.flags) pflags > > from sysmaster:systabnames t, sysmaster:sysptnhdr p > > where t.partnum = p.partnum > > and bitval(p.flags,32) = 1 -- Looking for temp tables > > and trunc(p.partnum / 1048576) -- Filter: Only temps not in > > in (select dbsnum -- temp dbspace > > from sysdbspaces where is_temp = 0) > > order by dbsname, tabname, partition> > > > Thank you, Vardan, for pointing this out to me. > > Just a followup, using Vardan's knowlege: > > First, note that Dbspace 5 is a temp dbspace. > > Database imm: > > create temp table yutz_r (one_col int); -- Default root > create temp table yutz_b (one_col int) in book_dbs; -- Name a dbspace > create temp table yutz_d (one_col int) with no log; -- Into temp space> > Now let's look for them in sysptnhdr: > > select t.dbsname, t.tabname, > hex(p.partnum) partition, hex(p.flags) pflags > from sysmaster:systabnames t, sysmaster:sysptnhdr p > where t.partnum = p.partnum > and bitval(p.flags,32) = 1 > and tabname matches "yutz*" > order by dbsname, tabname> > dbsname tabname partition pflags > > imm yutz_b 0x00900039 0x00000861 > imm yutz_d 0x005000AA 0x00000821 > imm yutz_r 0x001000A1 0x00000861 > > Notice that the 0x20 bit is set in all cases. When there was a > HASHTEMP or SORTTEMP pseudo-database in dbsname, this was also the case: > > HASHTEMP th_overflow_ffffff 0x005000A9 0x000048A0 > > If it is not already in the FAQ, I think it might be a useful little > factoid. > > +--------- Jacob Salomon DBA JSalomon@bn.com (212) 352-3889 ----------+ > | (In perpetual pursuit of undomesticated semi-aquatic avians) | > | The expedient performance of a task with excessive concern to its | > | duration-to-completion engenders a virtual certainty of diminished | > | benefit therefrom. | > | -- Benjamin Franklin (but he said it in 3 words) | > +----------------------------------------------------------------------+ > > Sent via Deja.com http://www.deja.com/ > Share what you know. Learn what you don't. -- vaar@geocities.com