PI90243: SERVICE UPDATE OF DB2 V11 NATIVE RESTFUL SERVICES SUPPORT

Obtain the fix for this APAR.

APAR status

• Closed as program error.

Error description

• See PI90244 for DB2 V12 for z/OS
  Multiple fixes to the RESTful services support in DB2
  are being delivered.  In particular, this APAR will address:
  - Db2 V11 APAR PI86867 delivered the initial DSN BIND SERVICE
    command support.  After delivery, it was discovered that the
    DSN BIND SERVICE support delivered in Db2 V11 APAR PI86867
    was incomplete, and will not work correctly in a Db2 Data
    Sharing mixed release environment with both Db2 V11 and
    Db2 V12.  Using Db2 V11 APAR PI86867 level DSN BIND SERVICE
    in a mixed release environment could result in various
    unexpected BIND SERVICE errors, including possible
    ABEND S0C4 in BIND related modules such as DSNTBAP2.
    The Db2 V11 BIND SERVICE Data Sharing release
    incompatibility issue is corrected by this APAR (PI90243).
    Please note that in order to correct the BIND SERVICE
    command support to work correctly in a Db2 Data Sharing
    mixed maintenance and release environment going forward,
    it was REQUIRED that the BIND SERVICE fix shipped in this
    APAR (PI90243) be INCOMPATIBLE with  Db2 V11 APAR PI86867
    level of BIND SERVICE.
  
        **********************
           Important Note
        **********************
    THIS MEANS THAT THIS APAR (PI90243) MUST BE APPLIED TO ALL
    Db2 V11 SUBSYSTEM ENVIRONMENTS, INCLUDING ALL TSO/BATCH
    ENVIRONMENTS WHERE THE DSN BIND SERVICE COMMAND WILL BE
    ISSUED FROM AND THE Db2 SUBSYSTEM(S) PROCESSING THE BIND
    SERVICE COMMAND, IN ORDER FOR THE BIND SERVICE COMMAND TO
    WORK CORRECTLY.
        **********************
           Important Note End
        **********************
  
    The requirement to install this APAR in all Db2 V11
    execution environments is not typical for Db2 maintenance,
    but is REQUIRED in this case in order for the DSN BIND
    SERVICE command to work correctly in mixed release
    environments moving forward.
    Using the DSN BIND SERVICE command in a Db2 V11 environment
    where PI90243 is not fully applied and active in all
    environments may result in failures similar to the
    BIND SERVICE failures described above, such as
    ABEND S0C4 in DSNTBAP2, which could occur in a Db2 V11
    APAR PI86867 level Db2 Data Sharing mixed release
    environment with both Db2 V11 and Db2 V12.
  - REBIND PACKAGE command does not work correctly for a REST
    service packages.
  - DDF Profile Monitoring does not work as expected for
    REST service execution.
  DB2REST
  
  Additional Keywords:
  IBM.Coexistence.DB2.SYSPLEXDataSharing
  IBM.Migrate-Fallback.DB2.V11
  

Local fix

Problem summary

