Overview

Skill Level: Any Skill Level

There are good and bad ways of designing Web Services, particularly where those services are accessing a transactional relational database, which is typically the context where DB2 for z/OS is used. This article presents the good ways 🙂

Ingredients

To implement the examples in this recipe, you need to be able to create RESTful DDF services in DB2 for z/OS V11 or higher.  See the following recipe for details of prerequisites and setup procedures: https://developer.ibm.com/recipes/tutorials/getting-started-with-restful-web-services-support-in-the-distributed-data-facility-of-db2-for-zos/

Step-by-step

  1. Problem Statement

    RESTful interactions in general, and the DDF REST interface in particular, are supposed to be “stateless”.  In the DDF REST context, this means you get the complete operation over and done with in a single call – no context preservation between calls, no holding database locks, none of that stuff.  This presents a challenge for CRUD applications, but it is not a “new thing”.  CICS programmers are well familiar with this problem.  CICS transactions are supposed to be short-lived, and units of work are not preserved between transaction invocations.  This restriction led to the “pseudo-conversational” pattern which has an analogue in the DDF REST environment.

    The steps below deal with Create, Read, Update and Delete in a “pseudo-conversational” manner, delivering stateless CRUD without compormising data integrity.

  2. Create = INSERT

    There isn’t much to say about creating a stateless service to INSERT a record in a table – it is an inherently stateless operation, with a UNIQUE index typically defending against duplicate rows, if necessary.  The only recommendation I would make here is that, as a matter of SQL best practice, you always include the list of column names, as well as values you are inserting, rather than hoping that you have specified the column values in the precise order that they are defined in the table.

    For example, use:

    INSERT INTO ARMY (NAME, RANK, SERIALNO) VALUES(:NAME, :RANK, :SERIALNO)

     rather than:

    INSERT INTO ARMY VALUES(:NAME, :RANK, :SERIALNO)

     So, to create a service in DDF for an INSERT operation, using cURL, for example:

    curl -s -X POST -u ADCDA:******** -H "Accept: application/json" -H "Content-Type: application/json" --data "{\"requestType\": \"createService\", \"sqlStmt\": \"INSERT INTO ARMY (NAME, RANK, SERIALNO) VALUES(:NAME, :RANK, :SERIALNO)\", \"collectionID\": \"MYCOLL\", \"serviceName\": \"addSoldier\", \"description\": \"Recruit soldier into army\"}" http://192.168.0.61:5035/services/DB2ServiceManager

     I won’t show the cURL command for creating services in the following steps, as the focus is on getting the embedded SQL statement right.  The wrapping to create each service is analagous to the above example.

  3. Read = SELECT

    Where you wish to retrieve a single row from a table (ie. the usual CRUD case), there are no special considerations.  To be sure, to be sure (as the Irish would say) that you will get one row (or none), use a “FETCH FIRST” clause.  At this time, DDF REST does not support the “SELECT INTO” single-row SELECT syntax.

    For example:

    SELECT NAME, RANK
    INTO :NAME, :RANK
    FROM ARMY
    WHERE SERIALNO = 12345
    FETCH FIRST ROW ONLY

    The other important case is where your SELECT may return a very large or unpredictable number of rows.  Given that you can’t hold a cursor open, you need a way to bring the rows back in blocks.  There are several ways to skin this cat.  For an exploration of the common methods, see https://www.ibm.com/developerworks/community/blogs/SQLTips4DB2LUW/entry/limit_offset?lang=en, but note that some of these methods will not work on DB2 for z/OS.  However, DB2 12 for z/OS introduces support for the “OFFSET” clause, which, when combined with a “FETCH FIRST” clause, provides the “blocking” capability we are after.

    Assuming you are running DB2 11 for z/OS, I will promote a method that uses an ORDER BY to add predictability to the sequence in which rows are returned, which is the critical element here.  I suggest using an inner SELECT that ascribes a row number to each row returned from an ORDERed SELECT against your target table, with the outer SELECT identifying the specific columns of interest and specifying a range of row numbers to be returned – this is what delivers the “blocking” functionality.

    For example:

    SELECT NAME, RANK, SERIALNO 
    FROM (SELECT ROW_NUMBER() OVER(ORDER BY SERIALNO) AS ROWNO,
    ARMY.*
    FROM ARMY)
    WHERE ROWNO BETWEEN :STARTROW AND :ENDROW

     If you are running DB2 12 for z/OS, then the following simpler option is available to you.  Note that you set SKIPROWS to zero to retrieve the first row(s) of the table:

    SELECT NAME, RANK, SERIALNO
    FROM ARMY
    ORDER BY SERIALNO
    OFFSET :SKIPROWS ROWS
    FETCH FIRST :ROWCOUNT ROWS ONLY
  4. Update = UPDATE and Delete = DELETE

    The considerations for Update and Delete are the same.  A typical front-end application will Read (= single-row SELECT) a record and present it to the end user.  The user then takes an arbitrary amount of time to decide on their changes to the record, or whether to delete it, then the user takes their chosen action.  The initial Read is what the previous step dealt with.  The subsequent Update or Delete has the vulnerability that, while the user was deciding what to do, another user may have come in behind and updated or deleted the record, since we can’t hold a lock on the record.

    To remedy this for an UPDATE (or DELETE), you expand the WHERE clause to include every column of the table, specifying the values returned from the original SELECT.  If the JSON response from your DDF service contains an “Update Count” of 0, you know that the target record was changed while you were waiting for the user to confirm the Update (or Delete), and the operation is not performed.

    The front-end application can inform the user that their Update (or Delete) failed then, for example, fetch the new version of the target record and offer a second chance to perform the original operation.

    To make this work properly, always fetch all columns of the target row, not just the ones that you are offering to update, so you can specify all columns on the WHERE clause for the UPDATE (or DELETE).

    For example, the SQL statement for a service to promote a soldier should look like this:

    UPDATE ARMY
    SET RANK = :NEWRANK
    WHERE NAME = :OLDNAME
    AND RANK = :OLDRANK
    AND SERIALNO = :SERIALNO

Join The Discussion