PACK scalar function

The PACK function returns a binary string value that contains a data type array and a packed representation of each non-null expression argument.

The schema is SYSIBM.

CCSID 1208: Specifies that CCSID 1208 is used to encode character string values.
CCSID DEFAULT: Specifies that character strings are to be packed in their original encoding, as-is, without CCSID conversion.
expression: An expression that returns a value to be encoded in the result string. The expression must be a built-in data type that is not DECFLOAT, GRAPHIC, VARGRAPHIC, ROWID, a LOB, XML, or a character string defined as FOR BIT DATA.

The result of the PACK function is a binary string that is constructed from the following items:

A flag byte that is reserved for future use
A 2-byte integer value that indicates the number of arguments encoded in the resulting string
The data type array that contains an element with data type information for each of the encoded arguments
The encoded values for the expression arguments in the order as specified in the function invocation.

The resulting binary string is formatted as follows:

2-byte length	Flag byte	Number of items	Data type array	Encoded data values
VARBINARY length	VARBINARY data

The data type array includes an element for each expression argument in the same order as specified in the function invocation. Each array element contains a 2-byte SQLTYPE value that indicates the data type of the corresponding expression. When the SQLTYPE value is an odd number, the corresponding expression represents a null value and the value is not encoded in the resulting string. When the SQLTYPE value is an even number, the resulting string contains an encoded representation of the value depending on the data type. The following table describes the data types:

Table 1. Data types for the expression of the PACK function
Data type of expression	Description of the encoded representation of the value in the resulting string
SMALLINT, INTEGER, or BIGINT	The value of expression as a 16-bit signed binary integer, 32-bit signed binary integer, or 64-bit signed binary integer depending on the data type
decimal(p,s)¹	A sequence of 1-byte precision p, 1-byte scale s, and (p+2)/2 bytes of the signed packed-decimal number
real² or double³	The value of expression as a 64-bit IEEE floating-point format
CHAR or VARCHAR	A sequence of the 2-byte CCSID of the string encoding, followed by the 2-byte length of the string and then the argument data in the specified CCSID encoding
BINARY or VARBINARY	A sequence of: 2-byte length of the string, followed by the argument data
DATE	A 4-byte unsigned packed-decimal number representation of the date in the form of YYYYMMDD
TIME	A 3-byte unsigned packed-decimal number representation of the time in the form of HHMMSS
TIMESTAMP(p) WITHOUT TIME ZONE	A sequence of a 2-byte unsigned binary integer value of the precision p, followed by 7+ (p+1)/2 bytes of an unsigned packed-decimal number representation of the timestamp in the form of YYYYMMDDHHMMSSNN, where NN is zero to six bytes of the fractional seconds, depending on the precision p
TIMESTAMP(p) WITH TIME ZONE	A sequence of a 2-byte unsigned binary integer value of the precision p, followed by 7+ (p+1)/2 bytes of an unsigned packed-decimal number representation of the timestamp in the form of YYYYMMDDHHMMSSNN, where NN is zero to six bytes of the fractional seconds, depending on the precision p, and then followed by 2 bytes of an unsigned packed-decimal number representation of the time zone (with high order bit set for negative time zone value)
Note: The data types in lower case are defined as follows: decimal = DECIMAL(p,s) or NUMERIC(p,s) real = REAL or FLOAT(n) where n is the specification for a single precision floating point double = DOUBLE, DOUBLE PRECISION, FLOAT or FLOAT(n) where n is the specification for a double precision floating point The synonyms for the data types, in either long or short form, are considered the same as those that are listed.

All numeric data is represented in big endian format.

The result of the function is VARBINARY. The length attribute of the result is MIN (32704, the length of the header + length of data type array + SUM(maximum lengths of encoded expression values) ). The result cannot be null.

Example 1: The following statement shows that the VARCHAR, DATE, and DOUBLE values are packed into a binary string, and the string is then returned to the application:

  SELECT PACK(CCSID 1208, 'Alina', DATE'1977-08-01', DOUBLE(0.5))
    FROM SYSIBM.SYSDUMMYU;

The statement returns a VARBINARY string with the following content (The result is displayed in hexadecimal format and includes space separators for readability. The actual result is not in hexadecimal format and does not include any space separators):

  00 0003 01C4 0180 01E0 04B80005416C696E61 19770801 3FE0000000000000

The character string 'Alina' is in UTF-8 (CCSID 1208) format regardless of the string's original encoding because of the CCSID 1208 specification in the PACK invocation.

The resulting string is VARBINARY(30). The length attribute of 30 is determined by the following elements:

1 (flag byte)
+2 (size of number of items)
+2*3 (2-byte data type times number of items)
+2 (CCSID) + 2 (length) + 5 (VARCHAR(5) data length)
+4 (DATE data length)
+8 (DOUBLE length)

The actual length of the result is also 30.

Example 2: The following statement shows that when NULL values are packed into a binary string, they do not occupy any space in the encoded values portion of the result:

SELECT PACK(CCSID DEFAULT, '', CAST(NULL AS TIME),
 CAST('Bridget' AS VARCHAR(20) CCSID EBCDIC))
 FROM SYSIBM.SYSDUMMYU;

The statement returns a VARBINARY string with the following content. (The result is displayed in hexadecimal format and includes space separators for readability. The actual result is not in hexadecimal format and does not include any space separators.)

00 0003 01C4 0185 01C4 04B80000 00250007C2D9C9C4C7C5E3

The character string '' (empty string) is packed in its original CCSID 1208 format, and 'Bridget' is packed in its original CCSID 37 format, because of the CCSID DEFAULT specification in the PACK invocation.

The resulting string is VARBINARY(40). The length attribute of 40 is determined by the following elements:

1 (flag byte)
+2 (size of number of items)
+2*3 (2-byte data type times number of items)
+2 (CCSID) + 2 (length) + 0 (empty string data length)
+3 (TIME data length)
+2 (CCSID) + 2 (length) + 20 (VARCHAR(20) max length)

The actual length of the resulting string is 24, which is determined by the following elements

1 (flag byte)
+2 (size of number of items)
+2*3 (2-byte data type times number of items)
+2 (CCSID) + 2 (length) + 0 (empty string data length)
+0 (NULL)
+2 (CCSID) + 2 (length) + 7 (VARCHAR(20) actual length)