• ****************************************************************
  * USERS AFFECTED: All users of DB2 11 for z/OS planning to     *
  *                 use, or currently using, DB2 native RESTful  *
  *                 services.                                    *
  ****************************************************************
  * PROBLEM DESCRIPTION: Update to DB2 native RESTful services   *
  *                      support, including both defect fixes    *
  *                      and new functionality.                  *
  ****************************************************************
  * RECOMMENDATION:                                              *
  ****************************************************************
  This APAR provides a service update to DB2 native RESTful
  services support, including both defect fixes and new
  functionality.  In particular, this service update APAR
  addresses the following issues:
  
  1.Db2 V11 APAR PI86867 delivered the initial DSN BIND SERVICE
    command support.  After delivery, it was discovered that the
    DSN BIND SERVICE support delivered in Db2 V11 APAR PI86867
    was incomplete, and will not work correctly in a Db2 Data
    Sharing mixed release environment with both Db2 V11 and
    Db2 V12.  Using Db2 V11 APAR PI86867 level DSN BIND SERVICE
    in a mixed release environment could result in various
    unexpected BIND SERVICE errors, including possible
    ABEND S0C4 in BIND related modules such as DSNTBAP2.
  
  2.An IBM DB2 Analytics Accelerator for z/OS (IDAA) offloaded
    table cannot be the object of a DB2 RESTful service.
  
  3.DB2 system profile monitoring is not supported during a
    DB2 RESTful service invocation.
  
  4.The DSN REBIND PACKAGE command could incorrectly allow
    options that are unsupported for DB2 native REST packages.
  
  5.DB2 received an HTTP REST request that included an HTTP
    "Expect: 100-continue" header field.  DB2 DDF native
    REST services do NOT currently support the HTTP
    "Expect: 100-continue" control, and instead requires
    that the client send the complete HTTP REST request before
    any processing is performed.  Although the REST client
    specified the HTTP "Expect: 100-continue" header, the client
    actually sent the entire request and body without waiting
    for, or receiving, an HTTP "100 (continue)" response from
    DB2.  When DB2 parsed the HTTP REST request and found the
    unsupported "Expect: 100-continue" header field, DB2 rejected
    the request returning an HTTP 406 (Not Accepted) response
    with a body content similar to:
      {
       "StatusCode": 406,
       "StatusDescription": "DB2 does not support this HTTP
        request. Content-Range, Transfer-Encoding, Expect, and
        Range header fields are not supported. Error
        Location:DSNLJHPP:55"
      }
    Although DB2 does not support the "Expect: 100-continue"
    header control, DB2 should not issue an error if the entire
    request and body have already been received.
  
  6.A customer attempted to create a DB2 native REST service
    using the SQL "WITH" (common table expression) syntax.
    The service create fails with HTTP 500 (Internal Server
    Error) and a response with a body content similar to:
      {
       "StatusCode":500,
       "StatusDescription":"SYSIBMSERVICE.DB2ServiceManager
        failed during create for CTESelect1 due to DSNX200I
        -DB2A BIND SQL ERROR  USING SYSADM AUTHORITY
        PLAN=(NOT APPLICABLE)  DBRM=CTESelect1  STATEMENT=0
        SQLCODE=-29  SQLSTATE=42601 TOKENS=  CSECT NAME=DSNHAPL2
        RDS CODE=2 . Error Location:DSNLJMUS:160"
      }
    The SQL statement text used for the native service was:
     "WITH GET_TABLES (CREATOR, NAME) AS (SELECT CREATOR, NAME
      FROM SYSIBM.SYSTABLES WHERE CREATOR = :tableCreator)
      SELECT GT.* FROM GET_TABLES GT"
  
  7.A customer created a DB2 native REST service that included
    several optional BIND options in the create service JSON body.
    The input JSON body contained a syntax error due to a missing
    a comma.  The create service request should have failed due to
    the syntax error.  Instead, the BIND options that followed the
    syntax error were ignored and the service was created without
    the expected BIND options.
  
  8.The DSN BIND PACKAGE command and CREATE SERVICE API allows
    specifying the REOPT bind option, which in turn automatically
    sets the DEFER bind option.  However, there is no direct
    method in which DEFER or NODEFER can be set.
  

Problem conclusion

