Fixes are available
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: MYHOST1 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
2022-04-03
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":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SSEPGG","label":"DB2 for Linux- UNIX and Windows"},"Platform":[{"code":"PF025","label":"Platform Independent"}],"Version":"11.1","Line of Business":{"code":"LOB10","label":"Data and AI"}}]
Document Information
Modified date:
04 May 2022