Course ID 00071769
HP product number U5082S
Course format ILT
Course length 2 days
Skill level Foundational
Delivery languages English
Related certifications
In preparation for these exams
Selected items from this course are included in these exams:
Register for this course.
Find this course in the Training calendar and click the “Register” link in the last column to begin your registration.

Course description

This course teaches practical techniques for designing HP NonStop™ SQL/MP databases. You 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.

Who should attend

• Database designers
• Database administrators
• Application programmers
• Analysts

Prerequisites

For complete prerequisites and requirements to achieve any of the related certifications or upgrade paths, see the certification description on the HP ExpertOne website.

Topics

  • 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 the following:
      o 6 CPUs (2 minimum), 16 MB each
      o NonStop Kernel operating system, C30, D20, D30, or later for NonStop K-series servers, or any Gxx version for NonStop S-series servers
      o NonStop Transaction Monitoring Facility (TMF), NonStop SQL/MP
    • PC compatible workstation with the following:
      o Pentium class processor
      o 4 MB memory (including 640 KB conventional memory)
      o VGA monitor (minimum)
      o 11.5 MB disk space
      o Windows operating system
      o Access to the NonStop server for each student

Objectives

After completing this course, you should be able to do the following:
  • 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

How to register

View the HP ExpertOne Global Training Calendar to register for the trainings offering that best meets your needs.

Policies, fees and cancellations

Course fees may vary and are established and collected by the training center delivering the course. Cancellation fees may apply. Contact your HP Authorized Training Partner for their respective policies.