SQL/MX Release 2.0 Best Practices

Contents 3
Contents
Objectives of This Document
Project Planning
HP Roles and Responsibilities...............................................................................................................................7
Roles.......................................................................................................................................................................7
Technical Lead................................................................................................................................................7
Database Consultant.....................................................................................................................................7
Extract, Transform, and Load (ETL) Specialist...............................................................................................8
Performance Consultant................................................................................................................................8
Database Communication Consultant (ODBC and/or JDBC)..................................................................8
Operations and Systems Specialist................................................................................................................8
Project Activities Guideline for Database Activities
Requirements Definition.......................................................................................................................................9
High-level Architecture and Design...................................................................................................................9
Architecture.....................................................................................................................................................9
Logical Design.................................................................................................................................................9
Query Analysis ...............................................................................................................................................10
Logical Access ..............................................................................................................................................10
Client Tools.....................................................................................................................................................10
Physical Design...................................................................................................................................................10
Denormalization Analysis..............................................................................................................................10
Partition and Primary Key Analysis...............................................................................................................10
Secondary Index Analysis.............................................................................................................................11
Create Catalog and Schema.....................................................................................................................11
Space Analysis...............................................................................................................................................11
Physical Access.............................................................................................................................................11
Insert and Update.........................................................................................................................................11
Manageability...............................................................................................................................................12
Design Guidelines and Considerations
Physical System Configuration Guidelines.......................................................................................................13
Logical Database-design Considerations.......................................................................................................13
Physical Database-design Considerations......................................................................................................13
Primary, Partitioning, and Clustering Keys..................................................................................................13
Primary-key Selection ...................................................................................................................................14
Data Access Efficiency.................................................................................................................................15
Cardinality......................................................................................................................................................15
Partitioning.....................................................................................................................................................16
Design Techniques to Avoid..............................................................................................................................16
Joining Tables on Calculated Columns......................................................................................................16
Over-normalizing Tables...............................................................................................................................16
Load-only Optimized Design........................................................................................................................16
Built-in Application Bottlenecks: Inadequate Design Analysis and Testing..................................................17
Database Sizing Considerations
General Rules for Sizing......................................................................................................................................18
Sizing Questionnaire...........................................................................................................................................18
Quantifiable Metrics .....................................................................................................................................18
For the Database System.............................................................................................................................18
Network Data................................................................................................................................................19
Operational Data..........................................................................................................................................19
For the Database System.............................................................................................................................19
Work and Swap Space Considerations...........................................................................................................19
About SQL/MX Scratch Disks........................................................................................................................19
Scratch Disk Management..........................................................................................................................20
Data-type Considerations.................................................................................................................................20
Consideration for Summary Tables...................................................................................................................20
Considerations for Secondary Indexes............................................................................................................21