bmc software confidential copyright 2002 all rights reserved. plan/package analysis with sql...

BMC Software Confidential Copyright 2002 All rights reserved.

Plan/Package Analysis

with

SQL EXPLORER

Plan/Package Analysis

with

SQL EXPLORER

2 BMC Software Confidential Copyright 2002

My Company Part Ibob Informatik AG www.bob.chIm Tiergarten 108055 Zürich

Mobil: +41 79 405 44 63Telefax: +41 1 451 34 76Email: [email protected]

Geschäftsleitung: Edi Wassmer, VR, CEORoland Frehner, Daniel Harzenmoser

Gründung: 1989Aktienkapital: Fr. 100'000


My Company Part II 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


Introduction to SQL Explorer

Overview / Architecture SQL Explorer - GUI - SQL Explorer - ISPF - Catalog Manager Integration Rules Customization


Reduce Application Development Costs

It is much more cost effective to optimize SQL prior to production implementation

User customizations offer the development opportunity to optimize the SQL in context to the users’ specific environment.

ImplementationImplementation DevelopmentDevelopment

AcceptanceAcceptance

ProductionProduction

Administration

SQL TestingObject MigrationMonitoringJCL Generation

JCL GenerationBackup & Recov.Housekeeping

PerformancePerformancePerformance

AutomationTestingMonitoring

MaintenanceMaintenance

Package


PATROL SQL-Explorer Product Life Cycle

Production cycle

Develop- ment cycle

CodeCode

TestTest

DesignDesign

SQL-ExplorerSQL-Explorer




PackagePackage

Development

Acceptance

Implementation

ProductionMaintenance


Client/Server Architecture for PATROL SQL-Explorer

MVSMVSSERVERSERVER

WindowsWindowsCLIENTCLIENT

TCP/IP protocol stack on TCP/IP protocol stack on the console and MVS server the console and MVS server

machinesmachinesPATROL PATROL SQL-SQL-ExplorerExplorer

(ISPF)(ISPF)

PATROL SQL-ExplorerPATROL SQL-Explorer

(GUI-client)(GUI-client)

• Can be run separately from GUI-client • Analysis of complete plans and packages


Architecture

TCP/IP TransportSNA Transport

MS Windows ConsolePATROL DB Voyager

UNIX, NT, OS/2

Client

Server

MVS

ISPF


Overview

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


Overview - Analyze

Analyzes SQL and database structures Analysis

Expert Rules Recommendations History

Compare Impact Analysis Report Mismatch Analysis SQLX (edit macro invocation) Reports What if scenarios *


Overview - Analyze

Driven by set of expert rules Generates detail and summary reports

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


Overview - Mismatch Analysis

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


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

ASSEMBLER, COBOL, C, FORTRAN and PL/1 User must “Initialize Variables” once to run

Selection “C” from Main Menu


Overview - Reports

Include Basic information from Plan Table Text of predefined expert rules Catalog data, filter factors, relative costs Recommendations

Report message level I - Information -- Condition Code 0 W - Warning --- Condition Code 4 S - Severe --- Condition Code 8

BMC Software Confidential Copyright 2002 All rights reserved.

SQL Explorer - ISPFSQL Explorer - ISPFInteractive approach to SQL Tuning using the SQL Explorer ISPF Interface


Patrol SQL-Explorer ISPF


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 (Direct)


SQL Analysis - 1 of 3

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



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



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


Compare

Compare differences access paths SQL Text catalog statistics

Report Batch / Online Optional show only differences

needs ANALYSIS with MAINTAIN HISTORY YES


Mismatch Analysis

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


TSO Edit Macro

Online Analysis against source code COBOL, PL/I, C, Assembler, FORTRAN

SQLX ssid mark begin and end with QQ issue SQLX on command line to invoke online analysis

Initialize SQL Explorer for online Analysis choose option C from SQL Explorer main menu


SQL Explorer - Examples

Plan / Package Analysis Using ISPF Plan / Package Analysis Compare Mismatch Analysis Impact Analysis SQLX TSO Edit Macro


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. Mismatch Analysis 5. Impact Analysis 6. DBRMlib / loadlib compare 7. 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.



Plan / Package Analysis Using ISPF Plan / Package Analysis


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)



-------------------- 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 U = Undo

_ Apply action to all objects

Plan Creator

cmd hist Name Name

_ ___ ACT621DL JKS

_ ___ ACT621DM JKS



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



-------------------- 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, Statement2. Cost3. Object, Cost

Generate Report Headings . YES (Yes/No) Analysis Report Title . . . . PLAN REPORT


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


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 (4@23),'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


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 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 250 0.00 0.00 0 R BXFLQ02 EXPLQ02 252 2.30 0.07 0 R BXFLQ02 EXPLQ02 255 0.00 0.00 0 R BXFLQ02 EXPLQ02 257 2.28 1.08 0 R BXFLQ02 EXPLQ02 261 0.00 0.00 0 R BXFLQ02 EXPLQ02 264 3.78 0.00 0 I

