Transcript - OOWidgets

A List of
DB2 Top Ten Lists
In which we ponder numerous DB2 topics for
learning and amusement
Craig S. Mullins
VP, Data Strategy
NEON Enterprise Software
http://www.neon.com
http://www.CraigSMullins.com
TAKE CONTROL
The Top Ten Lists
And now, from the home office in
Sugar Land, Texas… a series of
DB2 Top Ten lists about various
topics ranging across the
following subjects:
• Performance
• Coding
• Design
• Administration
• Management
• Features
• Tools
TAKE CONTROL
© 2009 Craig S. Mullins
1
Top Ten SQLCODEs to Memorize
1. 000 / +100
2. -904
3. -818
4. -101
5. -104
6. -530 / -532
7. -803
8. -913
9. -922
10. -805
successful / “no more rows”
resource unavailable
timestamp mismatch
SQL statement too complex
illegal symbol in SQL stmt.
RI constraint violation
unique violation (duplicate data)
deadlock or timeout
authorization failure
program not found in plan
TAKE CONTROL
© 2009 Craig S. Mullins
2
Top Ten DB2 V8 Features
1. Multi-row FETCH and INSERT
2. 2M SQL Limit
3. Partitioning changes
up to 4096 Partitions, table-based partitioning, clustering separation
4.
5.
6.
7.
8.
9.
10.
Stage 1 for Unlike Data Types
Data Partitioned Secondary Indexes
Sequences
Materialized Query Tables
Dynamic Scrollable Cursors
Recursive SQL
Online Schema Change
TAKE CONTROL
© 2009 Craig S. Mullins
3
Top Ten DB2 9
for z/OS
Features
1. Universal Table Spaces
and no more creation of simple table spaces (still supported if you have them tho’)
2.
3.
4.
5.
6.
7.
8.
9.
10.
11.
Rows arranged for variable data
Index on Expressions
ORDER BY and FETCH FIRST on Subselects
Clone Tables
Not Logged Table Space …but beware
BINARY / VARBINARY …at last, a true binary data type!
Native SQL Procedure Language …no more C compiler
SELECT from UPDATE, DELETE, MERGE
Implicitly Hidden Columns
And, OK, I guess I have to include it, pureXML
TAKE CONTROL
http://www.db2portal.com/blog.html
© 2009 Craig S. Mullins
4
Top Ten Significant Features of
DB2’s First 20+ Years
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Packages (V2.3)
Data Sharing (V4)
Referential Integrity (V2.3)
Type 2 Indexes (V4)
Segmented Table Spaces (V2.3)
Triggers and UDFs (V6)
Stored Procedures (V4)
Multiple Buffer Pools (V3…)
Breaking many limits (V8)
DATE / TIME data types (V1.3)
TAKE CONTROL
© 2009 Craig S. Mullins
5
Top Ten Most Common DB2 Performance
Problems
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
PEBCAK
Poorly coded SQL
Improper indexing
Bad program design
Bachelor programming syndrome
Improperly defined buffer pools
Index / table space needs to be reorganized
Improperly designed database structures
Copied code syndrome
RUNSTATS not up-to-date (or not even run)
TAKE CONTROL
© 2009 Craig S. Mullins
6
Top Ten Steps to Proper Indexing
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Index by workload, not by object
Build indexes based on predicates
Index most-heavily used queries
Index important queries
Index to avoid sorting (GROUP BY, ORDER BY)
Create indexes for uniqueness (PK, U)
Create indexes for foreign keys
Consider adding columns for IXO access
Don’t arbitrarily limit number of indexes
Be aware of I/U/D implications
TAKE CONTROL
© 2009 Craig S. Mullins
7
Top Ten Most Common Physical
DB2 Database Design Mistakes
3.
Relying on the defaults
Not basing the physical on a logical model
Over-relying on logical design
4.
Normalization problems
1.
2.
(Over-normalized or too denormalized)
5.
6.
7.
8.
9.
Not enough indexes
Indexing by table, not by workload
Too much (or not enough) free space
Failing to plan for data purging or archiving
Failure to share data
(not Data Sharing, but sharing data!)
10.
Kludging
TAKE CONTROL
© 2009 Craig S. Mullins
8
Top Ten Most Common
Misunderstandings About DB2
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
“There’s a problem with DB2!”
Using nulls can save space
DB2 is a “database”
DB2 is self-managing!
SQL is simple to learn and code (properly)
If it uses an index it doesn’t need ORDER BY
Extents don’t matter anymore
Using BP0 only performs OK
PIECESIZE matches up IX and TS partitions
It depends!
TAKE CONTROL
© 2009 Craig S. Mullins
9
Top Ten Most Under-Utilized
Features of DB2
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Real Time Stats
CASE statements
Table Expressions
Distinct Types
LOBs
zIIPs
User-Defined Functions
Triggers
Recursive SQL
Date/Time Data Types
TAKE CONTROL
© 2009 Craig S. Mullins
10
Top Ten Extinct* DB2 Features
* or soon to be extinct
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Type 1 indexes
The RCT
Host variables w/o a colon
SROD
Data set passwords
Simple table spaces
Manual stored procedure registration
Non-DRDA distribution
Hiperpools (and VPs in data spaces)
Denormalization (because of MQTs?)
TAKE CONTROL
© 2009 Craig S. Mullins
11
Top Ten DB2 Annoyances
1.
2.
3.
Changing the SQL Terminator for Triggers
No EXPLAIN parameter for CREATE TRIGGER
Fumbling thru the SQL Reference for Syntax
(specifically for SELECT)
4.
5.
6.
7.
8.
9.
10.
SQL examples are too simple in the manuals
Lack of 100% Platform Compatibility
Managing Tables with LOBs
DSNZPARM documentation
Utilities cost extra
The database object is strangely implemented
It is so good that people take it for granted!
TAKE CONTROL
LOAD, UNLOAD with LOBs V7 V8: PK22910
© 2009 Craig S. Mullins
12
Top Ten SQL Mistakes
1.
2.
3.
Syntax
The “flat file” mentality
Ignorance of New Features
(such as CASE and table expressions)
4.
5.
6.
Fear factor
Copied code syndrome
Not coding for performance
(ignorance of Stage 1/Stage 2, indexing, etc.)
7.
8.
9.
10.
Too many columns! .
Not running the most efficient SQL statement
Improper “existence” checking
The Never-Ending Story!
TAKE CONTROL
© 2009 Craig S. Mullins
13
What’s Wrong With This SQL?
SELECT LAST_NAME, FIRST_NAME,
JOB_CODE, DEPT, PHONENO
FROM DSN8810.EMP
WHERE JOB_CODE = ‘A’
AND
DEPT = ‘MIS’;
TAKE CONTROL
© 2009 Craig S. Mullins
14
Top Ten SQL Mistakes
1.
2.
3.
Syntax
The “flat file” mentality
Ignorance of New Features
(such as CASE and table expressions)
4.
5.
6.
Fear factor
Copied code syndrome
Not coding for performance
(ignorance of Stage 1/Stage 2, indexing, etc.)
7.
8.
9.
10.
Too many columns!
Not running the most efficient SQL statement
Improper “existence” checking
The Never-Ending Story!
TAKE CONTROL
© 2009 Craig S. Mullins
15
Top Ten Buffer Pool
Tuning Steps
1.
2.
3.
4.
5.
6.
Do not use one large BP0 – spread the wealth!
Use BP0 for system objects only
Separate BP for indexes and table spaces
Set DWQT to enable trickle writing
Separate random and sequential
Use VPSEQT to control sequential usage
(increase for sequential, decrease for random)
7.
8.
9.
“Peg” (small) frequently used tables in memory
Assign DSNDB07 to BP7 – tune it for sorting
Do not undersize hiperpool if you use them
(ROT: setup HP to be 2x – 3x the size of the VP it backs up)
10.
Consider dedicated buffer pools
(for “special” table spaces)
TAKE CONTROL
© 2009 Craig S. Mullins
16
Top Ten DBA Excuses
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
It depends.
RTFM
“Did you fill out the form?”
“I’m busy.”
“It’s working as designed – leave me alone.”
IBM says…
You couldn’t possibly understand why…
“That’s what they said in class.”
“Our standards say we do it this way.”
“Because I’m the DBA, that’s why!”
TAKE CONTROL
© 2009 Craig S. Mullins
17
Top Ten Programmer Excuses
3.
“There’s something wrong with DB2!”
“But I copied that from another program.”
“It worked yesterday.”
4.
“Isn’t there something you can do to make it work?”
1.
2.
5.
6.
7.
8.
9.
10.
“But I can do that better in C; Java; etc.”
“It works that way in Oracle; Access; etc.”
“It’s too late in the project to re-write that.”
“But I heard somewhere it works this way.”
“Why do I have to BIND every time?”
“DB2 is a hog.”
TAKE CONTROL
© 2009 Craig S. Mullins
18
Top Ten Management Excuses
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
“We’re over-budget.”
“The project is under-funded.”
“Work smarter, not harder.”
“You better work overtime on that.”
“This comes from upper-level management.”
“We’re running behind schedule on this.”
“You can’t be out of the office that long.”
“I read somewhere that isn’t how it works.”
“When I was a DBA/programmer/etc. …
“That is no longer strategic.”
TAKE CONTROL
© 2009 Craig S. Mullins
19
Top Ten Database Trends
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
From Many to “3”
Open Source
The Giant Sucking Sound
Complexity
Heterogeneity
Autonomic/Self-managing
Lies,Marketing
lies, lies, yeah!
The Checkbox Wars
From VLDB to VHDB
Application Centricity
TAKE CONTROL
© 2009 Craig S. Mullins
20
Top Ten Types of DB2 Tools
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
Change Manager
Catalog Visibility
SQL Performance Monitor
BIND and Access Path Analysis
System Performance Monitor
DBA Automation / Utility Automation
Table Editor
Log Analysis
Database Structure Analysis
Application Restart Control
TAKE CONTROL
© 2009 Craig S. Mullins
21
Top Ten Sources for
DB2 Information
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
IBM manuals
IDUG
Local DB2 user groups
DB2 Magazine(s)
Vendor web sites/webinars
(neonesoft.com, etc.)
DB2 books
Web portals
(DB2portal.com, SearchDataManagement.com)
IBM DB2 Developer’s Domain
Consultant web sites
Your co-workers!
TAKE CONTROL
© 2009 Craig S. Mullins
22
Top Ten Books for
DB2 Professionals
1.
2.
3.
4.
5.
6.
7.
8.
9.
10.
DB2 Developer’s Guide
DB2 Developer’s Guide
DB2 Developer’s Guide
DB2 Developer’s Guide
DB2 Developer’s Guide
DB2 Developer’s Guide
DB2 Developer’s Guide
DB2 Developer’s Guide
DB2 Developer’s Guide
DB2 Developer’s Guide
TAKE CONTROL
© 2009 Craig S. Mullins
23
Available Now
Craig S. Mullins
NEON Enterprise Software, Inc.
[email protected]
DB2 Developer’s Guide, 5ed
www.craigsmullins.com/cm-book.htm
http://www.craigsmullins.com
DBA: Practices & Procedures
TAKE CONTROL
www.craigsmullins.com/dba_book.htm
© Copyright, Craig S. Mullins 2009