Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only
  • 1.  Column ti_flags in systabinfo

    Posted Wed May 07, 2025 05:22 PM

    Man! This guy just keeps going!!  <grin>

    About 9 years ago I wrote a perl module (FragmentList.pm) to consolidate information about data and index partitions. I may even have posted it as part of a contribution to the IIUG Software Repository.  That aside: In that module I have the following mask settings for various types of partitions:

    $flag_systemp  = eval    '0x20';     # System-created temp table
    $flag_usrtemp  = eval    '0x40';     # User created temp tables
    $flag_sorttemp = eval    '0x80';     # Sort table
    $flag_hashtemp = eval '0x20000';     # [System-created] hash table
    $flag_anytemp  = $flag_systemp  | $flag_usrtemp
                   | $flag_sorttemp | $flag_hashtemp;  # Mask: Any of the above
    $flag_indexptn = eval '0x40000';     # Recognize an index partition

    I can't remember where I got these tidbits!  I don't see them in the most logical place to look: sysmaster.sql.  Did I post a question to this forum asking about it?  If so, where did my answerer get them?  In general, there are "flags" columns everywhere in sysmaster but no documentation on the individual bits in those flag columns.



    ------------------------------
    Jacob Salomon
    ------------------------------


  • 2.  RE: Column ti_flags in systabinfo

    Posted Wed May 07, 2025 05:39 PM

    As you know, systabinfo is a view on sysptnhdr.  In $INFORMIXDIR/etc/sysmaster.sql you will see inserts into flags_text for sysptnhdr:

    { Partition Header }

        insert into flags_text values ('sysptnhdr', 1, 'Page Level Locking');
        insert into flags_text values ('sysptnhdr', 2, 'Row Level Locking');
        insert into flags_text values ('sysptnhdr', 4, 'System Catalog Table');
        insert into flags_text values ('sysptnhdr', 8, 'Replicated Table');

        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');

        insert into flags_text values ('sysptnhdr', 256,'Contains Varchar Data Type');
        insert into flags_text values ('sysptnhdr', 512,'Contains BLOBSpace BLOBS');
        insert into flags_text values ('sysptnhdr', 1024,'Contains TBLSpace BLOBS');
        insert into flags_text values ('sysptnhdr', 2048,'Contains either Varchars,BLOBS or Rows > PAGESIZE-32');

        insert into flags_text values ('sysptnhdr', 4096,'Contains optical Sub-System BLOBS');
        insert into flags_text values ('sysptnhdr', 8192,'Permanent System created Table ( undroppable )');
        insert into flags_text values ('sysptnhdr', 16384,'Special Function Temp Tables, no Bitmap Maintenance');
        insert into flags_text values ('sysptnhdr', 32768,'Light Append Partition');

        insert into flags_text values ('sysptnhdr', 131072,'Hash Table');
        insert into flags_text values ('sysptnhdr', 262144,'Index Partition');
        insert into flags_text values ('sysptnhdr', 524288,'Sequence Object');
        insert into flags_text values ('sysptnhdr', 1048576,'Page free space cache disabled');
        insert into flags_text values ('sysptnhdr_flags2', 1,'Automatic Compression');

    Alternatively, run a query against sysmaster:flags_text:

    select flags, hex(flags) hflags, txt
    from flags_text
    where tabname = "sysptnhdr"



    ------------------------------
    Mike Walker
    xDB Systems, Inc
    www.xdbsystems.com
    ------------------------------



  • 3.  RE: Column ti_flags in systabinfo

    Posted Wed May 07, 2025 06:00 PM

    Thanks, Mike, for reviving that old memory!  That's exactly where I got it originally but my search didn't include this flags_text table.  And I think that then also, someone had pointed me to this able.  Was that Art? or Paul Watson?

    Thanks much again.



    ------------------------------
    Jacob Salomon
    ------------------------------