TSL_Commit function

The TSL_Commit function flushes data for all containers to disk in multiple transactions.

Syntax

TSL_Commit(
           handle          lvarchar,
           writeflag       integer DEFAULT 1,
           commit_interval integer DEFAULT NULL)
returns integer

handle: The table and column name combination that is returned by the TSL_Attach or the TSL_Init function.
writeflag (Optional): An integer that is the sum of flag values that represent whether duplicate elements are allowed for irregular time series and whether logging is reduced. You must supply a value for the duplicate element behavior (1 or 5). Possible values are: 1, 5, 257 (1 + 256), 261 (5 + 256).; 1 = Default. Duplicate elements are allowed. Cannot be combined with the value 5.; 5 = Duplicate elements replace existing elements. Cannot be combined with the value 1.; 256 = Logging is reduced. See the description of the TSOPEN_REDUCED_LOG flag in The flags argument values.

commit_interval (Optional): A positive integer that represents the maximum number of elements to insert per transaction. The default value if the argument is omitted or set to NULL is 10000.

Usage

Use the TSL_Commit function to write time series data to disk as part of a loader program. You must run the TSL_Commit function in the context of a loader session that was initialized by the TSL_Init function. You run the TSL_Commit function to save data that is loaded by the TSL_Put, TSL_PutSQL, or TSL_PutRow function. The TSL_Commit function is useful when the amount of data that you are loading is too large for your logical log to flush as one transaction.

The TSL_Commit function commits transactions after flushing the specified number of elements, or when all elements for a container are flushed. For example, suppose that you set the commit_interval argument to 5000. If you load 10 000 elements into a container, the TSL_Commit function commits two transactions. If you insert 10 000 elements in equal amounts into five containers, the TSL_Commit function commits five transactions of 2000 elements. If you run the TSL_Commit function within an explicit transaction, then the TSL_Commit function does not commit transactions.

If you specify that duplicate elements are allowed for irregular time series with the writeflag argument value of 1, the TSL_Commit function inserts data in the same way as the PutElem function. You can specify that duplicate elements replace existing elements that have the same timestamps with the writeflag argument value of 5 so that the TSL_Commit function inserts data in the same way as the PutElemNoDups function.

If you specify reduced logging with the writeflag argument, you must run TSL_Commit function in a transaction that can include only other functions that use reduced logging with the TSOPEN_REDUCED_LOG flag. The elements that are saved are not visible by dirty reads until after the transaction commits.

The TSL_FlushInfo function returns eight categories of information about the last flush operation that saved data to disk.

Returns

An integer that indicates the status of the function:

A positive integer = The number of elements that were inserted.
-1 = Process was interrupted or encountered a severe error.

Example: Run the TSL_Commit function

The following statement saves the data to disk after every 5000 elements are inserted for the table ts_data and the TimeSeries column raw_reads:

EXECUTE FUNCTION TSL_Commit('ts_data|raw_reads', 1, 5000);

Example: Run the TSL_Commit function with reduced logging

The following statement saves the data to disk with reduced logging after every 20 000 elements are inserted:

EXECUTE FUNCTION TSL_Commit('ts_data|raw_reads', 257, 20000);

Example: Run the TSL_Commit function with reduced logging and no duplicate elements

The following statement saves the data to disk with reduced logging and replaces existing elements that have the same timestamps as new elements:

EXECUTE FUNCTION TSL_Commit('ts_data|raw_reads', 261, 200000);