Tuesday, July 26, 2022

What Should a Modern Db2 for z/OS Client-Server Application Environment Look Like?

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.

Sunday, June 26, 2022

Db2 for z/OS: The Online Path from a Partition-by-Growth Table Space to Partition-by-Range

Last month, I posted to this blog an entry on the long-awaited capability to migrate tables, in an online way, from a multi-table segmented or simple table space to multiple single-table partition-by-growth table spaces (a capability delivered with function level 508 of Db2 12 for z/OS). This month, I'll describe an even newer Db2 feature that also enables online migration of tables from one table space type to another. This feature, introduced with function level 500 of Db2 13 for z/OS, allows a DBA to migrate a table from a partition-by-growth table space to a partition-by-range table space with an ALTER TABLE statement and a subsequent online REORG. Read on to learn more.


The motivation

When the universal partition-by-growth (PBG) table space type was introduced with Db2 9 for z/OS, the response among a lot of DBAs was very enthusiastic, and there was good reason for this: it enabled a table to grow beyond 64 GB in size without having to be range-partitioned. Range-partitioning a table, after all, requires a good bit of up-front analysis. What should be the table's partitioning key? How many partitions should the table have? What should be the limit key value for each partition? By contrast, a PBG table space has more of a "set it and forget it" quality - you just determine the appropriate DSSIZE value for the table space (the maximum size for a partition of the table space), and a maximum number of partitions (the MAXPARTITIONS specification - easily changed at a later time if need be), and you're done. If the table space's DSSIZE value is, for example, 16G (i.e., 16 GB), when partition 1 reaches that size then Db2 will automatically add a second partition for the table space, and when that one hits 16 GB then a third partition will be added by Db2, and so on. Easy.

Ah, but there came to be some "buyer's remorse" at more than a few Db2 for z/OS sites as certain PBG table spaces got larger and larger. Why? Because the larger a table gets the more advantageous it can be to have the table in a partition-by-range (PBR) table space. I described these PBR versus PBG advantages (for large tables) in an entry I posted to this blog a few years ago. They include potentially greater (maybe much greater) insert throughput, thanks to the ability to have multiple row-inserting processes execute concurrently for different partition of the PBR table space; great suitability for data rows managed on a time-series basis; and maximum partition-level utility independence. Here was the dilemma, though: prior to Db2 13, the only way to get a table from a PBG to a PBR table space was to unload the table, drop the table, re-create the table in a PBR table space, and reload the table's data. You had, then, this irritating situation: the advantages of PBR versus PBG would be more pronounced as a table got larger, but changing from PBG to PBR was more challenging as a table got larger, due to the unload/drop/re-create/re-load requirement.

Enter Db2 13, and this situation changes, big-time.


The Db2 13 difference

Let's say you have table BIGTAB in a PBG table space, and you'd really like for BIGTAB to be in a PBR table space. In a Db2 13 system (Db2 13 became generally available on May 31 of this year), with function level 500 (or higher) activated, you can issue the following SQL statement (I have highlighted the new syntax in green, and I am assuming that the ACCT_NUM column of BIGTAB is the desired partitioning key):

ALTER TABLE BIGTAB
  ALTER PARTITIONING TO PARTITION BY RANGE (ACCT_NUM)
    (PARTITION 1 ENDING AT (199),
     PARTITION 2 ENDING AT (299),
     PARTITION 3 ENDING AT (399),
     PARTITION 4 ENDING AT (MAXVALUE));


That ALTER is a pending change. When an online REORG is subsequently executed for BIGTAB's table space, coming out of that online REORG the BIGTAB table will be in a PBR table space. Done. The table will have the same indexes that it had before, and it'll be immediately available for access by users and programs.


Some additional information, and considerations

Here are a few things to keep in mind:

  • The partitions of the new PBR table space will initially have the same DSSIZE as the PBG table space that's being migrated to PBR, and that's kind of important. Here's why: when you're determining the partitioning scheme for the new PBR table space you need to consider whether all the rows that belong to a given partition (per the partitioning scheme of the PBR table space) will fit in the partition, given the DSSIZE. Suppose, for example (and continuing with the BIGTAB table referenced previously), that the DSSIZE value for BIGTAB's PBG table space is 4G, and the number of rows in BIGTAB with an ACCT_NUM value greater than 199 and less than or equal to 299 (i.e., rows that would go into partition 2 of the new PBR table space) will not fit into a 4 GB data set. In that case the online REORG after the ALTER will fail. To avoid that failure, you'd need to either change the partitioning scheme so that the rows assigned to a given partition will fit in a 4 GB data set, or change the DSSIZE value of BIGTAB's PBG table space to something larger than 4G. If you decide on the latter action (increase the DSSIZE value for BIGTAB's PBG table space), understand that you'll need to issue that ALTER for the table space (to go to a large DSSIZE value) and then execute an online REORG to materialize that change and then issue the ALTER to change from PBG to PBR and execute another online REORG to materialize that pending change. Why two online REORGs? Because, when you take action to change a PBG table space to PBR the online way, there can't be any other outstanding (i.e., not yet materialized) pending changes for the PBG table space - the change to PBR has to be the only pending change for the PBG table space.
  • The new PBR table space will use relative page numbering (RPN), which was introduced with Db2 12 for z/OS. This is a very good thing. To see why, check out the blog entry I wrote about RPN a couple of years ago.
  • As is very often the case when a pending change is materialized, the online REORG that changes a PBG table space to PBR will invalidate packages dependent on the associated table. You can identify those dependent packages by querying the SYSPACKDEP table in the Db2 catalog.
  • This ALTER + online REORG route from PBG to PBR is not available for a table that has an XML or a LOB column.

A closing thought

If you are on Db2 12 and you have some PBG table spaces that you'd like to change - easily, and in an online way - to PBR, the enhancement I've described herein could be a good reason for getting your Db2 13 migration project going.

