Informix

nested-group-icon.png

DB2

Expand all | Collapse all

Associate a container partition with its table

  • 1.  Associate a container partition with its table

    Posted Tue July 07, 2020 11:01 PM
    Hi again (Twice in one day!  Oh NO!  :-)

    More enlightenment sought, though this is of more practical application.

    Another discovery I recently made is that a container is a separate TBLspace/Partition.  I have partitions named raw_container and mult_container (and possibly a few more) showing up in my partitions report.  Looking at the schema for my database I see, for example:

    create table "jake".ts_data_v
      (
        loc_esi_id char(20) not null ,
        measure_unit varchar(10) not null ,
        direction char(1) not null ,
        tstamp datetime year to fraction(5),
        value decimal(14,3)
      ) using ts_vtam
      (
        basetabname='ts_data',
        tsfirstcolidx='3',
        tscolcnt='2',
        tsscanmode='0',
        tscolname='raw_reads',
        tselemtype='meter_data',
        tsprimarykey='1:2:3',
        nts='origin(2010-11-10 00:00:00.00000),calendar(cal15min),container(raw_container),threshold(0),regular'
    );

    However, not having read every manual cover to cover, I am at a loss to locate a system catalog that associates a container with its table.  My current scheme is that if a table has a partition header [in sysptnhdr] but no catalog entry, it must be a container.  Somehow, I'm pretty sure that's wrong.

    So in which table can I find such an association?  Hey, I can associate an index with its table by joining systables with sysindexes but what tables (catalogs?) can I join to associate the container with its table?  In the above case, a row describing container raw_container and mapping back up to table, ts_vtam.

    Thanks for light here.



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


  • 2.  RE: Associate a container partition with its table

    Posted Tue July 07, 2020 11:22 PM
    AFAIK there is no way of going from a container back to a table. 

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 3.  RE: Associate a container partition with its table

    Posted Tue July 07, 2020 11:30 PM
    But you can look at the TScontainer and find all the partitions the containers use, simple regex query I  believe

    Cheers
    Paul

    Paul Watson
    Oninit LLC
    +1-913-387-7529
    www.oninit.com
    Oninit®️ is a registered trademark of Oninit LLC





  • 4.  RE: Associate a container partition with its table

    Posted Wed July 08, 2020 07:19 AM
    Jacob:

    Multiple timeseries can be stored in a single container and a single timeseries can be spread across multiple containers, so you are lost there. There is a "catalog table", but it is just a timeseries catalog table: tscontainertable, so you can identify containers in a database. 

    However, there is no simple way to identify what timeseries use a given container. Myschema does go the other way to produce the VTAM details for the schema output for a table with timeseries columns, but harder to go the other way.

    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.








  • 5.  RE: Associate a container partition with its table

    Posted Wed July 08, 2020 10:04 AM
    Thanks MUCH Art and Paul.

    This information informs my actions and I know how to proceed from here:  I add another query to join up the few rows from tscontainertable to the partitions hash I had obtained from my big sysmaster query.  (Working in Perl.) The partition type will be marked as C (for container).

    I would presume there is no fragmentation scheme available for a container TBLspace. (Looking for trouble? :-)

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



  • 6.  RE: Associate a container partition with its table

    Posted Wed July 08, 2020 10:12 AM

    If you want to break things try detaching the indices on the TS* tables

     

    I sometimes explain a container as a special type of dbspace, and a timeseries row as a table within that space – not a perfect comparison but .....  Non-informix people can struggle with TS

     

    Cheers

    Paul

     






  • 7.  RE: Associate a container partition with its table

    Posted Wed July 08, 2020 11:36 AM
    This ts_data_v table is a virtual table over real table ts_data, I'd say.

    Without rows in ts_data, there's no "container associated with this table".  As soon as you create a row, and with that a timeseries, the timeseries will go into a container. You'd ask GetGontainerName(ts) for each timeseries in this table to learn which containers are in use.

    There's also a tsinstancetable, with a record for every timeseries instance in the database, which would inform you about container_name, but for this you'd have to have the ts instance ID.

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



  • 8.  RE: Associate a container partition with its table

    Posted Wed July 08, 2020 12:19 PM

    My understanding if you use the default threshold then the first few elements of the TS will be in row (max 1500bytes), once the threshold is reached then elements will be in the container specified or an auto container. 

     

    The docs implies that the in-rows elements would be moved to the container, I don't believe this was always the case. 

     

    Cheers

    Paul

     






  • 9.  RE: Associate a container partition with its table

    Posted Wed July 08, 2020 03:48 PM
    Andreas,

    As Art (and, I think, Paul) pointed out, many tables may share a container.  Kinda like a blobspace in that respect;  just as the blobspace can be associated with many tables, so is the container a pool resource for many tables.  The best I can do for my report utility is simply identify it and the amount of space it occupies and intelligently inform the user that this is a container, not a table.

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



  • 10.  RE: Associate a container partition with its table

    Posted Wed July 08, 2020 07:17 PM

    It is a resource for multiple TS rows

     

    Cheers

    Paul