IBM Support

PI98649: NEW FUNCTION UPDATE OF DB2 NATIVE RESTFUL SERVICES SUPPORT

A fix is available

Subscribe

You can track all active APARs for this component.

APAR status

  • Closed as new function.

Error description

  • New Function update of Db2 native RESTful services support.
    DB2REST
    
    Additional keywords and symptoms:
    ********************************************************
    Also addresses SQLDDNAME close/free issue indicated by
    message DSNT274I (DDNAME COULD NOT BE OPENED) and JES message
    IEC301I
    

Local fix

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * All users of DB2 for z/OS planning to                        *
    * use DB2 native RESTful services support.                     *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * Update to the DB2 native RESTful                             *
    * services support, including both                             *
    * defect fixes and new functionality.                          *
    ****************************************************************
    * RECOMMENDATION:                                              *
    ****************************************************************
    This APAR provides a service update to the DB2 native RESTful
    services support, including both defect fixes and new
    functionality.  In particular, this service update APAR
    addresses the following issues:
    1. Many customers have stated the need to be able to
    operationally control their defined Db2 REST services.
    2. Many customers have requested a formal and integrated
    solution for REST service versioning support which would
    allow for the development and deployment of new "versions"
    of existing user REST services, while still allowing the
    existing version(s) of the same named REST service to be
    used.
    3. REST Discover Service incorrectly displays DATE/TIME data
    type inputs as character string data type in Request Schema
    for statements executed on accelerator server.
    4. The camel case JSON key names, ServiceCollectionID,
    ServiceProvider, ServiceURL,  ServiceDescription, and
    ServiceName, which are returned in a DISCOVER ALL services
    request are inconsistent with the corresponding
    serviceCollectionID, serviceProvider, serviceURL,
    serviceDescription, and serviceName, JSON keys returned by
    the DISCOVER DETAILS API.  The returned JSON keys for
    DISCOVER ALL and DISCOVER DETAILS should be the same so
    that common code could be used to parse the replies.
    5. The result columns of a set operator(UNION/INTERSECT/EXCEPT)
    can appear unnamed.
    6. There is insufficient information in the response schema
    following the creation of a native REST service to determine
    what bind options were used, or if the supplied bind option
    was unrecognized.
    7. REST service execution can return incorrect result set for
    DECFLOAT type column.  Problem can happen when the string
    representation of subsequent values expands with respect to
    the first value.
    8. A Db2 RESTful service is created for an SQL statement asking
    for an output Db2 DECIMAL column. When the scale in the
    DECIMAL column definition is zero and the actual SQL data in
    the table is zero, Db2 native RESTful services generate an
    incorrectly formatted JSON numeric value 00.
    9. A Db2 RESTful service is created for an SQL statement that
    has an output Db2 DECIMAL column.  When the precision and
    scale in the DECIMAL column definition are equal, invalid
    JSON numeric values are generated for minimum and maximum of
    the column's response schema during discover details.  For
    example, the minimum of a DECIMAL(2,2) is -0.99, but -.99
    was generated.  The maximum of a DECIMAL(2,2) is 0.99, but
    .99 was generated.  For any other DECIMAL definitions,
    incorrect JSON numeric values are generated for minimum and
    maximum of the column's response schema.  For example, the
    minimum of a DECIMAL(8,3) is -99999.999, but -9999.999 was
    generated.  The maximum of a DECIMAL(8,3) is 99999.999, but
    9999.999 was generated.
    10.The Db2 statistical counters which reflect the number of
    messages sent and received from client locations are not
    incremented during Db2 RESTful service request processing.
    11.When attempting to process two or more BIND SERVICE
    subcommands, which reference the same SQLSTMTDD definition,
    within the same TSO batch job step, the second and
    subsequent BINDs receive an error that the specified
    SQLSTMTDD has been freed and is no longer allocated to the
    job step.
    12.Db2 RESTful services support generates client product_id
    (PRDID) and accounting values which are not documented.
    13.Create REST service fails with SQLCODE-104 when the input
    SQL statement text includes a terminating semi-colon
    character.  Although a semi-colon is used to terminate SQL
    statements in some environments (eg. SPUFI), the terminating
    semi-colon itself is NOT part of valid SQL statement text,
    which results in the SQLCODE-104 failure when it is supplied
    for a REST service SQL statement.  Although the current
    behavior is expected, several customers have requested a
    usability enhancement for the create REST service processing
    which will allow the input SQL statement text to include a
    terminating semi-colon.
    14.There is inconsistency in what bind options are allowed
    between CREATE SERVICE and BIND SERVICE.  Additionally, bind
    options that are not applicable to native REST services are
    accepted with no indication or warning.
    15.Due to an internal Db2 logic error, the cursor ISOLATION bind
    option specified when a REST service is created is not set
    correctly, and all REST services are being created with the
    default "CS" (cursor stability) isolation level.
    16.During BIND SERVICE and CREATE SERVICE, warnings due to bind
    options not being applicable can result in CREATE or BIND
    failure, and the service not being created.
    17.During CREATE SERVICE, the OPTHINT bind option value is
    folded to uppercase even though a lower cased hint value was
    specified.
    18.BIND SERVICE incorrectly allows delimited collection ids or
    service names containing invalid characters.
    19.Remote BIND PACKAGE at a DB2 for z/OS server can fail when
    bind option keyword values contain mixed cased characters.
    

