NonStop SQL/MP environment
- Processes, structure, and objects of the 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)
- 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 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 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 NonStop SQL/MP, the catalog, and several objects on your system
creating 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
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
