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 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 tables up to 2GB in size to the server by using the BLOB data type. 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 lists the data types supported by the MEMORY_TABLE function for the transfer of table data.
Table 1. MEMORY_TABLE table function supported data types
Data type Size Usage notes
SMALLINT 2 bytes of data
  • If nullable and the value is null, then first byte is -1.
  • If nullable but the value is not null, then the first byte is 0x01.
  • If the value is not nullable and the value is not null, an error is returned.
  • If the value is not nullable and the value is not null, then the first byte is 0x01.
  • 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 nullable and the value is null, then the first byte is -1.
  • If nullable, but the value is not null, then the first byte is 0x01.
  • If the value is not nullable and the value is not null, an error is returned.
  • If the value is not nullable and the value is not null, then the first byte is 0x01.
  • 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 nullable and the value is null, then the first byte is -1
  • If nullable, but the value is not null, then the first byte is 0x01
  • If the values is not nullable and the value is not null, an error is returned.
  • If the value is not nullable and the value is not null, then the first byte is 0x01
  • If the value is not null, write the 8 byte value as an 8 byte value: DataOutputStream.writeLong(BIGINT)
REAL 4 bytes of data N/A
DOUBLE 8 bytes of data
  • If nullable and the value is null, then the first byte is -1.
  • If nullable, but the value is not null, then the first byte is 0x01.
  • If the value is not nullable and the value is not null, an error is returned.
  • If the value is not nullable and the value is not null, then the first byte is 0x01.
  • If the value is not null, write the double as an 8 byte value: DataOutputStream.writeDouble(DOUBLE)
DECFLOAT(16) 8 bytes of data N/A
DECFLOAT(34) 16 bytes of data N/A
DECIMAL (in packed BCD format) 8 bytes of data
  • Other numeric data types in native endiness of client.
  • Size of data to transfer is precision/2+1 bytes.
VARCHAR, N/A
  • Data needs to be in UTF-16 format.
  • Data is converted to UTF-8 format at the server.
  • If nullable and the value is null, then the first and second bytes each are -1
  • If the values is not nullable and the value is not null, an error is returned
  • If the value is not null:
    • In the next two bytes, write the length * 2 of this byte string: DataOutputStream.writeShort(length * 2)
    • ext write the character string, with each byte written as a 2 byte value: DataOutputStream.writeChars(VARCHAR)
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.
VARBINARY N/A N/A
BOOLEAN N/A
  • If nullable and the value is null, then the first byte is -1.
  • If nullable but the value is not null, then the first byte is 0x01.
  • If the value is not nullable and the value is not null, an error is returned.
  • If the value is not nullable and the value is not null, then the first byte is 0x01.
  • 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 using 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 using DataOutputStream.writeShort(0x01).
DATE N/A
  • If nullable and the value is null, then the first byte is -1.
  • If nullable, but the value is not null, then the first byte is 0x01
  • If the values is not nullable and the value is not null, an error is returned.
  • If the value is not nullable and the value is not null, then the first byte is 0x01.
  • 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)
TIME N/A N/A
TIMESTAMP N/A
  • If nullable and the value is null, then the first byte is -1.
  • If nullable, but the value is not null, then the first byte is 0x01
  • If the values is not nullable and the value is not null, an error is returned.
  • If the value is not nullable and the value is not null, then the first byte is 0x01.
  • 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 using 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))