Problem conclusion

Temporary fix

Comments

  • The following changes have been made:
    1. This APAR adds new "-START RESTSVC", "-STOP RESTSVC",
       and "-DISPLAY RESTSVC" DB2 commands that can be used to
       operationally control Db2 user defined REST services.  The
       START/STOP commands will only affect REST services that
       exist at the time when the command is issued. The started
       or stopped state of REST services is preserved across Db2
       restarts and the commands apply to the entire group in a
       data sharing environment.  If a REST service is stopped,
       then any new invocation attempt or discover of the service
       details will fail with an HTTP 503 (resource unavailable)
       return status.
       In addition to the new commands to START and STOP services,
       the DISCOVER ALL services output now includes a new
       serviceStatus key that shows the current status, "started"
       or "stopped", for each reported service.
       The general syntax for the new Db2 -START RESTSVC command
       is:
         -START RESTSVC(service_specification)  SCOPE(GROUP)
       The general syntax for the new Db2 -STOP RESTSVC command
       is:
         -STOP RESTSVC(service_specification)
                ACTION(REJECT)   SCOPE(GROUP)
       The general syntax for the new Db2 -DISPLAY RESTSVC
       command is:
         -DISPLAY RESTSVC(service_specification)
                   STATUS(*|STARTED|STOPREJ)   LIMIT(integer|*)
                   SCOPE(GROUP)
       The new commands require the introduction of new Db2
       messages and a Db2 abend reason code.  An overview of those
       messages and the reason code can be found later in this APAR
       along with an explanation of the parameters for the new
       commands.
    
       Refer to the Db2 for z/OS documentation in the IBM Knowledge
       Center for complete information about the updated Db2
       native REST services support.
    2. This APAR provides support for a new Db2 REST service
       versioning solution.  Although a high level description of
       the new REST service versioning functionality is provided
       in this APAR closing text, please refer to the Db2 Knowledge
       Center for the complete documentation for the new Db2 REST
       service versioning support.
       The Db2 REST service versioning support is based on the
       existing Db2 package versioning support, along with REST
       service specific additions.  After REST service versioning
       is enabled, users will have the ability to create new REST
       services using a user specified "version ID" string, up to
       64 characters in length.
       As part of the new Db2 REST service versioning support, a
       new additional Db2 REST URI format has been added to enable
       the use, discovery, and invocation of versioned REST
       services.  The currently supported REST service URI format
       is:
    
        /services /<collection id> /<service name>
    
       If the <collection id> portion is omitted, then the default
       "SYSIBMSERVICE" collection ID is used.
        Example:
         /services/MyServices/simpleSelect1
       NOTE: In order to maintain compatibility with existing REST
       user applications, the existing Db2 REST URI format will
       continue to be fully supported, even after REST
       service versioning has been enabled.
    
       The new, additional Db2 REST service URI format, which
       supports an optional REST version identifier, is:
    
        /services/<collection id>/<service name> /<version>
    
       If the optional  /<version>  is specified, then the URI
       refers to the specified version of the service.  If the
       optional version is not included in the URI, then the URI
       will resolve to the "default service version" of the
       service.
    
       With this change, the valid Db2 REST service URI variations
       and mappings would be as follows:
    
        /services/<service_name>
         Example: /services/simpleSelect1
          Uses SYSIBMSERVICE as the default collection ID.
          Resolves to the default service version of REST service
          SYSIBMSERVICE/<service name>.
    
        /services/<collection id>/<service_name>
         Example: /services/MyServices/simpleSelect1
          Resolves to the default service version of REST service
          <collection id>/<service name>
    
        /services/<collection id>/<service name> /<version>
         Example: /services/MyServices/simpleSelect1/v2
          Resolves to version <version> of REST service
          <collection id>/<service name>
    
       NOTE: "default service version" refers to the first/initial
       Db2 service that is created for a given
       <collection id>.<service name>, when no other REST service
       with the same <collection id>.<service name> already exists.
    
       In addition to the new URI format, Db2 will now also report
       new service versioning related metadata in the Discover All
       Services and Discover Details Services output.  When REST
       service versioning is enabled (see below), the Discover All
       and Discover Details output can include new "version",
       "isDefault", and "alternateServiceURL" versioning metadata
       JSON keys.
    
       Db2 REST Service Versioning Enablement
       ======================================
       REST service versioning is enabled by running sample install
       JOB, DSNTIJR2, which is shipped with this APAR.
    
       WARNING WARNING WARNING
       This APAR MUST be installed to the target Db2 subsystem(s)
       *BEFORE* JOB DSNTIJR2 is run to enable REST service
       versioning.
       In a Db2 Data Sharing environment, this APAR MUST be
       installed on ALL MEMBERS in a data sharing group *BEFORE*
       JOB DSNTIJR2 is run to enable REST service versioning.
    
       Please refer to sample install JOB DSNTIJR2 for
       additional information, requirements, and details.
    3. Db2 REST Discover Service has been changed to correctly
       display the data type of DATE/TIME data type inputs in the
       Request Schema for statements executed on an accelerator
       server.
    4. The following JSON keys that are returned in a DISCOVER ALL
       services API call have been renamed using lower camel case,
       as shown, in order to be consistent with DISCOVER DETAILS
       output:
       ServiceCollectionID    ->  serviceCollectionID
       ServiceProvider        ->  serviceProvider
       ServiceURL             ->  serviceURL
       ServiceDescription     ->  serviceDescription
       ServiceName            ->  serviceName
    5. Db2 has been changed to show the expected names of the
       result columns of a set operator (UNION/INTERSECT/EXCEPT).
    6. Db2 has been changed to report all bind options, either
       explicitly specified or defaulted by Db2, in the response
       schema under StatusOptions/Applied following the successful
       creation of a REST service.  Explicitly specified, but
       unrecognized bind options, are now reported in the response
       schema under StatusOptions/Unrecognized.
    7. Db2 native RESTful services have been changed to correctly
       return result sets containing DECFLOAT columns with
       expanding values.
    8. Db2 native RESTful services have been changed to correctly
       return a JSON numeric value 0 for any output Db2 decimal
       type with a scale of zero and the Db2 SQL data value is
       zero.
    9. Db2 native RESTful services have been changed to correctly
       add a leading 0 before the decimal point if the data value
       has no digits before the decimal point, and return correct
       values for minimum and maximum in the column's response
       schema for any Db2 DECIMAL column definition during discover
       service details.
    10.Db2 native RESTful service support has been changed to
       properly increment the Db2 statistical counters which
       reflect the number of sent and received messages from a
       client location.
    11.The Db2 BIND SERVICE subcommand has been changed to no
       longer free the allocated SQLSTMTDD at the end of its
       processing.  The same SQLSTMTDD value can then be
       referenced by multiple BIND SERVICE subcommands within the
       same job step and the SQLSTMTDD will then only be freed at
       job step end.
    12.Db2 has been changed to document the contents of the
       distributed client PRDID and accounting strings used for
       Db2 native RESTful request processing.  The PRDID is set
       as follows:
       - HTP01010:  HTTP/REST requesting client
       - HTS01010:  HTTPS/REST requesting client
       The client accounting string is set as follows:
       - REST HTTP function code (Char(5)):
          'GET  ' - service discover
          'POST ' - service invoke
       - Fully qualified RESTful service name (Char242): may be
         truncated
       These changes, which are shipped with this APAR, have been
       made to the DSNDQMDA member of the Db2 externalized source
       library, SDSNMACS.
    13.Db2 native RESTful services have been changed to provide
       a usability enhancement in REST create service processing
       which allows a single terminating semi-colon to be present
       in the input SQL statement text for BIND SERVICE and the
       create service API.
    14.Db2 native RESTful services have been changed to allow the
       following bind options and values for BIND SERVICE and the
       CREATE SERVICE API: ACTION(ADD), CURRENTDATA(NO),
       DESCSTAT(YES), ENCODING(UNICODE), and SQLERROR(NOPACKAGE).
       The bind options APRETAINDUP, APREUSESOURCE, and
       PATHDEFAULT are not applicable and are no longer accepted
       for BIND SERVICE or the CREATE SERVICE API.
    15.Db2 native RESTful services have been changed to correctly
       set the specified cursor ISOLATION level bind option during
       REST service creation.  This change will only take effect
       during the creation of new REST services after this APAR has
       been installed.  Any existing REST services whose cursor
       ISOLATION level did not get correctly set must be recreated
       after this APAR has been installed.
    16.Db2 native RESTful services have been changed to correctly
       allow a REST service to be created following bind option
       warnings during BIND SERVICE and the CREATE SERVICE API.
    17.Db2 native RESTful services CREATE SERVICE API has been
       changed to not fold the OPTHINT bind option value to
       uppercase.
    18.Db2 native RESTful services BIND SERVICE has been changed to
       verify that delimited collection ids or service names
       contain only valid Db2 identifier characters.
    19.Db2 for z/OS server BIND PACKAGE processing has been
       corrected to recognize bind option keyword values containing
       mixed case characters.
    
    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.
    
    PI98649 introduces the following changes to externals:
    ======================================================
    New Db2 abend reason codes:
     00D36801
     Explanation
      A Db2 internal error was detected while processing a
      DISPLAY, START, or STOP RESTSVC command.
    

