DB2 User Group Meeting Presentation 060707

1
(No Transcript)
2
Introduction

• 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

3
Getting Startedwith CA Database Analyzerfor
DB2 for z/OS
Greg Parrish, Senior Consultant gregory.parrish_at_ca
.com
4
Agenda

• 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

5
CA 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
6
Performance 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
7
Database 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

8
Backup and Recovery Solutions for DB2 z/OS
9
CA 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)

10
DB2 Overview
ArchiveLog
Active Log
BSDS
Directory
Work Files
DB2
Catalog
User Applications
TablesIndexes
11
DB2 Catalog
12
DB2 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
13
Database 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
14
CA 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

15
Database 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
16
Extract Procedure
Tablespace
Index
Extract Procedure
DB2 Repository
17
Extract/Action Procedure

• Action procedure is tied to Extract Procedure

Alter Action
Extract HR Database
Reorg Action
Extract Payroll Database
18
Extract 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

19
To enter CA Database Analyzer, enter d or
PDA from Products Main Menu
20
Database Analyzer Main MenuFirst, lets create
an Extract Procedure enter 1 on Command Line
21
Create Extract ProcedureGood rule of thumb is
for Ext Proc name to match Database name
22
Object Specification
23
Object Selection / Help Prompt
24
Lets autobuild our extract procedure enter
a, hit Enter, then hit F3
25
Explode of Extract Procedure
26
Explode Extract Objects / Levels
27
Extract Objects ExplosionAfter reviewing, hit F3
thrice
28
Completed Extract Procedure
29
Lets run our Extract ProcedureEnter s line
command to left of your Extract Procedure
30
For Execution Mode, choose onlineFor
Collection Type, choose fastscan
31
PDA first extracts DB2 catalog information
32
then collects data statistics (from the actual
DB2 VSAM datasets). When you see Execution
Processing complete, type main
33
Reporting

• Two Methods
• Query Facility
• Write your own queries

34
Reporting

• Query Facility
• Reports can be generated online or batch
• Latest Statistics or Historical
• Trend
• Forecast
• Daily, Weekly, Monthly, or Yearly

35
Lets see what PDA has collected!Enter 9 for
Query Facility
36
In the PDA Query Facility, enter 1 to look at
Tablespace/Table info.
37
Lets look at a Space Summary report (Query
6).Enter information as noted below Report
Option Q, Format Option L.
38
Note the detailed info collected! F8 to page
down through your other Tablespaces
Date Time should reflect what you just ran
39
Action Procedure

• Trigger
• Condition and threshold
• Message
• TSO ID
• Ops Console
• CA Log
• Solution
• JCL

40
F3 back to Database Analyzer Main MenuNow lets
create an Action Procedure enter 2 on Command
Line
41
Create Action Procedure - Note Selection List
Masking. Also - provide a name reflecting what
the Action Procedure will be doing
42
Build Action Procedure Utility String - Note both
fast CA utilities and IBM utilities are
supported for easy illustration well use IBM
utils
43
Database 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

44
Hit Enter were taken into IC model JCL, first
one needing symbolic value. Scroll down (F8)
45
Enter yes to indicate full image copyF8 down
to see actual JCL statements (note you could edit
JCL from here)
46
After reviewing, F3 to next Model JCL member
requiring input
47
Next is the Alter enter mjutlal1, hit Enter
48
Enter 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
50
then scroll down (F8) and enter 4 for
SORTNUM.When finished, F3 to next Model JCL
member requiring input
51
which 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
52
Type mjutlrbn to REBIND PLANs hit Enter
53
Type mjutlrpk to REBIND PACKAGEs. Hit Enter
54
Now, 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)
56
For Clustered Percent (CLUSTERRATIO), select
using c. This indicates IX stats will be used
to determine if TS utilities are to be generated.
F3.
57
Finally, type y for Immediate Message. Press
Enter
58
Messaging Options Enter options shown and hit
Enter. Then hit F3.
59
Completed Action ProcedureHit F3 back to
Database Analyzer Main Menu
60
Now, lets tie our Action Procedure to our
Extract Procedure
61
Update Extract Procedure
62
Enter s next to Action Procedure Ties
63
Select your Action Procedure and hit Enter
64
Note Action Tied message.When finished, hit F3
65
Lets specify our Extract Procedure Extract
Options. Provide values as noted below, then hit
Enter
66
Enter all to indicate you want to select all
objects for update, then F3
67
On the Column Statistics Options screen, you can
specify NUMCOLS and COUNT values, used when
collecting cardinality and frequency statistics.
Hit F3.
68
Well set up our Action JCL to generate 1 job
mine will be named DBUSR20A it will generate one
utility per step.
69
Hit F3 twice, back to Extract Procedure Services
screen. Now lets submit our Extract Procedure,
with Action Procedure attached
70
First, 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)
71
Use values below then hit Enter...
72
Now type x and hit F3 to get back into PDA
73
On 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
74
PDA lets you know that it generated a reorg job
thanks to the immediate message you specified!
75
PDA will Extract fresh DB2 catalog information
76
Collect data statistics from the underlying DB2
VSAM datasets
77
and then do its Action Processing based on the
Conditions weve defined
78
Finally, as we specified, PDA will update the DB2
Catalog with latest statistics.
79
Lets look at our real live Action JCL! F2
P.3.4
80
F8 note one utility per step (easier
restartability)
81
Database 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

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

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

84
Questions?
85
CA 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

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

87
Thank you
Please complete the evaluation survey.