TSL_Put function

Syntax

TSL_Put(
           handle      lvarchar,
           elementlist lvarchar)
returns integer

TSL_Put(
           handle      lvarchar,
           CLOB_file CLOB)

TSL_Put(
           handle      lvarchar,
           file_path   lvarchar)

returns integer

handle

The table and column name combination that is returned by the TSL_Attach or the TSL_Init function.

elementlist

A buffer that contains the time series data to load. That maximum size of a buffer is 32 KB.

CLOB_file

A text file that contains an element list of time series data to load into an intermediate CLOB data type. Specify the file path and name and whether the file is on the server or client computer in the FileToClob function, for example: FileToClob('/data/tsdata.unl','server').

file_path

A text file that contains an element list of time series data to load. Include the keyword FILE: followed by the fully qualified path and file name. For example:FILE:/mydata/loadfile.unl.

Element list format

The contents of the file or LVARCHAR string that you load must be an element list that has the following format:

primary_key|timestamp|values|

primary_key

One or more column values that comprise the primary key, which are separated by pipe characters. If this field is empty, the value from the previous line is used.

timestamp

The format of the timestamp is specified by the timestamp_format argument of the TSL_Init function. The default format of the timestamp is: YYYY-mm-dd HH:MM:SS (year-month-day hour:minute:seconds).

values

Values for the columns in the TimeSeries subtype. Separate multiple values with pipe characters and end the list of values with a pipe character.

Values can be JSON documents or in BSON format.

Each element must be on a separate line. Each line must end with a newline character (\n). If necessary, enable newline characters in quoted strings by setting the ALLOW_NEWLINE configuration parameter or running the IFX_ALLOW_NEWLINE() procedure.

The following element list contains a value for every primary key and date field, even if the values are the same as the previous element:

MX230001|2011-01-01|00:00:00|23.4|56.7|
MX230001|2011-01-01|01:00:00|34.7|57.8|
MX230001|2011-01-01|02:00:00|12.8|58.3|
MX672382|2011-01-01|00:00:00|3.2|0.0|
MX672382|2011-01-01|01:00:00|4.7|0.0|

The following element list is equivalent to the previous list, but requires less room in the input buffer because duplicate primary key and date values are omitted:

MX230001|2011-01-01|00:00:00|23.4|56.7|
||01:00:00|34.7|57.8|
||02:00:00|12.8|58.8|
MX672382|2011-01-01|00:00:00|3.2|0.0|
||01:00:00|4.7|0.0|

The following element list has a primary key that has multiple columns. The values in the primary key that repeat are omitted:

MX23001|AQ74D|2011-01-01|00:00:00|23.11|98.43|
||AQ74E|||22.71|97.65|
||||00:01:00|22.69|94.56|
MX23002|AV90A|2011-01-01|00:00:00|23.12|91.43|

The following element list has values that are JSON documents:

met0000000|2014-01-01 00:00:00.00000|808464432|{"m1":-13.00,"m2":100.00}|
met0000000|2014-01-01 00:15:00.00000|825241648|{"m1":-4.00,"m2":100.00}|
met0000000|2014-01-01 00:30:00.00000|842018864|{"m1":-18.00,"m2":100.00}|

Usage

Use the TSL_Put function to load time series data as part of a loader program. You must run the TSL_Put function in the context of a loader session that was initialized by the TSL_Init function. You can run the TSL_Put function multiple times in the same session. The data is stored in the database server until you run the TSL_Flush function to write the data to disk.

The fastest way to load data with the TSL_Put function is directly from a file because you avoid extra processing on the client and the server. If you include the data as a string, the client processes the string into an LVARCHAR data type. If you included the data file as a CLOB, the server loads the contents of the file into a CLOB data type and then reads the data from the CLOB data type.

Returns

• An integer that indicates the number of records that were inserted.
• An exception if no records were inserted.