Continued ……...


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


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 9 09:01:08 1999 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 ________________________________________________________________________________


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.


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


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


Plan Analysis - Foreground OutputSample 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. ______________________________________________________________________



Plan / Package Analysis Using ISPF Compare


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. Mismatch Analysis 5. Impact Analysis 6. DBRMlib / loadlib compare 7. 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.


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 . . . . . . . . . . . . . (optional)

Package Version . . . . . . . (optional)




-------------------- 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 U = Undo

_ Display detailed Information (Y / N)

_ Apply action to all objects

Package Creator

cmd hist Name Name

_ ___ ACT621DL JKS

_ ___ ACT621DM JKS



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



------------------ 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, Statement2. Cost3. Object, Cost

Generate Report Headings . YES (Yes/No) Analysis Report Title . . . . PACKAGE REPORT



--------------------- SQL-Explorer 3.2.02 Compare Panel ---------- Command ===> _________________________________________________________________ Specify the Compare options below and press Enter to continue. Report Differences Only . . YES (Yes / No) SQL % Matching Threshold . 100 (0 - 100) Cost Translation Rate . . . 0.001 Cost per Timeron or Service Unit Cost Summary Report . . . . NO (Yes / No)



------------------------ PATROL SQL-Explorer 3.2.02 JCL Options ----- Command ===> _________________________________________________________ Review job card; type changes. Then Press Enter. Job Card Information. ===> //IUKBXFA JOB (4@23),'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)


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 ------ --- Sort Composite --- No Unique Join Order Group Unique Join Order Group Object Name 1 N N N N N N N N IUKBXF .SUP 1 N N N N N N N N IUKBXF .SUP 1 No Diff. _ _ _ _ _ _ _ _ ____________________________________________



------------------------------------------------------------------------------- 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 Access Name 2 Nest Loop I N 1 IS 0 L 0 0 IUKBXF .ANI_INDEX 2 Merge Scan R N 0 IS 0 S 0 0 . 2 Difference ** _ * __ _ * _ _ ********.******** Step ------ Sort New ------ --- Sort Composite --- No Unique Join Order Group Unique Join Order Group Object Name 2 N N N N N N N N IUKBXF .ANI 2 N Y N N N Y N N IUKBXF .ANI 2 Difference _ * _ _ _ * _ _ ____________________________________________


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



Plan / Package Analysis Using ISPF Mismatch Analysis


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. Mismatch Analysis 5. Impact Analysis 6. DBRMlib / loadlib compare 7. 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.



--------------- PATROL SQL-Explorer 3.2.02 HOST Variable Mismatch Panel ------- Command ===> Specify the Analysis Option below and Press Enter. Object type . . . . . . . 1 (1-Plan / 2-Package) Object Name . . . . . . . IUKBXFPG Creator Name . . . . . . . USERID Collection ID . . . . . . BFPK ( For Package Only )

Column Break . NO (Yes/No) Report Differences only . YES (Yes/No) Generate Report Headings . YES (Yes/No) Report Title . 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

DB2 NAME CHAR 20 HOST SUPPLIER.S-NAME CHAR 5 MISMATCH

DB2 SUPPLIER_ID INTEGER 4 HOST SUPPLIER.S-SUPPLIER-ID INTEGER 4



Plan / Package Analysis Using ISPF SQLX TSO Edit Macro


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. Mismatch Analysis 5. Impact Analysis 6. DBRMlib / loadlib compare 7. 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.


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'


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 /*******************************************************************/


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. COBOL (VS-COBOL II, COBOL/390) 2. PL/I 3. C 4. ASSEMBLER 5. FORTRAN 6. SQL Text Only Host Variable Indicator. 1 1. : (Standard) 2. # (Natural) For Additional Information - PRESS PF1.


Catalog Manager Integration

BMCEXPLORE Command on PLAN and PACKAGES Online or Batch (depending on a switch)

BIND on Plan/Packages/DBRMs option to include SQL Explorer Step after BIND

DESCRIBE Plan/Package Command : GET Stmt-Nr Analyse via SQLX or BATCH JOB



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


Using the SEE Command DBBK-R ------------ CATALOG MANAGER 6.1.01 PRIMARY MENU -------------------- SELECT ACTION AND TYPE OBJECT INFORMATION. THEN PRESS ENTER. ACTION 0. (L) LIST CATALOG OBJECTS (BLANK SAME AS 0) 1. (S) SEARCH FOR CATALOG OBJECTS 2. (C) CREATE OBJECTS 3. (G) GRANT PRIVILEGES 4. (O) CATALOG MANAGER OPTIONS PROCESSING 5. (D) DB2 COMMANDS 6. (M) MAINTAIN LOGS MENU 7. (Q) LIST SQL FOR EDIT & EXECUTION OBJ TYPE DB 11. DB DATABASE 16. TS TABLESPACE 21. PL PLAN 26. ST STRINGS 12. SG STOGROUP 17. SU SYSPRIVUSER 22. AL ALIAS 27. LO LOCATION 13. TB TABLE 18. SY SYNONYM 23. US USER 28. CK CHECKS 14. VW VIEW 19. PG PACKAGE 24. CO COLUMN 29. PR PROCS 15. IX INDEX 20. CI COLLECTION 25. DM DBRM QUALIFIER DEMOCIS0 ATTACHED TO DBBK COMMAND ===> see ENTER


