Plan/Package Analysis
with
SQL EXPLORER
BMC Software Confidential Copyright 2002 All rights reserved.
My Company Part I
bob Informatik AG
Im Tiergarten 10
8055 Zürich
www.bob.ch
Mobil:
Telefax:
Email:
+41 79 405 44 63
+41 1 451 34 76
[email protected]
Geschäftsleitung:
Edi Wassmer, VR, CEO
Roland Frehner, Daniel Harzenmoser
1989
Fr. 100'000
Gründung:
Aktienkapital:
2
BMC Software Confidential Copyright 2002
My Company Part II
3

Beratung und Review für sämtliche Bereiche Ihrer DB2-Installation

Applikatorische und technische Datenbankadministration für DB2

Beratung und Durchführung von Data Warehouse- und Business
Intelligence-Projekten

Leitung von Projekten für Standardsoftware und Eigenentwicklungen

Beratung und Konzeption von Applikationen im Client/Server- oder
im Mainframe-Umfeld

Entwicklung und Realisierung von Java- und OS390-Applikationen

Beratung, Unterstützung und Projektleitung in SAP-Retail und
SAP-Basis-Projekten
BMC Software Confidential Copyright 2002
Introduction to SQL Explorer
4

Overview / Architecture

SQL Explorer - GUI -

SQL Explorer - ISPF -

Catalog Manager Integration

Rules Customization
BMC Software Confidential Copyright 2002
Reduce Application Development
Costs

It is much more cost effective to optimize SQL prior to
production implementation

5
User customizations offer the development opportunity to optimize
the SQL in context to the users’ specific environment.
BMC Software Confidential Copyright 2002
PATROL SQL-Explorer
Product Life Cycle
Development
Implementation
Acceptance
Design
SQL-Explorer
Production
cycle
SQL-Explorer
Development cycle
Package
SQL-Explorer
Code
Test
SQL-Explorer
Production
6
BMC Software Confidential Copyright 2002
Maintenance
Client/Server Architecture for
PATROL SQL-Explorer
Windows
CLIENT
MVS
SERVER
 TCP/IP protocol stack on the
console and MVS server
machines
PATROL SQL-Explorer
(GUI-client)
7
BMC Software Confidential Copyright 2002
• Can be run separately
from GUI-client
• Analysis of complete
plans and packages
PATROL
SQLExplorer
(ISPF)
Architecture
ISPF
Server
Client
MVS
TCP/IP Transport
SNA Transport
MS Windows Console
PATROL DB Voyager
8
BMC Software Confidential Copyright 2002
UNIX, NT, OS/2
Overview

9
Provides DBA/Developer

Access path information correlated with
environmental variables

Easy Analysis of SQL and database structures

Ability to optimize performance of application

Ability to identify performance impacts from data
structure changes

‘What IF’ changes in indexing or statistics can be
analyzed to determine access path impact
BMC Software Confidential Copyright 2002
Overview - Analyze

Analyzes SQL and database structures

Analysis









10
Expert Rules
Recommendations
History
Compare
Impact Analysis Report
Mismatch Analysis
SQLX (edit macro invocation)
Reports
What if scenarios *
BMC Software Confidential Copyright 2002
Overview - Analyze


Driven by set of expert rules
Generates detail and summary reports







11
By Plan, package or DBRM
Easy to understand database access path
information
SQL relative cost as CPU value
SQL text
Recommendations
In Context with database and system
Creates and stores unique historical snapshot
BMC Software Confidential Copyright 2002
Overview - Mismatch Analysis




12
Analyzes and compares left and right sides of the
operand in the predicate
Analyzes SELECT INTO and UPDATE SET clause
Reports all occurrences of data type and length
mismatch
Enables DBA to eliminate mismatches that impact
performance
BMC Software Confidential Copyright 2002
Overview - SQLX




Allows developer to analyze SQL directly from
native program source or plain SQL text
Produces SQL-Explorer Analysis Report online
Executed from a TSO Edit Session
Supported Languages


User must “Initialize Variables” once to run

13
ASSEMBLER, COBOL, C, FORTRAN and PL/1
Selection “C” from Main Menu
BMC Software Confidential Copyright 2002
Overview - Reports

Include





Report message level



14
Basic information from Plan Table
Text of predefined expert rules
Catalog data, filter factors, relative costs
Recommendations
I - Information -- Condition Code 0
W - Warning --- Condition Code 4
S - Severe --- Condition Code 8
BMC Software Confidential Copyright 2002
SQL Explorer - ISPF
Interactive approach to SQL Tuning using
the SQL Explorer ISPF Interface
BMC Software Confidential Copyright 2002 All rights reserved.
Patrol SQL-Explorer ISPF
16
BMC Software Confidential Copyright 2002
BMC DB2 Administration Menu
----------------- BMC Software Administrative Products for DB2------------COMMAND ===>
1 DASD MANAGER for DB2
- Manage or monitor DB2 physical objects
2 CATALOG MANAGER for DB2
- Execute DDL or query the DB2 Catalog
3 CHANGE MANAGER for DB2
- Manage changes to DB2 objects/structures
4 PATROL SQL-Explorer
- SQL Analysis Tool
DB2 SSID . . . . . . . . . . . . DBBK
DB2 Catalog Access . . . . . . . DIRECT
17
BMC Software Confidential Copyright 2002
(Direct)
SQL Analysis - 1 of 3



18
Batch / Online

Plan, Package, DBRM (from DB2 Catalog)

DBRMLIB source
Report

summary - sequence by total cost of sql statement

detail - shows cost per query step number
Explain

use input from PLAN_TABLE

use result from dynamic explain
BMC Software Confidential Copyright 2002
SQL Analysis - 2 of 3


19
Report Message level

controls amount of output

ALL, Severe, Warning, Informational

can be controlled via RULES table
Rules driven

DB2 table

controls severity of messages (I, W, S)

several versions of the rules may exist
BMC Software Confidential Copyright 2002
SQL Analysis - 3 of 3


20
ANALYSIS level

Extended - cost per step

Detailed - no cost info
Maintain History

YES for later COMPARE of ACCESS PATH info

snapshots all relevant info in DB2 tables
BMC Software Confidential Copyright 2002
Compare



21
Compare differences

access paths

SQL Text

catalog statistics
Report

Batch / Online

Optional show only differences
needs ANALYSIS with MAINTAIN HISTORY YES
BMC Software Confidential Copyright 2002
Mismatch Analysis
22

Optimizer is very sensitive to attributes of table
column and variables in application program

Optimizer MAY change the access path based solely
on the difference in data types between the column
and the host variable

Optimizer provides no warning to indicate a data type
mismatch

