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

#Informix

Original Message:
Sent: 7/7/2020 11:22:00 PM
From: Paul Watson
Subject: RE: Associate a container partition with its table

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

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

Original Message:
Sent: Wed July 08, 2020 07:18 AM
From: Art Kagel
Subject: Associate a container partition with its table

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

www.askdbmgt.com

Blog: http://informix-myview.blogspot.com/

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:
Sent: 7/8/2020 10:04:00 AM
From: Jacob Salomon
Subject: RE: Associate a container partition with its table

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

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

Original Message:
Sent: 7/8/2020 11:36:00 AM
From: Andreas Legner
Subject: RE: Associate a container partition with its table

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

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

Original Message:
Sent: Wed July 08, 2020 11:36 AM
From: Andreas Legner
Subject: Associate a container partition with its table

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

Original Message:
Sent: 7/8/2020 3:48:00 PM
From: Jacob Salomon
Subject: RE: Associate a container partition with its table

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