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!