Mismatch Report provides the function to detect data
type mismatches
BMC Software Confidential Copyright 2002
TSO Edit Macro

Online Analysis against source code



SQLX ssid

mark begin and end with QQ

issue SQLX on command line to invoke online analysis
Initialize SQL Explorer for online Analysis

23
COBOL, PL/I, C, Assembler, FORTRAN
choose option C from SQL Explorer main menu
BMC Software Confidential Copyright 2002
SQL Explorer - Examples

24
Plan / Package Analysis Using ISPF

Plan / Package Analysis

Compare

Mismatch Analysis

Impact Analysis

SQLX TSO Edit Macro
BMC Software Confidential Copyright 2002
SQL Explorer Menu
------------------------- PATROL SQL-Explorer 3.2.02 Main Menu --------------Command ===> _________________________________________________________________
Select an option
_
1. Plan Analysis
2. Package Analysis
3. DBRM Analysis
4.
5.
6.
7.
Mismatch Analysis
Impact Analysis
DBRMlib / loadlib compare
Migrate Statistics
C. Initialize Variables
E. PATROL SQL-Explorer Rules Help
Copyright (C) 1996-1999 BMC Software, Inc.
PATROL SQL-Explorer is a Trademark of BMC Software, Inc.
25
BMC Software Confidential Copyright 2002
SQL Explorer - Examples

Plan / Package Analysis Using ISPF

