V5R3 changes to Query for iSeries
In V5R3, the Query for iSeries Use manual was not updated, but numerous functional updates were made to the Query for iSeries™ licensed program. Here is a summary of these updates:
- Support for BINARY, VARBINARY, BLOB, CLOB, DBCLOB, and ROWID data types.
- Support for larger numeric and decimal numbers, plus support for large numeric literals.
- The BINARY, VARBINARY, HEX, and LENGTH built-in functions were added.
They are described in this topic.
- The VARCHAR function now supports conversion from CLOB to VARCHAR.
- The VARGRAPHIC function now supports conversion from CLOB to DBCS graphic, CLOB to UCS2 graphic, DBCLOB to DBCS graphic, and DBCLOB to UCS2 graphic.
A binary constant is used for comparing a literal with a binary field (BINARY, VARBINARY, or BLOB). Binary constants are represented with an X followed by a sequence of characters that starts and ends with a string delimiter. The characters between the string delimiters must be an even number of hexadecimal digits. A hexadecimal digit is a digit or any of the letters A through F (uppercase and lowercase), as shown in the following example:
Field Test Value binarycol eq X'12AF'
BINARY built-in function for Query
The BINARY function returns a BINARY representation of a string of any type. The form is:
>>-BINARY--(--string-expression--+-----------+--)-------------->< '-, integer-'
The result of the function is a fixed-length binary string. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
The first argument must be a string-expression whose value must be a built-in character string, graphic string, binary string, or row ID data type. The second argument specifies the length attribute for the resulting binary string. The value must be between 1 and 32766. If the second argument is not specified, the following rules apply:
- If the string-expression is the empty string constant, the length attribute of the result is 1.
- Otherwise, the length attribute of the result is the same as the length attribute of the first argument, unless the argument is a graphic string. In this case, the length attribute of the result is twice the length attribute of the argument.
The actual length is the same as the length attribute of the result. If the length of the string-expression is less than the length of the result, the result is padded with hexadecimal zeros up to the length of the result. If the length of the string-expression is greater than the length attribute of the result, truncation is performed.
VARBINARY built-in function for Query
The VARBINARY function returns a VARBINARY representation of a string of any type. The form is:
>>-VARBINARY--(--string-expression--+-----------+--)----------->< '-, integer-'
The result of the function is VARBINARY. If the first argument can be null, the result can be null; if the first argument is null, the result is the null value.
The first argument is a string-expression whose value can be a character string, graphic string, binary string, or row ID. The second argument specifies the length attribute for the resulting binary string. The value must be between 1 and 32740 (32739 if nullable). If the second argument is not specified, the following rules apply:
- If the string-expression is the empty string constant, the length attribute of the result is 1.
- Otherwise, the length attribute of the result is the same as the length attribute of the first argument, unless the argument is a graphic string. In this case, the length attribute of the result is twice the length attribute of the argument.
The actual length is the same as the length attribute of the result. If the length of the string-expression is less than the length of the result, the result is padded with hexadecimal zeros up to the length of the result. If the length of the string-expression is greater than the length attribute of the result, truncation is performed.
HEX built-in function for Query
The HEX function returns a hexadecimal representation of a value. The form is:
>>-HEX--(--expression--)---------------------------------------><
The argument can be of any built-in data type. The result of the function is a character string. If the argument can be null, the result can be null; if the argument is null, the result is the null value.
The result is a string of hexadecimal digits. The first two digits represent the first byte of the argument, the next two digits represent the second byte of the argument, and so forth. If the argument is a datetime value, the result is the hexadecimal representation of the internal form of the argument.
The length attribute of the result is twice the storage length attribute of the argument. The length attribute of the result cannot be greater than 32766 for fixed-length results or greater than 32740 for varying-length results. If the argument is a varying-length string, the result is a varying-length string. Otherwise, the result is a fixed-length string.
The coded character set identifier (CCSID) of the string is the default single-byte character set (CCSID) on the current system.
LENGTH built-in function for Query
The LENGTH function returns the length of a value. The form is:
>>-LENGTH--(--expression--)------------------------------------><
The argument must be an expression that returns a value of any built-in data type. The result of the function is a large integer. If the argument can be null, the result can be null; if the argument is null, the result is the null value. The result is the length of the argument. The length of strings includes blanks. The length of a varying-length string is the actual length, not the length attribute. The length of a graphic string is the number of double-byte characters (the number of bytes divided by 2). The length of all other values is the number of bytes used to represent the value:
- 2 for small integer
- 4 for large integer
- 8 for big integer
- The integral part of (p/2)+1 for packed decimal numbers with precision p
- p for zoned decimal numbers with precision p
- 4 for single-precision float
- 8 for double-precision float
- The length of the string for strings
- 3 for time
- 4 for date
- 10 for timestamp
- 26 for row ID