Ho to identify temp tables from catalog or sysmaster/SMI

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? 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.