DB2 for i 7.2 Features and Fun: Part 3

In the second tip of this series, I explored three new features introduced in DB2 for i 7.2: KEEP IN MEMORY clause; AUTONOMOUS PROCEDURE; and the CHECK CONSTRAINT VIOLATION clause. This tip aims to keep the momentum going by exploring a handful of remaining miscellaneous features, in no particular order.

TABLE_NAME And TABLE_SCHEMA Scalar Functions

These two new functions exist in the SYSIBM schema and are used to lookup base information about an alias. Say an alias is defined on a DB2 remote system table on a system named WASHINGTON:

CREATE ALIAS DATALIB.WASHINGTON_TRANSACTIONS FOR
WASHINGTON.ADVWORKS.TRANSACTIONHISTORY;

The following VALUES statement will use these new functions to give back name and schema information about the table or view that the alias was built on:

VALUES (
SYSIBM.TABLE_NAME('WASHINGTON_TRANSACTIONS','DATALIB'),
SYSIBM.TABLE_SCHEMA('WASHINGTON_TRANSACTIONS','DATALIB')
);

The query returns the values: TRANSACTIONHISTORY, ADVWORKS. In other words, the query shows the table/view and schema that the alias is based on.

It seems like IBM should’ve also created a TABLE_CATALOG function to pull out the catalog (a.k.a. RDB) name. I suppose some of us are never happy! Incidentally, the base information underlying an alias (including the catalog/RDB name) can also be retrieved from the QSYS2.SYSTABLES catalog view.

SQL Expressions With EXECUTE IMMEDIATE And PREPARE

This is another one of those small enhancements that will come in handy. Both of these SQL statements (EXECUTE IMMEDIATE and PREPARE) can now accept a constant or SQL expression in addition to a host variable. In the past, a constant was not allowed.

Examples (embedded in RPG):

EXEC SQL
    EXECUTE IMMEDIATE 
    'UPDATE COMPANY
        SET CUR_AR_BALANCE='||VARCHAR(:AR_BALANCE,30)||'
      WHERE COMPANY_ID='||VARCHAR(:CUSTOMER_ID,10);

EXEC SQL
    EXECUTE IMMEDIATE 'DROP TABLE ' || :MYTABLE;

Remember, EXECUTE IMMEDIATE can only be embedded in an HLL program, SQL trigger, or SQL routine. It can’t be issued dynamically.

Using Group Profiles With GRANT And REVOKE

The GRANT and REVOKE statements now have a small syntax change that can be used to clarify what type of profile (user or group) is being referenced:

-- must be a user profile
GRANT ALL ON DEV.QRPGLESRC TO USER JOE

-- must be a group profile
GRANT ALL ON DEV.QRPGLESRC TO GROUP DEV_GROUP

If you specify the user or group keyword and the specified profile doesn’t match the type, DB2 will spit back error SQL0159 &1 in *N not correct type. Group profile authority assignments with GRANT AND REVOKE were allowed in prior versions of DB2 for i, but now a developer or DBA has the option to show what type of profile is intended and have the system validate that the intended profile type is correct.

TRANSFER OWNERSHIP Statement

This is another one of those items I’m pleased to have in my SQL tool box. This statement will change the ownership of an SQL table to the specified user profile:

TRANSFER OWNERSHIP OF TABLE ADVWORKS.ITEMS 
TO USER QPGMR PRESERVE PRIVILEGES

In this case, the existing owner will continue to retain the current privileges to the object. But in this next example, the existing owner loses its privileges:

TRANSFER OWNERSHIP OF INDEX ADVWORKS.IDX_SALES_ORDER 
TO USER QPGMR REVOKE PRIVILEGES

Exclusive access to the object is required, otherwise the statement will fail (at Group PTF Level 1) with a SQL0901 SQL system error. Checking the job log reveals the true error: CPF3202 File &1 in library &2 in use.

Having TRANSFER OWNERSHIP as a SQL statement will alleviate the need to use IBM i OS command Change Object Owner (CHGOBJOWN) when doing application maintenance. This is good because admins of other DB2/RDBMS platforms will have an easier time adjusting to the IBM i.

At the moment, this statement is only valid for tables, views and indexes. Changing the owner of other objects such as stored procedures and data areas will still need to be done with the IBM i Change Object Owner (CHGOBJOWN) command.

VERIFY_GROUP_FOR_USER Function

This function will allow your application to test if the current user belongs to a certain group profile. The first parameter is the current user (as specified by SESSION_USER, USER or CURRENT_USER) and the second parameter is the group profile in question. The result is a 0 or 1.

-- Returns 1 if the current user belongs to group profile QGPMR
VALUES (VERIFY_GROUP_FOR_USER(SESSION_USER,'QPGMR'));

It would’ve been nice if this function allowed for any user profile to be tested for group membership instead of just the current profile. This function is intended for use with the new row and column access control (RCAC) feature, so there’s probably a reason for the limitation.

LPAD And RPAD Functions

The LPAD (left pad) and RPAD (right pad) functions are used to pad an expression on either the left or right with spaces (default) or a string expression. The functions accept three parameters (the third one is optional):

1. Expression to pad
2. Length
3. Padding expression (optional)

The following LPAD expression can be used to format a monetary value for printing a check amount:

VALUES (LPAD(1235.25,15,'*'))

It returns a left padded value with a length of 15 characters:

********1235.25

Likewise, look at this RPAD expression:

VALUES (RPAD('1235.25',15,'*'))

It returns a right padded value with a length of 15 characters:

1235.25********

These padding functions are particularly useful for building fixed width columns for export files.

If you’re not on IBM i 7.2 yet, it’s fairly easy to emulate these functions as follows:

VALUES (RIGHT(REPEAT('*',15) || '1235.25',15)); -- Emulate LPAD
VALUES (LEFT('1235.25'||REPEAT('*',15),15));    -- Emulate RPAD

Author’s Note: At DB2 for i Group Level 1, these functions return an error SQL0440 (Routine LPAD in *N not found with specified parameters.)

That’s a Wrap

We’re not quite done yet. In a future tip, I’ll discuss the Grand Poobah of features in DB2 for i 7.2: row and column access control (a.k.a. RCAC).

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 STORIES

DB2 for i 7.2 Features and Fun, Part 2

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