At a glance
View schedule & enroll Sorted by: location or date
Course number U5082S
Length 2 days
Delivery method Virtual Instructor-Led Training (VILT)
Instructor-led training (ILT)
Onsite dedicated training (OST)
Price USD $1,600
CAD $1,760
*Courses are supported in the delivery formats above, but are not necessarily scheduled in every delivery format listed. Please click the schedule links at the top of the page to see which delivery formats are currently scheduled.

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.


  • Concepts and Facilities for HP NonStop Systems (U4147S) and
  • HP NonStop SQL/MP Essentials (U4181S) and
  • Basic understanding of the relational approach to database design


  • Database designers
  • Database administrators
  • Application programmers
  • Analysts

Ways to save

Course objectives

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


  • 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
  • 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

  • Display statistics
  • FILEINFO Command
  • PUP
  • 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
    • NonStop Transaction Monitoring Facility (TMF), NonStop SQL/MP
  • PC compatible workstation with:
    • Pentium class processor
    • 4 MB memory (including 640 KB conventional memory)
    • VGA monitor (minimum)
    • 11.5 MB disk space
    • Windows operating system
    • Access to the NonStop server for each student

U5082S G.00