Using the SEE Command (continued)

DBBK-R -------------------- DB2 SPECIAL REGISTERS -------------------------- CURRENT DATE . . : 03/30/1999 CURRENT DEGREE : 1 CURRENT PACKAGESET: CURRENT RULES . . : DB2 CURRENT SERVER : DBBK CURRENT SQLID . . : RDAEAS3 CURRENT TIME . . : 03:39 PM CURRENT TIMESTAMP : 1999-03-30-15.39.13.368870 CURRENT TIMEZONE : 00000060000 USER . . . . . . : RDAEAS3 --------------------------- CATALOG MANAGER SWITCHES -------------------------- ALLCAPS IS OFF DROP IS ON EXP ONLINE OFF DBCS IS OFF TRACE IS OFF SQL FLOW IS ON DEBUG IS OFF ADBCS IS OFF CAPS IS OFF LABEL IS OFF SHARED DATA OFF HDDL AUTH OFF PROFILE IS UTILITIES ORIGINAL COMMAND ===>

ENTER


Using the SEE Command (continued) DBBK-R -------------------- DB2 SPECIAL REGISTERS -------------------------- CURRENT DATE . . : 03/30/1999 CURRENT DEGREE : 1 CURRENT PACKAGESET: CURRENT RULES . . : DB2 CURRENT SERVER : DBBK CURRENT SQLID . . : RDAEAS3 CURRENT TIME . . : 03:39 PM CURRENT TIMESTAMP : 1999-03-30-15.39.13.368870 CURRENT TIMEZONE : 00000060000 USER . . . . . . : RDAEAS3 --------------------------- CATALOG MANAGER SWITCHES -------------------------- ALLCAPS IS OFF DROP IS ON EXP ONLINE OFF DBCS IS OFF TRACE IS OFF SQL FLOW IS ON DEBUG IS OFF ADBCS IS OFF CAPS IS OFF LABEL IS OFF SHARED DATA OFF HDDL AUTH OFF PROFILE IS UTILITIES ADVANCED COMMAND ===> F3



DBBK-R ------------ CATALOG MANAGER 6.1.01 Primary Menu -------------------- Command ===> Select action and type object information. Then press Enter. Action 0. (L) List catalog objects (blank same as 0) 1. (S) Search for catalog objects 2. (C) Create objects 3. (G) Grant privileges 4. (O) CATALOG MANAGER options processing 5. (D) DB2 Commands 6. (M) Maintain logs menu 7. (Q) List SQL for edit & execution Obj type TB 11. DB Database 16. TS Tablespace 21. PL Plan 26. ST Strings 12. SG Stogroup 17. SU SysPrivUser 22. AL Alias 27. LO Location 13. TB Table 18. SY Synonym 23. US User 28. CK Checks 14. VW View 19. PG Package 24. CO Column 29. PR Procs 15. IX Index 20. CI Collection 25. DM DBRM Qualifier IUKBXF.% Attached to DBBK



DBBK-R ------------------------- Table List ------------------- Row 22 of 37 Command ===> 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 ********************************


Catalog Manager Command - BMCEXPLORE

DBBK-R ------------------------ Package List -------------------- Row 1 of 1 Command ===> 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 ********************************

DBBK-R ------------------------ Package List -------------------- Row 1 of 1 Command ===> 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 ------------------ SQL/EXPLORER for Packages ------------------------ Command ===> JCL Dataset . . . '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


Catalog Manager Command - DESCRIBE

DBBK-R ------------------------ Package List -------------------- Row 1 of 1 Command ===> 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 ********************************

DBBK-R ------------------------ Package List -------------------- Row 1 of 1 Command ===> 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 ----------------------- Describe Package -------------- LINE 36 OF 95 Command ===> GET 137 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,


BIND with SQL Explorer Analysis

DBBK-R ------------------------- DSN UTILITY ------------------------------- COMMAND ===> JCL DATASET . . . 'IUKBXF.BMCCAT.UTILITY(DSNCMDS)' 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) ------------------------------ UTILITY OPTIONS ----------------------------- INCLUDE PATROL SQL-EXPLORER(TM) STEP N (Y/N - ANALYZE EXPLAIN RESULTS OF BIND)


Catalog Manager Integration ofSQL Explorer - Exercise 3

BMCEXPLORE command SQLX Edit Macro Processing BIND with SQL Explorer Analysis


Bericht aus dem Projekt


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


Hilfsmittel und Tools

Omegamon

SQL- Explorer

Catalog Manager


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


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

bmc software confidential copyright 2002 all rights reserved. plan/package analysis with sql...

Documents

sql explorer slide

sql explorer ispf

projekten slide

packages slide

access path impact slide

planpackage analysis

sapretail und sapbasis

database structures