TSContainerCreate procedure

The TSContainerCreate procedure creates a container to store the time series data for the specified TimeSeries subtype. You can create a container in one dbspace, a container that spans multiple partitions, or a rolling window container, which controls the amount of data that is stored.

Only users with update privileges on the TSContainerTable table can run this procedure.

Only users with update privileges on the TSContainerTable table and the TSContainerWindowTable can run this procedure.

Rolling window containers are a special type of container that requires additional arguments. See Syntax for rolling window containers. Use the rolling window container syntax to create a container that spans multiple partitions.

Syntax

TSContainerCreate(container_name varchar(128,1), 
                  dbspace_name   varchar(128,1), 
                  ts_type        varchar(128,1), 
                  container_size integer, 
                  container_grow integer);
            
container_name
The name of the new container. The container name must be unique.
dbspace_name
The name of the dbspace that holds the container. If the container is being created in a tenant database, the dbspace must be listed by the dbspace property in the tenant database definition.
Can be NULL. Then the container is created in the same dbspace as the table. For a tenant database, then the container is created in the dbspace that is specified for tenant database catalogs.
ts_type
The name of the TimeSeries subtype that is stored in the container. This argument must be the name of an existing row type that begins with a time stamp.
container_size
The first extent size of the container, in KB.
The value must be equivalent to at least 4 pages. If you specify 0 or a negative number, 16 KB is used. The maximum size of a container depends on the page size:
  • For 2-KB pages, the maximum size is 32 GB.
  • For 4-KB pages, the maximum size is 64 GB.
  • For 8-KB pages, the maximum size is 128 GB.
  • For 16-KB pages, the maximum size is 256 GB.
container_grow
The increments by which the container grows, in KB. The value must be equivalent to at least 4 pages. If you specify 0 or a negative number, 16 KB is used.

Usage

By default, containers are created automatically as needed when you insert data into a time series. However, you can create additional containers by using the TSContainerCreate procedure.

As a result of the TSContainerCreate procedure, the database server creates the container when the first time series is inserted into that container. You can create multiple containers in the same dbspace.

When you create a container, a row is inserted in the TSContainerTable table.

Example

The following example creates a container that is called new_cont in the space rootdbs for the time series type stock_bar:
execute procedure TSContainerCreate('new_cont', 'rootdbs','stock_bar', 0, 0);

Syntax for rolling window containers

TSContainerCreate(container_name     varchar(128,1), 
                  dbspace_name       varchar(128,1), 
                  ts_type            varchar(128,1), 
                  container_size     integer, 
                  container_grow     integer,
                  window_origin      datetime year to fraction(5));

TSContainerCreate(container_name     varchar(128,1), 
                  dbspace_name       varchar(128,1), 
                  ts_type            varchar(128,1), 
                  container_size     integer, 
                  container_grow     integer,
                  window_origin      datetime year to fraction(5),
                  window_interval    lvarchar default 'month',
                  active_windowsize  integer default 0,
                  dormant_windowsize integer default 0,
                  window_spaces      lvarchar(4096) default null,
                  window_control     integer default 0,
                  rwi_firstextsize   integer default 16,
                  rwi_nextextsize    integer default 16,
                  destroy_count      integer default 0,
                  future_count       integer default 0);
container_name
The name of the new container. The container name must be unique.
dbspace_name
The name of the dbspace that contains the container partition. If you do not specify additional dbspaces with the window_spaces argument, this dbspace also contains the partitions for time series elements. If the container is being created in a tenant database, the dbspace must be listed by the dbspace property in the tenant database definition.
Can be NULL. Then the container is created in the same dbspace as the table. For a tenant database, then the container is created in the dbspace that is specified for tenant database catalogs.
ts_type
The name of the TimeSeries subtype that is stored by the container. This argument must be the name of an existing row type that begins with a time stamp.
container_size
The first extent size for partitions, in KB.
The value must be equivalent to at least 4 pages. If you specify 0 or a negative number, 16 KB is used. The maximum size of a partition depends on the page size:
  • For 2 KB pages, the maximum size is 32 GB.
  • For 4 KB pages, the maximum size is 64 GB.
  • For 8 KB pages, the maximum size is 128 GB.
  • For 16 KB pages, the maximum size is 256 GB.
container_grow
The next extent size for partitions, in KB. The value must be equivalent to at least 4 pages. If you specify 0 or a negative number, 16 KB is used.
window_origin
The first timestamp that is allowed for the rolling window container. The rolling window container rejects time series values if the origin of the time series is before the origin of the container.
window_interval
The range of time for which data is stored in each partition. By default, the window interval is one month, which means that each window partition contains data from one calendar month. Possible values are:
  • day = The partitions contain data from one day. The container uses the ts_1day calendar.
  • week = The partitions contain data from one week. The container uses the ts_1week calendar.
  • month = Default. The partitions contain data from one calendar month. The container uses the ts_1month calendar.
  • year = The partitions contain data from one calendar year. The container uses the ts_1year calendar.
  • the name of a user-defined calendar in the CalendarTable table = The partitions contain data for the time period that is specified by the pattern in the calendar. The pattern specification for window interval calendars must have the format of {1 on} or {1 on, n off}. The length of the window interval is the sum of the on and off interval units: 1 + n. For example, the following pattern results in a window interval of 3 months: pattern({1 on,2 off}, month)
active_windowsize (Optional)
The maximum number of partitions in the active window:
  • 0 = Default. No size limit.
  • Positive integer = The maximum number of partitions in the active window.
dormant_windowsize (Optional)
The maximum number of partitions in the dormant window:
  • 0 = Default. No size limit.
  • Positive integer = The maximum number of partitions in the dormant window. Start with a value that is equal to or greater than the size of the active window.
