DB2 for i 7.2 Features And Fun: Part 2

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.

Examples:

-- 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.

Autonomous Procedures

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,'receipient@email.com');

    -- 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 'purchasing_service@mydomain.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 (purchasing_service@mydomain.com) 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.

Michael Sansoterra is a DBA for Broadway Systems in Grand Rapids, Michigan. Send your questions or comments for Mike via the IT Jungle Contact page.

RELATED STORY

DB2 for i 7.2 Features and Fun, Part 1

                     Post this story to del.icio.us
               Post this story to Digg
    Post this story to Slashdot