SQL Goodies in DB2 for i5/OS V5R4, Part 1

Since V4R2, I’ve watched SQL enhancements like a hawk, eagerly waiting to use the improvements IBM has given us. V5R4 SQL contains several new items that will be outlined here. Here are a few thoughts on this significant release, which I gleaned from the DB2 Summary sheet on IBM’s Web site.

• The SQL syntax in V5R4 meets the Core requirements for the 2003 SQL standard. A review of the 2003 SQL standard can be found by clicking here.
• Routines written in the SQL language will now have better execution times thanks to enhancements to the code generation engine, which converts the SQL statements in an SQL trigger, function, or stored procedure into an ILE C program under the covers. If you’re upgrading from an earlier release, you’ll need to re-create the existing SQL routines using the new engine to take advantage of the performance gains.
• Journaling has been enhanced to automatically journal sequence objects (i.e. data area) when appropriate.
• DRDA now offers support for distributed transactions (in other words, a transaction that spans the boundary of more than one database server).
• The unqualified column name has been increased from a limit of 30 characters to 128.
• The maximum number of tables referenced in an SQL statement has been increased from 256 to 1,000. However, the maximum number of tables stored in a view is still limited to 256.
• The maximum length of an SQL statement has been increased from 65 KB to 2 MB.

Why would a SQL statement need to be so long? Only a few months ago I created a program to automatically create a table function based on some user defined formulas. However, as much as I tried to remove extraneous spaces, the final SQL statement still exceeded the maximum length. Increased statement size will hopefully prevent me from re-writing SQL in RPG for future projects.

Also, I’ve worked on data upload projects where it would be handy to quickly write a client side program to generate a large INSERT VALUES(….),(….) SQL statement to insert many rows at one time.

Surprisingly, the maximum size for large objects (BLOBs, CLOBs, DBCLOBs) has not yet increased from the original 2 GB limit.

Now, let’s get into some of the new features in SQL with i5/OS V5R4.

Instead of Triggers

“Instead of Triggers” are triggers written in the SQL language that give the developer control over how data in a view is modified. Traditionally, views based on a single table were the only updateable views – and updating a view was therefore about the same as updating its underlying table. Now, however, logic can be added to any kind of SQL view via an “Instead of Trigger” that will allow inserts, updates or deletes.

Introduced in V5R3, Instead of Triggers were only capable of working against single table views. In V5R4, Instead Of Triggers are improved to work with multi-table views (i.e. views with unions or joins.) This means that updates and deletes can be executed against a complex view.

Updating multi-table views wasn’t possible in the past because the database manager couldn’t possibly know how to modify data in a multi-table view. Now however, the programmer has complete control over how updates should be done. If a view has primary and secondary tables, perhaps the update should only affect the secondary table and ignore changes to columns in the first – the programmer now codes for that scenario. For another example, if a view consists of the UNION of open purchase orders with closed purchase orders, the programmer can make sure that an update or delete against the view is only operational against rows in the open purchase orders table.

If your data requires encryption when it is being stored, an Instead of Trigger can be used to perform the encryption logic so that plain text is encrypted when stored in the table. This way, client applications don’t need to know about how to use DB2 encryption functions before inserting data–the application just inserts the data via a view with an Instead of Trigger. The possibilities are endless.

IT Jungle has already published an article demonstrating Instead of Triggers, called Perform the Impossible: Update a Multi-Table View using INSTEAD OF Triggers

Embedded SQL in Free Form RPG

At last free-form coders no longer have to relegate SQL calls to traditional C-spec subroutines or subprocedures at the bottom of the program (or worse, break out of /Free in the middle of the program). Now, an SQL statement can be placed directly in a /Free block. The only rules are:

• The SQL Statement must begin with EXEC SQL (on one line)
• The statement itself can span multiple lines (without a special line extender)
• The statement, like normal free format, must be terminated with a semicolon

A sample statement with host variables looks like the following in free form RPG:

EXEC SQL Select EmpName
           Into :EmpName
          Where EmpNo=:EmpNo;

Miscellaneous Enhancements

A PageSize parameter has been added to the CREATE INDEX statement. This option gives database administrators greater control over how efficiently their indexes can be used. Specifying a larger index page size means that DB2 can read more key values at a time–a good thing for queries that require the scanning of a large range of values for a given index.

CREATE INDEX ORDERS_I1 ON ORDERS (ORDERDATE,ORDTYPE,ORDERNO)
PAGESIZE(128)

In this example, the index is created with a page size of 128 KB.

Hex literals can now contain embedded spaces. I suppose this was done for readability as sometimes it would be nice if we could have a break in our long hex strings as a reminder where an important break should be. Spaces are just ignored:

Select x'F2  F3  F4  F5' From SysIBM.SysDummy1 

Returns: ‘2345’

I’d like to see this in RPG, as I have a greater need for it there!

ISO Timestamp Format

The new ISO timestamp format means that character representations of timestamps can now be entered as ‘yyyy-mm-dd hh:mm:ss.nnnnnn’ which is only slightly kinder and gentler than the IBM format of yyyy-mm-dd-hh.mm.ss.nnnnnn.

