Note: This little problem was found by SVT in db2_cobra, but the
problem has existed since V95 GA and is a problem in v95.
Group Reporting Issue: SVT
Problem Abstract: db2look -wlm: create subclass before
superclass
Problem Description:
Here are the set of files that I have to show to you from 2
different databases, db1 and db2:
/TMP/cathyw/svt/svtpdb11/db2look_wlm
$ ls -1 *
db1:
db2look.bigdb1.wlm.out1 = output from db2look -wlm
wlmcat.out03 = query output for various wlm catalog views
db2:
db2look.bigdb1.wlm.clp = same file as db2look.bigdb1.wlm.out1;
CLP file from db2look -wlm
db2look.bigdb1.wlm.clp.out1 = output from executing
db2look.bigdb1.wlm.clp
For db1, I ran some tools to randomly create and drop various
wlm objects, like service classes. The scripts will attempt to
create existing objects and drop objects that are in use.
For this db1, here are the objects related to
SERVICESUPERCLASSL1, ordered by SCID (which is likely the same
order that db2look uses)
wlmcat.out03 snippet - syscat.serviceclasses ORDER BY scid
===================
SERVICECLASS SYSCAT
===================
SELECT serviceclassid AS scid, char(serviceclassname,28) AS
scname, parentid, enabled, agentpriority AS agentp
ri, prefetchpriority AS prefetchpri, bufferpoolpriority as
bufferpoolpri, collectaggreqdata, collectaggactdata
, collectactdata, collectactpartition, collectreqmetrics,
char(outboundcorrelator,30) AS outboundcorrelator, c
har(remarks,15) AS remarks FROM syscat.serviceclasses ORDER BY
scid
SCID SCNAME PARENTID ENABLED AGENTPRI
PREFETCHPRI BUFFERPOOLPRI COLLECTAGGREQDATA COLL
ECTAGGACTDATA COLLECTACTDATA COLLECTACTPARTITION
COLLECTREQMETRICS OUTBOUNDCORRELATOR REMARKS
------ ---------------------------- -------- ------- --------
----------- ------------- ----------------- ----
------------- -------------- -------------------
----------------- ------------------------------ ------------
---
<...>
59 SERVICESUBCLASSL1_4 73 Y -32768
B E
D D N
- -
60 SERVICESUBCLASSL1_5 73 Y -32768
B E
D D N
- -
<...>
66 SERVICESUBCLASSL1_2 73 Y -32768
B E
D D N
- -
67 SERVICESUBCLASSE1_2 61 Y -32768
N N
N C N
- -
68 SERVICESUBCLASSG1_1 63 Y -32768
B N
D D N
- -
69 SERVICESUBCLASSL1_1 73 Y -32768
B E
D D N
- -
70 SERVICESUPERCLASSE2 0 Y -32768
N N
N C N
- -
71 SYSDEFAULTSUBCLASS 70 Y -32768
N N
N C N
- -
72 SERVICESUBCLASSG1_2 63 Y -32768
B N
D D N
- -
73 SERVICESUPERCLASSL1 0 Y -32768
N N
N C N
- Version 2.0
This grep shows that db2look -wlm produces DDL for the
subclasses of "SERVICESUPERCLASSL1" before producing the DDL for
superclass "SERVICESUPERCLASSL1"
/TMP/cathyw/svt/svtpdb11/db2look_wlm/db1
$ grep SERVICESUPERCLASSL1 db2look.bigdb1.wlm.out1
CREATE SERVICE CLASS "SERVICESUBCLASSL1_4" UNDER
"SERVICESUPERCLASSL1"
CREATE SERVICE CLASS "SERVICESUBCLASSL1_5" UNDER
"SERVICESUPERCLASSL1"
CREATE SERVICE CLASS "SERVICESUBCLASSL1_2" UNDER
"SERVICESUPERCLASSL1"
CREATE SERVICE CLASS "SERVICESUBCLASSL1_1" UNDER
"SERVICESUPERCLASSL1"
CREATE SERVICE CLASS "SERVICESUPERCLASSL1"
Thus, if one executes that produced DDL, as I did for db2,
attempt to create the subclasses will fail since the creation of
the superclass occurs later.
+178 CREATE SERVICE CLASS "SERVICESUBCLASSL1_4" UNDER
"SERVICESUPERCLASSL1" COLLECT ACTIVITY DATA ON ALL DA
TABASE PARTITIONS WITH DETAILS COLLECT AGGREGATE ACTIVITY DATA
EXTENDED COLLECT AGGREGATE REQUEST DATA BASE
+179 DB21034E The command was processed as an SQL statement
because it was not a
+180 valid Command Line Processor command. During SQL
processing it returned:
+181 SQL0204N "SERVICESUPERCLASSL1" is an undefined name.
SQLSTATE=42704
+182
+183 CREATE SERVICE CLASS "SERVICESUBCLASSL1_5" UNDER
"SERVICESUPERCLASSL1" COLLECT ACTIVITY DATA ON ALL DA
TABASE PARTITIONS WITH DETAILS COLLECT AGGREGATE ACTIVITY DATA
EXTENDED COLLECT AGGREGATE REQUEST DATA BASE
+184 DB21034E The command was processed as an SQL statement
because it was not a
+185 valid Command Line Processor command. During SQL
processing it returned:
+186 SQL0204N "SERVICESUPERCLASSL1" is an undefined name.
SQLSTATE=42704
+187
<...>
+237 COMMENT ON SERVICE CLASS "SERVICESUPERCLASSL1" IS
'Version 2.0'
+238 DB20000I The SQL command completed successfully.
+239
+240 CREATE SERVICE CLASS "SERVICESUBCLASSE2_1" UNDER
"SERVICESUPERCLASSE2"
+241 DB20000I The SQL command completed successfully.
+242 |
