Title: DB2 User Group Meeting Presentation 060707
1(No Transcript)
2Introduction
- Welcome!
- Alisa Claypool
- DTS Systems Software Specialist, DB2 for z/OS
Systems Support - Presentation Slides will be available on the DTS
website soon. - Please complete your Evaluation Surveys!
- Upcoming DTS Customer Forum
- DTS Service Catalog Customer Forum, June 28
- http//www.dts.ca.gov/calendar/registration/defa
ult.asp?key53eid1316 - Look for more DTS events coming soon at
http//www.dts.ca.gov/calendar
3Getting Startedwith CA Database Analyzerfor
DB2 for z/OS
Greg Parrish, Senior Consultant gregory.parrish_at_ca
.com
4Agenda
- CA Database Management
- Database Performance Management
- Database Administration
- Database Backup and Recovery
- Value Pack for DB2
- CA Database Analyzer for DB2 for z/OS
- Overview
- Product Flow
- Automation Setup Execution and Action
Procedures - Automation Execution
- Additional feature discussion
5CA Database Management
- CA Database Performance Management Suite
- Ensures optimal database performance and data
availability - CA Database Administration Suite
- Provides intelligent administration that reduces
complexity, time and effort - CA Database Backup/Recovery Suite
- Increases backup efficiency and automates
recoverability, increasing IT responsiveness to
the business
Enterprise Management Portal
Database Management
Database Performance Management
Database Administration
Database Backup and Recovery
CA COMMON SERVICES
6Performance Management Solutions for DB2 z/OS
Application Performance
CA-Insight?
CA Subsystem Analyzer?
CA Index Expert?
CA Data Compressor
CA Detector?
CA Database Analyzer?
Database Performance
CA Rapid Reorg
7Database Administration Solutions for DB2 z/OS
- Object/Database Maintenance
- CA RC/Query?
- CA RC/Update?
- CA RI Editor?
- CA Partition Expert
- CA Fast Index
- Data Extraction/Archive
- CA RC/Extract?
- CA Fast Unload
- CA Fast Load
- Migration Suite
- CA RC/Migrator?
- CA RC/Compare?
- CA RC/Merger?
- CA RC/Extract?
- CA Fast Unload
- CA Fast Load
- Database Security
- CA RC/Secure
8Backup and Recovery Solutions for DB2 z/OS
9CA DB2 Value Pack
- Included when you license any one of the CA DB2
Products - Script processing (Batch Processor)
- Interface into JES, processes data sets
containing SQL to dynamically call DB2 utilities
and application programs, execute DB2 (DSN)
commands - Thread termination/Online ZPARMS
- Terminates problem DB2 threads safely without
disrupting applications or end users - Can perform dynamic DSNZPARM updates set
DSNZPARMS without recycling DB2 - RI management
- Tracks, organizes, manages DB2 system-and
application-enforced RI relationships - Utility job generation
- Convenient and efficient facility for submitting
on demand utility requests - Interactive SQL processing (ISQL)
- Test SQL statements without pre-compiling and
executing the entire program - DB2 command processor
- Scrollable, offers command syntax help, stores
last 15 commands - Alternate catalog mapping (ACM)
- Redirects catalog queries to shadow tables, or to
views defined over the system catalog tables - Explain Table Conversions
- Convert the PLAN_TABLE, DSN_STATEMNT_TABLE,
DSN_FUNCTION_TABLE explain tables on a specific
DB2 subsystem to the DB2 V8 format by generating
ALTER statements to add or alter columns as
necessary highlvl.SRCLIB(EXPLCNV)
10DB2 Overview
ArchiveLog
Active Log
BSDS
Directory
Work Files
DB2
Catalog
User Applications
TablesIndexes
11DB2 Catalog
12DB2 Databases are getting huge!
Competitive Environments
Driving Increase Internet Transaction
Rates Detailed Data Historical Analysis Partner
Information
Future
Cost Reductions of Storage
Present
Competitive pressures are driving companies to
capture significantly more detailed data that in
turn is feeding the tremendous database growth
Past
20x 50x
10x
x
13Database Complexity
ERP Applications SAP PeopleSoft
BAAN Partners Legacy Systems Data
Warehousing Multiple Databases Multiple Platforms
Database Objects
Complex Relationships
Coordinated Systems
Unstructured Data
Database Complexity
Advances in DBMS Technology
14CA Database Analyzer a.k.a. PDA
-
- DBA In A Can
- Automatically manage DB2 objects
- Collect and maintain object statistics
- PDA stats can replace RUNSTATS utility
- Monitor/forecast DASD Usage
- Validate object integrity
- Schedule and execute utilities
- Object reporting
- Automate the repair of problems
- DB2 Dataset management
15Database Analyzer Processing Flow
Execution Procedure
MVS Cat.
Extract Proc.
Report Proc.
Reports
DB2 Cat.
Action Proc.
PDA Stats
Online Queries
Cond
Log
Msg.
Action JCL
16Extract Procedure
Tablespace
Index
Extract Procedure
DB2 Repository
17Extract/Action Procedure
- Action procedure is tied to Extract Procedure
Alter Action
Extract HR Database
Reorg Action
Extract Payroll Database
18Extract Procedures
- Definition of DB2 table spaces and indexes to
extract info on. - DB2 optimizer Stats
- all stats used by DB2
- Optionally Update the DB2 Catalog
- DASD space statistics
- I.e Space Used, Extents, allocated, rows, pages
- DB2 Page statistics
- I.e. Freespace, Freepage, Hole space, dropped
space - Page Audit
- Replacement for IBM runstats utility
19To enter CA Database Analyzer, enter d or
PDA from Products Main Menu
20Database Analyzer Main MenuFirst, lets create
an Extract Procedure enter 1 on Command Line
21Create Extract ProcedureGood rule of thumb is
for Ext Proc name to match Database name
22Object Specification
23Object Selection / Help Prompt
24Lets autobuild our extract procedure enter
a, hit Enter, then hit F3
25Explode of Extract Procedure
26Explode Extract Objects / Levels
27Extract Objects ExplosionAfter reviewing, hit F3
thrice
28Completed Extract Procedure
29Lets run our Extract ProcedureEnter s line
command to left of your Extract Procedure
30For Execution Mode, choose onlineFor
Collection Type, choose fastscan
31PDA first extracts DB2 catalog information
32then collects data statistics (from the actual
DB2 VSAM datasets). When you see Execution
Processing complete, type main
33Reporting
- Two Methods
- Query Facility
- Write your own queries
34Reporting
- Query Facility
- Reports can be generated online or batch
- Latest Statistics or Historical
- Trend
- Forecast
- Daily, Weekly, Monthly, or Yearly
35Lets see what PDA has collected!Enter 9 for
Query Facility
36In the PDA Query Facility, enter 1 to look at
Tablespace/Table info.
37Lets look at a Space Summary report (Query
6).Enter information as noted below Report
Option Q, Format Option L.
38Note the detailed info collected! F8 to page
down through your other Tablespaces
Date Time should reflect what you just ran
39Action Procedure
- Trigger
- Condition and threshold
- Message
- TSO ID
- Ops Console
- CA Log
- Solution
- JCL
40F3 back to Database Analyzer Main MenuNow lets
create an Action Procedure enter 2 on Command
Line
41Create Action Procedure - Note Selection List
Masking. Also - provide a name reflecting what
the Action Procedure will be doing
42Build Action Procedure Utility String - Note both
fast CA utilities and IBM utilities are
supported for easy illustration well use IBM
utils
43Database Analyzer Model Library
- Critical to Action Procedures
- Necessary to generate correct JCL
- PDS members represent
- Skeleton JCL
- Commands
- Anything
- Create your own !!!
- Fully customizable
- Can use IF/THEN logic in JCL
- Could, for example, write out to TAPE if gt 250
cylinders etc. - Copy CAs highlvl.MODEL to your own dataset
- Change Database Analyzer profile to point to your
model library
44Hit Enter were taken into IC model JCL, first
one needing symbolic value. Scroll down (F8)
45Enter yes to indicate full image copyF8 down
to see actual JCL statements (note you could edit
JCL from here)
46After reviewing, F3 to next Model JCL member
requiring input
47Next is the Alter enter mjutlal1, hit Enter
48Enter the calculations shown hit Enter. This
will make PRIQTY 20 larger than the objects
current size, SECQTY 10 of that. F3 to next
49...which is the REORG model. Enter sysda for
UNIT
50then scroll down (F8) and enter 4 for
SORTNUM.When finished, F3 to next Model JCL
member requiring input
51which is our 2nd image copy. F8 and type yes
for FULL, as before. Then hit Enter to register
the entry, and hit F3 to continue
52Type mjutlrbn to REBIND PLANs hit Enter
53Type mjutlrpk to REBIND PACKAGEs. Hit Enter
54Now, enter o to right of Condition Type
(OR-type Condition)
55(Note you can change the Boolean values can also
browse the Condition!) Select the Commonly
Used Conditions shown. Then, scroll down (F8)
56For Clustered Percent (CLUSTERRATIO), select
using c. This indicates IX stats will be used
to determine if TS utilities are to be generated.
F3.
57Finally, type y for Immediate Message. Press
Enter
58Messaging Options Enter options shown and hit
Enter. Then hit F3.
59Completed Action ProcedureHit F3 back to
Database Analyzer Main Menu
60Now, lets tie our Action Procedure to our
Extract Procedure
61Update Extract Procedure
62Enter s next to Action Procedure Ties
63Select your Action Procedure and hit Enter
64Note Action Tied message.When finished, hit F3
65Lets specify our Extract Procedure Extract
Options. Provide values as noted below, then hit
Enter
66Enter all to indicate you want to select all
objects for update, then F3
67On the Column Statistics Options screen, you can
specify NUMCOLS and COUNT values, used when
collecting cardinality and frequency statistics.
Hit F3.
68Well set up our Action JCL to generate 1 job
mine will be named DBUSR20A it will generate one
utility per step.
69Hit F3 twice, back to Extract Procedure Services
screen. Now lets submit our Extract Procedure,
with Action Procedure attached
70First, lets allocate our output dataset (that
will hold both our Extract JCL and our Action
JCL). Hit F2 enter P.3.2 enter info below
(your database)
71Use values below then hit Enter...
72Now type x and hit F3 to get back into PDA
73On Submit-Build JCL Parms screen, enter
Collection Type f, Create Action JCL y,
Action JCL Destination d, and enter the PDS you
just created. Then hit Enter
74PDA lets you know that it generated a reorg job
thanks to the immediate message you specified!
75PDA will Extract fresh DB2 catalog information
76Collect data statistics from the underlying DB2
VSAM datasets
77and then do its Action Processing based on the
Conditions weve defined
78Finally, as we specified, PDA will update the DB2
Catalog with latest statistics.
79Lets look at our real live Action JCL! F2
P.3.4
80F8 note one utility per step (easier
restartability)
81Database Analyzer Tips Tricks - 1
- In a real-life scenario, youd more likely run
your Extract Procedure in Batch (rather than
Online) - Batch Processor (BP) can be used to write out
your Extract Procedure as a PDS member - so you dont need to get into Database Analyzer
every time you want to run an Extract Procedure - Both your Extract Procedure JCL and your Action
Procedure JCL (the resulting REORG utility
string) should be written to a PDS that is under
the control of your Enterprise Job Scheduler - Good practice with Model JCL is to copy the
install PDS, and customize the copied members
to conform to your shop standards
82Database Analyzer Tips Tricks - 2
- To collect the most accurate statistics possible,
you should STOP the objects included in your
Extract Procedure - Can use CP Utility Code in your Action
Procedure utility string, which utilizes the CA
DB2 Command Processor (DB2C) - Dont forget to add in a START CP utility code
afterward! - As you continue to gather PDA statistics, your
PDA user tables will grow, and it is advisable to
run Statistics Maintenance - Automatic Statistics Deletion option lets you
specify that DB2 object statistics should be
automatically deleted when certain conditions are
met, e.g. - Execution Frequency gt 030
- Purge Stats Older Than gt 060 Days
- Save Latest Stats for Each Object gt Y
83And one more thing Dataset Facility
- Your best friend for those 307am calls!
- Dataset Facility (PDF) is a stand-alone facility
that automates the administration procedures
involved in moving user-created DB2 data sets - The easy-to-use online display of data set
information lets novice and experienced users
identify data sets that could benefit from moving - Use of the Dataset Facility avoids the more
complicated administration tasks involved in
moving data sets by migration or through a REORG - With the Dataset Facility, you can
- Move data sets from one DASD volume to another
- Change primary and secondary size allocations for
data sets - Change using type between STOGROUP and VCAT, and
change STOGROUP or VCAT name - View DASD usage information, including volume
summaries (with volume freespace and data sets
per volume) and data set details - By using the Dataset Facility features, you can
immediately implement any changed data set
alteration
84Questions?
85CA Database Management Strategy
- Advanced technology, integration and automation
for large complex databases - Stand alone or integrated offering extended
functionality and value - Multi-platform support for
- DB2 for z/OS, DB2 for Linux, Unix, Windows, IMS,
Oracle, MS SQL Server databases - For more information visit
- www.ca.com/databasemanagement
- Your local CA sales representative
86For More Information
- SupportConnect
- eNews signup http//supportconnectw.ca.com/enews.a
sp - Product news and PTFS downloads
- Contact your Customer Advocate for further
information - New database management whitepapers
- ca.com/db
- Multi-Platform support and trial downloads
- Database Command Center - supports DB2 LUW
z/OS, Ingres, Oracle, SQL Server, IMS on z/OS,
Linux, UNIX, Windows - Intelligently automates database admin tasks,
regardless of platform - Unicenter Database Performance Management -
supports DB2 LUW, DB2 for Linux for z/OS, Oracle,
MS SQL Server, Sybase - Unicenter SQL-Station v7 release - supports DB2
z/OS, DB2 LUW, Oracle, MS SQL Server, Sybase - To obtain
- The Unicenter Database Management trial CD, DB2
for z/OS v8 poster or DB2 for z/OS v8 reference
guide, contact your local CA sales representative
or call 1-866-573-3435
87Thank you
Please complete the evaluation survey.