APAR Information

  • APAR number

    PI98649

  • Reported component name

    DB2 OS/390 & Z/

  • Reported component ID

    5740XYR00

  • Reported release

    B10

  • Status

    CLOSED UR1

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    YesSpecatt / New Function / Xsystem

  • Submitted date

    2018-05-31

  • Closed date

    2018-09-11

  • Last modified date

    2018-11-27

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

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

    UI58425 UI58431

Modules/Macros

  • DSN9SCNE DSNWZDG2 DSN9SCNS DSNIXMSI DSNTBRB2 DSNTIJR2 DSNLJDSC
    DSNLJSPS DSNTBCM2 DSNLSSRB DSNLJCMD DSNDQMDA DSNTBCM5 DSNLJDSN
    DSNLJEMG DSNWDFDM DSNLJDSS DSNLJGUS DSNFLDIR DSNLJMUS DSNLSSRW
    DSNWDFMG DSNTBRSD DSNECP48 DSNTIJRS DSNECP49 DSNTBAP  DSNLDTML
    DSNTBAS  HDBBB10J DSNLJHPP DSNXODML DSNFCDIR DSNLXRWA DSNECP54
    DSNTBAS2 DSNLJACC DSNLJSSG DSNXOMD  DSN9SCN2 DSNDQ9ST DSNLJECK
    DSNLJSSP DSNLIRTR DSNTBMSG DSNLXRCS DSNTBCM  DSNTBFR2 DSNLJXUS
    DSNLJTIN DSN9SCNA
    

Publications Referenced
GC19405308 SC19405408 GC19406210 GC27884702 SC27884802
GC27885502        

Fix information

  • Fixed component name

    DB2 OS/390 & Z/

  • Fixed component ID

    5740XYR00

Applicable component levels

  • RB10 PSY UI58431

       UP18/09/18 P F809

  • RC10 PSY UI58425

       UP18/09/17 P F809

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.



Document information

More support for: DB2 for z/OS

Software version: 11.0

Reference #: PI98649

Modified date: 27 November 2018