window_spaces (Optional)
The dbspaces in which partitions are stored:
  • NULL = Default. Partitions are created in the dbspace that is specified by the dbspace_name argument.
  • A comma-separated list of dbspace names = Partitions are created in the listed dbspaces in round-robin order. The list cannot include temporary dbspaces or sbspaces. If the container is being created in a tenant database, the dbspaces must be listed by the dbspace property in the tenant database definition.
window_control (Optional)
An integer that is the sum of flag values that represent how many partitions are destroyed, whether active partitions can be destroyed, and whether to limit partitions for future dates. Possible values are: 0, 1, 2, 3 (1 + 2), 4, 5 (1 + 4), 6 (2 + 4), 7 (1 + 2 + 4).
  • 0 = Default. As many as necessary dormant partitions are destroyed. If the operation requires more new active partitions than the value of the active_windowsize parameter, the operation fails.
  • 1 = As many as necessary existing dormant partitions and older active partitions are destroyed. Use this setting with caution. Destroyed data cannot be recovered.
  • 2 = Dormant partitions are destroyed, but limited to the number specified by the destroy_count parameter. If the number of partitions that must be destroyed for an operation exceeds the value of the destroy_count parameter, the operation fails.
  • 4 = Partitions for future dates are limited to the number specified by the future_count parameter. If the number of partitions for future dates exceeds the value of the future_count parameter, the operation fails.
When you destroy a partition, the data that is stored in the partition is deleted.
rwi_firstextsize (Optional)
The first extent size, in KB, for the container partition. The default size is 16 KB.
rwi_nextextsize (Optional)
The next extent size, in KB, for the container partition. The default size is 16 KB.
destroy_count (Optional)
How many partitions can be destroyed in an operation. Valid if the window_control parameter includes the value 2.
0 = Default. No dormant partitions are destroyed.
A positive integer = The maximum number of partitions that can be destroyed in an operation. If the number of partitions that must be destroyed for an operation exceeds the value of the destroy_count parameter, the operation fails.
future_count
How many partitions for future dates can be added. Valid if the window_control parameter includes the value 4.
0 = Default. No limit on future partitions.
A positive integer = The maximum number of partitions for future dates that can be added. If the number of partitions for future dates exceeds the value of the future_count parameter, the operation fails.

Usage for rolling window containers

The rolling window container stores information about the properties of the windows and information about the partitions. The partitions store the time series data for specific date ranges. The TSContainerCreate procedure creates the rolling window container when the procedure completes. Partitions are created as needed when you insert time series elements.

To create a rolling window container that stores data in multiple dbspaces and automatically deletes old data, set the following arguments to non-default values:

  • active_windowsize: Set to a positive value to limit to the size of the active window.
  • dormant_windowsize: Set to a positive value to limit to the size of the dormant window.
  • window_spaces: Set to a list of dbspaces.

If you use the default size of 0 for the active window, you create a container that grows until you manually detach partitions into the dormant window and manually destroy partitions from the dormant window.

The container partitions and the partitions in the active and dormant windows can require significantly different amounts of storage space. Plan the storage for rolling window containers carefully.

When you create a rolling window container, a row is inserted in the TSContainerTable and the TSContainerWindowTable table. As partitions are added for time series data, rows are added to the TSContainerUsageActiveWindowVTI and the TSContainerUsageDormantWindowVTI tables.

Example 1: Create a rolling window container

The following example creates a rolling window container:
execute procedure TSContainerCreate('readings_container',
            'containerdbs', 'rt_raw_intvl', 25600, 12800,
            '2011-01-01 00:00:00.00000'::datetime year to fraction(5),
            'month', 4, 10, 'dbs0, dbs1, dbs2, dbs3, dbs4', 1, 16, 8);

The example configures a rolling window container that has the following properties:

  • The container name is readings_container.
  • The dbspace for the container partition is named containerdbs.
  • The name of the time series is rt_raw_intvl.
  • The first extent size of the partitions is 25600 KB.
  • The next extent size of the partitions is 12800 KB.
  • The active window contains up to 4 partitions.
  • The dormant window contains up to 10 partitions.
  • The origin of the container is 2011-01-01 00:00:00.00000.
  • Partitions each hold a month of data.
  • The dbspaces for partitions are named dbs0, dbs1, dbs2, dbs3, and dbs4.
  • Partitions that no longer fit into the dormant window are automatically destroyed.
  • The first extent size of the dbspace containerdbs is 16 KB.
  • The next extent size of the dbspace containerdbs is 8 KB.

Example 2: Create a container with multiple dbspaces

The following example creates a container that stores data in multiple dbspaces but does not use a purging policy:

execute procedure TSContainerCreate('readings_container',
            'containerdbs', 'rt_raw_intvl', 25600, 12800,
            '2011-01-01 00:00:00.00000'::datetime year to fraction(5),
            'month', 0, 0, 'dbs0, dbs1, dbs2, dbs3, dbs4', 0, 16, 8);

The example configures a container that has the following properties:

  • The container name is readings_container.
  • The dbspace for the container partition is named containerdbs.
  • The name of the time series is rt_raw_intvl.
  • The first extent size of the partitions is 25600 KB.
  • The next extent size of the partitions is 12800 KB.
  • The active window size is unlimited.
  • The dormant window size is unlimited.
  • The origin of the container is 2011-01-01 00:00:00.00000.
  • Partitions each hold a month of data.
  • The dbspaces for partitions are named dbs0, dbs1, dbs2, dbs3, and dbs4.
  • Partitions are not automatically destroyed.
  • The first extent size of the dbspace containerdbs is 16 KB.
  • The next extent size of the dbspace containerdbs is 8 KB.

The container stores each month of data in a partition in one of the five dbspaces for partitions. Because the active window size is unlimited, all partitions are active until they are manually detached and then destroyed.