MEMORY_TABLE table function

The MEMORY_TABLE table function provides fast batch ingestion of streaming workloads into Db2. Table data is transferred to the Db2 server in binary large object (BLOB) format, and rows are created by the table function from the transferred data.

Syntax

Read syntax diagramSkip visual syntax diagramMEMORY_TABLE(in_mem_table)

The schema is SYSPROC.

The following INSERT statement is used to call the table function and create the rows in a target database table:
INSERT into memtest() SELECT from table(SYSPROC.MEMORY_TABLE( CAST (? AS BLOB(2G)) )) AS T()

Table function parameters

in_mem_table
  • BLOB(2G) containing the encoded table.
  • The result of the function depends on the output specification that is used during the call to the table function.

Supported source data types

Table data that is transferred does not use a structured format like XML or JSON to avoid transfer overhead and parsing overhead on the server. The table function transfers a set of rows to the server that, when converted, occupy up to 2GB in size The rows are transferred by using the BLOB data type.

The BLOB needs to begin with 0xFFFE in the first 2 bytes in the BLOB header. As rows are transferred from tables into the BLOB, each column in a row is transferred by byte position in the source, following the conversion rules for each supported data type. Each row is then transferred in sequence into the BLOB. The rules for converting each data type are listed in Table 1

The data is always transferred by using the client endianess, which must match the server endianness.

Two database code pages are supported:
  • 819
  • 1208(UTF-8)
Table 1. MEMORY_TABLE table function supported data types
Data type Size Usage notes
SMALLINT 2 bytes of data
  • If the column is nullable and the value is null, the first byte is -1.
  • If the column is nullable and the value is not null, the first byte is 0x01.
  • If the column is not nullable and the value is null, an error is returned.
  • If the column is not nullable and the value is set in the BLOB as null, an error is returned.
  • If the value is not null, write the 2 byte integer value as a 2 byte value DataOutputStream.writeShort(SMALLINT)
INTEGER 4 bytes of data
  • If the column is nullable and the value is null, the first byte is -1.
  • If the column is nullable and the value is not null, the first byte is 0x01.
  • If the column is not nullable and the value is null, an error is returned.
  • If the column is not nullable and the value is set in the BLOB as null, an error is returned.
  • If the value is not null, write the 4 byte value as a 4 byte value DataOutputStream.writeInt(INTEGER)
BIGINT 8 bytes of data
  • If the column is nullable and the value is null, the first byte is -1.
  • If the column is nullable and the value is not null, the first byte is 0x01.
  • If the column is not nullable and the value is null, an error is returned.
  • If the column is not nullable and the value is set in the BLOB as null, an error is returned.
  • If the value is not null, write the 8 byte value as an 8 byte value DataOutputStream.writeLong(BIGINT).
CHAR(N) N/A
  • If the column is nullable and the value is null, the first byte is -1.
  • If the column is nullable and the value is not null, the first byte is 0x01.
  • If the column is not nullable and the value is null, an error is returned.
  • If the column is not nullable and the value is not null, the first byte is 0x01.
  • If the value is not null, write the character string with each byte written out as a 2 byte value DataOutputStream.writeChars(string value).
REAL 4 bytes of data
  • If the column is nullable and the value is null, the first byte is -1.
  • If the column is nullable and the value is not null, the first byte is 0x01.
  • If the column is not nullable and the value is null, an error is returned.
  • If the column is not nullable and the value is set in the BLOB as null, an error is returned.
  • If the value is not null, write out the float as a 4 byte value DataOutputStream.writeFloat(float value).
DOUBLE 8 bytes of data
  • If the column is nullable and the value is null, the first byte is -1.
  • If the column is nullable and the value is not null, the first byte is 0x01.
  • If the column is not nullable and the value is null, an error is returned.
  • If the column is not nullable and the value is set in the BLOB as null, an error is returned.
  • If the value is not null, write the double as an 8 byte value DataOutputStream.writeDouble(DOUBLE).
DECFLOAT(16) 8 bytes of data
  • If the column is nullable and the value is null, write bytes as -1 in the first byte. No other bytes need to be written for this value, as the one byte is sufficient for the null indicator.
  • If the column is nullable and the value is null, the first byte is -1.
  • If the column is not nullable and the value is null, an error is returned.
  • If the column is not nullable and the value is set in the BLOB as null, an error is returned.
  • If the value is not null, write the DECFLOAT(16) out as a 4 byte value DataOutputStream.writeFloat(float value).
DECFLOAT(34) 16 bytes of data
  • If the column is nullable and the value is null, write bytes as -1 in the first byte. No other bytes need to be written for this value, as the one byte is sufficient for the null indicator.
  • If the column is nullable and the value is null, the first byte is -1.
  • If the column is not nullable and the value is null, an error is returned.
  • If the column is not nullable and the value is set in the BLOB as null, an error is returned.
  • If the value is not null, write the DECFLOAT(34) as an 8 byte value DataOutputStream.writeDouble(DOUBLE).