Now timestamp constants can be constructed as follows:

Select TimeStamp('2005-12-25 12:00:00.000000') As MerryChristmas
  From SysDummy1 

The microseconds portion of the timestamp can be omitted:

Select TimeStamp('2005-12-25 12:00:00') As MerryChristmas
  From SysDummy1 

This also means ISO date and USA time values can now easily be concatenated to make a timestamp.

Row Value Expressions in Predicates

A predicate in SQL evaluates whether a condition is true, false or unknown. In the past, each predicate was evaluated for a single value. Now, however, groups or “rows” of values can be evaluated at once. These groups of values are called “row value expressions.” Below is a simple example:

Select * 
  From Projact
 Where (PROJNO, ACTNO)=('AD3100','10')

The column list enclosed in parenthesis is the row value expression.

In addition to constants, a row-expression can also be compared against columns in the current table, column expressions, or a full select. As you might have guessed, the number of columns must be equal and of compatible data types.

Below is an example of an IN predicate using a row value expression:

Select * 
  From SysTables 
 Where (Sys_TName,Sys_DName) In (Select Sys_TName,Sys_DName
                                   From SysColumns
                                  Where Column_Name='ITEMNO')

Note that a constant list is not allowed when using a row value expression with an IN predicate. I would have found that useful.

Although useful for coding shorthand comparisons with constants, as far as I can tell, everything that can be accomplished with row value expressions (when compared to a fullselect) can be accomplished with the EXISTS predicate. If anyone thinks of an example proving otherwise, please let me know.

Fullselect in a Subquery and Scalar Fullselect

In the past, writing subqueries suffered the limitation of not being able to include a “fullselect.” Just what is a fullselect? It is basically multiple subselect statements combined with a UNION, EXCEPT, or INTERSECT. (A subselect is only a portion of the Select statement in that it does not include any of the clauses following and including ORDER BY.)

A fullselect can now be used in a subquery and in a scalar subselect. A subquery is a query that is enclosed in parentheses and is typically used in a predicate test. A scalar fullselect is a query in parentheses that returns a single row and column (such as when embedding a subselect in the column list of a Select).

As an example, let’s take a simple subquery example where we want a list of all employees that do not have a photo or resume on file. To do this we need to check the employee master against the photo and resume tables. This problem can be solved with one subquery that contains a fullselect as follows:

Select *                
  From Employee         
 Where EmpNo Not In 
/* This subquery is a FULLSELECT */
     (Select EmpNo 
        From Emp_Photo
   Union
      Select EmpNo 
        From Emp_Resume)

ORDER OF in ORDER BY Clause

A new feature of the ORDER BY clause will allow the developer to force DB2 to use an ORDER BY specified in a common table expression or nested select in the outermost Select of a given query.

With

ProjectActivity As (                      
  Select ProjNo, Count(*) As Entries      
    From ProjAct                          
Group By ProjNo                           
Order By Entries Desc, ProjNo),           
                                          
EmpActivity As (                          
  Select ProjNo, Sum(EmpTime) As EmpHours 
    From EmpProjAct                       
Group By ProjNo                           
Order By EmpHours Desc, ProjNo)           
                                          
Select ProjectActivity.ProjNo, Entries,   
       EmpHours, Project.*                
  From ProjectActivity                    
  Join Project On Project.ProjNo=ProjectActivity.ProjNo  
  Join EmpActivity On EmpActivity.ProjNo=Project.ProjNo  
Order By Order Of ProjectActivity, EmpHours Desc

In this simplistic example, Order Of ProjectActivity makes the query follow the ORDER BY specified in the ProjectActivity common table expression, followed by the EmpHours column in descending order.

RCDFMT Keyword

The CREATE TABLE statement has a new RCDFMT keyword that RPG programmers who work with SQL tables will love. This iSeries specific keyword allows the SQL developer to name the record format of the table (and thereby avoid those pesky RPG renames on the F Spec).

Create Table Agency (AgencyId char(10), …) 
RcdFmt AgencyF

USE AND KEEP EXCLUSIVE LOCKS

If you use transaction processing in your SQL applications, a new lock clause “USE AND KEEP EXCLUSIVE LOCKS” is available on the isolation clause of the SELECT statement. Available for read stability and repeatable read isolation levels, it will keep update locks on all processed rows until a COMMIT or ROLLBACK is encountered. This clause will insure that after the data has been read none of the rows can be updated from another job until the locks are released. Prior to USE AND KEEP EXCLUSIVE LOCKS, read locks would be placed on rows instead of update locks.

/* Read data with intent to update */
Declare CompleteOrders Cursor For
 Select *
   From Shipments
  Where Processed='N'
   With RS USE AND KEEP EXCLUSIVE LOCKS

Of course you wouldn’t want to overuse this feature, as it incurs overhead and could cause conflicts with other jobs waiting to read the data. Effectively, this setting will keep others jobs from reading the data while the lock is in effect unless the other job has an isolation level of *NONE.