26
Plan / Package Analysis
BMC Software Confidential Copyright 2002
SQL Explorer - Plan Analysis
-------------------- PATROL SQL-Explorer 3.2.02 Plan Analysis Panel ---------Command ===> _________________________________________________________________
Specify the Analysis options below and press Enter.
Plan Name . . . . . . . . . . . ACT*
Creator Name . . . . . . . . . *
Package Analysis Count . 25
(0-9999) 0 - Pkg analysis will not be performed
Would you like to query DB2 for a selection list? YES (Yes/ No)
27
BMC Software Confidential Copyright 2002
SQL Explorer - Plan Analysis
-------------------- PATROL SQL-Explorer 3.2.02 Object Selection Panel ---------Command ===> _________________________________________________________________
Make a selection and press enter twice to continue.
Specify desired action next to Object name
A = Analysis
C = Compare
B = Analysis & Compare
_ Apply action to all objects
cmd
_
_
28
hist
___
___
Plan
Name
ACT621DL
ACT621DM
BMC Software Confidential Copyright 2002
Creator
Name
JKS
JKS
U = Undo
SQL Explorer - Plan Analysis
-------------------- PATROL SQL-Explorer 3.2.02 Plan Analysis Panel ---------Command ===> _________________________________________________________________
Specify the Analysis options below and press Enter to continue.
Maintain History . . . . . . . . YES (Yes/No) History needed to use Compare Option
Histories Retained . . . . . . . 4
(0-99)
Report Message Level . . . 2
(1-All / 2-Severe / 3-Warning / 4-Informational)
Analysis Level . . . . . . . . . . 1
(1-Extended / 2-Detailed)
Show SQL . . . . . . . . . . . . . YES (Yes / No)
Include Statistics. . . . . . . . NO (Yes / No)
Read Only Application . . NO (Yes / No)
Online Application . . . . . YES (Yes / No)
Use existing Plan Table .
NO (Yes / No)
Predicate Report . . . . . .
YES (Yes / No) works only with Extended Analysis
Columns Break . . . . . . .
NO (Yes / No)
Degree . . . . . . . . . . . . . .
___ (blank / 1 / any)
Select the BMC Message Option:
1
1. Produce the rule messages with the message text, the bmc message ID and the severity
2. Produce the rule messages with just the message text.
3. Bypass all rule message processing
29
BMC Software Confidential Copyright 2002
SQL Explorer - Plan Analysis
-------------------- PATROL SQL-Explorer 3.2.02 Plan Analysis Panel -----Command ===>
_________________________________________________________________
Specify the Analysis options below and press Enter to continue.
Rules Table Owner . . . . DEFAULT
Plan Table Owner . . . . . QUAL
Table Qualifier . . . . . OA1A
Display Text Length . . . 72 (length range from 60 trough 132)
Plan Table Summary . . . . YES (Yes / No)
STATS Summary . . . . . . NO (Yes / No)
INDEX Summary . . . . . . NO (Yes / No)
Select Summary Report Sort Order
1
1. Object, Statement
2. Cost
3. Object, Cost
Generate Report Headings . YES (Yes/No)
Analysis Report
Title . . . . PLAN REPORT
30
BMC Software Confidential Copyright 2002
SQL Explorer - Plan Analysis Batch
------------------------ PATROL SQL-Explorer 3.2.02 JCL Options ------------Command ===> _________________________________________________________________
Review job card; type changes.
Then Press Enter.
Job Card Information.
===> //USERID
JOB (ACCOUNT),'NAME'
===> //*
|-------------------------------------------------------------|
===> //*
| ----------------- Online/Batch Panel ------------------|
===> //*
| COMMAND ===> _____________________________________________ |
|
|
| How do you want to generate Analysis report
|
Output JCL |
|
Output JCL | Run report . . . . 2
1- Foreground
|
or Storag |
2- Batch
|
| PF 1=HELP
2=SPLIT
3=EXIT
9=SWAP
12=CANCEL
|
---------------------------------------------------------------
31
BMC Software Confidential Copyright 2002
SQL Explorer - Plan Analysis - JCL
EDIT
IUKBXF.SQLXPLR.CNTL(PSSAPLAN) - 01.01
Columns 00001 00072
Command ===>
Scroll ===> PAGE
****** ***************************** Top of Data ******************************
==MSG> -Warning- The UNDO command is not available until you change
==MSG>
your edit profile using the command RECOVERY ON.
000001 //IUKBXFA JOB ([email protected]),'ANOTHER'
000006 //*----------------------------------------------------------------000007 //STEPO
EXEC PGM=PSSXTND,PARM='DBBK'
000008 //STEPLIB DD DISP=SHR,DSN=DB2.SAT543.V41.LOAD
000009 //
DD DISP=SHR,DSN='SYS3.DBBK.DSNEXIT'
000010 //
DD DISP=SHR,DSN='SYS2.DB2V41N.DSNLOAD'
000011 //*----------------------------------------------------------------000012 //*
EXECUTION OF PLAN, PACKAGE OR DBRM ANALYSIS
000013 //*----------------------------------------------------------------000014 //ANALYSIS EXEC PGM=PSSMAIN,
000015 // PARM='01DBBK 02PSS227AD 03PSS 04PLAN 05%
X
000016 //
07EXPLQ02',
000017 //
REGION=5M,
000018 //
COND=(4,LT)
000019 //*----------------------------------------------------------------000020 //*-PARMS FOR EXECUTION:
000021 //*-01 - DB2 SUBSYSTEM ID FOR CONNECTION
000022 //*-02 - EXECUTION PLAN NAME FOR ANALYSIS
000023 //*-03 - SQL EXPLORER PRODUCT ID (SEB OR PSS)
000024 //*-04 - OBJECT ANALYSIS TYPE (PLAN,PACKAGE,DBRM)
000025 //*-05 - OBJECT CREATOR NAME
000026 //*-06 - PACKAGE OWNER NAME
32
BMC Software Confidential Copyright 2002
SQL Explorer - Plan Analysis Summary Analysis Report by Object
*********************************
TOP OF DATA **********************************
BMC SQL-EXPLORER BATCH SUMMARY ANALYSIS
SEQUENCED BY OBJECT AND STATEMENT
ANALYSIS DESCRIPTION: PLAN ANALYSIS REPORT
RUN DATE: MON AUG
OBJECT
________
BXFLQ02
BXFLQ02
BXFLQ02
BXFLQ02
BXFLQ02
BXFLQ02
BXFLQ02
9 09:01:08 1999
PROGRAM
COLLID
_________ __________________
EXPLQ02
EXPLQ02
EXPLQ02
EXPLQ02
EXPLQ02
EXPLQ02
EXPLQ02
Continued ……...
33
BMC Software Confidential Copyright 2002
STMT
____
232
250
252
255
257
261
264
TOTAL
FILTER
METH ACCS
COST
FACTOR
OD
TYPE
____________ ____________ ____ ____
348927.68
0.66
0
I
0.00
0.00
0
R
2.30
0.07
0
R
0.00
0.00
0
R
2.28
1.08
0
R
0.00
0.00
0
R
3.78
0.00
0
I
SQL Explorer - Plan Analysis Summary Analysis Report by Cost
Cont/……….
________________________________________
BMC SQL-EXPLORER BATCH SUMMARY ANALYSIS
SEQUENCED BY COST
ANALYSIS DESCRIPTION: PLAN ANALYSIS REPORT
RUN DATE: MON AUG
9 09:01:08 1999
TOTAL
FILTER
METH ACCS
OBJECT
PROGRAM
COLLID
STMT COST
FACTOR
OD
TYPE
________ _________ __________________ ____ ____________ ____________ ____ ____
BXFLQ02 EXPLQ02
232
348927.68
0.66
0
I
BXFLQ02 EXPLQ02
264
3.78
0.00
0
I
BXFLQ02 EXPLQ02
252
2.30
0.07
0
R
BXFLQ02 EXPLQ02
257
2.28
1.08
0
R
BXFLQ02 EXPLQ02
250
0.00
0.00
0
R
BXFLQ02 EXPLQ02
255
0.00
0.00
0
R
BXFLQ02 EXPLQ02
261
0.00
0.00
0
R
******************************** BOTTOM OF DATA ********************************
34
BMC Software Confidential Copyright 2002
SQL Explorer - Plan Analysis SYSPRINT Report Sample Extract
_____________________________________
| COMPOSITE COSTS : 71981.19
| FILTER FACTOR: 0.67
| DM EXAMINED ROWS : 286667
| RDS EXAMINED ROWS: 286667
| I/O COSTS: 4300.20
| CPU COSTS: 619213824.00
| ROWS WHICH PASS FILTER FACTORS: 286666
_____________________________________
BMC184380I-Step No: 1, is Accessing: IUKJXP.EMP, Using Index: IUKJXP.XEMP1.
BMC184384I-This table is the first table accessed in the query: IUKJXP.EMP.
BMC184404W-The table will be accessed using an Index. DB2 will access the
Index first to qualify the rows.
BMC184422W-The Index will be accessed using a Non-Matching Index Scan. DB2
is unable to exactly match the column or columns defined in the
index.
BMC SQL-EXPLORER BATCH ANALYSIS
ANALYSIS DESCRIPTION: PLAN ANALYSIS REPORT
RUN DATE: MON AUG
35
9 09:01:08 1999
BMC Software Confidential Copyright 2002
PAGE: 2
SQL Explorer - Plan Analysis SYSPRINT Report Cont/...
ANALYSIS CONTINUED
________________________________
BMC184428W-The table data pages will be accessed to satisfy this portion of
the query. This requires additional processing overhead.
BMC184456W-The LOCK mode for this access is Intent Share.
BMC184468W-The Data will be retreived Using Sequential Prefetch.
BMC184350I-Sequential Prefetch, which should reduce I/O wait time, will be
used to improve both CPU and I/O processing, use APPTUNE to
determine if the results are desirable.
________________________________________________________________________________
|PLAN TABLE INFORMATION:
________________________________________________________________________________
|STEP
ACCESS IX MATCH LOCK MI PRE
DEGREE JOIN
|NO. METHOD
TYPE
ON COLS MODE AP FETCH ACCESS DEGREE ACCESS NAME
________________________________________________________________________________
|
1
I
N
0
IS 0
S
0
0 IUKJXP.XEMP1
________________________________________________________________________________
|------ SORT NEW ------|
|-- SORT COMPOSITE ---|
|UNIQUE JOIN ORDER GROUP
UNIQUE JOIN ORDER GROUP
OBJECT NAME
________________________________________________________________________________
|
N
N
N
N
N
N
N
N
IUKJXP.EMP
________________________________________________________________________________
36
BMC Software Confidential Copyright 2002
SQL Explorer - Plan Analysis SYSPRINT Report Cont/...
ANALYSIS CONTINUED
BMC184208I-This table uses the EDITPROC: DSN8EAE1.
BMC184216I-The number of Parent Tables referenced by this table is: 1.
BMC184218I-The number of Children Tables referenced by this table is: 3.
BMC184224W-The Tablespace has a LOCKSIZE that does not match your
installation standard, The current LOCKSIZE is Page.
BMC184228W-The Tablespace has a FREEPAGE that does not match your
installation standard, The current FREEPAGE is 0.
BMC184230W-The Tablespace has a PCTFREE that does not match your
installation standard, The current PCTFREE is 5.
BMC184238W-RUNSTATS may need to be run, the last execution of Runstats was:
1997-01-26-08.18.32.
BMC184248I-The tablespace is using compression, the percent of rows
compressed is 0.
37
BMC Software Confidential Copyright 2002
SQL Explorer - Plan Analysis Foreground
------------------------ PATROL SQL-Explorer 3.2.02 JCL Options ------------Command ===> _________________________________________________________________
Review job card; type changes.
Then Press Enter.
Job Card Information.
===> //USERID
JOB (ACCOUNT),'NAME'
===> //*
|-------------------------------------------------------------|
===> //*
| ----------------- Online/Batch Panel ------------------|
===> //*
| COMMAND ===> _____________________________________________ |
|
|
| How do you want to generate Analysis report
|
Output JCL |
|
Output JCL | Run report . . . . 1
1- Foreground
|
or Storag |
2- Batch
|
| PF 1=HELP
2=SPLIT
3=EXIT
9=SWAP
12=CANCEL
|
---------------------------------------------------------------
38
BMC Software Confidential Copyright 2002
Plan Analysis - Foreground Output Sample Extract
Menu Utilities Compilers Help
------------------------------------------------------------------------------BROWSE
IUKBXF.SQLXPLR.SYSPRINT
Line 00000000 Col 001 080
Command ===>
Scroll ===> PAGE
********************************* Top of Data **********************************
-- SQL EXPLORER V3.2.02 PARMS---NEWOBJ
PKGCNT=25
HISTORY=YES
RETAIN=6
MSGLEVEL=ALL
ANALYZE=EXTENDED
SHOWSQL=YES
STATS=YES
READONLY=YES
ONLINE=YES
PLANTBL=NO
RULES=DEFAULT
RPTHDR=YES
DDFLOC=
TITLE=PLAN ANALYSIS REPORT
--ENDOBJ
Run DATE: MON AUG 9 09:19:02 1999
PAGE: 1
39
BMC Software Confidential Copyright 2002
Plan Analysis - Foreground Output
Sample Extract
PLAN INFORMATION FROM SYSPLAN
________________________________
CREATOR: IUKBXF
NAME: BXFLQ02
BIND DATE: 990604
BIND TIME: 11135447
Plan Valid: YES
PLAN OPERATIVE: YES
BOUND BY: IUKBXF
USING QUALIFIER: IUKJXP
CURSOR ISOLATION: READ STABILITY
ACQUIRE: FIRST USE
RELEASE: COMMIT
DEGREE : (1 )
BIND CONSIDERATIONS:
BMC184172I-This SQL and Authorities will be checked at Execution Time, this
can adversely impact performance, Review with DBA on this usage.
BMC184188S-The OBJECT was bound with the ACQUIRE option of USE, which is
recommended for concurrency.
BMC184192S-The OBJECT was bound with the RELEASE option of COMMIT, which is
recommended for concurrency.
BMC184204S-The PLAN was bound with DEGREE(1), this disables Parallel I/O on
partitioned TS.
______________________________________________________________________
40
BMC Software Confidential Copyright 2002
SQL Explorer - Examples

