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.
- 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.
- 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
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.
- 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.
- 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.
- 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.
- future_count
- How many partitions for future dates can be added. Valid if the window_control parameter includes the value 4.
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
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.