This article is the sixth in a series about the enhancements delivered in IBM Db2 12, and focuses on improved features that improve performance for analytical queries. Read part one, part two, part three, part four and part five.
New application development technologies and paradigms are entering the marketplace at a rapid rate, providing organizations with the capability for continuous, high-speed deployment of new IT solutions with sophisticated functionality. This can provide businesses with the capability to either keep pace with their competitors or to gain a competitive advantage over them.
Many new IT solutions are concerned with processing large amounts of data very quickly and inexpensively, performing complex analysis, and discovering information and obtaining insight from the vast amounts of data held in data warehouses and operational data stores.
IBM customers, many of whom are market leaders in their own business sectors, need to be able to maintain a competitive edge by unearthing the valuable information often hidden in the ever-growing amount of data stored in their Db2 for z/OS databases. Db2 12 therefore builds on the product's historical mission of continuously adapting to modern application development technologies and patterns with a large set of enhancements aimed at enabling those modern applications.
DB2 for z/OS: Adapting to Modern Application Development Paradigms
Many modern application developers work with REST and RESTful services, together with JSON data formats, both as a matter of preference and of company strategy. To enable this kind of application, Db2 12 provides the means for developers to use a REST service to access Db2 data via an HTTP request. This is done without the need to code SQL in the request, and the result is returned as JSON data, as shown below.
The story behind this feature, which was retrofitted to Db2 11, is that Db2 development wanted to get RESTful API requests and JSON data formats into the mainframe environment in a way that enables clients to take maximum advantage of the assets that exist there, those assets including IMS and CICS transactions, and Db2 stored procedures.
Providing support for JSON is vital because it’s the most widely used protocol/language-independent data format for developing mobile apps, and code for parsing and generating JSON data is readily available in a large variety of programming languages.
REST, an architectural style of APIs for networked hypermedia applications, is one of the most pervasive Web application technologies. Its main use is to build Web services; a service based on REST is called a RESTful service.
Underlying the Db2 for z/OS enablement of RESTful APIs and JSON data formats are two architectural principles:
1. Providing a common and consistent entry point for mobile access to one or many of the back-end systems (e.g., IMS TM, CICS, and Db2)
2. Simplifying front-end functions by allowing them to pass RESTful API requests and to retrieve JSON formatted data, rather than being involved in or even aware of, complex data transformation
This enablement was originally delivered via the Db2 Adapter for z/OS, but that has since been deprecated and has been superseded by native Db2 REST services, available for both Db2 12 and Db2 11. The Db2 product manuals
have been extensively updated to reflect this new feature.
Db2 rest services requires a REST HTTP client, such as IBM z/OS Connect Enterprise Edition or an IBM Cloud API management solution. In this article, we’ll focus on one of these, z/OS Connect, an IBM software product which provides a unified solution for cloud, mobile and web application integration. It offers service enablement, management and discovery, and secure access to z/OS assets such as IMS TM or CICS transactions, or Db2 stored procedures.
Db2 has provided an adaptor for z/OS Connect that runs as a new Db2 started task. Inside that started task is a WebSphere Liberty Profile Server that handles the RESTful API requests.
As seen in the figure above, this greatly simplifies connecting mobile devices to z/OS assets, allowing customers to realise the benefits of a streamlined approach to mobile application development.
Db2 12 delivers an impressive set of application-related enhancements. There are many changes in the areas of the SQL language, XML performance and improved JSON enablement, but let’s start with a quick look at some of the enhancements related to application management and ease-of-use:
- System profiling is enhanced so that system profiles—which, for example, provide the ability to monitor and control connections and set default values for special registers for specific applications—are automatically started by Db2.
- It’s possible to set default values for global variables based on system profiles. For example, you can set a default value for the MOVE_TO_ARCHIVE global variable to support transparent archiving.
- There’s a new option for the MODIFY DDF command, PKGREL(BNDPOOL), which causes high performance DBATs to be pooled at connection termination, instead being destroyed. This enhancement has been retrofitted to Db2 11.
- IMS Attach support is added for DSNULI, the universal language interface.
Let’s move on to look at some of the larger items, but remember that the order in which these are presented isn’t intended to represent their relative importance. Clearly, different clients will find different enhancements of interest, based on their requirements.
DRDA Fast Load
Distributed Relational Database Architecture (DRDA) Fast Load provides a callable API command for fast streaming of data into Db2 across the network. Db2 already provides a stored procedure called DSNUTILU to provide applications with the capability to load data into DB2 from a network client. However, this is difficult to use, and the application must first transfer the data to a z/OS file using FTP.
The solution is a new Db2 Client API, with support for the Command Level Interface and the Command Line Processor, providing the capability to load data into Db2 remotely. This enables easy, fast loading of data from a file that resides on the client without the need to use file transfer to send it to z/OS. DRDA Fast Load continuously streams data from the client into blocks for passing to the LOAD utility. The task that extracts data from the blocks and passes them into the LOAD utility runs under an SRB and is eligible for 100 percent IBM z Integrated Information Processor offload, helping customers manage their costs.
DRDA Fast Load overlaps network operations with data ingestion on the Db2 server, achieving elapsed time improvements. Measured results so far show this to be as fast as or faster than the previous method—using FTP together with the Db2 LOAD utility.
This solution is applicable for a number of cloud use cases. Db2 12 supports an internal format for SAP, as well as delimited and spanned format data, including large object data.
IBM plans to use this feature in the future for fast write from Spark.
Enhanced MERGE Support
Initial support for the MERGE statement was delivered with Db2 for z/OS V9, but the functionality was limited to UPDATE and INSERT—and only one of each—and the application programmer had to code host variable column arrays to provide multiple rows of input data.
In Db2 12, MERGE is greatly enhanced and is aligned with the behavior defined in the SQL Standard and with the other members of the Db2 family.
The source data can now be a table-reference. Multiple MATCHED clauses are allowed, and additional predicates can be coded with the MATCHED and NOT MATCHED clauses. The DELETE operation is supported, and IGNORE and SIGNAL are also supported. This allows the programmer, for example, to merge data from one table directly into another table, with a single SQL statement.
With the growth of web and mobile applications, application developers are looking for more efficient ways to develop applications that perform well. One of the challenges application programmers face is writing efficient SQL for paging through results sets. To address this problem, Db2 10 introduced a new access path called range list access.
This feature, known as SQL pagination has been very successful, but Db2 12 takes this a stage further by providing easier and cleaner syntax to support SQL Pagination, and introduces the OFFSET n and LIMIT keywords. OFFSET n provides the ability to return rows starting with the ‘nth’ row of the result set, and LIMIT is alternative syntax to FETCH FIRST and FETCH NEXT.
There are two kinds of SQL pagination, the first of which is numeric-based pagination. In the example below, all the columns are selected from the sample table, but the programmer has specified an offset into the result set (OFFSET 10 ROWS), and the number of rows to be retrieved (FETCH FIRST 10 ROWS ONLY).
SELECT * FROM table1 OFFSET 10 ROWS FETCH FIRST 10 ROWS ONLY
The second kind of SQL pagination is data-dependent pagination. Data-dependent pagination will perform much better than numeric pagination, especially when the offset into the result set is quite large, because with numeric pagination, Db2 has to discard many rows before it can begin to return rows to the application.
Prior to Db2 12, programmers had to use the existing syntax, but the new syntax is provided to make the SQL more intuitive and less cumbersome to code.
WHERE (LASTNAME = ‘SMITH’ AND FIRSTNAME >= ‘JOHN’) OR (LASTNAME > ‘SMITH’)
New equivalent syntax:
WHERE (LASTNAME, FIRSTNAME) > (‘SMITH’, ‘JOHN’)
A new feature called piecemeal delete allows interim commits for applications deleting many rows, to mitigate the locking and logging issues which often arise when many rows are affected by a single DELETE statement. Consider, for example, the following simple statement, which could potentially delete many millions of rows, taking many locks without a commit, and performing a very large amount of logging in a short period of time:
DELETE FROM T1 WHERE C1 > 7
Piecemeal delete mitigates these problems by allowing the application to delete a subset of the target rows in one commit scope. The application can then iteratively cycle round the process of deleting a subset of the rows, issuing a commit, deleting a further subset of rows and so on until all the target rows have been deleted. This is likely to reduce the amount of work Db2 has to do if the application abnormally terminates before issuing a commit, and to reduce the number of UNDO-REDO log records Db2 has to write during the roll-back process.
To enable this, Db2 12 provides support for a SELECT as the target of a DELETE statement, where the fullselect allows for FETCH FIRST n ROWS ONLY to be specified. For example,
DELETE FROM (SELECT * FROM T1 WHERE C1 > 7 FETCH FIRST 5000 ROWS ONLY)
This is an alternative to the existing tactical solution deployed by some customers, to call an AUTONOMOUS SQL PL stored procedure.
The SQL Procedures Language (PL) was introduced as far back as Db2 for z/OS V7, and was significantly enhanced in Db2 9 with support for native SQL stored procedures which run inside the Db2 engine. SQL PL has been further improved in subsequent releases, and Db2 12 introduces yet another broad set of enhancements.
Richer capability is now available to the programmer in the body of an SQL PL trigger. SQL PL control statements such as IF-THEN-ELSE, LOOP, and REPEAT are now allowed. A wider variety of SQL statements, including dynamic SQL, variables and handlers are also available. This all means easier porting of triggers from other database management systems (DBMSs).
Triggers also benefit from VERSION and DEBUGGER support. The VERSION support provides a much better way to change a trigger without having to DROP it and then CREATE it, and means that it is now possible to change a trigger online and maintain trigger activation order.
However, this richer capability means that there is some performance overhead compared to an equivalent traditional trigger.
SQL PL enhancements beyond triggers (e.g., in native SQL PL stored procedures and in user-defined functions (UDF) ) now includes support for constants. The SQL PL source can be obfuscated. Dynamic SQL is allowed in SQL PL UDFs and stored procedures, and there’s now DBMS_OUTPUT for UDF tracing. This last enhancement has been retro-fitted to Db2 11.
Gareth Z. Jones has worked in IT since 1985. Until 2000, he was an IBM client, with experience as a systems programmer and database administrator. He now works in Db2 for z/OS development as a member of the SWAT Team, which is led by John Campbell. He has worked with many customers around the world to help them succeed in their use of Db2. Gareth has written several technical papers and presented at many conferences and group meetings. He can be contacted via email at firstname.lastname@example.org.