Monday, May 30, 2022

Db2 for z/OS: The Online Path from a Multi-Table Table Space to Universal Table Spaces

Back in 2014, I posted an entry to this blog on the topic of getting to universal table spaces from non-universal table spaces. In that entry, I noted that there was an online path (ALTER followed by online REORG) for getting from a "classic" partitioned table space to a universal partition-by-range (PBR) table space, and for getting from a single-table simple table space or traditional segmented table space to a universal partition-by-growth (PBG) table space. I also pointed out that no such online path to universal table spaces existed for a multi-table table space: "For a simple or segmented table space containing multiple tables, you'd have to either go the unload/drop/create/re-load route (with one PBG universal table space created for each table in the multi-table simple or segmented table space), or wait and see if a future release of Db2 provides a non-disruptive universal table space conversion process for multi-table simple and segmented table spaces (this is a known requirement)." The wait for the hoped-for Db2 enhancement ended in October of 2020, when Db2 12 function level 508 became available (via the fix for APAR PH29392). In this blog entry, I will describe how a capability introduced with Db2 12 function level 508 enables online migration of tables from multi-table table spaces to universal PBG table spaces.

For illustrative purposes, let's say that you have a traditional segmented table space containing four tables (I say, "traditional segmented table space" because universal table spaces are also segmented). The tables are named T1, T2, T3 and T4. You have function level 508 (or later) activated on your Db2 12 system (or you have a Db2 13 system). How do you get tables T1, T2, T3 and T4 from the traditional segmented table space into universal PBG table spaces, in an online way? Here's how:

  • First, create a table space for each of the tables T1, T2 and T3 (I'll get to T4 momentarily). Here's the form of the CREATE TABLESPACE statement you should use for this purpose (let's assume that the new table spaces will be named TS1, TS2, etc.):

CREATE TABLESPACE TS1
  IN dbname
  ...
  MAXPARTITIONS 1
  DEFINE NO
  DSSIZE xxx;

Note: the database for each of these new PBG table spaces will need to be the same as the database of multi-table table space of interest (similarly, the CCSID of each of the new table spaces will have to be the same as the CCSID of the multi-table table space of interest). Also, MAXPARTITIONS 1 is required, at least initially (you can change the MAXPARTITIONS value later if desired). DEFINE NO is also required (the table space data sets will be created by Db2 later). DSSIZE can be any value appropriate for the tables that will be moved to the new table spaces (consider that 64G would work for any table, since a traditional segmented table space cannot exceed 64 GB in size).

  • Next, issue the following ALTER statement for the multi-table traditional segmented table space (the new ALTER TABLESPACE option introduced with Db2 12 function level 508 is highlighted in green):

ALTER TABLESPACE dbname.source-table-space-name
  MOVE TABLE T1 TO TABLESPACE dbname.TS1;

Note that this is a pending change for the source table space - the actual table move will be effected via a subsequent online REORG of the source table space, as explained below. The statement above would be executed as well for tables T2 and T3 (I haven't forgotten about table T4 - I'll get to that). Keep in mind that, starting with Db2 12, the APPLCOMPAT package bind specification applies to DDL as well as DML statements. What does that mean for the ALTER TABLESPACE statement shown above? It means that the package through which the ALTER is issued (e.g., a DSNTEP2 package, or a SPUFI package) needs to have an APPLCOMPAT value of V12R1M508 or higher.

  • OK, online REORG time. An online REORG executed for the source table space (the one in which tables T1, T2, T3 and T4 had been located) will cause each table for which an ALTER TABLESPACE with MOVE TABLE has been executed to be relocated to its designated PBG table space. When the online REORG has completed, each relocated table will be ready to use - it will have its indexes and everything.
So, what about table T4? You have a choice here. One option would be to do for table T4 what you did for tables T1, T2 and T3: create a new PBG table space for the table, and execute an ALTER TABLESPACE with MOVE TABLE T4. And the other option? Well, consider the situation after you've moved tables T1, T2 and T3 to their respective PBG table spaces. The source table space, which formerly held four tables, now holds only one table: T4. What does that mean? It means that you can alter the source table space with a MAXPARTITIONS value and then online REORG it to convert it to a PBG table space - you've been able to do that for a single-table traditional segmented table space or a single-table simple table space since Db2 10 for z/OS.

Here are a few things to keep in mind with regard to online migration of tables from a multi-table table table space to PBG table spaces:
  • Moving tables from multi-table table spaces to PBG table spaces is likely to mean an increase in the number of data sets for the Db2 system. Given that a table's database can't change when it goes from a multi-table table space to a PBG table space, you'll want to make sure that the number of OBIDs (object identifiers) for the database in question will not exceed the limit of 32,767. A page in the Db2 for z/OS documentation contains a query that you can issue to identify databases that might be getting close to the OBID limit.
  • More data sets could also lead you to increase the size of the DBD cache in the EDM pool (the associated ZPARM parameter is EDMDBDC), and/or to increase the Db2 subsystem's DSMAX value. Regarding the DBD cache, you generally want the ratio of "DBD requests" to "DBD not found" (referring to fields in a Db2 monitor-generated statistics long report, or in an online display of EDM pool activity) to be at least in the tens of thousands to one. As for DSMAX, you usually want that value to be sufficiently high so as to either not be reached (per your Db2 monitor) or so that only a few data sets per hour are closed as a result of hitting the DSMAX limit (again, per your Db2 monitor).
  • An online REORG that materializes a MOVE TABLE pending change will invalidate packages that depend on the table or tables being moved, so plan for rebinding those packages (the SYSPACKDEP catalog table contains information to identify dependent packages).
  • Additionally, an online REORG that materializes a MOVE TABLE pending change will operate on both the source and target table spaces; accordingly, inline image copies will be created for those table spaces. These will establish a recovery base for the objects, but note that after execution of the table-moving online REORG you will not be able to recover the source table space to a point in time prior to the table-moving online REORG.
  • Don't worry if a source table space holds hundreds of tables (as some do in the real world) - there's no requirement that all the tables be moved to PBG table spaces in one fell swoop. You can move a few at a time, no problem. Just keep in mind that an online REORG of the source table space will move every table that has not already been moved and for which there is a pending MOVE TABLE change.
There you have it. Universal table spaces are what you want, and you now have an online way to get there for your multi-table table spaces. Happy moving.

Thursday, April 28, 2022

Db2 for z/OS: Answering Some Questions About DBATs (i.e., DDF Threads)

Sometimes, a DBA will email me a question about Db2 for z/OS, and I'll respond in a pretty comprehensive way, and I'll look at that outbound message and think to myself, "Hmm. I may have written most of a blog entry there." This is one of those cases. I recently got a couple of questions about Db2 database access threads, or DBATs (threads associated with access to a Db2 subsystem through the distributed data facility, aka DDF), and I think the questions and answers might be of interest to a lot of people in the Db2 community. I am therefore packaging them in this blog entry. Off we go:


Question: When does a DBAT go from being active to idle, so that it is subject to the idle thread timeout setting for the Db2 subsystem?

Answer: OK, the first thing to understand here is that a DBAT is always active. Even when it's in the DBAT pool, a DBAT is active - it's just in a so-called disconnected state. A DBAT is idle when it is in-use (i.e., paired with a connection, which happens when a transaction comes along by way of that connection) and it’s not doing anything (or, more accurately, the transaction that was using the DBAT appears to Db2 to be doing nothing). It's normal for there to be some idle thread time for a DDF transaction - a client-side program issues a SQL statement, the result is sent back to that program, and the DBAT is briefly idle until the transaction's next SQL statement is issued. No big deal there. It's when idle time becomes really elongated that a DBAT might be affected by the Db2 subsystem's idle thread timeout value. That timeout value is specified via the IDTHTOIN parameter in the Db2 DSNZPARM module. The default value for IDTHTOIN is 120 seconds (check out the value on your Db2 system, and see if it's set to something other than 120).

Normally, at end-of-transaction there is a commit, and at that time the DBAT that had been used in processing the transaction goes back to the DBAT pool and the connection with which the DBAT had been paired goes back to an inactive state (inactive connections, a key contributor to Db2's connection scalability, are a server-side thing, invisible to a connected application - an inactive connection will go back to an active state when the next transaction associated with the connection begins). Can a DBAT in the pool be affected by the Db2 system's idle thread timeout value? No, but it is subject a limit specified by another ZPARM parameter called POOLINAC (more on that to come).

Let's say that a DDF transaction starts but then never commits. That could happen because of a problem on the client application side, or it could be that the developer of the transaction program decided that a commit is not necessary because the transaction is read-only in nature (that in fact would be a not-good decision - every DDF transaction needs to commit, because even a read-only transaction will hold one or more table space or partition locks and one or more claims on database objects, and those locks and claims will not be released without a commit). Because the transaction has not committed, it is perceived by Db2 to be still in-flight, and for that reason the transaction's DBAT can't be separated from the associated connection and returned to the DBAT pool. The apparently (to Db2) in-flight transaction continues to do nothing, and the related DBAT remains idle for a longer and longer period of time. Eventually the IDTHTOIN limit will be reached for the idle thread (unless IDTHTOIN is set to 0, which means a DBAT can remain indefinitely idle), and Db2 terminates the DBAT and the associated connection.

So, to recap: first, a DBAT does not go from active to idle, because a DBAT is always considered to be active - it's an in-use DBAT, as opposed to an in-the-pool DBAT, that can be idle. Second, an in-use DBAT will typically have at least some idle time (time when it seems to Db2 that the transaction associated with the DBAT is not doing anything - or, at least, not doing any SQL-related thing); it's when that "nothing SQL-related is happening" time gets long that the Db2 idle thread timeout limit can be reached for a DDF transaction and its DBAT.

[By the way, I mentioned earlier that when Db2 terminates a DDF transaction and its DBAT due to the idle thread timeout limit being reached, Db2 also terminates the connection with which the DDF transaction had been associated. If you'd prefer for Db2 to preserve the connection while terminating the transaction and the DBAT, you can get that behavior thanks to an enhancement introduced with Db2 12 for z/OS. The enhancement is enabled via specification of EXCEPTION_ROLLBACK as an attribute of a MONITOR IDLE THREADS row in the Db2 table SYSIBM.DSN_PROFILE_ATTRIBUTES. You can find more information about this enhancement in the Db2 for z/OS online documentation, at https://www.ibm.com/docs/en/db2-for-zos/12?topic=threads-monitoring-idle-by-using-profile-tables.]


Question: We had a DDF transaction surge, and as a result the number of DBATs went way up. The surge passed, and several minutes later I checked on the number of DBATs and it was still way high. What's with that?

Answer: There were (I'm pretty sure) two factors involved here. First, the POOLINAC value. That's a ZPARM parameter. If a DBAT in the pool has gone a POOLINAC number of seconds without being reused for a transaction, that DBAT will be subject to termination by Db2. For the Db2 subsystem looked after by the DBA who asked me this question, the POOLINAC value was 900 seconds, considerably higher than the default value of 120 seconds (I personally favor setting POOLINAC to the default value of 120 and leaving it there unless there's a good reason to make a change). A high POOLINAC value will definitely slow down the trimming of the number of pooled DBATs after the passing of a DDF transaction surge, but I think something else was going on, as well.

That "something else" was a change in Db2's termination of "too-long-in-the-pool-without-reuse" DBATs, effected by the application of the fix for Db2 APAR PH36114 (that fix came out in June of 2021, and I say that "I think" the fix was involved in this situation, rather than "I know," because I did not verify that the fix was applied to the Db2 subsystem in question - I'm inferring that based on the behavior reported by the DBA). Here's the deal: prior to the change associated with PH36114, Db2 would check the DBAT pool every two minutes to see if any DBATs in the pool had been there for a POOLINAC number of seconds without being reused. However many "too-long-in-the-pool-without-reuse" DBATs were found, were terminated by Db2. If a lot of DBATs went back to the pool at around the same time following the rapid subsidence of a DDF transaction surge, Db2 might find in a subsequent pooled DBAT purge cycle that a lot of DBATs needed to be terminated at one time due to the POOLINAC limit being exceeded. With the ZPARM parameter REALSTORAGE_MANAGEMENT set to AUTO or ON (and AUTO is the default), terminating a lot of DBATs at one time could put a good bit of pressure on the z/OS LPAR's ESQA resource, which in turn could cause spill-over into ECSA, which in turn could be bad news for an LPAR with only a small cushion of unused ECSA space.

With the fix for PH36114 applied, two things changed in this picture:
  1. Db2 went from checking every two minutes for DBATs with "too-long-in-the-pool-without-reuse" status to doing that every 15 seconds.
  2. In a given purge cycle (again, now once every 15 seconds), Db2 will terminate a maximum of 50 DBATs in the "too-long-in-the-pool-without-reuse" category.
What this means: a big pile of pooled DBATs left over from a since-passed DDF transaction surge will be worked down more frequently and more gradually. That could somewhat elongate the process of finalizing the right-sizing of the DBAT pool for a now-back-to-normal volume of DDF transactions, but it will avoid the pressure on ESQA that could result from the more-aggressive purging of "too-long-in-the-pool-without-reuse" DBATs that Db2 did prior to the PH36114 fix. It's a good trade-off, in my opinion.

Maybe you now know a few things about DBATs that you didn't know before. Thanks for visiting the blog, and I hope you'll return sometime.

Wednesday, March 9, 2022

Thoroughly Assessing Data Security in a Db2 for z/OS Environment - Part 2

In part 1 of this two-part blog entry on thoroughly assessing data security in a Db2 for z/OS environment, I covered four aspects of Db2 data protection: privilege management, client authentication, data encryption and column masks/row permissions. In this part 2 entry we'll take a look at auditing, application architecture, test data management and RACF (or equivalent) management of Db2-internal security.

Off we go:

  • Auditing - You can manage Db2 privileges in a careful and responsible way, but at the end of the day users have to have some privileges in order to perform assigned duties, and some users are going to require extensive privileges. Privileges that were properly granted can be abused. The best defense against that possibility is effective auditing of users' data access activities. What you want in this case is to identify improper data access activity so that steps can be taken to shut it down. An important assist in this area was delivered with Db2 10 for z/OS, which introduced audit policy functionality. Db2's audit policy capability enables you to monitor (among other things):
    • Occurrences of access actions that failed due to inadequate authorization (if a particular user is getting a pretty good number of these, that could indicate attempts to probe for "holes" in your organization's data protection measures).
    • Occurrences of a user changing his or her SQL ID (there are times when this is a legitimate action, and times when it is not)
    • Occurrences of tables being altered (depending on the ALTER action, this could be an attempt to circumvent a data protection measure).
    • Occurrences of a particular table being accessed in read or data-change mode (is a table holding sensitive data values being accessed at odd hours?).
    • Utility execution (could someone be trying to use a Db2 utility as a "back door" means of data access?).
    • Incidences of privileges being granted or revoked (inappropriate granting of Db2 privileges can be a warning sign).
    • Use of system administration "super-user" privileges: install SYSADM, install SYSOPR, SYSOPR, SYSCTRL, or SYSADM (to quote a line from several "Spider-Man" movies: "With great power comes great responsibility").
    • Use of database and security administration "super-user" privileges: DBMAINT, DBCTRL, DBADM, PACKADM, SQLADM, system DBADM, DATAACCESS, ACCESSCTRL, or SECADM (see the "Spider-Man" quote above).

Db2 12 function level 509 introduced an important audit policy enhancement: tamper-proof audit policies. With that enhancement, an audit policy can be set up so that it can be changed only with the permitting action of a person outside the Db2 team (specifically, a RACF administrator).

You can read all about developing, activating and using audit policies in the Db2 for z/OS online documentation.

If you're interested in a data access monitoring solution that can span all of your enterprise's data stores, both on-prem and cloud-based, check out IBM Security Guardium Data Protection.

  • Application architecture - Can application architecture enhance Db2 data protection? Absolutely. Consider, for example, the security advantage of static versus dynamic SQL statements. If an application process will access data in table T1 via a dynamic query, the authorization ID of the application process will require the SELECT privilege on T1. If, on the other hand, the application process will access data in T1 by way of a static query, the application's ID will not need any table access privileges; instead, the ID will need only the EXECUTE privilege on the Db2 package associated with the static query. Reducing the granting of table-access privileges to application and/or user IDs can strengthen Db2 data security.
OK. But what about applications of the client-server variety, particularly those that access Db2 for z/OS data from network-connected Linux or UNIX or Windows servers? For such applications, use of client-issued static SQL statements is often either not possible or, if possible, not favored by client-side programmers (for example, a Java program can issue static SQL statements in SQLJ form, but in my experience Java programmers overwhelmingly prefer JDBC to SQLJ, and JDBC means dynamic SQL on the Db2 side). In those cases, two convenient ways to utilize static SQL are 1) Db2 stored procedures and 2) Db2 REST services.

Lots of application developers who prefer the JDBC and ODBC forms of SQL (to name two very popular forms of non-DBMS-specific SQL) are plenty happy with a stored procedure approach, as stored procedures are widely used with relational database management systems such as Db2. A programmer can use (for example) JDBC statements to call Db2 stored procedures and to retrieve rows from associated query result sets (when a stored procedure declares and opens a cursor). The stored procedure calls will be dynamic on the Db2 side, but the "table-touching" SQL statements issued by the stored procedures will be static, and that means that the application's ID will require only the EXECUTE privilege on the called stored procedures - not table access privileges.

Db2's built-in REST interface is another way to make static SQL easy to use from a client-side programmer's perspective. By way of this interface, which is an extension of the Db2 distributed data facility, a static SQL statement can be invoked via a REST request. The static SQL statement associated with a Db2 REST service can be a SELECT, an INSERT, an UPDATE, a DELETE, a TRUNCATE or a CALL (of a stored procedure).

Here's another security advantage of client-side programs invoking Db2 server-side static SQL statements, whether through the use of stored procedures or the Db2 REST interface (which can itself be used, as noted, to invoke stored procedures): when this approach is used, client-side programmers do not have to know anything about table or column names - that knowledge is needed only by the people who code the server-side static SQL statements. How does this shielding of database schema information enhance data security? Well, the fewer the people who know stuff like table and column names, the less likely it is that a database will be hacked by bad guys.

[Note: when a stored procedure is to be invoked through the Db2 distributed data facility, either through a SQL call or a REST request, that stored procedure will get up to 60% zIIP offload when executed IF the stored procedure is written in SQL PL (i.e., if it is a so-called native SQL procedure). A stored procedure written in a language other than SQL PL will get little to no zIIP offload when called through DDF.]

  • Test data management - Let's say you have a production Db2 database in which some sensitive data values are stored (e.g., credit card numbers). Perhaps you have taken a number of steps to protect those sensitive data values. Great. But now an application team wants tables in their Db2 development environment populated with data from the production system. You could copy data over from the production to the development system, but will the sensitive data values be protected in the development environment as they are in production? Even if the data protection measures in the development environment are as strong as those in place for the production Db2 system, creating another copy of data that includes sensitive data values will still involve some data-security risk because the data copy increases what a security auditor might call the "threat area" - do you want to accept that risk?
Often, the best approach to use in this situation is to mask or otherwise obscure the sensitive data values before (or as part of) copying production data to a development or test system. How would you accomplish that? You could do it on your own, but that can be a time-consuming effort and the "roll-your-own" data masking could impact the CPU and elapsed times of a production-to-test data copy operation. An easier (and often better-performing) way to get this done would be to use a software tool designed for the purpose. Two options in this space that are available from IBM are IBM InfoSphere Optim Test Data Management Solution for z/OS and IBM Db2 Cloning Tool for z/OS (the former is useful for copying a referentially complete subset of data rows from one Db2 system to another, while the latter is more appropriate for copying entire table spaces and indexes - or even an entire subsystem's data - from one Db2 system to another). Both of those tools have data masking capabilities, to prevent sensitive data values from being copied "as-is" from a production environment to a test or development system.

  • RACF (or equivalent) management of Db2-internal security - It is very common for RACF (or an equivalent z/OS security management subsystem) to be used for external Db2 security purposes - that is, to control which application processes and/or users can connect to a Db2 subsystem, and how. Once an application process or a user has successfully connected to a Db2 subsystem, what happens next is a matter of Db2-internal security: does the ID of the application or user have the Db2 privileges needed to (for example) read data in a table, or update data in a table, or create an index or bind a package? In my experience, Db2-internal security is most often managed within Db2 by the Db2 administration team, who use the SQL statements GRANT and REVOKE to provide privileges for, or remove privileges from, various authorization IDs. It is possible to use RACF (or equivalent) to manage Db2-internal security as well as Db2-external security, and a growing number of organizations are doing just that.

I posted an entry to this blog a couple of years ago with a good bit of information about using RACF to manage Db2-internal security. I won't repeat that content here; rather, I'll provide some thoughts and observations on this topic:

    • Why do organizations do this? Quite often (in my experience) it's because someone - perhaps a security auditor - told them that it has to be done. Why might that pronouncement be made? Well, in the minds of many security people, it's a good thing for a single group of people to manage all aspects of security for a database management system. Because RACF can be used to manage both Db2-external and Db2-internal security, while Db2's security features apply mainly to internal security, if one team is going to manage all aspects of Db2 for z/OS security then it's going to be the RACF team.
    • Db2 11 for z/OS eliminated what were just about the last two hassles that were formerly associated with RACF management of Db2-internal security. Prior to Db2 11, auto-rebinds could fail with authorization errors when RACF was used to manage Db2-internal security. Why? Because when an auto-rebind occurs you generally want Db2 to do that based on the privileges held by the ID of the owner of the package. It used to be that when RACF managed Db2-internal security, the authorization check for an auto-rebind looked at the privileges held by the ID of the application process that prompted the auto-rebind by requesting execution of a package that had been marked invalid by Db2, and that ID rarely has the privileges needed for a successful auto-rebind. Db2 11 fixed that problem by enabling RACF to check the ID of a package owner for auto-rebind authorization. The other nagging problem fixed by Db2 11 concerned caches of authorization information that Db2 maintains in memory. Information in those caches was formerly not updated to reflect security changes effected through RACF, the result being a frustrating lag between some RACF-side changes and enforcement of same in Db2. Db2 11 fixed that problem by having Db2 listen for ENF signals (referring to the z/OS event notification facility) sent by RACF when authorization changes are made.
    • Organizations that have gone to RACF management of Db2-internal security are pretty happy with the arrangement, and that includes the Db2 for z/OS DBAs. Yes, there is a good bit of set-up work involved in making this transition, and that can seem more challenging than it really is because Db2 DBAs and RACF administrators speak different languages in a technical sense, but once things are set up and the transition has been completed, people find that it really works as advertised. Ask a Db2 DBA at a site that has gone to RACF management of Db2-internal security if he or she is OK with the change, and you'll likely get a thumbs-up. I haven't found many (any, actually) DBAs in these organizations that pine for the days when they had to issue GRANTs and REVOKEs to manage Db2-internal security. Letting the RACF team handle Db2-internal security lets the DBAs focus on database administration tasks (e.g., performance tuning, application enablement) that they generally find to me more satisfying.
OK, that's what I've got. Consider these areas, and those I covered in part 1 of this two-part entry, and I think you'll be able to comprehensively evaluate the Db2 security set-up you have at your site. I hope that this information will be helpful for you.

Thursday, February 24, 2022

Thoroughly Assessing Data Security in a Db2 for z/OS Environment - Part 1

I regularly get questions from Db2 for z/OS people that pertain to data security. Most of the time, these questions have a pretty narrow focus - a DBA, for example, wants to know more about Db2 roles and trusted contexts, or about SECADM authority, or about "at-rest" encryption of Db2 data on disk. Recently, I had a meeting with some people from a client's mainframe IT staff, and they wanted to know what a comprehensive Db2 for z/OS data security review would look like. For me, that was a refreshingly wide-scope question. What areas would one want to examine, if one wanted to thoroughly assess the data security posture of a Db2 for z/OS system? In this part one of a two-part blog entry I will give you my take on the matter, starting with four areas of Db2 data protection: privilege management, client authentication, data encryption, and column masks and row permissions. In the part two entry, which I hope to post in about two weeks, I will cover four other areas of Db2 data protection: auditing, application architecture, test data management and RACF (or equivalent) management of Db2-internal security.

Onward, then, to the first four areas of Db2 data protection I would recommend considering as part of a comprehensive Db2 security assessment:

  • Privilege management - This is about the Db2 privileges that have been granted to user, group and application IDs. With regard to user privileges, the best-practice approach is to grant to a given individual only those privileges that minimally enable the individual to do his or her job. One area where there has been a lot of cracking down in recent years concerns the granting of SYSADM authority. That's basically super-user status, and years ago it was common for organizations to give the SYSADM authority level to quite a few people on the Db2 support team. Why did that practice become problematic? Mainly because someone with SYSADM authority can look at (and even change) the data in any table. You could say, "It's OK - no one on my Db2 team is a bad actor," but that argument is not likely to sway security auditors these days. For some organizations, the solution to over-granting of SYSADM is to change that authority, for many or even most of the people on the Db2 team, to DBADM WITHOUT DATAACCESS ON SYSTEM. That move can spark protest from someone who loses SYSADM authority, but in fact many DBAs can do the large majority of things they need to do with system DBADM authority. If access to data in a specific table is required for a DBA with system DBADM WITHOUT DATAACCESS authority, the SELECT privilege on the table can be granted and then revoked when the task requiring access to the table's data has been completed.

Here's another way in which super-user authority has been reigned in lately: organizations can set the value of the Db2 ZPARM parameter SEPARATE_SECURITY to YES. What does that do? It removes from SYSADM authority the ability to create and manage security objects (e.g., roles, trusted contexts, row permissions and column masks), and the ability to grant privileges to others (unless the ID with SYSADM authority holds the privilege in question WITH GRANT OPTION, or owns the object on which a privilege is being granted). How do those things get done, if a SYSADM can't do them? They are done by an ID with SECADM authority (more information in this area can be found in an entry I posted to this blog last year).

What about the group IDs that I mentioned above? Those have been around for a long time. A group ID is so called because it refers to a RACF (or equivalent z/OS security manager) group to which individual IDs can be connected. When the Db2-supplied sample connection and sign-on exits are used by an organization (very widely done), the RACF group IDs to which your primary authorization ID are connected become your secondary authorization IDs in Db2, and the ability to execute most Db2 SQL statements and commands depends on the privileges held by your primary authorization ID and by any of your secondary authorization IDs. This can make Db2 privilege management much simpler, especially if a set of privileges tailor-made to enable execution of a certain set of Db2 actions is fairly complex - you just grant that set of privileges to a RACF group ID, and then connect to that group ID the IDs of individuals who need to perform the associated set of Db2 actions.

Some security administrators and auditors worry - understandably - about the privileges granted to the ID of an application that issues dynamic SQL statements, especially when that application connects to Db2 via TCP/IP communication links and through the Db2 distributed data facility (DDF). Why the worry? Well, for a dynamic SQL statement, such as a SELECT, to execute successfully, the associated Db2 authorization ID needs to have the SELECT privilege on the target table. A DDF-using application that issues SQL statements in JDBC or ODBC form (these will be dynamic SQL statements on the Db2 side) usually connects to the Db2 system using a certain ID and an associated password. What if someone who knows that ID and password tries to use those credentials to connect to the Db2 system from a PC, and then view data in tables that the application can access? An effective defense against that scenario can be implemented using Db2 roles and trusted contexts, as described in a blog entry I posted a few years ago.

  • Client authentication - I just now referred to applications that access Db2 systems via TCP/IP communication links and through the Db2 distributed data facility, and there are often individual users that do the same thing, perhaps using a workstation-based query and reporting tool. How are these applications and users authenticated at connection time? As noted above, this is usually done by way of a password. Typically, an organization requires a user to change his or her password on a regular basis - for example, every three months. What about the password associated with an application's ID? There was a time when it was quite common for such a password to be of the "never expire" type. That kind of password is increasingly deemed unacceptable by security auditors, who insist that the password associated with an application's ID be regularly changed, just as is done for passwords associated with user IDs. That is in fact a good policy from a security perspective, but it can lead to authentication-related connection errors when an application's password is changed. What if an application's password is changed in RACF before it is changed on the app server side, or vice versa? The strategy I've seen employed for non-disruptively changing a Db2 client-server application's password involves having two IDs for a given application. Shortly before the password for ID1 is set to expire, the application starts connecting to Db2 using ID2 (whose password will be good for the next three months or whatever). Once all instances of the application have switched over to ID2, the password for ID1 can be updated (and maybe that doesn't happen until a little before ID2's password is set to expire). As long as the application is running on at least two app servers, the switch from the one ID to the other can be accomplished with no application outage. What makes this work on the Db2 side is the fact that all privileges needed for the application's dynamic SQL statements to execute successfully are granted to ID1 and to ID2.

What if you'd prefer for an application or a user ID to be authenticated by some means other than a password? Are there alternatives? Yes. One alternative is to use RACF PassTickets - you can find more information about RACF PassTickets in the online z/OS documentation. Another option is to use certificate-based authentication. Often, when one thinks about certificates in a Db2 for z/OS context, it is in relation to SSL encryption for communication between Db2 and a network-connected client application (more on encryption to come - see below); however, in my experience Db2 SSL encryption typically involves use of a server certificate versus client certificates (the host certificate is transmitted to the client system and is presented at connection time by the client application - the host recognizes its own certificate and the "SSL handshake" can proceed to successful completion). That said, it is also possible for a client system to present its own certificate as a means of authentication when requesting a connection to a Db2 system. A good source of information on certificate-based authentication for a Db2 client is a document titled, "Db2 for z/OS: Configuring TLS/SSL for Secure Client/Server Communications," which can be downloaded from the IBM redbooks Web site. Refer to the information under the heading, "Client access to Db2 using TLS/SSL client authentication," on page 61 of the document.

One other thing regarding client authentication. There is a parameter in the Db2 DSNZPARM module called TCPALVER. The default value for that parameter is NO, and that is almost certainly the value you want. If TCPALVER is set to YES in your Db2 environment, it means that Db2 assumes that a process wanting to connect to the Db2 system via TCP/IP is already verified. That being the case, the Db2 system will accept a TCP/IP client connection request that provides an ID but no authentication credential - no password or PassTicket or client certificate is required. Now, if you see that TCPALVER is set to YES for a Db2 subsystem, don't panic - almost certainly, RACF (or equivalent) will block a connection request that lacks an authentication credential; still, in the interest of having "belt and suspenders" security safeguards (a good idea), you'll probably want to change the TCPALVER value from YES to NO in the very near future. Before making that change, consider that a setting of TCPALVER=YES might have been put in place a long time ago, when the only clients connecting to the Db2 system via TCP/IP were other Db2 for z/OS systems. When that kind of communication was first happening (again, a long time ago), a requester Db2 for z/OS system might not have been sending a password when requesting a connection to a server Db2 for z/OS system, the thinking being that user authentication had already happened on the requester Db2 for z/OS side. If you have Db2 for z/OS systems communicating with other Db2 for z/OS systems using DRDA, without passwords being sent, those connection requests will fail after you change the TCPALVER value from YES to NO. To avoid that problem, update the Db2 communications database on the Db2 for z/OS requester side to have a password sent with a connection request to a DRDA server, and THEN change the TCPALVER setting from YES to NO.
  • Data encryption - I posted an entry to this blog in 2020 about data encryption in a Db2 for z/OS context, and I won't repeat that content here. Instead, I'll add a few extra nuggets of information:
    • APAR PH08188 (April of 2019) made it possible to configure a Db2 for z/OS system so that its only SQL listener port is a "secure" port (i.e., one that requires a requester to use SSL encryption). When a Db2 system is set up that way, it is not possible for a client application to establish a non-SSL connection to the Db2 server.
    • Db2's leveraging of the data set encryption feature of z/OS for encryption of Db2 table space and index data "at rest" (i.e., on disk) involves associating an encryption key label with a data set and RACF-permitting use of that key label. This has caused some people to be concerned about the need to provide RACF permission for various encryption ket labels to various application and user IDs. That concern is unfounded. When a user or an application issues a SQL statement that targets a Db2 table, and table space and/or index data sets on disk are accessed as a result, from the z/OS perspective it is not the user or application accessing the data sets - it's Db2 accessing the data sets (and the same is true for the "online" IBM Db2 utilities, such as LOAD and COPY and REORG - they access database objects through Db2). That being the case, only the IDs of the Db2 database services and system services address spaces need to be RACF-permitted to use the key labels associated with encrypted Db2 data sets (if a "standalone" Db2 utility, such as DSN1PRNT, is to be run for an encrypted database object, the ID of that utility job will need RACF permission for the object's key label, as the standalone utilities operate outside of Db2).
    • Plenty of people get data encryption and data masking mixed up. They are two different things (see below)
  • Column masks and row permissions - This is another area I covered pretty thoroughly in a previous blog post, and I won't repeat that content in this entry. What I will do is try to clear up some misunderstandings I've encountered over the years since Db2 introduced column mask and row permission functionality:
    • Data masking and data encryption really are two different things. One difference is that encryption is reversible (if you have access to the encryption key) while a data masking transformation can be irreversible (if, for example, a column mask changes a credit card number to XXXXXXXXXXX1234, there is no way for a user or a program to reverse those X's back to their pre-masked values).
    • A Db2 column mask changes no values in a table; instead, the mask transforms the values in a column before they are returned to a user or a program; so, the actual unmasked values are in the table, but a column mask prevents a user (or a set of users, if it checks for a certain group ID) from being able to see the unmasked values. The masking is accomplished by a SQL CASE expression that is automatically added to a query when the ID (or group ID) of a query-issuing process matches one specified in the CREATE MASK statement.
    • One of the nice things about a column mask is that it doesn't change values in a column of a table. That means a column mask will not affect a query's predicates (including join predicates) - those predicates will be evaluated using the unmasked values in the referenced column. The mask is applied (as previously noted) when the column in question appears in a query's select-list. If the mask changed values in a column, it could really throw query results out of whack in a bad way.
    • Column masks and row permissions really are a very robust way to prevent access to certain data values (a row permission prevents a process with a certain ID or group ID from being able to access particular rows in a table, through addition of a row-filtering predicate to queries issued by an ID that matches one specified in the CREATE PERMISSION statement). Here's what I mean by that: the CASE expression associated with a column mask, and the predicate associated with a row permission, will be automatically added to ANY query - static OR dynamic - issued by ANY ID specified in the CREATE MASK or the CREATE PERMISSION statement. Does your ID (which I'll call SMITH) have SYSADM authority? Doesn't matter - if I have a column mask or a row permission that states that SMITH (or, maybe, any ID other than, for example, XYZ) cannot see certain rows in a table, or unmasked values in a certain column, you're not going to be able to access those rows or those unmasked column values. The Db2 privileges held by your ID are irrelevant.

And that's a wrap for this part 1 blog entry. Check back in a couple of weeks for part 2, which will cover auditing, application architecture, test data management and RACF (or equivalent) management of Db2-internal security.


Thursday, January 20, 2022

Db2 for z/OS: the REST Interface and IN-list Predicates

I've written several blog entries on the REST interface to Db2 for z/OS, including one posted early last year. How does it work? Pretty simple: you first code a SQL statement that you want to make REST-invokable; then, you create a REST service associated with that SQL statement, either by using Db2ServiceManager (a REST service provided by Db2 for creating REST services from SQL statements) or the BIND SERVICE command (which can be issued from a batch job). After that, a client application program can invoke the service by way of a REST request.

Quite often, a REST request that invokes a Db2 SQL statement will have one or more input values associated with it. These inputs provide substitution values for parameter markers or host variables in the REST-enabled statement. For example, suppose a REST service is created from the following SELECT statement to return to a client program some information about an account holder:

SELECT C.FIRSTNAME, C.LASTNAME, A.BALANCE, A.LIMIT
FROM ACCOUNTS A,
CUSTOMERS C
WHERE A.ID = ?
AND A.CUSTNO = C.CUSTNO

The parameter marker in the green-highlighted predicate needs a substitution value at execution time, and the service-invoking client provides that input value in a JSON document that accompanies the REST request:

POST http://mybank.com:4711/services/ACCOUNTS/getBalance
Body: { “ID”: 123456789 }

All well and good, but what about a query that has a predicate that would have a varying number of substitution values from one execution to another? An IN-list predicate is often of this type. Consider the following query:

SELECT NAME FROM SYSIBM.SYSTABLES
WHERE NAME IN (?, ?)

You could REST-enable that query, and a client program might invoke the REST service with this input JSON document (note that when you use positional parameter marker syntax in your query, as seen in the IN-list predicate shown above, REST will assign JSON parameter names as Px, where "x" is the parameter position - 1, 2, 3, etc):

{
"P1" : "CUSTOMER",
"P2" : "DEPT"
}

Now, what if you want to run that query again, but this time you want to return rows that have one of three different values in the NAME column of SYSIBM.SYSTABLES (e.g., 'CUSTOMER', 'DEPT' or 'EMP'), versus just looking for matches for two NAME values ('CUSTOMER' or 'DEPT', as shown above)? Oops. You can't send in three input values for a query that's coded with only two parameter markers. Here's the main issue: a Db2 SQL statement that is REST-enabled is not a statement that is dynamically constructed at invocation time. It is, rather, a pre-coded, server-side statement - what a Db2 DBA would call a static SQL statement (that being the case, what's really done via Db2's REST interface is REST enablement of a Db2 package, which you can think of as the compiled and executable form of a static SQL statement).

How, then, do you deal with the possibility that a REST-enabled query with an IN-list predicate might be invoked at different times with varying numbers of IN-list input values, knowing that the pre-coded query has a fixed number of parameter markers in its IN-list predicate?

The best way to deal with this situation might be to code the query (the Db2 server-side query that will be REST-enabled) with what you think will be the maximum number of IN-list values that a service-invoking program will provide. Suppose we determine that for the example query used in this blog entry, that number is six. The query to be REST-enabled, then, would look like this:

SELECT NAME FROM SYSIBM.SYSTABLES
WHERE NAME IN (?, ?, ?, ?, ?, ?)

A REST invocation of that query will work just fine when six values for the IN-list predicate are provided in the request's JSON input document; but, what if the query is invoked by a request that provides just three input values for the IN-list predicate? Will the REST invocation of the query work if the JSON input document contains only three values? Nope - the number of provided input values has to equal the number of parameter markers (or host variables, as the case may be) coded in the SQL statement that is being invoked via the REST request. What, then, can be done to successfully execute the query with six parameter markers in its IN-list when the requesting program only has (for example) three IN-list values to pass along with a request for the service?

There are a couple of approaches you could take in that case. One option would be to put the three distinct IN-list values in the input JSON document sent with the request, with the last of those three values repeated to provide the required fixed total number of input values, like this:

{
"P1" : "CUSTOMER",
"P2" : "DEPT",
"P3" : "EMP",
"P4" : "EMP",
"P5" : "EMP",
"P6" : "EMP"
}

Another option (feasible if the column referenced in the IN-list predicate is defined as NOT NULL) would be to provide the distinct input values in the JSON input document and then provide NULL to get the number of values up to the fixed requirement, as shown below:


"P1" : "CUSTOMER",
"P2" : "DEPT",
"P3" : "EMP",
"P4" : NULL,
"P5" : NULL,
"P6" : NULL
}

And there you have it. Takes a little adaptability on the client side, but you can indeed REST-enable a Db2 query that has a fixed number of IN-list predicate parameter markers (or host variables), and then successfully invoke that query with varying numbers of provided distinct values for the IN-list predicate, so long as the number of provided values does not exceed the number of parameter markers (or host variables) coded for the query's IN-list predicate.

I hope that this information will be useful for you. And, I want to give a shout-out here to Tom Toomire of the IBM Db2 for z/OS development team, from whom I have learned a great deal about the REST interface to Db2. Thanks, Tom!