DB2 for i 7.2 Features and Fun: Part 3
September 10, 2014 Michael Sansoterra
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.
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):
The following LPAD expression can be used to format a monetary value for printing a check amount:
It returns a left padded value with a length of 15 characters:
Likewise, look at this RPAD expression:
It returns a right padded value with a length of 15 characters:
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).