When a plan is bound, you can include a package list that consists
of wildcard characters, for example COLLID3.* or *.*. However, using
wildcard characters can result in a plan pointing at thousands of
packages with an even greater number of explainable SQL statements.
To prevent packages with SQL statements from being explained unnecessarily, Plan Table looks for
generic package lists in the plan being explained. If the collection
ID or the package name in a package list was specified as an asterisk
(*), Plan Table determines
the number of packages that belong to the package list specification.
If more than 100 packages belong to the package list specification,
a report with all the package names is produced, but no SQL statements
in these packages are explained. The collection IDs are not listed.
If any DBRMs belong to the plan, SQL statements in these DBRMs are
explained.
The plan TESTPLAN was bound with a package list of *.*, a plan
statement therefore results in the report shown in the following figure. Figure 1. TESTPLAN package
report
If more than 10 but less than 100 packages belong to the package
list specification, a report with all the package names is produced,
but no SQL statements in these packages are explained. This report
shows the collection IDs, the creator and owner names, the version
identifications, and the dates of precompilations. If any DBRMs belong
to the plan, SQL statements in these DBRMs are explained.
The plan LARGPLAN was bound with a package list of E23PACK.*, a
plan statement therefore results in the report shown in the following
figure. Figure 2. LARGPLAN package report
When a package is being explained, you might specify the collection
ID, the package name, or both as generic names using an asterisk (*)
as a wildcard character. For example, you might specify:
If a wildcard character is used, Plan Table counts the
total number of SQL statements that belong to the packages (the total
number of SQL statements also includes non-explainable statements).
If the number of packages is more than one and the total number
of SQL statements in these packages is larger than 300, Plan Table does not
explain any SQL statements unless the FORCE=YES subparameter is specified.
Instead the mini report shown in the following figure is produced.
Figure 3. Report where
statements in packages is larger than 300
2005-04-30 *** Explained EXPLAIN Information *** Report 1
Page 1-001
============================================================================
Package Location: DALLAS-CQ
Explain Location: CPHMVS1_Db2X
ANL372W The statement: PKG=E23PACK.E23*
will result in 25 packages
with a total of 2,042 SQL statements (including non-explainable).
If you want to explain that amount of packages, you should specify:
PKG=E23PACK.E23*,FORCE=Yes
If the package specification does not contain any wildcard characters,
the total number of SQL statements is less than 300, or only one package
conforms to the specification, the SQL statements are explained. However,
if the package exists in more than one version, only the most recent
version is explained. In this case, a report showing all the versions
for that package is produced. If you specify Package=e23pack.e23main(-7),gen=3 the
report shown in the following figure is produced: Figure 4. Package version
report
2005-04-30 *** Explained EXPLAIN Information *** Report 2
Information for Package: E23MAIN *** Version Report *** Page 2-000
============================================================================
Package Location: DALLAS-CQ
Explain Location: CPHMVS1_Db2X
ANL166I Package: E23MAIN in Collection: E23PACK has the following versions
Pre-Comp'd Exp Gen Version Identification
---------- --- --- ---------------------------
1999-12-20 Yes 0 1999-12-20-14.49.03.726556
1999-12-19 Yes -1 1999-12-19-20.12.46.296787
1999-12-19 Yes -2 1999-12-19-16.31.06.369415
1999-12-19 Yes -3 1999-12-19-16.15.48.278743
1999-12-18 Yes -4 1999-12-18-09.17.18.591934
1999-12-17 Yes -5 1999-12-17-09.16.06.101113
1999-12-17 Yes -6 1999-12-17-08.44.59.541029
1999-12-17 Yes -7*1999-12-17-08.22.45.446834
1999-12-16 Yes -8 1999-12-16-12.04.03.828613
1999-12-15 Yes -9 1999-12-15-11.11.59.937500
1999-12-15 Yes -10 <version id not specified>
1999-12-09 Yes -11 1999-12-09-10.15.10.307455
1999-12-08 Yes -12 1999-12-08-11.10.46.584507
1999-12-08 Yes -13 1999-12-08-10.47.06.119000
1999-12-08 Yes -14 1999-12-08-10.31.08.242248
1999-12-05 Yes -15 1999-12-05-19.01.27.836759
1999-12-04 Yes -16 1999-12-04-19.16.42.852327
1999-12-03 Yes -17 1999-12-03-08.28.25.916853
1999-12-02 Yes -18 ANL420.5
1999-11-13 Yes -19 ANL420.4
1999-11-13 Yes -20 ANL420.2
Start version generation number specified: -7
Number of version generations requested: 3
The listed report shows that the package exists in 21 versions,
where the version that corresponds to generation number -7 was selected
for explanation. The report further shows that you have asked for
explanation of three generations (-7, -8, and -9). Instead of specifying
a generation identification, the version identification could be specified.
You can either specify the version ID in full or in combination with
a wildcard character, as shown in the following example: