The distributed data facility (aka DDF) is the component of Db2 for z/OS that enables data access by applications that connect to Db2 via TCP/IP communication links. DDF has been around for over 30 years, but especially during the past 10 years or so DDF workloads have become very large at many Db2 for z/OS sites, with individual Db2 subsystems processing DDF transactions at sustained rates of over 4000 per second (and many more than that for Db2 data sharing groups running on Parallel Sysplex clusters of mainframe servers). For an ever-larger number of Db2-using organizations, the DDF workload is the largest - and the fastest-growing - component of the overall Db2 workload. Given the importance of DDF in the Db2 workload mix, it's worthwhile to consider what a modern Db2 client-server application environment should look like. In looking over the Db2 DDF scene in recent years I've seen a lot of things that I like. In this blog entry I'll share Db2 DDF application environment characteristics that get a thumbs-up from me.
Straight from the app servers to Db2 (for DRDA requesters)
For a number of years, organizations have been migrating away from the use of Db2 Connect "gateway servers" (application server to Db2 Connect gateway server, Db2 Connect gateway server to Db2 for z/OS system) in favor of direct connections from application servers to Db2 for z/OS systems by way of the IBM Data Server Driver (at many sites this transition is already complete). When access to Db2 for z/OS from DRDA requester applications is accomplished through the IBM Data Server Driver, "Db2 Connect" becomes, essentially, just a product licensing term, versus an actual product used - this because entitlement to use the IBM Data Server Driver is provided through an organization's Db2 Connect license (so, if an organization is licensed for Db2 Connect Unlimited Edition for System z, that organization can deploy the IBM Data Server Driver in an unlimited way for applications that access the mainframe system(s) associated with the Db2 Connect license).
There are several advantages to going with the direct connection to Db2 for z/OS versus going through a Db2 Connect gateway server. One is performance: with the "hop" to a Db2 Connect gateway server eliminated, better response times and throughput can be achieved. Another direct-connection benefit is improved problem diagnosis capabilities - error messages have more-specific meaning when the network-connected server that is "adjacent" to Db2 for z/OS is an application server, versus a Db2 Connect gateway server. The direct connection approach also tends to make Db2 client configuration and upgrade work more straightforward.
Note my mention that this Db2 Connect gateway versus direct Db2 for z/OS connection matter is relevant for DRDA requester applications. It is not pertinent to clients that utilize the REST interface to Db2 for z/OS, as such interactions do not involve the DRDA protocol. See below for more information about Db2 REST clients.
Greater use of the Db2 for z/OS REST interface (which is part of DDF functionality)
By "greater use" I don't mean to suggest that the REST interface to Db2 for z/OS is somehow "better" than the DRDA interface (which I like to call the SQL interface to Db2, as a DRDA requester application issues Db2-targeting SQL statements). The REST interface is different versus the SQL interface, and sometimes that difference makes it a good choice for a Db2-accessing client-server application. I wrote a blog entry a few years ago with a lot of compare-and-contrast information about the REST and SQL interfaces to Db2, and I won't repeat all that here. To Db2 for z/OS DBAs, I'd say this: 1) make sure your application developers know that Db2 has a built-in REST interface, and 2) be ready to help support use of the REST interface when that is the choice of a development team. Sometimes, developers - even those who have strong SQL skills - have a preference for the REST architectural style, often because it so fully abstracts the particulars of service-providing systems.
If you do make use of Db2's REST interface, and think you might expand on that in the future, consider what IBM z/OS Connect could do for your organization. When Db2 for z/OS is accessed through z/OS Connect, it's still Db2's REST interface that's being used (Db2 in that case is a REST provider to z/OS Connect), but z/OS Connect provides some important benefits: it makes creation of z/OS-based REST services easier, it provides richer "service discovery" information to client application developers, it adds flexibility to the formatting of service-output JSON documents, and it provides a single access point through which all manner of z/OS-based programmatic assets can be invoked through REST requests - not only Db2 SQL statements and stored procedures, but also CICS and IMS transactions (which might or might not involve access to Db2) and JES batch jobs.
Leveraging SQL procedure language (SQL PL)
SQL PL is for Db2 (for z/OS and for Linux/UNIX/Windows) what T-SQL is for SQL Server and what PLSQL is for Oracle - a way to write data-processing programs using only SQL statements. SQL PL makes that do-able via a set of SQL statements called control statements - "control" being short for "logic flow control." Among these statements are ITERATE, WHILE, GOTO, IF and LOOP - you get the idea. There are all kinds of reasons for using SQL PL, one being related to cost-of-computing: when a SQL PL routine (such as a native SQL procedure) is invoked through Db2's distributed data facility - whether through a SQL CALL issued by a DRDA requester or via a REST request - its execution is up to 60% offload-able to zIIP engines (mainframe processors that cost less than general-purpose processors and do not factor into the determination of z/OS software charges).
Besides the economic advantage of SQL PL for DDF-using applications, there are functional advantages. For example, a native SQL procedure (a stored procedure written in SQL PL) - and only a native SQL procedure - can be created (or altered) with the AUTONOMOUS option, which means that if the calling transaction fails and is rolled back by Db2, the data-changing actions (e.g., INSERT/UPDATE/DELETE) performed by the autonomous procedure will not be rolled back (this can make autonomous procedures very useful for "transaction initiation audit trail" purposes - you can use an autonomous procedure to record the fact that a transaction got started, and that information will be preserved even if the transaction ends up failing). SQL PL routines can also accept Db2-defined arrays as input, whereas external Db2 routines (written in languages such as COBOL) cannot.
Something else to consider: if you're using SQL PL only for stored procedure programs, you're missing out. SQL PL can also be used to write user-defined functions, and a SQL PL routine can be included in the body of an advanced trigger (advanced triggers were introduced with Db2 12 for z/OS).
And, you should take note of how the CREATE OR REPLACE PROCEDURE syntax introduced with Db2 12 function level 507 can enable greater agility when it comes to deploying Db2 stored procedure programs, especially those written in SQL PL.
And, you should be managing SQL PL source code (that would be, in the case of native SQL procedures, the associated CREATE PROCEDURE statements) with a source code management (SCM) tool - the SYSROUTINES table in the Db2 catalog is not intended to be a SQL PL SCM. Which SCM? Whichever one(s) your organization's developers use to manage their source code - that could be a vendor-supplied SCM or an open-source tool such as Git.
Using the right (no-charge) Db2 SQL development tool
If you (or others in your organization) are using IBM Data Studio for Db2 for z/OS SQL testing and for SQL PL routine development and debugging, it's time for a change. IBM's strategic replacement for Data Studio is the (also no-charge) Db2 for z/OS Developer Extension for Visual Studio Code (also available for the Eclipse Theia IDE).
Leveraging the Db2 profile tables
The Db2 for z/OS profile tables - SYSIBM.DSN_PROFILE_TABLE and SYSIBM.DSN_PROFILE_ATTRIBUTES - can be very helpful for a DRDA requester application workload. For one thing, they can be used to specify application-specific limits on concurrent DBAT (DDF thread) usage and/or connections established with a Db2 system and/or idle thread time - handy when the system-wide DBAT, connection and idle thread limits established via the ZPARM parameters MAXDBAT, CONDBAT and IDTHTOIN are not as granular as you need them to be. The Db2 profile tables can also be used to set the value of a number of Db2 special registers and/or built-in global variables, automatically when an application connects to the Db2 system. One example of this kind of profile table usage is setting the value of the CURRENT PACKAGE PATH special register to point a DRDA requester application to a collection in which the IBM Data Server Driver packages are bound with RELEASE(DEALLOCATE), as a means of getting high-performance DBAT functionality for the application.
Db2 for z/OS DBAs being heavily involved in application development
In way too many cases, DRDA requester or Db2 REST interface-using applications are developed with little Db2 DBA involvement, until late in the game when a production deadline is looming and physical implementation of tables is done in a rushed and sub-optimal way. Logical database design may also have happened with little DBA input, with negative consequences down the road. This situation is typically not a result of application developers giving Db2 DBAs the cold shoulder. Rather, my observation has been that some Db2 for z/OS DBAs view developers as a nuisance or an irritant - as "them." Wrong mindset. Way wrong. Db2 for z/OS DBAs maximize the value they deliver to an organization when they team with developers at the very early stages of an application development project. Not only can that help to ensure a logical and physical database design that will deliver optimal benefits for the application (and for application users), it also provides an opportunity for DBAs to ensure that developers are aware of Db2 features - temporal data support, transparent archiving, the REST interface, autonomous native SQL procedures, non-traditional data types (e.g., XML), global variables, newer built-in functions (e.g., LISTAGG), advanced triggers, whatever - that could enable and accelerate development of functionality of importance for an application. My advice is for Db2 for z/OS DBAs to think of themselves as part of the extended development team for Db2-accessing applications. That approach can be especially effective for modern Db2 client-server applications.
I hope that the information in this blog entry will be useful for you. As always, thanks for stopping by.
