HP NonStop SQL/MP database management

Prerequisites

• Concepts and Facilities course
• HP NonStop SQL/MP Essentials
• Solid understanding of systems, application areas, and daily operations for both systems and applications
• Understanding of the database environment at your business

Audience

• Database administrators
• System managers
• People responsible for availability of HP NonStop SQL/MP databases & applications

Ways to save

Benefits to you

• Review of the HP NonStop SQL/MP environment
• HP NonStop SQL/MP installation and version management
• Creating HP NonStop SQL/MP database base objects
• Loading, copying, appending, and reorganizing data
• DataLoader/MP
• Updating SQL/MP statistics
• Managing SQL/MP embedded programs
• Altering SQL/MP database objects
• Moving SQL/MP database objects
• Removing SQL/MP database objects
• Managing database protection and recovery using HP NonStop Transaction Manager/MP (TM/MP)
• Backing up and restoring SQL/MP database objects
• Database security management
• Tools for monitoring performance
• Enhancing DP2 performance
• Managing sorts
• Controlling SQL/MP processes

Next Steps

• HP NonStop SQL/MP Query Design for Performance U4183S

Course outline

• HP NonStop SQL/MP environment
• Processes, structure, and objects of the HP NonStop SQL/MP environment
• Locating the SQL/MP system catalog
• Determining the number and location of SQL/MP catalogs on a system
• Identifying and locating the objects registered in an SQL/MP database
• Related products and additional tables they can create in the SQL/MP system catalog
• Lab Exercise (30 minutes)
• HP NonStop SQL/MP Environment
• Locate the system catalog
• Locate all user catalogs on a system
• Identify the objects in the PERSNL database and their dependent objects
• Identify what SQL/MP processes you have running for your session
• Create a user catalog
• Installation and version management
• Identifying the system requirements for HP NonStop SQL/MP
• Installation and initialization process of SQL/MP software
• Version management for SQL/MP objects
• Managing the migration of SQL/MP from one HP NonStop Kernel operating system release to another
• Compatibility of SQL/MP software versions within a network
• Lab Exercise (30 minutes)
• Installation and Version Management
• Identify the versions of HP NonStop SQL/MP, the catalog, and several objects on your system
• Creating HP NonStop SQL/MP database objects
• Planning and creating NonStop SQL/MP database objects
• Performance considerations and guidelines for creating SQL/MP database objects
• Query metadata for the SQL/MP objects that have been created
• Lab Exercise (1.5 hours)
• Creating SQL/MP Database Objects
• Copying, loading, appending, reorganizing, and converting data
• Converting an Enscribe database to a NonStop SQL/MP database
• Use the COPY utility to load, extract, or display data
• Using the LOAD or APPEND utilities to load data
• Re-organizing data in key-sequenced tables and indexes
• Archiving data from the database
• Checking the referential integrity of the database
• Lab Exercise (1 hour)
• Using Copy, Load and Append Utilities
• Lab Exercise (40 minutes)
• Reorganizing Data
• Lab Exercise (40 minutes)
• Converting an Enscribe Database to an SQL/MP Database
• DataLoader/MP
• How DataLoader/MP can be used to load and maintain database data
• Components of DataLoader/MP (main logic, library routines, and user exits)
• Building customized DataLoader/MP processes
• Running DataLoader/MP processes
• Creating DataLoader/MP processes to load the data given a data loading scenario
• Updating NonStop SQL/MP statistics
• NonStop SQL/MP statistics
• Statistics for partitioned tables
• How the optimizer uses statistics to calculate selectivity for predicates
• How to update statistics
• When to update statistics
• Testing the impact of updating statistics in a test environment or the production environment
• Querying the catalog for statistical information
• Copying the production system statistics to the development system
• Statistics performance degradation
• Lab Exercise (30 minutes)
• Updating SQL/MP Statistics
• Managing NonStop SQL/MP embedded programs
• Explicit compilation and automatic recompilation of NonStop SQL/MP programs
• SQL/MP program run-time environment
• Compiling COBOL and C embedded SQL programs in the Guardian and Open System Services (OSS) environments using native and non-native compilers
• SQL/MP compiler options
• Creating, implementing, and testing a compilation strategy for an SQL/MP program
• Determining what actions make an SQL/MP program invalid
• Detecting invalid SQL/MP programs
• Detecting auto-recompiling SQL/MP programs
• Lab Exercise (45 minutes)
• Managing SQL/MP Embedded Programs
• Plan and implement different compiler options using two scenarios
• Altering NonStop SQL/MP database objects
• Altering NonStop SQL/MP objects and the impact to the database environment
• Methods for partitioning tables and indexes
• SQL/MP Format 2 Enabled objects and Format 1 and Format 2 partitions
• Lab Exercise (45 minutes)
• Altering SQL/MP Database Objects
• Moving NonStop SQL/MP database objects
• Process for moving NonStop SQL/MP objects
• Methods to move SQL/MP objects (Re-create and Load, ALTER MOVE, DUP, Backup and Restore)
• Lab Exercise 9.1 (30 minutes)
• Moving SQL/MP Database Objects
• Lab Exercise 9.2 (30 minutes)
• Moving SQL/MP Embedded Programs
• Removing NonStop SQL/MP database objects
• Removing NonStop SQL/MP objects
• Removing objects using the DROP command and PURGE and CLEANUP utilities
• Removing data from a table or a partition of a table using PURGEDATA utility
• Dropping a partition of a table using ALTER TABLE É DROP PARTITION
• Dropping a partition of an index using ALTER INDEX É DROP PARTITION
• TACL GOAWAY command
• Lab Exercise (1 hour, 15 minutes)
• Removing SQL/MP Database Objects
• Managing database protection and recovery
• NonStop Transaction Manager/MP (TM/MP) methods of protection and recovery
• TM/MP considerations for NonStop SQL/MP objects
• Protecting SQL/MP database with TM/MP
• Online dump strategy
• Recovery strategy
• TM/MP recovery of SQL/MP objects and their catalog
• TM/MP recovery of SQL/MP objects without recovering their catalog
• Using licensed copy of SQLCI2 to insert, update, or delete metadata
• Backing up and restoring NonStop SQL/MP database objects
• File-mode BACKUP and RESTORE options that can be used with NonStop SQL/MP objects
• Developing protection strategies with the BACKUP and RESTORE utilities for SQL/MP objects
• Volume-mode options of the BACKUP and RESTORE utilities
• Using the tape simulator program to implement BACKUP and RESTORE options
• Possible causes for objects becoming unusable
• Locating unusable objects
• Removing unusable objects
• Identifying the appropriate tools to make objects usable
• Using the simulator program to implement Backup and Restore options
• Lab Exercise (Optional, 45 minutes)
• Backing Up and Restoring SQL/MP Database Objects
• Managing NonStop SQL/MP database security
• Process for securing the Nonstop SQL/MP environment
• Authorization requirements for SQL/MP operations
• SQL/MP security commands and utilities
• SQL/MP protection views
• NonStop Kernel security and the Safeguard product security characteristics
• Security for the production and development environment
• Lab Exercise (1 hour)
• Managing SQL/MP Database Security
• Tools for monitoring performance
• Performance tools: SQLCI, Measure product, Tandem Performance Data Collector (TPDC), and basic guidelines for monitoring performance
• Disk process (DP2) performance enhancements
• Methods to enhance the performance of the disk process
• Managing sorts
• Sorts available to NonStop SQL/MP
• Managing sort processes to control system resources
• Configuring serial and parallel sort operations
• Controlling NonStop SQL/MP processes
• Controlling the impact of NonStop SQL/MP processes on performance