ndreas,
I like your query and I've done some of own observations, very similar to what I've found. I think the main kicker is that one clause (expanded for clarity)
where sysptnhdr.partnum != sysptnhdr.lockidlockid is the partition number that will be listed in the locks structures. (Again, Informix using misleading nomenclature.) For a table lock the two will be the same value. But for an index lock, the lockid is still the partnum of the table (or fragment, but that's another story.) My query does not [yet] use the case clause because I was looking to see the difference.
select hex(tn.partnum) partition, hex(ph.lockid) lockpartn, tn.dbsname[1,12], -- 3-component fully qualified table name: tn.owner[1,12], -- database:ower.table tn.tabname, --hex(ph.flags) flags, --bitand(ph.flags, 4) is_catalog, ph.nkeys from systabnames tn, sysptnhdr ph where tn.partnum = ph.partnum and bitand(ph.flags, 4) = 0 -- Skip catalogs --and ph.nkeys > 1 -- Seems to be 0 for most table, 1 for indexes. order by dbsname, owner, tabnameAnd indeed, all the indexes I could eyeball has nkeys == 1. Note that in sysmaster.sql, the comment on nkeyes is quite misleading:
nkeys smallint, { number of indexes } (OK, just plain WRONG!)
Note that clause that I commented out:
and ph.nkeys > 1There were a number of tables where this ranged between 2 and 6. But that is another can of worms. So I am reluctant to trust nkeyes == 1 as a reliable indicator of whether this is an index or table partition.
Conclusion: The most reliable indicator of whether a partition is table or index seems to be whether
partnum == lockid. I just thought of a query that could verify this, rub from a user database, not from sysmaster:
select idxname, hex(tn.partnum) partition, hex(ph.lockid) lockpartn, tn.dbsname[1,12], -- 3-component fully qualified table name: tn.owner[1,12], -- database:ower.table tn.tabname, ph.nkeys from sysmaster:systabnames tn, sysmaster:sysptnhdr ph, sysindexes ix where tn.partnum = ph.partnum and tn.tabname = ix.idxname and bitand(ph.flags, 4) = 0 -- Skip catalogs and ph.partnum = ph.lockid -- This should exclude indexes, according to my theory --and ph.nkeys > 1 -- Seems to be 0 for most table, 1 for indexes. order by tn.dbsname, tn.owner, tn.tabnameI would expect this to return 0 rows because of that partnum = lockid clause. Alas, my users had one over on me: There are a few indexes in the database there are named exactly like their tables. On the other hand, if I add the clause
and ph.nkeys = 1, it indeed returns 0 rows.
So, Andreas, I think you have provided my answer. I don't think npused can ever be 0, since there is a bitmap in each extent. And npdata can be 0 for an empty, newly created table (I think.) But these two other conditions almost certainly identify an index partition.
Now if someone can start a new thread to explain the use of nkeys and what it means when nkeys > 1, we might really get some good science in there.
Thank you all for the effort. A participation medal for anyone who had the patience to follow this thread!
------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra
------------------------------
Original Message:
Sent: Thu March 19, 2020 12:46 PM
From: Andreas Legner
Subject: Table or index partition? Flags in sysptnhdr
syspthhdr.lockid always point to the "table" a partition belongs to:
select hex(p.partnum) || ": " || trim(n.dbsname)||":"||trim(n.owner)||"."||trim(n.tabname) partnum_partn_name, hex(lockid) || ": " || trim(tn.dbsname)||":"||trim(tn.owner)||"."||trim(tn.tabname) lockid__table_name, CASE WHEN p.partnum != p.lockid and p.nkeys = 1 THEN "INDEX" WHEN p.npdata > 0 and p.nkeys > 0 THEN "DATA & INDEX" ELSE "DATA" END partn_type from sysptnhdr p, systabnames n, systabnames tn where p.partnum = n.partnum and p.lockid = tn.partnum;
HTH!
------------------------------
Andreas Legner
Original Message:
Sent: Thu March 19, 2020 10:57 AM
From: Jacob Salomon
Subject: Table or index partition? Flags in sysptnhdr
Thanks, Andreas.
As with Art's suggestion, I will try to verify this on a heavily populated production server. However...
My initial motive for me to want this ability to distinguish an index partition from a table partition was to avoid having to go into each individual database to fill in the information omitted in the sysmaster tables/views. That's why I so appreciated Art's other tip, about a flag to recognize a database catalog. But yesterday it occurred to me that I am not relieved of that requirement with regard to index partitions. Here's the reason:
Even with some fool-proof (and I've been known to defeat many a fool-proof design by being the better fool :-) indicator that this partition is an index, it leaves me no clue as to which table it indexes. (Nor should it, IMO.) That information is available ONLY in the system catalogs, by joining sysindexes with systables on the tabid column. So I have to go into each database anyway.
I also just realized the the package I'm trying to streamline is actually fragments.shar, not DBspaces. Sorry about the wrong name in my initial post.
OK, time to start verifying!
------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra
Original Message:
Sent: Thu March 19, 2020 10:03 AM
From: Andreas Legner
Subject: Table or index partition? Flags in sysptnhdr
Don't need flags, they don't tell about partition type, check instead sp.nkeys = 1 and sp.partnum != sp.lockid (and sp.npused > 1 and sp.npdata = 0, but not necessary) for an index partition.
------------------------------
Andreas Legner
Original Message:
Sent: Mon March 16, 2020 04:45 PM
From: Art Kagel
Subject: Table or index partition? Flags in sysptnhdr
Try this one:
select *
from systabnames st, sysptnhdr sp
where st.partnum = sp.partnum
and sp.npused > 1 { More than just a single bitmap page }
and sp.npdata = 0 { Empty }
and sp.ncols = 0 { Empty }
and sp.nrows = 0 { Empty }
and st.tabname != 'TBLSpace' { Not a tablespace tablespace }
and mod((flags / 4), 2) = 0; { Not a catalog table }
Art
Art S. Kagel, President and Principal Consultant
ASK Database Management
Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves.
Original Message------
Hi folks.
Back in my usual form, verbose as all get-out, for fear of being misunderstood. (I wouldn't mind so much being a misunderstood genius, but I'm missing a vital attribute to fit that designation. :-)
Bottom line at the top:
How can I tell from the sysmaster "tables" alone, whether a partition is a table or index partition? Without using sysfragments in an individual database?
Commentary:
I am trying to improve on an existing script, DBspaces, which I published to IIUG before it went all GITty. Currently, the way I can tell if a partition is a single table, a tabler fragment, or an index partition, is by looking into sysfragments of each database and examining fragtype. I for index, T for table fragment, and no entry in sysfragments means the partition is a simple table. I seek to improve on this by finding this out from systabnames and sysptnhdr.flags. Here is a quick query, with database & table names heavily edited to to protect my client.
select
hex(tn.partnum) partition,
tn.dbsname[1,12],
tn.owner[1,12],
tn.tabname[1,20],
hex(ph.flags) partn_flags1
from systabnames tn,
sysptnhdr ph
where tn.partnum = ph.partnum
and tn.tabname like "batman%"
And here is the output:
partition dbsname owner tabname partn_flags1
0x0220007A batcave alfred batman 0x00800902
0x02B000DB batcave alfred batman_ix1 0x00000802
0x02B000DC batcave alfred batman_ix2 0x00000802
0x02B000DD batcave alfred batman_ix3 0x00000802
(BTW, there is a second flags2 columns in sysptnhdr; all zeros so I left it out of this example.)
Well, I figured there oughta be a flag turned on among the flags of each of the three index partition to indicate that it is indeed an index partition. But, no, the table's partition header actually has two flags turned on that are off for the three index partitions. Just for reference I checked out the flags_text table in sysmaster.flags_text. Here's what I found:
insert into flags_text values ('sysptnhdr', 262144,'Index Partition');
This corresponds to 0x00040000, which is not turned on in any of the above flag-words. Looks like I'm barking up the wrong flagpole and saluting the wrong tree. :)
This leads in to my bottom line question:
How can I tell from the sysmaster "tables" alone, whether a partition is a table or index partition? Is there another sysmaster table I should be chasing? (BTW, systabinfo is a view on sysptnhdr, a subset of the columns.)
(I've done worse. :-)
------------------------------
Jacob Salomon
---
Nobody goes there anymore, it's too crowded. --Attr: Yogi Berra
------------------------------
#Informix