Tip:
Highlight text to annotate it
X
Welcome to this Skill Builder video from IBM
where we will be examining the use of RUNSTATS profiles
in DB2 for z/OS. First, we will define what RUNSTAT profiles are
and where they are stored. Then we'll create an initial RUNSTATS profile
using existing RUNSTATS statistics and use that profile to continue generating
statistics.
Next, we'll update the RUNSTATS profile with additional options.
And finally, we'll delete the RUNSTATS profile.
Throughout our discussion, we'll examine the catalog tables that are affected by
RUNSTATS
and RUNSTATS profiles. We'll start by looking at a new feature that was
introduced with DB2 version 10 RUNSTATS profiles.
A RUNSTATS profile is a saved set of options for the RUNSTATS utility
that applies to a particular table. DB2 uses RUNSTATS profiles for
autonomic statistics. You can also use RUNSTATS profiles to quickly invoke the
RUNSTATS utility
with a predefined set of options. A RUNSTATS profile for a table
is saved as a single row in the SYSIBM.SYSTABLES_PROFILES
catalog table.
After you create a profile for a table, you can specify that DB2 uses the same
options
that were specified in the SYSTABLES_PROFILES table
when you invoke RUNSTATS later. Profiles can also be updated with additional
options
and they can be deleted from the catalog. I'll demonstrate how RUNSTATS profiles
work in more detail.
First, I'll create a new table named TEST with one index.
The table was successfully created,
as was its index. Next, I'll
insert three rows into the TEST table. Three rows were successfully inserted.
At this point, no RUNSTATS have been gathered
and no profiles have been created. I can verify this by querying SYSTABSTATS and
SYSTABLES_PROFILES in the catalog. No statistics have been gathered on the
table
and no profile exists. Now, I'll gather RUNSTATS on the table.
Notice, that I'm just gathering RUNSTATS on the table not
its index.
My job job ran with a condition code of zero. The output of the utility shows that
RUNSTATS were gathered on the table.
Now I'm going to go back and query
the catalog for SYSTABSTATS and SYSTABLES_PROFILES.
SYSTABSTATS show that statistics have been gathered
but no profile exists. So that we can track what is happening,
I'm going to add three more rows to the table. Three rows were successfully added.
The table now contains six rows. Next, I'm going to run RUNSTATS with the option
to set a profile from the existing statistics.
RUNSTATS ran with a condition code of zero. From the output
you can see that no statistics were gathered but a profile was created.
To verify this, I'm going to query the catalog again.
I'll query SYSTABSTATS and
SYSTABLES_PROFILES. SYSTABSTATS
still show a row cardinality of three. RUNSTATS were not gathered
but there is a profile for the table TEST. Now that I have a profile
I can use the profile to run RUNSTATS against the table.
The job ran successfully.
The output shows that RUNSTATS were gathered.
I'm going to query the catalog again.
I'll query SYSTABSTATS and SYSTABLES_PROFILES.
SYSTABSTATS
shows that the cardinality was updated to six. And I still have a profile for my
table.
Once again, I'm going to insert three additional rows into the table
for a total of nine. The rows were successfully inserted.
Next, I'm going to run RUNSTATS.
I'm going to include the option to gather statistics on all
indexes but I'm just going to update the profile.
RUNSTATS
ran successfully. The output
shows that statistics were not gathered but the profile was updated.
I can verify this by querying
SYSTABSTATS and SYSTABLES_PROFILES. SYSTABSTATS shows that the cardinality
is still six
and I have a profile. I'm going to move over on the profile
so that you can see that it was updated. You can see it now includes
index asterisk. Now, I'll run RUNSTATS again and use the updated profile.
RUNSTATS ran successfully. The output shows the profile was used and
not only were statistics gathered on the table, they were gathered on the
indexes as well.
Once again, I'll query the
catalog. And, I see the cardinality has been updated to nine.
I can also delete a RUNSTATS profile.
When I run RUNSTATS with the DELETE PROFILE option RUNSTATS are not gathered
but the profile is deleted from the catalog. The output shows that no
statistics were gathered.
If I query the catalog
I can see that statistics were not
affected but that the profile has been deleted.
Now, I'm going to try and use the profile that no longer
exists. When I run RUNSTATS with the USE option
I get a condition code of eight. It was not able to run RUNSTATS because the profile does
not exist.
Let's recap what we've done.
RUNSTATS profiles are saved options in the SYSTABLES_PROFILES catalog table.
Each RUNSTATS profile applies to a single table.
A RUNSTATS profile can be set from existing statistics.
It can be used to generate RUNSTATS. It can be updated to merge additional options in
and it can be deleted to remove it from the catalog. If you would like to deepen
your understanding of DB2 10 for z/OS RUNSTATS profiles
considering enrolling in one of the following courses CV831
DB2 10 for z/OS Database Administration Workshop Part 1
CV311 CV312 DB2 10 for z/OS New Functions and Features.
We have a variety of training formats to meet your needs.
You may also want to become a Certified Professional.
To become an IBM Certified Database Administrator
DB2 10 for z/OS, you need to pass two tests.
The DB2 10.1 Fundamentals test. Test number 000-610
and DB2 10 DBA for z/OS test
000-612.