DB2 for i 7.1 TR10 and i 7.2 TR2 Features, Part 1
October 6, 2015 Michael Sansoterra
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:
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:
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:
The result is:
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:
Consider the following table definition:
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);
Now imagine after the table has been in production for a while, some application modifications are done that require several changes to the table. The changes can be done in a single CREATE OR REPLACE TABLE statement without having to save the rows in a different temporary table, change the table definition, and then restore the rows:
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
The above table changes could have been done one at a time with several ALTER TABLE statements, but over time it becomes difficult for a developer to rely on SQL source code where the source CREATE TABLE statement differs vastly from what is in production. Using CREATE OR REPLACE TABLE allows the source to stay in sync with the production table.
This functionality has been available for DDS files when using the Change Physical File (CHGPF) command with a DDS source file. It is great to have an SQL equivalent for this feature.
Of course, if DB2 determines there may be a data truncation issue when replacing a table (for example, changing a column’s data type from INT to SMALLINT) it will throw the following warning message in a green screen environment, giving the user an option to cancel the operation or ignore the warning:
CPA32B2 - Change of file ORDER_DETAIL may cause data to be lost. (C I)
In the green screen environment, it is easy enough to respond to the message with an ‘I’ to let the processing continue. However, when using a GUI tool such as RunSQL Scripts to execute SQL statements, by default the cancel option is automatically taken which will cause the CREATE OR REPLACE TABLE statement to end with this error:
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.
As demonstrated in a tip I shared earlier this year, a dynamic compound statement can be used to add a system reply list entry for this warning so that the message is automatically replied to with an I (ignore) instead of a C (cancel).
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;
As with other CREATE OR REPLACE statements, the beauty and ease of using it with a table definition is that existing security assignments, dependent indexes and views, RCAC masks, etc. will be preserved. Letting DB2 do this work minimizes the risk of a developer or administrator forgetting to recreate an unintentionally dropped object during this type of operation.