TSL_SetNewTS function

The TSL_SetNewTS function controls the creation of new time series by a loader program.

Syntax

TSL_SetNewTS(
             handle          LVARCHAR,
             NewTimeSeries   VARCHAR DEFAULT NULL,
             per_session     INTEGER DEFAULT NULL)
returns INTEGER;

TSL_SetNewTS(
            handle          LVARCHAR,
            NewTimeSeries   VARCHAR DEFAULT NULL)
returns INTEGER;

TSL_SetNewTS(
            handle          LVARCHAR)
returns INTEGER;

handle: The table and column name combination that is returned by the TSL_Attach function.
NewTimeSeries (Optional): Controls whether you can insert elements into a time series that does not yet exist in the base table either because the row does not exist or because the row does not yet have a time series instance.; A time series input sting = The definition of the new time series to create. For syntax, see Time series input string. The threshold parameter must be set to 0. If you use current date formatting directives in the origin parameter, the origin is set to the current date when the time series instance is created.; NULL = Default. New time series creation is disabled.
per_session (Optional): Sets the scope of the NewTimeSeries parameter:; 0 = Global. Default. The NewTimeSeries parameter applies to subsequent loading operations in all loader sessions for the specified handle.; 1 = Session. The NewTimeSeries parameter applies to subsequent loading operations in the current loader session for the specified handle. Overrides the global setting of the NewTimeSeries parameter.

Usage

Run the TSL_SetNewTS function to control whether a loader program can insert data that requires a new row in the base table or a new time series in a row that does not have an existing time series instance. By default, if you attempt to insert data for a new primary key value or for a TimeSeries column that is NULL, the data is rejected. You must run the TSL_SetnewTS function in the context of a loader session that was initialized by the TSL_Init function.

If you set the NewTimeSeries parameter to a time series definition, the TSL_Put, TSL_PutRow, TSL_PutJson, and TSL_PutSQL functions can insert data for new time series:

If the primary key value does not exist in the base table, a new row is added to the base table. The primary key value is inserted and a new time series instance is created with the supplied values. Data is inserted into only the primary key columns and the TimeSeries columns, regardless of whether values for other columns are included in the data. The other columns in the base table are filled with default values, if defaults are defined, or else null values. If any of the other columns has a NOTNULL constraint and no default value defined, the insert fails.
If the row for the primary key value exists but the TimeSeries column is NULL, a new time series instance is created with the supplied values.

Run the TSL_SetNewTS function to control the creation of new time series by a loader program in the following ways:

- Enable the creation of new time series. Run the TSL_SetNewTS function with a time series definition in the NewTimeSeries parameter.
- Change the new time series definition that you previously set with the TSL_SetNewTS function. Run the TSL_SetNewTS function with a different time series definition in the NewTimeSeries parameter. You can change the definition for the current session or globally for all sessions.
- Disable the creation of new time series that you previously set with the TSL_SetNewTS function. Run the TSL_SetNewTS function without the NewTimeSeries parameter. You can disable new time series for the current session or globally for all sessions.

Returns

0 = The new time series creation rule is changed for the specified scope.
1 = An error occurred.

Example: Set a global new time series definition

The following statement enables new time series creation and sets a definition for new times series for all sessions that update the ts_data column in the iot_device_data table:

EXECUTE FUNCTION TSL_SetNewTS('iot_device_data|ts_data',
                      'origin(2014-01-01 00:00:00.00000), calendar(ts_1min),
                       container(iot_cn2), threshold(0), irregular', 
                      0);

Example: Set a new time series definition for a session

The following statement sets a definition for new times series for the current session that updates the ts_data column in the iot_device_data table:

EXECUTE FUNCTION TSL_SetNewTS('iot_device_data|ts_data',
                      'origin(%Y-%m-%d 00:00:00.00000), calendar(ts_1min),
                       container(iot_cn2), threshold(0), irregular', 
                      1);

The origin is set to the day on which the time series is created by inserting data.