HP NonStop SQL/MX Installation and Management Guide Abstract This manual describes how to install and manage HP NonStop™ SQL/MX. Product Version NonStop SQL/MX Releases 2.0, 2.1, and 2.2 (SPR identifier for Release 2.2: H22^ACF) Supported Release Version Updates (RVUs) This publication supports H06.04 and all subsequent H-series RVUs until otherwise indicated by its replacement publication.
Document History Part Number Product Version Published 523723-001 NonStop SQL/MX Release 2.0 April 2004 523723-002 NonStop SQL/MX Release 2.0 August 2004 523723-003 NonStop SQL/MX Releases 2.0 and 2.1 June 2005 523723-004 NonStop SQL/MX Releases 2.0 and 2.1 October 2005 540436-001 NonStop SQL/MX Releases 2.0, 2.1, and 2.
HP NonStop SQL/MX Installation and Management Guide Index Figures What’s New in This Manual xv Manual Information xv New and Changed Information Tables xvi About This Manual xxi Audience xxi Organization xxi Related Documentation xxii Notation Conventions xxvi 1.
1. Introduction to SQL/MX Database Management (continued) Contents 1. Introduction to SQL/MX Database Management (continued) 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.
Contents 4. Understanding and Planning SQL/MX Tables 4.
5. Planning Database Security and Recovery (continued) Contents 5.
Contents 7. Creating an SQL/MX Database (continued) 7.
7. Creating an SQL/MX Database (continued) Contents 7. Creating an SQL/MX Database (continued) Creating Views of SQL/MX Tables 7-39 Rules for Creating and Accessing Views 7-40 Examples for Creating Views for SQL/MX Tables 7-40 Creating Triggers 7-41 Guidelines on Using Triggers 7-41 Trigger Temporary Tables 7-41 Creating Stored Procedures in Java 7-41 Database Design Guidelines for Improving OLTP Performance 7-41 8.
Contents 8. Querying SQL/MX Metadata (continued) 8.
Contents 8. Querying SQL/MX Metadata (continued) 8. Querying SQL/MX Metadata (continued) Displaying Object Integrity and Consistency 8-63 Displaying Version Numbers 8-63 Displaying the NonStop SQL/MX Release Identifier 8-63 Displaying the Schema Version 8-64 Displaying the System Schema Version 8-64 Displaying the Object Schema Version (OSV) 8-64 Displaying the Object Feature Version (OFV) 8-64 9.
Contents 9. Adding, Altering, and Dropping SQL/MX Database Objects (continued) 9. Adding, Altering, and Dropping SQL/MX Database Objects (continued) Dropping SQL/MX Indexes 9-30 Dropping Partitions for SQL/MX Tables and Indexes Dropping Schemas 9-32 Dropping SQL/MP Aliases 9-33 Dropping SPJs 9-34 Dropping SQL/MX Tables 9-34 Dropping Triggers 9-36 Dropping Views 9-37 9-31 10.
Contents 10. Reorganizing SQL/MX Tables and Maintaining Data (continued) 10.
Contents 11. Managing Database Applications (continued) 11. Managing Database Applications (continued) Producing Locally Placed Modules or Globally Placed Modules 11-15 Securing User Modules 11-16 Checking Module Dependencies With DISPLAY USE OF 11-19 Grouping Applications and Modules to Run Multiple DISPLAY USE OF Operations 11-21 Removing Modules 11-24 Converting Globally Placed Modules to Locally Placed Modules 11-25 Managing Module Files and Their Applications During Fallback From SQL/MX Release 2.
12. Performing Recovery Operations (continued) Contents 12. Performing Recovery Operations (continued) ALTER DISK, LABEL 12-39 INITIALIZE DISK 12-39 START DISK and STOP DISK 12-39 13.
. Enhancing SQLMX Database Performance (continued) Contents 15.
Tables (continued) Contents Tables (continued) Table 3-1. Table 3-2. Table 3-3. Table 5-1. Table 5-2. Table 6-1. Table 6-2. Table 6-3. Table 6-4. Table 8-1. Table 9-1. Table 9-2. Table 9-3. Table 9-4. Table 9-5. Table 9-6. Table 10-1. 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.
What’s New in This Manual Manual Information HP NonStop SQL/MX Installation and Management Guide Abstract This manual describes how to install and manage HP NonStop™ SQL/MX. Product Version NonStop SQL/MX Releases 2.0, 2.1, and 2.2 (SPR identifier for Release 2.2: H22^ACF) Supported Release Version Updates (RVUs) This publication supports H06.04 and all subsequent H-series RVUs until otherwise indicated by its replacement publication.
New and Changed Information What’s New in This Manual Document History Part Number Product Version Published 523723-001 NonStop SQL/MX Release 2.0 April 2004 523723-002 NonStop SQL/MX Release 2.0 August 2004 523723-003 NonStop SQL/MX Releases 2.0 and 2.1 June 2005 523723-004 NonStop SQL/MX Releases 2.0 and 2.1 October 2005 540436-001 NonStop SQL/MX Releases 2.0, 2.1, and 2.
What’s New in This Manual New and Changed Information Section 1, Introduction to SQL/MX Database Management Added information about the new FCHECK capability to perform integrity verification on an SQL/MX data row and FUP support for ANSI names with the INFO, LISTLOCKS, LISTOPENS, and RELOAD commands to Table 1-3, NonStop Tools for SQL/MX Database Management, on page 1-14.
What’s New in This Manual Section 7, Creating an SQL/MX Database New and Changed Information Added information about the REPEAT USE ALLOWED clause of CREATE SCHEMA under SQL/MX Subvolume Naming Guidelines on page 7-3. Added information about CREATE SCHEMA syntax options under Using the Subvolume Option in the CREATE SCHEMA Statement on page 7-5. Clarified when to use range partitioning and hash partitioning under Creating and Managing Partitions for SQL/MX Tables on page 7-16.
What’s New in This Manual Section 11, Managing Database Applications New and Changed Information Added the subsection Writing SQL/MX Applications to Recover From Temporary Network or Hardware Service Interruptions on page 11-1 to describe how SQL/MX applications can be written to recover from temporary network or hardware service interruptions.
What’s New in This Manual Section 12, Performing Recovery Operations New and Changed Information Added prohibitions against recovering SQL/MX files to new locations under Recovering Files to New Locations on page 12-4. Added information about recovering disabled triggers from dropped tables under Table Recovery Procedures on page 12-9 and Tables With Triggers on page 12-13.
About This Manual This manual describes how to install NonStop SQL/MX Releases 2.0, 2.1, and 2.2 on an HP NonStop server and discusses other issues related to managing an SQL/MX system. Throughout this manual, references to SQL/MX Release 2.x indicate SQL/MX Release 2.0, 2.1, and 2.2, and subsequent releases until indicated in a replacement publication.
Related Documentation About This Manual 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.
Related Documentation About This Manual 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.
Related Documentation About This 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. Visual Query Planner Help Context-sensitive help topics that describe how to use the Visual Query Planner graphical user interface. The NSM/web and Visual Query Planner help systems are accessible from their respective applications.
Related Documentation About This Manual Programming Manuals Introductory Guides SQL/MX Comparison Guide for SQL/MP Users SQL/MX Quick Start SQL/MX Programming Manual for C and COBOL SQL/MX Programming Manual for Java Reference Manuals SQL/MX Reference Manual SQL/MX Messages Manual SQL/MX Glossary SQL/MX Connectivity Service Administrative Command Reference DataLoader/MX Reference Manual Specialized Guides SQL/MX Installation and Management Guide NSM/web Installation Guide SQL/MX Guide to Sto
Notation Conventions About This Manual Notation Conventions Hypertext Links Blue underline is used to indicate a hypertext link within text. By clicking a passage of text with a blue underline, you are taken to the location described. For example: This requirement is described under Backup DAM Volumes and Physical Disk Drives on page 3-2. General Syntax Notation This list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS.
General Syntax Notation About This Manual { } Braces. A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines. For example: LISTOPENS PROCESS { $appl-mgr-name } { $process-name } ALLOWSU { ON | OFF } | Vertical Line.
General Syntax Notation About This Manual a blank line. This spacing distinguishes items in a continuation line from items in a vertical list of selections.
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.
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.
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.
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.
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.
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.
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.
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.
SQL/MX Database Object Types Introduction to SQL/MX Database Management 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.
Introduction to SQL/MX Database Management SQL/MX Database Object Types 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. A column is a set of values of the same data type with the same definition. The intersection of a row and column represents the data value of a particular field in a particular record.
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.
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.
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.
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.
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.2, the FUP commands INFO, LISTLOCKS, LISTOPENS, and RELOAD support the use of ANSI names for SQL/MX objects.
Introduction to SQL/MX Database Management SQL/MX Database Management Tools HP NonStop SQL/MX Installation and Management Guide—540436-001 1-16
2 Preparing to Install NonStop SQL/MX Before you install SQL/MX Release 2.x, check that you have met these preinstallation 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 2.
Software Requirements Preparing to Install NonStop SQL/MX Software Requirements To use SQL/MX Release 2.x, you must install the site update tape (SUT) for the H06.04 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.04 or later RVU contains the correct product versions for these key software products that are used with NonStop SQL/MX: Note.
Verifying Correct Versions of Independent Software Products Preparing to Install NonStop SQL/MX 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
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.
Preparing to Install NonStop SQL/MX For TMF configuration information, see the TMF Reference Manual.
Preparing to Install NonStop SQL/MX HP NonStop SQL/MX Installation and Management Guide—540436-001 2 -6 Starting TMF
3 Installing NonStop SQL/MX Summary of Installation Tasks Before you install SQL/MX Release 2.0, 2.1, or 2.2 (SQL/MX Release 2.x), read Using the InstallSqlmx Script on page 3-20 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.
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.
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 a later release of SQL/MX Release 2.x. 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.
Installing NonStop SQL/MX Installing NonStop SQL/MX OSS environment is case-sensitive, so enter all commands in the appropriate case. You must be a super ID user to execute the InstallSqlmx script. Caution. InstallSqlmx creates the anchor file and assigns it a security of “N----.” Do not attempt to change this setting. If you change the security setting and the anchor file is modified, NonStop SQL/MX will cease to operate on the system.
Verifying the SQL/MX Installation Installing NonStop SQL/MX Verifying the SQL/MX Installation After you have installed NonStop SQL/MX, perform these verification tasks as dictated by the features and requirements of your SQL/MX database environment: For more information about See The SQL/MX files that are automatically installed during SUT installation Overview of Automatic File Installations on page 3-5 The files that need to be licensed and how to verify that they have been properly licensed Verify
Installing NonStop SQL/MX • • • • • • • • • • • Overview of Automatic File Installations T1050PAX, which contains the SQL/MX internal module files T1051OBJ, which contains the object files sqlcli.o, esqlcli.o, and the ANSI names server process executable, ansp.exe. T1051PAX, which contains the header files Platform.h and sqlcli.h.
Installing NonStop SQL/MX • • • Overview of Automatic File Installations mxtool setmxdb ansp.exe b. COPYOSS installs these system module files in the OSS directory /usr/tandem/sqlmx/SYSTEMMODULES: • • • • • • • • • • • • NONSTOP_SQLMX_NSK.SYSTEM_SCHEMA.CMNAMEMAPSQLM NONSTOP_SQLMX_NSK.SYSTEM_SCHEMA.CMSMDIOREADM NONSTOP_SQLMX_NSK.SYSTEM_SCHEMA.CMSMDIOWRITEM NONSTOP_SQLMX_NSK.MXCS_SCHEMA.CATANSIMX NONSTOP_SQLMX_NSK.MXCS_SCHEMA.CATANSIMXGTI NONSTOP_SQLMX_NSK.MXCS_SCHEMA.CATANSIMXJAVA NONSTOP_SQLMX_NSK.
Installing NonStop SQL/MX Verify That Files Are Licensed Verify That Files Are Licensed Make sure that all these files have been licensed: • • • • • • IMPORT MXAUDSRV MXCMP MXESP MXRTDSRV ZCLIPDLL 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.
Summary of Installed SQL/MX Files Installing NonStop SQL/MX 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 700 file, which performs audit fixup for online MODIFY operations after a fuzzy data copy has been created.
Summary of Installed SQL/MX Files Installing NonStop SQL/MX File Name Description Where Installed MXUTP The executable file for the materialized view feature, which is not supported with SQL/MX Release 2.x. $SYSTEM.SYSTEM MXOMSG A message file associated with MXCS. $SYSTEM.SYSTEM IMPORT The IMPORT utility, which resides in Guardian file space and is a licensed Guardian code 700 file.
Summary of Installed SQL/MX Files Installing NonStop SQL/MX File Name Description Where Installed mxexportddl The OSS file for the mxexportDDL utility. See Using Import Catalog Tools on page 3-20. 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.
Summary of Installed SQL/MX Files Installing NonStop SQL/MX 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.
Summary of Installed SQL/MX Files Installing NonStop SQL/MX 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.
Summary of Installed SQL/MX Files Installing NonStop SQL/MX NONSTOP_SQLMX_NSK. SYSTEM_SCHEMA.READDEF The system module file that NonStop SQL/MX uses to read default information in the SYSTEM_DEFAULTS table. NONSTOP_SQLMX_NSK. SYSTEM_SCHEMA.RFORK The system module file that NonStop SQL/MX uses to read and write resource forks and to manage extended label information. NONSTOP_SQLMX_NSK. SYSTEM_SCHEMA.
Verifying Executable File Versions Installing NonStop SQL/MX Header Files These SQL/MX header files are exported for use by embedded SQL/MX applications: File Name Description Where Installed Platform.h Contains platform specific defines used by SQL/MX preprocessor (mxsqlc or mxsqlco) generated code. /usr/tandem/sqlmx/include sqlcli.h Contains all exported CLI function declarations. /usr/tandem/sqlmx/include Interface Description Files File Name Description Where Installed sqlcli.
Installing NonStop SQL/MX Version Version Version TNS/E Post-Installation Tasks procedure: T1050H21_14OCT2005_FCS_212_0915 procedure: T6520H02_01MAY2005_TFDS_SH_H02 procedure: T6520H02_01MAY2005_TFDSAPI_14JUN05_H02 Native Mode: runnable file To make sure that the displayed version information for an executable is correct for the SUT, check the version procedure text.
Installing NonStop SQL/MX Installing Visual Query Planner You are now in the OSS environment. 2. Enter the mxci command to start MXCI. Always enter the command using lowercase letters, because the OSS environment is case-sensitive. The MXCI session begins, and the MXCI prompt (>>) appears. For information about MXCI commands, see the SQL/MX Reference Manual. Installing Visual Query Planner Visual Query Planner is distributed as a setup file named MXVQP in the $SYSTEM.ZMXTOOLS subvolume.
Installing NonStop SQL/MX Installing the SQL/MX Online Help The Web site online help files are updated more often than the files provided on the SUT or on NTL physical media (CD or DVD). Downloading Online Help From NTL On NTL, SQL/MX online help is distributed as downloadable .chm files. To install a help file from NTL: 1. Navigate to the H06.04 or later H-series RVU. 2. From the Categories list, click Online Help. 3.
Installing NonStop SQL/MX Installing the Windows-Hosted SQL/MX C and C++ Preprocessor Installing the Windows-Hosted SQL/MX C and C++ Preprocessor The Windows-hosted SQL/MX C and C++ preprocessor is distributed as a self-extracting ZIP file named T0607SET in the $SYSTEM.ZMXTOOLS subvolume. To install the preprocessor: 1. Use OutsideView, the Receive File option, or FTP to download T607SET to a Windows workstation. The setup file for the preprocessor is a binary file.
Installing NonStop SQL/MX Using Import Catalog Tools Using Import Catalog Tools mxexportddl is the only Import Catalog tool available for SQL/MX Release 2.x. Its primary purpose is to provide a file describing the SQL/MX compilation environment for a table or a single schema. This file is used by service providers to evaluate reported problems with the SQL/MX compiler.
Installing NonStop SQL/MX • • The Phases of InstallSqlmx Verifies that the system metadata tables have been created in the specified volume. Performs FCHECK on all volumes on the local node to locate and possibly correct the labels of SQL/MP tables with added columns. The Phases of InstallSqlmx Table 3-2 describes the six phases of activity performed by InstallSqlmx. If InstallSqlmx cannot successfully initialize NonStop SQL/MX, it aborts. Table 3-2.
Installing NonStop SQL/MX InstallSqlmx Syntax Table 3-2. The Phases of InstallSqlmx (page 2 of 2) Phase Phase Activities 4 InstallSqlmx loads MXCS metadata tables in the MXCS_SCHEMA of the system catalog with their default values. After loading MXCS metadata tables, InstallSqlmx creates a log at /usr/tandem/sqlmx/bin/mxcslog. The viewable log file mxcslog contains status information about the MXCS initialization operation.
InstallSqlmx Error Messages Installing NonStop SQL/MX Table 3-3. InstallSqlmx Command Options Option Description -b Use this option to rebuild the anchor file if it is corrupted. When the -b option is specified, InstallSqlmx searches the system for the system metadata subvolume ZSD0, which should exist at only one location on a system. When InstallSqlmx locates ZSD0, it drops the current anchor file, re-creates it, and adds the correct volume.
Installing NonStop SQL/MX InstallSqlmx Error Messages HP NonStop SQL/MX Installation and Management Guide—540436-001 3- 24
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.
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 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.
Understanding and Planning SQL/MX Tables Avoid Putting Same-Named Catalogs on Multiple Distributed Nodes Avoid Putting Same-Named Catalogs on Multiple Distributed Nodes If you create catalogs with the same name on two or more nodes, each node can access only the objects in the local catalog. You cannot join the tables or indexes in the same-named catalogs on different nodes.
Understanding and Planning SQL/MX Tables Types of Keys Used in SQL/MX Tables and Indexes page 4-5 discusses the structure of key-sequenced files. An understanding of the key-sequenced file structure can help you plan the best use of disk storage space when sizing your SQL/MX database, implementing economical table-access methods, and analyzing various performance trade-offs. This understanding is also essential for anyone using the FCHECK utility to operate on physical file structures.
Understanding and Planning SQL/MX Tables Partitioning Key Partitioning Key You specify a partitioning key through the PARTITION BY clause of the CREATE TABLE or CREATE INDEX statement. The FIRST KEY option of the PARTITION clause specifies the beginning of the range for a range-partitioned table or index partition. The FIRST KEY clause specifies the lowest values in the partition for columns stored in ascending order and the highest values for columns stored in descending order.
Understanding and Planning SQL/MX Tables Key-Sequenced Tree Structure When NonStop SQL/MX reads a key-sequenced file by its clustering key, each read operation retrieves the record containing the next sequentially higher key value. When NonStop SQL/MX reads the file through an alternate index, each operation randomly accesses the table. If you do not use an index, access occurs by the clustering key.
Key-Sequenced Tree Structure Understanding and Planning SQL/MX Tables Figure 4-1.
Understanding and Planning SQL/MX Tables Planning Table and Index Partitioning Key-sequenced files are also used to store indexes. When an index block fills up, it is split in a similar manner: a new index block is allocated, and some of the pointers are moved from the old index block to the new one. The first time a split occurs in a file, the disk process must generate a new level of indexes.
Understanding and Planning SQL/MX Tables When to Use Range Partitioning For information about creating and managing partitions, see the SQL/MX Reference Manual. When to Use Range Partitioning Range partitioning is useful when your data has logical ranges and boundaries into which it can be subdivided and distributed (for example, months of the year). Range partitioning is optimal when the data is evenly distributed across the range.
Understanding and Planning SQL/MX Tables Determining a Database Layout Advantages of Hash Partitioning • • Hash partitioning automatically provides balanced and even distribution of data across available disks, helping to prevent skewing. Hash partitioning is equally efficient as range partitioning for queries involving exact matches on clustering key values because the search can be confined to one disk volume.
Understanding and Planning SQL/MX Tables Using Views Disadvantages include: • • Although you can restrict access to specified table columns when you use GRANT to provide a user with UPDATE or REFERENCES privileges to a table, the GRANT SELECT, DELETE, INSERT, and ALL privileges provide the user with access to all columns in the table. To protect your tables from access by unauthorized users, provide GRANT privileges to table views.
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.
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.
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
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.
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.
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.
Understanding and Planning SQL/MX Tables Determining When to Use Indexes Consider this query: SELECT * FROM INVNTRY ORDER BY QTY_ORDERED, RETAIL_PRICE ; If the INVNTRY table is large, the cost of sorting the table might be very high.
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.
Understanding and Planning SQL/MX Tables Determining When to Use Indexes HP NonStop SQL/MX Installation and Management Guide—540436-001 4- 20
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.
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
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 (
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.
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.
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.
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 2.
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.
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.
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.
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.
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.
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.
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.
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.
Operations That Impact TMF Online Dumps Planning Database Security and Recovery 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.
Using the TMF DUMP FILES Command Planning Database Security and Recovery Table 5-1. SQL/MX Operations That Impact TMF Online Dumps (page 2 of 2) Operation RESTORE Option Effect Recovery Strategy MOVE (simple move) Creates a new partition and deletes the existing partition, invalidating online dumps. Make a new online dump of the moved partition to retain TMF file recovery protection. REUSE Does not invalidate any online dumps of the reused partition. No action needed.
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.
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.
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.
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.
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.
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
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.
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.
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, 2003, the saved DDL file is: /usr/tandem/sqlmx/ddl/CAT.SCH.TAB01-20030423-115715.
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.
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-32. Explicitly restores the named catalog and all subordinate objects. Schema Explicitly backs up the named schema and all subordinate objects.
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 Unless you specify the INDEXES EXCLUDED option, all indexes and their index partitions are automatically backed up when you back up their catalog, schema, or table. See Indexes and Index Partitions on page 5-33.
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.
Planning Database Security and Recovery Backup Objects Hierarchy Daily Backups To provide a high degree of protection, you can use Backup and Restore 2 to perform daily backups. Then the maximum amount of data lost from a failure never exceeds one working day. 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.
Planning Database Security and Recovery SQL/MX Objects That Are Backed Up Explicitly SQL/MX Objects That Are Backed Up Explicitly Backing Up Catalogs SQL/MX catalogs are backed up only when specified, not by default. When you back up a catalog, all of its subordinate objects (for example, schemas and tables) are backed up as well unless they are specifically excluded.
Planning Database Security and Recovery SQL/MX Objects That Are Backed Up Implicitly You can use the BACKUP job option PARTONLY only when a database has partitioned tables and indexes. PARTONLY enables you to back up and restore individual components of a partitioned database. (For more information about this and other job options, see the Backup and Restore 2 Manual.) You cannot use the PARTONLY option with the INDEXES INCLUDED option. Caution.
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.
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.
Using the RESTORE SHOWDDL ON Option to ReCreate Objects Planning Database Security and Recovery 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.
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.
Reviewing and Setting System Defaults Consequences of Inserting Rows Into the SYSTEM_DEFAULTS Table The statements in this example insert a default into the SYSTEM_DEFAULTS table on \MYSYS to set the default setting for the transaction isolation level: SET SCHEMA nonstop_sqlmx_mysys.
New System Defaults for SQL/MX Release 2.0 Reviewing and Setting System Defaults New System Defaults for SQL/MX Release 2.0 Table 6-1 lists the system defaults that are supported by SQL/MX Release 2.0 or later. For more information about these and other system defaults, see the SQL/MX Reference Manual. Table 6-1. New System Defaults for SQL/MX Release 2.0 (page 1 of 3) Default Description CACHE_HISTOGRAMS_ REFRESH_INTERVAL Controls interval at which histograms are refreshed.
New System Defaults for SQL/MX Release 2.0 Reviewing and Setting System Defaults Table 6-1. New System Defaults for SQL/MX Release 2.0 (page 2 of 3) Default Description NOT_NULL_CONSTRAINT_ DROPPABLE_OPTION Default for DROPPABLE (ON) or NOT DROPPABLE for NOT NULL constraint. PM_OFFLINE_TRANSACTION_ GRANULARITY Number of rows that are copied in an offline MODIFY transaction. PM_ONLINE_TRANSACTION_ GRANULARITY Number of rows that are copied in an online MODIFY transaction.
New System Default for SQL/MX Release 2.1 Reviewing and Setting System Defaults Table 6-1. New System Defaults for SQL/MX Release 2.0 (page 3 of 3) Default Description SCRATCH_FREESPACE_ THRSHOLD_PERCENT Indicates how much free space, as a percentage, is left on a disk as a threshold. TEMPORARY_TABLE_HASH_ PARTITIONS Controls the partitioning scheme for triggers temporary tables.
System Default Settings to Review After Installing SQL/MX Release 2.x Reviewing and Setting System Defaults Table 6-2. New System Defaults for SQL/MX Release 2.1 Default Description DDL_DEFAULT_LOCATIONS The physical location of the primary range partition to be created by CREATE statements that do not provide a LOCATION clause, specified as [\node.]$volume. You can specify multiple locations separated by commas.
Alternative Settings For Selected System Defaults Reviewing and Setting System Defaults Table 6-3. Alternative 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.
Alternative Settings For Selected System Defaults Reviewing and Setting System Defaults Table 6-3. Alternative 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.
Reviewing and Setting System Defaults Alternative Settings For Selected System Defaults Table 6-3. Alternative 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.
Alternative Settings For Selected System Defaults Reviewing and Setting System Defaults Table 6-3. Alternative 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.
Reviewing and Setting System Defaults Alternative Settings For Selected System Defaults Table 6-3. Alternative 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 (,).
Settings Required to Achieve ANSI Compliance Reviewing and Setting System Defaults Settings Required to Achieve ANSI Compliance Table 6-4 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-4.
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.
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.
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.
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).
Creating an SQL/MX Database Using the Subvolume Option in the CREATE SCHEMA Statement where subvolume is the name of the designated schema subvolume for the schema in which the table or index is being created, and file-name is a Guardian file name that conforms to the rules specified in SQL/MX Subvolume Naming Guidelines on page 7-3. Otherwise, an error is generated.
Creating an SQL/MX Database • Correcting File Name Problems index-definition 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.
Creating an SQL/MX Database Creating Catalogs When NonStop SQL/MX is installed and initialized on a node, the system catalog, NONSTOP_SQLMX_nodename, and its schemas are automatically created. These metadata schemas are: • • • • • SYSTEM_SCHEMA schema, which contains the five system schema tables used for catalog reference and recording catalog and schema information.
Creating an SQL/MX Database Rules for Creating and Dropping Catalogs It is permissible for two nodes (for example, nodes \A and \B) in a distributed SQL/MX environment to have catalogs with the same name. Each node can access its own catalog and the objects in it by using location-independent ANSI names. However, node \A cannot access anything in the same-named catalog on node \B, and vice versa.
Creating an SQL/MX Database Creating Schemas Creating Schemas After you have created a catalog, you can begin to create its schemas. 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.
Creating an SQL/MX Database Access Privileges for SQL/MX Database Objects This example creates a schema and uses the LOCATION clause to specify the schema subvolume: CREATE SCHEMA mycat.myschema LOCATION ZSDXMJH2; For more information about the CREATE SCHEMA statement, see the SQL/MX Reference Manual. Access Privileges for SQL/MX Database Objects In NonStop SQL/MX, Guardian and Safeguard security are not applicable.
Creating an SQL/MX Database Examples for Granting Privileges Use the WITH GRANT OPTION to specify that users to which privileges are granted are authorized to grant the same privileges to other authorization IDs. To grant a privilege on an SQL/MX table or view, you must have that privilege and also have the right to grant that privilege. That is, the privilege must be issued to you through the WITH GRANT OPTION and not have been revoked.
Creating an SQL/MX Database Security Rules for Creating and Altering SQL/MX Tables stored. Carefully consider the file attributes to ensure that the table meets the needs of your application. Note. To create SQL/MX tables and build an SQL/MX database, you must purchase and install the SQL/MX DDL License product on your system. See Section 2, Preparing to Install NonStop SQL/MX, for more information about this license.
Creating an SQL/MX Database Creating and Using Keys Creating and Using a Primary Key A primary key is the column or set of columns that defines a unique key for a table. Primary key columns cannot contain nulls, so each one must be declared NOT NULL. Use the PRIMARY KEY constraint clause in CREATE TABLE to specify the table columns of a primary key. For NonStop SQL/MX, you use the primary key to: • • Specify one or more columns that uniquely define and identify each row.
Creating and Using Keys Creating an SQL/MX Database • • Use STORE BY PRIMARY KEY to base the clustering key on the primary key columns. This STORE BY option requires that a primary key first be defined in the PRIMARY KEY constraint for CREATE TABLE and that it is NOT DROPPABLE. If the primary key is defined as DROPPABLE, you cannot use it as the clustering key. If you attempt to use it, NonStop SQL/MX returns an error.
Creating an SQL/MX Database Creating and Using Keys Using the SYSKEY A SYSKEY (system-defined clustering key) is a clustering or storage key defined by NonStop SQL/MX rather than by the user. Tables in key-sequenced files with no user-defined clustering keys must have their clustering keys defined by NonStop SQL/MX and stored in a column named SYSKEY. The SYSKEY column’s character type is LARGEINT SIGNED.
Creating an SQL/MX Database Creating and Managing Partitions for SQL/MX Tables Partially Decoupling the Clustering Key and the Partitioning Key Decoupling the clustering key from the partitioning key in a table or index allows the columns that make up the keys to differ. NonStop SQL/MX does not support full decoupling, where all the columns in the two keys differ.
Creating an SQL/MX Database Creating and Managing Partitions for SQL/MX Tables Performance Benefits of Partitioning • • • • • Partitions are independent of one another and only the accessed partition needs to be available. The query plan stores the primary partition. If the primary partition cannot be found, the alternate partitions are checked, starting with local partitions. NonStop SQL/MX does not support the SKIP UNAVAILABLE PARTITION option of SQL/MP.
Creating an SQL/MX Database Creating and Managing Partitions for SQL/MX Tables Recommended Maximum Number of Partitions HP recommends that you limit the number of partitions in an SQL/MX table or index to no more than 512. If you exceed this recommended limit, you might get an MXCMP internal error because of a shortage of virtual memory space. Prohibition Against Using Floating-Point Columns in Partitioning Keys The floating-point data type is an approximate—not an exact—data type.
Creating an SQL/MX Database Creating and Managing Partitions for SQL/MX Tables datatype as the planned table. You can use the CAST function to force a given value to a particular datatype. HASHPARTFUNC is also sensitive to the number of partitions you specify. Therefore, when using the HASHPARTFUNC, make sure this number matches the exact number of partitions you plan for your new or modified table.
Creating an SQL/MX Database Creating and Managing Partitions for SQL/MX Tables include a LOCATION clause. If DDL_DEFAULT_LOCATIONS specifies more than one default volume, it chooses one at random for the location specification. This arrangement might be particularly useful for an application that creates small, short-lived tables as part of processing a more complex task.
Creating an SQL/MX Database Additional Guidelines for Creating Tables Example for Creating an SQL/MX Table With Multiple Range Partitions CREATE TABLE cat1.sch1.
Creating an SQL/MX Database • • • Additional Guidelines for Creating Tables ° Use AUDITCOMPRESS to minimize the amount of audit-trail resources required. Use NO AUDITCOMPRESS if you need to read the complete before-images and after-images directly from the audit trails. ° Use the CLEARONPURGE attribute to control the security and the ability to write to or drop a table. CLEARONPURGE controls the erasure of data from the disk when an SQL/MX table or index is dropped.
Creating an SQL/MX Database Managing Table Data Managing Table Data Defining Table Columns To ensure the validity of your database, you must first define columns correctly for the use of the data and assign data types that provide the best design for your application. It is the database administrator’s task to consider how the data is used and to assign appropriate data types and constraints. When you define a column, you specify the column name, data type, and, optionally, other column attributes.
Creating an SQL/MX Database • • • • Managing Table Data Specify the HEADING and UPSHIFT attributes for your application’s use, if applicable. For more information, see Specifying Column Attributes on page 7-32. Collation of numeric values occurs with negative numbers preceding positive numbers. For the purpose of sorting, the null value is considered to be greater than all other values.
Managing Table Data Creating an SQL/MX Database • Define data types to match those used in host variables or by users, or encourage those who use and program the system to match the data types in the database, including datetime data type ranges. This strategy minimizes data type translations. For example, a NUMERIC data type in DDL might translate to a double data type for the host variable in C code.
Managing Table Data Creating an SQL/MX Database SQL/MX tables do not support the KANJI or KSC5601 character sets. If you do not specify a value for the -n option, the national character set defaults to UCS2. For more information, see InstallSqlmx Options on page 3-22.
Managing Table Data Creating an SQL/MX Database type. These rules apply both to columns of an SQL/MX object and to a host variable field. SQL/MX tables require IEEE floating-point data. SQL/MP tables require Tandem floating-point data. For more information, see the SQL/MX Reference Manual. 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.
Managing Table Data Creating an SQL/MX Database A column value of type TIMESTAMP is made up of these fields: YEAR Year 0001 to 9999 MONTH Month of year 01 to 12 DAY Day of month 01 to 31 HOUR Hour of day 00 to 23 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.
Managing Table Data Creating an SQL/MX Database Guidelines for Date and Time Interval Data When you define a column to hold date and time, date, time, or time interval values, use these general guidelines: • • • • • • • • • A column of the DATE, TIME, or TIMESTAMP type holds a value that represents a date or an instant in time, and a column of the INTERVAL type holds a value that represents a time interval, or duration.
Creating an SQL/MX Database • • Managing Table Data Carries and borrows can occur when adding or subtracting an INTERVAL value to or from a datetime value. For example, subtracting INTERVAL ‘1’ MONTH from TIMESTAMP ‘2004-01-01 12:00:00’ results in TIMESTAMP ‘2003-12-31 12:00:00’. That is, the month field is changed and a borrow occurs from the year field. If an arithmetic expression causes invalid data, an SQL/MX error is generated.
Creating an SQL/MX Database ° • • Managing Table Data For a numeric column, literal should be a numeric literal. NonStop SQL/MX issues warning or error messages if the literal exceeds the defined length of the column. For example: ° If the number of digits to the right of the decimal point exceeds the scale of the column, NonStop SQL/MX issues a warning message that the value was truncated.
Creating an SQL/MX Database Managing Table Data NOT NULL clause to define primary key or clustering key columns. If you provide a null value, NonStop SQL/MX returns an error message. • • A column used in nonunique index keys can contain null values. A column used as a unique key for a single-column index can contain null values, but you can use only one row with a null value. Therefore, you might want to define this type of column as NOT NULL.
Creating an SQL/MX Database Creating Indexes for SQL/MX Tables For example, a part-number column defined in several tables should have the same definition. Do not define one column as PIC X(4) and another as PIC 9(4). By using the same column definition, you ensure that applications can perform join operations during data retrieval or predicate comparisons.
Creating an SQL/MX Database Creating an Index For more information about the rules for accessing SQL/MX objects, see Access Privileges for SQL/MX Database Objects on page 7-10. 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-23.
Creating an SQL/MX Database • • • • • • • Creating and Managing Partitions for SQL/MX Indexes Indexes are automatically loaded in parallel. 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 SQLMX Database Performance.
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.
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.
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.
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.
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.
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.
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.
Creating an SQL/MX Database ° Database Design Guidelines for Improving OLTP Performance If you add a column, reload the table.
Creating an SQL/MX Database Database Design Guidelines for Improving OLTP Performance HP NonStop SQL/MX Installation and Management Guide—540436-001 7- 44
8 Querying SQL/MX Metadata Use the commands described in this section to obtain the information you need to manage a database.
Commands for Displaying Information Querying SQL/MX Metadata 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.
Querying SQL/MX Metadata mxtool INFO Command For detailed information and examples of this command, see the SQL/MX Reference Manual. 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.
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.
SQL/MX Metadata Tables Querying SQL/MX Metadata SQL/MX Metadata Tables Figure 8-1 shows the SQL/MX metadata table structure. Figure 8-1. SQL/MX Metadata Table Structure Catalog Schemas Note: User catalogs are not shown.
SQL/MX Metadata Tables Querying SQL/MX Metadata Figure 8-1 for that schema. User catalogs do not contain the other metadata schemas shown in Figure 8-1. • There is one set of statistics tables per schema. These are not shown in Figure 8-1. Table 8-1 lists the SQL/MX metadata tables located in the system catalog, including the clustering keys for each table.
SQL/MX Metadata Tables Querying SQL/MX Metadata Table 8-1.
SQL/MX Metadata Tables Querying SQL/MX Metadata Table 8-1.
SQL/MX Metadata Tables Querying SQL/MX Metadata Table 8-1.
SQL/MX Metadata Tables Querying SQL/MX Metadata Table 8-1.
SQL/MX Metadata Tables Querying SQL/MX Metadata Table 8-1.
SQL/MX Metadata Tables Querying SQL/MX Metadata Table 8-1.
SQL/MX Metadata Tables Querying SQL/MX Metadata Table 8-1.
Understanding ANSI External and Internal Names Querying SQL/MX Metadata 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.
Displaying System Schema Information Querying SQL/MX Metadata 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.
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.
Locating System Schema Tables Querying SQL/MX Metadata • 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 4> fup info assoc200, detail $DATA08.ZSD1JHD.
Locating System Defaults Schema Tables Querying SQL/MX Metadata • This is an example from the OSS prompt: # cat /G/system/zsqlmx/mxanchor SQLMX_MetaData_Loc=$data08 # ls /G/data08/zsd0 alluid00 catsys00 alluid01 catsys01 catref00 schema00 catref01 schema01 schrep00 schrep01 # mxtool info \$data08.zsd0.alluid00 NonStop SQL/MX MXTOOL Utility 2.0 (c) Copyright 2003 Hewlett-Packard Development Company, LP. All Rights Reserved. File Name: \FIGARO.$DATA08.ZSD0.
Locating System Defaults Schema Tables Querying SQL/MX Metadata • In this example, the physical Guardian name of the SYSTEM_DEFAULTS table is \FIGARO.$DATA08.ZSDA76D1.WF39P500: showlabel nonstop_sqlmx_node.system_defaults_schema.system_defaults; ============================================================= GuardianName: \FIGARO.$DATA08.ZSDA76D1.WF39P500 AnsiName: NONSTOP_SQLMX_FIGARO.SYSTEM_DEFAULTS_SCHEMA.
Locating System Defaults Schema Tables Querying SQL/MX Metadata • This example is more complex. It uses a SELECT statement to access information from the PARTITIONS, OBJECTS, SCHEMATA, and CATSYS tables to display the partition that contains the SYSTEM_DEFAULTS table. 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.ZSDA984F are the user metadata tables that contain histogram information.
Querying SQL/MX Metadata Locating MXCS Schema Tables All Rights Reserved. File Name: \FIGARO.$DATA08.ZSDA984F.FPJCX800 Object Schema Version: 1200 Ansi Name: NONSTOP_SQLMX_FIGARO.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_figaro.system_defaults_schema.
Locating MXCS Schema Tables Querying SQL/MX Metadata This example uses TACL commands to locate the MXCS schema tables. 1> fup copy $system.zsqlmx.mxanchor SQLMX_MetaData_Loc=$DATA08 2> volume $data08.zsd1 3> files $DATA08.ZSD1 ASSOC200 NAME2I00 ASSOC201 NAME2I01 DATASO00 RESOUR00 DATASO01 ENVIRO00 RESOUR01 ENVIRO01 4> fup info assoc200, detail $DATA08.ZSD1JHD.ASSOC200 12 Feb 2004, 12:41 SQL ANSI TABLE ANSI NAME NONSTOP_SQLMX_FIGARO.MXCS_SCHEMA.ASSOC2DS RESOURCE FORK \FIGARO.$DATA08.ZSD1.
Displaying Catalog Information Querying SQL/MX Metadata Displaying Catalog Information This subsection discusses: • • • • • • Displaying a Catalog UID on page 8-23 Displaying All Catalogs Visible on a Node on page 8-24 Determining Whether a Catalog is Local or Remote on page 8-24 Displaying All Schemas in a Catalog on page 8-25 Displaying the Guardian Location of the Metadata Tables for a Catalog on page 8-25 Displaying the Attributes of a Catalog on page 8-26 Related topic: • Displaying All Tables i
Displaying All Catalogs Visible on a Node Querying SQL/MX Metadata 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.
Displaying All Schemas in a Catalog Querying SQL/MX Metadata Displaying All Schemas in a Catalog This example displays a list of all schemas in a catalog: >> set schema nonstop_sqlmx_figaro.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.
Displaying the Attributes of a Catalog Querying SQL/MX Metadata MVS00000 MVSTIN00 PARTNS00 RIUU0000 TBLPRI00 TRGUSD00 VCOLTC00 VWTBLU00 MVS00001 MVSTIN01 PARTNS01 RIUU0001 TBLPRI01 TRGUSD01 VCOLTC01 VWTBLU01 MVSCOL00 MVSUSE00 REFCON00 ROUTIN00 TBLTEX00 TRIGS000 VCOLU000 MVSCOL01 MVSUSE01 REFCON01 ROUTIN01 TBLTEX01 TRIGS001 VCOLU001 MVSJCL00 OBJECT00 REPLIC00 TBLCON00 TRGCU000 VCOLTA00 VIEWS000 MVSJCL01 OBJECT01 REPLIC01 TBLCON01 TRGCU001 VCOLTA01 VIEWS001 Displaying the Attributes of a Catalog This
Displaying a Schema UID Querying SQL/MX Metadata • • • • • Displaying All Tables in a Schema on page 8-33 Displaying All Views in a Schema on page 8-37 Displaying the Schema Version on page 8-64 Displaying the System Schema Version on page 8-64 Displaying the Object Schema Version (OSV) on page 8-64 The SCHEMATA table on each node is a system schema table that describes all the schemas visible from the node.
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_figaro.system_schema; --- SQL operation complete. >> select substring(schema_name,1,30) as schema_name, substring(cat_name,1,20) as cat_name, node_name from schemata s, catsys c, cat_references cr where s.cat_uid=c.cat_uid and c.cat_uid=cr.
Displaying All Objects in a Schema Querying SQL/MX Metadata 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_1200; --- SQL operation complete.
Displaying the Attributes of a Schema Querying SQL/MX Metadata Displaying the Attributes of a Schema This example displays most of the attributes (in other words, SCHEMA_OWNER, SCHEMA_VERSION, SCHEMA_SUBVOLUME, and others) for the schema SAMDBCAT. SALES: >> set schema nonstop_sqlmx_figaro.system_schema; --- SQL operation complete. >> select substring(s.schema_name, 1, 15) as name, s.schema_owner as owner, s.schema_version as version, s.schema_subvolume as subvol, case s.
Querying SQL/MX Metadata Displaying Table Information Displaying Table Information Topics in this subsection: • • • • • Displaying All Tables in a Catalog on page 8-32 Displaying All Tables in a Schema on page 8-33 Displaying All DDL Locks on a Table on page 8-34 Displaying the Attributes of a Table on page 8-35 Displaying All Tables on a System on page 8-36 Related topics: • • • • • • • • • • Locating System Schema Tables on page 8-15 Locating System Defaults Schema Tables on page 8-18 Determining W
Displaying All Tables in a Catalog Querying SQL/MX Metadata Displaying All Tables in a Catalog This example displays all base table objects in the catalog SAMDBCAT: >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select substring (object_name, 1, 25) as table_name, substring(schema_name, 1, 30) as schema_name from nonstop_sqlmx_figaro.system_schema.catsys c, nonstop_sqlmx_figaro.system_schema.schemata s, objects o where c.cat_uid = s.cat_uid and c.
Displaying All Tables in a Schema Querying SQL/MX Metadata DEPT EMPLOYEE HISTOGRAMS HISTOGRAM_INTERVALS JOB PROJECT CUSTOMER HISTOGRAMS HISTOGRAM_INTERVALS ODETAIL ORDERS PARTS PERSNL PERSNL PERSNL PERSNL PERSNL PERSNL SALES SALES SALES SALES SALES SALES Displaying All Tables in a Schema This example displays all table objects in the schema SALES and catalog SAMDBCAT. Table objects include base tables, views, MP aliases, and SPJs: >> set schema samdbcat.
Displaying All DDL Locks on a Table Querying SQL/MX Metadata Displaying All DDL Locks on a Table This example displays the DDL locks on the table SAMDBCAT.SALES.CUSTOMER: >> set schema samdbcat.definition_schema_version_1200; --- 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.object_name = 'CUSTOMER' and o.object_uid = l.base_object_uid and l.
Displaying the Attributes of a Table Querying SQL/MX Metadata 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_1200; --- SQL operation complete.
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-24). 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-32).
Displaying All Views in a Schema Querying SQL/MX Metadata 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_1200; --- SQL operation complete. >>select substring (object_name, 1, 25) as view_name, object_name_space as name_space from nonstop_sqlmx_figaro.system_schema.catsys c, nonstop_sqlmx_figaro.system_schema.schemata s, objects o where c.cat_uid = s.cat_uid and c.cat_name = 'SAMDBCAT' and s.
Querying SQL/MX Metadata Displaying Information About SQL/MP Aliases Displaying Information About SQL/MP Aliases Topics in this subsection: • • Displaying All SQL/MP Alias Names in a Schema on page 8-38 Displaying All Attributes of an SQL/MP Alias Name on page 8-39 The MP_PARTITIONS table contains the partition names of SQL/MP tables that have SQL/MP aliases. For detailed information about all metadata tables, see Table 8-1 on page 8-6 in this manual or the SQL/MX Reference Manual.
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_1200; --- SQL operation complete. >> select substring(object_name,1,25) as SQLMP_Alias, m.mppartition_name from nonstop_sqlmx_figaro.system_schema.catsys c, nonstop_sqlmx_figaro.system_schema.
Querying SQL/MX Metadata Determining Whether a Table Has Indexes Related topics: • • Displaying All Partitions for a Table or Index on page 8-45 Displaying All Columns in an Index on page 8-53 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.
Displaying All Indexes for a Table Querying SQL/MX Metadata 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_1200; --- SQL operation complete. >> select substring (o1.object_name, 1, 40) as index_name from samdbcat.definition_schema_version_1200.access_paths a, samdbcat.definition_schema_version_1200.objects o1 where a.
Displaying All DDL Locks on an Index Querying SQL/MX Metadata 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_1200; --- 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.
Displaying the Attributes of an Index Querying SQL/MX Metadata 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_1200; --- SQL operation complete. >> select substring(o1.
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_1200; --- 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_1200.access_paths a, samdbcat.
Displaying Partition Information Querying SQL/MX Metadata Displaying Partition Information Topics in this subsection: • • Displaying All Partitions for a Table or Index on page 8-45 Displaying Attributes of a Partition on page 8-46 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 Table 8-1 on page 8-6 in this manual or the SQL/MX Reference Manual.
Displaying Attributes of a Partition Querying SQL/MX Metadata 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.
Querying SQL/MX Metadata Displaying Constraint Information Displaying Constraint Information Topics in this subsection: • • • Determining Whether a Table Has Constraints on page 8-47 Displaying All Constraints on a Table on page 8-48 Displaying the Attributes of a Constraint on page 8-49 Related topics: • • • Displaying All Columns in a Primary Key or Unique Constraint on page 8-54 Displaying All Columns in a NOT NULL Constraint on page 8-55 Displaying All Columns in a Referential Integrity Constrain
Displaying All Constraints on a Table Querying SQL/MX Metadata ) ) 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_1200; --- SQL operation complete. >> select substring (o1.object_name, 1, 30) as constraint_name, case (t.
Displaying the Attributes of a Constraint Querying SQL/MX Metadata 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_1200; --- SQL operation complete. >> select t.constraint_uid, t.table_uid, case t.
Querying SQL/MX Metadata • • • Displaying Column Information Displaying All Columns in a Referential Integrity Constraint on page 8-56 Displaying the Attributes of a Column on page 8-57 Displaying All Tables Containing a Selected Column on page 8-58 Related topic: • Displaying All Privileges for a Column on page 8-62 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.
Displaying All Columns in a Table Querying SQL/MX Metadata Displaying All Columns in a Table This example displays all columns in the table SAMDBCAT.PERSNL.EMPLOYEE: >> set schema samdbcat.definition_schema_version_1200; --- 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.schema_uid = (select schema_uid from nonstop_sqlmx_figaro.system_schema.
Displaying All Columns in a View Querying SQL/MX Metadata Displaying All Columns in a View This example displays all columns in the view SAMDBCAT.SALES.CUSTLIST: >> set schema samdbcat.definition_schema_version_1200; --- 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_figaro.system_schema.
Displaying All Columns in an Index Querying SQL/MX Metadata 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_1200; --- 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.object_uid and ap.
Displaying All Columns in a Primary Key or Unique Constraint Querying SQL/MX Metadata 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_1200; >> select substring(c.column_name, 1, 15) as column_name, c.column_number as col_num, case t.
Displaying All Columns in a NOT NULL Constraint Querying SQL/MX Metadata 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_1200; >> select substring(c.column_name, 1, 15) as column_name, c.column_number as col_num, case o2.
Displaying All Columns in a Referential Integrity Constraint Querying SQL/MX Metadata Displaying All Columns in a Referential Integrity Constraint This example displays all columns with referential integrity constraints for the table SAMDBCAT.SALES.ODETAIL: >> alter table samdbcat.sales.odetail add constraint p1 foreign key (partnum) references samdbcat.sales.parts(partnum); >> set schema samdbcat.definition_schema_version_1200; >> select substring(c.column_name, 1, 15) as column_name, c.
Displaying the Attributes of a Column Querying SQL/MX Metadata 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_1200; --- 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.
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.
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-60 Displaying All Privileges for a View on page 8-61 Displaying All Privileges for a Column on page 8-62 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.
Displaying All Privileges for a Table Querying SQL/MX Metadata Displaying All Privileges for a Table This example displays all privileges for the table SAMDBCAT.SALES.ORDERS: >>set schema samdbcat.definition_schema_version_1200; --- SQL operation complete.
Displaying All Privileges for a View Querying SQL/MX Metadata Displaying All Privileges for a View This example displays all privileges for the view SAMDBCAT.SALES.CUSTLIS: >>set schema samdbcat.definition_schema_version_1200; --- SQL operation complete.
Displaying All Privileges for a Column Querying SQL/MX Metadata Displaying All Privileges for a Column This example displays all privileges for the column EMPNUM in the table SAMDBCAT.PERSNL.EMPLOYEE: >>set schema samdbcat.definition_schema_version_1200; --- SQL operation complete.
Querying SQL/MX Metadata Displaying Object Integrity and Consistency Displaying Object Integrity and Consistency The mxtool VERIFY command detects and reports inconsistencies between the information contained in the metadata, the resource forks, and the file labels. It does not verify data integrity. This command is often used together with the mxtool INFO command, which displays various aspects of a database.
Querying SQL/MX Metadata Displaying the Schema Version Displaying the Schema Version This example displays the version number of a specified schema: >> select s.schema_version from nonstop_sqlmx_node.system_schema.schemata s, nonstop_sqlmx_node.system_schema.catsys c where c.cat_name = 'catalog-name' and c.cat_uid = s.cat_uid and s.schema_name = 'schema-name'; Displaying the System Schema Version This example displays the version number of the system schema: >> select s.
Querying SQL/MX Metadata Displaying the Object Feature Version (OFV) and s.schema_uid = o.schema_uid and o.object_name = 'object-name' and o.
Querying SQL/MX Metadata Displaying the Object Feature Version (OFV) HP NonStop SQL/MX Installation and Management Guide—540436-001 8- 66
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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-17.
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.
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.
Adding, Altering, and Dropping SQL/MX Database Objects Adding SQL/MX Tables 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.
Adding, Altering, and Dropping SQL/MX Database Objects Adding Triggers 9. 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. 10. Perform a TMF online dump. For a partitioned table, perform an online dump of each partition. For more information and examples of adding a table, see Creating SQL/MX Tables on page 7-11.
Adding, Altering, and Dropping SQL/MX Database Objects Adding Views For more information and examples of creating triggers, see Creating Triggers on page 7-41. Adding Views To add a view, use the CREATE VIEW statement. Steps for Adding 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 names of any tables whose columns you want to include in the view.
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.
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.
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.
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.
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.
Adding, Altering, and Dropping SQL/MX Database Objects Altering SQL/MX Tables 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.
Adding, Altering, and Dropping SQL/MX Database Objects • • • • Altering SQL/MX Tables ALLOCATE/DEALLOCATE AUDITCOMPRESS CLEARONPURGE MAXEXTENTS 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-25.) Steps for Altering Table File Attributes 1. Start an MXCI session.
Adding, Altering, and Dropping SQL/MX Database Objects Altering Triggers Altering Table Privileges To alter table privileges, use the GRANT and REVOKE statements. For more information, see the SQL/MX Reference Manual. 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.
Adding, Altering, and Dropping SQL/MX Database Objects Dropping Objects From an SQL/MX Database 3. Enter the GRANT or REVOKE statements. For more information and examples of altering views, see the SQL/MX Reference Manual. 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Reorganizing SQL/MX Tables and Maintaining Data Choosing a Reorganization Method 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. If you do not want NonStop SQL/MX to save DDL when you drop table, you can turn this feature off by setting the SAVE_DROPPED_TABLE_DDL control query to “OFF.” For more information, see the SQL/MX Reference Manual.
Reorganizing SQL/MX Tables and Maintaining Data • • Reorganizing Table Files With FUP RELOAD During the operation, performance can be degraded if the data blocks have become physically disorganized. Disorganization can occur as a result of INSERT, UPDATE, or DELETE operations performed over a period of time. You can, however, control the amount of degradation by using the command’s RATE option. The higher the rate, the faster the reload occurs, but the more performance degrades.
Reorganizing SQL/MX Tables and Maintaining Data Determining the Status of a Reorganization With FUP STATUS This command initiates a FUP RELOAD operation for the table named CUSTOMER. The SLACK option sets a minimum amount of free space in the blocks. FUP - RELOAD $DATA08.ZSDAWVHH.J8MGRZ00, RATE 30, SLACK 50 Determining the Status of a Reorganization With FUP STATUS The FUP STATUS command reports the status of a FUP RELOAD operation.
Reorganizing SQL/MX Tables and Maintaining Data DDL Lock Considerations for MODIFY, import, POPULATE INDEX, DUP, and PURGEDATA When you want to restart the reorganization of the table, reissue the FUP RELOAD command. In this case, you must omit the NEW option so that FUP restarts a previously suspended reload operation. If you want to change the RATE or SLACK option, you can do so in the restart command.
Reorganizing SQL/MX Tables and Maintaining Data • • • • Consequences of a Failed Utility Operation A DDL operation that changes the definition of the object (for example, alter table, add column, or create index) A DDL operation on a dependent object (for example, an index, constraint, or trigger) A utility operation that changes the definition of the object A utility operation on a dependent object These SQL/MX utilities use DDL locks: • • • • • MODIFY.
Reorganizing SQL/MX Tables and Maintaining Data Using a Query to Obtain DDL Lock and Process ID Information When the cause of the failure is fixed, you should perform a RECOVER command—or in the case of some failed MODIFY operations, a FUP RELOAD command—to cancel or resume the utility operation. Using a Query to Obtain DDL Lock and Process ID Information You can issue a query to read the metadata and generate a list of DDL locks on a given object or set of objects.
Reorganizing SQL/MX Tables and Maintaining Data Performing Recovery on Failed Utility Operations You can also use SHOWDDL to display information about DDL locks. An example SHOWDDL output would include: -- WARNING: A DDL lock exists on the following object, SHOWDDL may be inaccurate -CREATE TABLE CAT.SCH.
Reorganizing SQL/MX Tables and Maintaining Data Structure Changing Operations That Can Run With Active or Inactive DDL Locks Present If you attempt to drop or alter an object that a utility operation is using, error 1250 is returned: 1250 Operation cannot be performed on object object-name because a utility operation (operation-type) associated with DDL_LOCK lock-name is currently running. For more information about the error message, see the SQL/MX Messages Manual.
Reorganizing SQL/MX Tables and Maintaining Data • • • Using MODIFY to Manage Table and Index Partitions AUDITCOMPRESS CLEARONPURGE MAXEXTENTS DDL locks on indexes have their own set of considerations. ALTER operations are often allowed on the parent table even if the index has a DDL lock because the change to the parent table does not affect the structure of the dependent index. For more information about DDL locks, see the SQL/MX Reference Manual.
Reorganizing SQL/MX Tables and Maintaining Data MODIFY and TMF Online partition management is not supported for these types of tables and indexes: • • • hash-partitioned tables and indexes System-clustered tables range-partitioned tables and indexes where the partitioning key is not a prefix of the clustering key MODIFY and TMF Many partition management requests require movement of massive amounts of data.
Reorganizing SQL/MX Tables and Maintaining Data MODIFY and Table Reloading on target rows. Avoid choosing a default value greater than 500, because DP2 escalates locking from selected rows to the entire partition if the partition has more than 511 row and file locks. MODIFY and Table Reloading Some MODIFY command options start the online reload process ORSERV, which runs in the background.
Reorganizing SQL/MX Tables and Maintaining Data Recovering a Failed MODIFY Request and Resetting Flags For more information about these and other FUP RELOAD parameters, see the File Utility Program (FUP) Reference Manual. You can monitor the progress of the ORSERV process with this TACL command: FUP STATUS physical-file-name You can determine the Guardian (physical) file name by using the MXCI SHOWLABEL command. For example: SHOWLABEL CAT.SCH.
Recovering a Failed MODIFY Request and Resetting Flags Reorganizing SQL/MX Tables and Maintaining Data affected table or index as shown. The query returns information you can use to recover a failed MODIFY request. >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select substring (o1.object_name from 1 for 15) as lock_name, l.operation as util, l.status, substring (l.process_uid from 1 for 25) as process ID from objects o, objects o1, ddl_locks l where o.
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Range-Partitioned Tables and Indexes process to reset the UNRECLAIMEDSPACE or INCOMPLETE SQLDDL OPERATION flag, wait until the reload operation completes. ° If the STATUS process-name command returns no process information, the returned lock information is for an inactive DDL lock. Run the RECOVER command on the table or index associated with the MODIFY request to cancel or resume the operation.
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Range-Partitioned Tables and Indexes add first key ('Chicago') location $DATA02, add first key ('New York') location $DATA03 ); Also suppose that the table ORDERSR contains orders with these locations: • • • • Atlanta Chicago Cincinnati New York Example of Adding a New Range Partition You expect several thousand orders for ‘Seattle’ to arrive shortly.
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Range-Partitioned Tables and Indexes from ‘Chicago’ up to—but not including—‘Cincinnati’ to the $DATA00 partition. Use this MODIFY TABLE command: MODIFY TABLE cat2.sch2.ordersr move where key = first key upto key = value ('Cincinnati') to previous partition; Example of Merging Two Adjacent Range Partitions Into One Range Partition You have sold your New York subsidiary to another company.
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Range-Partitioned Tables and Indexes ADD FIRST KEY TIMESTAMP '2004-01-01 00:00:00.000000' LOCATION $data02, ADD FIRST KEY TIMESTAMP '2004-07-01 00:00:00.
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Hash-Partitioned Tables and Indexes Example of Moving an Existing Partition to a New Location You suddenly need to use $DATA04 for other purposes. Use this MODIFY command to move the index partition on $DATA04 to another volume: MODIFY INDEX cat2.sch2.ordersr_via_ordertime move where key = value (timestamp '2004-05-01 00:00:00.
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Hash-Partitioned Tables and Indexes In response to this command, a portion of the data in each of the original three partitions is moved automatically to the fourth partition, rebalancing the data evenly across the four partitions. Example of Dropping an Existing Hash Partition and Rebalancing Data Orders suddenly decline, and you decide to free up some of the disk resources used by the table ORDERSH.
Reorganizing SQL/MX Tables and Maintaining Data Managing System-Clustered Tables and Indexes Example of Adding a New Hash Partition and Rebalancing Data The index ORDERSR_VIA_ORDERTIME contains three partitions. When business orders increase, you decide to rebalance the index over four partitions. Use this MODIFY command to add the fourth partition: MODIFY INDEX cat2.sch2.
Reorganizing SQL/MX Tables and Maintaining Data Using import to Load SQL/MX Tables Example of Moving an Entire System-Clustered Partition to a New Location You suddenly need to use $DATA09 for other purposes. You decide to move the system-clustered table ORDERSSC to a different volume. Use this MODIFY TABLE command to move the table to the volume $DATA03: MODIFY TABLE cat4.sch4.
Reorganizing SQL/MX Tables and Maintaining Data Summary of import Options Table 10-1. import Command Options 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). import terminates when the number of rows specified has been inserted or when it reaches the end of the input file.
Reorganizing SQL/MX Tables and Maintaining Data Other import Features 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.
Reorganizing SQL/MX Tables and Maintaining Data Recommended Practices for Improving import Performance To improve the performance of the fasting load technique, import turns off the audit attribute for the entire table at the start of the operation and turns it back on when the operation ends. If another import operation is attempted on the same table while an import operation with the fast loading technique is being performed, the second operation fails with a concurrent access error.
Reorganizing SQL/MX Tables and Maintaining Data • • Recommended Practices for Improving import Performance Verify that all data being imported is contained in a single flat input file. OSS flat files cannot exceed 2 GB. If the input file exceeds 2 GB, consider using DataLoader/MX, which supports larger Guardian files. For information about using DataLoader/MX with import to load and maintain SQL/MP and SQL/MX databases, see the DataLoader/MX Reference Manual. For an empty range-partitioned table: 1.
Reorganizing SQL/MX Tables and Maintaining Data Recommended Practices for Improving import Performance Importing Data Into Multiple Range Partitions Before you import data into a table with multiple range partitions, first identify all affected partitions and where they are located in the table. When you use import to load multiple partitions, running multiple instances of import is usually faster than running a single instance.
Reorganizing SQL/MX Tables and Maintaining Data Recommended Practices for Improving import Performance performance. For more information, see Managing Indexes to Improve import Performance on page 10-28. • RI constraints require a lookup of each row of the referenced table to confirm that the row exists, which imposes an extra I/O for each row being inserted. The extra I/O can impose a significant performance penalty when running import on a table with one or more RI constraints.
Reorganizing SQL/MX Tables and Maintaining Data Using import to Load Partitions Using import to Load Partitions • For range-partitioned tables, you can load the data from a single input file or several input files into a table’s range partitions. The data in the input file or files must first be ordered by the table’s range partitioning key.
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using import to Load an SQL/MX Table You might specify the three import commands as follows: /usr/bin:import corpcat.persnl.employee -I empfile -C 3000 -T 500 /usr/bin:import corpcat.persnl.employee -I empfile -C 2000 -F 3000 -T 500 /usr/bin:import corpcat.persnl.
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using import to Load an SQL/MX Table 7,"Test String 7","111-222-3333","222-333-4444" 8,"Test String 8","111-222-3333","222-333-4444" Format File Describing the Data Create a format file, FORMFILE1, which consists of [DATE FORMAT], [COLUMN FORMAT], and [DELIMITED FORMAT] sections: [DATE FORMAT] DateOrder=MDY DateDelimiter=/ TimeDelimiter=: FourDigitYear=Y DecimalSymbol=.
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using import to Load an SQL/MX Table Format File Describing the Data Create a format file, FORMFILE2, which consists of [DATE FORMAT], [COLUMN FORMAT], and [FIXED WIDTH FORMAT] sections: [DATE FORMAT] DateOrder=MDY DateDelimiter=/ TimeDelimiter=: FourDigitYear=Y DecimalSymbol=. [COLUMN FORMAT] col=id,N col=company,N col=phone,N col=fax,N [FIXED WIDTH FORMAT] col=id,1,11 col=company,14,16 col=phone,33,12 col=fax,48,12 RecordLength=61 Note.
Reorganizing SQL/MX Tables and Maintaining Data Using import to Append Data to Tables or Partitions Data to Input The input file, COINPUT_FX2, contains records like this: 0123456789012345 ABCDEFGHIJKLMNPQ 0123456789012345 Note that there are three hidden spaces and a newline character at the end of each line.
Reorganizing SQL/MX Tables and Maintaining Data Using import to Append Data to Tables or Partitions Guidelines for Appending Data to Tables • • • • • • Use the import command to add data to a table or partition without purging existing data. Use import to add data to SQL/MX tables. You cannot use import to add data to ASCII files or any other files other than SQL/MX tables. import can use ASCII source files or input files from DataLoader/MX.
Reorganizing SQL/MX Tables and Maintaining Data Using DUP to Copy Tables Into Tables You can use the DataLoader/MX product to help implement these tasks. For example, use DataLoader/MX to arrange to have the input data delivered to the correct target partitions. For more information about the DataLoader/MX product, see the DataLoader/MX Reference Manual. Example of Using import to Append Data Suppose that you have a history table containing 80 weeks of data.
Reorganizing SQL/MX Tables and Maintaining Data • • • Examples of Using DUP to Copy Tables The catalog that defines the source table or tables can exist on a remote node and be referenced by the current DUP operation if the remote node is visible to the local node. The target table can also exist in a catalog and structure that reside on a visible remote node.
Reorganizing SQL/MX Tables and Maintaining Data Using PURGEDATA to Delete Data From Tables This example copies the partitions of the source table on $data1 and $data2 to the partitions of the target table on $data2 and $data3 respectively. If there is no PART clause for a specific volume and source partitions exist on that volume, the target partitions are created on the same volume as the source partitions. DUP mycat.myschema1.mytable TO *.myschema2.
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using PURGEDATA to Delete Table Data an UPDATE STATISTICS statement to record current statistics for the table in the catalog. If the statistics are incorrect, the SQL compiler might not select the best access path for performance. • You cannot use the PURGEDATA command on a view or in an SQL program. PURGEDATA returns errors if: • • • • You specify a list of partition for a hash-partitioned table.
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.
Managing Database Applications Recovery for Read-Only Queries resolved by the takeover of the backup CPU or the prompt restoration of the network connection. 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.
Managing Database Applications SQL/MX and SQL/MP Differences in Recovery Action for Read-Only Queries It is not necessary to start a new TMF transaction. The application can incorporate special retry logic, such as saving the key predicate of the last row returned for use in 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.
Managing Database Applications • Reasons Not to Move a Program With Compiled Modules Moving a program without 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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
Managing Database Applications Securing User Modules To maintain compatibility with C, C++, and COBOL applications created in earlier releases, the SQL/MX Release 2.x SQL compiler produces a globally placed module unless instructed to produce a locally placed module. 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.
Managing Database Applications • • Securing User Modules Does not attempt to change ownership of the USERMODULES directory. 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.
Securing User Modules Managing Database Applications Securing Locally Placed Modules You secure locally placed modules from the local directory in which they reside. The objective should be to assign the responsibility and accountability for safeguarding the integrity of each locally placed module to its own creator and owner. 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.
Managing Database Applications Checking Module Dependencies With DISPLAY USE OF 7. Alternatively, the SUPER.SUPER user can make sure that all modules in the local directory can be written to only by their respective owners by entering: chmod 0644 /usr/local-directory/* 8.
Managing Database Applications Object: Object: Object: Object: 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.T03 CAT.SCH.T04 Module: Object: Object: Object: Object: CAT.SCH.TESTMEM CAT.SCH.T15 CAT.SCH.T16 CAT.SCH.T17 CAT.SCH.
Managing Database Applications Grouping Applications and Modules to Run Multiple DISPLAY USE OF Operations You must specify the fully qualified OSS directory path of a locally placed module. Otherwise, an error occurs. The local directory is searched for modules matching the 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.
Grouping Applications and Modules to Run Multiple DISPLAY USE OF Operations Managing Database Applications ----- 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.
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 '*.*.
Managing Database Applications Removing Modules 11. Verify how the local modules are generated: /user/smith/tmp/tmp/DUO: ls -l *.*.INVENTORYAPP^*^^ -rw-rw---- 1 JAVAQA.MODMAN JAVAQA 48293 Nov 29 14:00 T01CAT.T01SCH.INVENTORYAPP^T01MOD^^ -rw-rw---- 1 JAVAQA.MODMAN JAVAQA 48293 Nov 29 14:00 T01CAT.T01SCH.INVENTORYAPP^T02MOD^^ -rw-rw---- 1 JAVAQA.MODMAN JAVAQA 48293 Nov 29 14:00 T01CAT.T01SCH.INVENTORYAPP^T03MOD^^ /user/smith/tmp/tmp/DUO: ls -l *.*.SHIPMENTAPP^*^^ -rw-rw---- 1 JAVAQA.
Managing Database Applications Converting Globally Placed Modules to Locally Placed Modules 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.globmod1 from the /usr/tandem/sqlmx/USERMODULES directory, enter: rm /usr/tandem/sqlmx/USERMODULES/CAT.SCH.
Managing Database Applications • Converting Globally Placed Modules to Locally Placed Modules Converting Applications in a Production Environment on page 11-26 Converting Applications in a Development Environment To build the application in a development environment as if it were a new version of the application: 1. Before you create new locally placed modules, set the MXCMP_PLACES_LOCAL_MODULES attribute ON in the SYSTEM_DEFAULTS table.
Managing Database Applications Managing Module Files and Their Applications During Fallback From SQL/MX Release 2.x c. Note the names of the module files that are generated in the temporary directory. d. Delete the contents of the temporary directory. If you are not using embedded module definitions, you can find the name of the modules in the module statement of the Module Definition file (MDF). 2.
Managing Database Applications Backing Up and Restoring Programs HP NonStop SQL/MX Installation and Management Guide—540436-001 11 -28
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.
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.
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.
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).
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.
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.
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.
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.
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/.
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.
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.
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.
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.
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.
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.
Recovering Tables Performing Recovery Operations 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.
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.
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.
Recovering Tables Performing Recovery Operations >>> >>> $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.
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.
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.
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.
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.
Performing Recovery Operations Restoring Indexes 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.
Performing Recovery Operations Repairing Damaged SQL/MX Metadata and Objects Repairing Damaged SQL/MX Metadata and Objects Each SQL/MX file consists of two physical Guardian files: the data fork and the resource fork. Normally, when a data fork is dropped, DP2 automatically drops the corresponding resource fork. In some cases, however, either a resource fork or a data fork can become an orphan, requiring your intervention and repair.
Performing Recovery Operations Strategies for Repairing Damaged Metadata and Objects For information about using FIXUP, see Using FIXUP to Correct Problem Data and Objects on page 12-32. GOAWAY Use the mxtool GOAWAY command to remove SQL/MX format file labels for both the resource and data forks associated with a Guardian file. Use GOAWAY to remove objects that are broken or cannot be dropped by normal DDL operations. GOAWAY does not remove corresponding metadata entries.
Performing Recovery Operations Identifying Problem Data and Objects Identifying Problem Data and Objects NonStop SQL/MX keeps information about object structures in metadata, resource forks, and DP2 labels. DP2 labels consist of file structure information and security settings. Resource forks contain specific SQL/MX information that includes, among other things, the ANSI name, partition maps, row and key information, and system metadata location.
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 Protection FIle security setting, specified as *sql. NonStop SQL/MX supports ANSI GRANT and REVOKE security and does not use Guardian security of the form RWEP. The Guardian protection for all SQL/MX format objects is set to “AAAA.” Do not use this field to determine the security attributes for the object.
Performing Recovery Operations Identifying Problem Data and Objects Table 12-2. Run-time metadata Stored in the Resource Fork Run-time metadata Contents ANSI name A three-part name of the form catalog.schema.object, where each part can be up to 128 characters long. ANSI namespace Indicates the space the ANSI name belongs to. In NonStop SQL/MX, tables and indexes have separate namespaces; indexes and tables in the same schema can have the same ANSI names.
Performing Recovery Operations Identifying Problem Data and Objects Using SHOWLABEL to Retrieve Run-Time Metadata You can also use the SHOWLABEL command to retrieve run-time metadata from both the labels and the resource fork of a Guardian file. For command syntax and example outputs, see the SQL/MX Reference Manual.
Performing Recovery Operations Identifying Problem Data and Objects This VERIFY feature compares information saved in the DP2 file label with similar information stored in metadata and returns inconsistencies.
Performing Recovery Operations Using FIXUP to Correct Problem Data and Objects you can use the MXCI SHOWLABEL command to get more details for a specific Guardian file. See the SQL/MX Reference Manual for command syntax and example outputs. Guardian files have a set of DP2 labels attached to them that contain basic information, such as file type, extent sizes, security, and timestamps. A separate entity, a resource fork, is attached to an SQL/MX file.
Performing Recovery Operations Using FIXUP to Correct Problem Data and Objects In many cases, you know which files are actually corrupt and which are consistent. Normally, it is better to allow the TMF file recovery method to recover all the files and determine which are corrupt and which are not. See Recovering Files on page 12-2. If you are able to determine that a file is not corrupt, you can simply reset the BROKEN flag that indicates to the system that the file is corrupt.
Performing Recovery Operations Using FIXUP to Correct Problem Data and Objects Changing the Broken Bit Like the broken flag, the broken bit is set by DP2 when it detects that something is wrong with the partition. After the problem with the partition is fixed, the broken bit needs to be turned off to access the data. The ability to turn off the broken bit is equivalent to using the ALTER TABLE … RESETBROKEN command in SQL/MP.
Performing Recovery Operations Examples of Using FIXUP Changing Catalog, Schema, and Object UIDs Every time a partition for a table or index is created, its related catalog, schema, and object UID is stored in the resource fork.
Performing Recovery Operations Using GOAWAY to Delete Damaged Objects Partition (add first key (1000) location \local.$data02 name i1part2, Add first key (4000) location \remove.$data02 name i1part3); 3. Create three partitions with these names: • • • \LOCAL.$DATA02.ZSDJFKEO.SJDIFL00 \LOCAL.$DATA04.ZSDJFKEO.SJDIFM00 \REMOTE.$DATA04.ZSDFHKEO.SJDIFN00 CREATE INDEX index2 (col3) on table1 Location \local $data02 name i2part1 Hash partition (add location \local.$data04 name i2part2, Add location \remote.
Performing Recovery Operations Recovering From SCF Commands mxtool GOAWAY is an OSS command-line utility run from mxtool that removes SQL/MX format file labels for both the resource and the data forks associated with a Guardian file. Note. mxtool GOAWAY does not remove corresponding metadata entries and does not support ANSI names. Use the MXCI SHOWLABEL command to identify the Guardian file names that correspond to ANSI object names.
Performing Recovery Operations ALTER DISK, ALTNAME and ALTER DISK, VOLNAME The only exception to the preceding rule is if the entire database has been consistently brought down as a unit. For example, suppose that you use ABORT DISK to bring down all the backup volumes of the mirrored pairs containing SQL/MX objects in a consistent state. The other mirrored set continues the active database, but the inactive mirrors also contain a set of consistent SQL/MX objects.
Performing Recovery Operations ALTER DISK, LABEL ALTER DISK, LABEL Caution. ALTER DISK, LABEL use is extremely dangerous because it can corrupt a database. Before using an ALTER DISK command, perform a STOPOPENS DISK and a CONTROL DISK, REFRESH on the volume to ensure valid file labels. Then perform a STOP DISK to make the volume inactive. Do not use this command on disks containing SQL/MX objects, with a few exceptions.
Performing Recovery Operations START DISK and STOP DISK After the disk drive is stopped, you can bring the disk up as a phantom drive (without a name), label the disk with another volume name, or reuse the disk in any other way. This operation is often done on nodes where nonmirrored disk space is needed for a short time. Later, you can return the previously stopped disk drive to its original mirrored state by performing a START DISK.
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.
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.
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 2.x, users 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.
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.
Managing an SQL/MX Distributed Database SQL/MX Distributed Database Features When you register a catalog from the local node to remote nodes, you ensure that the catalog’s database objects can be successfully resolved to corresponding underlying Guardian file names when accessed by their ANSI names on remote nodes.
Managing an SQL/MX Distributed Database Naming Network Nodes nodes can execute DML and DDL statements and utility commands on the objects just as the original node can. 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.
Managing an SQL/MX Distributed Database Naming SQL/MX Database Objects NonStop SQL/MX resolves these ANSI names to the corresponding underlying Guardian file names. Note. For information about naming SQL/MP database objects, including guidelines on using DEFINEs for network objects names, see the SQL/MP Installation and Management Guide. ANSI Names SQL/MX database objects have three-part, location-independent ANSI names of the form catalog.schema.object.
Managing an SQL/MX Distributed Database Using Catalog References in an SQL/MX Distributed Database Using Catalog References in an SQL/MX Distributed Database This subsection describes how to manage catalog references in an SQL/MX Release 2.x network-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.
Managing an SQL/MX Distributed Database Using Catalog References in an SQL/MX Distributed Database Catalog Reference Guidelines for SQL/MX Release 2.x • • • • • The node where a catalog is created contains the object metadata for all database objects in that catalog and is the only automatic catalog reference for that catalog. The object metadata for a catalog, including schema metadata and metadata definitions, cannot be replicated from the automatic reference node to other nodes.
Managing an SQL/MX Distributed Database • • Using Catalog References in an SQL/MX Distributed Database The CATSYS table on \N1 contains one row for the catalog CAT1, including these entries in the CAT_NAME and REPLICATION_RULE columns.
Managing Network Security Managing an SQL/MX Distributed Database • The CAT_REFERENCES table on \N2 and \N3 contains two rows for the catalog CAT1, one for \N1 and another for the local node.
Managing an SQL/MX Distributed Database Maintaining Local Autonomy in a Network for SQL/MX Release 2.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 2.
Managing an SQL/MX Distributed Database • Maintaining Local Autonomy in a Network for SQL/MX Release 2.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.
Managing an SQL/MX Distributed Database Maintaining Local Autonomy in a Network for SQL/MX Release 2.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.
Maintaining Local Autonomy in a Network for SQL/MX Release 2.x Managing an SQL/MX Distributed Database 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.
Maintaining Local Autonomy in a Network for SQL/MX Release 2.x Managing an SQL/MX Distributed Database 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.
Maintaining Local Autonomy in a Network for SQL/MX Release 2.x Managing an SQL/MX Distributed Database 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.
Managing an SQL/MX Distributed Database Maintaining Local Autonomy in a Network for SQL/MX Release 2.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.
Creating a Distributed SQL/MX Database Managing an SQL/MX Distributed 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.
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.
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.
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.
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.
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.
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.
Managing an SQL/MX Distributed Database Managing Mixed Versions of NonStop SQL/MX as these are within the range of the oldest support plan version (800). Note these other restrictions: • • • 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 2.x nodes and SQL/MX Release 1.8 nodes.
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.
SHOWLABEL Command Measuring Performance 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.
SET STATISTICS and DISPLAY STATISTICS Commands Measuring Performance Max Extents: 160 Extents Allocated: 1 EOF: 12288 Index Levels: 2 Record Expression Label Length: 14280 Security Label Length: 120 Key Columns: 0 ASC Partitioning Scheme: RP Partition Array - 0 partition[s] IndexMap Array - 2 index[es] Index[0]: \KINGPIN.$TX0101.ZSDFM5XP.WR9ZNQ00 Index columns: 2 ASC , 1 ASC , 0 ASC Index[1]: \KINGPIN.$TX0101.ZSDFM5XP.
Measure Performance Measurement Tool Measuring Performance 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.
Measure Performance Measurement Tool Measuring Performance 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.
Measuring Performance Statistics and Reports for NonStop SQL/MX 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. SQLPROC Statistics The SQLPROC report provides information about specific statistics concerning recompilations, NEWPROCESS calls, and opens of files or processes.
Measuring Performance • • • • • • • • • • Statistics and Reports for NonStop SQL/MX 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. You can use the FILE report along with other reports on a specific volume or subvolume. NonStop SQL/MX does not collect statistics on timeouts or file-busy-time (the time spent waiting on file I/O activities).
Measuring Performance SQL/MX Measurement Models DISCOPEN to determine if the partitions in a database are being accessed evenly by the processes in an application. SQL/MX Measurement Models Measure always updates counters, so starting a measurement adds only the overhead of writing the counters out to disk files. Still, when using the Measure product, you must determine whether the overhead for gathering Measure statistics is worth the information provided by the reports.
Measuring Performance SQL/MX Measurement Models After a program begins running, startup costs have already been incurred. The costs associated with processing the statements are stored in the SQLSTMT entity. The first time a statement in a procedure executes, overhead is added for setting up the counters for the procedure. You can use these SQLSTMT counters to analyze a running process: • • CALLS stores the number of times the SQL statement was executed.
Measuring Performance SQL/MX Measurement Models examine the cause. You might consider a finer locking granularity (for example, row locks instead of generic locks or table locks) or redesigning the database. • ESCALATIONS stores the number of times a record lock was escalated to a file (table) lock. Since most, if not all, attempts at lock escalation fail, the number should usually be a 1. If the number is greater than 1, you should consider using a table lock for the program.
15 Enhancing SQLMX 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.
Enhancing SQLMX 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.
Enhancing SQLMX 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.
Enhancing SQLMX 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.
Enhancing SQLMX 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.
Enhancing SQLMX 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.
Enhancing SQLMX 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.
Enhancing SQLMX Database Performance Creating More Accurate Histogram Tables With UPDATE STATISTICS Creating More Accurate Histogram Tables With UPDATE STATISTICS SQL/MX Release 2.1.1 introduced a new internal algorithm for estimating the UEC of a column in a full table from a sample of rows in the table. This algorithm achieves acceptable levels of both histogram accuracy and UPDATE STATISTICS performance using relatively small table row samples.
Enhancing SQLMX Database Performance How DP2 Manages and Reuses Query Plan Fragments generally require more SQL/MX buffer space than those running DSS transactions with relatively few opens. Both SQL/MX buffer space and DP2 data cache can be taken by the memory manager when they are not in use. When there is no approaching memory pressure, DP2 issues long-term locks on both DP2 data cache and SQL/MX buffer pages, thereby preventing those pages from being taken.
Enhancing SQLMX Database Performance • • • • • Reduction of Plan Fragment Size for Unique Queries Too many client processes are connected to and communicating with the DP2 process, sending more queries than DP2 or its buffers can accommodate and retain for reuse. Too many tables are partitioned on the affected disk, resulting in too many plan fragments. Usually, one new plan fragment is created and stored in the SQL/MX buffer with each table access.
Enhancing SQLMX Database Performance Using SCF STATS DISK to Monitor SQL/MX Statistics and Reuse Failures If you are running SQL/MX Release 2.0 or SQL/MX Release 2.1 and want to leverage query plan size reduction, you must upgrade your system to SQL/MX Release 2.2 and then SQL compile your SQL/MX applications. For more information, see the SQL/MX Database and Application Migration Guide.
Enhancing SQLMX Database Performance Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute Failed ID under Reuse identifies the number of plan fragments that are present in the free space tables but whose IDs reported that they could not be reused. Together, Failed FST and Failed ID indicate the current number of unsuccessful attempts that have been made to reuse plan fragments, whether missing or unusable.
Enhancing SQLMX Database Performance • • • Strategies for Reducing Reuse Failures You cannot specify a value that is too small. For example, if you specify 0 (zero), the default value of 128 MB is used. However, if you specify a buffer size that is too large, SCF rejects the request. DP2 automatically rounds any positive value you specify to the modulo16 value. There should be sufficient physical memory in both the primary and backup DP2 CPUs to accommodate the altered buffer configuration.
Enhancing SQLMX Database Performance Managing DP2 Data Cache Memory Size To preserve proper data cache function, try not to increase the SQL/MX buffer size above 512 MB. If high reuse failure rates persist, consider implementing the other strategies described in this subsection. For more information about changing the size of the buffer, see Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute on page 15-12.
Enhancing SQLMX Database Performance Maximizing Disk Process Prefetch Capabilities DP2 data cache size is controlled through the Subsystem Control Facility (SCF). For more information on using SCF to set cache size, see the SCF Reference Manual for the Storage Subsystem. Maximizing Disk Process Prefetch Capabilities NonStop SQL/MX can enhance performance by reading blocks of data into the DP2 data cache asynchronously before they are needed.
Enhancing SQLMX Database Performance Optimizing Index Use SQL/MX processes can consume large amounts of addressable memory space as a result of: • • • Parallelism among a large number of ESPs The execution of plans that use sort and grouping operators The optimization of complex plans The heavy consumption of addressable memory space by SQL/MX processes can lead to insufficient swap file space.
Enhancing SQLMX Database Performance Maximizing Parallel Index Maintenance For more information about determining when to use indexes, see Section 4, Understanding and Planning SQL/MX Tables. Maximizing Parallel Index Maintenance Indexes are automatically updated whenever you insert a row into the underlying table or whenever you change a column of the index. You can update multiple indexes in parallel.
Enhancing SQLMX Database Performance Checking Data Integrity 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. Also, FUP RELOAD is the only method available for inserting or altering slack space into SQL/MX files.
Enhancing SQLMX Database Performance • Creating Logical Views of Data 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. ° Constraints simplify the change process to a simple, online process. If you add one constraint, the system immediately applies the constraint to all subsequent transactions.
Enhancing SQLMX Database Performance Avoiding Automatic Recompilations 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. Automatic recompilation makes it possible for application programs to continue to perform when invalidating events occur or when access paths are unavailable.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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.
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
B Index Applications (continued) 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-33 Audit trails 12-2 AUDITCOMPRESS attribute altering index attributes 9-20 altering table attributes 9-24
C Index Blocks, key-sequenced file 4-6 Broken bit, changing 12-34 BROKEN flag 12-32 Broken partitions, recovering 12-33 Buffer size, effect on query performance 15-14 C C preprocessor 3-19 Cache size 15-14 CACHE_HISTOGRAMS 6-7 CACHE_HISTOGRAMS_ REFRESH_INTERVAL 6-3, 6-7 CALLS counter, Measure 14-9 CASCADE option 12-11 CAST function 7-24 Catalogs adding 9-4 CATALOGS table, displaying contents of 8-36 creating 7-7 dropping 9-28 querying metadata about 8-23/8-26 recovering 12-6 SCF ALTER DISK issues 12-38 S
D Index CREATE INDEX statement adding an index 9-8 description of 4-17 CREATE TABLE statement 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-19 D Damaged objects, deleting 12-36 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 correc
D Index DDL locks (continued) operations that can run with DDL locks present 10-9 performing recovery on failed utility operations 10-8 using a query to obtain DDL lock and process ID information 10-7 DDL operation limits 15-6 DDL statements 1-13 DDL_DEFAULT_LOCATIONS description of 6-6 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-12 description of 6-1 new for Release 2.
E Index Distributed database (continued) visibility rules 13-5 ways to distribute objects 13-2 Distributed objects, managing 13-1 Distributing programs across nodes 11-8 DLLs ZCLIDLL 3-6 ZCLIPDLL 3-6 DML statements 1-13 DOUBLE PRECISION data type 7-26 DP2 file labels 8-2 DP2_CACHE_4096_BLOCKS 6-3, 6-7 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
G Index Files See also Tables characteristics 4-3 command summary 1-13 consistent, recovering 12-32 EDIT 7-22 EMS events 3-6 executable 3-9 header 3-15 interface description 3-15 messages 3-14 recovering 12-2, 12-4 reorganizing 10-2 SPJ product 3-12 SQL/MX 3-8 structure 4-3, 4-5 FILE, Measure entity access costs 14-10 description of 14-5 statistics 14-6 FIRST KEY clause 7-32 FIXUP command, mxtool broken objects 12-32 description of 12-25 examples of 12-20, 12-21, 12-35 timestamps 12-11 FLOAT data type 7-2
J Index Independent products software 2-3 Indexed columns benefits of 4-14 defining 4-16 Indexes adding 9-8 altering 9-20 benefits of 9-9 creating 7-33, 9-8 dropping 9-30 EXPLAIN 9-9 frequently used columns 4-17 improving performance access path 15-16 aggregate functions 4-18 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-40/8-44 restoring 12-24 unique 4-14 Index-only ac
M Index LOCK-WAITS counter, Measure 14-9, 14-10 Logical file structures 4-3 M Maintaining local autonomy 11-10 Maintaining query execution plan validity 11-13 Managing a database 9-1 Managing permissions for files in Guardian space 11-13 Managing permissions for files in OSS space 11-12 MAX function, optimization 4-18 MAXEXTENT attribute, altering index attributes 9-20 MAXEXTENTS attribute, altering table attributes 9-24 MAX_ROWS_LOCKED_FOR_ STABLE_ACCESS 6-3 Measure CALLS counter 14-9 description of 14-
M Index Metadata, querying (continued) DDL locks on an index, displaying 8-42 description of 8-2 detecting database inconsistencies 8-63 index attributes, displaying 8-43 index states, displaying 8-44 indexes in a table, displaying 8-41 indexes in a table, locating 8-40 metadata tables, list of 8-6/8-13 object schema version, displaying 8-64 objects in a schema, displaying 8-29 partition attributes, displaying 8-46 partitions for a table or index, displaying 8-45 privileges for a column, displaying 8-62 p
N Index Modules (continued) displaying dependent objects for one or more specified modules 11-20 displaying in a local directory 11-20 grouping to run multiple DISPLAY USE OF operations 11-21/11-24 managing 11-15 producing globally placed or locally placed 11-15 recompiling 11-14 removing 11-24 securing globally placed 11-16 securing locally placed 11-18 Moving applications to a remote node 11-8 MSCF_ET_REMOTE_MSG_ TRANSFER 6-3 Multiple character sets 1-5 mxcierrors.
O Index Node remote, availability 13-13 Nodes adding 13-24 altering 13-24 migrating for improved query plan performance 11-14 removing 13-24 NonStop Server for Java installing 3-4 NonStop Server for Java 4 1-12 NonStop Technical Library (NTL) 3-17 NonStop Transaction Management Facility (TMF) See TMF NOT NULL clause 7-30, 7-31 NOT_NULL_CONSTRAINT_ DROPPABLE_OPTION alternate setting 6-8 ANSI compliance 6-12 description of 6-4 NSM/web display of metadata 8-1 NTL Web site 3-17 NULL clause 7-30 Null values 7-
Q Index Performance (continued) concurrency 15-2 data definitions 7-24 distributed databases 13-23 improving description of 15-1 with index 4-18 index maintenance 15-17 indexes 4-12, 9-9 measuring 14-1/14-10 MODIFY utility 10-11 OLTP 7-41 partitioning data 7-16, 15-19 partitions 9-11 queries 15-2 statistics 14-1 testing 9-9 tools for analyzing 14-1 VARCHAR columns 7-24 views 15-19 Periodic backup 5-31 Physical file structure 4-3 Platform.
R Index QUERY_CACHE_REQUIRED_ PREFIX_KEYS 6-9 R RDF configuring disk volumes 5-22 creating a backup database with identical file names 5-24 creating an online backup database 5-24 creating backup catalogs 5-22 creating backup schemas 5-22 populating an online backup database 5-25 recovering dropped tables 5-25 retrieving saved DDL 5-26 setting up a backup system 5-21 synchronizing a backup database 5-25 using Backup and Restore to create offline backup databases 5-23 using Guardian names for SQL/MX objec
S Index Remote node availability 13-13 Remote node, running applications on 11-9 Removing modules 11-24 Repairing damaged SQL/MX objects 12-25 REPEAT USE ALLOWED clause 7-3 Reports, Measure statistics 14-6 Requesters 15-18 Resource fork description of 8-2 physical files 12-25 run-time metadata 12-28 RESTORE command 12-23 Rows, ordering using an index 4-14 Running applications on a remote node 11-9 Run-time library files ZCLIDLL 3-12 ZCLIPDLL 3-12 Run-time metadata, retrieving 12-30 S Safeguard, used for
S Index SQLHIST file 3-13, 3-14 SQLPROC entity counters 14-8 description of 14-5 statistics 14-6 SQLSTMT entity counters 14-8/14-10 description of 14-5 statistics 14-6 SQL-NEWPROCESS counter, Measure 14-8 SQL-NEWPROCESS-TIME counter, Measure 14-8 SQL-OBJ-RECOMPILE-TIME counter, Measure 14-8 SQL-STATEMENT-RECOMPILES counter, Measure 14-8 SQL-STATEMENT-RECOMPILES-TIME counter, Measure 14-8 SQL/MP aliases adding 9-14 altering 9-21 dropping 9-33 querying metadata about 8-38 SQL/MX access privileges for databa
S Index Statistics displaying 14-3 execution 14-8 FILE entity 14-6 Measure collection 14-4/14-10 MXCI STATISTICS option 14-3 performance 14-1 PROCESSH entity 14-7 program execution 14-8 reports, Measure 14-6 SQLPROC report 14-6 SQLSTMT report 14-6 updating 12-11 STATUS command 10-4 STOP DISK command, SCF 12-39 Stored procedures adding 9-14 altering 9-22 dropping 9-34 querying metadata about 8-39 restoring 12-24 Super ID, privileges for executing utilities 1-12 SYSKEY 7-15 System catalog, displaying inform
T Index System defaults (continued) QUERY_CACHE_MAX_ VICTIMS 6-8 QUERY_CACHE_REQUIRED_ PREFIX_KEYS 6-9 READONLY_CURSOR 6-12 REF_CONSTRAINT_NO_ ACTION_LIKE_RESTRICT 6-4, 6-10, 6-12 SAVE_DROPPED_TABLE_ DDL 6-4 SAVE_DROPPED_TABLE_DDL 6-10 SCRATCH_DISKS 6-10 SCRATCH_DISKS_ EXCLUDED 6-10 SCRATCH_DISKS_ PREFERRED 6-11 setting and updating 6-1 TEMPORARY_TABLE_ HASH_PARTITIONS 6-11 TEMPORARY_TABLE_HASH_ PARTITIONS 6-5 TIMEOUT 6-11 UDR_JAVA_OPTIONS 6-5, 6-11 UPD_ABORT_ON_ERROR 6-5 VARCHAR_PARAM_DEFAULT_SIZE 6 -5 S
U Index TMF subsystem (continued) guidelines for configuring 5-13 INFO DUMPS, OBEYFORM command 5-9 invalidation of online dumps 5-15 online dumps 5-11 INVALID attribute 12-10 performing 5-9 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 consi
W Index Versioning issues 11-8 Versions executable files 3-15 querying metadata about 8-63/8-65 Views adding 9-17 altering 9-25 creating 7-39, 9-17 defining 4-11 description of 4-11 dropping 9-37 performance issues 15-19 querying metadata about 8-36/8-37 recovering 12-22 restoring 12-24 securing 7-39, 7-40 Visual Query Planner, installing 3-17 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 a