• The following changes have been made:
  1.The Db2 V11 BIND SERVICE Data Sharing release
    incompatibility issue is corrected by this APAR (PI90243).
    Please note that in order to correct the BIND SERVICE
    command support to work correctly in a Db2 Data Sharing
    mixed maintenance and release environment going forward,
    it was REQUIRED that the BIND SERVICE fix shipped in this
    APAR (PI90243) be INCOMPATIBLE with  Db2 V11 APAR PI86867
    level of BIND SERVICE.
    THIS MEANS THAT THIS APAR (PI90243) MUST BE APPLIED TO ALL
    Db2 V11 SUBSYSTEM ENVIRONMENTS, INCLUDING ALL TSO/BATCH
    ENVIRONMENTS WHERE THE DSN BIND SERVICE COMMAND WILL BE
    ISSUED FROM AND THE Db2 SUBSYSTEM(S) PROCESSING THE BIND
    SERVICE COMMAND, IN ORDER FOR THE BIND SERVICE COMMAND TO
    WORK CORRECTLY.
  2.This APAR adds support to allow DB2 RESTful services to
    reference offloaded tables on an IDAA V4 or V5 server.  This
    includes accessing both offloaded and accelerator-only tables.
  
    The following BIND options are now allowed when creating a
    DB2 native REST service using the BIND SERVICE command, or the
    native REST create service API:
     - QUERYACCELERATION
         (NONE|ENABLE|ENABLEWITHFAILBACK|ELIGIBLE|ALL)
     - GETACCELARCHIVE(NO|YES)
  
  3.This APAR adds support for DB2 system profile monitoring
    for DB2 RESTful services.  The following lists the System
    Profile Monitor filter criteria allowed for DB2 native REST
    services:
  
    - IP address or domain name, in the LOCATION column.
    - Product identifier, in the PRDID column.
    - Role and authorization identifier, in both ROLE and
      AUTHID columns.
    - Role, in the ROLE column only.
    - Authorization identifier, in the AUTHID column only.
    - Collection identifier and package name, in both COLLID
      and PKGNAME columns.
    - Collection identifier, in the COLLID column only.
    - Package name, in the PKGNAME column only.
    - Client application name, in the CLIENT_APPLNAME column.
    - Client user identifier, in the CLIENT_USERID column.
    - Client workstation name, in the CLIENT_WRKSTNNAME column.
  
    Other criteria combinations are not accepted.
  
    Note that the current PRDID value used for DB2 native REST
    services is 'HTP01010' for non-secure HTTP URL connections,
    and 'HTS01010' for HTTPS/secure connections.
  
    System monitor profile exception queuing is not supported
    for DB2 native REST requests.  If a system monitor profile
    EXCEPTION level threshold is met for an incoming DB2 native
    REST request, the REST request will be rejected.
  
  4.This APAR adds formal support for using the DSN REBIND
    PACKAGE command to REBIND DB2 native REST packages.
  
    For REBIND PACKAGE, where the package represents a native
    REST service, the following options and values are ALLOWED:
    - OWNER
    - QUALIFIER
    - PLANMGMT(BASIC|EXTENDED|OFF)
    - APREUSE(NONE|WARN|ERROR|NO)
    - APCOMPARE(NONE|WARN|ERROR|NO)
    - APRETAINDUP(YES|NO)
    - DEFER(PREPARE)
    - NODEFER(PREPARE)
    - DEGREE(1|ANY)
    - DESCSTAT(YES)
    - EXPLAIN(NO|YES)
    - FLAG(I|W|E|C)
    - IMMEDWRITE(NO|YES)
    - ISOLATION(RR|CS|RS|UR|NC)
    - REOPT(VARS|ALWAYS|NONE|ONCE|AUTO)
    - PATH
    - PATHDEFAULT
    - RELEASE(COMMMIT|DEALLOCATE)
    - VALIDATE(RUN|BIND)
    - CONCURRENTACCESSRESOLUTION
        (USECURRENTLYCOMMITTED|WAITFOROUTCOME)
    - BUSTIMESENSITIVE(YES|NO)
    - SYSTIMESENSITIVE(YES|NO)
    - ARCHIVESENSITIVE(YES|NO)
    - APPLCOMPAT
    - GETACCELARCHIVE(NO|YES)
    - QUERYACCELERATION
        (NONE|ENABLE|ENABLEWITHFAILBACK|ELIGIBLE|ALL)
  
    For REBIND PACKAGE, where the package represents a native REST
    service, the following options and values are NOT ALLOWED and
    will result in a DSNT215I message and a REBIND PACKAGE error:
    - ENABLE
    - DISABLE
    - CURRENTDATA
    - DBPROTOCOL
    - DEFER(INHERITFROMPLAN)
    - DESCSTAT(NO)
    - DYNAMICRULES
    - ENCODING
    - EXPLAIN(ONLY)
    - IMMEDWRITE(INHERITFROMPLAN)
    - KEEPDYNAMIC
    - OPTHINT
    - RELEASE(INHERITFROMPLAN)
    - SWITCH
    - EXTENDEDINDICATOR
    - GENERIC
  
  5.DB2 native REST services has been corrected to no longer
    issue an error for an unsupported "Expect: 100-continue"
    header control field if the entire request and body have
    already been received.
  
  6.DB2 native REST services has been enhanced to support the
    "WITH" SQL statement.
  
  7.DB2 native REST create service processing has changed
    to detect syntax errors in the input JSON body, issue an error
    message, and fail the create service request.
  
  8.The DSN BIND SERVICE and REBIND PACKAGE commands, and the
    CREATE SERVICE API now allow the following bind options:
     - DEFER(PREPARE)
     - NODEFER(PREPARE)
  
  Refer to the DB2 for z/OS documentation in the IBM
  Knowledge Center for complete information about the
  updated DB2 native REST services support.
  
  DB2 Information APAR II14827 MUST be reviewed for
  any IMPORTANT additional documentation, and any known
  limitations or restrictions related to the DB2 native REST
  services support.
  
  This APAR contains the following message changes.
  ===================================================
  The package-type token for message DSNT215I is updated
  to include "REST SERVICE" as a possible value.  The
  updated message description is as follows:
  
  DSNT215I   csect-name bind-type bind-subtype FOR PACKAGE =
             package-name FAILED BECAUSE AT LEAST ONE BIND
             OPTION IS NOT ALLOWED FOR A package-type PACKAGE
  
   Explanation
    The BIND or REBIND subcommand has been submitted with an
    invalid combination of bind options. The message tokens
    provides the following information:
  
   csect-name
    The name of the control section that issued the message.
   bind-type
    Type of bind subcommand: BIND or REBIND
   bind-subtype
    Type of bind subcommand: COPY, DEPLOY, or BLANK.
   package-name
    Name of the package in the format:
    location-id.collection-id.package-id(version-id).package-type
   package-type
    Type of package:
      PROCEDURE
      NATIVE SQL PL PROCEDURE
      TRIGGER
      FUNCTION
      REST SERVICE
  
   System action
    The bind fails.
   User response
    Remove the incompatible bind options and reissue the command.
  

Temporary fix

Comments

APAR Information

• APAR is sysrouted FROM one or more of the following:

• APAR is sysrouted TO one or more of the following:

  PI90244 UI53635

Modules/Macros

•    DSNECP00 DSNECP08 DSNECP19 DSNECP29 DSNECP48
  DSNECP54 DSNLIRTR DSNLJACC DSNLJDSN DSNLJEMG DSNLJHPP DSNLJMUS
  DSNLJSPS DSNLJSSG DSNLJSSP DSNLJTIN DSNLJXUS DSNLTACC DSNLTEXC
  DSNTBAP  DSNTBAP1 DSNTBCM  DSNTBMSG DSNTBRB2 DSNTBRSD DSNXEPP
  DSNXERT2 DSNXODML DSNXOMD  DSNXONZC
  

Fix information

• Fixed component name

  DB2 OS/390 & Z/

• Fixed component ID

  5740XYR00

Applicable component levels

• RB10 PSY UI53635

     UP18/02/17 P F802

Fix is available

• Select the PTF appropriate for your component level. You will be required to sign in. Distribution on physical media is not available in all countries.