DECIMAL (in packed BCD format) 8 bytes of data
  • Other numeric data types in the native endiness of the client.
  • Size of data to transfer is precision/2+1 bytes.
  • If the column is nullable and the value is null, write bytes as -1 in the first byte. No other bytes need to be written for this value, as the one byte is sufficient for the null indicator.
  • If the column is nullable and the value is not null, the first byte is 0x01.
  • If the column is not nullable and the value is set in the BLOB as null, an error is returned.
  • If the value is not null, write the character string with each byte written out as a 2 byte value, such as using DataOutputStream.writeChars(string value).
VARCHAR, N/A
  • Data needs to be in UTF-16 format.
  • Data is converted to UTF-8 format at the server.
  • If the column is nullable and the value is null, then the first two bytes are -1. No other bytes need to be written for the value.
  • If the column is not nullable and the value is not null, an error is returned.
  • If the value is not null, write the first two bytes as the length * 2 of this byte string DataOutputStream.writeShort(length * 2)
LONG VARCHAR N/A For VARCHAR FOR BIT DATA or VARCHAR data types on non-Unicode databases, the data is not converted.
VARGRAPHIC N/A Data needs to be in UTF-16 format.
BOOLEAN N/A
  • If the column is nullable and the value is null, the first byte is -1.
  • If the column is nullable and the value is not null, the first byte is 0x01.
  • If the column is not nullable and the value is set in the BLOB as null, an error is returned.
  • If the value is not null and the value is false, write the next two bytes with 0x00. For example, write the value as a 2 byte value that uses DataOutputStream.writeShort(0x00).
  • If the value is not null and the value is true, then write the next two bytes with 0x01. For example, write the value as a 2 byte value that uses DataOutputStream.writeShort(0x01).
DATE N/A
  • If the column is nullable and the value is null, the first byte is -1. No other bytes need to be written for the value.
  • If the column is nullable and the value is not null, the first byte is 0x01.
  • If the column is not nullable and the value is set in the BLOB as null, an error is returned.
  • If the value is not null, the value must be >= 0
  • If the value is not null, run the following code for the java.sql.Date value: 
    // get the year, month, and day values and convert each to strings 
year = (Date value.getYear + 1900).toString 
// if the month or day values are single-digit (< 10), add a leading 0 
month = addLeadingZero(Date value.getMonth + 1).toString) day = addLeadingZero(Date value.getDate.toString) 
// Convert the values to a date String and then convert to a byte array of BCD using the function below. 
dateString = year + month + day dateBcd = convertDateTimeToBCD(dateString) 
// send the bcd over as a series of bytes Write using DataOutputStream.write(dateBcd, 0, dateBcd.length)
TIMESTAMP N/A
  • If the column is nullable and the value is null, the first byte is -1. No other bytes need to be written for the value.
  • If the column is nullable and the value is not null, the first byte is 0x01.
  • If the column is not nullable and the value is set in the BLOB as null, an error is returned.
  • If the value is not null, the value must be >= 0
  • If the value is not null, convert the timestamp elements into a string, convert the string to BCR, and write the output as a byte array: 
    timestamp = java.sql.Timestamp value 
// Get the string year, month, and day values 
year = (timestamp.getYear + 1900).toString 
// If the string month/day/hour/minute/second values which are single-digit (< 10), they need a leading 0. 
month = addLeadingZero((timestamp.getMonth + 1).toString) day = addLeadingZero(timestamp.getDate.toString) 
hours = addLeadingZero(timestamp.getHours.toString) minutes = addLeadingZero(timestamp.getMinutes.toString) 
seconds = addLeadingZero(timestamp.getSeconds.toString) nano = timestamp.getNanos.toString 
// verify # of digits in nano, if < 9, this means that 0s were truncated off the beginning, add them back 
if (nano.length < 9) { val difference = 9 - nano.length for (diff <- 0 until difference) { nano = "0" + nano } } 
// Remove last 3 digits of fractional seconds to match timestamp with default scale. 
nano = nano dropRight(3) 
// Convert the values to a date/time String and then convert to a byte array of BCD using the function below. 
dateString = year + month + day + hours + minutes + seconds + nano bcd = convertDateTimeToBCD(dateString) 
// Send the bcd over as a series of bytes Write using DataOutputStream.write(bcd, 0, bcd.length)
Note: The following data types are currently not supported for high speed ingest into Db2® by using the MEMORY_TABLE function:
  • CHAR and GRAPHIC
  • LONG VARCHAR, LONG VARGRAPIC
  • CLOB, BLOB, and DBCLOB

Authorization

You need to grant EXECUTE privilege on the MEMORY_TABLE table function.

Examples

The following example shows a table INSERT operation that uses the MEMORY_TABLE function to insert data into the MEMSET table with the schema (PKID INT, STRINGCOL VARCHAR(255)): 
INSERT into memtest(pkid, stringcol) SELECT pkid, stringcol from table(SYSPROC.MEMORY_TABLE( CAST (? AS BLOB(2G)) )) AS T(PKID INT, STRINGCOL VARCHAR(255))