Re: Ho to identify temp tables from catalog or sysmaster/SMI
Jacob Salomon wrote:
>
> Hi Family,
>
> I am trying to identify abuses of temp tables - where my programmers
> have failed to specify "WITH NO LOG" in the creation of a temp table. I
> am fortunate we hold a naming convention - most temp tables begin
> with "temp", "tmp" or "t_". This is not a reliable determinant, however.
>
> I went looking for flags in systables and found them to be all 0 for
> everything I found in systables. This just tells me that some tables
> named "temp*" are not really temp tables to the engine - I found them
> in systables.
>
> So: Is there a flag in an SMI table that reliable tells me a tabler is
> really a temp table? I find that systabinfo has no flag or is_temp
> column. (Amazingly, the 9.2 version of the Admin Guide seems not to
> document systabinfo!)
>
> IN my systenm dbspaces 4 and 5 are temp dbspaces. So I ran a query to
> look at flags from sysptnhdr and got some results I ma having
> difficulty sorting out. Here's the query:
>
> 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
> order by dbsname, tabname>
> Here's a snippet from the output:
>
> imm sysusers 0x006000E6 0x00000802
> imm sysviews 0x006000E5 0x00000802
> imm sysviolations 0x00600004 0x00000802
> imm t_alpha_summary 0x001000C4 0x00000861
> imm t_alpha_summary 0x001000B4 0x00000861
> imm t_callout 0x004000C6 0xFFFF8821
> imm t_callout 0x00500043 0xFFFF8821
> imm t_cat 0x00400006 0x00000821
> imm t_cat 0x00500064 0x00000821
> imm t_changes 0x004000C1 0xFFFF8821
> imm t_changes 0x00500041 0xFFFF8821
> imm t_keys 0x00600029 0x00000801
> imm temp_book_ins 0x006001BF 0x00000902
> imm temp_buyer_changes 0x006000D8 0x00000802
> imm temp_co_hdr 0x0060019E 0x00000802
> imm temp_dinv 0x00600130 0x00000801
> imm temp_div_inventory 0x00600109 0x00000802
> imm temp_ean_store 0x001000E9 0x00000861
> imm temp_ean_store 0x001000AC 0x00000861
>
> I think I see a partial pattern: temp tables not in the temp dbspaces
> end their flags in 0x61, while tables in the temp dbspace end their
> flags with 0x21. Conclusion: The ON conditions of flags 0x00000021 is
> the indicator of a temp table. Non-temp tables (like temp_book_ins)
> end in 0x02 or 01 but I don't see a pattern.
>
> Can someone please confirm or correct this?
>
> Is there ANY documentation on these flags?
Comments in sysmaster.sql and values in flags_text table :-)
I used to use some variations of this query to check temporary tables:
select tabname,
case
when bitval( p.flags, 32 ) = 1
then 'sys_temp'
when bitval( p.flags, 64 ) = 1
then 'usr_temp'
when bitval( p.flags, 128 ) = 1
then 'sort_file'
end type,
hex(n.partnum) h_n_partnum,
n.partnum n_partnum,
-- n.owner,
-- hex(p.flags) h_p_flags,
name dbspace_name
from sysptnhdr p,
systabnames n,
sysdbstab d
where p.partnum = n.partnum
and partdbsnum( n.partnum ) = d.dbsnum
and ( bitval( p.flags, 32 ) = 1 -- System created TempTable
-- or bitval( p.flags, 64 ) = 1 -- User created Temp
Table
-- or bitval( p.flags, 128 ) = 1 ) -- Sort File
)
;
>
> Is there a documented way to find the information I seek?
>
> Thanks.
> --
> +---- Jacob Salomon - DBA JSalomon@bn.com - ---------------------------+
> |--------------- Obligatory sesquipedalian obfuscation: ---------------|
> | An object of igneous, sedimentary or metamorphic mineral in combined |
> | states of elevated linear and rotational kinetic energy acquires no |
> | accumulation of bryophytic vegetation. |
> +----------------------------------------------------------------------+
>
> Sent via Deja.com http://www.deja.com/
> Share what you know. Learn what you don't.
HTH
Vardan
--
Vardan Aroustamian
vaar@geocities.com
Jacob Salomon wrote:
-- SNIP --
>> I went looking for flags in systables and found them to be all 0 for
-- SNIP --
>> 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
>> order by dbsname, tabname
-- SNIP -->> I think I see a partial pattern: temp tables not in the temp dbspaces
>> end their flags in 0x61, while tables in the temp dbspace end their
>> flags with 0x21. Conclusion: The ON conditions of flags 0x00000021
>> is the indicator of a temp table.
-- SNIP --
>> Is there ANY documentation on these flags?
In article <7pa0pp$8qk$1@news.xmission.com>,
Vardan Aroustamian <vardana@infogain.com> replied:
> Comments in sysmaster.sql and values in flags_text table :-)
>
> I used to use some variations of this query to check temporary tables:
>
> select tabname,
> case
> when bitval( p.flags, 32 ) = 1
> then 'sys_temp'
> when bitval( p.flags, 64 ) = 1
> then 'usr_temp'
> when bitval( p.flags, 128 ) = 1
> then 'sort_file'
> end type,
> hex(n.partnum) h_n_partnum,
> n.partnum n_partnum,
> -- n.owner,
> -- hex(p.flags) h_p_flags,
> name dbspace_name
> from sysptnhdr p,
> systabnames n,
> sysdbstab d
> where p.partnum = n.partnum
> and partdbsnum( n.partnum ) = d.dbsnum
> and ( bitval( p.flags, 32 ) = 1 -- System created Temp> Table
> -- or bitval( p.flags, 64 ) = 1 -- User created Temp Table
> -- or bitval( p.flags, 128 ) = 1 ) -- Sort File
> );
Vardan,
After receiving your reply I got to a little experimenting. The pattern
I noticed is the the 0x20 flag - bitval(p.flags, 32) - is the marker of
any kind of temp table. I noticed that SORTTEMP and HASHTEMP tables
have some other flags set but all of them has this one flag on.
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.
--
+---- Jacob Salomon - DBA JSalomon@bn.com - ---------------------------+
|--------------- Obligatory sesquipedalian obfuscation: ---------------|
| An object of igneous, sedimentary or metamorphic mineral in combined |
| states of elevated linear and rotational kinetic energy acquires no |
| accumulation of bryophytic vegetation. |
+----------------------------------------------------------------------+
Sent via Deja.com http://www.deja.com/
Share what you know. Learn what you don't.
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.