DB2 for i 7.2 Features And Fun: Part 2
July 23, 2014 Michael Sansoterra
In the first tip of this series, I covered three new features introduced in DB2 for i 7.2: ALTER TRIGGER, TRUNCATE TABLE, and the ability to vary the fractional precision of a TIMESTAMP column. This tip continues the adventure by exploring a few more new features in IBM i 7.2 (in no particular order).
KEEP IN MEMORY Clause For Tables And Index
When creating a new index or table, a new KEEP IN MEMORY clause is available to tell DB2 to keep the data in main storage when possible. (A table or index cached in this manner should, of course, result in a performance increase.) This clause is also available with ALTER TABLE and only requires a value of YES or NO.
-- Change existing table to be in main storage ALTER TABLE ADVWORKS.TRANSACTIONHISTORY ALTER KEEP IN MEMORY YES; -- Create index for ORDER TRANSACTIONS CREATE INDEX ADVWORKS.IDX_TRANSACTIONHISTORY__ORDERS ON ADVWORKS.TRANSACTIONHISTORY (PRODUCTID,MODIFIEDDATE DESC) WHERE TRANSACTIONTYPE='ORDER' KEEP IN MEMORY YES;
For production tables and indexes with this option, a database or system administrator should monitor to make sure available memory is sufficient for the data growth.
This feature can be helpful with “special” workloads, such as month end, when other users aren’t on the system and available memory is generally higher than usual. This option is allowed to be specified on temporary tables and could benefit a scenario such as month end processing, if the temp table is used frequently throughout the month end process:
DECLARE GLOBAL TEMPORARY TABLE MONTH_END_SALES (ORDER_ID INT NOT NULL, PRODUCT_ID INT NOT NULL, ...more columns... ORDER_STAMP TIMESTAMP(0), SHIP_STAMP TIMESTAMP(0), QTY DEC(9,3), EXT_AMOUNT DEC(19,4)) WITH REPLACE KEEP IN MEMORY YES;
In my testing, I created a table and loaded it with more data than available RAM and there were no error messages so you shouldn’t have to worry about an application failure. I imagine in a production environment, though, this “overload” situation will cause performance to drag.
If you’re using commitment control in your applications, you’ll like this one. An autonomous procedure is one that will COMMIT data changes independently of any transaction processing the caller is performing.
Consider the CREATE_ORDER procedure below that inserts data, calls a procedure that does email confirmation and audit logging functions, does some more work, and then commits or rolls back the changes:
-- Procedure CREATE_ORDER CREATE OR REPLACE PROCEDURE ADVWORKS.CREATE_ORDER (@ORDER_ID INT) BEGIN SET TRANSACTION ISOLATION LEVEL READ COMMITTED; INSERT INTO ADVWORKS.ORDER_HEADER VALUES (@ORDER_ID,1,CURRENT_TIMESTAMP); -- Send an e-mail, create a log entry in the db CALL ADVWORKS.SEND_EMAIL_CONF (@ORDER_ID,'firstname.lastname@example.org'); -- More work is done here -- Test if something bad happened IF @ERROR_ID>0 THEN ROLLBACK; ELSE COMMIT; END IF; END;
In prior releases, the SEND_EMAIL_CONF procedure’s work would be rolled back if the caller rolled back its work. But now, the AUTONOMOUS keyword can be used on a procedure definition to indicate that all changes should be committed independently of the caller:
-- Procedure SEND_EMAIL_CONF CREATE OR REPLACE PROCEDURE ADVWORKS.SEND_EMAIL_CONF (@ORDER_ID INT,@EMAIL VARCHAR(64)) AUTONOMOUS BEGIN INSERT INTO ADVWORKS.ORDER_INFO VALUES (@ORDER_ID,@EMAIL,CURRENT_TIMESTAMP); -- Send an e-mail here END;
Since procedure SEND_EMAIL_CONF is defined as autonomous, its committed data can stay “as is” in DB2, even if calling procedure CREATE_ORDER opts to ROLLBACK thereafter. In the above example, the ORDER_INFO table will still have a newly inserted row if the transaction started in procedure CREATE_ORDER is rolled back.
Sometimes it just happens within a transaction that you want some remnants of data left in the database after a rollback. Depending on the order of operations and complexity of the app, it’s often a pain in the neck to engineer code to work this way. The AUTONOMOUS keyword allows developers to get around this conundrum without creating spaghetti code.
If an autonomous procedure ends with an error, its changes will not be committed. Also, autonomous procedures may not return dynamic result sets.
CHECK CONSTRAINT VIOLATION Clause
Check constraints play a critical role in maintaining database integrity by protecting columns in the table from being populated with invalid values. Consider this simple PO_HEADER table definition:
CREATE TABLE data.PO_HEADER ( PO_ID INT NOT NULL, PO VARCHAR(12) NOT NULL, VENDOR_ID INT NOT NULL, PO_DATE DATE NOT NULL DEFAULT CURRENT_DATE, EMAIL VARCHAR(64) NOT NULL DEFAULT 'email@example.com');
Here is an example of a CHECK CONSTRAINT that attempts to do minimal validation of an email address:
ALTER TABLE data.PO_HEADER ADD CONSTRAINT data.ck_PO__EMAIL_VALIDATION CHECK (EMAIL LIKE '%@%.%')
If an operation attempts to insert or update data in column EMAIL that does not match the constraint’s predicate, the check constraint will cause the data modification to fail.
However, there may be cases when you don’t want bad data in your database, but you don’t want an insert/update operation to fail either if a certain check constraint is violated. For example, if you have an external system feeding new purchase orders into your application, you may want to allow a PO to be created on your system even if the email address is invalid. (Sometimes fixing a problem is easier once it’s in your db instead of trying to get an external system to fix their data and send it again.)
The new VIOLATION clause can be used to allow an INSERT or UPDATE operation to succeed and tell DB2 what to use in place of the bad value:
ALTER TABLE data.PO_HEADER ADD CONSTRAINT data.ck_PO__EMAIL_VALIDATION CHECK (EMAIL LIKE '%@%.%') ON INSERT VIOLATION SET EMAIL=DEFAULT ON UPDATE VIOLATION PRESERVE EMAIL
In this case, if an INSERT violates the check constraint rule, the EMAIL column’s default value (firstname.lastname@example.org) will be inserted instead of the supplied value. Currently, the DEFAULT value is the only allowed value. The column name supplied on the violation clause must be included in the CHECK clause’s rule definition.
Likewise, for an update, “ON UPDATE VIOLATION PRESERVE EMAIL” instructs DB2 to leave the existing value in the column instead of failing the update operation.