TR8 DB2 For i Enhancements, Part 1

IBM i 7.1 Technology refresh 8 was released on June 6, 2014, and it has another group of small database enhancements worth reviewing. IBM i 7.2 users will find that they already have these features. This tip assumes that you have IBM i 7.2 or IBM i 7.1 with Database Group PTF Level 29 or higher installed.

QSYS2.GENERATE_SQL Stored Procedure

This is one sweet new stored procedure IBM has given us. Its purpose is to programmatically retrieve SQL source code into a source member.

This procedure can accept a boatload of parameters as shown below. Many of the parameters control an aspect of how the code should be retrieved, such as whether or not the OR REPLACE option should be included with a CREATE statement, what DATE_FORMAT should be used, etc.

All of the parameters are input only, and remember, all parameters with defaults may be omitted. If you need help understanding some of the parameters, just see the IBM developerWorks page that describes this procedure.

Here are a few examples of how to use it. Note that with so many parameters, it’s advantageous to call the procedure using named arguments:

-- 
-- Available in DB2 for i 7.1 Group PTF Level 29 or IBM i 7.2
CALL QSYS2.GENERATE_SQL('%', 'ADVWORKS', 'TABLE', 
REPLACE_OPTION => '0');
CALL QSYS2.GENERATE_SQL('%', 'ADVWORKS', 'PROCEDURE', 
REPLACE_OPTION => '1');
-- The file and member must already exist 
CALL QSYS2.GENERATE_SQL('%', 'ADVWORKS', 'PROCEDURE', 
REPLACE_OPTION => '1', 
DATABASE_SOURCE_FILE_NAME=>'QSQLSRC',
DATABASE_SOURCE_FILE_LIBRARY_NAME=>'DEV',
DATABASE_SOURCE_FILE_MEMBER => 'PROCEDURES');

The specified source file name and source member must already exist. The % wildcard character can be used in the schema name and the object name.

In this example, all function definitions are retrieved from any library that begins with SYS, such as SYSIBM and SYSIBMADM:

-- Get all functions in libraries that begin with SYS
CALL QSYS2.GENERATE_SQL('%', 'SYS%', 'FUNCTION', 
REPLACE_OPTION => '1', 
DATABASE_SOURCE_FILE_NAME=>'QSQLSRC',
DATABASE_SOURCE_FILE_LIBRARY_NAME=>'DEV',
DATABASE_SOURCE_FILE_MEMBER => 'SYSFUNCS');

While the QSQGNDDL API can also retrieve SQL source code, this procedure is much simpler to use. IBM has done a great job of giving DB2 coders features formerly only available using IBM i OS or a high level language programming language. It’s a great thing when a DB2 database developer doesn’t have to delve into platform specific items to accomplish a task.

This feature can really help administrators and developers by allowing them to retrieve source code programmatically. Of course, if the procedure or function source has been obfuscated, this procedure will not retrieve the source code.

If I could make a wish for an enhancement, it would be that a procedure like this would allow the user to optionally specify the creation of an INSERT or UPDATE template statement based on a given table or view name. When I develop in SQL Server Management Studio, I use a similar feature and it saves quite a bit of time. If you’ve ever had to manually code an INSERT statement against a table with a truckload of columns, you can imagine what a time saver it would be to have the column names mapped out already.

QSYS2.DELIMIT_NAME Scalar Function

The DELIMIT_NAME function accepts a string expression and will return delimiters (as necessary) to make the expression a valid DB2 column name. For example, normally you can’t name or alias a column expression with a name like % Total. However, with delimiters (DB2 uses the double quote character), DB2 will accept the delimited column name “% Total” as valid. That’s cool that you can have crazy names in DB2 but it can be a pain in the neck because to reference them in a query you have to remember they’re case sensitive, the delimiters are required, etc.

DELIMIT_NAME will accept a string expression representing a column name and see if it conforms to the DB2 naming rules (valid characters, no embedded spaces, etc.). If it conforms, the function will simply return the name passed to it. If it doesn’t conform, then the function will add the DB2 delimiter (double quote) around the name.

This function is useful when constructing dynamic SQL. For example, if you have a report writing application that allows the user to create queries with specific names for the columns, this function would allow the user to define friendly column names (for something like Excel headings) without making SQL choke! This function can also be used to validate whether or not a name is a valid for DB2.

This SQL statement:

VALUES (DELIMIT_NAME('Sales Order')),
       (DELIMIT_NAME('TOTAL$')),
       (DELIMIT_NAME('PRODUCT LENGTH (")')),
       (DELIMIT_NAME('%Rank***'));

Returns the following results:

"Sales Order"	
TOTAL$	
"PRODUCT LENGTH ("")"	
"%Rank***"	

Reference: QSYS2.DELIMT_NAME UDF

QSYS2.RESTART_IDENTITY() Procedure

This procedure gives developers and administrators the ability to reset a DB2 identity column’s next value. For example, if you use the CPYF command to copy a table with an identity column to a new table, you’ll unpleasantly find that the destination table’s identity column starts with the original initial value. That’s a recipe for a duplicate key value error.

Say you copy file DATALIB/ITEMMASTER (with an INTEGER identity column) to DEV/ITEMMASTER2. The following procedure call will resync the next identity value from the old table to the new table:

CALL QSYS2.RESTART_IDENTITY(
'DATALIB', 'ITEMMASTER', 'DEV', 'ITEMMASTER2') 

As shown by the parameters, the source table’s identity column attributes are placed on the destination table’s identity column.

I’m not sure why IBM did it this way instead of just letting you reset the identity column’s next value to whatever you’d like. I’d like to see a procedure that simply lets you set the next value for a table. However, with a little finagling, you can almost do that. Let’s say DEV/ITEMMASTER2 has an identity column called ITEM_ID and that you want the next ID value to be 100,000. You can create a dummy table containing an identity column of the same name of the table you want to change, and then call RESTART_IDENTITY:

CREATE TABLE DEV.ID_DUMMY 
(ITEM_ID INT AS IDENTITY 
(START WITH 100000 INCREMENT BY 1) 
NOT NULL PRIMARY KEY);

-- Reset DEV/ITEMMASTER2 next id (using system name ITEMM00001)
CALL QSYS2.RESTART_IDENTITY(
SOURCE_SYSTEM_TABLE_SCHEMA=>'DEV', 
SOURCE_SYSTEM_TABLE_NAME=>'ID_DUMMY',
TARGET_SYSTEM_TABLE_SCHEMA=>'DEV',
TARGET_SYSTEM_TABLE_NAME=>'ITEMM00001'); 

Now, DEV/ITEMMASTER2’s next identity value will be 100,000.

Reference: QSYS2.RESTART_IDENTITY

Run SQL Scripts

With the June 2014 release of System i Access (SI53584), the Run SQL Scripts utility will allow you to save all of the result sets when running a stored procedure (or multiple SELECT queries run in a script) that return multiple result sets. In prior releases, the “Save Results” option was not available when multiple result sets were returned. Although not officially part of TR8, the timing of this service pack was close to TR8 so I thought I’d mention it as I find myself working with multiple result set procedures quite frequently.

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

Protect Your Intellectual Property: Obfuscate DB2 For i Source Code

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