Michigan DB2 Users Group Chris Skelnik Sr' DBA TransUnion, LLC cskelniktransunion'com

1
Green Light, Go!Building Your Own DBA Dashboard
Michigan DB2 Users GroupChris SkelnikSr.
DBATransUnion, LLCcskelnik_at_transunion.com
2
5 Bullet Points

• The expanded role of the DBA - trending,
  forecasting, fortune-telling
• Understanding the problem stove piped
  performance reporting and monitoring
• Build your own Dashboard without buying anything
• Use your Dashboard to answer DBA, application,
  and (most importantly) management questions
• Pictures speak louder than words - the benefits
  of a visual tool

3
Our Environment DB2 for z/OS

• 13 DB2 for z/OS Subsystems
• 7 production subsystems
• 6 test subsystems
• Spread across 10 different LPARs
• Some 2-way Data-Sharing
• 9 subsystems are non-DS
• All Home-Grown Applications
• Mix of Java, COBOL, Assembler
• Mix of CICS, Distributed, Batch

4
Our Environment Tools

• DB2 for z/OS Monitors
• Use online monitors for real-time analysis
• Run batch reports for troubleshooting and
  investigation
• 3rd-Party DB2 Tools
• Query, edit, create DB2 data and objects
• Data, application, and subsystem analytics
• Home-Grown Tools
• DASD Usage, Schema Info, Control Card Generator,
  etc
• Our Overall Environment is Fairly Normal / Typical

5
Typical Monitoring

• Active Monitoring
• Triggers/alerts for critical problems
• Console alerts, emails, problem tickets, etc.
• Reactive Monitoring
• Post-problem investigations
• Determine what type of report to run, analyze
  report, rerun
• Snapshot Monitoring
• Certain batch reports run on a regular basis
• Reports stored as datasets

6
The Discomfort Zone

• New Systems
• Huge growth in DASD
• Huge growth in objects and DB2 datasets
• New Management
• Asking more tough questions about our DB2
  systems
• Talk is cheap show me the analysis
• New Responsibilities
• More emphasis on trending and forecasting
• Expected to still do DDL, code reviews, plus all
  things new

7
The Analysis Challenge

• Each Tool is Unique
• Different layouts, terminology, syntax
• Different data sources and data stores
• Reporting is Mainframe-Centric
• Many reports are generated via batch jobs
• Output stored in mainframe datasets
• Lots of DBA Interaction
• DBAs generate jobs to run reports
• DBAs interpret numbers

8
The Dashboard Concept

• Make it Centralized
• Bring all that information to one centralized
  place
• Make it Accessible
• Dont have to login to the mainframe to access
  the data
• Accessible to DBAs, developers, managers, etc.
• Make it Meaningful
• High-level representation of data
• Low-level details available if necessary
• Make it Graphical
• One graph is worth a thousand reports

9
Get Ready

• Build It vs. Buy It
• No vendor has comprehensive tools
• Needed to be customizable for our environment
• Needed to be meaningful for our organization
• We went with in-house development
• Internally vs. Externally Developed
• Application team has programming resources
• DBAs have existing tool and SQL knowledge
• We went with DBAs developing the Dashboard
• Lots of complex SQL
• Inherent authority to access data

10
Get Set

• Location
• Intranet application
• Web-enabled is the ideal delivery solution
• Existing infrastructure no cost to deploy
• Materials
• Lots of options for building dynamic web-based
  systems
• We chose PHP
• Open source language no cost to develop
• Mortar
• DB2 will be the glue that bonds all the materials
  together
• Using DB2 no cost for data storage

11
Go!

• Full Throttle, Right?
• Start with what is easy
• Add more complexity as you go
• Practice What You Preach
• Work on SQL first
• Design of GUI should be based on the data
  available
• Green Light, Go!
• Lets see the Dashboard

12
Our DBA Dashboard
13
Our DBA Dashboard
14
Our DBA Dashboard
15
Our DBA Dashboard
16
Our DBA Dashboard
17
Our DBA Dashboard
18
Our DBA Dashboard
19
Our DBA Dashboard
20
Our DBA Dashboard
21
Our DBA Dashboard
22
Our DBA Dashboard
23
Our DBA Dashboard
24
Our DBA Dashboard
25
Our DBA Dashboard
26
Our DBA Dashboard
27
Our DBA Dashboard
28
Next Steps

• Expanding the Dashboard
• Add more data sources
• DB2 Realtime Statistics
• Other 3rd-party tool data
• Expand to include other DB2 for z/OS subsystems
• Expand to include DB2 on other platforms
• Support and Training
• Get other DBAs involved in PHP coding
• Documentation

29
Conclusion

• Monitors and Tools Are Great Resources
• Managing the data they produce can be challenging
• Best analysis is done over time
• Dashboard Concept
• Centralize the data in one spot
• Present it in meaningful ways to various users
• You Can Built It Yourself
• Basic HTML/PHP knowledge along with SQL
• Build it, and they will come

30
Resources

• PHP
• http//www.php.net
• DHTML Popup Library (JavaScript Routines)
• http//www.macridesweb.com/oltest
• PHP/SWF Charts
• http//www.maani.us/charts
• All of These Are Free!