SQL Enhancements in DB2 12 ESP for z/OS

Disclaimer: Information regarding potential future products is intended to outline IBM’s general product direction and it should not be relied on in making a purchasing decision. The information mentioned regarding potential future products is not a commitment, promise, or legal obligation to deliver any material, code or functionality. Information about potential future products may not be incorporated into any contract. The development, release, and timing of any future features or functionality described for IBM’s products remains at IBM’s sole discretion. 

There are numerous enhancements to SQL in DB2 12 for z/OS, including, but not limited to:

• Enhanced MERGE statement
• Piecemeal DELETE statement
• Pagination support
• Enhanced array data type support
• Global variable enhancements
• SQL PL in triggers
• Unicode columns in EBCDIC tables
• Increase in the number of tables referenced in views, UDFs, and statements
• Very cool new built-in functions, including hashing functions

There is also a significant amount of SQL performance enhancements in DB2 12 for z/OS which contribute to the “out of the box” savings realized when migrating from DB2 11 for z/OS. The IDUG team explored some of the most significant new SQL features and tried them out on our test system.

Here is just a taste of what is coming in our white paper due out this fall!

Enhanced MERGE

Prior to DB2 12 for z/OS, you could only insert / update a single row of data, or multiple rows by using arrays of host variables.  There was no option to do a delete of an existing row.  There is also a very important use case for Merge that has been available in DB2 for LUW, but not available in DB2 for z/OS.  This use case allows the source to be a Select statement - from another table, view, join, etc.  A very common scenario is to have a staging table populated with a set of new data to be merged with the target table.  The staging table might include rows to be inserted, updated, or deleted from the target.

DB2 12 adds this important capability and makes Merge a truly valuable tool in the programmer’s toolbox.

XMLModify Update Support

In DB2 10 for z/OS XMLMODIFY introduced the possibility to update partial XML documents. This removed the need to delete and reinsert the entire document.  XMLMODIFY allowed to do in place replacing, inserting and deleting of nodes.  The way to do this was issuing an UPDATE statement against the table and set the XML column using the XMLMODIFY function.

In DB2 12 for z/OS the XMLMODIFY functionality has been improved to allow multiple updates within one XML document. In order to achieve this the XMLMODIFY syntax now accepts FLWOR expressions as well as conditional and sequence expressions. The improvements in XMLMODIFY in DB2 12 for z/OS do not only increase the usability of this feature, but also the performance.  The numbers vary with the amount of elements that can be changed in a single statement, from 5 changes and up the performance improvements can be spectacular.  Any feature that makes code more readable, more usable and better performing should definitely be explained to every DBA and developer and gets an IDUG thumbs up.

SQL Pagination

One of the oldest problems in DB2 performance was the efficient processing of pagination.  Pagination is a programming paradigm in which an online application needs to process an indeterminate number of rows, but does not want to fetch them all in before presenting them to the user.  Instead, it fetches a fixed number, returns these to the user, and then pages forward at the user’s request.  In particular, this is a best practice for CICS transactions (browser based applications present a new set of challenges because the UI makes it easy for the user to scroll forward, rather than page forward). The second problem with pagination is that the correct SQL code is somewhat cumbersome and error prone.

Prior to DB2 12 for z/OS the typical SQL to read the next set of rows would be to open a cursor similar to:

SELECT cols FROM T1
  WHERE (KEY1 = :LAST_KEY1 AND KEY2 > :LAST_KEY2    OR
                  KEY1 > :LAST_KEY1)
  ORDER BY KEY1, KEY2
  FETCH FIRST 20 ROWS ONLY  OPTIMIZE FOR 1 ROW

When coding the same functionality in DB2 12 for z/OS a predicate such as the following will serve the same purpose: 

WHERE (KEY1, KEY2) > (:LAST_KEY1, :LAST_KEY2)

There is another new way in DB2 12 to fetch the next set of rows.  This is the OFFSET clause:

 OFFSET 20 ROWS FETCH FIRST 20 ROWS ONLY

 This tells DB2 to skip the first 20 rows and then start fetching.  This of course is better than the application fetching from the beginning of the rows and discarding the first 20.  If done by the application, there would be 40 fetches for the second page, 60 fetches for the third page, and so on.  With the OFFSET clause, the skipping is done by DB2 and there are only 20 fetches and 20 rows returned to the application.  This syntax is very simple and the larger the offset, the faster the result compared to the application discarding rows.  But, it is not nearly as fast as the new data dependent syntax which takes full advantage of the index to quickly get to the first qualifying row.

Piecemeal DELETE

