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 |
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. |
