HPE NonStop SQL/MX Database ManagementU8617SHPHPHP-U8617S1.0<ul>
<li>Describe HP NonStop SQL/MX architecture</li><li>Install SQL/MX and upgrade SQL/MX metadata to current release</li><li>Create, alter, and drop SQL/MX database objects (catalogs, schemas, tables, constraints, indexes, views, and triggers)</li><li>Grant and revoke access privileges to SQL/MX database objects</li><li>Import data into SQL/MX non-partitioned and range and hash partitioned tables</li><li>Protect and recover SQL/MX database objects with HP NonStop Transaction Management Facility</li><li>Use the MODIFY utility to add, split, merge, and drop SQL/MX range and hash partitioned tables</li><li>Describe and use the SQL/MX utilities (DUP, import, migrate, MODIFY, mxtool, mxexportddl, MXGNAMES, POPULATE INDEX, and PURGEDATA)</li><li>Describe the SQL.MX distributed database</li><li>Become familiar with the SQL/MX architecture using SQL/MX native tables</li><li>Create and manage SQL/MX database objects</li></ul><ul>
<li>NonStop SQL/MX Basics (U4184S) •NonStop SQL/MX Quick Start (U4185S)</li></ul><ul>
<li>Database administrators •System managers •Personnel responsible for the availability of NonStop SQL/MX databases and applications</li></ul><h5>Module 1: Overview of SQL/MX Architecture </h5><ul>
<li>SQL/MX logical structure, catalogs, schemas, and objects</li><li>SQL/MX physical structure: SMD volumes, schema subvolumes, data forks and resource forks</li><li>SQL/MX processes and utilities</li><li>Lab exercise: Overview of SQL/MX Architecture</li><li>Identify the system catalog, schemas, and objects</li><li>Identify user catalogs, schemas, and objects</li><li>Locate the underlying files for SQL/MX objects</li><li>Metadata basics</li></ul><h5>Module 2: SQL/MX Installation</h5>
<ul>
<li>Hardware and software requirements for installing SQL/MX</li><li>Installing SQL/MX with the InstallSqlmx utility</li><li>Verifying the SQL/MX Installation</li><li>Create the SQL/MX sample database</li><li>Lab exercise: Perform a post-installation check for SQL/MX.</li></ul><h5>Module 3: Metadata Migration to SQL/MX R3.0 </h5><ul>
<li>Migrating considerations</li><li>Post upgrade scenarios</li><li>FIXRCB operation</li><li>UPGRADE utility</li></ul><h5>Module 4: Creating SQL/MX Objects</h5><ul>
<li>Creating catalogs, schemas, tables, constraints, indexes, and views</li><li>Viewing object metadata</li><li>Lab exercise: Creating SQL/MX Objects with mxci or NSM/web</li></ul><h5>Module 5: Referential Integrity Constraints </h5><ul>
<li>Referential Integrity</li><li>Creating referential integrity constraints as column or table constraints</li><li>Lab exercise: Creating Referential Integrity constraints with mxci or NSM/web</li></ul>
<h5>Module 6: Creating SQL/MX Partitioned Tables and Indexes</h5>
<ul>
<li>Range and hash partitioned tables</li><li>Creating range and hash partitioned tables and indexes</li><li>Advantages and Disadvantages of each type of partitioning.</li><li>Decoupled keys, co-partitioned tables, and co-located partitions</li><li>Lab exercise: Creating range and hash partitioned tables with mxci or NSM/web</li></ul>
<h5>Module 7: Creating SQL/MX Triggers </h5><ul>
<li>Trigger terminology</li><li>Creating BEFORE and AFTER triggers</li><li>Cascading and conflicting triggers</li><li>Lab exercise: Creating SQL/MX Triggers</li></ul><h5>Module 8: SQL/MX Security</h5><ul>
<li>Object ownership and security rules</li><li>Object Privileges</li><li>Use of Security Administrator’s Group</li><li>Change object ownership</li><li>SQL/MX GRANT and REVOKE commands</li><li>Lab exercise: Granting and Revoking Privileges with mxci or NSM/web</li></ul><h5>Module 9: Import and Populate Index Utilities</h5>
<ul>
<li>Importing fixed and delimited data with the import utility</li><li>Importing data in parallel</li><li>Populating (loading) indexes</li><li>Lab exercise: Importing Data into Range and Hash Partitioned Tables and Indexes</li></ul><h5>Module 10: Reorganizing SQL/MX Tables and Indexes </h5><ul>
<li>Reorganizing data</li><li>Determining file fragmentation</li><li>FUP RELOAD and INFO commands</li><li>Lab exercise: Reorganizing Data</li></ul><h5>Module 11: Updating Statistics </h5><ul>
<li>Histogram Statistics and histogram metadata tables</li><li>Generating single column and multicolumn statistics</li><li>Using sampling with the update statistics utility</li><li>Lab exercise: Updating and Viewing Statistics</li></ul><h5>Module 12: Database Protection and Recovery with TMF </h5><ul>
<li>TMF Protection and Recovery Strategies</li><li>MXGNAMES Utility</li><li>Performing Online Dumps</li><li>Recovering dropped SQL/MX Objects</li><li>Lab exercise: SQL/MX Protection and Recovery Using TMF</li></ul><h5>Module 13: Compiling SQL/MX Programs in the OSS Environment </h5><ul>
<li>Compiling SQL/MX Programs!Preprocessing source files for SQL/MX</li><li>Language compiling SQL/MX annotated source files</li><li>SQL/MX compiling module definition files</li><li>Global or local module files</li><li>Defining search paths for module files</li><li>Lab exercise: Compiling Embedded SQL/MX C or COBOL Programs in the OSS environment</li></ul><h5>Module 14: PURGEDATA Command and MODIFY Utility </h5><ul>
<li>Purging data from range and hash partitioned tables</li><li>Adding, splitting, merging, and dropping range partitions</li><li>Adding and dropping hash partitions</li><li>Lab exercise: PURGEDATA Command and MODIFY Utility</li></ul><h5>Module 15: SQL/MX Utilities </h5><ul>
<li>DUP tables, indexes, and constraints</li><li>RECOVER utility</li><li>mxtool (INFO, VERIFY, FIXUP, and GOAWAY) utility</li><li>Lab exercise: SQL/MX Utilities</li></ul><h5>Module 16: NonStop MXDM </h5><ul>
<li>Features and requirements of MXDM</li><li>Installing and uninstalling</li><li>Example screens</li></ul><h5>Module 17: Managing SQL/MX Applications </h5><ul>
<li>Name references in programs</li><li>Compile time and late name resolution</li><li>Similarity checking</li><li>Automatic recompilation</li><li>Methods for moving SQL/MX applications</li><li>Lab exercise: Managing Embedded SQL/MX Programs</li></ul><h5>Module 18: SQL/MX Distributed Databases</h5>
<ul>
<li>Distributed databases</li><li>Transparency, visibility, and availability</li><li>ANSI name translation for local and remote objects</li><li>REGISTER and UNREGISTER CATALOG commands</li><li>Lab exercise: SQL/MX Distributed Database Demonstration</li></ul><h5>Module 19: Adding, Altering, and Dropping SQL/MX Objects</h5>
<ul>
<li>Authorization for adding, altering, and dropping SQL/MX objects</li><li>Adding SQL/MX objects</li><li>Altering SQL/MX objects</li><li>Dropping SQL/MX objects</li><li>Lab exercise: Altering and dropping SQL/MX objects</li></ul><h5>Module 20: Open Source Tools </h5><ul>
<li>Overview of open source tools available for SQL/MX usage</li><li>Describe usage of Eclipse to access SQL.MX</li><li>Overview of use of DBVisualizer to access SQL/MX</li></ul>- Describe HP NonStop SQL/MX architecture
- Install SQL/MX and upgrade SQL/MX metadata to current release
- Create, alter, and drop SQL/MX database objects (catalogs, schemas, tables, constraints, indexes, views, and triggers)
- Grant and revoke access privileges to SQL/MX database objects
- Import data into SQL/MX non-partitioned and range and hash partitioned tables
- Protect and recover SQL/MX database objects with HP NonStop Transaction Management Facility
- Use the MODIFY utility to add, split, merge, and drop SQL/MX range and hash partitioned tables
- Describe and use the SQL/MX utilities (DUP, import, migrate, MODIFY, mxtool, mxexportddl, MXGNAMES, POPULATE INDEX, and PURGEDATA)
- Describe the SQL.MX distributed database
- Become familiar with the SQL/MX architecture using SQL/MX native tables
- Create and manage SQL/MX database objects- NonStop SQL/MX Basics (U4184S) •NonStop SQL/MX Quick Start (U4185S)- Database administrators •System managers •Personnel responsible for the availability of NonStop SQL/MX databases and applicationsModule 1: Overview of SQL/MX Architecture
- SQL/MX logical structure, catalogs, schemas, and objects
- SQL/MX physical structure: SMD volumes, schema subvolumes, data forks and resource forks
- SQL/MX processes and utilities
- Lab exercise: Overview of SQL/MX Architecture
- Identify the system catalog, schemas, and objects
- Identify user catalogs, schemas, and objects
- Locate the underlying files for SQL/MX objects
- Metadata basics
Module 2: SQL/MX Installation
- Hardware and software requirements for installing SQL/MX
- Installing SQL/MX with the InstallSqlmx utility
- Verifying the SQL/MX Installation
- Create the SQL/MX sample database
- Lab exercise: Perform a post-installation check for SQL/MX.
Module 3: Metadata Migration to SQL/MX R3.0
- Migrating considerations
- Post upgrade scenarios
- FIXRCB operation
- UPGRADE utility
Module 4: Creating SQL/MX Objects
- Creating catalogs, schemas, tables, constraints, indexes, and views
- Viewing object metadata
- Lab exercise: Creating SQL/MX Objects with mxci or NSM/web
Module 5: Referential Integrity Constraints
- Referential Integrity
- Creating referential integrity constraints as column or table constraints
- Lab exercise: Creating Referential Integrity constraints with mxci or NSM/web
Module 6: Creating SQL/MX Partitioned Tables and Indexes
- Range and hash partitioned tables
- Creating range and hash partitioned tables and indexes
- Advantages and Disadvantages of each type of partitioning.
- Decoupled keys, co-partitioned tables, and co-located partitions
- Lab exercise: Creating range and hash partitioned tables with mxci or NSM/web
Module 7: Creating SQL/MX Triggers
- Trigger terminology
- Creating BEFORE and AFTER triggers
- Cascading and conflicting triggers
- Lab exercise: Creating SQL/MX Triggers
Module 8: SQL/MX Security
- Object ownership and security rules
- Object Privileges
- Use of Security Administrator’s Group
- Change object ownership
- SQL/MX GRANT and REVOKE commands
- Lab exercise: Granting and Revoking Privileges with mxci or NSM/web
Module 9: Import and Populate Index Utilities
- Importing fixed and delimited data with the import utility
- Importing data in parallel
- Populating (loading) indexes
- Lab exercise: Importing Data into Range and Hash Partitioned Tables and Indexes
Module 10: Reorganizing SQL/MX Tables and Indexes
- Reorganizing data
- Determining file fragmentation
- FUP RELOAD and INFO commands
- Lab exercise: Reorganizing Data
Module 11: Updating Statistics
- Histogram Statistics and histogram metadata tables
- Generating single column and multicolumn statistics
- Using sampling with the update statistics utility
- Lab exercise: Updating and Viewing Statistics
Module 12: Database Protection and Recovery with TMF
- TMF Protection and Recovery Strategies
- MXGNAMES Utility
- Performing Online Dumps
- Recovering dropped SQL/MX Objects
- Lab exercise: SQL/MX Protection and Recovery Using TMF
Module 13: Compiling SQL/MX Programs in the OSS Environment
- Compiling SQL/MX Programs!Preprocessing source files for SQL/MX
- Language compiling SQL/MX annotated source files
- SQL/MX compiling module definition files
- Global or local module files
- Defining search paths for module files
- Lab exercise: Compiling Embedded SQL/MX C or COBOL Programs in the OSS environment
Module 14: PURGEDATA Command and MODIFY Utility
- Purging data from range and hash partitioned tables
- Adding, splitting, merging, and dropping range partitions
- Adding and dropping hash partitions
- Lab exercise: PURGEDATA Command and MODIFY Utility
Module 15: SQL/MX Utilities
- DUP tables, indexes, and constraints
- RECOVER utility
- mxtool (INFO, VERIFY, FIXUP, and GOAWAY) utility
- Lab exercise: SQL/MX Utilities
Module 16: NonStop MXDM
- Features and requirements of MXDM
- Installing and uninstalling
- Example screens
Module 17: Managing SQL/MX Applications
- Name references in programs
- Compile time and late name resolution
- Similarity checking
- Automatic recompilation
- Methods for moving SQL/MX applications
- Lab exercise: Managing Embedded SQL/MX Programs
Module 18: SQL/MX Distributed Databases
- Distributed databases
- Transparency, visibility, and availability
- ANSI name translation for local and remote objects
- REGISTER and UNREGISTER CATALOG commands
- Lab exercise: SQL/MX Distributed Database Demonstration
Module 19: Adding, Altering, and Dropping SQL/MX Objects
- Authorization for adding, altering, and dropping SQL/MX objects
- Adding SQL/MX objects
- Altering SQL/MX objects
- Dropping SQL/MX objects
- Lab exercise: Altering and dropping SQL/MX objects
Module 20: Open Source Tools
- Overview of open source tools available for SQL/MX usage
- Describe usage of Eclipse to access SQL.MX
- Overview of use of DBVisualizer to access SQL/MX5 Tage3750.004500.003750.00