Plan / Package Analysis Using ISPF

41
Compare
BMC Software Confidential Copyright 2002
SQL Explorer - Package Compare
------------------------- PATROL SQL-Explorer 3.2.02 Main Menu --------------Command ===> _________________________________________________________________
Select an option
2
1. Plan Analysis
2. Package Analysis
3. DBRM Analysis
4.
5.
6.
7.
Mismatch Analysis
Impact Analysis
DBRMlib / loadlib compare
Migrate Statistics
C. Initialize Variables
E. PATROL SQL-Explorer Rules Help
Copyright (C) 1996-1999 BMC Software, Inc.
PATROL SQL-Explorer is a Trademark of BMC Software, Inc.
42
BMC Software Confidential Copyright 2002
SQL Explorer - Package Compare
-------------------- PATROL SQL-Explorer 3.2.02 Package Analysis Panel ---------Command ===> _________________________________________________________________
Specify the Analysis options below and press Enter.
Package Name . . . . . . . . . ACT*
Collection ID . . . . . . . . . . *
Contoken . . . . . . . . . . . . .
Package Version . . . . . . .
(optional)
(optional)
Would you like to query DB2 for a selection list? YES (Yes/ No)
43
BMC Software Confidential Copyright 2002
SQL Explorer - Package Compare
-------------------- PATROL SQL-Explorer 3.2.02 Object Selection Panel ---------Command ===> _________________________________________________________________
Make a selection and press enter twice to continue.
Specify desired action next to Object name
A = Analysis
C = Compare
B = Analysis & Compare
_ Display detailed Information (Y / N)
_ Apply action to all objects
cmd
_
_
44
hist
___
___
Package
Name
ACT621DL
ACT621DM
BMC Software Confidential Copyright 2002
Creator
Name
JKS
JKS
U = Undo
SQL Explorer - Package Compare
-------------------- PATROL SQL-Explorer 3.2.02 Package Analysis Panel ---------Command ===> _________________________________________________________________
Specify the Analysis options below and press Enter to continue.
Maintain History . . . . . . . . YES (Yes/No) History needed to use Compare Option
Histories Retained . . . . . . . 4
(0-99)
Report Message Level . . . 2
(1-All / 2-Severe / 3-Warning / 4-Informational)
Analysis Level . . . . . . . . . . 1
(1-Extended / 2-Detailed)
Show SQL . . . . . . . . . . . . . YES (Yes / No)
Include Statistics. . . . . . . . NO (Yes / No)
Read Only Application . . NO (Yes / No)
Online Application . . . . . YES (Yes / No)
Use existing Plan Table .
NO (Yes / No)
Predicate Report . . . . . .
YES (Yes / No) works only with Extended Analysis
Columns Break . . . . . . .
NO (Yes / No)
Degree . . . . . . . . . . . . . .
___ (blank / 1 / any)
Select the BMC Message Option:
1
1. Produce the rule messages with the message text, the bmc message ID and the severity
2. Produce the rule messages with just the message text.
3. Bypass all rule message processing
45
BMC Software Confidential Copyright 2002
SQL Explorer - Package Compare
------------------ PATROL SQL-Explorer 3.2.02 Package Analysis Panel -----Command ===>
_________________________________________________________________
Specify the Analysis options below and press Enter to continue.
Rules Table Owner . . . . DEFAULT
Plan Table Owner . . . . . QUAL
Table Qualifier . . . . . OA1A
Display Text Length . . . 72 (length range from 60 trough 132)
Plan Table Summary . . . . YES (Yes / No)
STATS Summary . . . . . . NO (Yes / No)
INDEX Summary . . . . . . NO (Yes / No)
Select Summary Report Sort Order
1
1. Object, Statement
2. Cost
3. Object, Cost
Generate Report Headings . YES (Yes/No)
Analysis Report
Title . . . . PACKAGE REPORT
46
BMC Software Confidential Copyright 2002
SQL Explorer - Package Compare
--------------------- SQL-Explorer 3.2.02 Compare Panel ---------Command ===>
_________________________________________________________________
Specify the Compare options below and press Enter to continue.
Report Differences Only .
SQL % Matching Threshold
Cost Translation Rate . .
Cost Summary Report . . .
47
BMC Software Confidential Copyright 2002
.
.
.
.
YES
100
0.001
NO
(Yes
(0 Cost
(Yes
/ No)
100)
per Timeron or Service Unit
/ No)
SQL Explorer - Package Compare
------------------------ PATROL SQL-Explorer 3.2.02 JCL Options ----Command ===> _________________________________________________________
Review job card; type changes.
Then Press Enter.
Job Card Information.
===> //IUKBXFA JOB ([email protected]),'ANOTHER'
===> //
CLASS=A,MSGCLASS=X,MSGLEVEL=(1,1),
===> //*
===> //*
Counter . . . . . . . . . . . . 3
Output JCL file . . . . . . . . 'IUKBXF.SQLXPLR.CNTL(SQL####)'
Summary Dataset or Sysout . . . 'IUKBXF.SQLXPLR.CNTL(SQL####)’
Sysout Class . . . . . . . . .
(used only when SUMMARY = SYSOUT)
SYSPRINT Dataset or Sysout . . . 'IUKBXF.SQLXPLR.CNTL(SQL####)’
Sysout Class . . . . . . . . .
(used only when SUMMARY = SYSOUT)
48
BMC Software Confidential Copyright 2002
SQL Explorer - Package Compare
------------------------------------------------------------------------------BROWSE
IUKBXF.SQLXPLR.SYSPRINT
Line 00000018 Col 001 080
Command ===>
Scroll ===> CSR
Package Information from History
____________________________________
Name: IUKBXFPG
Collection Id: BFPK
New Hist Date : 1999-08-09-12.50.03.192770
Comp Hist Date: 1998-09-18-06.51.33.808985
____________________________________
Match Found for Query:145
________________________________
Step
Access IX Match Lock MI Pre
Degree Join
No. Method
Type
On Cols Mode AP fetch Access Degree Access Name
1 First Table
I
N
0
IS 0
0
0 IUKBXF .XSUPP3
1 First Table
R
N
0
IS 0
S
0
0
.
1 Difference
** _
_
__ _
*
_
_ ********.********
Step
------ Sort New -----No
Unique Join Order Group
1
N
N
N
N
1
N
N
N
N
1 No Diff.
_
_
_
_
________________________________
49
BMC Software Confidential Copyright 2002
--- Sort Composite --Unique Join Order Group
N
N
N
N
N
N
N
N
_
_
_
_
Object Name
IUKBXF .SUP
IUKBXF .SUP
____________
SQL Explorer - Package Compare
------------------------------------------------------------------------------BROWSE
IUKBXF.SQLXPLR.SYSPRINT
Line 00000046 Col 001 080
Command ===>
Scroll ===> CSR
Match Found for Query:145
________________________________
Step
Access IX Match Lock MI Pre
Degree Join
No. Method
Type
On Cols Mode AP fetch Access Degree
2
Nest Loop
I
N
1
IS 0
L
0
0
2 Merge Scan
R
N
0
IS 0
S
0
0
2 Difference
** _
*
__ _
*
_
_
Step
------ Sort New -----No
Unique Join Order Group
2
N
N
N
N
2
N
Y
N
N
2 Difference
_
*
_
_
________________________________
50
BMC Software Confidential Copyright 2002
Access Name
IUKBXF .ANI_INDEX
.
********.********
--- Sort Composite --Unique Join Order Group
N
N
N
N
N
Y
N
N
_
*
_
_
Object Name
IUKBXF .ANI
IUKBXF .ANI
____________
SQL Explore - Package Compare
------------------------------------------------------------------------------BROWSE
IUKBXF.SQLXPLR.SYSPRINT
Line 00000060 Col 001 080
Command ===>
Scroll ===> CSR
________________________________
Stats Data for Query:145
________________________________
Step Estimated
Filter
IX
IX
Index Cluster Clus Clus FirstKey
Full
No. Costs
Factor
Cols Len Count Ratio
terd ting Card
Card
2 0.016393
0.016393
1
4
8
95
N
N
30
2 0.016393
0.016393
0
0
4
0
0
2
_________**** **** **** *****
***** **** ****
*******
****
Differences Encountered: 2
_________________________________________
End of Analysis Processing
_________________________________________
Compare Object Summary:
Plans......: 0
DBRMs......: 0
Packages...: 1
Statements.: 5
Differences: 2
_________________________________________
51
BMC Software Confidential Copyright 2002
SQL Explorer - Examples

