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 Release 2.3.4 Supported Release Version Updates (RVUs) This publication supports J06.03 and all subsequent J-series RVUs and H06.10 and all subsequent H-series RVUs, until otherwise indicated by its replacement publications.
Document History Part Number Product Version Published 544536-003 NonStop SQL/MX Release 2.3.1 February 2008 544536-004 NonStop SQL/MX Release 2.3.1 August 2008 544536-005 NonStop SQL/MX Release 2.3.2 November 2008 544536-006 NonStop SQL/MX Release 2.3.3 August 2009 544536-007 NonStop SQL/MX Release 2.3.
Legal Notices © Copyright 2010 Hewlett-Packard Development Company L.P. Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice.
HP NonStop SQL/MX Installation and Management Guide Glossary Index Figures Tables Legal Notices What’s New in This Manual xv Manual Information xv New and Changed Information xv About This Manual xix Audience xix Organization xix Related Documentation xx Notation Conventions xxii 1.
1. Introduction to SQL/MX Database Management (continued) Contents 1. Introduction to SQL/MX Database Management (continued) NonStop Server for Java 1-12 JDBC/MX Driver 1-13 Database Management Tasks 1-13 SQL/MX Database Management Tools 1-13 2. Preparing to Install NonStop SQL/MX Hardware Requirements 2-1 Software Requirements 2-2 Verifying Correct Versions of Independent Software Products Installing the DDL Licensing Product (T0394) 2-3 Starting TMF 2-4 2-3 3.
Contents 4. Understanding and Planning SQL/MX Tables (continued) 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.
Contents 8. Querying SQL/MX Metadata (continued) 8.
Contents 8. Querying SQL/MX Metadata (continued) 8. Querying SQL/MX Metadata (continued) Displaying All Privileges for a View 8-58 Displaying All Privileges for a Column 8-59 Displaying Object Integrity and Consistency 8-60 Displaying Version Numbers 8-60 Displaying the NonStop SQL/MX Release Identifier 8-60 Displaying the Schema Version 8-61 Displaying the System Schema Version 8-61 Displaying the Object Schema Version (OSV) 8-61 Displaying the Object Feature Version (OFV) 8-61 9.
Contents 9. Adding, Altering, and Dropping SQL/MX Database Objects (continued) 9. Adding, Altering, and Dropping SQL/MX Database Objects (continued) Dropping Columns from an SQL/MX Table 9-28 Dropping Constraints 9-29 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.
12. Performing Recovery Operations (continued) Contents 12. Performing Recovery Operations (continued) Using FIXUP to Correct Problem Data and Objects 12-32 Examples of Using FIXUP 12-35 Using GOAWAY to Delete Damaged Objects 12-36 Recovering From SCF Commands 12-37 ABORT DISK 12-37 ALTER DISK, ALTNAME and ALTER DISK, VOLNAME 12-38 ALTER DISK, LABEL 12-39 INITIALIZE DISK 12-39 START DISK and STOP DISK 12-39 13.
15. Enhancing SQL/MX Database Performance Contents 15.
Tables Contents Figure 14-1. Measure Entities and Program Structures 14-5 Tables Table 1-1. Table 1-2. Table 1-3. Table 2-1. 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 6-5. Table 9-1. Table 9-2. Table 9-3. Table 9-4. Table 9-5. Table 9-6. Table 10-1. Table 10-2. Table 11-1. Table 12-1. Table 12-2. Table 13-1. Table 13-2. Table 13-3. Table 13-4. Table 13-5. Table 13-6.
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 Release 2.3.4 Supported Release Version Updates (RVUs) This publication supports J06.03 and all subsequent J-series RVUs and H06.10 and all subsequent H-series RVUs, until otherwise indicated by its replacement publications.
What’s New in This Manual • • • • • • • • • • • Changes to the H06.16/J06.05 Manual Added a new SQL/MX operation called fastcopy on page 5-16. Added FASTCOPY utility to the utilities list on page 5-20. Added Rollback of DP2 Large Block on page 5-37. Updated the value of POS_RAISE_ERROR on page 6-4. Added New System Default for SQL/MX Release 2.3.3 on page 6-6. Removed Table 8-1. SQL/MX Metadata Tables from 8-6. Added Figure 8-2, SQL/MX Metadata Tables, on page 8-7.
What’s New in This Manual ° Changes to the 544536-003 Manual System Module Files on page 3-13. Changes to the 544536-003 Manual This manual has been revised to correct the supported release information.
What’s New in This Manual Changes to the 544536-003 Manual HP NonStop SQL/MX Installation and Management Guide—544536-007 xviii
About This Manual This manual describes how to install NonStop SQL/MX Release 2.3.1 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.3.1, and subsequent releases until indicated in a replacement publication.
Related Documentation About This Manual 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. Section 13, Managing an SQL/MX Distributed Database Describes methods for managing an SQL/MX distributed database.
Related Documentation About This Manual Programming Manuals SQL/MX Programming Manual for C and COBOL Describes how to embed SQL/MX statements in ANSI C and COBOL programs. Specialized Guides SQL/MX Installation and Management Guide Describes how to plan for, install, create, and manage an SQL/MX database. Explains how to use installation and management commands and utilities. SQL/MX Query Guide Describes how to understand query execution plans and write optimal queries for an SQL/MX database.
Notation Conventions About This Manual The NSM/web and Visual Query Planner help systems are accessible from their respective applications. You can download the Reference, Messages, and Glossary online help from the $SYSTEM.ZMXHELP subvolume or from the HP NonStop Technical Library (NTL) at docs.hp.com. For more information about downloading online help, see Downloading Online Help on page 3-17.
General Syntax Notation About This Manual italic computer type. Italic computer type letters within text indicate C and Open System Services (OSS) variable items that you supply. Items not enclosed in brackets are required. For example: pathname [ ] Brackets. Brackets enclose optional syntax items. For example: TERM [\system-name.]$terminal-name INT[ERRUPTS] A group of items enclosed in brackets is a list from which you can choose one item or none.
Change Bar Notation About This Manual Quotation marks around a symbol such as a bracket or brace indicate the symbol is a required character that you must type as shown. For example: "[" repetition-constant-list "]" Item Spacing. Spaces shown between items are required unless one of the items is a punctuation symbol such as a parenthesis or a comma. For example: CALL STEPMOM ( process-id ) ; If there is no space between two items, spaces are not permitted.
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.3.1, 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—544536-007 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-4 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 Starting TMF Typically, you develop TMF startup and configuration files as OBEY command files. These files contain the TMF configuration options and parameters that describe the audit trails and the dump process. For information about TMF auditing requirements, configuration guidelines, and considerations for NonStop SQL/MX, see Section 5, Planning Database Security and Recovery and the TMF Planning and Configuration Guide.
Preparing to Install NonStop SQL/MX Starting TMF new TMFSERVE process, named $SV02, runs on Processor 3 on the same node as your TMFCOM, with an execution priority of 150: TMF 63> OPEN SERVER $SV02, CPU 3, PRI 150 The last OPEN SERVER command establishes communication between your TMCOM process and an existing TMFSERVE server on a remote node. The TMFSERVE process is named $SV03 and runs on the node named \DENVER: TMF 64> OPEN SERVER \DENVER.
Preparing to Install NonStop SQL/MX HP NonStop SQL/MX Installation and Management Guide— 544536-007 2 -6 Starting TMF
3 Installing NonStop SQL/MX Summary of Installation Tasks Before you install SQL/MX Release 2.3.1 (SQL/MX Release 2.x), read Using the InstallSqlmx Script on page 3-18 to familiarize yourself with the InstallSqlmx script and the functions it performs during the installation of NonStop SQL/MX. Note. When you install NonStop SQL/MX on your system from the H06.04 or later H-series RVU, you also install NonStop SQL/MP. To initialize NonStop SQL/MP, you must issue the INITIALIZE SQL command from SQLCI.
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 For a detailed description of InstallSqlmx command syntax, command options, and informational messages, see Using the InstallSqlmx Script on page 3-18. The 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.
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-18. This file can be used only if you have purchased and installed the DDL licensing product (T0394) for SQL/MX tables. /usr/tandem/sqlmx/bin mxsqlc The OSS-based C preprocessor to preprocess embedded SQL statements in C/C++ programs.
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.
Verifying Executable File Versions Installing NonStop SQL/MX Messages Files File Name Description Where Installed mxcierrors.cat An OSS file that contains the error message text for all SQL errors. It is implicitly used by all components that are capable of displaying error text (for example, the NT-hosted preprocessors, MXCI, and MXCMP). /usr/tandem/sqlmx/bin MXOMSG The message file for MXCS. $SYSTEM.SYSTEM ZMXSTMPL Contains the compiled event messages for NonStop SQL/MX. $SYSTEM.
Installing NonStop SQL/MX Post-Installation Tasks To display the version information about executable files in the OSS environment, enter: vproc executable-file-name To display version information about executable files in the Guardian environment, enter: VPROC executable-file-name For example, to find the version of MXCMP installed, at a TACL prompt, enter: VPROC $SYSTEM.SYSTEM.
Installing NonStop SQL/MX Reviewing and Setting System Defaults Reviewing and Setting System Defaults Before using the installed SQL/MX Release 2.x database, you should first review the system defaults contained in the user table SYSTEM_DEFAULTS. Depending on your database needs, you might want to change some of the system defaults to alternative settings. For more information, see Section 6, Reviewing and Setting System Defaults and the SQL/MX Reference Manual.
Installing NonStop SQL/MX Installing the SQL/MX Online Help For information about using Visual Query Planner, see the SQL/MX Query Guide, or see the Visual Query Planner online help in the Visual Query Planner. Installing the SQL/MX Online Help SQL/MX online help files are downloadable from the HP Software Depot at: http://www.software.hp.com The help files are designed for best viewing on a TSM workstation or NonStop system console provided by HP.
Installing NonStop SQL/MX Relinking the SQL/MX UDR Server 1. Use OutsideView, the Receive File option, or FTP to download T610SET to a Windows workstation. The setup file for the preprocessor is a binary file. If you use the Receive File option, select Binary but do not select Add CR LF. Specify setup.exe for the local file name. The Installshield Wizard installs the preprocessor files. By default, NonStop SQL/MX places the executable in the C:\Program Files\HP SQL-MX COBOL Preprocessors directory.
Installing NonStop SQL/MX • • The Phases of InstallSqlmx Verifies that SQL/MX objects have been laid down correctly from the SUT installation. Creates an anchor file that specifies the volume location of system metadata tables. Caution. The person who runs InstallSqlmx must have super ID (SUPER.SUPER) privileges on the affected node. InstallSqlmx does not support user aliases of SUPER.SUPER. • After creating the anchor file, InstallSqlmx assigns a security of “N---” to the anchor file. Caution.
Installing NonStop SQL/MX The Phases of InstallSqlmx Table 3-2. The Phases of InstallSqlmx Phase Phase Activities 1 InstallSqlmx verifies that the SQL/MX objects were laid down correctly from the SUT installation. If any required SQL/MX objects are missing from their default locations, InstallSqlmx notifies the user about the missing objects and aborts the installation. See Overview of Automatic File Installations on page 3-5 for file name and default location information.
InstallSqlmx Syntax Installing NonStop SQL/MX InstallSqlmx Syntax InstallSqlmx [-help | -h ] | options options are: { [ -l \$volume [ -n national_charset_name ] [ -nofcheck ]] | -initmxcs | -b } volume is: the DP2 volume in which to install the system metadata tables You must always specify the -l option first. You can specify other options in any order. InstallSqlmx Options Table 3-3 describes the command options for InstallSqlmx. Table 3-3.
InstallSqlmx Error Messages Installing NonStop SQL/MX Table 3-3. InstallSqlmx Command Options (page 2 of 2) Option Description -l volume Use this option to specify the volume in which InstallSqlmx installs SQL/MX system metadata tables. The volume, which must be audited, should not go on $SYSTEM. InstallSqlmx determines whether system metadata tables already exist. If they already exist, an error message appears and InstallSqlmx terminates.
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 block and is convenient for reading multiple records sequentially. However, the large block support feature might not be useful for random access, where only one or a few records are accessed at a time. Therefore, you must select a disk block size depending on the type of most frequently used queries to the database. By default, the table is created with 4096 bytes (4 KB) disk block size.
Understanding and Planning SQL/MX Tables SYSKEY SYSKEY A SYSKEY (or system-defined clustering key) is a clustering or storage key defined by NonStop SQL/MX instead of the user. SQL/MX tables with no user-defined clustering key have a clustering key defined by NonStop SQL/MX and stored in a column named SYSKEY. You can use the primary key as the clustering key. If you do not, NonStop SQL/MX appends a SYSKEY to the specified clustering key column list.
Understanding and Planning SQL/MX Tables Types of Key-Sequenced File Access Rows are stored in a key-sequenced file logically in ascending or descending order, according to their clustering key values, as defined by the use of the ASCENDING or DESCENDING specification in the CREATE TABLE statement. For information about the file structures of SQL/MP tables, see the SQL/MP Installation and Management Guide and the SQL/MP Reference Manual.
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— 544536-007 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 Authorization Requirements for Altering Database Objects on page 9-19 Authorization Requirements for Dropping Database Objects on page 9-26 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: • • • Databa
Planning Database Security and Recovery • • • • • • • • OSS File and Directory Security r (read) for view or print a file, or read a directory w (write) for change or delete a file, or add or delete directory entries 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
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 Option Effect Recovery Strategy MODIFY utility (MODIFY INDEX and MODIFY TABLE) ADD (split range partition) Does not invalidate online dumps of existing partitions. Make an online dump of the new partition to retain TMF file recovery protection.
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.
Planning Database Security and Recovery Rollback of DP2 Large Block Rollback of DP2 Large Block The SQL/MX 2.3.3 release provides large block support. The large block support feature enables you to create objects of block size 32768 bytes (32 KB). NonStop SQL/MX depends on DP2 for large block support. To fall back to an earlier SQL/MX version, you must use the minimum DP2 SPR— T9053H02^ASI (available in H06.09 or later H-series RVU) or T9053H02^ATZ (available in J06.03 or later J-series RVU).
Planning Database Security and Recovery Fixup of Tables with Referential Integrity Constraints >>drop table tab32k; --- SQL operation complete. Fixup of Tables with Referential Integrity Constraints SQL/MX 2.3.4 and later releases support the referential integrity (RI) actions CASCADE, SET DEFAULT and SET NULL. Falling back to SQL/MX versions earlier to 2.3.4 results in tables (that include the RI actions) exhibiting the NO ACTION behavior for DML operations.
Planning Database Security and Recovery Fixup of Tables with Referential Integrity Constraints .DEFINITION_SCHEMA_VERSION_1200.OBJECTS O, .DEFINITION_SCHEMA_VERSION_1200.OBJECTS O1, .DEFINITION_SCHEMA_VERSION_1200.TBL_CONSTRAINTS T, .DEFINITION_SCHEMA_VERSION_1200.REF_CONSTRAINTS R WHERE CA.CAT_UID = S.CAT_UID AND CA.CAT_NAME='' AND S.SCHEMA_UID=O.SCHEMA_UID AND O.OBJECT_UID=T.CONSTRAINT_UID AND O.OBJECT_SECURITY_CLASS='UT' AND O.
Planning Database Security and Recovery Fixup of Tables with Referential Integrity Constraints HP NonStop SQL/MX Installation and Management Guide— 544536-007 5- 40
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.
New System Default for SQL/MX Release 2.3.3 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.
System Default Settings to Review After Installing SQL/MX Release 2.x Reviewing and Setting System Defaults System Default Settings to Review After Installing SQL/MX Release 2.x Alternative Settings For Selected System Defaults Table 6-4 lists the system defaults that you should consider changing after you install SQL/MX Release 2.x and before you run your applications. For more information about these and other system defaults, see the SQL/MX Reference Manual. Table 6-4.
Alternative Settings For Selected System Defaults Reviewing and Setting System Defaults Table 6-4. Alternative Settings for Selected System Defaults (page 2 of 6) System Default Default Value Alternative Setting HIST_SCRATCH_VOL For SQL/MX tables, the default is a blank. If you do not set this value, NonStop SQL/MX uses the default volume specified by the _DEFAULTS define for SQL/MX tables and the volume of the table’s primary partition for SQL/MP tables.
Alternative Settings For Selected System Defaults Reviewing and Setting System Defaults Table 6-4. Alternative Settings for Selected System Defaults (page 3 of 6) System Default Default Value Alternative Setting QUERY_CACHE The default value is 1024. The value 0 deactivates the query cache in the current session. QUERY_CACHE_MAX_ VICTIMS The default value is 10. A very large value means that all the cache entries could be displaced to accommodate one very large query.
Alternative Settings For Selected System Defaults Reviewing and Setting System Defaults Table 6-4. Alternative Settings for Selected System Defaults (page 4 of 6) System Default Default Value Alternative Setting RECOMPILATION_WARNINGS The default value is OFF. The value ON means that NonStop SQL/MX generates a warning when a statement is automatically recompiled in an application (because of various factors). A warning is also generated when a similarity check passes.
Reviewing and Setting System Defaults Alternative Settings For Selected System Defaults Table 6-4. Alternative Settings for Selected System Defaults (page 5 of 6) System Default Default Value Alternative Setting SCRATCH_DISKS 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 (,).
Reviewing and Setting System Defaults Alternative Settings For Selected System Defaults Table 6-4. Alternative Settings for Selected System Defaults (page 6 of 6) System Default Default Value Alternative Setting TIMEOUT The default is 6000 in hundredths of seconds, which is equivalent to 60 seconds. The time in hundredths of seconds to wait for a lock before returning an error. The range of values you can enter is from -1 to 2147483519. The value -1 directs NonStop SQL/MX not to time out.
Settings Required to Achieve ANSI Compliance Reviewing and Setting System Defaults Settings Required to Achieve ANSI Compliance Table 6-5 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-5.
Reviewing and Setting System Defaults Settings Required to Achieve ANSI Compliance HP NonStop SQL/MX Installation and Management Guide— 544536-007 6- 14
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-21.
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 SQL/MX 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 FROM SALES.ODETAIL OD INNER JOIN SALES.PARTS P ON od.partnum = p.partnum; 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.
Creating an SQL/MX Database • • Make columns NOT NULL NOT DROPPABLE unless null values are needed. Null processing imposes disk space and performance overhead that should be avoided whenever possible. Align fields on appropriate boundaries: ° Align character data types as follows: ° ° ° ° • • • • • ° Align ASCII character types on one-byte boundaries. Align UCS2 character types on two-byte boundaries.
Creating an SQL/MX Database • Database Design Guidelines for Improving OLTP Performance ° Use datetime fields if datetime arithmetic is needed in an SQL query (for example, where date-col + interval '1' day > date '2001-0911'). ° Use LARGEINT or CHAR if datetime is needed for storage and retrieval only. Where possible, avoid adding columns to a table: ° ° Added columns turn off internal expression optimizations. 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— 544536-007 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.
Querying SQL/MX Metadata SQL/MX Metadata Tables 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. Figure 8-2 lists the SQL/MX metadata tables located in the system catalog, including the primary keys for each table. Use this table as a reference for the examples later in this section.
SQL/MX Metadata Tables Querying SQL/MX Metadata Figure 8-2.
SQL/MX Metadata Tables Querying SQL/MX Metadata Figure 8-2.
SQL/MX Metadata Tables Querying SQL/MX Metadata Figure 8-2.
SQL/MX Metadata Tables Querying SQL/MX Metadata Figure 8-2.
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-20 Displaying All Catalogs Visible on a Node on page 8-21 Determining Whether a Catalog is Local or Remote on page 8-21 Displaying All Schemas in a Catalog on page 8-22 Displaying the Guardian Location of the Metadata Tables for a Catalog on page 8-22 Displaying the Attributes of a Catalog on page 8-23 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-30 Displaying All Views in a Schema on page 8-34 Displaying the Schema Version on page 8-61 Displaying the System Schema Version on page 8-61 Displaying the Object Schema Version (OSV) on page 8-61 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-29 Displaying All Tables in a Schema on page 8-30 Displaying All DDL Locks on a Table on page 8-31 Displaying the Attributes of a Table on page 8-32 Displaying All Tables on a System on page 8-33 Related topics: • • • • • • • • • • Locating System Schema Tables on page 8-12 Locating System Defaults Schema Tables on page 8-15 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-21). 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-29).
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-35 Displaying All Attributes of an SQL/MP Alias Name on page 8-36 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 Figure 8-2 on page 8-7 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-42 Displaying All Columns in an Index on page 8-50 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-42 Displaying Attributes of a Partition on page 8-43 The PARTITIONS table, located in the schema DEFINITION_SCHEMA_VERSION_version-number of each catalog, describes all partitions in a catalog. For detailed information about all metadata tables, see Figure 8-2 on page 8-7 in this manual or the SQL/MX Reference Manual.
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-44 Displaying All Constraints on a Table on page 8-45 Displaying the Attributes of a Constraint on page 8-46 Related topics: • • • Displaying All Columns in a Primary Key or Unique Constraint on page 8-51 Displaying All Columns in a NOT NULL Constraint on page 8-52 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-53 Displaying the Attributes of a Column on page 8-54 Displaying All Tables Containing a Selected Column on page 8-55 Related topic: • Displaying All Privileges for a Column on page 8-59 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-57 Displaying All Privileges for a View on page 8-58 Displaying All Privileges for a Column on page 8-59 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 For example, the mxci banner would display this information for SQL/MX Release 2.3: Hewlett-Packard NonStop(TM) SQL/MX Conversational Interface 2.3 (c) Copyright 2007 Hewlett-Packard Development Company, LP. 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.
Querying SQL/MX Metadata Displaying the Object Feature Version (OFV) nonstop_sqlmx_node.system_schema.catsys c, catalog-name.definition_schema_version_schema-version.objects o where c.cat_name = 'catalog-name' and c.cat_uid = s.cat_uid and s.schema_name = 'schema-name' and s.schema_uid = o.schema_uid and o.object_name = 'object-name' and o.
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 Using FUP RELOAD to Reorganize Tables If this directory becomes too full, NonStop SQL/MX will not drop any more objects until more space is made available. To prevent this from happening, periodically check (once a week, for example) the DDL directory and purge all DDL files for tables you know will not have to be recovered. The DDL directory contains timestamp information for each DDL file that identifies the time and date when a table was dropped.
Reorganizing SQL/MX Tables and Maintaining Data • Determining the Status of a Reorganization With FUP STATUS Beginning with SQL/MX Release 2.2, you can either specify an ANSI name or a Guardian file name when you perform a FUP RELOAD on an SQL/MX table or index. FUP RELOAD only supports the use of ANSI names for individual table or index partitions.
Reorganizing SQL/MX Tables and Maintaining Data Suspending a Reorganization Operation This command requests the status of the FUP RELOAD operation started in the previous example for the table CUSTOMER: FUP - STATUS $DATA08.ZSDAWVHH.J8MGRZ00 The status is reported in this format: FUP - STATUS $DATA08.ZSDAWVHH.J8MGRZ00 $DATA08.ZSDAWVHH.J8MGRZ00 RELOAD IN PROGRESS RELOAD INITIATED: 21 Jan 2004, 13:02 If the operation completes, terminates abnormally, or is suspended, FUP displays the appropriate message.
Reorganizing SQL/MX Tables and Maintaining Data DDL Lock Considerations for MODIFY, import, POPULATE INDEX, DUP, FASTCOPY, and If you want to keep the same RATE and SLACK values when you restart the reorganization process, enter the FUP RELOAD command without the RATE and SLACK parameters: FUP - RELOAD $DATA08.ZSDAWVHH.J8MGRZ00 After a FUP RELOAD operation has been suspended and you want to start this operation completely over again, enter the FUP RELOAD command with the NEW option: FUP - RELOAD $DATA08.
Reorganizing SQL/MX Tables and Maintaining Data Consequences of a Failed Utility Operation These SQL/MX utilities use DDL locks: • • • • • • MODIFY. See Using MODIFY to Manage Table and Index Partitions on page 10-10. import (for the fast loading option only). See Using import to Load SQL/MX Tables on page 10-22. POPULATE INDEX DUP. See Using DUP to Copy Tables Into Tables on page 10-40. FASTCOPY. See Using FASTCOPY to Copy Tables into Tables on page 10-42. PURGEDATA.
Reorganizing SQL/MX Tables and Maintaining Data Using a Query to Obtain DDL Lock and Process ID Information locks and the MXCMP process IDs associated with the catalog SAMDBCAT and the schema SALES: set schema samdbcat.definition_schema_version_1200; select substring (o.object_name from 1 for 15) as lock_name, o.object_type as type, l.operation, l.status, substring (l.process_id from 1 for 25) as process_id from objects o, ddl_locks 1 where l.object_uid = o.object_uid and o.
Reorganizing SQL/MX Tables and Maintaining Data Performing Recovery on Failed Utility Operations Performing Recovery on Failed Utility Operations If the utility request fails to complete successfully, the request must be recovered to a known good state (resumed or cancelled), which removes the DDL lock and resets all flags. Most utilities respond to a request failure by invoking their own internal recovery procedure.
Reorganizing SQL/MX Tables and Maintaining Data Structure Changing Operations That Can Run With Active or Inactive DDL Locks Present Structure Changing Operations That Can Run With Active or Inactive DDL Locks Present Structure changing operations are sometimes allowed to run even with active or inactive DDL locks present.
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Table and Index Partitions Using MODIFY to Manage Table and Index Partitions As the data in SQL/MX tables and indexes is updated and removed and new data is added, you need to manage table and index partitions. Use the MODIFY utility to perform these partition management operations: • • • Adding, splitting, merging, moving, and dropping partitions for range-partitioned tables and indexes.
Reorganizing SQL/MX Tables and Maintaining Data MODIFY and TMF MODIFY and TMF Many partition management requests require movement of massive amounts of data. Because these operations can take longer than the default TMF time limit of two hours, operations involving data movement are performed in multiple transactions.
Reorganizing SQL/MX Tables and Maintaining Data MODIFY and Table Reloading operations and utility operations—including another MODIFY command—on the affected table or index. ORSERV keeps the file open and does not terminate for five minutes after the reload operation completes.
Reorganizing SQL/MX Tables and Maintaining Data Recovering a Failed MODIFY Request and Resetting Flags For information about the MXCI SHOWLABEL command, see the SQL/MX Reference Manual. Recovering a Failed MODIFY Request and Resetting Flags The UNRECLAIMEDSPACE (F) flag or the INCOMPLETE SQLDDL OPERATION (D) flag can be left set when a MODIFY operation fails to complete successfully.
Using MODIFY to Manage Range-Partitioned Tables and Indexes Reorganizing SQL/MX Tables and Maintaining Data schema_name = 'SALES' and cat_uid = (select cat_uid from nonstop_sqlmx_figaro.system_schema.catsys where cat_name = 'SAMDBCAT' ) ) for read uncommitted access order by l.object_uid; LOCK_NAME ----------------------CUSTOMER_77179541_2217 UTIL ----PI STATUS ----------4 PROCESS_ID ---------------\FIGARO.
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Range-Partitioned Tables and Indexes MODIFY supports online partition management for range-partitioned tables and indexes where the partitioning key is a prefix of the clustering key. MODIFY supports these partition management operations for range partitions: • • • • • • • Adding a new empty partition. You can add range partitions online.
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Range-Partitioned Tables and Indexes another disk volume. This MODIFY command splits the $DATA03 partition, moving all locations from ‘Seattle’ and beyond to a new partition on $DATA04: MODIFY TABLE cat2.sch2.
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Range-Partitioned Tables and Indexes this partition into the partition on $DATA02 and to drop the $DATA03 partition. Use this MODIFY TABLE command: MODIFY TABLE cat2.sch2.ordersr move where key = value ('New York') thru key = last key to previous partition; Note that the DROP PARTITON syntax is not used in this example because the partition $DATA03, which contains ‘New York,’ is not yet empty.
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Hash-Partitioned Tables and Indexes MERGE operations. The only table operation that cannot be performed on an index is REUSE. The next examples illustrate these supported operations. Example of Adding a New Range Partition Business has exceeded expectations in the first half of 2004. The index partition on $DATA02 has become too large, and you want to move May and June orders to a new partition, $DATA04.
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Hash-Partitioned Tables and Indexes You can perform only offline partition management operations for hash-partitioned tables and indexes. MODIFY supports these offline partition management operations for hash partitions: • • • Adding a new hash partition and rebalancing data (that is, redistributing existing data to all partitions, including the new partition). Dropping the last existing hash partition and rebalancing data.
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Hash-Partitioned Tables and Indexes Example of Moving an Existing Hash Partition to a New Location You decide to use $DATA03 for another purpose and to move the hash partition on $DATA03 to a different volume. Use this MODIFY TABLE command to move the partition to a new location: MODIFY TABLE cat3.sch3.
Reorganizing SQL/MX Tables and Maintaining Data Managing System-Clustered Tables and Indexes Orders suddenly decline, and you decide to free up some of the disk resources used by the index. Use this MODIFY command to drop the fourth partition and redistribute its data to the remaining partitions: MODIFY INDEX cat2.sch2.ordersr_via_ordernumber drop partition where last partition; By using the syntax last partition in the example, you avoid the need to specify the last partition by its location.
Reorganizing SQL/MX Tables and Maintaining Data Using import to Load SQL/MX Tables Using import to Load SQL/MX Tables Use the import command to move data from an input file in ASCII or UCS2 format into an existing SQL/MX table. For example, you would use import to load an input file that contains the ASCII-formatted contents of another vendor’s SQL table into an SQL/MX table. DataLoader/MX can also send output to an import process. Note.
Reorganizing SQL/MX Tables and Maintaining Data Summary of import Options Summary of import Options Table 10-1 describes the most important import command options. Table 10-1. import Command Options (page 1 of 2) import Option Description -D Disables all triggers before the data is inserted, which prevents trigger actions from occurring. -F and -C Specifies the first row and the number of rows to import from the input file. The first row is designated 0 (zero).
Reorganizing SQL/MX Tables and Maintaining Data Summary of import Options Table 10-1. import Command Options (page 2 of 2) import Option -U Description Specifies the name of the file that contains format specifications for the input file. The format file must be an OSS or Guardian text file. The Guardian file name must be specified in OSS format. The format file cannot exceed 60,000 bytes.
Reorganizing SQL/MX Tables and Maintaining Data Summary of import Options For more information about these and other import command options, see the SQL/MX Reference Manual. Format File Considerations for import For more information about format file structures and field options for import, see the SQL/MX Reference Manual. Format File for a DELIM Input File If the input file type is DELIM and you want to use a format file, you must include the [COLUMN FORMAT] section. The other sections are optional.
Reorganizing SQL/MX Tables and Maintaining Data Other import Features 1. The format of numbers may be changed. Example: 123456.7 in the original input file may appear as 1.234567E+05 in the error log file. 2. The value of some floating-point numbers may not be exactly the same (though very close). This is due to an internal conversion to binary format and then back to floating point ASCII format for logging to the error log file. 3.
Reorganizing SQL/MX Tables and Maintaining Data Recommended Practices for Improving import Performance import operation with the fast loading technique is being performed, the second operation fails with a concurrent access error. For more information, see the SQL/MX Reference Manual. If the table is not empty or has dependent indexes, import continues with the normal load operation using TMF transactions. For more information about import command features and options, see the SQL/MX Reference Manual.
Reorganizing SQL/MX Tables and Maintaining Data Recommended Practices for Improving import Performance 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. Make sure the data in the input file is presorted according to the clustering key order if a clustering key is present. Presorting the input file data prevents external sorts and extra block splits.
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-30. • 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 Support for restarting import 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 Examples of Using import to Load an SQL/MX Table Example 1: Importing Data From a Delimited File This example is of an import of data from a delimited file. It shows the structure of the table to be loaded, the data to be loaded (the input data file), the format file describing the data, and the import command used to load it.
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using import to Load an SQL/MX Table RowDelimiter=\n Qualifier=" import Load Command This import command imports data into the target table COMPANY from the delimited input file COINPUT using the format file FORMFILE1: import cat.sch.
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using import to Load an SQL/MX Table col=fax,48,12 RecordLength=61 Note. All commas and double-quote characters in the input file are ignored because they are not covered by any of the col= entries in the [FIXED WIDTH FORMAT] section of the format file. In this example, the RecordLength value of 61 includes one newline character at the end of each input record.
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using import to Load an SQL/MX Table col=col1 [FIXED WIDTH FORMAT] FileIsBinary=N RecordLength =5 col=col1,1,5 import Load Command This import command imports data into the target table TABLE_2 from the delimited input file COINPUT_FX3 using the format file FORMFILE3: import cat.sch.
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using import to Load an SQL/MX Table A select statement on the table will show the following: select * from cat.sch.Xample; C1 C2 ----------- -------11 12345678 15 62345678 27 72345678 55 12345678 --- 4 row(s) selected. The X.perrs file will contain: A8,22345678 14,423456789 17,52345678,9 BZ,XYZ The X.perrmsg file will contain: *** ERROR[20081] Row number 2 and column number 1 could not be processed.
Reorganizing SQL/MX Tables and Maintaining Data Using import to Append Data to Tables or Partitions Elapsed time = 0:0:0.088011 Elapsed time since skipping initial rows = 0:0:0.088011 Rows Imported Successfully = 4 Rows ignored due to parsing errors = 4 Rows ignored due to execution errors = 2 Parsing Error Log File Name: X.perrs Parsing Error Messages File Name: X.perrmsg Execution Error Log File Name: X.xerrs Execution Error Messages File Name: X.
Reorganizing SQL/MX Tables and Maintaining Data Using import to Append Data to Tables or Partitions Appending Data to Multiple Partitions in Parallel Use import to append data to partitioned tables in parallel. This strategy can improve append performance if table partitions are distributed across disks, CPUs, and I/O channels. To append data to partitions in parallel: 1. Start an import process for each partition. One way is to start each import process in the CPU associated with the target partition.
Reorganizing SQL/MX Tables and Maintaining Data Using DUP to Copy Tables Into Tables $VOL4. The target partitions reside on volumes $VOL33 through $VOL48. The record layout corresponds exactly to the target table layout. The -l option ensures that the import operation proceeds even if an input record contains data that cannot be parsed for loading into the target table. /user/bin> /user/bin> /user/bin> . . . /user/bin> import cat1.schema1.t1 -I /G/VOL33/TARGET/HIST01 -L 1000 import cat1.schema1.
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using DUP to Copy Tables DUP operation fails before the data is successfully copied, specify RECOVER with the CANCEL option to roll back the DUP operation. You can access this status information by reading the DDL_LOCK metadata table. If you specify the wrong RECOVER operation, RECOVER reports this so that you can use the correct operation, possibly with RECOVER...CANCEL.
Reorganizing SQL/MX Tables and Maintaining Data Using FASTCOPY to Copy Tables into Tables Using FASTCOPY to Copy Tables into Tables FASTCOPY is a syntax-based utility that can be executed from MXCI and from a program using dynamic SQL. It enables you to copy a source table and its indexes into an existing equivalent target table and indexes. A fastcopy operation includes the execution of one or more FASTCOPY commands. For more information about the FASTCOPY utility, see the SQL/MX Reference Manual.
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using FASTCOPY to Copy Tables complete, you can re-enable the triggers using the ALTER TRIGGER ENABLE command. The FASTCOPY command checks for the RI constraints that are defined on the target table. RI constraint checking, like trigger actions, also has a negative performance impact. To remove the RI and other droppable constraints that would interfere with a FASTCOPY command, use the ALTER TABLE...DROP CONSTRAINT command.
DUP versus FASTCOPY Reorganizing SQL/MX Tables and Maintaining Data the fastcopy operation. Because the source index SI2 has no equivalent target index, it does not participate in the fastcopy operation. FASTCOPY TABLE ST TO TT INDEXES EXPLICIT; The following command copies all the rows from source index SI1 to the target index TI1. FASTCOPY INDEX SI1 to TI1; The base table TT can be accessed only after completing the fastcopy operation (copying of indexes SI1 to TI1).
Reorganizing SQL/MX Tables and Maintaining Data Using PURGEDATA to Delete Data From Tables Using PURGEDATA to Delete Data From Tables PURGEDATA is a syntax-based utility you can execute from MXCI to delete data from a table and its related index or from the specified partitions of a table that has no index. For more information about the PURGEDATA utility, see the SQL/MX Reference Manual.
Reorganizing SQL/MX Tables and Maintaining Data • • Examples of Using PURGEDATA to Delete Table Data Another table references the named table through a trigger or referential integrity constraint. You attempt to perform PURGEDATA within a user-defined transaction. There is no notion of a transaction existing for a table. Examples of Using PURGEDATA to Delete Table Data This example purges the data in the specified table. If the table has indexes, the indexes are also purged. PURGEDATA mycat.myschema.
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 Checking Module Dependencies with DISPLAY USE OF For a description of SQL/MX DISPLAY USE OF command syntax, see SQL/MX Reference Manual. Displaying All Modules and Dependent Objects When you omit the module clause and the object clause, DISPLAY USE OF returns information about all modules and dependent objects. >>DISPLAY USE OF; Module: Object: Object: Object: Object: Object: CAT.SCH.CONSTRAINTM CAT.SCH.T24 CAT.SCH.T25 CAT.SCH.T26 CAT.SCH.T27 CAT.SCH.
Managing Database Applications Checking Module Dependencies with DISPLAY USE OF Displaying Dependent Objects for One or More Specified Modules When you specify the module name and omit the object clause, DISPLAY USE OF returns information about a specific module and all its dependent objects. >>DISPLAY USE OF module 'CAT.SCH.CONSTRAINTM'; Module: Object: Object: Object: Object: Object: CAT.SCH.CONSTRAINTM CAT.SCH.T24 CAT.SCH.T25 CAT.SCH.T26 CAT.SCH.T27 CAT.SCH.
Managing Database Applications Checking Module Dependencies with DISPLAY USE OF Object: CAT.SCH.TABLE1 Module: CAT.SCH.MODTABLE1 Source Name: /E/NODE11/modulelist/module1.sql Module: CAT.SCH.MODTABLE2 Source Name: /E/NODE11/modulelist/module2.sql Display source SQL file for a given module in a given OSS directory >>DISPLAY USE OF MODULE_DIR '/usr/user1/all/modulestorage' SOURCE 'CAT.ALL.MODULE1'; Module: CAT.ALL.MODULE1 Source Name: /E/NODE11/usr/user2/constraint1.
Managing Database Applications Grouping Applications and Modules to Run Multiple DISPLAY USE OF Operations Displaying the Modules in a Local Directory DISPLAY USE OF returns information about the modules in a local OSS directory when you specify the directory with the MODULE_DIR clause. DISPLAY USE OF MODULE_DIR '/usr/myapp' MODULE 'cat.sch.t24'; You must specify the fully qualified OSS directory path of a locally placed module. Otherwise, an error occurs.
Managing Database Applications Grouping Applications and Modules to Run Multiple DISPLAY USE OF Operations The INVENTORYAPP applications access the SQL/MX table T01CAT.T01SCH.T01TBL. The SHIPMENTAPP applications access the SQL/MX table T01CAT.T01SCH.T02TBL. 1. Precompile the applications to the group INVENTORYAPP: /user/smith/tmp/tmp/DUO: mxsqlc t01.sql -g moduleGroup=INVENTORYAPP Hewlett-Packard NonStop(TM) SQL/MX C/C++ Preprocessor 2.1 (c) Copyright 2005 Hewlett-Packard Development Company, LP.
Managing Database Applications Grouping Applications and Modules to Run Multiple DISPLAY USE OF Operations % mxCompileUserModule t11.exe % mxCompileUserModule t12.exe 6. Check the SQL compiled module files using INVENTORYAPP: % ls /usr/tandem/sqlmx/USERMODULES/*INVENTORYAPP^*^^ /usr/tandem/sqlmx/USERMODULES/T01CAT.T01SCH.INVENTORYAPP^T01MOD^^ /usr/tandem/sqlmx/USERMODULES/T01CAT.T01SCH.INVENTORYAPP^T02MOD^^ /usr/tandem/sqlmx/USERMODULES/T01CAT.T01SCH.INVENTORYAPP^T03MOD^^ 7.
Grouping Applications and Modules to Run Multiple DISPLAY USE OF Operations Managing Database Applications 1 modules found, 1 modules extracted. 1 mxcmp invocations: 1 succeeded, 0 failed. 10. Perform Step 9 for the remainder of the applications: % % % % /user/smith/tmp/tmp/DUO: /user/smith/tmp/tmp/DUO: /user/smith/tmp/tmp/DUO: /user/smith/tmp/tmp/DUO: mxCompileUserModule mxCompileUserModule mxCompileUserModule mxCompileUserModule -g -g -g -g moduleLocal moduleLocal moduleLocal moduleLocal t02.
Managing Database Applications Removing Modules Removing Modules You should prepare carefully before you remove module files. At best, removing a module file can free up disk space. At worst, it can cause some of their applications to return run-time errors (8809, unable to open module file). Before you remove a module file: 1. Verify that no other applications use modules that you will remove. 2. Verify that the modules (or the application source files) have been backed up.
Managing Database Applications Converting Globally Placed Modules to Locally Placed Modules Converting Globally Placed Modules to Locally Placed Modules You might mix globally placed modules and locally placed modules in a development environment. However, in a production environment, you should choose globally placed modules or locally placed modules and not mix the two.
Managing Database Applications Managing Module Files and Their Applications During Fallback From SQL/MX Release 2.x a. Copy the executable to an empty temporary directory. b. From the OSS prompt, invoke mxCompileUserModule: mxCompileUserModule -g moduleLocal temp-directory/application-executable The -g moduleLocal option of mxCompileUserModule generates a locally placed module in the temporary directory.
Managing Database Applications Backing Up and Restoring Programs Backing Up and Restoring Programs Make backing up and restoring programs part of both the day-to-day maintenance of program files and the high-level strategy for backing up and restoring the entire database. The former should include periodically storing copies of programs to safe locations (such as another disk, magnetic tape, or another system). As a rule, you should back up programs before you make major changes to the database.
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 supported plan version (1200). 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 SQL/MX Database Performance To achieve maximum performance, you must provide sufficient hardware to handle the throughput and size of the application database. In addition to hardware, many factors affect the performance of a database and application. Some factors are system dependent, others are application dependent. The factors discussed in this section are specific performance issues that can arise in an installed and operating SQL/MX environment.
Enhancing SQL/MX Database Performance Using Queries in an SQL/MX Database Using Queries in an SQL/MX Database Queries are the basis of a relational database application. You specify queries explicitly by using application-embedded SELECT and CURSOR statements, ad hoc query requests, and report writer selections. Queries are implicitly specified through UPDATE, INSERT SELECT, and DELETE statements. The number and type of queries used in an SQL/MX environment influence the performance of the database.
Enhancing SQL/MX Database Performance Understanding the Implications of Concurrency remainder of the operation. Use this method if you want to complete the partition move as soon as possible and if users do not require concurrent access to the data. • • • Creating a constraint ° When you create a CHECK or NOT NULL constraint, NonStop SQL/MX confirms that the column data in all table rows complies with, and does not violate, the constraint before adding it.
Enhancing SQL/MX Database Performance Minimizing Contention Minimizing Contention When managing partitions, you can minimize contention by using the WITH SHARED ACCESS option of the MODIFY TABLE or MODIFY INDEX statements. For example, this MODIFY TABLE statement uses the WITH SHARED ACCESS option to provide online execution on range-partitioned objects: MODIFY TABLE cat1.sch1.
Enhancing SQL/MX Database Performance Minimizing Contention Considerations for WITH SHARED ACCESS Option of MODIFY • • • • • To eliminate the interval between the time the MODIFY operation completes and a new online dump is taken, use the WITH SHARED ACCESS option so that you can take online dumps while the MODIFY operation proceeds. NonStop SQL/MX sends an event message to EMS, indicating when online dumps can be taken. An operator uses the TMFCOM DUMP FILES command to start online dumps.
Enhancing SQL/MX Database Performance Avoiding Contention Between DDL or Utility Operations Avoiding Contention Between DDL or Utility Operations Only one DDL or utility statement can operate on a given SQL/MX object (or partition of an SQL/MX object) at a time. An error occurs if you attempt to execute a DDL or utility statement while another process is executing a DDL or utility statement on the same object.
Enhancing SQL/MX Database Performance • Keeping Statistics Current For large tables, audit trail space can be exceeded during the course of the operation, resulting in termination of the operation and backout by the TMF subsystem. This condition is minimized if you allow NonStop SQL/MX to manage TMF transactions. Note. HP recommends that you do not initiate a user-defined TMF transaction for long-running DDL operations. Utility operations are not supported in user-defined TMF transactions.
Enhancing SQL/MX 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 SQL/MX 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 SQL/MX 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 SQL/MX 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.3.1 and then SQL compile your SQL/MX applications. For more information, see the SQL/MX Database and Application Migration Guide.
Enhancing SQL/MX 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 SQL/MX 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 SQL/MX 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 SQL/MX 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 SQL/MX 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 SQL/MX 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 SQL/MX 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 SQL/MX 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 SQL/MX 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-17 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-33 creating 7-7 dropping 9-28 querying metadata about 8-20/8-23 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-17 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-6 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-13 description of 6-1 new for Release 2.
E Index Distributed database (continued) 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 9-26 Dropping partitio
G Index FILEINFO utility (continued) volume recovery 12-2 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-14 interface description 3-14 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,
J Index import (continued) running on populated tables 10-28 Inconsistencies, detecting 8-60 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
M Index Label data (continued) description of 12-27 Large Block Support 4-3 Local autonomy 13-2, 13-12 Locally distributed database 13-1 Locks 14-9, 14-10 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 attrib
M Index Metadata, querying (continued) commands for displaying information 8-2 constraint attributes, displaying 8-46 constraints on a table, displaying 8-45 constraints on a table, locating 8-44 DDL locks on a table, displaying 8-31 DDL locks on an index, displaying 8-39 description of 8-2 detecting database inconsistencies 8-60 index attributes, displaying 8-40 index states, displaying 8-41 indexes in a table, displaying 8-38 indexes in a table, locating 8-37 metadata tables, list of 8-6/8-10 object sch
N Index Modules (continued) converting globally placed to locally placed 11-28 displaying all dependent on a specific object 11-21 displaying all modules and dependent objects 11-20 displaying dependent objects for one or more specified modules 11-21 displaying in a local directory 11-23 grouping to run multiple DISPLAY USE OF operations 11-23/11-26 managing 11-15 producing globally placed or locally placed 11-15 recompiling 11-14 removing 11-27 securing globally placed 11-16 securing locally placed 11-18
O Index New process statistics 14-8 NO DEFAULT clause 7-30 NOAUDITCOMPRESS attribute 7-22 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 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-13 description of 6-4 NSM/web display of metadata 8-1 NULL
Q Index Performance (continued) column definitions 7-24 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_MAX_ VICTIMS 6-9 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 Gu
S Index Remote Database Facility (RDF) 5-6 Remote node availability 13-13 Remote node, running applications on 11-9 Removing modules 11-27 Rename Table 7-40, 10-41 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 rollback of large block 5-37 Rows, ordering using an index 4-14 Running applications on a remote node 11-9 Run-time library fil
S Index SPRs for H-series RVUs 3-3 SQL audited objects, recovering purged 5-8 SQL objects 8-11 sqlcli.
S Index SQL/MX objects 5-8 SQL/MX objects, repairing 12-25 START DISK command, SCF 12-39 START TMF command 12-2 Statements, execution costs 14-9 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 que
T Index System defaults (continued) PRIMARY_KEY_CONSTRAINT_ DROPPABLE_OPTION 6-4, 6-13 PRIMARY_KEY_CONSTRAINT_DROP PABLE_OPTION 6-8 QUERY_CACHE 6-9 QUERY_CACHE_ STATEMENT_PINNING 6-9 QUERY_CACHE_MAX_ VICTIMS 6-9 QUERY_CACHE_REQUIRED_ PREFIX_KEYS 6-9 READONLY_CURSOR 6-13 REF_CONSTRAINT_NO_ ACTION_LIKE_RESTRICT 6-4, 6-10, 6-13 SAVE_DROPPED_TABLE_ DDL 6-4 SAVE_DROPPED_TABLE_DDL 6-10 SCRATCH_DISKS 6-11 SCRATCH_DISKS_ EXCLUDED 6-11 SCRATCH_DISKS_ PREFERRED 6-11 setting and updating 6-1 TEMPORARY_TABLE_ HASH_PA
U Index TMF subsystem (continued) concepts 5-10 creating and managing online dumps 5-14 description of 5-10 determining a level of data protection 5-13 determining size requirements 5-13 DUMP FILES command 12-1 file recovery 5-12, 12-1, 12-2 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 co
V Index V W VARCHAR columns, performance issues 7-24 VARCHAR_PARAM_DEFAULT_SIZE 6-5, 6-12 VERIFY utility, mxtool description of 8-3, 12-30 example of 12-20, 12-21 recovered objects 12-11 Verifying software 3-15 Versioning issues 11-8 Versions executable files 3-15 querying metadata about 8-60/8-62 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-33/8-34 recovering 12-22 restoring 12-24 securing 7-39, 7-
Content Feedback First Name: __________________ Phone: _____________________ Company: ___________________ Last Name: _________________ e-mail address: ______________ (All contact information fields are required.) If you’re reporting an error or omission, is your issue: Minor: I can continue to work, but eventual resolution is requested. Major: I can continue to work, but prompt resolution is requested. Critical: I cannot continue to work without immediate response.