If ever there was proof needed that an exciting feature doesn’t need to be complicated it has arrived in DB2 12 for z/OS in the shape of “piecewise deletes”.  It solves the old problem of not being able to control locks and logs of a simple delete statement.

DB2 12 for z/OS introduces the possibility to add a “Fetch first.. rows only” clause to a delete statement.  This will allow the simplicity of a simple searched delete statement, with the possibility to control commit frequency, and therefore locking, logging and all other problems connected to long running threads.

Array Data Type Support

The need for flexibility in the creation of services via routines has caused a lot of grief for application developers. This need for flexibility resulted in overloaded parameters passed to routines, or many versions of the same routine implemented for a different collection of parameters. These designs required extra programming time, and an increased effort to maintain the routines after deployment. Even though the introduction of arrays and the user-defined array data type in DB2 11 for z/OS was useful, it was only a partial implementation. The arrays could only be defined and manipulated in SQL PL routines. JDBC applications could pass an array to an SQL procedure as well, but there was no manipulation of the array data types and DB2 defined arrays outside of the SQL PL routines. This limited the use of arrays in applications, and especially in more traditional static embedded applications.

DB2 12 for z/OS introduces additional support for arrays in the form of an array type of global variable. With DB2 12 for z/OS a user-defined global variable can now have an array type, which means that applications can, by way of SQL, populate and manipulate elements of an array global variable and pass those variables to other application processes, as well as to/from DB2 routines. All of this happens inside of SQL statements and so very little application code, if any, is needed to pass variable amounts of data in and out of SQL statements!

What follows is an example of being about to insert a variable amount of data into a table using arrays:

SET HOTEL_TAX_TYPE = ARRAY[1,5];
SET HOTEL_TAX_AMT = ARRAY[2.12,0.35];
 
INSERT INTO TAX
SELECT *
FROM (SELECT 124 AS TRAN_ID FROM SYSIBM.SYSDUMMY1)
INNER JOIN
UNNEST(HOTEL_TAX_TYPE, HOTEL_TAX_AMT)
AS T(TTYPE,TAMT)
ON 1=1;

Advanced Triggers

Existing basic triggers in DB2 11 for z/OS have limited functionality. This limits the ability to handle errors and use complex conditional statements in an area that would benefit greatly from this capability. The addition of SQL PL support enables the users of triggers to overcome the current limitations. This also improves family compatibility allowing development on DB2 for LUW and deployment to DB2 for z/OS. With the introduction of Advanced Triggers, DB2 can support many more SQL statements, logic programming capability, SQL variables, the ability to debug triggers, and versioning. As with many other enhancements to routines in DB2, the addition of SQL PL to Triggers improves portability of applications from other RDBMS products that have this support. The following example is that of a trigger using SQL PL to invoke a stored procedure that actually returns parameters that modify the values of columns inserted into the table.

CREATE TRIGGER mybefore2
    BEFORE INSERT ON t_source
    REFERENCING NEW ROW AS n
    FOR EACH ROW
    BEGIN
        DECLARE multiplier INTEGER CONSTANT 10;
        DECLARE parm2 INTEGER;
        DECLARE parm3 INTEGER;
        CALL mysp1(multiplier, n.data1, n.data2);
    END

SQL Query Performance Enhancements

Performance is a serious focus of DB2 12 for z/OS, and there are several areas of improvements that will impact SQL performance. In this section we will focus on query performance improvements  to the DB2 query transformation and access path selection components that complement some of the important SQL enhancements in recent DB2 releases.

There are two major external factors driving these query performance improvements in DB2 12 for z/OS. This first is the proliferation of the use of certain tools that generate SQL during the application process, and the second is the widespread usage of UNION ALL infrastructure both by DB2 with  system-period temporal tables and transparent archive  introduced in DB2 10 for z/OS and DB2 11 respectively,  and also customer adoption of UNION ALL by customers for similar purposes. With that in mind, the major query performance improvements to benefit new SQL functionality include, but are not limited to:

• UNION ALL transformation improvements
  • Applies to system-period temporal table time travel queries
  • Applies to archive table related queries
  • Also any other home-grown or modern application query involving UNION ALL in a view or nested table expression
• Outer join improvement, especially to views and table expressions
• Join predicate improvements for predicates with expressions
• Improvements to function and expression processing
• Enhanced pruning of unused columns and tables in joins
• Push down of fetch first into nested table expressions and views

Conclusion

There is a dizzying amount of new functionality and performance coming with DB2 12 for z/OS. A lot of these enhancements will improve the performance of your applications without any programming changes, while other enhancements enable significant functionality with a minimal amount of new programming!