IBM Support

IT27267: DB2: DROP XSROBJECT MIGHT FAIL WITH "SQLRI_DDL_COMMON: UNEXPECTED SQLCODE FOR DDL" IF SCHEMA HAS EXACTLY 100 DOCUMENTS

Subscribe to this APAR

By subscribing, you receive periodic emails alerting you to the status of the APAR, along with a link to the fix after it becomes available. You can track this item individually or track all items by product.

Notify me when this APAR changes.

Notify me when an APAR for this component changes.

 

APAR status

  • Closed as program error.

Error description

  • An attempt to drop an XML schema (XSROBJECT) might fail with:
    
    DB21034E  The command was processed as an SQL statement because
    it was not a
    valid Command Line Processor command.  During SQL processing it
    returned:
    SQL0901N  The SQL statement or command failed because of a
    database system
    error. (Reason "sqlri_ddl_common: unexpected sqlcode for DDL!".)
    
    
    if given schema has exactly 100 documents (or a multiple of 100,
    i.e. 200, 300 etc.) associated with
    itself, which is reflected by a relationship in
    SYSXSROBJECTHIERARCHIES table.
    Failed drop attempt will result in a -901 error returned to the
    application (as noted above), FODC_AppErr created in DIAGPATH
    and an error similar to this one written to db2diag.log:
    
    2018-12-11-10.45.21.664771+060 E95432E1586           LEVEL: Info
    (Origin)
    PID     : 21515                TID : 140232502535936 PROC :
    db2sysc 0
    INSTANCE: db2v111              NODE : 000            DB   :
    SAMPLE
    APPHDL  : 0-783                APPID:
    *LOCAL.db2v111.181211093501
    AUTHID  : DB2V111              HOSTNAME: kkuduk
    EDUID   : 1001                 EDUNAME: db2agent (SAMPLE) 0
    FUNCTION: DB2 UDB, SW- common services, sqlnn_cmpl, probe:650
    MESSAGE : ZRC=0x80040001=-2147221503=SQLD_NOREC "NO MORE RECORDS
    FOUND ON FETCH"
              DIA8000C An unexpected end of file was reached "".
    ...
    DATA #7 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
     sqlcaid : SQLCA     sqlcabc: 136   sqlcode: 100   sqlerrml: 0
     sqlerrmc:
     sqlerrp : SQLNN067
     sqlerrd : (1) 0x80040001      (2) 0x00000001      (3)
    0x00000000
               (4) 0x00000000      (5) 0x00000000      (6)
    0x00000000
     sqlwarn : (1)      (2)      (3)      (4)        (5)       (6)
    
               (7)      (8)      (9)      (10)        (11)
     sqlstate:
    DATA #8 : Hex integer, 4 bytes
    0x00000040
    DATA #9 : String with size, 42 bytes
    drop xsrobject db2v111.ts001564691_with100
    DATA #10: String, 167 bytes
    Compiler error stack for rc = -2147221503:
    sqlnn_cmpl[300]
    sqlnp_main[250]
    sqlnp_parser[510]
    sqlnp_smactn[180]
    sqlnq_drop_db_object[700]
    sqlnq_drop_db_object_end[510]
    
    
    For instance, if the affected XML schema is named
    DB2V111.SCHEMA_WITH100DOCS:
    $ db2 drop xsrobject db2v111.schema_with100docs
    DB21034E  The command was processed as an SQL statement because
    it was not a
    valid Command Line Processor command.  During SQL processing it
    returned:
    SQL0901N  The SQL statement or command failed because of a
    database system
    error. (Reason "sqlri_ddl_common: unexpected sqlcode for DDL!".)
    
    SQLSTATE=58004
    
    one can verify the number of documents added to given XML schema
    by running the following query:
    
    db2 "select count(*) as doc_count from syscat.xsrobjects as o,
    syscat.xsrobjecthierarchies as h where o.objectid = h.objectid
    and o.objectschema = 'DB2V111' and o.objectname =
    'SCHEMA_WITH100DOCS' "
    
    DOC_COUNT
    -----------
            100
    

Local fix

  • Adding an extra document to the XML schema will allow to drop
    the XML schema:
    $ db2 "add xmlschema document to db2v111.schema_with100docs add
    http://url_dummy from customer.xsd"
    DB20000I  The ADD XMLSCHEMA DOCUMENT command completed
    successfully.
    $ db2 "drop xsrobject db2v111.schema_with100docs"
    DB20000I  The SQL command completed successfully.
    
    If schema is already completed, one will not be able to add a
    document to it:
    
    db2 "add xmlschema document to ts001564691_with100 add
    http://url_dummy from customer.xsd"
    SQL20339N  The XML schema "DB2V111.TS001564691_WITH100" is not
    in the correct
    state to perform operation "ADD XMLSCHEMA DOCUMENT".
    SQLSTATE=55063
    
    in which case one shall
    1. create a new schema:
    $ db2 "register xmlschema http://repro.com from customer.xsd as
    ts001564691_with100_evolved"
    DB20000I  The REGISTER XMLSCHEMA command completed successfully.
    
    2. complete it
    $ db2 "complete xmlschema ts001564691_with100_evolved"
    
    3. evolve the old schema into the new one, with '1' switch.
    
    $ db2 "CALL SYSPROC.XSR_UPDATE(CURRENT
    SCHEMA,'TS001564691_WITH100',CURRENT
    SCHEMA,'TS001564691_WITH100_EVOLVED',1)"
    
    4. Drop the schema:
    $ db2 drop xsrobject  TS001564691_WITH100
    DB20000I  The SQL command completed successfully.
    

Problem summary

  • ****************************************************************
    * USERS AFFECTED:                                              *
    * ALL                                                          *
    ****************************************************************
    * PROBLEM DESCRIPTION:                                         *
    * See Error Description                                        *
    ****************************************************************
    * RECOMMENDATION:                                              *
    * Upgrade to Db2 11.1 Mod 4 Fixpack 5 or higher                *
    ****************************************************************
    

Problem conclusion

  • First fixed in Db2 11.1 Mod 4 Fixpack 5
    

Temporary fix

Comments

APAR Information

  • APAR number

    IT27267

  • Reported component name

    DB2 FOR LUW

  • Reported component ID

    DB2FORLUW

  • Reported release

    B10

  • Status

    CLOSED PER

  • PE

    NoPE

  • HIPER

    NoHIPER

  • Special Attention

    NoSpecatt / Xsystem

  • Submitted date

    2018-12-11

  • Closed date

    2020-01-16

  • Last modified date

    2020-01-16

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

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

Fix information

  • Fixed component name

    DB2 FOR LUW

  • Fixed component ID

    DB2FORLUW

Applicable component levels

  • RB10 PSN

       UP

[{"Business Unit":{"code":"BU053","label":"Cloud & Data Platform"},"Product":{"code":"SSEPGG","label":"Db2 for Linux, UNIX and Windows"},"Component":"","ARM Category":[],"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Edition":"","Line of Business":{"code":"LOB10","label":"Data and AI"}}]

Document Information

Modified date:
16 January 2020