Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Partition number for external tables?

  • 1.  Partition number for external tables?

    Posted Tue July 07, 2020 05:58 PM
    Hi Family.

    Enlightenment requested:

    I am working on a far better version (a considerable rewrite) of my FragmentList.pm package and fragments.pl utility tha tI had put up on IIUG some years ago.  In the process I have made an interesting discovery in the sysptnhdr table in sysmaster.

    For example, in my demo database stores10k I have an external table named ext_customer.  As it happens, my program's first encounter with it is in a query that systabnames and sysptnhdr.  Thus I have a partition number for it.  However, in the catalog systables, its tabtype = "E" and, indeed, the schema for this "table" explicitly defines ext_customer as an external table with that is actually a .unl file. Actually: disk:INFORMIXDIR/demo/dbaccess/demo_ids/ext_customer.unl.

    I created the database in DBspace data10kdbs and the partition number indicates this as the DBspaces for the table. The information my program currently displays for the external table looks like this:

    |Partnum |Table/Index                |PT|P-Size|DBspace   |FS|Partn-Name|Pos|Nrows|Extents|NP-Used|NP-Total|
    |0xe00045|stores10k:jake.ext_customer|E |    10|data10kdbs|- |-         |  0|    0|      0|      0|       0|

    The PT is Partition Type.  It is is T for tables and I for indexes.  Note the E in this case.  I have supplied a DBspace name here but, consistent with the partition number; indeed data10dbs is DBpsace[14] (0xE).   But this is a lie!  The data is not there at all!

    What possible meaning could a partition number have for an external table?  I would prefer that my program display "(file)" rather than a that DBspace for the information is displays.  How does this even start to make sense?

    Just requesting an explanation.

    Thanks.





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


  • 2.  RE: Partition number for external tables?

    Posted Tue July 07, 2020 06:28 PM
    Jacob:

    A table's partition number is the location of its TABLESPACE TABLESPACE header page, ie the page displayed in part in sysptnhdr. Even an external table has a header page.

    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: Partition number for external tables?

    Posted Tue July 07, 2020 08:44 PM
    Thanks much, Art.

    So for a regular table, the partition header page truly represents the partition, including the extent list. So you can see why someone would conflate the two functions of the header page.  But for an external table, there is a partition header page without the actual partition.
    Here's a little sample of the output:
    Partitions and fragment descriptions of selected tables

    So notice the line for stores10k:jake.ext_customer.  HMMmm.. The incorrigible tinker in me.. Maybe I should say (external) rather than (file)?

    Again, Thank you Art.

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