DB2 for i 7.1 TR10 and i 7.2 TR2 Features, Part 1

This is just another great DB2 feature tip, calling attention to some useful features available in the recent releases of DB2 for i. To determine whether you can use these recent features, check with your IBM i administrator to make sure your database group PTF levels are up to date. For IBM i 7.1 the database group PTF level should be at least 34, and for 7.2 the database group PTF level should be at least 5.

VARCHAR_FORMAT_BINARY and VARBINARY_FORMAT

Two new functions were added to the DB2 for i built-in function arsenal that will assist developers with the annoying task of changing a character representation of a universally unique identifier (UUID; also called a globally unique identifier or GUID in the Microsoft world) value to binary and vice-versa.

Say your application is handed a UUID in binary format from another system. The value is BX’6F6562B855AF4347B25747E5EDC630D5′ and you want to make it legible and store it in a local table as VARCHAR. Function VARCHAR_FORMAT_BINARY performs this transformation:

VALUES VARCHAR_FORMAT_BINARY(
BX'6F6562B855AF4347B25747E5EDC630D5','XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX');

The function returns the UUID formatted as VARCHAR with the UUID broken up into five groups separated by hyphens (a common way to display UUIDs) as shown here:

6F6562B8-55AF-4347-B257-47E5EDC630D5

VARCHAR_FORMAT_BINARY accepts two parameters: a 16-byte binary (or character FOR BIT DATA) value, and a format code. There is only one format code, and that is the UUID format shown above. The Xs in the format can alternatively be supplied as lower case. It would be nice if a user-defined format were allowed here, but for now UUIDs are it.

To reverse the process and take the character representation of a UUID and convert it to binary, use the VARBINARY_FORMAT function:

VALUES VARBINARY_FORMAT(
'6F6562B8-55AF-4347-B257-47E5EDC630D5',
'XXXXXXXX-XXXX-XXXX-XXXX-XXXXXXXXXXXX')

For this example, the function will return a VARBINARY(16) UUID value:

BX'6F6562B855AF4347B25747E5EDC630D5'

This function accepts a valid hex string (with hyphens) and a format code. It only allows one basic format code: the popular UUID format (specified as all upper or all lowercase).

The VARBINARY_FORMAT function offers one more useful purpose in that it can convert any hex string to its binary equivalent. To do this, simply omit the second (format) parameter. For example, the hex string ‘F1F2F3’ (represents ‘123’ in EBCDIC) will be converted to binary as follows:

VALUES VARBINARY_FORMAT('F1F2F3')

The result is:

BX'F1F2F3'.

While this is a great way to transform hex string representation to binary, what I would really like is for IBM to allow the direct cast of various data types (DECIMAL, NUMERIC, INT, CHARACTER, etc.) to binary and vice versa. Currently, external user-defined functions need to be written to accomplish this task.

As for name alternatives, VARCHAR_FORMAT_BIT is another name for VARCHAR_FORMAT_BINARY. VARCHAR_BIT_FORMAT is similar to VARBINARY_FORMAT except that it returns a varying-length character string FOR BIT DATA instead of returning a VARBINARY data type.

CREATE OR REPLACE TABLE

Since IBM i 7.1, DB2 for i developers have enjoyed the convenience of many CREATE OR REPLACE statements. Whether using it for stored procedures, views, or functions, CREATE OR REPLACE allows the definition of an object to be changed without having to recreate all of the security, labels, etc. that go along with dropping and recreating an object. At long last, CREATE OR REPLACE table is now available as well.

To use CREATE OR REPLACE TABLE, make sure you have the appropriate PTF installed on your system:

CREATE TABLE ORDER_DETAIL (
ORDER_ID   INT       NOT NULL,
LINE_ID    INT       NOT NULL,
PRODUCT_ID INT       NOT NULL,
QTY        INT       NOT NULL,
UOM        CHAR(2)   NOT NULL,
UNIT_PRICE FLOAT     NOT NULL DEFAULT 0);

CREATE OR REPLACE TABLE ORDER_DETAIL (
ORDER_ID   INT       NOT NULL,
LINE_ID    SMALLINT  NOT NULL,
PRODUCT_ID INT       NOT NULL,
QTY        DEC(9,2)  NOT NULL,
UOM        CHAR(2)   NOT NULL,
UNIT_PRICE DEC(19,4)  NOT NULL DEFAULT 0,
CONSTRAINT pk_ORDER_DETAIL PRIMARY KEY (ORDER_ID,LINE_ID),
CONSTRAINT udx_ORDER_DETAIL__PRODUCT_ID UNIQUE (ORDER_ID,PRODUCT_ID),
CONSTRAINT ch_ORDER_DETAIL__UNIT_PRICE  CHECK (UNIT_PRICE>=0)
ON REPLACE PRESERVE ALL ROWS

CPA32B2 - Change of file ORDER_DETAIL may cause data to be lost. (C I)

SQL State: 57014
Vendor Code: -952
Message: [SQL0952] Processing of the SQL statement ended.  Reason code 10.
Cause . . . . . :   The SQL operation was ended before normal completion.
The reason code is 10.
Reason codes and their meanings are:.. 
10 -- A cancel reply to an inquiry message was received.

BEGIN
IF NOT EXISTS (
    SELECT * 
      FROM QSYS2.REPLY_LIST_INFO 
     WHERE MESSAGE_ID='CPA32B2') THEN
    CALL QCMDEXC('ADDRPYLE SEQNBR(5000) MSGID(CPA32B2) RPY(I)');
END IF;
/* Change the job to accept the system reply */
CALL QCMDEXC('CHGJOB INQMSGRPY(*SYSRPYL)');

END;