Topic Thread

Expand all | Collapse all

Table or index partition? Flags in sysptnhdr

  • 1.  Table or index partition? Flags in sysptnhdr

    Posted Mon March 16, 2020 04:13 PM
    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
    ------------------------------


  • 2.  RE: Table or index partition? Flags in sysptnhdr

    Posted Mon March 16, 2020 04:46 PM
    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.








  • 3.  RE: Table or index partition? Flags in sysptnhdr

    Posted Mon March 16, 2020 06:07 PM
    I was also thinking about how to separate tables and indexes through sysmaster, but I gave up because I couldn't identify the flag value.
    That's a good tip!

    ------------------------------
    SangGyu Jeong
    Software Engineer
    Infrasoft
    Seoul Korea, Republic of
    ------------------------------



  • 4.  RE: Table or index partition? Flags in sysptnhdr

    Posted Mon March 16, 2020 06:41 PM
    That's a great start, Art.  However....
    ncols == 0 does not mean it has no columns.  In usual Informix style (I have always chided them for confusing nomenclature), ncols is actually the number of special columns, like blob or varchar.  Indeed, for the table I arbitrarily picked as my example,
    ncols == 2, and it has 2 varchar columns, but a total 12 columns.  Furthermore, the definition of table sysptnhdr in sysmaster.sql has this column definition and comment:
    ncols smallint, { number of varchar or blob columns }

    In fact, I have found tables (like systabauth) with a few columns but ncols == 0.

    The other flag you kicked around, and mod((flags / 4), 2) = 0, will be quite helpful however, since by default I do exclude catalogs from the display.  But I might want to use and bitand(sp.flags, 4) = 0  to exclude a catalog.  I just tried it; it works to identify a partition as a catalog when looking for "systables".

    But thus far, a way to distinguish a table partition from an index partition remains elusive.

    Hey, the floor is still open....  (This forum needs smileys!)

    Again, thanks fr that other tip!


    ------------------------------
    Jacob Salomon
    ---
    Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
    ------------------------------



  • 5.  RE: Table or index partition? Flags in sysptnhdr

    Posted Thu March 19, 2020 10:03 AM
    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
    ------------------------------



  • 6.  RE: Table or index partition? Flags in sysptnhdr

    Posted Thu March 19, 2020 10:58 AM
    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
    ------------------------------



  • 7.  RE: Table or index partition? Flags in sysptnhdr

    Posted Thu March 19, 2020 12:46 PM
    Edited by Andreas Legner Thu March 19, 2020 12:52 PM
    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
    ------------------------------



  • 8.  RE: Table or index partition? Flags in sysptnhdr

    Posted Thu March 19, 2020 03:36 PM
    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.lockid
    lockid 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, tabname
    And 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 > 1
    There 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.tabname

    I 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
    ------------------------------



  • 9.  RE: Table or index partition? Flags in sysptnhdr

    Posted Mon April 06, 2020 11:06 AM
    Hi Y'all.

    I started this thread about 3 weeks ago based on the erroneous assumption that there is a flag in some sysmaster table that would tell me if a partition is a table or index partitions.  Andreas started to set me straight by pointing me to columns lockid and partnum in table sysptnhdr and throwing nkeys == 0 into the mix.  I was not quite comprehending of his response and I had found what I mistook for counterexamples.  I won't recount my mistakes here but I would like to share the understanding I've gained from using Andreas's advice.  Here are the major points I had to rediscover:
    • In sysptnhdr, column lockid is the partition number that will show up in onstat -k as the table with the lock. Note that I have not quite verified this.
    • For your run-of-the-mill standard table, column partnum == lockid.  (I'll discuss nkeys shortly)
    • For a fragmented table, lockid == partmum only for the first partition; for all other partitions, lockid = the partnum of the first partition of that table, not the partition itself.
    • For a detached index, the kind that shows up on systabnames and sysptnhdr, lockid is the partnum of the first (or only) partition of its table.
      And nkeys == 1, all as Andreas explained to my uncomprehending self.  Of course, an attached index (they still exist) won't show up in these SMI tables.
    • I noticed some database-level system catalogs have nkeys == 2, 3, 4, or 6.  I also observed that these seemed to have no associated  index partitions.  Finally I realized what nkeys really is: nkeys == the number of attached indexes on the table.  I was able to verify this many times using dbaccess/Query language/Info/Indexes.
    So the almost perfect way to distinguish an index partition is that it satisfies both of these two conditions:
    1. sysptnhdr.lockid != sysptnhdr.partnum
    2. nkeys == 1
    Why is this not the perfect distinction?  Because these conditions would also be satisfied by a fragmented table with exactly one index, attached.  I think I can live with that one off-the-wall case; nobody deliberately creates an attached index, let alone for a fragmented table.  Why the catalogs have attached indexes is a question for the developers at IBM but I see it as the initialization code of an OS: The infrastructure is not yet in place for it to follow the rules it sets out for everyone else.

    By the way, running dbschema on catalogs seems to be futile, which never occurred to me in 25+ years of working with Informix.  Observe:
    $ dbschema -ss -d (hidden) -t systables$ dbschema -ss -d (hidden) -t systables
    DBSCHEMA Schema Utility       INFORMIX-SQL Version 12.10.FC12

    No table or view systables.


    This ought to conclude the question.  Again, kudos to Andreas for kick-starting me on this answer.

    ------------------------------
    Jacob Salomon
    ---
    Nobody goes there anymore, it's too crowded.  --Attr: Yogi Berra
    ------------------------------



  • 10.  RE: Table or index partition? Flags in sysptnhdr

    Posted Mon April 06, 2020 01:02 PM
    Yes, a partitioned table with an attached index would look just like an index in sysptnhdr as far as nkeys and lockid.

    On your last observation, yes, dbschema will not report on catalog tables nor on any tables in sysmaster, not even the handful of real tables and views in there. Myschema will do both if asked however. 

    Oh, and yes, the indexes in many of the catalog tables are attached indexes (see the myschema output below for systables).

    Witness:

    art@Elbereth:~/Desktop$ dbschema -ss -d sysmaster -t flags_text

    DBSCHEMA Schema Utility       INFORMIX-SQL Version 14.10.FC3

    Database has pseudo tables - can't build schema. 

    art@Elbereth:~/Desktop$ myschema -s -d sysmaster -t flags_text             
    Writing full schema DDL to:       stdout
    { TABLE "informix".flags_text row size = 333 number of columns = 3 index size = 204 }
    CREATE TABLE "informix".flags_text (
           tabname CHAR(128),
           flags INTEGER,
           txt VARCHAR(200,0)
    ) IN rootdbs EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW;

    {
           Please review extent sizing and adjust to allow for growth.
    }

    REVOKE ALL ON "informix".flags_text FROM public AS "informix";
    CREATE UNIQUE INDEX "informix".flags_text_ix1 ON "informix".flags_text (
           tabname ASC,
           flags ASC
    ) USING btree IN rootdbs;


    GRANT SELECT ON "informix".flags_text TO "public" AS "informix";

    art@Elbereth:~/Desktop$ myschema -s -d sysmaster -t systabnames
    Writing full schema DDL to:       stdout
    { TABLE "informix".systabnames row size = 328 number of columns = 6 index size = 0 }
    CREATE TABLE "informix".systabnames (
           partnum INTEGER,
           dbsname CHAR(128),
           owner CHAR(32),
           tabname CHAR(128),
           collate CHAR(32),
           dbsnum INTEGER
    )
     EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW;

    {
           Please review extent sizing and adjust to allow for growth.
    }

    REVOKE ALL ON "informix".systabnames FROM public AS "informix";
    CREATE UNIQUE INDEX "informix".systabs_pnix ON "informix".systabnames (
           partnum ASC
    ) USING btree IN TABLE;


    CREATE INDEX "informix".systabs_pnix2 ON "informix".systabnames (
           dbsnum ASC
    ) USING btree IN TABLE;


    GRANT SELECT ON "informix".systabnames TO "public" AS "informix";

    art@Elbereth:~/Desktop$ myschema -s -d art -t systables
    Writing full schema DDL to:       stdout
    { TABLE "informix".systables row size = 500 number of columns = 26 index size = 259 }
    CREATE TABLE "informix".systables (
           tabname VARCHAR(128,0),
           owner CHAR(32),
           partnum INTEGER,
           tabid SERIAL(357),
           rowsize SMALLINT,
           ncols SMALLINT,
           nindexes SMALLINT,
           nrows FLOAT,
           created DATE,
           version INTEGER,
           tabtype CHAR(1),
           locklevel CHAR(1),
           npused FLOAT,
           fextsize INTEGER,
           nextsize INTEGER,
           flags SMALLINT,
           site VARCHAR(128,0),
           dbname VARCHAR(128,0),
           type_xid INTEGER,
           am_id INTEGER,
           pagesize INTEGER,
           ustlowts DATETIME YEAR TO FRACTION(5),
           secpolicyid INTEGER,
           protgranularity CHAR(1),
           statchange SMALLINT,
           statlevel CHAR(1)
    ) IN datadbs EXTENT SIZE 16 NEXT SIZE 16 LOCK MODE ROW;

    {
           System catalog table.
    }

    REVOKE ALL ON "informix".systables FROM public AS "informix";
    CREATE UNIQUE INDEX "informix".tabid ON "informix".systables (
           tabid ASC
    ) USING btree IN TABLE;


    CREATE UNIQUE INDEX "informix".tabname ON "informix".systables (
           tabname ASC,
           owner ASC
    ) USING btree IN TABLE;


    GRANT SELECT ON "informix".systables TO "public" AS "informix";



    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.








  • 11.  RE: Table or index partition? Flags in sysptnhdr

    Posted Tue April 07, 2020 04:46 AM
    It's even better:  there's no such thing as a "fragmented table with exactly one index, attached", at least not in the sense that you can have a fragmented table whose indices and data share same partition/fragment.   So "sysptnhdr.lockid != sysptnhdr.partnum and nkeys != 0" indeed is the criteria for an index partition (and nkeys would always be 1).
    Fragmented tables always imply that indices will get their own partitions, so will be detached, and, depending which 'school' you're adhering to, these would be semi- or fully detached or, as some call them, attached and detached.  The distinction here is whether an index has its own storage clause (fully detached) or not (following table's storage options, so no need to store a row's partition info alongside the rowid pointer -> semi-detached).

    ------------------------------
    Andreas Legner
    ------------------------------



  • 12.  RE: Table or index partition? Flags in sysptnhdr

    Posted Mon April 20, 2020 05:32 AM
    Hello, would not help this info from sysmaster.sql (from 12.10.FC13)?:

    insert into flags_text values ('sysptnhdr', 262144,'Index Partition');

    ------------------------------
    Milan Rafaj
    ------------------------------



  • 13.  RE: Table or index partition? Flags in sysptnhdr

    Posted Mon April 20, 2020 10:30 AM
    Hi Milan,

    good catch - yet this flag isn't in use really (should be removed from this list).
    Let us know if you're able to see an index (or any other) partition carrying this flag.

    BR,
     Andreas

    ------------------------------
    Andreas Legner
    ------------------------------



  • 14.  RE: Table or index partition? Flags in sysptnhdr

    Posted Mon April 20, 2020 12:21 PM
    Hi Andreas,
    I am afraid, you should be right, but would be nice if working:-)

    Milan

    ------------------------------
    Milan Rafaj
    ------------------------------



  • 15.  RE: Table or index partition? Flags in sysptnhdr

    Posted Mon March 23, 2020 09:10 AM
    Thank you!

    ------------------------------
    Gabor Torda
    Keresomarketing.com
    ------------------------------