Now, let’s take a look at some new statements available in i5/OS V5R4.

Alter Procedure

This is a small but extremely useful enhancement for shops that require high availability for their stored procedures or that have to deal with granting security to various users. Using ALTER PROCEDURE means not having to DROP and CREATE an existing procedure. Now you can issue one statement to alter an existing procedure. This carries the extra benefit of preserving all authorities on the existing procedure so that GRANT statements (or EDTOBJAUT) do not have to be issued again. If you’ve ever created a stored procedure, assigned authority and then realized you had forgotten a keyword, such as a SET OPTION value, your life will now be easier.

A developer can also put a new procedure in production without “hosing” processes using the old version of the procedure. In the past, iSeries developers have had to “drop” a procedure and then recreate it, creating a small window of time when the procedure doesn’t exist, a potential pitfall for procedures with high usage. I was able to successfully create a long running SQL stored procedure, call it from a job, alter the procedure (while it was still in use by another job), call the altered procedure from yet another job and still have everything finish OK.

As a final reminder, when using “overloaded” procedures (i.e., the same procedure name with different parameter signatures), create your procedure with a specific name so that it can be unambiguously referenced in the ALTER SPECIFIC PROCEDURE statement.

Now that ALTER PROCEDURE has arrived, hopefully ALTER FUNCTION and ALTER VIEW will be given to us in an upcoming release, especially since views have more security settings to maintain than do procedures.

As a side note, early adopters of V5R4 will need to load PTF SI22741. I’m guessing this PTF will be included in the first database group PTF set.

Create Procedure TEST()  
Language SQL
Set Option UsrPrf=*Owner
Begin
    /* Logic goes here */
End 

Oops, we forgot a parameter:

Alter Procedure TEST()
Replace(In Test Char(1))   
Begin                      
    /* Modified Logic goes here */     
End                        

The above example demonstrates how to replace the body of the SQL function. External procedures can be altered as well as illustrated here:

Create Procedure I3/PROGRAM1(In Parm1 Char(10))
External 
Language RPGLE 

Blast, I forgot to specify the parameter style again. ALTER PROCEDURE to the rescue:

Alter Procedure I3/PROGRAM1 
Parameter Style General

Label On Index

While not adding functionality, per se, this is a welcome enhancement, especially for companies required by Sarbanes-Oxley to use source management software. When creating an SQL object such as a table or view, I generally like to include multiple statements in a single source member, for example:

/* Create Table */
Create Table ItemMaster (…);

/* Put a label on the object */
Label On Table ItemMaster Is 'Item Master';

I can now do the same with Indexes to make sure the object is labeled after being created. The syntax is simply:

Label On Index ITEMI1 
Is 'Item Master by Description SRTSEQ(*LANGIDSHR)'

Developers will no longer have to remember the tedious task of manually issuing a CHGOBJD command to put a label on the index object. I’m hoping we’ll be able to LABEL ON more SQL objects in the near future.

Set Current Debug Mode

The current debug mode determines whether subsequent SQL routines will be enabled for debugging or not. Acceptable parameter values are ALLOW, DISALLOW or DISABLE:

SET CURRENT DEBUG MODE = ALLOW

The difference between DISALLOW and DISABLE is that an ALTER PROCEDURE statement can be issued to change a procedure from DISALLOW to ALLOW to permit debugging, whereas if a procedure is created with DISALLOW, debugging cannot be enabled without dropping and re-creating the procedure.

The new CURRENT DEBUG MODE special register is used to query which mode the SQL session is currently in.

Set Current Degree

SET CURRENT DEGREE allows the developer to control the degree of parallel processing for a given query from within the comfort of SQL. Previously, this was done with the CHGQRYA CL command or PARALLEL_DEGREE parameter in the QAQQINI query options file. Whether you have a hog query that you want to limit or if you have a query that needs maximum horsepower, SET CURRENT DEGREE can help. The new CURRENT DEGREE special register can be used to query the current setting.

Set Session Authorization

The new SET SESSION AUTHORIZATION statement is used to change the user profile handle for the current thread (not the job). This statement can be issued interactively and used in embedded SQL programs but is not allowed in SQL triggers, procedures and functions.

Normally profile changes are handled via adopted authority. However, areas of the iSeries such as the IFS don’t use adopted authority and hence it cannot be used to give the end user access to those resources. SET SESSION AUTHORIZATION would be useful for allowing SQL to work with external procedures or functions that access the IFS. It could also be useful when, in an embedded SQL program, adopted authority should be temporarily suspended.

Examples:
// Change the user
SET SESSION AUTHORIZATION=:USER
// Change to the job user
SET SESSION AUTHORIZATION=SYSTEM_USER

A word of warning about using this feature: Make sure you clean up any open resources and global temporary tables before swapping the user profile, as the system may clean them up for you.

Give Me More!

Stay tuned, since there are a few more enhancements to be discussed. In the future, I still need to cover new SQL descriptor statements, new functions, new special registers, recursive queries, new OLAP ranking functions, and Materialized Table Queries.

Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. Click here to contact Michael Sansoterra by e-mail.