Plan / Package Analysis Using ISPF

52
Mismatch Analysis
BMC Software Confidential Copyright 2002
SQL Explorer - MisMatch Analysis
------------------------- PATROL SQL-Explorer 3.2.02 Main Menu --------------Command ===> _________________________________________________________________
Select an option
4
1. Plan Analysis
2. Package Analysis
3. DBRM Analysis
4.
5.
6.
7.
Mismatch Analysis
Impact Analysis
DBRMlib / loadlib compare
Migrate Statistics
C. Initialize Variables
E. PATROL SQL-Explorer Rules Help
Copyright (C) 1996-1999 BMC Software, Inc.
PATROL SQL-Explorer is a Trademark of BMC Software, Inc.
53
BMC Software Confidential Copyright 2002
SQL Explorer - MisMatch Analysis
--------------- PATROL SQL-Explorer 3.2.02 HOST Variable Mismatch Panel ------Command ===>
Specify the Analysis Option below and Press Enter.
Object type
Object Name
. . . . . . . 1
(1-Plan / 2-Package)
. . . . . . . IUKBXFPG
Creator Name . . . . . . . USERID
Collection ID
. . . . . . BFPK
Column Break
. NO
Report Differences only . YES
Generate Report Headings . YES
( For Package Only )
(Yes/No)
(Yes/No)
(Yes/No)
Report Title . MISMATCH ANALYSIS
Would you like to query DB2 for a selection list? YES (Yes/ No)
54
BMC Software Confidential Copyright 2002
SQL Explorer - MisMatch Analysis
------------------------------------------------------------------------------BROWSE
IUKBXF.SQLXPLR.SYSPRINT
Line 00000025 Col 001 080
Command ===>
Scroll ===> CSR
______________________________
SQL Text from: IUKBXFPG Statement No: 154
______________________________________________________________________
SELECT SUPPLIER_ID , WAREHOUSE_ID , START_DATE ,
NAME
INTO :SUPPLIER.S-SUPPLIER-ID , :SUPPLIER.S-WAREHOUSE-ID , :SUPPLIER.S-START-DATE ,
:SUPPLIER.S-NAME
FROM SUPPLIER
WHERE SUPPLIER_ID = :SUPPLIER.S-SUPPLIER-ID
______________________________________________________________________
SRC
Column
Data Type
Length
_____ __________________________________________
__________________ _______
DB2
SUPPLIER_ID
INTEGER
4
HOST
SUPPLIER.S-SUPPLIER-ID
INTEGER
4
DB2
WAREHOUSE_ID
SMALLINT
2
HOST
SUPPLIER.S-WAREHOUSE-ID
SMALLINT
2
DB2
START_DATE
DATE
10
HOST
SUPPLIER.S-START-DATE
CHAR
10 NO IMPACT
55
DB2
HOST
NAME
SUPPLIER.S-NAME
CHAR
CHAR
20
5
DB2
HOST
SUPPLIER_ID
SUPPLIER.S-SUPPLIER-ID
INTEGER
INTEGER
4
4
BMC Software Confidential Copyright 2002
MISMATCH
SQL Explorer - Examples

