SQL/MX 3.1 Installation and Management Guide (H06.23+, J06.12+)

ManualsBrandsHP ManualsServerHP Integrity NonStop H-Series

HP NonStop SQL/MX

Release 3.1 Installation

and Management Guide

Abstract

This manual describes how to install and manage HP NonStop

™

SQL/MX.

Product Version

NonStop SQL/MX Release 3.1

Supported Release Version Updates (RVUs)

This publication supports J06.12 and all subsequent J-series RVUs and H06.23 and all

subsequent H-series RVUs, until otherwise indicated by its replacement publications.

Additionally, all considerations for H-series throughout this manual will hold true for

J-series also, unless mentioned otherwise.

Part Number Published

663852-001 October 2011

Summary of content (492 pages)

PAGE 1
HP NonStop SQL/MX Release 3.1 Installation and Management Guide Abstract This manual describes how to install and manage HP NonStop™ SQL/MX. Product Version NonStop SQL/MX Release 3.1 Supported Release Version Updates (RVUs) This publication supports J06.12 and all subsequent J-series RVUs and H06.23 and all subsequent H-series RVUs, until otherwise indicated by its replacement publications.
PAGE 2
Document History Part Number Product Version 640325-001 NonStop SQL/MX Release 3.
PAGE 3
Legal Notices  Copyright 2011 Hewlett-Packard Development Company L.P. Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice.
PAGE 4
PAGE 5
HP NonStop SQL/MX Release 3.1 Installation and Management Guide Glossary Index Examples Figures Legal Notices What’s New in This Manual xv Manual Information xv New and Changed Information About This Manual xvii Audience xvii Organization xvii Related Documentation xviii Notation Conventions xx HP Encourages Your Comments xv xxiii 1.
PAGE 6
Contents 2. Preparing to Install NonStop SQL/MX MXCS 1-12 NonStop Server for Java 1-12 JDBC/MX Driver 1-13 Database Management Tasks 1-13 SQL/MX Database Management Tools 1-13 2. Preparing to Install NonStop SQL/MX Hardware Requirements 2-1 Software Requirements 2-2 Verifying Correct Versions of Independent Software Products Installing the DDL Licensing Product (T0394) 2-3 Starting TMF 2-3 2-3 3.
PAGE 7
Contents 5.
PAGE 8
Contents 6.
PAGE 9
Contents 7.
PAGE 10
Contents 8. Querying SQL/MX Metadata Creating Stored Procedures in Java 7-41 Database Design Guidelines for Improving OLTP Performance 7-41 8.
PAGE 11
Contents 8.
PAGE 12
Contents 9. Adding, Altering, and Dropping SQL/MX Database Objects 9.
PAGE 13
Contents 10. Reorganizing SQL/MX Tables and Maintaining Data 10.
PAGE 14
Contents 11. Managing Database Applications Guidelines for Using PURGEDATA 10-45 Examples of Using PURGEDATA to Delete Table Data 10-46 11.
PAGE 15
Contents 12. Performing Recovery Operations 12.
PAGE 16
Contents 14. Measuring Performance Maintaining Local Autonomy in a Network for SQL/MX Release 3.
PAGE 17
Contents A. Using Guardian Names with TMF, RDF, and Measure Performing FUP RELOADs to Generate More Accurate Query Plans Checking Data Integrity 15-18 Creating Logical Views of Data 15-19 Adding and Dropping Partitions 15-19 Avoiding Automatic Recompilations 15-20 Matching Block Split Operation to Table Usage 15-20 15-17 A.
PAGE 18
Contents Table 9-5. Table 9-6. Table 10-1. Table 10-2. Table 11-1. Table 12-1. Table 12-2. Table 13-1. Table 13-2. Table 13-3. Table 13-4. Table 13-5. Table 13-6.
PAGE 19
What’s New in This Manual Manual Information Abstract This manual describes how to install and manage HP NonStop™ SQL/MX. Product Version NonStop SQL/MX Release 3.1 Supported Release Version Updates (RVUs) This publication supports J06.12 and all subsequent J-series RVUs and H06.23 and all subsequent H-series RVUs, until otherwise indicated by its replacement publications. Additionally, all considerations for H-series throughout this manual will hold true for J-series also, unless mentioned otherwise.
PAGE 20
What’s New in This Manual New and Changed Information HP NonStop SQL/MX Release 3.
PAGE 21
About This Manual This manual describes how to install NonStop SQL/MX Release 3.1 on an HP NonStop server and discusses other issues related to managing an SQL/MX system. In this manual, references to SQL/MX Release 2.x indicate SQL/MX Release 2.3.1, and subsequent releases and SQL/MX Release 3.x indicates SQL/MX Release 3.0 and subsequent releases, until indicated in a replacement publication.
PAGE 22
About This Manual Related Documentation Section 9, Adding, Altering, and Dropping SQL/MX Database Objects Describes how to add, alter, and drop objects from an SQL/MX database. Section 10, Reorganizing SQL/MX Tables and Maintaining Data Describes how to reorganize SQL/MX tables and maintain the data. Section 11, Managing Database Applications Describes how to manage SQL/MX applications. Section 12, Performing Recovery Operations Describes how to perform recovery operations on an SQL/MX database.
PAGE 23
About This Manual Related Documentation DataLoader/MX Reference Manual Describes the features and functions of the DataLoader/MX product, a tool to load SQL/MX databases. SQL/MX Messages Manual Describes SQL/MX messages. SQL/MX Glossary Defines SQL/MX terminology. Programming Manuals SQL/MX Programming Manual for C and COBOL Describes how to embed SQL/MX statements in ANSI C and COBOL programs.
PAGE 24
About This Manual Notation Conventions Online Help The SQL/MX Online Help consists of: Reference Help Overview and reference entries from the SQL/MX Reference Manual. Messages Help Individual messages grouped by source from the SQL/MX Messages Manual. Glossary Help Terms and definitions from the SQL/MX Glossary. NSM/web Help Context-sensitive help topics that describe how to use the NSM/web management tool.
PAGE 25
About This Manual General Syntax Notation UPPERCASE LETTERS. Uppercase letters indicate keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: MAXATTACH lowercase italic letters. Lowercase italic letters indicate variable items that you supply. Items not enclosed in brackets are required. For example: file-name computer type. Computer type letters within text indicate C and Open System Services (OSS) keywords and reserved words.
PAGE 26
About This Manual Change Bar Notation … Ellipsis. An ellipsis immediately following a pair of brackets or braces indicates that you can repeat the enclosed sequence of syntax items any number of times. For example: M address [ , new-value ]… [ - ] {0|1|2|3|4|5|6|7|8|9}… An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. For example: "s-char…" Punctuation.
PAGE 27
About This Manual HP Encourages Your Comments The CRE has many new message types and some new message type codes for old message types. In the CRE, the message type SYSTEM includes all messages except LOGICAL-CLOSE and LOGICAL-OPEN. HP Encourages Your Comments HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs. Send any errors found, suggestions for improvement, or compliments to docsfeedback@hp.com.
PAGE 28
About This Manual HP Encourages Your Comments HP NonStop SQL/MX Release 3.
PAGE 29
1 Introduction to SQL/MX Database Management This section provides an introduction to managing an SQL/MX database. For information about managing an SQL/MP database, see the SQL/MP Installation and Management Guide. Managing an SQL/MX relational database typically involves managing sets of continuously active programs, as well as the database itself.
PAGE 30
Introduction to SQL/MX Database Management  SQL/MX Database Features Database management tools    SQL/MX statements, SQL/MX utilities, and SQL/MX conversational interface (MXCI) commands Guardian and HP NonStop Open System Services (OSS) utilities HP products for database security, conversion, and reorganization For more information about database protection and recovery, see Section 5, Planning Database Security and Recovery.
PAGE 31
Introduction to SQL/MX Database Management   Database Protection and Recovery The HP NonStop Remote Database Facility (RDF), which monitors changes made to a production database on a local (primary) system and maintains a copy of that database on a remote (backup) system. The Guardian Backup and Restore 2 utility, which you can use to perform tape backups and restore SQL/MX objects.
PAGE 32
Introduction to SQL/MX Database Management Data Integrity Data Integrity The database management system protects the database by ensuring that entered data meets the definitional requirements. Application programs, therefore, do not need to perform data checking. These data definition features ensure definitional integrity:     Column definitions ANSI views Constraints Indexes These features provide additional data integrity for SQL/MX databases:     Database changes are monitored by TMF.
PAGE 33
Introduction to SQL/MX Database Management Multiple Character Sets Multiple Character Sets NonStop SQL/MX allows you to associate one of these character sets with a literal or host variable: ISO88591 Default single-byte 8-bit character set for character data types. It supports English and other Western European languages. USC2 Double-byte Unicode character set in UTF16 big-endian encoding. All Basic Multilingual Plan (BMP) characters are included.
PAGE 34
Introduction to SQL/MX Database Management High Availability the number of partitions when there is no contention in the CPU-controller-disk path; that is, when every participating disk is primary to a different CPU. Parallel join operations are performed by the SQL/MX executor during query processing. Parallel index maintenance reduces the effect of multiple indexes on performance.
PAGE 35
Introduction to SQL/MX Database Management Guardian Physical Names You name tables, views, and other SQL/MX objects by using a three-part ANSI logical name: catalog-name.schema-name.object-name In this three-part name, catalog-name is the name of the catalog, schema-name is the name of the schema, and object-name is the simple name of the object. Each of the parts is an SQL identifier.
PAGE 36
Introduction to SQL/MX Database Management Object Namespaces You cannot assign DEFINE names to SQL/MX tables or views. However, you can use prototyped host variables to name SQL/MX tables or views in C and COBOL programs. For more information about prototyped host variables, see the SQL/MX Programming Manual for C and COBOL. For more information about using DEFINE names, see the SQL/MX Programming Manual for C and COBOL.
PAGE 37
Introduction to SQL/MX Database Management SQL/MX Metadata Table 1-1. SQL/MX Metadata Schemas Schema Name Schema Contents SYSTEM_SCHEMA One set of five system schema tables resides in the system catalog under this schema. Use these system metadata tables, which record the existence of catalogs and schemas, to find the correct user catalog on which to perform data lookup.
PAGE 38
Introduction to SQL/MX Database Management SQL/MX Database Object Types SQL/MX Database Object Types Table 1-2 describes SQL/MX object types. For information about SQL/MP objects, see the SQL/MP Installation and Management Guide and the SQL/MP Reference Manual. Table 1-2. SQL/MX Object Types (page 1 of 2) SQL/MX Object Type Table Description A logical representation of data in which a set of records is represented as a sequence of rows, and the set of fields common to all rows is represented by columns.
PAGE 39
Introduction to SQL/MX Database Management The SQL/MX Environment Table 1-2. SQL/MX Object Types (page 2 of 2) SQL/MX Object Type Constraints Description Constraints protect the integrity of data in a table by restricting the values in a particular column or set of columns to those that meet specified conditions. The SQL/MX constraints are:      Not Null, which is a constraint that identifies the column or columns that cannot contain nulls.
PAGE 40
Introduction to SQL/MX Database Management OSS Environment attributes of an MXCI session. MXCI runs as an OSS process and must be started within the OSS environment. For information about MXCI commands, see the SQL/MX Reference Manual. OSS Environment Several SQL/MX processes and utilities run in the OSS environment. Consequently, OSS must be installed and running on your system to run NonStop SQL/MX. For information, see the SQL/MX Reference Manual.
PAGE 41
Introduction to SQL/MX Database Management JDBC/MX Driver JDBC/MX Driver To create and execute SPJs in NonStop SQL/MX, you must install the JDBC/MX driver on a NonStop system. The JDBC/MX driver enables a Java application to use NonStop SQL/MX to access an SQL/MX or SQL/MP database. For details about this product, see the JDBC Driver for SQL/MX Programmer’s Reference. For installation instructions, see the README file on the product CD. For version requirements, see Table 2-1.
PAGE 42
Introduction to SQL/MX Database Management SQL/MX Database Management Tools Resource Control and Optimization Locks or unlocks tables and their indexes to limit other accesses to the tables during DML statement execution. Control Regulates the execution default options, plans, and performance of DML statements. Object Naming Sets the value of the NAMETYPE attribute, which in turn determines whether object naming is ANSI or Guardian for the current session.
PAGE 43
Introduction to SQL/MX Database Management SQL/MX Database Management Tools Table 1-3. NonStop Tools for SQL/MX Database Management (page 2 of 2) Program Description FUP File Utility Program; reorganizes SQL/MX files while they are in use, licenses programs, and uses INFO to obtain useful information about SQL/MX objects and files. For SQL/MX Release 2.3.1, the FUP commands INFO, LISTLOCKS, LISTOPENS, and RELOAD support the use of ANSI names for SQL/MX objects.
PAGE 44
Introduction to SQL/MX Database Management SQL/MX Database Management Tools HP NonStop SQL/MX Release 3.
PAGE 45
2 Preparing to Install NonStop SQL/MX Before you install SQL/MX Release 3.1, check that you have met the following requirements as they apply to your installation:      Hardware Requirements on page 2-1 Software Requirements on page 2-2 Verifying Correct Versions of Independent Software Products on page 2-3 Installing the DDL Licensing Product (T0394) on page 2-3 Starting TMF on page 2-3 Hardware Requirements Before you install SQL/MX Release 3.
PAGE 46
Preparing to Install NonStop SQL/MX Software Requirements Software Requirements To use SQL/MX Release 3.1, you must install the Site Update Tape (SUT) for the H06.23 or a later RVU on your Integrity NonStop NS-series system. See the H06.nn Software Installation and Upgrade Guide for information about installing the NonStop system software. The H06.23 or later RVU contains the correct product versions for these key software products that are used with NonStop SQL/MX. Note.
PAGE 47
Preparing to Install NonStop SQL/MX Verifying Correct Versions of Independent Software Products Verifying Correct Versions of Independent Software Products If you intend to develop and execute Java programs that perform SQL operations or Stored Procedures for Java (SPJs) on your NonStop system, verify that you have installed the correct versions of these independent software products:   NonStop Server for Java JDBC/MX driver These independent products need to be ordered separately from the software p
PAGE 48
Preparing to Install NonStop SQL/MX Starting TMF Planning Database Security and Recovery and the TMF Planning and Configuration Guide. The product version of TMF provided on the SUT must be compatible with the SQL/MX software provided with the SQL/MX SPR you install. When you start the TMF subsystem, configured data volumes are started for transaction processing if they are accessible.
PAGE 49
Preparing to Install NonStop SQL/MX For TMF configuration information, see the TMF Reference Manual. HP NonStop SQL/MX Release 3.
PAGE 50
Preparing to Install NonStop SQL/MX HP NonStop SQL/MX Release 3.
PAGE 51
3 Installing NonStop SQL/MX Summary of Installation Tasks Before you install SQL/MX Release 3.1, read Using the InstallSqlmx Script on page 3-18 to familiarize yourself with the InstallSqlmx script and the functions it performs during the installation of NonStop SQL/MX. Note. When you install NonStop SQL/MX on your system from the H06.04 or later H-series RVU, you also install NonStop SQL/MP. To initialize NonStop SQL/MP, you must issue the INITIALIZE SQL command from SQLCI.
PAGE 52
Installing NonStop SQL/MX Preinstallation Tasks Preinstallation Tasks 1. Confirm that your NonStop system meets all the hardware and software requirements identified in Section 2, Preparing to Install NonStop SQL/MX. After ensuring system requirements and performing the remaining preinstallation tasks, you can install the SQL/MX software and the SQL/MX sample database that is distributed with it. For instructions on installing NonStop SQL/MX, see Installing NonStop SQL/MX on page 3-2.
PAGE 53
Installing NonStop SQL/MX Installing NonStop SQL/MX 3. As needed, use DSM/SCM to install SPRs to obtain the functionality and defect repair for SQL/MX Release 3.1. Table 3-1 identifies the default SQL/MX release for each H-series RVU and the SPRs available for installing later releases. For information about using DSM/SCM, see the DSM/SCM User’s Guide. For information about installing SPRs, see the H06.nn Software Installation and Upgrade Guide. Table 3-1.
PAGE 54
Installing NonStop SQL/MX Verifying the SQL/MX Installation 7. Run the InstallSqlmx script by entering: Caution. The volume you specify in the InstallSqlmx command line specifies the system catalog location for the system metadata tables. This volume should not go on the $SYSTEM disk volume. If you ever replace the $SYSTEM disk, you can lose system metadata information. The specified volume must be audited.
PAGE 55
Installing NonStop SQL/MX Overview of Automatic File Installations Overview of Automatic File Installations This subsection identifies the activities that automatically occur during installation of the H06.23 or later H-series RVU: Note. During SQL/MX installation, these files are installed only if you have the DDL licensing product (T0394) for SQL/MX tables:      import IMPORT MXAUDSRV mxexportddl MXGNAMES For details about each file, see Executable Files on page 3-9. 1. The H06.
PAGE 56
Installing NonStop SQL/MX   Overview of Automatic File Installations T1058PAX, which contains mxcierrors.cat, the MXCI-related error messages file T1231PAX, which contains the SQL/MX language manager files mxlangman.jar and mxlangman.policy 3. The SUT installation automatically installs the public DLLs ZCLIDLL and ZCLIPDLL to the $SYSTEM.ZDLLnnn subvolume, where nnn is a number picked by the public DLL installation during the SYSGEN operation. Caution.
PAGE 57
Installing NonStop SQL/MX    Verify That Files Are Licensed NONSTOP_SQLMX_NSK.SYSTEM_SCHEMA.SQLUTILS NONSTOP_SQLMX_NSK.SYSTEM_SCHEMA.SQLJ NONSTOP_SQLMX_NSK.SYSTEM_SCHEMA.ANSINAMES c. COPYOSS installs these user module files in the OSS directory /usr/tandem/sqlmx/USERMODULES:   NONSTOP_SQLMX_NSK.SCH.MIGRATE NONSTOP_SQLMX_NSK.SCH.MXGNAMES d. COPYOSS installs these files in the OSS directory /usr/tandem/sqlmx/include:   Platform.h sqlcli.h e.
PAGE 58
Installing NonStop SQL/MX    Summary of Installed SQL/MX Files MXRTDSRV ZCLIPDLL MXTOOL To verify that these files have been licensed: Note. You can change the security and licensing of DLLs, but the new security and licensing do not take effect until the next system coldload. 1. From a TACL prompt, issue a FILEINFO command on each of the files. IMPORT, MXAUDSRV, MXCMP, MXESP, MXRTDSRV, and MXTOOL reside in $SYSTEM.SYSTEM. ZCLIPDLL resides in $SYSTEM.ZDLLnnn.
PAGE 59
Installing NonStop SQL/MX Summary of Installed SQL/MX Files Executable Files File Name Description Where Installed ansp.exe The OSS file for the ANSI names server process (ANSP), which provides ANSI-to-Guardian name mapping for NonStop utilities. /usr/tandem/sqlmx/bin MXAUDSRV The SQL/MX audit fixup server and a licensed Guardian code 800 file, which performs audit fixup for online MODIFY operations after a fuzzy data copy has been created.
PAGE 60
Installing NonStop SQL/MX Summary of Installed SQL/MX Files File Name Description Where Installed MXUTP The executable file for the materialized view feature, which is not supported with SQL/MX Release 3.1. $SYSTEM.SYSTEM MXOMSG A message file associated with MXCS. $SYSTEM.SYSTEM IMPORT The IMPORT utility, which resides in the Guardian file space and is a licensed Guardian code 800 file.
PAGE 61
Installing NonStop SQL/MX Summary of Installed SQL/MX Files File Name Description Where Installed mxexportddl The OSS file for the mxexportDDL utility. See Using Import Catalog Tools on page 3-18. This file can be used only if you have purchased and installed the DDL licensing product (T0394) for SQL/MX tables. /usr/tandem/sqlmx/bin mxsqlc The OSS-based C preprocessor to preprocess embedded SQL statements in C/C++ programs.
PAGE 62
Installing NonStop SQL/MX Summary of Installed SQL/MX Files SPJ Product Files These product files are necessary for developing and executing stored procedures in Java (SPJs) in NonStop SQL/MX: File Name Description Where Installed mxlangman.jar A JAR file that contains Java bytecode that implements part of the SQL/MX language manager, an application program interface (API) that the SQL/MX UDR server calls to load, invoke, and unload SPJs.
PAGE 63
Installing NonStop SQL/MX Summary of Installed SQL/MX Files InstallSqlmx Script File File Name Description Where Installed InstallSqlmx The OSS script file that initializes NonStop SQL/MX. /usr/tandem/sqlmx/bin File Name Description Where Installed MXANCHOR The anchor file created by the InstallSqlmx script that specifies the location of the system metadata tables. $SYSTEM.
PAGE 64
Installing NonStop SQL/MX Verifying Executable File Versions Messages Files File Name Description Where Installed mxcierrors.cat An OSS file that contains the error message text for all SQL errors. It is implicitly used by all components that are capable of displaying error text (for example, the NT-hosted preprocessors, MXCI, and MXCMP). /usr/tandem/sqlmx/bin MXOMSG The message file for MXCS. $SYSTEM.SYSTEM ZMXSTMPL Contains the compiled event messages for NonStop SQL/MX. $SYSTEM.
PAGE 65
Installing NonStop SQL/MX Post-Installation Tasks To display the version information about executable files in the OSS environment, enter: vproc executable-file-name To display version information about executable files in the Guardian environment, enter: VPROC executable-file-name For example, to find the version of MXCMP installed, at a TACL prompt, enter: $SYSTEM SYSTEM 4> vproc mxcmp Information similar to the following will be displayed: VPROC - T9617H01 - (01 FEB 2009) SYSTEM \DMR18 Date 13 JUL 201
PAGE 66
Installing NonStop SQL/MX Reviewing and Setting System Defaults Reviewing and Setting System Defaults Before using the installed SQL/MX Release 3.1 database, you must review the system defaults contained in the user table SYSTEM_DEFAULTS. Depending on your database needs, you might want to change some of the system defaults to alternative settings. For more information, see Section 6, Reviewing and Setting System Defaults and the SQL/MX Reference Manual.
PAGE 67
Installing NonStop SQL/MX Installing the SQL/MX Online Help For information about using Visual Query Planner, see the SQL/MX Query Guide, or see the Visual Query Planner online help in the Visual Query Planner. Installing the SQL/MX Online Help SQL/MX online help files are downloadable from the HP Software Depot at: http://www.software.hp.com The help files are designed for best viewing on a TSM workstation or NonStop system console provided by HP.
PAGE 68
Installing NonStop SQL/MX Using Import Catalog Tools 1. Use OutsideView, the Receive File option, or FTP to download T610SET to a Windows workstation. The setup file for the preprocessor is a binary file. If you use the Receive File option, select Binary but do not select Add CR LF. Specify setup.exe for the local file name. The Installshield Wizard installs the preprocessor files. By default, NonStop SQL/MX places the executable in the C:\Program Files\HP SQL-MX COBOL Preprocessors directory.
PAGE 69
Installing NonStop SQL/MX  The Phases of InstallSqlmx After creating the anchor file, InstallSqlmx assigns a security of “N---” to the anchor file. Caution. After InstallSqlmx assigns a security of “N---” to the anchor file, do not change this setting. If you attempt to change this setting and the anchor file is modified, SQL/MX will cease to operate on the system.      Initializes NonStop SQL/MX after successfully creating the anchor file.
PAGE 70
Installing NonStop SQL/MX The Phases of InstallSqlmx Table 3-2. The Phases of InstallSqlmx Phase Phase Activities 1 InstallSqlmx verifies that the SQL/MX objects were laid down correctly from the SUT installation. If any required SQL/MX objects are missing from their default locations, InstallSqlmx notifies the user about the missing objects and aborts the installation. See Overview of Automatic File Installations on page 3-5 for file name and default location information.
PAGE 71
Installing NonStop SQL/MX InstallSqlmx Syntax InstallSqlmx Syntax InstallSqlmx [-help | -h ] | options options are: { [ -l \$volume [ -n national_charset_name ] [ -nofcheck ]] | -initmxcs | -b } volume is: the DP2 volume in which to install the system metadata tables You must always specify the -l option first. You can specify other options in any order. InstallSqlmx Options Table 3-3 describes the command options for InstallSqlmx. Table 3-3.
PAGE 72
Installing NonStop SQL/MX InstallSqlmx Error Messages Table 3-3. InstallSqlmx Command Options (page 2 of 2) Option Description -l volume Use this option to specify the volume in which InstallSqlmx installs SQL/MX system metadata tables. The volume, which must be audited, should not go on $SYSTEM. InstallSqlmx determines whether system metadata tables already exist. If they already exist, an error message appears and InstallSqlmx terminates.
PAGE 73
4 Understanding and Planning SQL/MX Tables An understanding of the types of organizations of SQL/MX tables and their key-sequenced file structure is essential for effective use and functioning of the database.
PAGE 74
Understanding and Planning SQL/MX Tables Avoid Renaming Nodes Avoid Renaming Nodes After an SQL/MX database node has been installed and initialized, do not attempt to change the node name. The node name is recorded in system metadata entries and file labels throughout the database. The node number is not recorded anywhere. Avoid Changing Object Names or Moving Database Objects You should develop and implement an object-naming strategy that prohibits changing object names or moving named objects.
PAGE 75
Understanding and Planning SQL/MX Tables Understanding SQL/MX Table and File Structures Each SQL/MX table and index, and each partition of a table or index, is implemented as a pair of Guardian physical files representing:   A data fork, where the user data resides A resource fork, where the runtime metadata resides If a table or index is not partitioned, then it has just one data fork and one resource fork.
PAGE 76
Understanding and Planning SQL/MX Tables Types of Keys Used in SQL/MX Tables and Indexes Types of Keys Used in SQL/MX Tables and Indexes At the physical level, a key is a field or group of fields that the system can use to order records and to identify records for processing. SQL/MX tables and indexes use the keys described in this subsection. Primary Key A primary key is the column or set of columns that define a unique key for an SQL/MX table.
PAGE 77
Understanding and Planning SQL/MX Tables Foreign Key columns stored in ascending order and the highest values for columns stored in descending order. Hash partitions have a partitioning key that translates into a hash value. All rows that translate into the same value are assigned to the same hash partition. All these column values are referred to as the partitioning key.
PAGE 78
Understanding and Planning SQL/MX Tables Key-Sequenced Tree Structure If you do not use an index, access occurs by the clustering key. Access can begin with the first record in the file or can be requested for only a specified range of records in the file. Key-Sequenced Tree Structure Key-sequenced files are physically organized as one or more bit-map blocks and a B-tree structure of index blocks and data blocks. Bit-map blocks within a structured file organize the file’s free space.
PAGE 79
Understanding and Planning SQL/MX Tables Planning Table and Index Partitioning Each data block contains a header plus one or more data records, depending on the record size and data-block size. For each data block, an entry in an index block contains the value of the key field for the first record in the data block and the address of that data block. The position of a new record inserted into a key-sequenced file is determined by the value of its clustering key field.
PAGE 80
Understanding and Planning SQL/MX Tables When to Use Range Partitioning are placed on a particular disk. For example, rows for employee numbers 1-500 might be placed on disk 1, rows for employee numbers 501-1000 placed on disk 2, and so on. This approach offers good performance for range-based queries and reasonable performance for exact-match queries involving the partitioning key. You can also put multiple partitions on the same disk.
PAGE 81
Understanding and Planning SQL/MX Tables When to Use Hash Partitioning When to Use Hash Partitioning Use hash partitioning if your data is not easily distributed among ranges or to evenly distribute data across a specified number of partitions. Creating and using hash partitions gives you a highly tunable method for data placement because you can influence availability and performance by spreading these evenly sized partitions across I/O devices (striping).
PAGE 82
Understanding and Planning SQL/MX Tables Using SQL/MX Tables For information about determining an SQL/MP database layout, see the SQL/MP Installation and Management Guide. Using SQL/MX Tables You can use SQL/MX tables to externalize all the data to the user. Using tables is the most direct method because views, constraints, and indexes ultimately depend on their underlying tables.
PAGE 83
Understanding and Planning SQL/MX Tables  Using Views To present only the columns and rows a user needs to work with instead of all the columns and rows in underlying tables or views. Note. A view does not insulate the programs that use it from being invalidated by changes to the table definition. Even changes that do not directly affect the view require that the programs be explicitly SQL compiled to be revalidated.
PAGE 84
Understanding and Planning SQL/MX Tables Determining When to Use Indexes Use updatable views to:   Provide validity checks on the underlying table for inserts and updates Provide restrictions so that only certain information can be presented to a user by masking rows and columns of the underlying table from displays or updating. If an updatable view is defined with the WITH CHECK option, through the view you can only insert or update rows that the WHERE clause of the view query evaluates to true.
PAGE 85
Understanding and Planning SQL/MX Tables Determining When to Use Indexes Each index is assigned a name and is physically stored in a separate key-sequenced file. Index files are not tables, and they cannot be queried directly through NonStop SQL/MX. They are only a tool for providing faster access to tables.
PAGE 86
Understanding and Planning SQL/MX Tables Determining When to Use Indexes A second use of an index is to eliminate a run-time sort of data columns by providing an access path to the data in the order of the desired selection or by reverse order: SELECT A,B FROM ATABLE; ORDER BY A, B; A third use of an index is to avoid a full table scan: SELECT ITEM_NAME, RETAIL_PRICE FROM INVNTRY WHERE RETAIL_PRICE = 100; Without an index on RETAIL_PRICE and assuming RETAIL_PRICE is not the leading column on the table’s c
PAGE 87
Understanding and Planning SQL/MX Tables   Determining When to Use Indexes All the necessary information can be obtained from the index (index-only access). The column is an argument of the MIN or MAX function. Index performance is affected by the ratio of INSERT, UPDATE, and DELETE operations to the use of the index, either as a selection predicate for selecting rows or to satisfy ORDER BY. For example, an index might be built to support one execution of a SELECT statement.
PAGE 88
Understanding and Planning SQL/MX Tables       Determining When to Use Indexes If there are ordering requirements, consider defining the sequence of columns so that it meets those requirements. Otherwise, a sort will be necessary to fulfill the ordering requirements. If an index is unique, define it as unique. NonStop SQL/MX can access the index more efficiently if the index is unique and you specify equality predicates on all index columns.
PAGE 89
Understanding and Planning SQL/MX Tables Determining When to Use Indexes information about the NOT NULL NOT DROPPABLE clause, see the SQL/MX Reference Manual.
PAGE 90
Understanding and Planning SQL/MX Tables Determining When to Use Indexes Consider this query: SELECT qty_ordered, retail_price FROM INVNTRY ORDER BY QTY_ORDERED, RETAIL_PRICE ; If the INVNTRY table is large, the cost of sorting the table might be very high.
PAGE 91
Understanding and Planning SQL/MX Tables Determining When to Use Indexes In this example, COL1 and COL2 are key prefixes from two different indexes. NonStop SQL/MX uses the indexes to retrieve all the rows that satisfy the predicate that COL2 = 20 or COL1 = 10: SELECT * FROM T WHERE COL1 = 10 OR COL2 = 20 ; Evaluating the query by using the indexes is much more efficient than scanning the entire table. HP NonStop SQL/MX Release 3.
PAGE 92
Understanding and Planning SQL/MX Tables Determining When to Use Indexes HP NonStop SQL/MX Release 3.
PAGE 93
5 Planning Database Security and Recovery Database security and recovery are essential topics to consider before creating an SQL/MX database. Planning for security is the primary protection against unauthorized user intervention. Security, however, cannot eliminate errors by authorized users. Planning for recovery is essential for protecting your database.
PAGE 94
Planning Database Security and Recovery     Planning Guidelines Managing Permissions for Files in OSS Space on page 11-12 Managing Permissions for Files in Guardian Space on page 11-13 Securing User Modules on page 11-16 Managing Network Security on page 13-11 Planning Guidelines When you plan your SQL/MX database security you should consider:    Database Access and Security OSS File and Directory Security Safeguard Security Database Access and Security Access to SQL/MX database objects is gov
PAGE 95
Planning Database Security and Recovery       OSS File and Directory Security x (execute) for execute the file as a program, or search a directory X for S_IXUSR, S_IXGRP, or S_IXOTH in the “execute” position s for set user ID or set group ID, in the owner or group “execute” position t for test segment (the sticky bit), in the others “execute” position s for an AF_UNIX socket under types - for “regular” file under types Users on the system are classified as one or more of the following:    u (
PAGE 96
Planning Database Security and Recovery Safeguard Security The super ID can include a umask command in the /etc/profile file to specify the user mask for all users who log on to the shell. An individual user can also include a umask command in his or her profile file to establish a personal user mask. Safeguard Security For additional security protection, use the Safeguard product to restrict access to physical Guardian volumes and subvolumes containing the distributed SQL/MX product component files.
PAGE 97
Planning Database Security and Recovery  Safeguard Security Safeguard process-protection records can control who is authorized to use specific process names. Safeguard access control lists cannot be used to protect OSS files. Access to OSS files is controlled by OSS file-permission bits. User Administration Authentication records for all system users, including those who work in the OSS environment, must be added and managed by using SAFECOM USER commands.
PAGE 98
Planning Database Security and Recovery Planning Database Recovery Planning Database Recovery HP provides several recovery mechanisms, including:     Mirrored disk volumes are a primary protection against disk failures. These volumes also provide the ability to repair and maintain disk volumes online, without interrupting application processing. For information about how to use mirrored disk volumes, see the Guardian User’s Guide and the Guardian Disk and Tape Utilities Reference Manual.
PAGE 99
Planning Database Security and Recovery Database Recovery Guidelines Database Recovery Guidelines When planning backup strategies, consider these guidelines: Topic Guideline SQL/MX subvolume and file naming guidelines In planning database recovery, it is important to properly follow the rules for naming SQL/MX subvolumes and files. For information, see SQL/MX Subvolume Naming Guidelines on page 7-3. Using Guardian names with TMF, RDF, and Measure For SQL/MX Release 3.
PAGE 100
Planning Database Security and Recovery Recovering Dropped Database Objects Recovering Dropped Database Objects The method for recovering an accidentally dropped SQL/MX object depends on whether that object is a catalog, table, index, or view. Recovery of a single view or index is usually a straightforward operation. Recovery of a table or catalog, however, can be complex and difficult if there are multiple dependent objects.
PAGE 101
Planning Database Security and Recovery   Recovering Dropped Database Objects Maintain a hard copy of the entire TMF catalog, using the TMFCOM INFO DUMPS, OBEYFORM command. Whenever you request a TMF online dump, back up that dump to tape and use the TMFCOM INFO DUMPS, OBEYFORM command to obtain a hard copy with that tape. For good TMF practice, be sure to maintain a backup copy of the entire TMF catalog on tape.
PAGE 102
Planning Database Security and Recovery Using TMF for Transaction Recovery, Database Consistency, and Database Recovery Using TMF for Transaction Recovery, Database Consistency, and Database Recovery TMF protects SQL/MX databases by managing database transactions, monitoring database activity through the use of audit trails, and enabling database recovery.
PAGE 103
Planning Database Security and Recovery Levels of Database Recovery With TMF Audited Files Files protected by TMF are called audited files. All SQL/MX files (including files created for tables) are audited by TMF, and changes to them are logged to audit trails. Audit Dumps An audit dump is a copy of an audit trail file written to a tape or disk volume by an audit dump process. If audit dumping is configured, audit dumps occur automatically when an audit trail file becomes full.
PAGE 104
Planning Database Security and Recovery NonStop SQL/MX Requirements for TMF TMF handles backout operations without operator intervention by using the audit trails automatically cycled by TMF. The TMF backout process uses before-images in audit trails to undo the effects of an aborted transaction. Volume Recovery Volume recovery recovers the database in the event of a disk crash or system failure.
PAGE 105
Planning Database Security and Recovery Guidelines for Configuring TMF Some previously compiled programs or previously prepared DML statements execute successfully when TMF is unavailable if the statements do not require TMF transactions. These DML statements include queries (SELECT statements or cursor operations) that specify BROWSE access on tables and views.
PAGE 106
Planning Database Security and Recovery   Guidelines for Creating and Managing Online Dumps The volume or volumes containing the audit trails must have sufficient free space to accommodate the extents required for the number of audit-trail files. If there is insufficient space to create a new audit-trail file, transactions can be suspended while the TMF operator dumps older audit-trail files to tape and frees enough space to continue.
PAGE 107
Planning Database Security and Recovery Operations That Impact TMF Online Dumps should always make new online dumps to ensure the new status of the database is recorded correctly. See Operations That Impact TMF Online Dumps on page 5-15.  TMF can retain online dumps of several generations of each file. The number of generations retained depends on the RETAINDEPTH option of TMF configuration parameters. Each generation of an online dump provides a starting point for a file recovery operation.
PAGE 108
Planning Database Security and Recovery Operations That Impact TMF Online Dumps Table 5-1. SQL/MX Operations That Impact TMF Online Dumps (page 1 of 2) Operation Option Effect Recovery Strategy CREATE INDEX POPULATE (or no option specified) Does not invalidate any existing online dumps. Make online dumps of all partitions of the new index to provide TMF file recovery protection. NO POPULATE Does not invalidate any existing online dumps.
PAGE 109
Planning Database Security and Recovery Using the TMF DUMP FILES Command Table 5-1. SQL/MX Operations That Impact TMF Online Dumps (page 2 of 2) Operation Option Effect Recovery Strategy MODIFY utility (MODIFY INDEX and MODIFY TABLE) ADD (split range partition) Does not invalidate online dumps of existing partitions. Make an online dump of the new partition to retain TMF file recovery protection.
PAGE 110
Planning Database Security and Recovery Using the TMF DUMP FILES Command For example, if an SQL/MX table to be dumped has the definition: CREATE TABLE CAT.SCH.T056T11 ( WREN INT NO DEFAULT -- NOT NULL NOT DROPPABLE , JUNCO INT NO DEFAULT -- NOT NULL NOT DROPPABLE , BLACKBIRD INT DEFAULT NULL , CONSTRAINT CAT.SCH.T056T11_104569472_0002 PRIMARY KEY (WREN ASC, JUNCO ASC) NOT DROPPABLE , CONSTRAINT CAT.SCH.T056T11_104569472_0001 CHECK (CAT.SCH.T056T11.WREN IS NOT NULL AND CAT.SCH.T056T11.
PAGE 111
Planning Database Security and Recovery TMF Considerations in Using MXCI The corresponding DUMP FILES command to dump only the files in this table would be: DUMP FILES & ( & $DATA1.ZSDADLON.NSPD2Q00,& $DATA2.ZSDADLON.N0FK2Q00,& $DATA3.ZSDADLON.N75R2Q00,& $DATA4.ZSDADLON.OFVY2Q00,& $DATA5.ZSDADLON.ONL42Q00,& $DATA1.ZSDADLON.R70U5Q00,& $DATA2.ZSDADLON.SFQ15Q00,& $DATA3.ZSDADLON.SNG85Q00,& $DATA1.ZSDADLON.NSPD2Q01,& $DATA2.ZSDADLON.N0FK2Q01,& $DATA3.ZSDADLON.N75R2Q01,& $DATA4.ZSDADLON.OFVY2Q01,& $DATA5.
PAGE 112
Planning Database Security and Recovery Using TMF In Recovery Operations These commands and statements make up a complete user-defined TMF transaction:         BEGIN WORK; SELECT...; INSERT...; DELETE...; COMMIT WORK; For many utility statements, the utility operation generates the appropriate number of TMF transactions for the operation, reducing the overhead associated with TMF audit trails and ensuring that the necessary locks are acquired for the operations.
PAGE 113
Planning Database Security and Recovery Using RDF to Set Up a Backup System Using RDF to Set Up a Backup System The RDF subsystem monitors changes made to a production database on a local (primary) system and maintains a copy of that database on a remote (backup) system. Because it applies changes to the backup database as soon as they are detected on the primary system, the RDF subsystem keeps the backup database continuously up to date with changes made by business applications on the primary system.
PAGE 114
Planning Database Security and Recovery Configuring Disk Volumes for RDF Configuring Disk Volumes for RDF You perform disk volume configuration for RDF as you do for SQL/MX databases. HP recommends that volume names match exactly on the primary and backup nodes. If they do not, they can be remapped for RDF with the RDF SET VOLUME configuration command.
PAGE 115
Planning Database Security and Recovery Using Backup and Restore 2 to Create Offline RDF Backup Databases Using Backup and Restore 2 to Create Offline RDF Backup Databases Use Backup and Restore 2 to specify:   An RDF backup catalog name that is different from the primary catalog, using the TGT CATALOG syntax The physical location of SQL/MX objects as they are restored, using the LOCATION option to restore an SQL/MX table file to a different location Using the Target Catalog Option of RESTORE You sho
PAGE 116
Planning Database Security and Recovery Using Backup and Restore 2 to Create Offline RDF Backup Databases Each source-filename specified is restored to the exact dest-filename to which it is mapped. The main purpose of this option is to restore a database with the exact same file names as the source database. Use this option if you are using RESTORE to create an RDF backup database in which every partition of the source object must be explicitly mapped to its correct destination.
PAGE 117
Planning Database Security and Recovery Using Backup and Restore 2 to Create Offline RDF Backup Databases 3. Create DDL scripts for all replicated database objects. Use the LOCATION clause to specify the Guardian file names, making sure that they are the same on the backup system as the primary system. Change the primary catalog name to the backup catalog name for each table created. Alternately, the catalog name can be removed, and a default catalog name can be used. 4.
PAGE 118
Planning Database Security and Recovery Using Backup and Restore 2 to Create Offline RDF Backup Databases Retrieving Saved DDL The DDL for a CREATE TABLE statement is written to an OSS file named /usr/tandem/sqlmx/ddl/catalog.schema.tablename-yyyymmddtimestamp.ddl. For example, if the table CAT.SCH.TAB01 is dropped at 11:57:15 am on April 23, 2011, the saved DDL file is: /usr/tandem/sqlmx/ddl/CAT.SCH.TAB01-20110423-115715.
PAGE 119
Planning Database Security and Recovery Using Backup and Restore 2 for SQL/MX Database Recovery Using Backup and Restore 2 for SQL/MX Database Recovery Use Backup and Restore 2 to back up to tape and restore to disk SQL/MX objects, OSS program files, and other components, as required to properly recover your database. Backup and Restore 2 uses BRCOM, the command-line user interface from which you enter and manage BACKUP and RESTORE commands for SQL/MX and OSS objects.
PAGE 120
Planning Database Security and Recovery Using Backup and Restore 2 for SQL/MX Database Recovery Table 5-2. Backup and Restore of SQL/MX and OSS Objects (page 1 of 3) SQL/MX or OSS Object BACKUP Command RESTORE Command Catalog Explicitly backs up the named catalog and all subordinate objects. See Backing Up Catalogs on page 5-31. Explicitly restores the named catalog and all subordinate objects. Schema Explicitly backs up the named schema and all subordinate objects.
PAGE 121
Planning Database Security and Recovery Using Backup and Restore 2 for SQL/MX Database Recovery Table 5-2. Backup and Restore of SQL/MX and OSS Objects (page 2 of 3) SQL/MX or OSS Object BACKUP Command RESTORE Command Index Explicitly backs up the named index. Unless specifically excluded, an index is automatically backed up when you back up its associated catalog or schema. See Backing Up Indices and Index Partitions on page 5-33 Explicitly restores the named index.
PAGE 122
Planning Database Security and Recovery Frequency of Backups Table 5-2. Backup and Restore of SQL/MX and OSS Objects (page 3 of 3) SQL/MX or OSS Object BACKUP Command RESTORE Command OSS program executable file or user module Does not automatically back up program executables or user modules. You back up program files or executables by specifying OSS backup objects. See Backing Up and Restoring Program Executables and User Modules on page 5-35.
PAGE 123
Planning Database Security and Recovery Backup Objects Hierarchy A daily backup should either be a full backup of all database objects or a limited backup of specific objects. If you use limited daily backups, you should also perform periodic full backups, as explained in Periodic Full Backups. Periodic Full Backups A full backup performed periodically might be adequate for protecting your database.
PAGE 124
Planning Database Security and Recovery SQL/MX Objects That Are Backed Up Explicitly You can specify the MX CATALOG backup object to back up one or more catalogs: BR> BACKUP $tape-drive, MX CATALOG mycat1; You can also use it to specify every catalog in the SQL/MX file system. BR> BACKUP $tape-drive, MX CATALOG CAT*; You can specify either CAT or CATALOG in the BACKUP command.
PAGE 125
Planning Database Security and Recovery SQL/MX Objects That Are Backed Up Implicitly You cannot use the PARTONLY option with the INDEXES INCLUDED option. Caution. Use caution when using PARTONLY in Backup and Restore 2 operations for partitioned tables. It is possible to make the primary and secondary partitions of a table inconsistent, both with each other and with indexes (for example, by performing DML operations on only one of two backed up partitions before you restore both).
PAGE 126
Planning Database Security and Recovery SQL/MX Objects That Are Not Restored by BRCOM SQL/MX Objects That Are Not Restored by BRCOM You cannot use Backup and Restore 2 to restore these SQL/MX objects:      GRANT and REVOKE privileges Referential integrity constraints SPJs Triggers Views However, except for GRANT and REVOKE privileges, you can capture the DDL for the CREATE statements executed for these objects and use this information to manually re-create these objects after a Restore operation.
PAGE 127
Planning Database Security and Recovery Backing Up and Restoring OSS Program Files and SPJs Backing Up and Restoring OSS Program Files and SPJs Backing Up and Restoring Program Executables and User Modules Program executables and user modules in the SQL/MX environment are stored in OSS directories.
PAGE 128
Planning Database Security and Recovery Using the RESTORE SHOWDDL ON Option to ReCreate Objects For detailed information about backing up and restoring OSS objects, see the Backup and Restore 2 Manual. Using the RESTORE SHOWDDL ON Option to Re-Create Objects Every backup operation captures the DDL from all SQL/MX CREATE statements that have been executed. This capture always occurs; there is no backup job option to prevent it.
PAGE 129
6 Reviewing and Setting System Defaults NonStop SQL/MX uses system-defined default settings for attributes associated with compiling and executing queries. The system-defined default settings, which are hard-coded settings, are optimal in most cases. Under some circumstances, however, you might want to override a specific system-defined default setting.
PAGE 130
Reviewing and Setting System Defaults Consequences of Inserting Rows Into the SYSTEM_DEFAULTS Table The statements in this example update the current default setting for the transaction isolation level on \MYSYS: SET SCHEMA nonstop_sqlmx_mysys.
PAGE 131
Reviewing and Setting System Defaults Alternative Settings For Selected System Defaults Table 6-1. Settings for Selected System Defaults (page 1 of 5) System Default Default Value Alternative Setting CACHE_HISTOGRAMS The default value is ON. The value OFF means histograms are only fetched for columns of a table that are involved in a statement. CACHE_HISTOGRAMS_ REFRESH_INTERVAL The default value is 3600.
PAGE 132
Reviewing and Setting System Defaults Alternative Settings For Selected System Defaults Table 6-1. Settings for Selected System Defaults (page 2 of 5) System Default Default Value Alternative Setting NATIONAL_CHARSET The default value is UCS2. The value ISO88591 is appropriate if ISO88591 is the preferred national character set. A value for NATIONAL_CHARSET is set when you run the InstallSqlmx script during installation of NonStop SQL/MX. This value can be changed only by reinstalling NonStop SQL/MX.
PAGE 133
Reviewing and Setting System Defaults Alternative Settings For Selected System Defaults Table 6-1. Settings for Selected System Defaults (page 3 of 5) System Default Default Value Alternative Setting QUERY_CACHE_REQUIRED_ PREFIX_KEYS The default value is 255. A value greater than the number of columns in a composite key indicates all columns of the key are required.
PAGE 134
Reviewing and Setting System Defaults Alternative Settings For Selected System Defaults Table 6-1. Settings for Selected System Defaults (page 4 of 5) System Default Default Value Alternative Setting REF_CONSTRAINT_NO_ ACTION_LIKE_RESTRICT The default value is SYSTEM. This default affects the behavior of a new referential constraint specified with NO ACTION.
PAGE 135
Reviewing and Setting System Defaults Alternative Settings For Selected System Defaults Table 6-1. Settings for Selected System Defaults (page 5 of 5) System Default Default Value Alternative Setting SCRATCH_DISKS_ PREFERRED The default is for the system to determine what disks to use. Set to a list of scratch disk volumes, where each item in the list has the form \node.$volume, and the items in the list are separated by a comma (,).
PAGE 136
Reviewing and Setting System Defaults Settings Required to Achieve ANSI Compliance Settings Required to Achieve ANSI Compliance Table 6-2 lists the system default settings required to achieve ANSI compliance. If these settings are used, all except these will have a significant negative impact on the performance of your database:    NAMETYPE RECOMPILATION_WARNINGS REF_CONSTRAINT_NO_ACTION_LIKE_RESTRICT Table 6-2.
PAGE 137
7 Creating an SQL/MX Database When you install and initialize NonStop SQL/MX on a system node, NonStop SQL/MX creates the system catalog and several system schema. After the installation is complete, you can create your own user catalogs and schemas. Creating a catalog updates the system schema. The definition schema for a user catalog is created when you create the first schema in that catalog. See Creating SQL/MX Metadata on page 7-6 for more information.
PAGE 138
Creating an SQL/MX Database Accessing an SQL/MP Database From NonStop SQL/MX Accessing an SQL/MP Database From NonStop SQL/MX From NonStop SQL/MX, you can run queries on an SQL/MP database located on the same node. Such queries must conform to these guidelines:      Perform DML operations only. From NonStop SQL/MX, you cannot perform DDL operations or utility operations on SQL/MP database objects.
PAGE 139
Creating an SQL/MX Database SQL/MX Subvolume Naming Guidelines user-specified names. These rules are in addition to Guardian naming rules already enforced by the Guardian file system. Note. NonStop SQL/MX additionally restricts system-generated names by excluding vowels to prevent names containing actual words that could be unexpected or misleading.
PAGE 140
Creating an SQL/MX Database Resource Forks For more information about file naming, see the SQL/MX Reference Manual. Resource Forks NonStop SQL/MX maintains substantial information about data files in companion files known as resource forks. Resource forks are located in the same volume and subvolume as data files and have the same name as their corresponding data file, with the final “0” (zero) changed to “1” (one).
PAGE 141
Creating an SQL/MX Database Using the Subvolume Option in the CREATE SCHEMA Statement that conforms to the rules specified in SQL/MX Subvolume Naming Guidelines on page 7-3. Otherwise, an error is generated. When you create SQL/MX tables and indexes, you would typically use the LOCATION clause to partition data on particular volumes, without regard to the Guardian subvolume and file name assigned to these partitions.
PAGE 142
Creating an SQL/MX Database Correcting File Name Problems One use of the schema subvolume is to identify all Guardian files in an schema for use with Guardian-based commands for TMF, RDF, and other subsystems. Unless you use the REPEAT USE ALLOWED clause of CREATE SCHEMA, NonStop SQL/MX prevents you from specifying a subvolume name that is already in use by another schema. If you use this clause, you will receive a warning and the operation will succeed.
PAGE 143
Creating an SQL/MX Database      Creating Catalogs SYSTEM_DEFAULTS_SCHEMA schema, which contains the default settings for options and other attributes used with MXCI commands and SQL/MX statements. MXCS_SCHEMA schema, which contains the metadata information needed by MXCS. SYSTEM_SQLJ_SCHEMA, which contains the stored procedures VALIDATEROUTINE and VALIDATEROUTINE2.
PAGE 144
Creating an SQL/MX Database Rules for Creating and Dropping Catalogs used by a catalog on another node. If you intend to register the remote catalog from either node, do not use the same catalog names. For more information about catalog naming, see the SQL/MX Reference Manual. Rules for Creating and Dropping Catalogs     Any user on a node can create a catalog on that node. Any user on a node can drop an empty catalog. A nonempty catalog cannot be dropped.
PAGE 145
Creating an SQL/MX Database Schema Naming A schema is a named collection of SQL/MX database objects, including tables, views, indexes, and so forth. Each object is described in exactly one schema, although an object can refer to objects that are described in other schemas. A schema cannot contain other schemas. Schema Naming The schema name is an SQL identifier that represents the second part of the three-part ANSI name of the form catalog.schema.name.
PAGE 146
Creating an SQL/MX Database Access Privileges for SQL/MX Database Objects Access Privileges for SQL/MX Database Objects In NonStop SQL/MX, Guardian and Safeguard security are not applicable. Security for SQL/MX tables and views is implemented by the ANSI standard schema ownership rules and by using the GRANT statement to grant access privileges to specified users. The schema in which a table or view is located is the unit of object ownership. The user who creates the schema is the owner of that schema.
PAGE 147
Creating an SQL/MX Database Examples for Granting Privileges Examples for Granting Privileges This example grants SELECT privileges on a table, but not the privilege of granting SELECT privileges to others: GRANT SELECT ON TABLE persnl.employee TO "sql.user1"; This example grants SELECT and DELETE privileges on a table, including the privilege of granting SELECT and DELETE privileges to others: GRANT SELECT, DELETE ON TABLE sales.odetail TO "sql.user1", "sql.
PAGE 148
Creating an SQL/MX Database Security Rules for Creating and Altering SQL/MX Tables For information about loading tables, see the guidelines for loading tables in Section 10, Reorganizing SQL/MX Tables and Maintaining Data. Security Rules for Creating and Altering SQL/MX Tables Table security ultimately defines much of the security for indexes and views.
PAGE 149
Creating an SQL/MX Database Creating and Using Keys multiple occurrences of other types of constraints. For more information about constraints, see the SQL/MX Reference Manual. Example for Creating an SQL/MX Table With a User-Defined Primary Key This example creates a single-partition SQL/MX table with a user-defined primary key: CREATE TABLE CAT1.SCHEMA1.
PAGE 150
Creating an SQL/MX Database   Creating and Using Keys The columns of the clustering key cannot contain null values. If you do not specify NOT NULL for a clustering key column, an error is returned. After you have defined the clustering key, you cannot change the choice of which columns compose the clustering key.
PAGE 151
Creating an SQL/MX Database Creating and Managing Partitions for SQL/MX Tables Creating and Using a Partitioning Key The partitioning consists of the columns you specify in the PARTITION BY clause of CREATE TABLE and CREATE INDEX statements. The partitioning key determines how data is distributed into partitions that are associated with table or index objects.
PAGE 152
Creating an SQL/MX Database Creating and Managing Partitions for SQL/MX Tables SQL/MX tables can use range partitioning or hash partitioning:   With range partitioning, you define key ranges for each partition, and each record is assigned to the partition whose range includes the value of its partitioning key. With hash partitioning, NonStop SQL/MX uses a hash function on the values of the partitioning key, and each record is assigned to a partition based on the result.
PAGE 153
Creating an SQL/MX Database Creating and Managing Partitions for SQL/MX Tables Restrictions on Creating and Placing Partitions     When you create a hash-partitioned table with a single partition, you can add additional hash partitions to the table. However, you cannot change the partitioning scheme to range partitioning. If you do not specify a partitioning scheme when you create a single-partition table, you can only add range partitions to the table. You cannot add hash partitions to the table.
PAGE 154
Creating an SQL/MX Database Creating and Managing Partitions for SQL/MX Tables Using HASHPARTFUNC to Plan and Build Hash-Partitioned Tables NonStop SQL/MX uses the HASHPARTFUNC function to hash partition data. NonStop SQL/MX passes column values and the number of partitions, n, to HASHPARTFUNC. The function then returns a hash value in the range 0 through n-1, where n is the number of partitions. This hash value determines the partition where a row with the given colum value(s) will be stored.
PAGE 155
Creating an SQL/MX Database Creating and Managing Partitions for SQL/MX Tables Guidelines for Creating a Balanced Hash-Partitioned Table To create a hash-partitioned table with a fairly balanced distribution of data across the partitions:   Make sure that you have an appropriate number of partitions relative to the unique entry count of the partitioning key. The partitioning key unique entry count refers to the total number of unique values derived from the columns that comprise the partitioning key.
PAGE 156
Creating an SQL/MX Database Creating and Managing Partitions for SQL/MX Tables different from the clustering key. The choice of leftmost primary key column—and the partitioning strategy—can greatly affect access. Choose a leftmost key column that minimizes contention while allowing acceptable load and delete performance.
PAGE 157
Creating an SQL/MX Database Additional Guidelines for Creating Tables Example for Creating an SQL/MX Table With Multiple Hash Partitions CREATE TABLE cat1.sch1.
PAGE 158
Creating an SQL/MX Database Managing Table Data in an OSS file than interactively in MXCI. Optionally, do a SHOWDDL and send the results to an OSS file.   Consider creating dependent objects at the same time you create a table. To simplify these operations, put all statements (such as CREATE TABLE, CREATE VIEW, and CREATE INDEX) in the same OSS OBEY command file. Alternately, you can incorporate these create statements into the CREATE SCHEMA statement.
PAGE 159
Creating an SQL/MX Database Managing Table Data delimited names are. For more information about SQL identifiers and SQL/MX reserved words, see the SQL/MX Reference Manual.   Use descriptive column names for your application to help programmers and users remember the names correctly. Nondelimited column names can be specified in the CREATE TABLE statement in any combination of uppercase and lowercase letters. For example, these three column names are equivalent: LOCATION, Location, and location.
PAGE 160
Creating an SQL/MX Database  Managing Table Data A SIGNED column is required for a number with 10 or more digits. Performance Considerations To achieve maximum performance when defining columns:        Define the column data type so that the values stored in the column match the use of the data in applications. To improve application performance, try to eliminate unnecessary data conversion in programs. For the best performance, varying-length columns should be trailing.
PAGE 161
Creating an SQL/MX Database Managing Table Data Defining Character Data NonStop SQL/MX includes both fixed-length character data and variable-length character data. The data types for character data are: Fixed-length characters CHAR PIC NCHAR, NATIONAL CHAR, or NATIONAL CHARACTER Variable-length characters CHAR VARYING VARCHAR NCHAR VARYING or NATIONAL CHAR VARYING For NonStop SQL/MX, the maximum row size is approximately 32 KB, provided the Control Query Default DEFAULT_BLOCKSIZE is set to 32768.
PAGE 162
Creating an SQL/MX Database Managing Table Data Restrictions on Using Character Set Data Use only ISO88591 characters in these contexts: ISO88591 Field Where Found BY partitioning-column CREATE INDEX, CREATE TABLE CHECK constraint text CREATE TABLE, ALTER TABLE Column default values CREATE TABLE, ALTER TABLE, CREATE VIEW Column HEADING text CREATE TABLE, ALTER TABLE FIRST KEY values CREATE TABLE, CREATE INDEX, MODIFY TABLE sql-datatype character string data types CREATE PROCEDURE SQL/MX name
PAGE 163
Creating an SQL/MX Database Managing Table Data When NonStop SQL/MX performs arithmetic operations on operands that have mixed data types, the data type allowing the largest value is used to evaluate the numbers. For instance, if a REAL number is used and REAL is the data type that allows the largest value, all other numeric data types are converted first to REAL and then used in the expression.
PAGE 164
Creating an SQL/MX Database Managing Table Data MINUTE Minutes of hour 00 to 59 SECOND* Second of minute 00 to 59 *The SECONDS field can have an optional fractional part of from zero to six digits in length. The maximum value of DAY depends on the length of the month.
PAGE 165
Creating an SQL/MX Database           Managing Table Data The range of fields defined for an INTERVAL column can limit the value stored, as shown: COLUMN_1 INTERVAL YEAR COLUMN_2 INTERVAL HOUR (3) COLUMN_3 INTERVAL YEAR TO MONTH COLUMN_4 INTERVAL DAY TO MINUTE The fields in a datetime or INTERVAL value have this implied order: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
PAGE 166
Creating an SQL/MX Database Managing Table Data Using Default and Null Values In NonStop SQL/MX, a null value is a marker that indicates that a column in a specified row has no value. The null value is not treated as a normal data value. It serves only as a placeholder necessary for certain relational operations. To an application interacting with a database, the null value indicates “unknown” or “do not know.
PAGE 167
Creating an SQL/MX Database    Managing Table Data If the number of digits to the left of the decimal point exceeds the number in the length (or length minus scale, if you specified scale for the column), NonStop SQL/MX issues an error message.  For a datetime column, literal must be datetime literal with a precision that matches the precision of the column.  For an INTERVAL column, literal must be an INTERVAL literal that has the range of INTERVAL fields defined for the column.
PAGE 168
Creating an SQL/MX Database Managing Table Data this type of column as NOT NULL. If you define the unique key through a unique constraint, the column must be NOT NULL. Unique multicolumn indexes can contain columns with null values. The same rule applies as for single-column unique indexes; that is, the index can have at most one row of all null values in the columns. Null values are treated as all other values and therefore cause duplication of rows in the same way.
PAGE 169
Creating an SQL/MX Database Creating Indexes for SQL/MX Tables Understanding Data Type Correspondence With Host Languages For information about data type correspondence between NonStop SQL/MX and the host programming languages used for embedded SQL/MX, see the SQL/MX Programming Manual for C and COBOL. Creating Indexes for SQL/MX Tables An index provides an alternate access path to a table that differs from the inherent access path (clustering key).
PAGE 170
Creating an SQL/MX Database Creating an Index Creating an Index To create an index, use the CREATE INDEX statement. If the underlying table contains data, the creation process automatically loads the index unless you specify NO POPULATE. When you define an alternate index, first consider the column-related guidelines described under Defining Table Columns on page 7-22.
PAGE 171
Creating an SQL/MX Database       Creating and Managing Partitions for SQL/MX Indexes Indexes can be updated in parallel by the disk process while the table is being updated. To take full advantage of parallel updating, you should create a table’s indexes on separate disk volumes, with each disk volume configured for a separate processor. The performance effects of parallel updates are discussed in Section 15, Enhancing SQL/MX Database Performance.
PAGE 172
Creating an SQL/MX Database Creating and Managing Partitions for SQL/MX Indexes Use the MODIFY INDEX utility to manage range-partitioned and hash-partitioned indexes. For detailed information and examples, see the information on using MODIFY to manage table and index partitions in Section 10, Reorganizing SQL/MX Tables and Maintaining Data. An important consideration when you partition indexes is whether to co-locate the index partitions with the base table partitions.
PAGE 173
Creating an SQL/MX Database Creating Constraints on SQL/MX Tables Creating Constraints on SQL/MX Tables Constraints are rules that protect the integrity of data in a table by restricting the values in a particular column or set of columns to those that meet the conditions of the constraints. NonStop SQL/MX enforces the constraint criteria when the constraint is created, when table rows are updated and or inserted, and when table rows are deleted.
PAGE 174
Creating an SQL/MX Database Naming Constraints If you define several NOT NULL NOT DROPPABLE constraints on a table, NonStop SQL/MX automatically combines them into one constraint. This approach ensures that, at run time, only one constraint check is performed. If you perform a SHOWDDL, all constraint information is shown as one constraint. Combining constraints is implemented only for NOT NULL NOT DROPPABLE constraints.
PAGE 175
Creating an SQL/MX Database Guidelines for Creating Constraints When working with large tables, you should create the supporting index for each table before you create the constraint. You precreate the index by issuing a CREATE [UNIQUE] INDEX on the columns you want to specify in the constraint. UNIQUE is required for the PRIMARY KEY and UNIQUE constraints. Then you issue the ALTER TABLE ADD CONSTRAINT statement.
PAGE 176
Creating an SQL/MX Database Rules for Creating and Accessing Views subqueries— from several SQL/MX tables. You cannot use columns from SQL/MP tables. Use the CREATE VIEW statement to define new column names for a view, instead of using the column names from the underlying tables. When using the view, applications use the view-defined column names. Applications can also use views to rename, reorder, and project subsets of columns from one or more tables.
PAGE 177
Creating an SQL/MX Database Creating Triggers Creating Triggers Use the CREATE TRIGGER statement to create triggers on SQL/MX tables. A trigger is a mechanism that sets up the database to perform certain actions automatically in response to the occurrence of specified events. To create a trigger, you must own its schema or be the super ID user. You must also own the schema where the subject table resides and have appropriate privileges on the referenced table.
PAGE 178
Creating an SQL/MX Database           Align UCS2 character types on two-byte boundaries. Align numeric data types as follows:     Database Design Guidelines for Improving OLTP Performance Align smallint on two-byte boundaries. Align int on four-byte boundaries. Align largeint on eight-byte boundaries. Use fillers as required to attain proper alignment:  Recommended: CREATE TABLE t1, where column a is SMALLINT NOT NULL, c is SMALLINT NOT NULL, and b is INT NOT NULL.
PAGE 179
Creating an SQL/MX Database  Database Design Guidelines for Improving OLTP Performance If you add a column, reload the table. HP NonStop SQL/MX Release 3.
PAGE 180
Creating an SQL/MX Database Database Design Guidelines for Improving OLTP Performance HP NonStop SQL/MX Release 3.
PAGE 181
8 Querying SQL/MX Metadata Use the commands described in this section to obtain the information you need to manage a database.
PAGE 182
Querying SQL/MX Metadata Commands for Displaying Information NonStop SQL/MX stores information about object structures in file labels, resource forks, and metadata, as follows: File labels Contain file structure information and security settings. Use the MXCI SHOWLABEL command to obtain file-label information. Most of the information in file labels is the same as the metadata.
PAGE 183
Querying SQL/MX Metadata mxtool INFO Command mxtool INFO Command Use the mxtool INFO command to display information about a database, including SQL/MX-related information for a given Guardian file set list. Because access to metadata is open to all users, anyone can run the mxtool INFO command. For detailed information and examples of this command, see the SQL/MX Reference Manual.
PAGE 184
Querying SQL/MX Metadata SHOWDDL Command SHOWDDL Command Use the MXCI SHOWDDL command to display the DDL syntax equivalent to the syntax that was used to create an SQL/MX table, view, or SPJ and its dependent objects. This command also supports SQL/MP alias names. You can choose to display the DDL for the underlying SQL/MP object and its dependent objects either in SQL/MX or in SQL/MP DDL syntax. The command is especially useful for displaying a thorough description of an object as it exists in metadata.
PAGE 185
Querying SQL/MX Metadata SQL/MX Metadata Tables SQL/MX Metadata Tables Figure 8-1 shows the SQL/MX metadata table structure. Figure 8-1. SQL/MX Metadata Table Structure Catalog Schemas NONSTOP_SQLMX_nodename SYSTEM_SCHEMA Note: User catalogs are not shown.
PAGE 186
Querying SQL/MX Metadata     SQL/MX Metadata Tables SQL/MX metadata tables are quite different from SQL/MP catalogs. Each node contains a single NONSTOP_SQLMX_nodename catalog. This catalog contains metadata pertinent to the node as a whole. Figure 8-1 shows the metadata schemas and catalogs in the NONSTOP_SQLMX_nodename catalog. Typically, a node has additional user catalogs; Figure 8-1 does not show these user catalogs.
PAGE 187
Querying SQL/MX Metadata SQL/MX Metadata Tables Figure 8-2.
PAGE 188
Querying SQL/MX Metadata SQL/MX Metadata Tables Figure 8-2.
PAGE 189
Querying SQL/MX Metadata SQL/MX Metadata Tables Figure 8-2.
PAGE 190
Querying SQL/MX Metadata SQL/MX Metadata Tables Starting with SQL/MX Release 3.1, a new schema, called SYSTEM_SECURITY_SCHEMA is introduced. Figure 8-3 displays the tables in the new schema. Figure 8-3. SQL/MX Metadata Tables SYSTEM_SECURITY_SCHEMA MGM PRIVILEGE PRIVILEGED USERS PK PK GRANTEE USER_CLASS GRANTEE_TYPE GRANTOR GRANTOR_TYPE IS_GRANTABLE GRANT_TIMESTAMP PK PK PK GRANTEE PRIVILEGE_TYPE TARGET_UID GRANTOR GRANTOR_TYPE GRANTEE_TYPE GRANT_TIMESTAMP IS_GRANTABLE HP NonStop SQL/MX Release 3.
PAGE 191
Querying SQL/MX Metadata Understanding ANSI External and Internal Names Understanding ANSI External and Internal Names ANSI SQL objects have both external and internal names. When objects are created in NonStop SQL/MX, they are given external names. However, when the same objects are stored in the metadata, they are stored by their internal name. You need to be aware of external and internal naming when performing metadata queries.
PAGE 192
Querying SQL/MX Metadata Displaying System Schema Information for read uncommitted access order by object_name; Note that "my "" catalog" is in external format, and 'SALES' and 'my " catalog' are in internal format. The query does not work correctly if the internal and external names are incorrect.
PAGE 193
Querying SQL/MX Metadata  Locating System Schema Tables This example uses the Guardian FILES command from a TACL prompt to list the locations of the associated Guardian files for system schema tables on a local node. Note. Beginning with SQL/MX Release 2.2, you can specify fully-qualified ANSI names for SQL/MX tables and indexes in the FUP INFO command. FUP will perform a FUP INFO on each of the corresponding Guardian files for that object as if the Guardian names had been entered directly.
PAGE 194
Querying SQL/MX Metadata Locating System Schema Tables EOF: 98304 (0.0% USED) EXTENTS ALLOCATED: 1 INDEX LEVELS: 1 PARTITION ARRAY FORMAT2ENABLED HP NonStop SQL/MX Release 3.
PAGE 195
Querying SQL/MX Metadata  Locating System Schema Tables This example also uses TACL commands to locate the MXCS schema tables on ZSD1. 1> fup copy $system.zsqlmx.mxanchor SQLMX_MetaData_Loc=$DATA08 2> volume $data08.zsd1 3> files $DATA08.ZSD1 ASSOC200 DTQSFJ00 PX1KHJ00 Z9FPGJ00 ASSOC201 DTQSFJ01 PX1KHJ01 Z9FPGJ01 B7NMCJ00 ENVIRO00 Q4DNDJ00 B7NMCJ01 ENVIRO01 Q4DNDJ01 DATASO00 NAME2I00 RESOUR00 DATASO01 NAME2I01 RESOUR01 $DATA02 ZSDI 9> fup copy $system.zsqlmx.
PAGE 196
Querying SQL/MX Metadata Locating System Schema Tables EXTENTS ALLOCATED: 1 INDEX LEVELS: 1 PARTITION ARRAY FORMAT2ENABLED HP NonStop SQL/MX Release 3.
PAGE 197
Querying SQL/MX Metadata  Locating System Defaults Schema Tables This is an example from the OSS prompt: # cat /G/system/zsqlmx/mxanchor SQLMX_MetaData_Loc=$DATA02 # ls /G/data02/zsd0 catref00 catsys00 catref01 catsys01 schema00 schema01 schrep00 schrep01 # /usr/tandem/sqlmx/bin/mxtool info \$data02.zsd0.catsys00 Hewlett-Packard NonStop(TM) SQL/MX MXTOOL Utility 3.0 (c) Copyright 2003, 2004-2010 Hewlett-Packard Development Company, LP. File Name: \DMR15.$DATA02.ZSD0.
PAGE 198
Querying SQL/MX Metadata  Locating System Defaults Schema Tables In this example, for a node called \DMR15, the physical Guardian name of the SYSTEM_DEFAULTS table is $ DATA02.ZSD5VXWZ.M26FB700: showlabel nonstop_sqlmx_dmr15.system_defaults_schema.system_defaults; ============================================================= GuardianName: \DMR15.$DATA02.ZSD5VXWZ.M26FB700 AnsiName: NONSTOP_SQLMX_DMR15.SYSTEM_DEFAULTS_SCHEMA.
PAGE 199
Querying SQL/MX Metadata Locating System Defaults Schema Tables or has failed) Primary Extent Size: 128 Pages Secondary Extent Size: 256 Pages Max Extents: 512 Extents Allocated: 1 EOF: 32768 Index Levels: 1 HP NonStop SQL/MX Release 3.
PAGE 200
Querying SQL/MX Metadata  Locating System Defaults Schema Tables In the following example on a node called \DMR15, a SELECT statement to access information from the PARTITIONS, OBJECTS, SCHEMATA, and CATSYS tables to display volume and subvolume of the SYSTEM_DEFAULTS table is used. Then it uses the ls command on that volume and subvolume to get a list of the default schema table names. The additional files in the subvolume $DATA08.
PAGE 201
Querying SQL/MX Metadata Locating System Defaults Schema Tables Hewlett-Packard NonStop(TM) SQL/MX MXTOOL Utility 3.1 (c) Copyright 2003, 2004-2011 Hewlett-Packard Development Company, LP. File Name: \DMR15.$DATA08.ZSDA984F.FPJCX800 Object Schema Version: 3000 Ansi Name: NONSTOP_SQLMX_DMR15.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS Ansi NameSpace: TA  This example uses the SHOWLABEL command to locate the SYSTEM_DEFAULTS table; only part of the output is included. >>showlabel nonstop_sqlmx_.
PAGE 202
Querying SQL/MX Metadata Locating MXCS Schema Tables progress or has failed) RedoNeeded: F RollfwdNeeded: F UndoNeeded: F UnreclaimedSpace: F (If T, a Utility operation is in progress or has failed) Primary Extent Size: 128 Pages Secondary Extent Size: 256 Pages Max Extents: 512 Extents Allocated: 1 EOF: 32768 Index Levels: 1 Locating MXCS Schema Tables On a local node, the anchor file $SYSTEM.ZSQLMX.MXANCHOR contains a pointer to the location of the system catalog.
PAGE 203
Querying SQL/MX Metadata Locating MXCS Schema Tables This example uses TACL commands to locate the MXCS schema tables. 1> fup copy $system.zsqlmx.mxanchor SQLMX_MetaData_Loc=$DATA02 1 RECORDS TRANSFERRED 2> volume $data02.zsd1 3> files $DATA02.ZSD1 ASSOC200 NAME2I00 ASSOC201 NAME2I01 DATASO00 DATASO01 ENVIRO00 RESOUR00 RESOUR01 ENVIRO01 4> fup info assoc200, detail $DATA02.ZSD1.ASSOC200 28 Jan 2011, 12:25 SQL ANSI TABLE ANSI NAME NONSTOP_SQLMX_DMR15.MXCS_SCHEMA.ASSOC2DS RESOURCE FORK \DMR15.$DATA02.
PAGE 204
Querying SQL/MX Metadata Displaying Catalog Information Displaying Catalog Information This subsection discusses:       Displaying a Catalog UID on page 8-24 Displaying All Catalogs Visible on a Node on page 8-25 Determining Whether a Catalog is Local or Remote on page 8-25 Displaying All Schemas in a Catalog on page 8-26 Displaying the Guardian Location of the Metadata Tables for a Catalog on page 8-26 Displaying the Attributes of a Catalog on page 8-27 Related topic:  Displaying All Tables i
PAGE 205
Querying SQL/MX Metadata Displaying All Catalogs Visible on a Node Displaying All Catalogs Visible on a Node The CATSYS table contains one (and only one) row for each catalog that is visible on that node. This example displays a list of all the visible catalogs on a local node (where node-name is the name of the local node): >> select cat_name from nonstop_sqlmx _node-name.system_schema.
PAGE 206
Querying SQL/MX Metadata Displaying All Schemas in a Catalog Displaying All Schemas in a Catalog This example displays a list of all schemas in a catalog: >> set schema nonstop_sqlmx_.system_schema; --- SQL operation complete. >> select substring(schema_name, 1, 30) as schema_name, schema_uid from schemata s, catsys c where cat_name = 'SAMDBCAT' and s.cat_uid = c.
PAGE 207
Querying SQL/MX Metadata DDLKS000 MODULE00 MVS00000 MVSTIN00 PARTNS00 RIUU0000 TBLPRI00 TRGUSD00 VCOLTC00 VWTBLU00 DDLKS001 MODULE01 MVS00001 MVSTIN01 PARTNS01 RIUU0001 TBLPRI01 TRGUSD01 VCOLTC01 VWTBLU01 Displaying the Attributes of a Catalog DDPLKS00 MPPART00 MVSCOL00 MVSUSE00 REFCON00 ROUTIN00 TBLTEX00 TRIGS000 VCOLU000 DDPLKS01 MPPART01 MVSCOL01 MVSUSE01 REFCON01 ROUTIN01 TBLTEX01 TRIGS001 VCOLU001 KYCLU000 MVRGRP00 MVSJCL00 OBJECT00 REPLIC00 TBLCON00 TRGCU000 VCOLTA00 VIEWS000 KYCLU001 MVRGRP01
PAGE 208
Querying SQL/MX Metadata       Displaying a Schema UID Locating System Defaults Schema Tables on page 8-17 Displaying All Tables in a Schema on page 8-35 Displaying All Views in a Schema on page 8-40 Displaying the Schema Version on page 8-67 Displaying the System Schema Version on page 8-67 Displaying the Object Schema Version (OSV) on page 8-67 The SCHEMATA table on each node is a system schema table that describes all the schemas visible from the node.
PAGE 209
Querying SQL/MX Metadata Displaying All Schemas Visible on a Node Displaying All Schemas Visible on a Node This example selects information from the CATSYS and SCHEMATA tables to display a list of all the schemas visible on a node: >>set schema nonstop_sqlmx_.system_schema; --- SQL operation complete. >>select substring(schema_name,1,30) as schema_name, cat_uid, schema_uid from nonstop_sqlmx_.system_schema.
PAGE 210
Querying SQL/MX Metadata Displaying All Objects in a Schema Displaying All Objects in a Schema This example selects information from the CATSYS, SCHEMATA, and OBJECTS tables to display a list of all the objects within the SALES schema in the SAMDBCAT catalog. You can use a similar set of SELECT statements to display all the views or indexes on a node. >> set schema samdbcat.definition_schema_version_; --- SQL operation complete.
PAGE 211
Querying SQL/MX Metadata Displaying the Attributes of a Schema MVS_USED_UMD_343845152_51 CN ODETAIL TA ODETAIL_545675672_5188 CN ODETAIL_793765672_5188 CN ORDERS TA ORDERS_347711272_5188 CN ORDERS_917521272_5188 CN ORDREP TA PARTS TA PARTS_386351772_5188 CN PARTS_836161772_5188 CN XCUSTNAM IX XORDCUS IX XORDREP IX XPARTDES IX PK BT PK NN BT NN PK VI BT NN PK IX IX IX IX OTHER User Table User Table User Table User Table User Table User Table User Table User Table User Table User Table User Table User Tab
PAGE 212
Querying SQL/MX Metadata Displaying users with catalog and schema creation rights --- SQL operation complete. >> select substring(schema_name, 1, 30) as schema_name, schema_owner from schemata s, catsys c where cat_name = 'SAMDBCAT' and s.cat_uid = c.
PAGE 213
Querying SQL/MX Metadata Displaying Table Information c, NONSTOP_SQLMX_COBOLT.SYSTEM_SECURITY_SCHEMA.MGM_PRIVILEGES m where c.cat_uid = m.target_uid; GRANTEE PRIVILEGE_TYPE CAT_NAME ----------- -------------- ---------- 44802 CS CAT1 --- 1 row(s) selected.
PAGE 214
Querying SQL/MX Metadata Displaying All Tables in a Catalog Displaying All Tables in a Catalog This example displays all base table objects in the catalog SAMDBCAT: >> set schema samdbcat.definition_schema_version_; --- SQL operation complete. >> select substring (object_name, 1, 25) as table_name, substring(schema_name, 1, 30) as schema_name from nonstop_sqlmx_.system_schema.catsys c, nonstop_sqlmx_.system_schema.schemata s, objects o where c.cat_uid = s.
PAGE 215
Querying SQL/MX Metadata TRIGGER_USED VWS VW_COL_TBL_COLS VW_COL_USAGE VW_TBL_USAGE HISTOGRAMS HISTOGRAMS_FREQ_VALS HISTOGRAM_INTERVALS MVS_TABLE_INFO_UMD MVS_UMD MVS_USED_UMD PARTLOC PARTSUPP SUPPLIER T1 DEPT EMPLOYEE HISTOGRAMS HISTOGRAMS_FREQ_VALS HISTOGRAM_INTERVALS JOB MVS_TABLE_INFO_UMD MVS_UMD MVS_USED_UMD PROJECT CUSTOMER HISTOGRAMS HISTOGRAMS_FREQ_VALS HISTOGRAM_INTERVALS MVS_TABLE_INFO_UMD MVS_UMD MVS_USED_UMD ODETAIL ORDERS PARTS Displaying All Tables in a Schema DEFINITION_SCHEMA_VERSION_3000
PAGE 216
Querying SQL/MX Metadata Displaying All DDL Locks on a Table nonstop_sqlmx_.system_schema.schemata s, objects o where c.cat_uid = s.cat_uid and c.cat_name = 'SAMDBCAT' and s.schema_uid = o.schema_uid and s.schema_name = 'SALES' and o.
PAGE 217
Querying SQL/MX Metadata Displaying All DDL Locks on a Table where cat_name = 'SAMDBCAT' ) ) for read uncommitted access order by l.object_uid; LOCK_NAME ------------------------CUSTOMER_LOCK UTIL ---IM STATUS (EXPR) ----------- ---------------------4 \DMR15.$:2:619:1565916 HP NonStop SQL/MX Release 3.
PAGE 218
Querying SQL/MX Metadata Displaying the Attributes of a Table Displaying the Attributes of a Table This example displays some of the attributes of the base tables defined in the SAMDBCAT.SALES schema: >> set schema samdbcat.definition_schema_version_; --- SQL operation complete.
PAGE 219
Querying SQL/MX Metadata Displaying All Tables on a System Displaying All Tables on a System Displaying all tables on a node (system) is a two-step process. To display all tables on a node, you must: 1. Run a query that displays all the catalogs on a node (see Displaying All Catalogs Visible on a Node on page 8-25). 2. Run a query for each catalog that displays all the tables for that catalog (see Displaying All Tables in a Catalog on page 8-34).
PAGE 220
Querying SQL/MX Metadata Displaying All Views in a Schema Displaying All Views in a Schema This example displays all views in the schema SALES in the catalog SAMDBCAT: >>set schema samdbcat.definition_schema_version_; --- SQL operation complete. >>select substring (object_name, 1, 25) as view_name, object_name_space as name_space from nonstop_sqlmx_.system_schema.catsys c, nonstop_sqlmx_.system_schema.schemata s, objects o where c.cat_uid = s.cat_uid and c.
PAGE 221
Querying SQL/MX Metadata CUSTLIST ORDREP Displaying Information About SQL/MP Aliases N N Y N Y N Displaying Information About SQL/MP Aliases Topics in this subsection:   Displaying All SQL/MP Alias Names in a Schema on page 8-41 Displaying All Attributes of an SQL/MP Alias Name on page 8-42 The MP_PARTITIONS table contains the partition names of SQL/MP tables that have SQL/MP aliases.
PAGE 222
Querying SQL/MX Metadata Displaying All Attributes of an SQL/MP Alias Name Displaying All Attributes of an SQL/MP Alias Name This example displays some of the attributes for the SQL/MP aliases in the SAMDBCAT.SALES schema: >> set schema samdbcat.definition_schema_version_; --- SQL operation complete. >> select substring(object_name,1,25) as SQLMP_Alias, m.mppartition_name from nonstop_sqlmx_.system_schema.catsys c, nonstop_sqlmx_.system_schema.
PAGE 223
Querying SQL/MX Metadata Determining Whether a Table Has Indexes Related topics:   Displaying All Partitions for a Table or Index on page 8-48 Displaying All Columns in an Index on page 8-56 The OBJECTS table is the definition schema table that lists all indexes in a catalog. The ACCESS_PATHS table, located in the same schema for each catalog, describes the physical instances of indexes.
PAGE 224
Querying SQL/MX Metadata Displaying All Indexes for a Table ) ) for read uncommitted access; NUM_INDEXES -------------------1 Displaying All Indexes for a Table This example (when run on the node on which the metadata is located) displays all indexes for a given table: >> set schema samdbcat.definition_schema_version_; --- SQL operation complete. >> select substring (o1.object_name, 1, 40) as index_name from samdbcat.definition_schema_version_.access_paths a, samdbcat.
PAGE 225
Querying SQL/MX Metadata Displaying All DDL Locks on an Index Displaying All DDL Locks on an Index This example (when run on the node on which the metadata is located) displays the DDL locks on the index SAMDBCAT.SALES.XCUSTNAM: >> set schema samdbcat.definition_schema_version_; --- SQL operation complete. >> select substring (o1.object_name from 1 for 25) as lock_name, l.operation as util, l.status, substring (l.process_id from 1 for 25) from objects o, objects o1, ddl_locks l where o.
PAGE 226
Querying SQL/MX Metadata Displaying the Attributes of an Index Displaying the Attributes of an Index This example (when run on the node on which the metadata is located) displays some of the attributes for the index SAMDBCAT.SALES.XCUSTNAM: >> set schema samdbcat.definition_schema_version_; --- SQL operation complete. >> select substring(o1.
PAGE 227
Querying SQL/MX Metadata Displaying the State of Indexes for a Table Displaying the State of Indexes for a Table This example displays the state of the indexes for the table SAMDBCAT.SALES.CUSTOMER: >> set schema samdbcat.definition_schema_version_; --- SQL operation complete. >> select substring (o1.object_name, 1, 40) as index_name, case a.valid_data when 'Y' then 'populated' else 'unpopulated' end as index_status from samdbcat.definition_schema_version_.
PAGE 228
Querying SQL/MX Metadata Displaying Partition Information Displaying Partition Information Topics in this subsection:   Displaying All Partitions for a Table or Index on page 8-48 Displaying Attributes of a Partition on page 8-49 The PARTITIONS table, located in the schema DEFINITION_SCHEMA_VERSION_version-number of each catalog, describes all partitions in a catalog. For detailed information about all metadata tables, see Figure 8-2 on page 8-7 in this manual or the SQL/MX Reference Manual.
PAGE 229
Querying SQL/MX Metadata Displaying Attributes of a Partition the MXCI SHOWLABEL command with the DETAIL option to display a list of partitions for a table or an index. For examples of the SHOWDDL and SHOWLABEL commands, see the SQL/MX Reference Manual. Displaying Attributes of a Partition This example displays attributes for the partitions for the table SAMDBCAT.SALES.CUSTOMER. To display additional fields, select different fields from the PARTITIONS table: >> set schema samdbcat.
PAGE 230
Querying SQL/MX Metadata Displaying Constraint Information Displaying Constraint Information Topics in this subsection:    Determining Whether a Table Has Constraints on page 8-50 Displaying All Constraints on a Table on page 8-51 Displaying the Attributes of a Constraint on page 8-52 Related topics:    Displaying All Columns in a Primary Key or Unique Constraint on page 8-57 Displaying All Columns in a NOT NULL Constraint on page 8-58 Displaying All Columns in a Referential Integrity Constrain
PAGE 231
Querying SQL/MX Metadata Displaying All Constraints on a Table where cat_name = 'SAMDBCAT' ) ) for read uncommitted access; NUM_CONSTRAINTS -------------------2 Displaying All Constraints on a Table This example displays all constraints for a given table. >> set schema samdbcat.definition_schema_version_; --- SQL operation complete. >> select substring (o1.object_name, 1, 30) as constraint_name, case (t.
PAGE 232
Querying SQL/MX Metadata Displaying the Attributes of a Constraint Displaying the Attributes of a Constraint This example displays some of the attributes of the constraints in the table SAMDBCAT.SALES.CUSTOMER. To display additional fields, select the fields from the TBL_CONSTRAINTS table: >> set schema samdbcat.definition_schema_version_; --- SQL operation complete. >> select t.constraint_uid, t.table_uid, case t.
PAGE 233
Querying SQL/MX Metadata    Displaying Column Information Displaying All Columns in a Referential Integrity Constraint on page 8-58 Displaying the Attributes of a Column on page 8-60 Displaying All Tables Containing a Selected Column on page 8-61 Related topic:  Displaying All Privileges for a Column on page 8-65 The COLS table, located in the schema DEFINITION_SCHEMA_VERSION_version-number of each catalog, describes the columns in all tables and views in that catalog.
PAGE 234
Querying SQL/MX Metadata Displaying All Columns in a Table Displaying All Columns in a Table This example displays all columns in the table SAMDBCAT.PERSNL.EMPLOYEE: >> set schema samdbcat.definition_schema_version_; --- SQL operation complete. >> select substring(column_name, 1, 25) as column_name, column_number from cols c, objects o where c.object_uid = o.object_uid and o.object_name = 'EMPLOYEE' and o.object_type = 'BT' and o.
PAGE 235
Querying SQL/MX Metadata Displaying All Columns in a View Displaying All Columns in a View This example displays all columns in the view SAMDBCAT.SALES.CUSTLIST: >> set schema samdbcat.definition_schema_version_; --- SQL operation complete. >> select substring(column_name, 1, 25) as column_name, column_number from cols c, objects o where c.object_uid = o.object_uid and o.object_name = 'CUSTLIST' and o.object_type = 'VI' and o.schema_uid = (select schema_uid from nonstop_sqlmx_.
PAGE 236
Querying SQL/MX Metadata Displaying All Columns in an Index Displaying All Columns in an Index This example displays all columns in the index SAMDBCAT.SALES.XCUSTNAM in the schema SAMDBCAT.SALES: >> set schema samdbcat.definition_schema_version_; --- SQL operation complete. >> select substring(c.column_name, 1, 25) as column_name, c.column_number as base_table_column, ac.system_added_column from access_paths ap, access_path_cols ac, cols c, objects o where ap.access_path_uid = o.
PAGE 237
Querying SQL/MX Metadata Displaying All Columns in a Primary Key or Unique Constraint Displaying All Columns in a Primary Key or Unique Constraint This example displays all columns with primary key or unique constraints for the table SAMDBCAT.SALES.ODETAIL; two columns of this table make up the primary key: >> set schema samdbcat.definition_schema_version_; >> select substring(c.column_name, 1, 15) as column_name, c.column_number as col_num, case t.
PAGE 238
Querying SQL/MX Metadata Displaying All Columns in a NOT NULL Constraint Displaying All Columns in a NOT NULL Constraint This example displays all columns with NOT NULL constraints for the table SAMDBCAT.SALES.ODETAIL: >>set schema samdbcat.definition_schema_version_; >> select substring(c.column_name, 1, 15) as column_name, c.column_number as col_num, case o2.
PAGE 239
Querying SQL/MX Metadata Displaying All Columns in a Referential Integrity Constraint >> select substring(c.column_name, 1, 15) as column_name, c.column_number as col_num, r.update_rule, substring(o2.object_name , 1, 24) as constraint_name from cols c, objects o1, objects o2, key_col_usage k, ref_constraints r, tbl_constraints t where c.object_uid = t.table_uid and k.constraint_uid = t.constraint_uid and r.constraint_uid = t.constraint_uid and k.column_number = c.column_number and t.table_uid = o1.
PAGE 240
Querying SQL/MX Metadata Displaying the Attributes of a Column Displaying the Attributes of a Column This example displays some of the attributes of the columns in the table SAMDBCAT.PERSNL.EMPLOYEE. To display additional attributes, select different fields from the COLS table: >> set schema samdbcat.definition_schema_version_; --- SQL operation complete. >> select substring(c.column_name, 1, 15) as column_name, c.column_number as col_num, c.sql_data_type, case c.
PAGE 241
Querying SQL/MX Metadata Displaying All Tables Containing a Selected Column Displaying All Tables Containing a Selected Column This example displays all tables in schema SAMDBCAT.SALES that contain the column ORDERNUM and displays the SQL data type of the column: >> select substring(column_name, 1, 20) as column_name, sql_data_type, substring(object_name, 1, 20) as table_name from cols c, objects o where c.object_uid = o.object_uid and column_name = 'ORDERNUM' and o.object_type = 'BT' and o.
PAGE 242
Querying SQL/MX Metadata Displaying Information About Privileges Displaying Information About Privileges Topics in this subsection:    Displaying All Privileges for a Table on page 8-63 Displaying All Privileges for a View on page 8-64 Displaying All Privileges for a Column on page 8-65 The TBL_PRIVILEGES table, located in the schema DEFINITION_SCHEMA_VERSION_version-number of each catalog, describes the privileges for all tables and views in that catalog.
PAGE 243
Querying SQL/MX Metadata Displaying All Privileges for a Table Displaying All Privileges for a Table This example displays all privileges for the table SAMDBCAT.SALES.ORDERS: >>set schema samdbcat.definition_schema_version_; --- SQL operation complete.
PAGE 244
Querying SQL/MX Metadata -2 -2 System System Displaying All Privileges for a View 65535 65535 User User Select Update Y Y Displaying All Privileges for a View This example displays all privileges for the view SAMDBCAT.SALES.CUSTLIS: >>set schema samdbcat.definition_schema_version_; --- SQL operation complete.
PAGE 245
Querying SQL/MX Metadata ----------2 --------System Displaying All Privileges for a Column ---------- --------- ------- --------65535 Unknown Delete Y -2 System 65535 Unknown Insert Y -2 System 65535 Unknown Refer Y -2 System 65535 Unknown Select Y -2 System 65535 Unknown Update Y Displaying All Privileges for a Column This example displays all privileges for the column EMPNUM in the table SAMDBCAT.PERSNL.EMPLOYEE: >>set schema samdbcat.
PAGE 246
Querying SQL/MX Metadata Displaying Object Integrity and Consistency name>.system_schema.
PAGE 247
Querying SQL/MX Metadata Displaying the NonStop SQL/MX Release Identifier Displaying the NonStop SQL/MX Release Identifier To obtain the release number of the SQL/MX software running on a specified node, see the banner generated by mxci. For SQL/MX Release 3.1, mxci displays the release identifier 3.1. For example, the mxci banner displays the following information for SQL/MX Release 3.1: Hewlett-Packard NonStop(TM) SQL/MX Conversational Interface 3.
PAGE 248
Querying SQL/MX Metadata Displaying the Object Feature Version (OFV) and s.schema_uid = o.schema_uid and o.object_name = 'object-name'; This example is the same as the previous but limits the query to a specified name space: >> select o.object_feature_version from nonstop_sqlmx_node.system_schema.schemata s, nonstop_sqlmx_node.system_schema.catsys c, catalog-name.definition_schema_version_schema-version.objects o where c.cat_name = 'catalog-name' and c.cat_uid = s.cat_uid and s.
PAGE 249
9 Adding, Altering, and Dropping SQL/MX Database Objects After you create an SQL/MX database, you can assume the database is consistent and the application data is valid. Database management operations that add, alter, or drop database objects must maintain this same level of data consistency and validity, and must be planned carefully.
PAGE 250
Adding, Altering, and Dropping SQL/MX Database Objects Adding Objects to an SQL/MX Database Adding Objects to an SQL/MX Database Table 9-1 summarizes the objects you can add to an SQL/MX database and identifies the statement, command, or utility required for each add operation. Table 9-1.
PAGE 251
Adding, Altering, and Dropping SQL/MX Database Objects Authorization Requirements for Adding Database Objects Authorization Requirements for Adding Database Objects Table 9-2 describes the authorization requirement for each add operation. Table 9-2. Authorization Requirements for Adding Database Objects Operation Authorization Requirements Adding Catalogs Any user on a node can use the CREATE CATALOG statement to add a catalog to the node.
PAGE 252
Adding, Altering, and Dropping SQL/MX Database Objects Adding Catalogs Adding Catalogs To add a catalog, use the CREATE CATALOG statement. A catalog is owned by the user ID that created it, although catalog ownership does not imply authorization over schemas or objects in that catalog. Each of the schemas described in a catalog has an owner. A catalog can contain multiple schemas, each possibly owned by a different user. A catalog cannot contain other catalogs.
PAGE 253
Adding, Altering, and Dropping SQL/MX Database Objects Adding Columns to an SQL/MX Table Adding Columns to an SQL/MX Table To add a column to an SQL/MX table, use the ALTER TABLE statement with the ADD COLUMN clause. You can add columns to SQL/MX tables, but not to views or indexes. Each ALTER TABLE statement adds only one column. To add several columns, use the statement once for each column. Note. Where possible, avoid adding columns to a table.
PAGE 254
Adding, Altering, and Dropping SQL/MX Database Objects Adding Columns to an SQL/MX Table cause similarly check to fail and force automatic recompilation. If they will, you should SQL compile these modules after making the changes to avoid expensive automatic recompilations at run time. SQL applications that are running while you make these changes will still undergo automatic recompilation. For information about explicit and automatic recompilation, see the SQL/MX Programming Manual for C and COBOL.
PAGE 255
Adding, Altering, and Dropping SQL/MX Database Objects Adding Constraints Example of Adding a Column to a Table The following example adds a column to the CUSTOMER table: >> LOG myfile; >> ALTER TABLE CAT.SCH.CUSTOMER +> ADD COLUMN PRIOR_YEARS_SALES +> PIC S9(9)V99 COMP --- SQL operation complete.
PAGE 256
Adding, Altering, and Dropping SQL/MX Database Objects Adding Indexes to SQL/MX Tables USE OF, see Checking Module Dependencies with DISPLAY USE OF on page 11-19 and the SQL/MX Reference Manual. 6. Enter the ALTER TABLE ADD CONSTRAINT statement. 7. Revise the application source code as needed to reflect your changes to the database. Process and compile the updated source file. For more information, see the SQL/MX Programming Manual for C and COBOL.
PAGE 257
Adding, Altering, and Dropping SQL/MX Database Objects Adding Indexes to SQL/MX Tables Consider the following guidelines when adding an index to an existing table:   Index creation can be quite a long operation, depending on the size of the table and the load on the system. Therefore, the default locking strategy acquires a shared table lock on the underlying table. The shared table lock ensures that no users can modify rows during the creation of the index.
PAGE 258
Adding, Altering, and Dropping SQL/MX Database Objects Adding Indexes to SQL/MX Tables 6. Test the same queries against the tables by using DISPLAY STATISTICS to obtain the new statistical information. 7. Determine any improvement in performance. 8. If the query execution plans include using the new index and if you determine that the performance improvement is sufficiently advantageous over the increased system overhead of maintaining the index, add the index to the production database. 9.
PAGE 259
Adding, Altering, and Dropping SQL/MX Database Objects Adding Partitions to SQL/MX Tables and Indexes 9. Make a new TMF online dump containing the new index. For more information and examples of adding an index, see Precreating Indexes or Managing Constraint-Created Indexes on page 4-13 and Creating Indexes for SQL/MX Tables on page 7-33.
PAGE 260
Adding, Altering, and Dropping SQL/MX Database Objects Adding Partitions to SQL/MX Tables and Indexes of the objects involved, parallel processing operates best when a table or index is partitioned. Use the Measure product to obtain statistics concerning disk message levels, queuing, and other measurements on various volumes or file partitions to identify the levels of use. For more information about the benefits of new partitions, see Performance Benefits of Partitioning on page 7-16.
PAGE 261
Adding, Altering, and Dropping SQL/MX Database Objects Adding Schemas Example of Adding a Hash Partition to an SQL/MX Table For this example, suppose that the table ORDERSH has been created in this way: >> CREATE TABLE cat3.sch3.
PAGE 262
Adding, Altering, and Dropping SQL/MX Database Objects Adding SQL/MP Aliases Schema names that being with DEFINITION_SCHEMA_VERSION_ are reserved (in all catalogs) for system metadata. You cannot create schemas with these names in user catalogs. For more information, see the SQL/MX Reference Manual. Steps for Adding a Schema 1. Start an MXCI session. Enter a LOG command to initiate a log file for statements and commands entered in this session. Keep the log for your records. 2.
PAGE 263
Adding, Altering, and Dropping SQL/MX Database Objects Adding Triggers The name for the new table must be unique among names of tables, views, SQL/MP aliases, and procedures within its schema. You cannot specify a Guardian physical location as the name of the table, although you can specify the names of the Guardian files that will contain table data by using the LOCATION clause. For more information, see SQL/MX Subvolume Naming Guidelines on page 7-3.
PAGE 264
Adding, Altering, and Dropping SQL/MX Database Objects Adding Views User applications that change (INSERT, UPDATE, or DELETE) information in a table are automatically recompiled when a trigger with a matching event is added or dropped. User applications that use a SELECT on the subject table do not require recompilation. User applications require SQL recompilations only when triggers are added, altered or dropped. No source code changes or language compilations are required.
PAGE 265
Adding, Altering, and Dropping SQL/MX Database Objects Adding Views 2. Determine the names of any tables whose columns you want to include in the view., and a name for the view you want to add. 3. Query the system metadata to ensure the new view name is unique among existing view, table, SQL/MP alias and SPJ names in the schema. 4. Determine the column names of the view and the column names of the underlying table or tables of the view.
PAGE 266
Adding, Altering, and Dropping SQL/MX Database Objects Altering Objects in an SQL/MX Database Altering Objects in an SQL/MX Database Table 9-3 summarizes the database objects that you can alter and identifies the statements, commands, or utilities to use. Table 9-3.
PAGE 267
Adding, Altering, and Dropping SQL/MX Database Objects  Authorization Requirements for Altering Database Objects SQL/MX Guide to Stored Procedures in Java Authorization Requirements for Altering Database Objects Table 9-4 describes the authorization requirement for each alter operation. Table 9-4.
PAGE 268
Adding, Altering, and Dropping SQL/MX Database Objects Altering SQL/MX Indexes Altering SQL/MX Indexes To alter these physical file attributes of SQL/MX indexes, use the ALTER index statement:     ALLOCATE/DEALLOCATE AUDITCOMPRESS CLEARONPURGE MAXEXTENTS For more information, see the SQL/MX Reference Manual. Steps for Altering an Index 1. Start an MXCI session. Enter a LOG command to initiate a log file for statements and commands entered in this session. Keep the log for your records. 2.
PAGE 269
Adding, Altering, and Dropping SQL/MX Database Objects Altering Partitions for SQL/MX Tables and Indexes Altering Partitions for SQL/MX Tables and Indexes To alter a table or index partition, use the MODIFY utility. For MODIFY utility syntax, see the SQL/MX Reference Manual. Steps for Altering a Partition 1. Start an MXCI session. Enter a LOG command to initiate a log file for statements and commands entered in this session. Keep the log for your records. 2.
PAGE 270
Adding, Altering, and Dropping SQL/MX Database Objects Altering SPJs 3. Use the DISPLAY USE OF command to identify which user modules are associated with this object. See the similarity check criteria in the SQL/MX Programming Manual for C and COBOL to determine if your changes are likely to cause similarly check to fail and force automatic recompilation. If they will, you should SQL compile these modules after making the changes to avoid expensive automatic recompilations at run time.
PAGE 271
Adding, Altering, and Dropping SQL/MX Database Objects Altering SQL/MX Tables cause similarly check to fail and force automatic recompilation. If they will, you should SQL compile these modules after making the changes to avoid expensive automatic recompilations at run time. SQL applications that are running while you make these changes will still undergo automatic recompilation. For information about explicit and automatic recompilation, see the SQL/MX Programming Manual for C and COBOL.
PAGE 272
Adding, Altering, and Dropping SQL/MX Database Objects Altering SQL/MX Tables Altering a table's file attributes neither invalidates any programs nor affects dependencies of the table. To alter security attributes of SQL/MX tables, however, use the GRANT and REVOKE statements. (See Altering Table Privileges on page 9-24.) Steps for Altering Table File Attributes 1. Start an MXCI session. Enter a LOG command to initiate a log file for statements and commands entered in this session.
PAGE 273
Adding, Altering, and Dropping SQL/MX Database Objects Altering Triggers Steps for Altering a Table’s Privileges 1. Start an MXCI session. Enter a LOG command to initiate a log file for statements and commands entered in this session. Keep the log for your records. 2. Determine the name of the table you want to alter and what privileges you want to change. 3. Enter the GRANT or REVOKE statement. For more information and examples of altering table privileges, see the SQL/MX Reference Manual.
PAGE 274
Adding, Altering, and Dropping SQL/MX Database Objects Dropping Objects From an SQL/MX Database Dropping Objects From an SQL/MX Database Table 9-5 summarizes the objects that you can remove from the database and the statements, commands, and utilities to use. All these operations are discussed in this subsection except for dropping damaged SQL objects, which is described in Using GOAWAY to Delete Damaged Objects on page 12-37. Table 9-5.
PAGE 275
Adding, Altering, and Dropping SQL/MX Database Objects Authorization Requirements for Dropping Database Objects Table 9-6. Authorization Requirements for Dropping Database Objects Operation Authorization Requirements Dropping Catalogs Any user can drop any empty catalog visible on the local node. (All metadata tables for the catalog must be accessible at the time DROP CATALOG executes.) No user can drop a non empty catalog, however, even if the catalog contains only empty schemas.
PAGE 276
Adding, Altering, and Dropping SQL/MX Database Objects Dropping Catalogs Dropping Catalogs To drop a catalog, use the DROP CATALOG statement. DROP CATALOG automatically removes the SQL/MX user metadata tables, system metadata tables, and definition schema tables associated with the catalog and all entries in the system catalog associated with the catalog. All metadata tables for the catalog must be accessible at the time DROP CATALOG executes. Before you can drop a catalog, it must be empty.
PAGE 277
Adding, Altering, and Dropping SQL/MX Database Objects Dropping Constraints 4. Use the DISPLAY USE OF command to identify which user modules are associated with this object. See the similarity check criteria in the SQL/MX Programming Manual for C and COBOL to determine if your changes are likely to cause similarly check to fail and force automatic recompilation. If they will, you should SQL compile these modules after making the changes to avoid expensive automatic recompilations at run time.
PAGE 278
Adding, Altering, and Dropping SQL/MX Database Objects Dropping SQL/MX Indexes Steps for Dropping a Constraint 1. Start an MXCI session. Enter a LOG command to initiate a log file for statements and commands entered in this session. Keep the log for your records. 2. Determine the name of the table for which you want to drop the constraint. 3. Use the DISPLAY USE OF command to identify which user modules are associated with this object.
PAGE 279
Adding, Altering, and Dropping SQL/MX Database Objects Dropping Partitions for SQL/MX Tables and Indexes 3. Use the DISPLAY USE OF command to identify which user modules are associated with this object. See the similarity check criteria in the SQL/MX Programming Manual for C and COBOL to determine if your changes are likely to cause similarly check to fail and force automatic recompilation.
PAGE 280
Adding, Altering, and Dropping SQL/MX Database Objects  Dropping Schemas If you plan to use the TMF subsystem for recovering a table or index, see Recovering Database Objects on page 12-7 before proceeding. For more information, see the SQL/MX Reference Manual. Steps for Dropping a Partition See Using MODIFY to Manage Table and Index Partitions on page 10-10. Example of Dropping a Partition The following example drops an empty partition of a range-partitioned table: >>MODIFY TABLE CAT.SALES.
PAGE 281
Adding, Altering, and Dropping SQL/MX Database Objects Dropping SQL/MP Aliases The RESTRICT option, by default, will not allow a schema to be dropped if the schema is not empty of specifications for dependent objects. However, the CASCADE option, if specified, will drop both the schema and any such dependent objects.
PAGE 282
Adding, Altering, and Dropping SQL/MX Database Objects Dropping SPJs Steps for Dropping an SQL/MP Alias 1. Start an MXCI session. Enter a LOG command to initiate a log file for statements and commands entered in this session. Keep the log for your records. 2. Determine the name of the SQLMP alias that you wish to drop. 3. Use the DISPLAY USE OF command to identify which user modules are associated with this object.
PAGE 283
Adding, Altering, and Dropping SQL/MX Database Objects Dropping SQL/MX Tables Guidelines for Dropping an SQL/MX Table and its Data        If you plan to use the TMF subsystem to recover an SQL/MX table, see Recovering Tables on page 12-8. To have the authority to drop a table, you must have all the security and authority required to drop or invalidate all dependent objects, including access to all the catalogs describing all the dependent objects.
PAGE 284
Adding, Altering, and Dropping SQL/MX Database Objects Dropping Triggers 4. Enter the DROP TABLE statement. 5. Revise the application source code as needed to reflect your changes to the database. Process and compile the updated source file. For more information, see the SQL/MX Programming Manual for C and COBOL. For more information and examples of dropping tables, see the SQL/MX Reference Manual.
PAGE 285
Adding, Altering, and Dropping SQL/MX Database Objects Dropping Views To drop a trigger, you must own its schema or be the super ID. If you specify the CASCADE option for the DROP TRIGGER statement, you can also drop any objects used by the trigger (for example, tables, views or columns). For more information, see the SQL/MX Reference Manual. Steps for Dropping a Trigger 1. Start an MXCI session. Enter a LOG command to initiate a log file for statements and commands entered in this session.
PAGE 286
Adding, Altering, and Dropping SQL/MX Database Objects Dropping Views For more information, see the SQL/MX Reference Manual. Steps for Dropping a View 1. Start an MXCI session. Enter a LOG command to initiate a log file for statements and commands entered in this session. Keep the log for your records. 2. Determine the name of the view you wish to drop. 3. Use the DISPLAY USE OF command to identify which user modules are associated with this object.
PAGE 287
10 Reorganizing SQL/MX Tables and Maintaining Data Sometimes you might need to restructure the data in an SQL/MX table by reloading or reorganizing the table. NonStop SQL/MX offers several tools to aid the restructuring task:          Purge DDL files for dropped tables from the directory at /usr/tandem/sqlmx/ddl to provide more space for DROP operations. See Purging Dropped Tables From the DDL Directory on page 10-1. Choose a method for reorganizing the data.
PAGE 288
Reorganizing SQL/MX Tables and Maintaining Data Using FUP RELOAD to Reorganize Tables If this directory becomes too full, NonStop SQL/MX will not drop any more objects until more space is made available. To prevent this from happening, periodically check (once a week, for example) the DDL directory and purge all DDL files for tables you know will not have to be recovered. The DDL directory contains timestamp information for each DDL file that identifies the time and date when a table was dropped.
PAGE 289
Reorganizing SQL/MX Tables and Maintaining Data  Determining the Status of a Reorganization With FUP STATUS You can either specify an ANSI name or a Guardian file name when you perform a FUP RELOAD on an SQL/MX table or index. FUP RELOAD only supports the use of ANSI names for individual table or index partitions.
PAGE 290
Reorganizing SQL/MX Tables and Maintaining Data Suspending a Reorganization Operation This command requests the status of the FUP RELOAD operation started in the previous example for the table CUSTOMER: FUP - STATUS $DATA08.ZSDAWVHH.J8MGRZ00 The status is reported in this format: FUP - STATUS $DATA08.ZSDAWVHH.J8MGRZ00 $DATA08.ZSDAWVHH.J8MGRZ00 RELOAD IN PROGRESS RELOAD INITIATED: 21 Jan 2004, 13:02 If the operation completes, terminates abnormally, or is suspended, FUP displays the appropriate message.
PAGE 291
Reorganizing SQL/MX Tables and Maintaining Data DDL Lock Considerations for MODIFY, import, POPULATE INDEX, DUP, FASTCOPY, and If you want to keep the same RATE and SLACK values when you restart the reorganization process, enter the FUP RELOAD command without the RATE and SLACK parameters: FUP - RELOAD $DATA08.ZSDAWVHH.J8MGRZ00 After a FUP RELOAD operation has been suspended and you want to start this operation completely over again, enter the FUP RELOAD command with the NEW option: FUP - RELOAD $DATA08.
PAGE 292
Reorganizing SQL/MX Tables and Maintaining Data Consequences of a Failed Utility Operation These SQL/MX utilities use DDL locks:       MODIFY. See Using MODIFY to Manage Table and Index Partitions on page 10-10. import (for the fast loading option only). See Using import to Load SQL/MX Tables on page 10-22. POPULATE INDEX DUP. See Using DUP to Copy Tables Into Tables on page 10-40. FASTCOPY. See Using FASTCOPY to Copy Tables into Tables on page 10-42. PURGEDATA.
PAGE 293
Reorganizing SQL/MX Tables and Maintaining Data Using a Query to Obtain DDL Lock and Process ID Information locks and the MXCMP process IDs associated with the catalog SAMDBCAT and the schema SALES: set schema samdbcat.definition_schema_version_; select substring (o.object_name from 1 for 15) as lock_name, o.object_type as type, l.operation, l.status, substring (l.process_id from 1 for 25) as process_id from objects o, ddl_locks 1 where l.object_uid = o.object_uid and o.
PAGE 294
Reorganizing SQL/MX Tables and Maintaining Data Performing Recovery on Failed Utility Operations Performing Recovery on Failed Utility Operations If the utility request fails to complete successfully, the request must be recovered to a known good state (resumed or cancelled), which removes the DDL lock and resets all flags. Most utilities respond to a request failure by invoking their own internal recovery procedure.
PAGE 295
Reorganizing SQL/MX Tables and Maintaining Data Structure Changing Operations That Can Run With Active or Inactive DDL Locks Present Structure Changing Operations That Can Run With Active or Inactive DDL Locks Present Structure changing operations are sometimes allowed to run even with active or inactive DDL locks present.
PAGE 296
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Table and Index Partitions Using MODIFY to Manage Table and Index Partitions As the data in SQL/MX tables and indexes is updated and removed and new data is added, you need to manage table and index partitions. Use the MODIFY utility to perform these partition management operations:    Adding, splitting, merging, moving, and dropping partitions for range-partitioned tables and indexes.
PAGE 297
Reorganizing SQL/MX Tables and Maintaining Data MODIFY and TMF MODIFY and TMF Many partition management requests require movement of massive amounts of data. Because these operations can take longer than the default TMF time limit of two hours, operations involving data movement are performed in multiple transactions.
PAGE 298
Reorganizing SQL/MX Tables and Maintaining Data MODIFY and Table Reloading operations and utility operations—including another MODIFY command—on the affected table or index. ORSERV keeps the file open and does not terminate for five minutes after the reload operation completes.
PAGE 299
Reorganizing SQL/MX Tables and Maintaining Data Recovering a Failed MODIFY Request and Resetting Flags For information about the MXCI SHOWLABEL command, see the SQL/MX Reference Manual. Recovering a Failed MODIFY Request and Resetting Flags The UNRECLAIMEDSPACE (F) flag or the INCOMPLETE SQLDDL OPERATION (D) flag can be left set when a MODIFY operation fails to complete successfully.
PAGE 300
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Range-Partitioned Tables and Indexes schema_name = 'SALES' and cat_uid = (select cat_uid from nonstop_sqlmx_.system_schema.catsys where cat_name = 'SAMDBCAT' ) ) for read uncommitted access order by l.object_uid; LOCK_NAME ----------------------CUSTOMER_LOCK UTIL ----IM STATUS ----------4 PROCESS_ID ---------------\DMR15.
PAGE 301
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Range-Partitioned Tables and Indexes MODIFY supports online partition management for range-partitioned tables and indexes where the partitioning key is a prefix of the clustering key. MODIFY supports these partition management operations for range partitions:        Adding a new empty partition. You can add range partitions online.
PAGE 302
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Range-Partitioned Tables and Indexes another disk volume. This MODIFY command splits the $DATA03 partition, moving all locations from ‘Seattle’ and beyond to a new partition on $DATA04: MODIFY TABLE cat2.sch2.
PAGE 303
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Range-Partitioned Tables and Indexes this partition into the partition on $DATA02 and to drop the $DATA03 partition. Use this MODIFY TABLE command: MODIFY TABLE cat2.sch2.ordersr move where key = value ('New York') thru key = last key to previous partition; Note that the DROP PARTITON syntax is not used in this example because the partition $DATA03, which contains ‘New York,’ is not yet empty.
PAGE 304
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Hash-Partitioned Tables and Indexes MERGE operations. The only table operation that cannot be performed on an index is REUSE. The next examples illustrate these supported operations. Example of Adding a New Range Partition Business has exceeded expectations in the first half of 2004. The index partition on $DATA02 has become too large, and you want to move May and June orders to a new partition, $DATA04.
PAGE 305
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Hash-Partitioned Tables and Indexes You can perform only offline partition management operations for hash-partitioned tables and indexes. MODIFY supports these offline partition management operations for hash partitions:    Adding a new hash partition and rebalancing data (that is, redistributing existing data to all partitions, including the new partition). Dropping the last existing hash partition and rebalancing data.
PAGE 306
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Hash-Partitioned Tables and Indexes Example of Moving an Existing Hash Partition to a New Location You decide to use $DATA03 for another purpose and to move the hash partition on $DATA03 to a different volume. Use this MODIFY TABLE command to move the partition to a new location: MODIFY TABLE cat3.sch3.
PAGE 307
Reorganizing SQL/MX Tables and Maintaining Data Managing System-Clustered Tables and Indexes Orders suddenly decline, and you decide to free up some of the disk resources used by the index. Use this MODIFY command to drop the fourth partition and redistribute its data to the remaining partitions: MODIFY INDEX cat2.sch2.ordersr_via_ordernumber drop partition where last partition; By using the syntax last partition in the example, you avoid the need to specify the last partition by its location.
PAGE 308
Reorganizing SQL/MX Tables and Maintaining Data Using import to Load SQL/MX Tables Using import to Load SQL/MX Tables Use the import command to move data from an input file in ASCII or UCS2 format into an existing SQL/MX table. For example, you would use import to load an input file that contains the ASCII-formatted contents of another vendor’s SQL table into an SQL/MX table. DataLoader/MX can also send output to an import process.
PAGE 309
Reorganizing SQL/MX Tables and Maintaining Data Summary of import Options Summary of import Options Table 10-1 describes the most important import command options. Table 10-1. import Command Options (page 1 of 2) import Option Description -D Disables all triggers before the data is inserted, which prevents trigger actions from occurring. -F and -C Specifies the first row and the number of rows to import from the input file. The first row is designated 0 (zero).
PAGE 310
Reorganizing SQL/MX Tables and Maintaining Data Summary of import Options Table 10-1. import Command Options (page 2 of 2) import Option -U Description Specifies the name of the file that contains format specifications for the input file. The format file must be an OSS or Guardian text file. The Guardian file name must be specified in OSS format. The format file cannot exceed 60,000 bytes.
PAGE 311
Reorganizing SQL/MX Tables and Maintaining Data Summary of import Options For more information about these and other import command options, see the SQL/MX Reference Manual. Format File Considerations for import For more information about format file structures and field options for import, see the SQL/MX Reference Manual. Format File for a DELIM Input File If the input file type is DELIM and you want to use a format file, you must include the [COLUMN FORMAT] section. The other sections are optional.
PAGE 312
Reorganizing SQL/MX Tables and Maintaining Data Other import Features 2. The value of some floating-point numbers may not be exactly the same (though very close). This is due to an internal conversion to binary format and then back to floating point ASCII format for logging to the error log file. 3. Any string which includes the field delimiter, the row delimiter, or the field-qualifier character may appear differently.
PAGE 313
Reorganizing SQL/MX Tables and Maintaining Data Recommended Practices for Improving import Performance If the table is not empty or has dependent indexes, import continues with the normal load operation using TMF transactions. For more information about import command features and options, see the SQL/MX Reference Manual.
PAGE 314
Reorganizing SQL/MX Tables and Maintaining Data Recommended Practices for Improving import Performance 1. Make sure the data in the input file is presorted according to the clustering key order if a clustering key is present. Presorting the input file data prevents external sorts and extra block splits. When DP2 inserts unordered data into a partition, import performance is slowed. 2. Use the CONTROL QUERY DEFAULT statement to set the UPD_ORDERED attribute to OFF.
PAGE 315
Reorganizing SQL/MX Tables and Maintaining Data Recommended Practices for Improving import Performance If you are running NonStop SQL/MX on a single node, you have these options for using import with range-partitioned tables:   Put the input data into several files, each of which is destined for a different partition. Run an import instance for each partition. Put the input data into several files, each of which is destined for all the partitions that reside on a particular disk.
PAGE 316
Reorganizing SQL/MX Tables and Maintaining Data Using import to Load Partitions Managing Indexes to Improve import Performance A given table with one or more indexes takes much longer to load with import than does the same table without indexes. When you perform an import operation on an SQL/MX table with indexes, you should first drop all indexes, and then re-create them after the operation completes: 1. Perform a SHOWDDL command on the table and save the SHOWDDL output. 2. Drop all indexes on the table.
PAGE 317
Reorganizing SQL/MX Tables and Maintaining Data Using import to Load Partitions can also specify the start and end positions of the range partition based on the key column data in the input file. However, you cannot use these options to specify an actual key value at the command line.  For hash-partitioned tables, import loads all hash partitions in the destination table in a single operation.
PAGE 318
Reorganizing SQL/MX Tables and Maintaining Data  Support for restarting import Run multiple instances of the import command—one for each partition in the destination table—to load data into a partitioned table by using a separate input file for each partition. Each of the input files contains the data for each partition. For example, you might specify the three import commands for the partitioned EMPLOYEE table as: /usr/bin:import corpcat.persnl.employee -I empfile1 -T 500 /usr/bin:import corpcat.
PAGE 319
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using import to Load an SQL/MX Table Examples of Using import to Load an SQL/MX Table Example 1: Importing Data From a Delimited File This example is of an import of data from a delimited file. It shows the structure of the table to be loaded, the data to be loaded (the input data file), the format file describing the data, and the import command used to load it.
PAGE 320
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using import to Load an SQL/MX Table RowDelimiter=\n Qualifier=" import Load Command This import command imports data into the target table COMPANY from the delimited input file COINPUT using the format file FORMFILE1: import cat.sch.
PAGE 321
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using import to Load an SQL/MX Table col=fax,48,12 RecordLength=61 Note. All commas and double-quote characters in the input file are ignored because they are not covered by any of the col= entries in the [FIXED WIDTH FORMAT] section of the format file. In this example, the RecordLength value of 61 includes one newline character at the end of each input record.
PAGE 322
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using import to Load an SQL/MX Table col=col1 [FIXED WIDTH FORMAT] FileIsBinary=N RecordLength =5 col=col1,1,5 import Load Command This import command imports data into the target table TABLE_2 from the delimited input file COINPUT_FX3 using the format file FORMFILE3: import cat.sch.
PAGE 323
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using import to Load an SQL/MX Table A select statement on the table will show the following: select * from cat.sch.Xample; C1 C2 ----------- -------11 12345678 15 62345678 27 72345678 55 12345678 --- 4 row(s) selected. The X.perrs file will contain: A8,22345678 14,423456789 17,52345678,9 BZ,XYZ The X.perrmsg file will contain: *** ERROR[20081] Row number 2 and column number 1 could not be processed.
PAGE 324
Reorganizing SQL/MX Tables and Maintaining Data Using import to Append Data to Tables or Partitions Elapsed time = 0:0:0.088011 Elapsed time since skipping initial rows = 0:0:0.088011 Rows Imported Successfully = 4 Rows ignored due to parsing errors = 4 Rows ignored due to execution errors = 2 Parsing Error Log File Name: X.perrs Parsing Error Messages File Name: X.perrmsg Execution Error Log File Name: X.xerrs Execution Error Messages File Name: X.
PAGE 325
Reorganizing SQL/MX Tables and Maintaining Data Using import to Append Data to Tables or Partitions Appending Data to Multiple Partitions in Parallel Use import to append data to partitioned tables in parallel. This strategy can improve append performance if table partitions are distributed across disks, CPUs, and I/O channels. To append data to partitions in parallel: 1. Start an import process for each partition. One way is to start each import process in the CPU associated with the target partition.
PAGE 326
Reorganizing SQL/MX Tables and Maintaining Data Using DUP to Copy Tables Into Tables $VOL4. The target partitions reside on volumes $VOL33 through $VOL48. The record layout corresponds exactly to the target table layout. The -l option ensures that the import operation proceeds even if an input record contains data that cannot be parsed for loading into the target table. /user/bin> /user/bin> /user/bin> . . . /user/bin> import cat1.schema1.t1 -I /G/VOL33/TARGET/HIST01 -L 1000 import cat1.schema1.
PAGE 327
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using DUP to Copy Tables DUP operation fails before the data is successfully copied, specify RECOVER with the CANCEL option to roll back the DUP operation. You can access this status information by reading the DDL_LOCK metadata table. If you specify the wrong RECOVER operation, RECOVER reports this so that you can use the correct operation, possibly with RECOVER...CANCEL.
PAGE 328
Reorganizing SQL/MX Tables and Maintaining Data Using FASTCOPY to Copy Tables into Tables Using FASTCOPY to Copy Tables into Tables FASTCOPY is a syntax-based utility that can be executed from MXCI and from a program using dynamic SQL. It enables you to copy a source table and its indexes into an existing equivalent target table and indexes. A fastcopy operation includes the execution of one or more FASTCOPY commands. For more information about the FASTCOPY utility, see the SQL/MX Reference Manual.
PAGE 329
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using FASTCOPY to Copy Tables complete, you can re-enable the triggers using the ALTER TRIGGER ENABLE command. The FASTCOPY command checks for the RI constraints that are defined on the target table. RI constraint checking, like trigger actions, also has a negative performance impact. To remove the RI and other droppable constraints that would interfere with a FASTCOPY command, use the ALTER TABLE...DROP CONSTRAINT command.
PAGE 330
Reorganizing SQL/MX Tables and Maintaining Data DUP versus FASTCOPY the fastcopy operation. Because the source index SI2 has no equivalent target index, it does not participate in the fastcopy operation. FASTCOPY TABLE ST TO TT INDEXES EXPLICIT; The following command copies all the rows from source index SI1 to the target index TI1. FASTCOPY INDEX SI1 to TI1; The base table TT can be accessed only after completing the fastcopy operation (copying of indexes SI1 to TI1).
PAGE 331
Reorganizing SQL/MX Tables and Maintaining Data Using PURGEDATA to Delete Data From Tables Using PURGEDATA to Delete Data From Tables PURGEDATA is a syntax-based utility you can execute from MXCI to delete data from a table and its related index or from the specified partitions of a table that has no index. For more information about the PURGEDATA utility, see the SQL/MX Reference Manual.
PAGE 332
Reorganizing SQL/MX Tables and Maintaining Data   Examples of Using PURGEDATA to Delete Table Data Another table references the named table through a trigger or referential integrity constraint. You attempt to perform PURGEDATA within a user-defined transaction. There is no notion of a transaction existing for a table. Examples of Using PURGEDATA to Delete Table Data This example purges the data in the specified table. If the table has indexes, the indexes are also purged. PURGEDATA mycat.myschema.
PAGE 333
11 Managing Database Applications Managing an SQL/MX database includes supporting the operating requirements and access requirements of application programs and maintaining valid application programs. Providing this support and maintenance can include both performance-related and operation tasks.
PAGE 334
Managing Database Applications Recovery for Read-Only Queries Any active transaction that does transaction work on a data volume incurs a unilateral abort when the CPU for the primary DP2 process fails. This is fundamental to high performance with audited tables and is true for Enscribe, NonStop SQL/MP, and NonStop SQL/MX. The transaction abort is initiated by the backup DP2 process during an unplanned takeover. TMF might also initiate an abort for some transactions.
PAGE 335
Managing Database Applications SQL/MX and SQL/MP Differences in Recovery Action for Read-Only Queries the WHERE clause of the SELECT statement. Using this technique, the program can reopen the cursor and continue the query from the point where the error was encountered.
PAGE 336
Managing Database Applications Reasons Not to Move a Program With Compiled Modules However, there are few circumstances under which you can compile a program’s modules on a development system and then move the program and modules to a production system without recompiling the modules there. See Reasons Not to Move a Program With Compiled Modules on page 11-4.
PAGE 337
Managing Database Applications  Moving a Program Without Compiled Modules If you fail to properly set up all DEFINEs for SQL/MP tables and the development and production systems are on the same Expand network, the wrong tables might be accessed. Moving a Program Without Compiled Modules When you move a program from a development system to a production system, you should always compile module definitions (either embedded module definitions or module definition files) on the production system.
PAGE 338
Managing Database Applications   Moving a Program Without Compiled Modules For hard-coded Guardian names of SQL/MP objects, make sure volume, subvolume, and file names on the development system are the same as those on the production system. Omit the node from the object names in the program. The object names will be qualified with the node of the system on which the statements are compiled.
PAGE 339
Managing Database Applications Moving a Program Without Compiled Modules Steps for Moving to a Production System These instructions explain how to move a program with its uncompiled module definitions (either embedded module definitions or module definition files) from a development system to a production system. After moving the program to the production system, you must SQL compile the module definitions on the production system.
PAGE 340
Managing Database Applications Distributing Programs Across Nodes 3. If the application refers to SQL/MP tables or views by using SQL/MP aliases, create the SQL/MP aliases in the production environment. See Setting SQL/MP Aliases on page 11-11. 4. Generate a new module or modules for the program:  For an embedded SQL program in C, C++, or COBOL, run the mxCompileUserModule utility on the production system to SQL compile an embedded module definition: mxCompileUserModule myprog.
PAGE 341
Managing Database Applications Running Applications on a Remote Node Running Applications on a Remote Node In a distributed database environment:    You can always run a local Guardian or OSS application from the local node by running the local program file. You can run a local Guardian or OSS application on a remote node by copying the application to the remote node and running the remote program file there.
PAGE 342
Managing Database Applications Maintaining Local Autonomy for Programmatic Queries Maintaining Local Autonomy for Programmatic Queries The ability of local programs to successfully query objects on remote nodes in a distributed SQL/MX database depends on the operations they perform and the availability of remote nodes that contain the queried object data and its metadata.
PAGE 343
Managing Database Applications Setting DEFINEs Setting DEFINEs You can refer to an SQL/MP table or view with a class MAP DEFINE that resolves to a Guardian physical name. When NonStop SQL/MX compiles such statements, it replaces the DEFINE name in the statement with the Guardian name currently associated with the DEFINE.
PAGE 344
Managing Database Applications Assigning Permissions for Running Database Applications In a distributed environment, when you create an SQL/MP alias name for an SQL/MP object on a remote node, use a fully qualified SQL/MP object name. The SQL/MP aliases must be in SQL/MX user catalogs that are visible on the nodes where the programs exist.
PAGE 345
Managing Database Applications Managing Permissions for Files in Guardian Space the group ownership of a file, use the chgrp command. To change group ownership, you must be a member of the group from which you are changing the file. For more information about these and other OSS commands, see the Open System Services User’s Guide.
PAGE 346
Managing Database Applications   Recompiling a Module By default, automatic recompilation is enabled for all database applications. To disable automatic recompilation, set the AUTOMATIC_RECOMPILATION default attribute to OFF. By default, the SQL/MX executor does not return a warning message to the database application when a DML statement is automatically recompiled.
PAGE 347
Managing Database Applications Managing Modules For details on how to analyze query execution plans and determine an optimal plan, see the SQL/MX Query Guide.
PAGE 348
Managing Database Applications Securing User Modules For more information about generating locally placed modules or globally placed modules in a C, C++, or COBOL programming environment, including full command syntax examples, see the SQL/MX Programming Manual for C and COBOL. Securing User Modules Because the SQL/MX executor runs in privileged mode and runs query plans in the compiled user modules, it is important to properly control access to globally placed modules and locally placed modules.
PAGE 349
Managing Database Applications  Securing User Modules Performs this OSS command: chmod a+rwxt /usr/tandem/sqlmx/USERMODULES After InstallSqlmx runs, you might want to perform some or all of these tasks: 1. To make sure that the sticky bit of the USERMODULES directory has been turned on, enter: ls -ld /usr/tandem/sqlmx/USERMODULES The output should look like this: drwxrwxrwt 1 SUPER.SUPER SUPER 4096 Apr 1 06:26 /user/tandem/sqlmx/USERMODULES/ 2. Only the SUPER.
PAGE 350
Managing Database Applications Securing User Modules Proper security of the local directory requires that:    The owner of the local directory be a trusted user. The sticky bit of the local directory be set. The compiled locally placed modules be writable only by the owner. To secure the locally placed module directory: 1. Create the local directory by issuing the OSS command: mkdir -p /usr/local-directory 2.
PAGE 351
Managing Database Applications Checking Module Dependencies with DISPLAY USE OF 8. Make sure that all future modules that you create are writable only by you or the owner of the local directory owner by entering: umask 022 This command makes module files (and OSS files in general) that the user creates readable by all but writable only by the module owner and creator. 9.
PAGE 352
Managing Database Applications Checking Module Dependencies with DISPLAY USE OF Displaying All Modules and Dependent Objects When you omit the module clause and the object clause, DISPLAY USE OF returns information about all modules and dependent objects. >>DISPLAY USE OF; Module: Object: Object: Object: Object: Object: CAT.SCH.CONSTRAINTM CAT.SCH.T24 CAT.SCH.T25 CAT.SCH.T26 CAT.SCH.T27 CAT.SCH.T28 Module: Object: Object: Object: Object: CAT.SCH.CURSOMEM \NODE1.DATA10.ORDERS.T01 CAT.SCH.T02 CAT.SCH.
PAGE 353
Managing Database Applications Module: Object: Object: Object: Object: Object: Checking Module Dependencies with DISPLAY USE OF CAT.SCH.CONSTRAINTM CAT.SCH.T24 CAT.SCH.T25 CAT.SCH.T26 CAT.SCH.T27 CAT.SCH.T28 Display source SQL file name for a given module >>DISPLAY USE OF SOURCE 'CAT.ALL.MODULE1'; Module: CAT.ALL.MODULE1 Source Name: /E/NODE11/usr/user2/constraint.
PAGE 354
Managing Database Applications Checking Module Dependencies with DISPLAY USE OF Display source SQL file for a given module in a given OSS directory >>DISPLAY USE OF MODULE_DIR '/usr/user1/all/modulestorage' SOURCE 'CAT.ALL.MODULE1'; Module: CAT.ALL.MODULE1 Source Name: /E/NODE11/usr/user2/constraint1.sql Display all modules and their corresponding source SQL files in a given OSS directory >>DISPLAY USE OF MODULE_DIR '/usr/user1/all/modulestorage' SOURCE; Module: CAT.ALL.
PAGE 355
Managing Database Applications Grouping Applications and Modules to Run Multiple DISPLAY USE OF Operations pattern you specified in the MODULE_DIR clause. If modules that match the pattern are not found in the local directory, an error is returned and searching does not extend beyond the local directory. For more information about the syntax of the MODULE_DIR clause of DISPLAY USE OF, see the SQL/MX Reference Manual.
PAGE 356
Managing Database Applications Grouping Applications and Modules to Run Multiple DISPLAY USE OF Operations ----- End of File Processing Checks ----0 errors, 0 warnings in SQL C file "/user/smith/tmp/tmp/DUO/t01.sql" 2. Repeat Step 1 for t02.sql and t03.sql: % mxsqlc t02.sql -g moduleGroup=INVENTORYAPP % mxsqlc t03.sql -g moduleGroup=INVENTORYAPP 3. Process the applications to generate annotated application executables, referring to the SQL/MX Programming Manual for C and COBOL for details.
PAGE 357
Managing Database Applications Grouping Applications and Modules to Run Multiple DISPLAY USE OF Operations 7. Check the SQL compiled module files using SHIPMENTAPP: % ls /usr/tandem/sqlmx/USERMODULES/*SHIPMENTAPP^*^^ /usr/tandem/sqlmx/USERMODULES/T01CAT.T01SCH.SHIPMENTAPP^T11MO D^^ /usr/tandem/sqlmx/USERMODULES/T01CAT.T01SCH.SHIPMENTAPP^T12MO D^^ 8. Use DISPLAY USE OF to display the modules and SQL/MX objects used by the inventory applications: >>display use of module '*.*.
PAGE 358
Managing Database Applications Removing Modules 10. Perform Step 9 for the remainder of the applications: % % % % /user/smith/tmp/tmp/DUO: /user/smith/tmp/tmp/DUO: /user/smith/tmp/tmp/DUO: /user/smith/tmp/tmp/DUO: mxCompileUserModule mxCompileUserModule mxCompileUserModule mxCompileUserModule -g -g -g -g moduleLocal moduleLocal moduleLocal moduleLocal t02.exe t03.exe t11.exe t12.exe 11. Verify how the local modules are generated: /user/smith/tmp/tmp/DUO: ls -l *.*.
PAGE 359
Managing Database Applications Converting Globally Placed Modules to Locally Placed Modules Before you remove a module file: 1. Verify that no other applications use modules that you will remove. 2. Verify that the modules (or the application source files) have been backed up. This gives you the option of restoring or rebuilding modules at a later time. To remove a module from its directory, use the OSS rm command. For example, to delete the globally placed module file cat.sch.
PAGE 360
Managing Database Applications Converting Globally Placed Modules to Locally Placed Modules You can convert applications that use globally placed modules to those that use locally placed modules in these environments:   Converting Applications in a Development Environment on page 11-28 Converting Applications in a Production Environment on page 11-28 Converting Applications in a Development Environment To build the application in a development environment as if it were a new version of the applicatio
PAGE 361
Managing Database Applications Managing Module Files and Their Applications During Fallback From SQL/MX Release 3.1 The -g moduleLocal option of mxCompileUserModule generates a locally placed module in the temporary directory. Using the temporary directory prevents disruptions to the application and prevents overwriting of any existing modules. c. Note the names of the module files that are generated in the temporary directory. d. Delete the contents of the temporary directory.
PAGE 362
Managing Database Applications Backing Up and Restoring Programs locations (such as another disk, magnetic tape, or another system). As a rule, you should back up programs before you make major changes to the database. For information about the strategies and procedures for backing up and restoring database files, see Using Backup and Restore 2 for SQL/MX Database Recovery on page 5-27. HP NonStop SQL/MX Release 3.
PAGE 363
12 Performing Recovery Operations The success of recovery operations depends on the effectiveness and consistency of the plan you develop for handling recovery situations. (See Planning Database Recovery on page 5-6.) Before beginning a recovery operation, you should thoroughly evaluate the tools available (RDF, TMF online dumps, backup tapes, and so forth) and appropriate for the type of failure.
PAGE 364
Performing Recovery Operations  Recovering Disk Volumes Use the file recovery method with a specified TIME option to recover a database to a given consistent time, as described in Recovering Files With the TIME Option on page 12-3. Recovering Disk Volumes TMF volume recovery is invoked automatically by the START TMF command and is invoked as needed thereafter when a volume becomes accessible.
PAGE 365
Performing Recovery Operations Recovering Files With the TIME Option online dump following a create operation. If you do not do so, you cannot recover the file because TMF looks for a starting point on the latest online dump.   A REDONEEDED or UNDONEEDED flag in the FILEINFO display for a file, after a volume has been enabled for TMF processing, indicates that you must use file recovery to recover the file.
PAGE 366
Performing Recovery Operations Recovering Files to New Locations Recovering Files to New Locations Caution. If you are using SQL/MP aliases, you might need to update your alias definitions after you recover an SQL/MP file to a new location. For more information, see the SQL/MX Reference Manual. Note. NonStop SQL/MX does not allow you to recover SQL/MX files to new locations by specifying different node names, volumes, subvolumes, or file IDs (for example, to recover from a hardware failure).
PAGE 367
Performing Recovery Operations Recovering Files to New Locations are located on $DATA10 for the source and $DATA12 for the target, you can use the RECOVER FILES command to recover these objects: RECOVER FILES $DATA10.ZSDT5356.*, NOT $DATA10.ZSDT5356.*1, & MAP NAMES ($DATA10.ZSDT5356.* TO DATA12.ZSDT3122.*) SQL/MX objects being recovered to a new location must be created before the recovery process is run, and the target objects must exactly match the source objects.
PAGE 368
Performing Recovery Operations Recovering Metadata Caution. Use the TOFIRSTPURGE, TIME, or TOMATPOSITION option in the RECOVER FILES command to avoid replaying the purge operation on a target object.
PAGE 369
Performing Recovery Operations Recovering Database Objects If the TMF and RESTORE recovery methods fail or are not available, you might be able to correct the inconsistencies by using a licensed MXCI process to change metadata information. Inconsistencies can arise from the incorrect use of SCF commands or the incorrect use of licensed programs. Recovering Database Objects Note.
PAGE 370
Performing Recovery Operations Recovering SPJs Some inconsistencies reported as a result of this verification might be due to user errors in the file recovery operation. For example, not all files to be recovered were properly specified. However, even if the recovery was done correctly, VERIFY might report inconsistencies between the labels and the metadata. In this case, you must run the mxtool FIXUP command. For information about the VERIFY and FIXUP commands, see the SQL/MX Reference Manual.
PAGE 371
Performing Recovery Operations Recovering Tables recovery script for that table would be needed.) You can change the minimum age at which the file is deleted by replacing the 7 in the example script with a value that is appropriate for your environment. find /usr/tandem/sqlmx/ddl -mtime +7 -print | grep "/ddl/" | sed "s/.
PAGE 372
Performing Recovery Operations Recovering Tables earlier in this discussion. And, as cautioned previously, you should always leave the SAVE_DROPPED_TABLE_DDL attribute set ON. Caution. Unless performed with great care and precision, SQL/MX table recovery involves risk of database corruption and loss of data integrity.
PAGE 373
Performing Recovery Operations Recovering Tables MXGNAMES utility. This utility can also be used to build lists of these Guardian files for use with TMFCOM commands.      For the recovered objects, use the mxtool VERIFY command to verify that the redefinition timestamps, catalog UIDs, schema UIDs, and object UIDs in the file labels or resource forks match those in the SQL/MX metadata.
PAGE 374
Performing Recovery Operations Recovering Tables When the table is dropped, NonStop SQL/MX produces a file containing the DDL for all the statements in this example.
PAGE 375
Performing Recovery Operations Recovering Tables 5. Use the TMFCOM ALTER DUMPS command to reset the INVALID and RELEASED attributes of the online dumps for the dropped table to OFF. 6. Execute this RECOVER FILES command: RECOVER FILES ($DATA4.ZSDADHFW.AL0P3N00, & $DATA4.ZSDADHFW.BGSB3N00, $DATA4.ZSDADHFW.AL0P3N01,& $DATA4.ZSDADHFW.
PAGE 376
Performing Recovery Operations Recovering Tables (CAT.SCH.T046_TRIG_T2.A IS NOT NULL AND CAT.SCH.T046_TRIG_T2.B IS NOT NULL) NOT DROPPABLE ) LOCATION \SQUAW.$DATA08.ZSDBNHZH.D6VSVM00 NAME SQUAW_DATA08_ZSDBNHZH_D6VSVM00 ; CREATE TRIGGER CAT.SCH.T046_ATRIG111_2 AFTER UPDATE ON CAT.SCH.T046_TRIG_T2 REFERENCING OLD AS OLDR, NEW AS NEWR FOR EACH ROW INSERT INTO CAT.SCH.T046_TRIG_T3 VALUES (NEWR.A + 20, OLDR.B || CHAR(ASCII(NEWR.B))); ALTER TRIGGER DISABLE CAT.SCH.
PAGE 377
Performing Recovery Operations Recovering Tables --- SQL operation complete. Suppose you later discover that someone subsequently issued an MXCI DROP TABLE command that purged the table: >>DROP TABLE HRDATA.PERSNL.EMPLOYEE; ---SQL operation complete. You determine that this table was dropped inadvertently. Now, you want to recover it: 1. Verify that the EMPLOYEE table has indeed been removed from the database, by entering the MXCI SHOWDDL command: >> SHOWDDL HRDATA.PERSNL.
PAGE 378
Performing Recovery Operations Recovering Tables b. Use CTOEDIT to transfer this file to the Guardian space: $DATA01 SUBVOL 15>ctoedit ddlfile,employee c. Use MXGNAMES with the -showddl option, specifying the target Guardian file from Step b: $DATA01 SUBVOL 16>$system.zmxtools.mxgnames showddl=employee -tmf (--Table HRDATA.PERSNL.EMPLOYEE--& $DATA10.ZSDT5356.NG59FJ*,& --Index XEMPNAME ON HRDATA.PERSNL.EMPLOYEE--& $DATA10.ZSDT5356.JN9VFJ*,& --Index XEMPDEPT ON HRDATA.PERSNL.EMPLOYEE--& $DATA10.ZSDT5356.
PAGE 379
Performing Recovery Operations Recovering Tables HRDATA.PERSNL.EMPLOYEE-20031114-160629.ddl. Issue an MXCI OBEY command to execute the commands in this file from a licensed MXCI process: >>OBEY /USR/TANDEM/SQLMX/DDL/HRDATA.PERSNL.EMPLOYEE +>-20031114-160629.DDL; As MXCI executes the commands, they appear on your screen, along with certain related messages: >>CREATE TABLE HRDATA.PERSNL.
PAGE 380
Performing Recovery Operations Recovering Tables 5. Use the DELETE and INSERT statements saved with the DDL to re-create access privileges for the table and its indexes. Note. GRANT and REVOKE statements are not saved as part of DDL text. However, the DDL text includes DELETE statements to remove the default security information from the metadata tables TBL_PRIVILEGES and COL_PRIVILEGES and INSERT statements to record the correct security information.
PAGE 381
Performing Recovery Operations >>> >>> Recovering Tables $DATA10.ZDST5356.NPFH8J01), INVALID OFF,& RELEASED OFF, SERIAL 70 Note. If, for any reason, the dumps were completely removed from the TMF catalog, you would need to add them again, using the TMFCOM ADD DUMPS command. In this command, you would also set the INVALID and RELEASED attributes to OFF. 8. Recover the table and its indexes. Caution. Objects to be recovered are spread across different disk volumes and subvolumes.
PAGE 382
Performing Recovery Operations Recovering Tables timestamps in the SQL catalog. You must then manually perform the synchronization for these objects, as explained in Step 10. Alternatively, you can use the mxtool VERIFY utility to list the inconsistencies between the object descriptions in the file labels and in the SQL/MX metadata: /USR/TANDEM/SQLMX/DDL:MXTOOL VERIFY HRDATA.PERSNL.EMPLOYEE NonStop SQL/MX MXTOOL Utility 2.0 (c) Copyright 2004 Hewlett-Packard Development Company, LP. All Rights Reserved.
PAGE 383
Performing Recovery Operations Recovering Tables Reserved. Operation successfully completed. For further information about the mxtool utility, see the SQL/MX Reference Manual. 11. Use the mxtool FIXUP command to update the UIDs in the resource forks to reflect the values saved in the OBJECTS table in the SQL/MX metadata for all objects identified as inconsistent in Step 9: /USR/TANDEM/SQLMX/DDL:MXTOOL FIXUP TABLE HRDATA.PERSNL.EMPLOYEE -RU -D NonStop SQL/MX MXTOOL Utility 2.
PAGE 384
Performing Recovery Operations Recovering Views and Indexes that was the case, you should re-create the dropped views now, using the DDL you saved when the view was created. Caution. To ensure that you can recover the table and its indexes again in the future, take new online dumps of the recovered files now. Also dump any newly created views. 14. Update the statistics for the recovered table.
PAGE 385
Performing Recovery Operations Restoring Catalogs Restoring Catalogs When you restore a catalog, all its dependent objects (for example, schemas, tables, and indexes) are also restored.
PAGE 386
Performing Recovery Operations Restoring Indices and Index Partitions You can restore one or more table partitions, either implicitly, by restoring associated tables, or explicitly, by using the PARTITION restore object: BR> RESTORE $tape-drive, MX (TPART cat1.sch1.table1 PARTITION (part1)), PARTONLY ON; BR> RESTORE $tape-drive, MX (TPART cat1.sch1.table1 PARTITION (part1, part2)) BR+>, PARTONLY ON; Caution. Use care when using the PARTONLY object in Backup and Restore 2 operations for partitioned tables.
PAGE 387
Performing Recovery Operations     Repairing Damaged SQL/MX Metadata and Objects Referential integrity constraints SPJs Triggers Views For information about MXCI OBEY command files, see the SQL/MX Reference Manual. To use the EDIT files as MXCI OBEY command files, you must first modify them slightly. You must qualify the locations of objects specified in each CREATE command to indicate the catalog and table associated with the object.
PAGE 388
Performing Recovery Operations    Strategies for Repairing Damaged Metadata and Objects Section 5, Planning Database Security and Recovery Section 8, Querying SQL/MX Metadata The SQL/MX Reference Manual FIXUP Use the mxtool FIXUP command to perform limited repair operations that enable you to fix problems, such as inconsistencies between metadata and labels, by adjusting the label or resource fork. FIXUP does not repair all problems with inconsistencies, however.
PAGE 389
Performing Recovery Operations Identifying Problem Data and Objects VERIFY Use the mxtool VERIFY utility to check consistency within the database between metadata, resource forks, and file labels. For information about using VERIFY, see Using VERIFY to Check Data Consistency on page 12-30. Correcting Problems Manually You can correct some problems in metadata directly by manually executing inserts, updates, selects, and deletes.
PAGE 390
Performing Recovery Operations Identifying Problem Data and Objects Table 12-1. Label Data Associated With SQL/MX Objects (page 1 of 2) Label Field Contents Data fork name This name matches the external file label name and is of the form ZSDXXXXXX.YYYYYY00. Resource fork name This name is similar to the data fork name, except it ends in “01” (for example, ZSDXXXXXX.YYYYYY01). File code The file code that is used by NonStop SQL/MX. Unlike with SQL/MP, you cannot choose your own file codes.
PAGE 391
Performing Recovery Operations Identifying Problem Data and Objects Table 12-1. Label Data Associated With SQL/MX Objects (page 2 of 2) Label Field Contents Maximum packed record length The maximum byte-length of packed records. Flags Flags that indicate whether the object is an SQL/MX format object, and whether the object is a resource fork, audited, in the middle of a DDL operation, left in the crash-open state, broken, offline, or SQLsecured.
PAGE 392
Performing Recovery Operations Identifying Problem Data and Objects Table 12-2. Run-time metadata Stored in the Resource Fork Run-time metadata Contents Constraint information Contains entries for droppable check constraints. This information is needed for similarity-checking to determine whether plans need to be recompiled because of a new or deleted check constraint. Trigger information Indicates whether or not a trigger is enabled.
PAGE 393
Performing Recovery Operations  Identifying Problem Data and Objects  ANSI name. The ANSI name stored in the resource fork must match that stored in metadata.  ANSI namespace. The ANSI namespace stored in the resource fork must match that stored in metadata.  Partition map. The number of partitions defined in the PARTITIONS table must match the number of partitions in the partition map array in the resource fork.  Version information.
PAGE 394
Performing Recovery Operations     Using FIXUP to Correct Problem Data and Objects You must be logged on using the super ID, be owner of the schema where the object being verified resides, or have SELECT privilege on the object being verified. VERIFY obtains read-only locks on metadata while verifying an object. Other operations that read metadata can run concurrently.
PAGE 395
Performing Recovery Operations Using FIXUP to Correct Problem Data and Objects While performing database operations, information between metadata, DP2 labels, and resource forks can become inconsistent. For example, a failed TMF transaction might leave a file in the broken state, or a software bug might not set the redefinition timestamp correctly. When such an inconsistency is detected, it must be fixed to guarantee proper execution of NonStop SQL/MX.
PAGE 396
Performing Recovery Operations     Using FIXUP to Correct Problem Data and Objects Audit bit Broken bit Corrupt bit Redefinition timestamp Changing the Audit Bit SQL/MX tables are always created as audited. In some cases, you might need to use FIXUP to turn off the audit bit to run a low-level DP2 utility operation, such as TANDUMP.
PAGE 397
Performing Recovery Operations Examples of Using FIXUP For example, this command creates table A with three partitions: Create table tableA (col1 int not null primary key, col2 char (20)) Partition (add first key (1000) location $vol1, add first key (2000) location $vol2); Table A also has a dependent index that contains two partitions: Create index tableA_index1 on tableA (col2) Partition (add first key ("m" location $vol1)); The three partitions on tableA must have the same redefinition timestamp.
PAGE 398
Performing Recovery Operations    Examples of Using FIXUP \LOCAL.$DATA02.ZSD1123U.SUEIFO00 \LOCAL.$DATA01.ZSD1123U.SUEIGO00 \REMOTE.$DATA01.ZSD1123U.SUEIHO00 CREATE TABLE cat.sch.table1 (Col1 int not null primary key, Col2 int not null, Col3 timestamp not null no default, Col4 char (20)) Partition (Add first key (1000) location \local.$data01 name part2, Add first key (2000) location \remote.$data01 name part3) Location \local.$data02 name part1; 2.
PAGE 399
Performing Recovery Operations Using GOAWAY to Delete Damaged Objects Example 2: Correcting Mismatched Timestamps Several of the timestamps on table1 do not match the value on the label. To fix the problem, change the redefinition timestamps: mxtool FIXUP TABLE cat.sch.table1 -rt -d Hewlett-Packard NonStop(TM) SQL/MX MXTOOL Utility 3.0 (c) Copyright 2003, 2004-2010 Hewlett-Packard Development Company, LP.
PAGE 400
Performing Recovery Operations ALTER DISK, ALTNAME and ALTER DISK, VOLNAME store production SQL/MX database files. Use the drive for a test database or for temporary space for sort files. You must ensure that you can make the volume inactive and revive the drive back to its original mirrored configuration with no effect on the original database. Use the ABORT DISK command on a volume to put a particular volume out of operation.
PAGE 401
Performing Recovery Operations ALTER DISK, LABEL 2. Restore the files to the disk volume, mapping the old volume names to the new volume names in the RESTORE command. 3. Map the objects to the new catalogs. 4. Verify the database by using the mxtool VERIFY utility. See the SQL/MX Reference Manual for command syntax and functionality. 5. SQL compile all programs with new DEFINEs to revalidate the programs. For more information, see the SQL/MX Programming Manual for C and COBOL. ALTER DISK, LABEL Caution.
PAGE 402
Performing Recovery Operations START DISK and STOP DISK labels the disk. To recover an initialized disk volume, follow the steps in ALTER DISK, LABEL on page 12-39. START DISK and STOP DISK Use the STOP DISK command on a mirrored volume pair to make one half of the pair inactive. The active disk drive of the mirrored pair continues to maintain the current database, without the protection of mirroring.
PAGE 403
13 Managing an SQL/MX Distributed Database SQL/MX databases can be distributed over disk volumes on a single system (node) or in a network of nodes. Likewise, application programs that access NonStop SQL/MX can be distributed across CPUs in a single node or in a network. When managing a database distributed across volumes or nodes, you can usually use the same SQL statements you would use with a nondistributed database.
PAGE 404
Managing an SQL/MX Distributed Database   Managing a Network-Distributed SQL/MX Database Using the total available processing power of the system while balancing the workload Enabling very large database objects to physically spread across multiple disk volumes while accessed as a single object The impact of unavailable disks on an SQL/MX query of a table partitioned across multiple local disks depends on whether the unavailable disks are needed for the query.
PAGE 405
Managing an SQL/MX Distributed Database  SQL/MX Distributed Database Features To increase local computing power by the aggregate total of the computing power of the network. SQL/MX Distributed Database Features For SQL/MX Release 3.1, you can:    Register a user catalog on nodes other than the node where the catalog was created. The object metadata for the catalog remains on the original node. Each registered node then can locate the object metadata for that catalog.
PAGE 406
Managing an SQL/MX Distributed Database SQL/MX Distributed Database Features User Data Distribution You can distribute tables and indexes across nodes in an Expand network or allow them to reside intact on single nodes. User database objects, including base tables, indexes, and views, are distributed with the LOCATION clause in DDL and utility operations. Distribution of user data is an explicit user action.
PAGE 407
Managing an SQL/MX Distributed Database SQL/MX Distributed Database Features Guardian file names when accessed by their ANSI names on remote nodes. Similarly, the metadata definitions for a database object, identified by its ANSI name, can be located and retrieved regardless of where that database object's metadata definitions are located in the network. For location-independent ANSI names, ANSI name lookup always starts with the CATSYS and SCHEMATA system schema tables on the local node.
PAGE 408
Managing an SQL/MX Distributed Database Naming Network Nodes Because of transparency, users need not be aware of what node they are executing on or where metadata or user data is located. Security Guidelines     Access to a remote node in an Expand network—including access to distributed SQL/MX database objects and registered catalogs—requires successful remote password validation.
PAGE 409
Managing an SQL/MX Distributed Database Using Catalog References in an SQL/MX Distributed Database ANSI Names SQL/MX database objects have three-part, location-independent ANSI names of the form catalog.schema.object. Location independence means that there is no identification of the node or volume on which an SQL/MX database object exists. SQL/MX objects that reside on one node and are accessed from other nodes must be visible from those other nodes.
PAGE 410
Managing an SQL/MX Distributed Database Using Catalog References in an SQL/MX Distributed Database System Schema Tables A record of the catalogs that are registered on a node is maintained in the system schema on the node. The system schema name, NONSTOP_SQLMX_nodename.SYSTEM_SCHEMA, enables each node in an Expand network to have a unique ANSI name for its system schema. The metadata tables in the system schema are called system schema tables.
PAGE 411
Managing an SQL/MX Distributed Database     Using Catalog References in an SQL/MX Distributed Database The object metadata for a catalog, including schema metadata and metadata definitions, cannot be replicated from the automatic reference node to other nodes. Catalog registration metadata can be replicated to other nodes. Use the REGISTER CATALOG command from the automatic catalog reference node to create empty catalog references on other nodes in the distributed database.
PAGE 412
Managing an SQL/MX Distributed Database  Using Catalog References in an SQL/MX Distributed Database The SCHEMATA table on \N1 contains one row for each CAT1 schema, including these entries in the SCHEMA_NAME column: SCHEMA_NAME SCH1 SCH2  The CAT_REFERENCES table on \N1 contains three rows for the catalog CAT1, one for the automatic catalog reference node and one for each empty manual reference node.
PAGE 413
Managing an SQL/MX Distributed Database  Managing Network Security The CAT_REFERENCES table on \N2 and \N3 contains two rows for the catalog CAT1, one for \N1 and another for the local node.
PAGE 414
Managing an SQL/MX Distributed Database Maintaining Local Autonomy in a Network for SQL/MX Release 3.x Users of a distributed node must have remote passwords for remote network access. The access privileges assigned by GRANT to users for SQL/MX objects on the local node also apply to those users when the objects are located on a remote node or are accessed from a remote node to which a user has remote access authorization. Maintaining Local Autonomy in a Network for SQL/MX Release 3.
PAGE 415
Managing an SQL/MX Distributed Database  Maintaining Local Autonomy in a Network for SQL/MX Release 3.x The user data that the query attempts to access is stored on available disks on the local node or some other available nodes. When a query on a range-partitioned table uses the partitioning key to limit the table scan to one row or a range of rows that can be located in a subset of the partitions, only those necessary partitions are accessed.
PAGE 416
Managing an SQL/MX Distributed Database Maintaining Local Autonomy in a Network for SQL/MX Release 3.x configurations with respect to their ability to minimize the consequences of node loss on surviving network nodes. Network Outage Failure Scenario Suppose that you have an SQL/MX database that is distributed across three nodes (\A, \B, and \C). The database includes two user catalogs, CAT_1 and CAT_2, which were created on node \B and have been registered for nodes \A and \C.
PAGE 417
Managing an SQL/MX Distributed Database Maintaining Local Autonomy in a Network for SQL/MX Release 3.x Table 13-3. Consequences of Network Node Loss, Original Configuration Node CAT_1 and CAT_2 User Data Present? CAT_1 and CAT_2 Metadata Present? CAT_1 and CAT_2 Applications Present? \A Yes No Yes From nodes \B and \C, you cannot perform DDL or utility operations on objects partitioned across node \A. You cannot perform staticallycompiled DML operations that require data stored on node \A.
PAGE 418
Managing an SQL/MX Distributed Database Maintaining Local Autonomy in a Network for SQL/MX Release 3.x Note that the loss of the node where the object metadata resides does not prevent statically-compiled queries from being performed in application processes on the surviving nodes to access local data. However, they can query only data located on the local node or some other surviving node.
PAGE 419
Managing an SQL/MX Distributed Database Maintaining Local Autonomy in a Network for SQL/MX Release 3.x Table 13-5. Consequences of Network Node Loss, Reconfiguration 2 Node CAT_1 and CAT_2 User Data Present? CAT_1 and CAT_2 Metadata Present? CAT_1 and CAT_2 Applications Present? \A Yes Yes for CAT_1 Yes for those parts that mainly access CAT_1 From nodes \B and \C, you cannot perform DDL or utility operations on any of the CAT_2 catalog’s objects, which have partitions on node \A.
PAGE 420
Managing an SQL/MX Distributed Database Maintaining Local Autonomy in a Network for SQL/MX Release 3.x If CAT_1 and CAT_2 have few or no interrelations and if the database objects in the two catalogs are rarely or never accessed by the same application programs or queries, a configuration like that shown in Table 13-5 might make good sense. In this configuration, an outage of node \A does not affect metadata access by applications and queries that access object data in CAT_2.
PAGE 421
Managing an SQL/MX Distributed Database Creating a Distributed SQL/MX Database Table 13-6. Consequences of Network Node Loss, Reconfiguration 3 Node CAT_1 and CAT_2 User Data Present? CAT_1 and CAT_2 Metadata Present? CAT_1 and CAT_2 Applications Present? \A Yes Yes for CAT_1 Yes, accesses CAT_1 and CAT_2 From nodes \B and \C, you cannot perform DDL operations on objects partitioned across node \A.
PAGE 422
Managing an SQL/MX Distributed Database  Creating, Registering, and Unregistering Catalog References Distributing SQL/MX Database Objects on page 13-20 For instructions on creating a distributed SQL/MP database, see the SQL/MP Installation and Management Guide.
PAGE 423
Managing an SQL/MX Distributed Database Distributing SQL/MX Database Objects Example—Creating Local Views on Local and Remote Tables 1. From the local node \A, create the catalog CAT1: CREATE CATALOG CAT1; 2. From the local node, create the schema SCH1: CREATE SCHEMA CAT1.SCH1; 3. From the local node, register the catalog CAT1 on the remote node \B: REGISTER CATALOG CAT1 ON \b.$data02; 4. From the local node, create the table TABLOC, which resides on the local node: CREATE TABLE CAT1.SCH1.
PAGE 424
Managing an SQL/MX Distributed Database Altering Distributed Objects 2. From the local node \A, create the table TABREM, which will reside on the remote node \B: CREATE TABLE CAT1.SCH1.TABREM ( ORDERITEM NUMERIC (6) UNSIGNED ORDERNUM NUMERIC (4) UNSIGNED QTY_ORDERED NUMERIC (5) UNSIGNED LOCATION \B.$DATA02; NO DEFAULT NOT NULL, NO DEFAULT NOT NULL, NO DEFAULT NOT NULL,) 3. From the local node \A, create a local index for the table TABREM on the remote node \B: CREATE INDEX CAT1.SCH1.
PAGE 425
Managing an SQL/MX Distributed Database Dropping Distributed Objects Dropping Distributed Objects Before you can drop a distributed table, index, or view, all objects dependent on that table and its metadata must be accessible. For range-partitioned tables or indexes, use the MODIFY utility to drop empty partitions on remote nodes. For hash-partitioned tables and indexes, use MODIFY to drop nonempty partitions only.
PAGE 426
Managing an SQL/MX Distributed Database Changing Network Environments running on a remote note. Local programs can update or retrieve remote data directly through NonStop SQL/MX. NonStop SQL/MX uses the remote I/O features of the Guardian file system and disk process. Alternately, when you need to update data stored on a remote node, you can send a message containing an update request to an application on that remote node.
PAGE 427
Managing an SQL/MX Distributed Database Managing Mixed Versions of NonStop SQL/MX the operating system release imposes compatibility issues on the database. For more information about versioning issues, see The SQL/MX Environment on page 1-11.   Communication to a node is lost. Nodes can become unavailable for a variety of reasons. For planned outages, you should systematically make inactive all network transactions to the affected node before taking it offline.
PAGE 428
Managing an SQL/MX Distributed Database    Managing Mixed Versions of NonStop SQL/MX Running different versions of SQL/MX software simultaneously on separate nodes of an interrelated SQL/MX database application is not allowed between SQL/MX Release 3.x nodes and SQL/MX Release 2.x nodes. Running a later version of SQL/MX software on a development node that supports a production node or nodes running an earlier version of SQL/MX software is not allowed between SQL/MX Release 3.
PAGE 429
14 Measuring Performance During the life of an SQL application, you might need to measure the performance of all, or part, of the application. Several NonStop software products can provide statistical information about performance. Collecting these statistics requires an in-depth understanding of the system, the layout of the database tables, and the use of the application programs. You usually gather statistics when you are:  Running a performance benchmark.
PAGE 430
Measuring Performance SHOWLABEL Command SHOWLABEL Command The MXCI SHOWLABEL command displays file-label information for SQL/MX objects. (SHOWLABEL does not support SQL/MP objects or SQL/MP aliases.) For performance statistics, use SHOWLABEL to determine index levels and extent information. This example returns detailed information about the EMPLOYEE table. Use the DETAIL option to display information about partitions and indexes: SHOWLABEL SAMDBCAT.PERSNL.
PAGE 431
Measuring Performance SET STATISTICS and DISPLAY STATISTICS Commands has failed) Primary Extent Size: 16 Pages Secondary Extent Size: 64 Pages Max Extents: 160 Extents Allocated: 1 EOF: 12288 Index Levels: 1 Record Expression Label Length: 13536 Security Label Length: 120 Key Encode Length: 2 Key Columns: 0 ASC Partitioning Scheme: RP Partition Array - 1 partition[s] IndexMap Array - 2 index[es] Index[0]: \DMR15.$SYSTEM.ZSDG5WVN.W3LB7T00 Index columns: 2 ASC , 1 ASC , 0 ASC Index[1]: \DMR15.$SYSTEM.
PAGE 432
Measuring Performance SET STATISTICS and DISPLAY STATISTICS Commands To obtain statistics, use either of these commands in your MXCI session: Command Type this command. . . SET STATISTICS ON; Before a statement DISPLAY STATISTICS; After a statement Suppose that this is the last DML command issued: SELECT * FROM SAMDBCAT.SALES.EMPLOYEE: HP NonStop SQL/MX Release 3.
PAGE 433
Measuring Performance Measure Performance Measurement Tool You can display statistics for this statement by using the DISPLAY STATISTICS command: DISPLAY STATISTICS; Start Time End Time Elapsed Time Compile Time Execution Time Table Name 2003/12/11 18:18:24.489 2003/12/11 18:18:24.677 00:00:00.188 00:00:00.046 00:00:00.142 Records Accessed SAMDBCAT.PERSNL.
PAGE 434
Measuring Performance Measure Performance Measurement Tool You can collect performance statistics for SQL/MX objects by using these Measure entities:   SQLPROC provides information about an SQL process. There is one SQLPROC counter record per SQL process selected. SQLSTMT provides information about all SQL statements within an SQL process. There is one SQLSTMT counter record per SQL statement of a selected SQL process. Note.
PAGE 435
Measuring Performance Statistics and Reports for NonStop SQL/MX and prepare reports, see the Measure Reference Manual and the Measure User’s Guide. Statistics and Reports for NonStop SQL/MX Use the Measure entities to gather statistics on an SQL/MX database and application programs. After gathering the statistics, you can generate reports about the statistics. Information gathered by the entities is described next.
PAGE 436
Measuring Performance Statistics and Reports for NonStop SQL/MX FILE Statistics Monitor database files with the FILE entity. The FILE report provides information on all partitions of a specific file and record use by a user process. The FILE report gathers statistics on these measurements:           Escalations of locks Deletes Disk reads Lock waits Message activity Reads Records accessed Records used Updates Writes The FILE report can provide specific data on SQL tables.
PAGE 437
Measuring Performance SQL/MX Measurement Models The DISKFILE entity gathers activity statistics against all partitions of a file object. Use DISKFILE to determine if data is being evenly distributed across all the partitions in a database. You must use DISCOPEN to determine how data is distributed among all partitions of a FILE. The DISCOPEN entity gathers statistics for partition opens by process.
PAGE 438
Measuring Performance SQL/MX Measurement Models Execution Costs Use SQLSTMT counters to analyze the execution costs of a running SQL/MX process. These counters provide information on a statement basis. For counters that have the same names as counters for database access costs (described in Section 15, Enhancing SQL/MX Database Performance), you can directly compare the statement values with table values returned by those counters. After a program begins running, startup costs have already been incurred.
PAGE 439
Measuring Performance     SQL/MX Measurement Models MESSAGES stores the number of messages sent by the system on behalf of this statement, including messages sent by the file system to the disk process. MESSAGE-BYTES stores the number of message bytes sent for this statement by the system. This counter accumulates the number of bytes sent for the messages reported by the MESSAGES counter. LOCK-WAITS stores the number of times the statement waited for a lock request.
PAGE 440
Measuring Performance SQL/MX Measurement Models HP NonStop SQL/MX Release 3.
PAGE 441
15 Enhancing SQL/MX Database Performance To achieve maximum performance, you must provide sufficient hardware to handle the throughput and size of the application database. In addition to hardware, many factors affect the performance of a database and application. Some factors are system dependent, others are application dependent. The factors discussed in this section are specific performance issues that can arise in an installed and operating SQL/MX environment.
PAGE 442
Enhancing SQL/MX Database Performance Using Queries in an SQL/MX Database Using Queries in an SQL/MX Database Queries are the basis of a relational database application. You specify queries explicitly by using application-embedded SELECT and CURSOR statements, ad hoc query requests, and report writer selections. Queries are implicitly specified through UPDATE, INSERT SELECT, and DELETE statements. The number and type of queries used in an SQL/MX environment influence the performance of the database.
PAGE 443
Enhancing SQL/MX Database Performance Understanding the Implications of Concurrency remainder of the operation. Use this method if you want to complete the partition move as soon as possible and if users do not require concurrent access to the data.    Creating a constraint  When you create a CHECK or NOT NULL constraint, NonStop SQL/MX confirms that the column data in all table rows complies with, and does not violate, the constraint before adding it.
PAGE 444
Enhancing SQL/MX Database Performance Minimizing Contention Minimizing Contention When managing partitions, you can minimize contention by using the WITH SHARED ACCESS option of the MODIFY TABLE or MODIFY INDEX statements. For example, this MODIFY TABLE statement uses the WITH SHARED ACCESS option to provide online execution on range-partitioned objects: MODIFY TABLE cat1.sch1.
PAGE 445
Enhancing SQL/MX Database Performance Minimizing Contention Considerations for WITH SHARED ACCESS Option of MODIFY      To eliminate the interval between the time the MODIFY operation completes and a new online dump is taken, use the WITH SHARED ACCESS option so that you can take online dumps while the MODIFY operation proceeds. NonStop SQL/MX sends an event message to EMS, indicating when online dumps can be taken. An operator uses the TMFCOM DUMP FILES command to start online dumps.
PAGE 446
Enhancing SQL/MX Database Performance Avoiding Contention Between DDL or Utility Operations Avoiding Contention Between DDL or Utility Operations Only one DDL or utility statement can operate on a given SQL/MX object (or partition of an SQL/MX object) at a time. An error occurs if you attempt to execute a DDL or utility statement while another process is executing a DDL or utility statement on the same object.
PAGE 447
Enhancing SQL/MX Database Performance  Keeping Statistics Current For large tables, audit trail space can be exceeded during the course of the operation, resulting in termination of the operation and backout by the TMF subsystem. This condition is minimized if you allow NonStop SQL/MX to manage TMF transactions. Note. HP recommends that you do not initiate a user-defined TMF transaction for long-running DDL operations. Utility operations are not supported in user-defined TMF transactions.
PAGE 448
Enhancing SQL/MX Database Performance Managing SQL/MX Buffer Space Managing SQL/MX Buffer Space This subsection contains this information:       How DP2 Manages and Reuses Query Plan Fragments on page 15-9 Causes and Symptoms of Query Plan Fragment Reuse Failures on page 15-9 Reduction of Plan Fragment Size for Unique Queries on page 15-10 Using SCF STATS DISK to Monitor SQL/MX Statistics and Reuse Failures on page 15-10 Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute on page 15-13 Strate
PAGE 449
Enhancing SQL/MX Database Performance How DP2 Manages and Reuses Query Plan Fragments cache function. For information about configuring the SQL/MX buffer, see Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute on page 15-13. How DP2 Manages and Reuses Query Plan Fragments A query plan is created whenever an SQL statement is compiled. Depending on the statement, the query plan can execute against a single nonpartitioned table, several partitions of a table, or several tables in a multi-table join.
PAGE 450
Enhancing SQL/MX Database Performance   Reduction of Plan Fragment Size for Unique Queries Planned or unplanned takeovers by the backup DP2 CPU have generated reuse failures. A planned takeover, such as from using SCF PRIMARY or by automatic switch due to a path error, checkpoints only SQL/MX session data that is in use at the time of the takeover. An unplanned takeover, such as from primary CPU failure or softdown failure, ensures that there will be no SQL/MX session data in the new primary CPU.
PAGE 451
Enhancing SQL/MX Database Performance In Use...................           Using SCF STATS DISK to Monitor SQL/MX Statistics and Reuse Failures 0 Failed FST....... Failed ID........ 0 3 Max Data bytes identifies the configured size of the SQL/MX buffer for the disk. In the given examples, the disk has been configured for 131072 KB, or 128 MB, which is the default value.
PAGE 452
Enhancing SQL/MX Database Performance Using SCF STATS DISK to Monitor SQL/MX Statistics and Reuse Failures For information about methods for reducing the incidence of reuse failures in an SQL/MX buffer, see Strategies for Reducing Reuse Failures on page 15-14. Publish/Subscribe counters 4-> stats disk $data02,sqlmx STORAGE - Stats DISK \DMR15.$DATA02 The following are the Publish/Subscribe counters: Publish/Subscribe bytes.. 54765 KB Max Subscribers.......... 512 Keys In Use......
PAGE 453
Enhancing SQL/MX Database Performance  Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute Max denotes the maximum savepoint buffer size per volume. The value is denoted in KB. 16 KB Blocks   Max denotes the maximum number of blocks that can be used to store savepoint data. In Use denotes the current number of blocks holding savepoint data. Failures     SavePoint Limit denotes the number of failures caused because the savepoint limit was reached.
PAGE 454
Enhancing SQL/MX Database Performance Strategies for Reducing Reuse Failures above the current maximum supported size, DP2 automatically uses the current maximum value. Note. The default, minimum, and maximum SQLMXBUFFER sizes listed are subject to change. The amount of total memory available to the DP2 data cache and SQL/MX buffer space might even be reduced.     As a general rule, try to set SQLMXBUFFER to the lowest size possible that achieves acceptable plan reuse failure rates.
PAGE 455
Enhancing SQL/MX Database Performance Managing DP2 Data Cache Memory Size Increase SQL/MX Buffer Space A common contributing factor for high reuse failure rates is an SQL/MX buffer that is too small to effectively store all the plan fragments that are repeatedly executed by applications, particularly larger fragments. After you confirm a high reuse failure rate in a buffer, use the SCF ALTER command to increase SQLMXBUFFER size, starting with 128 MB and increasing it, as required, in increments of 128 MB.
PAGE 456
Enhancing SQL/MX Database Performance Maximizing Disk Process Prefetch Capabilities To see if a DP2 data cache is operating efficiently, use the SCF STATS DISK, CACHE command. If CACHE READ HITS are less than 90 percent, consider increasing the cache size. If the ratio of CACHE FAULTS to CACHE CALLS is even one or two percent, consider reducing the cache size, adding more physical memory to the CPU, or processing to other CPUs.
PAGE 457
Enhancing SQL/MX Database Performance   Optimizing Index Use Application-addressable memory Memory space for the master executor to execute SQL/MX statements The memory space requirement for the master executor can be minimized by using parallelism to distribute the work to other processes, as explained in the discussion of parallelism in the SQL/MX Query Guide.
PAGE 458
Enhancing SQL/MX Database Performance Maximizing Parallel Index Maintenance Index-only access is faster than a table scan. A sort prevented by index access must be looked at closely, however, because the cost of a scan plus a sort might be less than the cost of index and base table access. For more information about selectivity and cost, see the SQL/MX Query Guide. For more information about determining when to use indexes, see Section 4, Understanding and Planning SQL/MX Tables.
PAGE 459
Enhancing SQL/MX Database Performance Checking Data Integrity it can become impossible for MXCMP to accurately estimate the costs for different query plans for those tables. In effect, the B-trees for the tables become too deep and asymmetrical. Performing a FUP RELOAD on a table or index reduces data fragmentation and other structural inefficiencies and considerably decreases the duration of subsequent table and index queries.
PAGE 460
Enhancing SQL/MX Database Performance Creating Logical Views of Data methods or programs to verify that the existing tables conform to the new data checks.   Programs with hard-coded validity checking cannot move as easily from one set of users to another as programs without hard-coded values. Data integrity checking by constraints  Constraints can greatly enhance the flexibility of programs so that applications move easily from one set of users to another.
PAGE 461
Enhancing SQL/MX Database Performance Avoiding Automatic Recompilations in turn, results in a correspondingly longer access time to the table or index. In such circumstances, you might want to drop this partition while leaving the others defined for the object intact. Avoiding Automatic Recompilations Automatic recompilation can become a significant performance concern. In most cases, you should attempt to run valid programs at all times to ensure the best possible performance.
PAGE 462
Enhancing SQL/MX Database Performance Matching Block Split Operation to Table Usage HP NonStop SQL/MX Release 3.
PAGE 463
A Using Guardian Names with TMF, RDF, and Measure The TMF, RDF, and Measure subsystems provide important infrastructure and management support to NonStop SQL/MX. Currently, however, the user interfaces to these subsystems require Guardian physical file names, whereas SQL/MX tables can be referenced by their ANSI logical names:   Guardian physical file name format: [\node.][[$volume.]subvolume.]filename ANSI logical file name format: [[catalog.]schema.
PAGE 464
Using Guardian Names with TMF, RDF, and Measure MXGNAMES Input Files MXGNAMES Input Files MXGNAMES uses the same information that is provided by SHOWDDL, which always includes all partitions of the base table and all implicitly and explicitly defined indexes on the table. This information constitutes the entire set of files that would have to be recovered or restored to replace all objects described in the SHOWDDL text.
PAGE 465
Using Guardian Names with TMF, RDF, and Measure  MXGNAMES Output Files Avoid using digits at the end of the output file name. Using Output Files With RESTORE to Create an RDF Backup Database For the RESTORE command of Backup and Restore 2, LOCATION clauses contain both the source and target file names. For RDF backup database creation, the source and target file names must match in the volume, subvolume, and file name portions. Only the node names can differ.
PAGE 466
Using Guardian Names with TMF, RDF, and Measure MXGNAMES Examples MXGNAMES Examples For these MXGNAMES examples, suppose that these SQL/MX tables and indexes exist on the system: create table cat.sch.t126a (c1 int not null , c2 timestamp default current_timestamp not null , c3 char(4) default 'abcd' , c4 smallint not null , primary key (c1,c2)) location $vol1.zsd0126a.bxnl1r00 partition (add first key (1r00) location $vol2.zsd0126a.bxnl2r00 , add first key (2r00) location $vol3.zsd0126a.
PAGE 467
Using Guardian Names with TMF, RDF, and Measure MXGNAMES Examples Output would be: (-- Table CAT.SCH.T126A --& $vol1.zsd0126a.bxnl1r*,& $vol2.zsd0126a.bxnl2r*,& $vol3.zsd0126a.bxnl3r*,& $vol4.zsd0126a.bxnl4r*,& $vol5.zsd0126a.bxnl5r*,& $vol6.zsd0126a.bxnl6r*,& & -- index T126A_NDX1 on CAT.SCH.T126A--& $vol1.zsd0126a.qdxwg1*,& $vol2.zsd0126a.qdxwg2*,& $vol3.zsd0126a.qdxwg3*,& $vol4.zsd0126a.qdxwg4*,& -- end of Table CAT.SCH.T126A--& & -- Table CAT.SCH.T126B--& $vol1.zsd0126a.bxnw1r*, & $vol2.zsd0126a.
PAGE 468
Using Guardian Names with TMF, RDF, and Measure MXGNAMES Examples Given the file SHOWD123: CREATE TABLE CAT.SCH.T126A ( C1 INT NO DEFAULT -- NOT NULL NOT DROPPABLE , C2 TIMESTAMP(6) DEFAULT CURRENT_TIMESTAMP -- NOT NULL NOT DROPPABLE , C3 CHAR(4) CHARACTER SET ISO88591 COLLATE DEFAULT DEFAULT_ISO88591'abcd' , C4 SMALLINT NO DEFAULT -- NOT NULL NOT DROPPABLE , CONSTRAINT CAT.SCH.T126A_106009919_0001 PRIMARY KEY (C1 ASC, C2 ASC) NOT DROPPABLE , CONSTRAINT CAT.SCH.T126A_106009919_0000 CHECK (CAT.SCH.T126A.
PAGE 469
Using Guardian Names with TMF, RDF, and Measure MXGNAMES Examples NAME PART_A_Z_1 ) ; The resulting contents of file NAMELST would be: (-- Table CAT.SCH.T126A -- & $vol1.zsd0126a.bxnl1r*,& $vol2.zsd0126a.bxnl2r*,& $vol3.zsd0126a.bxnl3r*,& $vol4.zsd0126a.bxnl4r*,& $vol5.zsd0126a.bxnl5r*,& $vol6.zsd0126a.bxnl6r*,& & -- index T126A_NDX1 on CAT.SCH.T126A -- & $vol1.zsd0126a.qdxwg1*,& $vol2.zsd0126a.qdxwg2*,& $vol3.zsd0126a.qdxwg3*,& $vol4.zsd0126a.qdxwg4* & -- end of Table CAT.SCH.
PAGE 470
Using Guardian Names with TMF, RDF, and Measure MXGNAMES Examples additional comment, indicating what object’s partitions are being displayed. For example: (-- index T126A_NDX1 on CAT.SCH.T126A continued -- & $vol1.zsd0126a.qdxwg1*,& $vol2.zsd0126a.qdxwg2*,& $vol3.zsd0126a.qdxwg3*,& $vol4.zsd0126a.qdxwg4* & -- end of Table CAT.SCH.T126A) -- Restore Example 1: Input Is a List of SQL/MX Table Names MXGNAMES -SQLNames=$VOL1.SQLSTUFF.
PAGE 471
Using Guardian Names with TMF, RDF, and Measure MXGNAMES Examples Assuming the local node is called \PNODE, the output of the command would be: LOCATION ( \pnode.$vol1.zsd0126a.bxnl1r00 TO \bnode.$vol1.zsd0126a.bxnl1r00, \pnode.$vol2.zsd0126a.bxnl2r00 TO \bnode.$vol2.zsd0126a.bxnl2r00, \pnode.$vol3.zsd0126a.bxnl3r00 TO \bnode.$vol3.zsd0126a.bxnl3r00, \pnode.$vol4.zsd0126a.bxnl4r00 TO \bnode.$vol4.zsd0126a.bxnl4r00, \pnode.$vol5.zsd0126a.bxnl5r00 TO \bnode.$vol5.zsd0126a.bxnl5r00, \pnode.$vol6.zsd0126a.
PAGE 472
Using Guardian Names with TMF, RDF, and Measure MXGNAMES Examples Suppose that the contents of the file SHOWD123 are as indicated previously. The contents of output file NAMELST2 would be: LOCATION ( \pnode.$vol1.zsd0126a.bxnw1r00 TO \bnode.$vol1.zsd0126a.bxnw1r00, \pnode.$vol2.zsd0126a.bxnw2r00 TO \bnode.$vol2.zsd0126a.bxnw2r00, \pnode.$vol3.zsd0126a.bxnw3r00 TO \bnode.$vol3.zsd0126a.bxnw3r00, \pnode.$vol4.zsd0126a.bxnw4r00 TO \bnode.$vol4.zsd0126a.bxnw4r00, \pnode.$vol5.zsd0126a.bxnw5r00 TO \bnode.
PAGE 473
Index A ABORT DISK command, SCF 12-37 Access paths alternate 7-33 clustering key 7-33 Access privileges for SQL/MX objects 7-10 ADD COLUMN clause 7-23 ADD DUMPS TMF command 5-15 Adding aliases 9-14 Adding catalogs 9-4 Adding columns description of 9-5 example of 9-7 Adding constraints 9-7 Adding indexes 9-8 Adding objects authorization requirements 9-3 summary 9-2 Adding partitions description of 9-11 example of 9-13 Adding schemas 9-13 Adding SQL/MP aliases 9-14 Adding stored procedures 9-14 Adding tables
PAGE 474
Index B environment 1-1 moving programs from development to production 11-3 moving to remote node 11-8 reasons not to move a program with compiled modules 11-4 running on a remote node 11-9 writing to recover from temporary service organizations 11-1 Archiving SQL/MX objects 5-30 ASCII character set 7-23 Attributes altering 9-23 column, specifying 7-32 Audit bit, changing 12-34 Audit trails 12-2 AUDITCOMPRESS attribute altering index attributes 9-20 altering table attributes 9-24 audit trail data 7-22 Aud
PAGE 475
Index C BROKEN flag 12-33 Broken partitions, recovering 12-33 Buffer size, effect on query performance 15-14 C C preprocessor 3-17 Cache size 15-14 CACHE_HISTOGRAMS 6-3 CACHE_HISTOGRAMS_ REFRESH_INTERVAL 6-3 CALLS counter, Measure 14-10 CASCADE option 12-11 CAST function 7-24 Catalogs adding 9-4 CATALOGS table, displaying contents of 8-42 creating 7-7 dropping 9-28 querying metadata about 8-23/8-26 recovering 12-6 SCF ALTER DISK issues 12-38 SCF ALTER DISK, LABEL issues 12-39 Changing catalog, schema, an
PAGE 476
Index D adding columns 9-23 adding tables 9-15 creating table partitions 7-16 description of 7-11 CREATE VIEW statement 7-39 Creating catalogs 7-7 Creating constraints 7-37 Creating table partitions 7-16 C++ preprocessor 3-17 D Damaged objects, deleting 12-37 Data checking 1-4, 15-18 integrity constraints 15-19 partitioned 7-16 validity 1-4 views and consistency 4-11 Data consistency, checking 12-30 Data problems correcting 12-32 identifying 12-27 Data types ASCII 7-25 character 7-24 datetime 7-24 defaul
PAGE 477
Index D DDL operation limits 15-6 DDL statements 1-13 DDL_DEFAULT_LOCATIONS using to distribute primary range partitions 7-19 Decoupled keys 7-16 DEFAULT clause 7-30 DEFAULT NULL clause 7-31 Default settings altering 9-22 ANSI compliance 6-8 description of 6-1 setting and updating 6-1 SYSTEM_DEFAULTS table 6-2 Defaults See System defaults DEFINE names 1-7 Defining table columns 7-23 Definitional integrity 1-4 DELETES counter 14-11 dependent objects 11-19 Disks erasing 12-39 labeling 12-39 message operatio
PAGE 478
Index E DROP command, CASCADE option 12-11 Dropping aliases 9-33 Dropping catalogs 9-28 Dropping columns 9-28 Dropping constraints 9-29 Dropping indexes 9-30 Dropping objects authorization requirements 9-26 summary 9-26 Dropping partitions description of 9-31 example of 9-32 Dropping schemas 9-32 Dropping SQL/MP aliases 9-33 Dropping stored procedures 9-34 Dropping table data 9-36 Dropping tables 9-34 Dropping triggers 9-36 Dropping views 9-37 Dump See also Backing up file recovery 12-2 DUP copying tables
PAGE 479
Index G SQL/MX 3-8 structure 4-3, 4-5 FILE, Measure entity access costs 14-11 description of 14-6 statistics 14-7 FIRST KEY clause 7-32 FIXUP command, mxtool broken objects 12-32 description of 12-26 examples of 12-20, 12-21, 12-35 timestamps 12-11 FLOAT data type 7-26 FLOATTYPE 6-3 FUP RELOAD command 10-2 STATUS command 10-3, 10-4 SUSPEND command 10-4 Using RELOAD to generate more accurate query plans 15-17 G GOAWAY command, mxtool damaged objects 12-37 description of 12-26 Guardian files physical names
PAGE 480
Index J benefits of 9-8 description of 4-12 OR operations 4-18 sort operations 4-18 ordering rows 4-18 parallel maintenance 15-17 partitioning 15-19 primary key 4-16 querying metadata about 8-46/8-50 unique 4-14 Index-only access 4-17 Indices restoring 12-24 INFO command, mxtool description of 12-26 Guardian files 8-3, 12-32 INITIALIZE DISK command, SCF 12-39 Installing SPRs for H-series RVUs 3-3 Installing Visual Query Planner 3-16 InstallSqlmx command options 3-21 error messages 3-22 phases of 3-19 synt
PAGE 481
Index M CALLS counter 14-10 description of 14-5 DISK-READS counter 14-10, 14-11 ELAPSED-BUSY-TIME counter 14-10 ELAPSED-RECOMPILE-TIME counter 14-10 FILE entity 14-6, 14-7, 14-11 index creation 9-9 LOCK-WAITS counter 14-10, 14-11 MESSAGES counter 14-10 MESSAGES-BYTES counter 14-10 OPENS counter 14-9 RECOMPILES counter 14-10 RECORDS-ACCESSED counter 14-10, 14-11 RECORDS-USED counter 14-10, 14-11 SQLPROC entity 14-6 SQLSTMT entity 14-6 SQL-NEWPROCESS counter 14-9 SQL-NEWPROCESS-TIME counter 14-9 SQL-OBJ-REC
PAGE 482
Index M schema feature version, displaying 8-70 schema owner, displaying 8-35 schema UID, displaying 8-27 schema version, displaying 8-70 schemas in a catalog, displaying 8-25 schemas on a node, displaying 8-28 SQL/MP alias name attributes, displaying 8-45 SQL/MP names in a schema, displaying 8-44 SQL/MX version, displaying 8-69 system schema tables, locating 8-21 system schema version, displaying 8-70 table attributes, displaying 8-41, 8-42 tables containing a column, displaying 8-64 tables in a catalog,
PAGE 483
Index N EDIT files A-2 examples of A-4 input files A-2 output files A-2 SHOWDDL file A-2 SQL names file A-2 mxlangman.jar file 3-12 mxlangman.
PAGE 484
Index P Objects altering 9-18 archiving 5-7 naming guidelines for 4-1 restoring 12-22 SCF ALTER DISK, LABEL issues 12-39 OLTP 7-41 Online dumps 12-10 Online help downloading from NTL at doc.hp.
PAGE 485
Index Q alternate setting 6-4 ANSI compliance 6-8 Privileges querying metadata about 8-65/8-68 required to execute utilities 1-12 PROCESSH entity 14-8 Production system, steps for moving applications to 11-7 Program files moving to a production system 11-3 moving without compiled modules 11-5, 11-7 Programs backing up and restoring 11-29 constraints 15-18 description of 11-1, 14-9 distributing across nodes 11-8 integrating new columns 9-6 versioning issues for a distributed database environment 11-8 Prohi
PAGE 486
Index S FROMARCHIVE option 12-2, 12-10 Guardian names 12-3 indexes 12-13 MAP NAMES parameter 12-6 TOFIRSTPURGE option 12-10 Recovery BROKEN flag, resetting 12-33 broken partition 12-33 catalog 12-6 consistent files 12-33 crash-open files 12-1 disk or node failure 12-1 EDIT files 7-22 file 12-2 from SCF commands 12-37 indexes 12-22 planning 5-1 RESETBROKEN option 12-33 SQL/MX objects 5-8 tables description of 5-8 partitioned 12-13 with indexes 12-13 TMF 5-20, 12-2 tools 12-1 views 12-22 volume 12-2 Redefin
PAGE 487
Index S Security globally placed modules 11-16 locally placed modules 11-17 planning 5-1 Safeguard 5-4 views 7-40 SELECT statement views 15-19 Sequential cache 15-14 SERVER object, OPEN command 2-4 Servers constraints 15-18 opening 2-4 SET STATISTICS ON command 14-1, 14-3 Setting DEFINEs 11-11 Setting SQL/MP aliases 11-11 Setting the national character set 3-22 SHOWDDL command CREATE INDEX statement 12-22 DDL syntax 5-8 description of 8-4 example of 12-18 OBEY command file 5-9 output files 12-25 recovery
PAGE 488
Index S subvolume and file naming 7-2 using InstallSqlmx 3-18 verifying software versions of Java components 2-3 verifying the versions of executable files 3-15 SQL/MX buffer causes and symptoms of reuse failures 15-9 how DP2 reuses plan fragments 15-9 managing 15-8 plan fragment size reduction 15-10 reducing reuse failures 15-13 using SCF ALTER DISK to resize the buffer 15-12 using SCF STATS DISK to monitor reuse failures 15-10 SQL/MX C 3-17 SQL/MX COBOL 3-17 SQL/MX compilation compile and recompile stat
PAGE 489
Index T PRIMARY_KEY_CONSTRAINT_DROP PABLE_OPTION 6-4 QUERY_CACHE 6-4 QUERY_CACHE_ STATEMENT_PINNING 6-5 QUERY_CACHE_MAX_ VICTIMS 6-4 QUERY_CACHE_REQUIRED_ PREFIX_KEYS 6-5 READONLY_CURSOR 6-8 REF_CONSTRAINT_NO_ ACTION_LIKE_RESTRICT 6-6, 6-8 SAVE_DROPPED_TABLE_DDL 6-6 SCRATCH_DISKS 6-6 SCRATCH_DISKS_ EXCLUDED 6-6 SCRATCH_DISKS_ PREFERRED 6-7 setting and updating 6-1 TEMPORARY_TABLE_ HASH_PARTITIONS 6-7 TIMEOUT 6-7 UDR_JAVA_OPTIONS 6-7 System module files 3-13 System schema tables, locating 8-11/?? SYSTEM_DE
PAGE 490
Index U RELEASED attribute 12-10 operations that affect TMF online dumps 5-15 preinstallation tasks 3-2 RECOVER FILES command description of 12-2 example of 12-19 examples of 12-5 indexes 12-13 MXGNAMES utility 12-3 partitions 12-10 recovery catalogs 12-6 database 5-20 dropped SQL/MX objects 5-20 files 12-2, 12-3 network considerations 13-25 volumes 1-3, 5-12, 12-1, 12-2 re-creating online dumps 5-15 specifying attributes 5-13 SQL/MX requirements for 5-12 START command 12-2 starting 2-3 transaction backou
PAGE 491
Index W creating 7-39, 9-17 defining 4-11 description of 4-11 dropping 9-37 performance issues 15-19 querying metadata about 8-42/8-43 recovering 12-22 restoring 12-24 securing 7-39, 7-40 Visual Query Planner, installing 3-16 Volume recovery, TMF 1-3, 5-12, 12-2 Volumes labeling 12-39 mirrored 5-6 removing 12-40 Safeguard protection 5-4 SCF ALTER DISK, LABEL command 12-39 SCF STOP DISK and START DISK 12-40 W WITH SHARED ACCESS option 15-4 WRITEs 14-11 Z ZCLIDLL 3-6 ZCLIPDLL 3-6 ZMXSTMPL file 3-14 HP No
PAGE 492
Index Z HP NonStop SQL/MX Release 3.