Re: Procedure bitval() in sysmaster

Try the following? Select * from systabinfo where bitval(ti_flags,32)=1 or bitval(ti_flags,64)=1;Find the partnum with the hex value, which matches the partnum of the onstat -g ses/sql output.In this way you will findout, the temp table info. Ganesh. "Jacob Salomon" <JSalomon@bn.com> on 02/14/2000 03:15:20 PM Please respond to "Jacob Salomon" <JSalomon@bn.com> To: informix-list@iiug.org cc: (bcc: Ganesh Sankar/PFC/Providian) Subject: Procedure bitval() in sysmaster Hi family, Bottom lines first: 1. In sysmaster:systabinfo.ti_flags (or any other table), how can I recognize a temp table? It may be system created, user created, a HASHTEMP or SORTTEMP table but how would I recognize it? 2. Has anyone ever been able to make sense of the bitval procedure? I'm getting nonsesical results. Now, a lengthy dissertation, not for the faint of heart. A couple of years ago, I posted a question asking how to recognize a system catalog if I'm scanning systabinfo. At the time, I did not get a satisfactory answer that worked but, since I got a workaround that did what I needed. The only lasting benefit I got then was the procedure name sysmaster:bitval(). Well, now I have a similar need: I have a query scanning up through [a join of] sysmaster:systabnames and sysmaster:systabinfo. I would like to recognize temp tables by ti_flags (or some other means). Looking through buildsmi.sql, I see that indeed, some flags in ti_flags should point me in this direction: insert into flags_text values ('sysptnhdr', 32, 'System created Temp Table'); insert into flags_text values ('sysptnhdr', 64, 'User created Temp Table'); insert into flags_text values ('sysptnhdr', 128, 'Sort File'); This would correspond to a hex mask value of 0x'E0'. At the individual bit level, if I understand the numbers correctly and I refer as the lowest order bit of a word as bit-1, I could identify temp or sort table by the clause: ... and ( (bitval(ti_flags,6) = 1) or (bitval(ti_flags,7) = 1) or (bitval(ti_flags,8) = 1) ) Unfortunately, the bitval function as written seems to assume a 32-bit number. And it does not seem to return a sensible value corresponding to the known bit positions in the flags. Don't believe be? Look at the following SQL in the sysmaster database: select trim(tn.dbsname) || ":" || trim(tn.tabname) table_name, hex(tn.partnum) partition, hex(ti_flags) flags, -- bitval(ti_flags,'0x0') b00, bitval(ti_flags,'0x1') b01, bitval(ti_flags,'0x2') b02, bitval(ti_flags,'0x3') b03, bitval(ti_flags,'0x4') b04, bitval(ti_flags,'0x5') b05, bitval(ti_flags,'0x6') b06, bitval(ti_flags,'0x7') b07, bitval(ti_flags,'0x8') b08, bitval(ti_flags,'0x9') b09, bitval(ti_flags,'0xA') b10, bitval(ti_flags,'0xB') b11, bitval(ti_flags,'0xC') b12, bitval(ti_flags,'0xD') b13, bitval(ti_flags,'0xE') b14, bitval(ti_flags,'0xF') b15, bitval(ti_flags,'0x10') b16 from sysmaster:systabnames tn, sysmaster:systabinfo ti where tn.partnum = ti.ti_partnum order by table_name, partition I am using hex values to mimic what I found in other code in buildsmi.sql. But I gor the same results when I used regular decimal numbers. (bitval(anything, 0) yields an error in the mod function.) Results? Oh, here is a sample: table_name HASHTEMP:th_overflow_ffffff partition 0x00400042 flags 0x000048A0 b01 0 b02 0 b03 1 b04 0 b05 0 b06 0 b07 0 b08 0 b09 1 b10 1 b11 0 b12 1 b13 0 b14 0 b15 1 b16 0 . . . table_name imm:yakayak partition 0x0040001A flags 0xFFFF8921 b01 1 b02 0 b03 1 b04 0 b05 1 b06 1 b07 1 b08 0 b09 1 b10 1 b11 1 b12 0 b13 1 b14 1 b15 1 b16 0 ------------------------------------------------------------------- That yakayak table is a remp table I deliberately created (select into temp..) to look for the bit patterns. This presents some flies in the ointment: (1) The bit values we see from the bitval function to not correspond with the bit values we know should be there from the hex value. (2) Table yakayak would test positive if masked against '0xE0' but because of bit mask '0x80', which corresponds to a sort-temp table (according to the comments in buildsmi). So the whole test becomes unreliable. (3) The high order bit is on for the smallint ti_flags. This is the reason for the FFFF in the hex() result. But that high order bit 16 is not listed in the buildsmi code that defines these flags. If I can't get bitval to work the way I need it, a better solution would have been the clause: ... and (ti_flags && '0XE0') != 0 This was my first choice. Unfortunately, from the doc available to me, SQL does not support a bitwise AND operator. My attempts to use it got me an illegal character error. Back to may main questions: 1. How to I select all temp tables? 2. How do I get what I need from bitval() on a 16-bit smallint column? Thanks much! -- +----- Jacob Salomon - DBA JSalomon@bn.com - --------------------------+ |------------------- Bulletin Board Announcement ----------------------| | Congregants will please note that the bowl at the back of the church | | bearing the sign "For the Sick" is for monetary contributions only. | +----------------------------------------------------------------------+ Sent via Deja.com http://www.deja.com/ Before you buy.