Plan / Package Analysis Using ISPF

56
SQLX TSO Edit Macro
BMC Software Confidential Copyright 2002
Edit Macro Initialization
------------------------- PATROL SQL-Explorer 3.2.02 Main Menu --------------Command ===> _________________________________________________________________
Select an option
C
1. Plan Analysis
2. Package Analysis
3. DBRM Analysis
4.
5.
6.
7.
Mismatch Analysis
Impact Analysis
DBRMlib / loadlib compare
Migrate Statistics
C. Initialize Variables
E. PATROL SQL-Explorer Rules Help
Copyright (C) 1996-1999 BMC Software, Inc.
PATROL SQL-Explorer is a Trademark of BMC Software, Inc.
57
BMC Software Confidential Copyright 2002
Edit Macro Initialization
---------------------Online Datasets -------------------------------------Command ===> _________________________________________________________________
(Use #### in member ame to have variable generated into member name)
Counter . . . . . . . 1
Output JCL file
. . . ‘GTM.SQLXPLR.CNTL(SQLX####)’
Summary Dataset
. . . 'GTM.SQLXPLRB.SUMMARY(SQLX####)'
Sysprint Dataset . . . 'GTM.SQLXPLB.SYSPRINT(SQLX####)'
Sysin Dataset. . . . . 'GTM.SQLXPLRB.SYSIN(SQLX####)'
DBRM / Load library Compare Only - Detail file, not a partitioned data set
Detail Dataset . . . . 'GTM.SQLXPLR.DETAIL'
Migrate Stats - Migrate update file, not a partitioned data set
Update Dataset . . . . 'GTM.SQLXPLR.UPDATE'
58
BMC Software Confidential Copyright 2002
SQLX
Line Command
File Edit Confirm Menu Utilities Compilers Test Help
------------------------------------------------------------------------------EDIT
PSS.TOOLS.PLI(EXPLQ02) - 22.00
Columns 00001 00072
Command ===> sqlx dbbk
Scroll ===> CSR
022900 /******************************************************************/
023000 /* SQL CURSORS
*/
023100 /******************************************************************/
qq3200 EXEC SQL DECLARE EXPLQ02 CURSOR FOR
023300 SELECT EMPNO, FIRSTNME, LASTNAME
023400 FROM IUKJXP.EMP WHERE SEX <> 'F'
023500 GROUP BY EMPNO, FIRSTNME, LASTNAME
qq3600 ORDER BY LASTNAME;
023700
023800 /*****************************************************************/
023900 /* SQL RETURN CODE HANDLING
*/
024000 /*****************************************************************/
024100
024200 EXEC SQL WHENEVER SQLERROR
GOTO DBERROR;
024300 EXEC SQL WHENEVER SQLWARNING GOTO DBERROR;
024400 EXEC SQL WHENEVER NOT FOUND CONTINUE;
024500
024600 1/*******************************************************************/
024700 /* MAIN PROGRAM ROUTINE
*/
024800 /*******************************************************************/
59
BMC Software Confidential Copyright 2002
SQLX
Analysis Options
----------------SQLX Analysis Parameters ---------------------------------Command ===> __________________________________________________________________
Specify the Analysis Options below and Press Enter.
SSID . . . . . . . . . . DBBK
DB2 Catalog Access . . . 1
(1-Direct / 2-Indirect)
Analysis Creator Name. . iukjxp
Extended Analysis. . . . 1
1. Extended
2. Detailed
Source Language. . . . . 2
1.
2.
3.
4.
5.
6.
Host Variable Indicator. 1
1. : (Standard)
2. # (Natural)
COBOL (VS-COBOL II, COBOL/390)
PL/I
C
ASSEMBLER
FORTRAN
SQL Text Only
For Additional Information - PRESS PF1.
60
BMC Software Confidential Copyright 2002
Catalog Manager Integration


BMCEXPLORE Command

on PLAN and PACKAGES

Online or Batch (depending on a switch)
BIND on Plan/Packages/DBRMs


61
option to include SQL Explorer Step after BIND
DESCRIBE Plan/Package

Command : GET Stmt-Nr

Analyse via SQLX or BATCH JOB
BMC Software Confidential Copyright 2002
Catalog Manager Integration


62
EXP ONLINE Switch

determines ONLINE or BATCH mode

use SEE command to check

OFF=BATCH, ON=ONLINE

to switch : place cursor on the value and hit ENTER. This
toggles the value between ON and OFF
Analyze a DML statement

program source or any sql text

Block QQ command to mark SQL text to be analyzed

SQLX ssid
BMC Software Confidential Copyright 2002
Using the SEE Command
DBBK-R ------------
CATALOG MANAGER 6.1.01 PRIMARY MENU
SELECT ACTION AND TYPE OBJECT INFORMATION.
ACTION
0.
1.
2.
3.
4.
5.
6.
7.
(L)
(S)
(C)
(G)
(O)
(D)
(M)
(Q)
OBJ TYPE
63
BMC Software Confidential Copyright 2002
THEN PRESS ENTER.
LIST CATALOG OBJECTS (BLANK SAME AS 0)
SEARCH FOR CATALOG OBJECTS
CREATE OBJECTS
GRANT PRIVILEGES
CATALOG MANAGER OPTIONS PROCESSING
DB2 COMMANDS
MAINTAIN LOGS MENU
LIST SQL FOR EDIT & EXECUTION
DB 11. DB DATABASE
12. SG STOGROUP
13. TB TABLE
14. VW VIEW
15. IX INDEX
QUALIFIER DEMOCIS0
ATTACHED TO DBBK
COMMAND ===> see
--------------------
16.
17.
18.
19.
20.
TS
SU
SY
PG
CI
TABLESPACE
SYSPRIVUSER
SYNONYM
PACKAGE
COLLECTION
21.
22.
23.
24.
25.
PL
AL
US
CO
DM
PLAN
ALIAS
USER
COLUMN
DBRM
26.
27.
28.
29.
ST
LO
CK
PR
STRINGS
LOCATION
CHECKS
PROCS
ENTER
Using the SEE Command (continued)
DBBK-R -------------------CURRENT
CURRENT
CURRENT
CURRENT
CURRENT
CURRENT
CURRENT
CURRENT
CURRENT
USER .
DATE . . :
DEGREE
:
PACKAGESET:
RULES . . :
SERVER
:
SQLID . . :
TIME . . :
TIMESTAMP :
TIMEZONE :
. . . . . :
DB2 SPECIAL REGISTERS
--------------------------
03/30/1999
1
DB2
DBBK
RDAEAS3
03:39 PM
1999-03-30-15.39.13.368870
00000060000
RDAEAS3
--------------------------- CATALOG MANAGER SWITCHES -------------------------ALLCAPS IS
DBCS IS
DEBUG IS
CAPS IS
SHARED DATA
PROFILE IS
OFF
OFF
OFF
OFF
OFF
COMMAND ===>
64
BMC Software Confidential Copyright 2002
DROP IS
TRACE IS
ADBCS IS
LABEL IS
HDDL AUTH
UTILITIES
ON
OFF
OFF
OFF
OFF
ORIGINAL
EXP ONLINE OFF
SQL FLOW IS ON
ENTER
Using the SEE Command (continued)
DBBK-R -------------------CURRENT
CURRENT
CURRENT
CURRENT
CURRENT
CURRENT
CURRENT
CURRENT
CURRENT
USER .
DATE . . :
DEGREE
:
PACKAGESET:
RULES . . :
SERVER
:
SQLID . . :
TIME . . :
TIMESTAMP :
TIMEZONE :
. . . . . :
DB2 SPECIAL REGISTERS
--------------------------
03/30/1999
1
DB2
DBBK
RDAEAS3
03:39 PM
1999-03-30-15.39.13.368870
00000060000
RDAEAS3
--------------------------- CATALOG MANAGER SWITCHES -------------------------ALLCAPS IS
DBCS IS
DEBUG IS
CAPS IS
SHARED DATA
PROFILE IS
OFF
OFF
OFF
OFF
OFF
COMMAND ===>
65
BMC Software Confidential Copyright 2002
DROP IS
TRACE IS
ADBCS IS
LABEL IS
HDDL AUTH
UTILITIES
ON
OFF
OFF
OFF
OFF
ADVANCED
EXP ONLINE OFF
SQL FLOW IS ON
F3
Catalog Manager Integration
DBBK-R -----------Command ===>
CATALOG MANAGER 6.1.01 Primary Menu
Select action and type object information.
Action
0.
1.
2.
3.
4.
5.
6.
7.
(L)
(S)
(C)
(G)
(O)
(D)
(M)
(Q)
Obj type
BMC Software Confidential Copyright 2002
Then press Enter.
List catalog objects (blank same as 0)
Search for catalog objects
Create objects
Grant privileges
CATALOG MANAGER options processing
DB2 Commands
Maintain logs menu
List SQL for edit & execution
TB 11. DB Database
12. SG Stogroup
13. TB Table
14. VW View
15. IX Index
Qualifier IUKBXF.%
Attached to DBBK
66
--------------------
16.
17.
18.
19.
20.
TS
SU
SY
PG
CI
Tablespace
SysPrivUser
Synonym
Package
Collection
21.
22.
23.
24.
25.
PL
AL
US
CO
DM
Plan
Alias
User
Column
DBRM
26.
27.
28.
29.
ST
LO
CK
PR
Strings
Location
Checks
Procs
Catalog Manager Integration
DBBK-R ------------------------Command ===>
------------------- Row 22 of 37
Scroll ===> CSR
01
CMD will show commands for this list. Type command and press ENTER
Lst: AL CA CK CL CO DB DS FK IC IX KC MX PA PG PK PL RI SG SY TM TS TT UA US VW
LIKE: IUKBXF.%
C Table Name
Database Tblspace Cols PK
Rows
Pages
----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v--IUKBXF.PET_SHOP
DGDCD06 D06PETTS
7
98
4
IUKBXF.PLAN_TABLE
P3PSS213 BMCPPLAN 43
7
1
IUKBXF.PP346
BXFDB1
BXFTS8
2
0
0
IUKBXF.PP346B
BXFDB1
BXFTS7
3
1
0
0
IUKBXF.PREDICATE_TABLE
P3PSS213 BMCPPLAN 19
52
2
IUKBXF.PROJ
DBPERF01 BXFTS81P
8
1
19
1
IUKBXF.PROJACT
DBPERF01 BXFTS81P
5
3
77
1
IUKBXF.STRUCTURE_TABLE
P3PSS213 BMCPPLAN 10
34
1
PGIUKBXF.SUPPLIER
DGDCD06 D06SUPTS
9
61
2
IUKBXF.TDATE
BXFDB1
BXFTS6
2
0
0
IUKBXF.TDECIMAL
BXFDB1
BXFTS5
2
3
1
IUKBXF.TEST
DBPERF01 BXFTS81D
5
-1
-1
IUKBXF.TEST_TAB_PART
BXFDB1
BXFTS3
5
37
4
IUKBXF.TESTPK
BXFDB1
BXFTS7
3
1
0
0
IUKBXF.TEST2
DBPERF01 BXFTS81D
5
-1
-1
IUKBXF.WAREHOUSE
DGDCD06 D06WARTS
6
19
1
******************************* Bottom of data ********************************
67
BMC Software Confidential Copyright 2002
Table List
Catalog Manager
Command - BMCEXPLORE
DBBK-R -----------------------Command ===>
Package List
-------------------- Row 1 of 1
Scroll ===> CSR
02
CMD will show commands for this list. Type command and press ENTER
Lists: AL CA CI IM IX PA PL SY TB TS UA US VW
QUALIFIER: TABLE=IUKBXF.SUPPLIER
CmdCollection Id
Name
Version
Owner
Bnd Date
Valid Oper
----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v--BFPK
IUKBXFPG
IUKBXF
1998-09-18
Y
Y
******************************* Bottom of data ********************************
DBBK-R -----------------------Command ===>
-------------------- Row 1 of 1
Scroll ===> CSR
01
CMD will show commands for this list. Type command and press ENTER
Lists: AL CA CI IM IX PA PL SY TB TS UA US VW
LIKE: IUKBX%
CmdCollection Id
Name
Version
Owner
Bnd Date
Valid Oper
----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v--BMCEXPLORE
IUKBXFPG
IUKBXF
1998-09-18
Y
Y
******************************* Bottom of data ********************************
68
BMC Software Confidential Copyright 2002
Package List
Catalog Manager Integration
DBBK-R -----------------Command ===>
JCL Dataset
SQL/EXPLORER for Packages
------------------------
. . . 'IUKBXF.BMCCAT.UTILITY(SQLEXPLO)'
Set JCL options N
(Y/N - Change options for creating JCL)
Build Job . . Y
(Y/N - Create JCL, save in JCL dataset)
Edit Dataset . Y
(Y/N - Edit JCL dataset)
Submit . . . . Y
(Y/N - Submit JCL dataset)
--------------------------- SQL/Explorer Options --------------------------History . . . N
(Y/N Y-Update history tables)
Retain . . . . 0
Number of analysis results to retain
Msglevel . . . 1
(1/2/3/A 1-severe, 2-warnings, 3-info,A-all)
Analyze . . . D
(D/E D-detailed, E-extended)
Show SQL . . . Y
(Y/N Y-include SQL text in the report)
Stats . . . . Y
(Y/N Y-include catalog statistics for objects)
Readonly . . . Y
(Y/N Y-object's access intent is read only)
Online . . . . Y
(Y/N Y-objects usage is online)
Plantbl . . . N
(Y/N Y-use a previous EXPLAIN for analysis)
Rules . . . . DEFAULT Rule id of rules for this analysis
Rpthdr . . . . Y
(Y/N Y-generate report header)
Title . . . . PACKAGE ANALYSIS REPORT
69
BMC Software Confidential Copyright 2002
Catalog Manager
Command - DESCRIBE
DBBK-R -----------------------Command ===>
Package List
-------------------- Row 1 of 1
Scroll ===> CSR
02
CMD will show commands for this list. Type command and press ENTER
Lists: AL CA CI IM IX PA PL SY TB TS UA US VW
QUALIFIER: TABLE=IUKBXF.SUPPLIER
CmdCollection Id
Name
Version
Owner
Bnd Date
Valid Oper
----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v--BFPK
IUKBXFPG
IUKBXF
1998-09-18
Y
Y
******************************* Bottom of data ********************************
DBBK-R -----------------------Command ===>
-------------------- Row 1 of 1
Scroll ===> CSR
02
CMD will show commands for this list. Type command and press ENTER
Lists: AL CA CI IM IX PA PL SY TB TS UA US VW
QUALIFIER: TABLE=IUKBXF.SUPPLIER
CmdCollection Id
Name
Version
Owner
Bnd Date
Valid Oper
----v----1----v----2----v----3----v----4----v----5----v----6----v----7----v--describe
IUKBXFPG
IUKBXF
1998-09-18
Y
Y
******************************* Bottom of data ********************************
70
BMC Software Confidential Copyright 2002
Package List
Catalog Manager Integration
DBBK-R ----------------------Command ===> GET 137
Describe Package
-------------- LINE 36 OF 95
Scroll ===> CSR
Package = BFPK.IUKBXFPG
------------------------------------------------------------------------------WAREHOUSE_ID SMALLINT NOT NULL ,
START_DATE DATE NOT NULL ,
END_DATE DATE )
81 DECLARE ANIMAL TABLE (
ANIMAL_ID INTEGER NOT NULL ,
SUPPLIER_ID INTEGER ,
BREED_ID CHAR ( 6 ) NOT NULL ,
BIRTH_DATE DATE NOT NULL ,
SEX CHAR ( 1 ) NOT NULL ,
SIZE CHAR ( 1 ) NOT NULL ,
TEMPERAMENT CHAR ( 5 ) NOT NULL ,
COLOUR CHAR ( 20 ) NOT NULL ,
SP_MARKINGS CHAR ( 30 ) NOT NULL ,
ENTRY_TIME TIMESTAMP )
137 DECLARE SUPPLIER_CURSOR CURSOR FOR
SELECT SUPPLIER_ID ,
WAREHOUSE_ID ,
NAME
FROM
SUPPLIER
WHERE NAME LIKE 'A%'
ORDER BY NAME
145 DECLARE JOIN_CURSOR CURSOR FOR SELECT S.SUPPLIER.ID,
71
BMC Software Confidential Copyright 2002
BIND with SQL Explorer Analysis
DBBK-R ------------------------COMMAND ===>
JCL DATASET
. . .
SET JCL OPTIONS
BUILD JOB . . .
EDIT DATASET . .
SUBMIT . . . . .
.
.
.
.
DSN UTILITY
-------------------------------
'IUKBXF.BMCCAT.UTILITY(DSNCMDS)'
.
.
.
.
.
.
.
.
.
.
.
.
N
Y
Y
Y
(Y/N
(Y/N
(Y/N
(Y/N
------------------------------
-
CHANGE OPTIONS FOR CREATING JCL)
CREATE JCL, SAVE IN JCL DATASET)
EDIT JCL DATASET)
SUBMIT JCL DATASET)
UTILITY OPTIONS
-----------------------------
INCLUDE PATROL SQL-EXPLORER(TM) STEP N (Y/N - ANALYZE EXPLAIN RESULTS OF BIND)
72
BMC Software Confidential Copyright 2002
Catalog Manager Integration of
SQL Explorer - Exercise 3
BMCEXPLORE command
 SQLX Edit Macro Processing
 BIND with SQL Explorer Analysis

73
BMC Software Confidential Copyright 2002
Bericht aus dem Projekt
74
BMC Software Confidential Copyright 2002
Start und Ausgangslage
 Oktober 2001: Probleme mit einem Online Monitor zur
Überwachung der Börsenapplikation
 Dieser Monitor verbraucht 9% der Gesamten CPU
 Kosten Pro Jahr ca. 1‘000‘000 Euro
 Davon sind ca. 65 Programme betroffen
 Die Daten befinden sich in 24 Tabellen
75
BMC Software Confidential Copyright 2002
Hilfsmittel und Tools
 Omegamon
 SQL- Explorer
 Catalog Manager
76
BMC Software Confidential Copyright 2002
Vorgehensweise
 Ermittlung der 10 CPU Intensivsten Programme
 Analyse mit SQL-Explorer
 Vorschläge ausarbeiten für Verbesserungen
 Begleitung des Projektes und Schulung des
SQL-Explorers
77
BMC Software Confidential Copyright 2002
Resultate bis 31.12.01
 Eliminierung von 276 Missmatchs in den Programmen
 Erstellung von 16 neuen Indizes
 Löschen von 28 Indizes die nicht verwendet wurden
 Verbesserung des durchschnittlichen CPU-Verbrauchs
der Applikation um 32% ohne Codeänderung
 Anpassen des Lockings bei 3 Tabellen
78
BMC Software Confidential Copyright 2002
Descargar

Plan/Package Analysis using SQL Explorer