SQL Goodies in DB2 for i5/OS V5R4, Part 1
April 12, 2006 Michael Sansoterra
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.
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:
A sample statement with host variables looks like the following in free form RPG:
EXEC SQL Select EmpName Into :EmpName Where EmpNo=:EmpNo;
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)
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
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.
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.
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.