Remotely assisted instructional learning (RAIL)
Instructor-led training (ILT)
Onsite dedicated training (OST)
Price
USD $1,000
CAD $1,100
Course overview
Learn practical techniques for designing HP NonStop™ SQL/MP databases in this 2-day course. You will learn how to map a logical design into a physical design, accommodating such physical performance considerations as partitioning, VARCHAR, SLACK, and disk caching. You will gain valuable practical experience with SQL database design. The course is 60 percent lecture and 40 percent hands-on labs using HP servers.
Prerequisites
Concepts and Facilities for HP NonStop Systems (U4147S) and
At the conclusion of this course you should be able to:
Identify site requirements that impact physical design decisions
Identify the characteristics and selection criteria of various table types
Understand primary keys, considerations when slecting them, and performance implications
Identify advantages and disadvantages of implementing indexes and their performance implications
Understand the implications of using default parameters and how they impact design decisions
Understand the advantages and disadvantages for using partitioned tables and the different methods available to implement them
Understand the impact of disk cache and I/O performance
Describe the advantages and disadvatages for using the VARCHAR data type and SLACK parameter
Identify tools that can be used to assist in physical database design
Benefits to you
Optimize your database performance by knowing how to minimize disk input/output, processor time, and disk storage
Know the factors that influence physical database design decisions and apply them to your data center environment
Gain valuable hands-on experience with designing databases, implementing and modifying the design, and measuring whether a design change improves application performance
Course outline
Overview
Application design matrix
Preliminary life cycle phases
Logical and physical database designs
Module 1 - Factors that Influence Physical Database Design
Site requirements impacting physical design decisions
Module 2 - Table Type Selection
Table type characteristics and selection criteria
Number of I/Os associated with accessing each table type
Module 3 - Primary Key Selection
Primary key characteristics
Selection considerations and performance implications
Generic locking
Additional selection considerations
Module 4 - Secondary Index Selection
Index characteristics
Advantages and disadvantages for selecting and implementing indexes
Performance implications when using indexes
Which columns in a table should and should not be indexed
Using temporary indexes
Module 5 - Create Table Attributes
Default parameters and implications
BUFFERED, LOCKLENGTH, and BLOCKSIZE parameters
DCOMPRESS, SERIALWRITES, and VERIFIEDWRITES parameters and how they impact design decisions
Module 6 - Using Partitioned Tables
Advantages and disadvantages for using partitioned tables
Methods available to implement partitioned tables
Module 7 - Other Considerations
Advantages and disadvantages for using the VARCHAR data type and SLACK parameter
Disk cache and I/O performance impact
NULL values
Other performance influences
Module 8 - Physical Design Tools
SQLCI
Display statistics
FILEINFO Command
PUP
DSAP/DCOM
Measure
Onsite Delivery Equipment Requirements
One or two NonStop servers, CLX or above with:
6 CPUs (2 minimum), 16 MB each
NonStop Kernel operating system, C30, D20, D30, or later for NonStop K-series servers, or any Gxx version for NonStop S-series servers
