More V5R3 SQL Enhancements
July 14, 2004 Michael Sansoterra
My last article briefly covered the new Binary and Varbinary data types, some new scalar functions, and the sequence object that is new to the iSeries implementation of DB2. The good news is there are more SQL enhancements to cover!
Once again, since I don’t yet have access to a machine with V5R3, this information was gleaned from the new V5R3 manuals and may be subject to revision! I’d love to hear from someone who has a beta of V5R3 who can offer more insight on these features.
NAMED COLUMN JOIN
A new shorthand join syntax called a “named column join” is implemented with the USING keyword. For example, the following:
Select * From Order Join OrderLines On OrderLines.Company=Order.Company And OrderLines.OrderID=Order.OrderID
Can be shortened to:
Select * From Order Join OrderLines Using (Company,OrderID)
As evidenced by the syntax, this shorthand can only be used when the tables being joined have the same column names. Outer and exception joins are allowed as well.
Lateral correlation is an enhancement that allows a nested select to reference columns in higher levels of the subquery hierarchy.
Consider the following query, which is supposed to join the order header to the summary of several detail files (order lines, order charges, and order taxes):
Select O.OrderID, O.CustName, OL.LineTotal, OC.OrdChgTotal, OT.TaxTotal From Orders O Left Join (Select OrderID, Sum(NetAmt) As LineTotal From OrderLines Group By OrderID) OL On OL.OrderID=O.OrderID Left Join (Select OrderID, Sum(ChgAmt) As OrdChgTotal From OrderCharges Group By OrderID) OC On OC.OrderID=O.OrderID Left Join (Select OrderID, Sum(TaxAmt) As TaxTotal From OrderTaxes Group By OrderID) OT On OT.OrderID=O.OrderID
Notice that the joins must take place outside of the nested table expression, because a correlation to the Orders table (O) is not allowed within the nested select.
Using lateral correlation, the query can be rewritten as follows:
Select O.OrderID, O.CustName, OL.LineTotal, OC.OrdChgTotal, OT.TaxTotal From Orders O, Lateral (Select Sum(NetAmt) As LineTotal From OrderLines Lines Where Lines.OrderID=O.OrderID) As OL, Lateral (Select Sum(ChgAmt) As OrdChgTotal From OrderCharges Charges Where Lines.OrderID=O.OrderID) As OC, Lateral (Select Sum(TaxAmt) As TaxTotal From OrderTaxes Taxes Where Taxes.OrderID=O.OrderID) As OT
As you can see, the LATERAL keyword allows the nested selects to reference columns in the Orders table (O). This enhancement should improve performance in queries where joins to one or more nested selects is required.
EXCEPT AND INTERSECT KEYWORDS
Similar to UNION DISTINCT and UNION ALL, EXCEPT and INTERSECT are used for combining multiple row sets. Here is the syntax:
Select * From Table1 Except [Distinct] Select * From Table2 Select * From Table1 Intersect [Distinct] Select * From Table2
As with UNIONs, the number of columns in both SELECTs must be the same, and the data types of the columns must be compatible. The EXCEPT keyword is used to process all rows in the first row set (table1) that are not in the second row set (table2). The EXCEPT keyword produces results similar in concept to an Exception Join. Say, for example, you want a list of all open orders that have no shipments against them yet. EXCEPT can be used for this purpose:
Select OrderID, CustName From OpenOrders Except Select OrderID, CustName From Shipments
This query will only return rows from the OpenOrders table that don’t exist in the Shipments table.
The INTERSECT keyword is used to combine row sets only under the condition that the same row exists in both row sets. In the prior example changing the EXCEPT keyword to INTERSECT will only pull open orders that have at least one shipment against them:
Select OrderID, CustName From OpenOrders Intersect Select OrderID, CustName From Shipments
Both EXCEPT and INTERSECT are “distinct” operations; that is, all duplicate rows are removed. To clarify this concept for other programmers, the optional DISTINCT keyword may be specified after EXCEPT or INTERSECT.
/* Select All Late Orders */ Select * From OpenOrders Where ExpShipDate<Current_Date And OrderID In (Select OrderID From OpenOrders Except Distinct Select OrderID From Shipments)
SQL PRECOMPILER ENHANCEMENTS
The following is a quick run down on some of the enhancements beneficial to embedded SQL programmers.
USING AN UPDATE WITH A DATA STRUCTURE
For some time now, embedded SQL programmers have had the ability to insert a row into a table from a data structure, as in this RPG example:
D dsOrderLine E DS ExtName(OrderLine) C/Exec SQL C+ Insert Into OrderLine C+ Values(:dsOrderLine) C/End-Exec
The Update statement now works with a data structure as well. The syntax for the update uses the ROW keyword, as follows:
D dsOrderLine E DS ExtName(OrderLine) C/Exec SQL C+ Update OrderLine C+ Set Row=:dsOrderLine C+ Where OrderNo=:OrderNo C+ And LineNo=:LineNo C/End-Exec
Of course, the columns in the table or view should match the fields in the data structure in number and compatible data type. It would be nice if only a subset of columns could be updated as follows (although the manual seems to imply that this isn’t possible.):
D dsShipInfo DS D ShipNo 10I 0 D ShipQty 9P 3 D BOQty 9P 3 D InvAmt 17P 4 C/Exec-SQL C+ Update OrderLine C+ Set (ShipNo, ShipQty, BOQty, InvAmt)=:dsShipInfo C+ Where Current Of OrderLines C/End-Exec
USING THE GET DIAGNOSTICS STATEMENT
As with SQL routines, embedded SQL programs may now use the GET DIAGNOSTICS statement to retrieve information about the status of the last executed SQL statement. In prior releases, variables in the SQL Communications Area provided this feedback. Using GET DIAGNOSTICS in embedded SQL has the advantage of being able to return multiple condition messages, returning more message data, and being consistent with procedures and functions written in the SQL procedural language.
SUPPORT FOR BINARY AND VARBINARY KEYWORDS
The SQL precompiler has been enhanced to allow high-level languages to recognize the new Binary and VarBinary data types. The SQLType keyword is used to declare special data types for use by SQL, as shown in this RPG example:
D TrackData S SQLType(Binary:50)
The SQL precompiler simply replaces the above declaration with the following standard code:
D TrackData S 50A
Since a standard character variable is used for character and binary data, it appears the only reason for using the SQLType keyword is to assure the precompiler that you recognize the distinction between binary and character data. Within the RPG program, TrackData is used just like any other character variable.
Here is a declaration of a VarBinary variable in C:
SQL TYPE IS VARBINARY(50) TrackData;
RPG PRE-COMPILER ENHANCEMENTS
The RPG pre-compiler now includes support for qualified data structure names, nested /Copy directives, and the LikeRec and LikeDS keywords. The precompiler’s inability to handle some of the features of the standard compiler like nested /COPYs has been a bane to many. Thankfully, the pre-compiler can now handle some of these features.
For example, using host variables from qualified data structures is just as you’d expect, with the data structure name and subfield name separated by a period:
DdsScreenData DS Qualified D Item D DueDate C/Exec SQL C+ Select * C+ From MfgOrders C+ Where MfgItem=:dsScreenData.Item C+ And DueDate>=:dsScreenData.DueDate C/End-Exec
As with the Qualified keyword, the LikeDS and LikeRec keywords generate data structures requiring the use of qualified subfield names. Before V5R3, these qualified names were not allowed to be used by embedded SQL.
Unicode is an encoding scheme designed to be used as a universal character set. Instead of using different character sets to identify different alphabets, Unicode aims to store all characters in the world within a single character set. In addition to UCS-2, the iSeries now supports Unicode UTF-8 and UTF-16 transformation formats. UTF-8’s CCSID is 1208 and UTF-16’s CCSID is 1200.
Create Table UnicodeDemo ( UCS2 Graphic(10) CCSID 13488, UTF8 Char(10) CCSID 1208, /* New in V5R3 */ UTF16 Graphic(10) CCSID 1200) /* New in V5R3 */
For a brief explanation of Unicode, see the character conversion portion of the SQL Reference guide.
In the future, I suspect iSeries applications will migrate to one of the Unicode translation formats in order to provide superior international character support.
EXTERNAL PROCEDURE CALLS FOR SERVICE PROGRAMS
A subprocedure (or function) in a service program can now be the target of a stored procedure call. Here’s a sample stored procedure definition and the corresponding procedure interface in RPG program OrderReport:
Create Procedure xxxxx/OrderReport (parmStartDate In Date, parmEndDate In Date) Result Sets 1 External Name 'xxxxx/OrderReport(GETDATA)' Language RPGLE Parameter Style General Reads SQL Data // RPG procedure interface for program // order report P GetData B Export D GetData PI D StartDate D D EndDate D
The External Name keywords specify the library, service program name, along with the subprocedure name enclosed in parenthesis. In RPG, exported subprocedure names are always forced to upper case by default, so make sure the subprocedure name is in upper case in your Create Procedure definition.
This is another welcome enhancement, as a service program’s procedures can now be accessed directly by SQL as a stored procedure call without writing a “wrapper” program.
IMPROVING PERFORMANCE OF SQL ROUTINES
Although it’s not an “enhancement,” another thing worth mentioning is a new section in the V5R3 SQL programming guide that discusses how to improve the efficiency of routines (stored procedures, functions, and the like) written in SQL. This section gives some background on how the SQL procedural processor translates SQL code into C, and notes several things that can hinder the efficiency of the code. This material can be found in “improving performance of procedures and functions” on the iSeries Information Center.
THE POWER OF SQL ON THE ISERIES
During my long, dreary, overextended days on V3R2, I was convinced SQL/400 was forever doomed to be lacking in features when compared with other implementations of SQL. As evidenced with the new powerful join techniques, encryption functions, and pre-compiler enhancements, the iSeries implementation of SQL is as powerful as they come. I used to envy the features offered by other systems, including those in Microsoft Access! Now, it’s the other way around: I wish other systems had the features of the iSeries. After two articles I still didn’t cover all the enhancements in this release!
However, the one thing I envy in other versions of SQL is the capability to issue an UPDATE statement using a join syntax (similar to SQL Server’s syntax). Since IBM has been so good to us, I’m hoping it will make this enhancement sometime soon.
Special thanks to IBM’s Kent Milligan for supplying the syntax for the embedded UPDATE statement using a host structure.
Michael Sansoterra is a programmer/analyst for i3 Business Solutions, an IT services firm based in Grand Rapids, Michigan. E-mail: firstname.lastname@example.org