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 3.0 Supported Release Version Updates (RVUs) This publication supports J06.11 and all subsequent J-series RVUs and H06.22 and all subsequent H-series RVUs, until otherwise indicated by its replacement publications.
Document History Part Number Product Version 640325-001 NonStop SQL/MX Release 3.
Legal Notices © Copyright 2011 Hewlett-Packard Development Company L.P. Confidential computer software. Valid license from HP required for possession, use or copying. Consistent with FAR 12.211 and 12.212, Commercial Computer Software, Computer Software Documentation, and Technical Data for Commercial Items are licensed to the U.S. Government under vendor's standard commercial license. The information contained herein is subject to change without notice.
HP NonStop SQL/MX Installation and Management Guide Glossary Index Examples Figures Legal Notices What’s New in This Manual xv Manual Information xv New and Changed Information About This Manual xvii Audience xvii Organization xvii Related Documentation xviii Notation Conventions xx HP Encourages Your Comments xv xxiii 1.
2. Preparing to Install NonStop SQL/MX Contents MXCS 1-12 NonStop Server for Java 1-12 JDBC/MX Driver 1-13 Database Management Tasks 1-13 SQL/MX Database Management Tools 1-13 2. Preparing to Install NonStop SQL/MX Hardware Requirements 2-1 Software Requirements 2-2 Verifying Correct Versions of Independent Software Products Installing the DDL Licensing Product (T0394) 2-3 Starting TMF 2-3 2-3 3.
Contents 5.
6.
Contents 7.
8. Querying SQL/MX Metadata Contents Creating Stored Procedures in Java 7-41 Database Design Guidelines for Improving OLTP Performance 7-41 8.
Contents 8.
Contents 9. Adding, Altering, and Dropping SQL/MX Database Objects 9.
Contents 10. Reorganizing SQL/MX Tables and Maintaining Data 10.
11. Managing Database Applications Contents Guidelines for Using PURGEDATA 10-45 Examples of Using PURGEDATA to Delete Table Data 10-46 11.
12. Performing Recovery Operations Contents 12.
14. Measuring Performance Contents Maintaining Local Autonomy in a Network for SQL/MX Release 3.
A. Using Guardian Names with TMF, RDF, and Measure Contents Performing FUP RELOADs to Generate More Accurate Query Plans Checking Data Integrity 15-18 Creating Logical Views of Data 15-19 Adding and Dropping Partitions 15-19 Avoiding Automatic Recompilations 15-20 Matching Block Split Operation to Table Usage 15-20 15-17 A.
Contents 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 Abstract This manual describes how to install and manage HP NonStop™ SQL/MX. Product Version NonStop SQL/MX Release 3.0 Supported Release Version Updates (RVUs) This publication supports J06.11 and all subsequent J-series RVUs and H06.22 and all subsequent H-series RVUs, until otherwise indicated by its replacement publications. Additionally, all considerations for H-series throughout this manual will hold true for J-series also, unless mentioned otherwise.
What’s New in This Manual • • New and Changed Information Updated the description of Managing Mixed Versions of NonStop SQL/MX on page 13-25. Updated SCF STATS output and its description on page 15-11.
About This Manual This manual describes how to install NonStop SQL/MX Release 3.0 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 and SQL/MX Release 3.x indicate SQL/MX Release 3.0 and subsequent releases, until indicated in a replacement publication.
Related Documentation About This Manual Section 9, Adding, Altering, and Dropping SQL/MX Database Objects Describes how to add, alter, and drop objects from an SQL/MX database. Section 10, Reorganizing SQL/MX Tables and Maintaining Data Describes how to reorganize SQL/MX tables and maintain the data. Section 11, Managing Database Applications Describes how to manage SQL/MX applications. Section 12, Performing Recovery Operations Describes how to perform recovery operations on an SQL/MX database.
Related Documentation About This Manual DataLoader/MX Reference Manual Describes the features and functions of the DataLoader/MX product, a tool to load SQL/MX databases. SQL/MX Messages Manual Describes SQL/MX messages. SQL/MX Glossary Defines SQL/MX terminology. Programming Manuals SQL/MX Programming Manual for C and COBOL Describes how to embed SQL/MX statements in ANSI C and COBOL programs.
Notation Conventions About This Manual Glossary Help Terms and definitions from the SQL/MX Glossary. NSM/web Help Context-sensitive help topics that describe how to use the NSM/web management tool. Visual Query Planner Help Context-sensitive help topics that describe how to use the Visual Query Planner graphical user interface. The NSM/web and Visual Query Planner help systems are accessible from their respective applications.
General Syntax Notation About This Manual lowercase italic letters. Lowercase italic letters indicate variable items that you supply. Items not enclosed in brackets are required. For example: file-name computer type. Computer type letters within text indicate C and Open System Services (OSS) keywords and reserved words. Type these items exactly as shown. Items not enclosed in brackets are required. For example: myfile.c italic computer type.
Change Bar Notation About This Manual An ellipsis immediately following a single syntax item indicates that you can repeat that syntax item any number of times. For example: "s-char…" Punctuation. Parentheses, commas, semicolons, and other symbols not previously described must be typed as shown. For example: error := NEXTFILENAME ( file-name ) ; LISTOPENS SU $process-name.
HP Encourages Your Comments About This Manual HP Encourages Your Comments HP encourages your comments concerning this document. We are committed to providing documentation that meets your needs. Send any errors found, suggestions for improvement, or compliments to docsfeedback@hp.com. Include the document title, part number, and any comment, error found, or suggestion for improvement concerning this document.
About This Manual HP Encourages Your Comments HP NonStop SQL/MX Installation and Management Guide—640325-001 xxiv
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 Metadata 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 SQL/MX Database Object Types Table 1-2 describes SQL/MX object types. For information about SQL/MP objects, see the SQL/MP Installation and Management Guide and the SQL/MP Reference Manual. Table 1-2. SQL/MX Object Types (page 1 of 2) SQL/MX Object Type Table Description A logical representation of data in which a set of records is represented as a sequence of rows, and the set of fields common to all rows is represented by columns.
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—640325-001 1-16
2 Preparing to Install NonStop SQL/MX Before you install SQL/MX Release 3.0, check that you have met the following requirements as they apply to your installation: • • • • • Hardware Requirements on page 2-1 Software Requirements on page 2-2 Verifying Correct Versions of Independent Software Products on page 2-3 Installing the DDL Licensing Product (T0394) on page 2-3 Starting TMF on page 2-3 Hardware Requirements Before you install SQL/MX Release 3.
Software Requirements Preparing to Install NonStop SQL/MX Software Requirements To use SQL/MX Release 3.x, you must install the site update tape (SUT) for the H06.22 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.22 or later RVU contains the correct product versions for these key software products that are used with NonStop SQL/MX: Note.
Verifying Correct Versions of Independent Software Products Preparing to Install NonStop SQL/MX Verifying Correct Versions of Independent Software Products If you intend to develop and execute Java programs that perform SQL operations or Stored Procedures for Java (SPJs) on your NonStop system, verify that you have installed the correct versions of these independent software products: • • NonStop Server for Java JDBC/MX driver These independent products need to be ordered separately from the software p
Preparing to Install NonStop SQL/MX Starting TMF Planning Database Security and Recovery and the TMF Planning and Configuration Guide. The product version of TMF provided on the SUT must be compatible with the SQL/MX software provided with the SQL/MX SPR you install. When you start the TMF subsystem, configured data volumes are started for transaction processing if they are accessible.
Preparing to Install NonStop SQL/MX For TMF configuration information, see the TMF Reference Manual.
Preparing to Install NonStop SQL/MX HP NonStop SQL/MX Installation and Management Guide—640325-001 2 -6 Starting TMF
3 Installing NonStop SQL/MX Summary of Installation Tasks Before you install SQL/MX Release 3.0, 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 SQL/MX Release 3.0. 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.
Verifying the SQL/MX Installation Installing NonStop SQL/MX 7. Run the InstallSqlmx script by entering: Caution. The volume you specify in the InstallSqlmx command line specifies the system catalog location for the system metadata tables. This volume should not go on the $SYSTEM disk volume. If you ever replace the $SYSTEM disk, you can lose system metadata information. The specified volume must be audited.
Installing NonStop SQL/MX Overview of Automatic File Installations Overview of Automatic File Installations This subsection identifies the activities that automatically occur during installation of the H06.22 or later H-series RVU: Note. During SQL/MX installation, these files are installed only if you have the DDL licensing product (T0394) for SQL/MX tables: • • • • • import IMPORT MXAUDSRV mxexportddl MXGNAMES For details about each file, see Executable Files on page 3-9. 1. The H06.
Installing NonStop SQL/MX • • Overview of Automatic File Installations T1058PAX, which contains mxcierrors.cat, the MXCI-related error messages file T1231PAX, which contains the SQL/MX language manager files mxlangman.jar and mxlangman.policy 3. The SUT installation automatically installs the public DLLs ZCLIDLL and ZCLIPDLL to the $SYSTEM.ZDLLnnn subvolume, where nnn is a number picked by the public DLL installation during the SYSGEN operation. Caution.
Installing NonStop SQL/MX • • • Verify That Files Are Licensed NONSTOP_SQLMX_NSK.SYSTEM_SCHEMA.SQLUTILS NONSTOP_SQLMX_NSK.SYSTEM_SCHEMA.SQLJ NONSTOP_SQLMX_NSK.SYSTEM_SCHEMA.ANSINAMES c. COPYOSS installs these user module files in the OSS directory /usr/tandem/sqlmx/USERMODULES: • • NONSTOP_SQLMX_NSK.SCH.MIGRATE NONSTOP_SQLMX_NSK.SCH.MXGNAMES d. COPYOSS installs these files in the OSS directory /usr/tandem/sqlmx/include: • • Platform.h sqlcli.h e.
Installing NonStop SQL/MX • • Summary of Installed SQL/MX Files ZCLIPDLL MXTOOL To verify that these files have been licensed: Note. You can change the security and licensing of DLLs, but the new security and licensing do not take effect until the next system coldload. 1. From a TACL prompt, issue a FILEINFO command on each of the files. IMPORT, MXAUDSRV, MXCMP, MXESP, MXRTDSRV, and MXTOOL reside in $SYSTEM.SYSTEM. ZCLIPDLL resides in $SYSTEM.ZDLLnnn.
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 800 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 3.0. $SYSTEM.SYSTEM MXOMSG A message file associated with MXCS. $SYSTEM.SYSTEM IMPORT The IMPORT utility, which resides in the Guardian file space and is a licensed Guardian code 800 file.
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: $SYSTEM SYSTEM 4> vproc mxcmp You will see something similar to: VPROC - T9617H01 - (01 FEB 2009) SYSTEM \DMR15 Date 18 JAN 2011, 18:51:10 Copyright
Installing NonStop SQL/MX Reviewing and Setting System Defaults Reviewing and Setting System Defaults Before using the installed SQL/MX Release 3.0 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—640325-001 4- 20
5 Planning Database Security and Recovery Database security and recovery are essential topics to consider before creating an SQL/MX database. Planning for security is the primary protection against unauthorized user intervention. Security, however, cannot eliminate errors by authorized users. Planning for recovery is essential for protecting your database.
Planning Database Security and Recovery • • • • Planning Guidelines Managing Permissions for Files in OSS Space on page 11-12 Managing Permissions for Files in Guardian Space on page 11-13 Securing User Modules on page 11-16 Managing Network Security on page 13-11 Planning Guidelines When you plan your SQL/MX database security you should consider: • • • Database Access and Security OSS File and Directory Security Safeguard Security Database Access and Security Access to SQL/MX database objects is gov
Planning Database Security and Recovery • • • • • • OSS File and Directory Security x (execute) for execute the file as a program, or search a directory X for S_IXUSR, S_IXGRP, or S_IXOTH in the “execute” position s for set user ID or set group ID, in the owner or group “execute” position t for test segment (the sticky bit), in the others “execute” position s for an AF_UNIX socket under types - for “regular” file under types Users on the system are classified as one or more of the following: • • • u (
Planning Database Security and Recovery Safeguard Security The super ID can include a umask command in the /etc/profile file to specify the user mask for all users who log on to the shell. An individual user can also include a umask command in his or her profile file to establish a personal user mask. Safeguard Security For additional security protection, use the Safeguard product to restrict access to physical Guardian volumes and subvolumes containing the distributed SQL/MX product component files.
Planning Database Security and Recovery • Safeguard Security Safeguard process-protection records can control who is authorized to use specific process names. Safeguard access control lists cannot be used to protect OSS files. Access to OSS files is controlled by OSS file-permission bits. User Administration Authentication records for all system users, including those who work in the OSS environment, must be added and managed by using SAFECOM USER commands.
Planning Database Security and Recovery Planning Database Recovery Planning Database Recovery HP provides several recovery mechanisms, including: • • • • Mirrored disk volumes are a primary protection against disk failures. These volumes also provide the ability to repair and maintain disk volumes online, without interrupting application processing. For information about how to use mirrored disk volumes, see the Guardian User’s Guide and the Guardian Disk and Tape Utilities Reference Manual.
Planning Database Security and Recovery Database Recovery Guidelines Database Recovery Guidelines When planning backup strategies, consider these guidelines: Topic Guideline SQL/MX subvolume and file naming guidelines In planning database recovery, it is important to properly follow the rules for naming SQL/MX subvolumes and files. For information, see SQL/MX Subvolume Naming Guidelines on page 7-3. Using Guardian names with TMF, RDF, and Measure For SQL/MX Release 3.
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-31. 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 Explicitly backs up the named index. Unless specifically excluded, an index is automatically backed up when you back up its associated catalog or schema. See Backing Up Indices and Index Partitions on page 5-33 Explicitly restores the named index.
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 A daily backup should either be a full backup of all database objects or a limited backup of specific objects. If you use limited daily backups, you should also perform periodic full backups, as explained in Periodic Full Backups. Periodic Full Backups A full backup performed periodically might be adequate for protecting your database.
Planning Database Security and Recovery SQL/MX Objects That Are Backed Up Explicitly You can specify the MX CATALOG backup object to back up one or more catalogs: BR> BACKUP $tape-drive, MX CATALOG mycat1; You can also use it to specify every catalog in the SQL/MX file system. BR> BACKUP $tape-drive, MX CATALOG CAT*; You can specify either CAT or CATALOG in the BACKUP command.
Planning Database Security and Recovery SQL/MX Objects That Are Backed Up Implicitly You cannot use the PARTONLY option with the INDEXES INCLUDED option. Caution. Use caution when using PARTONLY in Backup and Restore 2 operations for partitioned tables. It is possible to make the primary and secondary partitions of a table inconsistent, both with each other and with indexes (for example, by performing DML operations on only one of two backed up partitions before you restore both).
Planning Database Security and Recovery SQL/MX Objects That Are Not Restored by BRCOM SQL/MX Objects That Are Not Restored by BRCOM You cannot use Backup and Restore 2 to restore these SQL/MX objects: • • • • • GRANT and REVOKE privileges Referential integrity constraints SPJs Triggers Views However, except for GRANT and REVOKE privileges, you can capture the DDL for the CREATE statements executed for these objects and use this information to manually re-create these objects after a Restore operation.
Planning Database Security and Recovery Backing Up and Restoring OSS Program Files and SPJs Backing Up and Restoring OSS Program Files and SPJs Backing Up and Restoring Program Executables and User Modules Program executables and user modules in the SQL/MX environment are stored in OSS directories.
Using the RESTORE SHOWDDL ON Option to ReCreate Objects Planning Database Security and Recovery For detailed information about backing up and restoring OSS objects, see the Backup and Restore 2 Manual. Using the RESTORE SHOWDDL ON Option to Re-Create Objects Every backup operation captures the DDL from all SQL/MX CREATE statements that have been executed. This capture always occurs; there is no backup job option to prevent it.
6 Reviewing and Setting System Defaults NonStop SQL/MX uses system-defined default settings for attributes associated with compiling and executing queries. The system-defined default settings, which are hard-coded settings, are optimal in most cases. Under some circumstances, however, you might want to override a specific system-defined default setting.
Reviewing and Setting System Defaults Consequences of Inserting Rows Into the SYSTEM_DEFAULTS Table The statements in this example update the current default setting for the transaction isolation level on \MYSYS: SET SCHEMA nonstop_sqlmx_mysys.system_defaults_schema; UPDATE SYSTEM_DEFAULTS SET ATTR_VALUE = 'READ COMMITTED' WHERE ATTRIBUTE = 'ISOLATION_LEVEL'; The system-defined default settings can be viewed using following query.
Alternative Settings For Selected System Defaults Reviewing and Setting System Defaults Table 6-1. Settings for Selected System Defaults (page 1 of 5) System Default Default Value Alternative Setting CACHE_HISTOGRAMS The default value is ON. The value OFF means histograms are only fetched for columns of a table that are involved in a statement. CACHE_HISTOGRAMS_ REFRESH_INTERVAL The default value is 3600.
Alternative Settings For Selected System Defaults Reviewing and Setting System Defaults Table 6-1. Settings for Selected System Defaults (page 2 of 5) System Default Default Value Alternative Setting NATIONAL_CHARSET The default value is UCS2. The value ISO88591 is appropriate if ISO88591 is the preferred national character set. A value for NATIONAL_CHARSET is set when you run the InstallSqlmx script during installation of NonStop SQL/MX. This value can be changed only by reinstalling NonStop SQL/MX.
Reviewing and Setting System Defaults Alternative Settings For Selected System Defaults Table 6-1. Settings for Selected System Defaults (page 3 of 5) System Default Default Value Alternative Setting QUERY_CACHE_REQUIRED_ PREFIX_KEYS The default value is 255. A value greater than the number of columns in a composite key indicates all columns of the key are required.
Alternative Settings For Selected System Defaults Reviewing and Setting System Defaults Table 6-1. Settings for Selected System Defaults (page 4 of 5) System Default Default Value Alternative Setting REF_CONSTRAINT_NO_ ACTION_LIKE_RESTRICT The default value is SYSTEM. This default affects the behavior of a new referential constraint specified with NO ACTION.
Reviewing and Setting System Defaults Alternative Settings For Selected System Defaults Table 6-1. Settings for Selected System Defaults (page 5 of 5) System Default Default Value Alternative Setting SCRATCH_DISKS_ PREFERRED The default is for the system to determine what disks to use. Set to a list of scratch disk volumes, where each item in the list has the form \node.$volume, and the items in the list are separated by a comma (,).
Settings Required to Achieve ANSI Compliance Reviewing and Setting System Defaults Settings Required to Achieve ANSI Compliance Table 6-2 lists the system default settings required to achieve ANSI compliance. If these settings are used, all except these will have a significant negative impact on the performance of your database: • • • NAMETYPE RECOMPILATION_WARNINGS REF_CONSTRAINT_NO_ACTION_LIKE_RESTRICT Table 6-2.
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 table with an NCHAR column, you receive an error message because 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 A column of an exact numeric type can accept a floating-point number. Also, a column of the FLOAT data type can accept either a floating-point number or an exact numeric 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.
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 ° Align character data types as follows: ° ° ° • • • • • • ° Align ASCII character types on one-byte boundaries. Align UCS2 character types on two-byte boundaries. Align numeric data types as follows: ° ° ° ° Database Design Guidelines for Improving OLTP Performance Align smallint on two-byte boundaries. Align int on four-byte boundaries. Align largeint on eight-byte boundaries.
Creating an SQL/MX Database • Database Design Guidelines for Improving OLTP Performance 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—640325-001 7- 44
8 Querying SQL/MX Metadata Use the commands described in this section to obtain the information you need to manage a database.
Commands for Displaying Information Querying SQL/MX Metadata NonStop SQL/MX stores information about object structures in file labels, resource forks, and metadata, as follows: File labels Contain file structure information and security settings. Use the MXCI SHOWLABEL command to obtain file-label information. Most of the information in file labels is the same as the metadata.
Querying SQL/MX Metadata mxtool INFO Command For detailed information and examples of this command, see the SQL/MX Reference Manual. mxtool INFO Command Use the mxtool INFO command to display information about a database, including SQL/MX-related information for a given Guardian file set list. Because access to metadata is open to all users, anyone can run the mxtool INFO command. For detailed information and examples of this command, see the SQL/MX Reference Manual.
Querying SQL/MX Metadata SHOWDDL Command SHOWDDL Command Use the MXCI SHOWDDL command to display the DDL syntax equivalent to the syntax that was used to create an SQL/MX table, view, or SPJ and its dependent objects. This command also supports SQL/MP alias names. You can choose to display the DDL for the underlying SQL/MP object and its dependent objects either in SQL/MX or in SQL/MP DDL syntax. The command is especially useful for displaying a thorough description of an object as it exists in metadata.
SQL/MX Metadata Tables Querying SQL/MX Metadata SQL/MX Metadata Tables Figure 8-1 shows the SQL/MX metadata table structure. Figure 8-1. SQL/MX Metadata Table Structure Catalog Schemas NONSTOP_SQLMX_nodename SYSTEM_SCHEMA Note: User catalogs are not shown.
Querying SQL/MX Metadata • • • • SQL/MX Metadata Tables SQL/MX metadata tables are quite different from SQL/MP catalogs. Each node contains a single NONSTOP_SQLMX_nodename catalog. This catalog contains metadata pertinent to the node as a whole. Figure 8-1 shows the metadata schemas and catalogs in the NONSTOP_SQLMX_nodename catalog. Typically, a node has additional user catalogs; Figure 8-1 does not show these user catalogs.
Querying SQL/MX Metadata SQL/MX Metadata Tables Figure 8-2.
Querying SQL/MX Metadata SQL/MX Metadata Tables Figure 8-2.
Querying SQL/MX Metadata SQL/MX Metadata Tables Figure 8-2.
Querying SQL/MX Metadata Understanding ANSI External and Internal Names Understanding ANSI External and Internal Names ANSI SQL objects have both external and internal names. When objects are created in NonStop SQL/MX, they are given external names. However, when the same objects are stored in the metadata, they are stored by their internal name. You need to be aware of external and internal naming when performing metadata queries.
Querying SQL/MX Metadata Displaying System Schema Information for read uncommitted access order by object_name; Note that "my "" catalog" is in external format, and 'SALES' and 'my " catalog' are in internal format. The query does not work correctly if the internal and external names are incorrect.
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.
Querying SQL/MX Metadata Locating System Schema Tables EOF: 98304 (0.
Querying SQL/MX Metadata • Locating System Schema Tables This example also uses TACL commands to locate the MXCS schema tables on ZSD1. 1> fup copy $system.zsqlmx.mxanchor SQLMX_MetaData_Loc=$DATA08 2> volume $data08.zsd1 3> files $DATA08.ZSD1 ASSOC200 DTQSFJ00 PX1KHJ00 Z9FPGJ00 ASSOC201 DTQSFJ01 PX1KHJ01 Z9FPGJ01 B7NMCJ00 ENVIRO00 Q4DNDJ00 B7NMCJ01 ENVIRO01 Q4DNDJ01 DATASO00 NAME2I00 RESOUR00 DATASO01 NAME2I01 RESOUR01 $DATA02 ZSDI 9> fup copy $system.zsqlmx.
Querying SQL/MX Metadata Locating System Schema Tables EXTENTS ALLOCATED: 1 INDEX LEVELS: 1 PARTITION ARRAY FORMAT2ENABLED HP NonStop SQL/MX Installation and Management Guide—640325-001 8-15
Querying SQL/MX Metadata • Locating System Defaults Schema Tables This is an example from the OSS prompt: # cat /G/system/zsqlmx/mxanchor SQLMX_MetaData_Loc=$DATA02 # ls /G/data02/zsd0 catref00 catsys00 catref01 catsys01 schema00 schema01 schrep00 schrep01 # /usr/tandem/sqlmx/bin/mxtool info \$data02.zsd0.catsys00 Hewlett-Packard NonStop(TM) SQL/MX MXTOOL Utility 3.0 (c) Copyright 2003, 2004-2010 Hewlett-Packard Development Company, LP. File Name: \DMR15.$DATA02.ZSD0.
Querying SQL/MX Metadata • Locating System Defaults Schema Tables 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: \DMR15.$DATA02.ZSD5VXWZ.M26FB700 AnsiName: NONSTOP_SQLMX_DMR15.SYSTEM_DEFAULTS_SCHEMA.SYSTEM_DEFAULTS AnsiNameSpace: TA ObjectSchemaVersion: 3000 ObjectFeatureVersion: 1200 Owner: SUPER.
Querying SQL/MX Metadata Locating System Defaults Schema Tables or has failed) Primary Extent Size: 128 Pages Secondary Extent Size: 256 Pages Max Extents: 512 Extents Allocated: 1 EOF: 32768 Index Levels: 1 HP NonStop SQL/MX Installation and Management Guide—640325-001 8-18
Querying SQL/MX Metadata • Locating System Defaults Schema Tables 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 System Defaults Schema Tables Hewlett-Packard NonStop(TM) SQL/MX MXTOOL Utility 3.0 (c) Copyright 2003, 2004-2010 Hewlett-Packard Development Company, LP. File Name: \FIGARO.$DATA08.ZSDA984F.FPJCX800 Object Schema Version: 3000 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_dmr15.
Querying SQL/MX Metadata Locating MXCS Schema Tables progress or has failed) RedoNeeded: F RollfwdNeeded: F UndoNeeded: F UnreclaimedSpace: F (If T, a Utility operation is in progress or has failed) Primary Extent Size: 128 Pages Secondary Extent Size: 256 Pages Max Extents: 512 Extents Allocated: 1 EOF: 32768 Index Levels: 1 Locating MXCS Schema Tables On a local node, the anchor file $SYSTEM.ZSQLMX.MXANCHOR contains a pointer to the location of the system catalog.
Querying SQL/MX Metadata Locating MXCS Schema Tables This example uses TACL commands to locate the MXCS schema tables. 1> fup copy $system.zsqlmx.mxanchor SQLMX_MetaData_Loc=$DATA02 1 RECORDS TRANSFERRED 2> volume $data02.zsd1 3> files $DATA02.ZSD1 ASSOC200 NAME2I00 ASSOC201 NAME2I01 DATASO00 DATASO01 ENVIRO00 RESOUR00 RESOUR01 ENVIRO01 4> fup info assoc200, detail $DATA02.ZSD1.ASSOC200 28 Jan 2011, 12:25 SQL ANSI TABLE ANSI NAME NONSTOP_SQLMX_DMR15.MXCS_SCHEMA.ASSOC2DS RESOURCE FORK \DMR15.$DATA02.
Querying SQL/MX Metadata Displaying Catalog Information Displaying Catalog Information This subsection discusses: • • • • • • Displaying a Catalog UID on page 8-23 Displaying All Catalogs Visible on a Node on page 8-24 Determining Whether a Catalog is Local or Remote on page 8-24 Displaying All Schemas in a Catalog on page 8-25 Displaying the Guardian Location of the Metadata Tables for a Catalog on page 8-25 Displaying the Attributes of a Catalog on page 8-26 Related topic: • Displaying All Tables i
Querying SQL/MX Metadata Displaying All Catalogs Visible on a Node Displaying All Catalogs Visible on a Node The CATSYS table contains one (and only one) row for each catalog that is visible on that node. This example displays a list of all the visible catalogs on a local node (where node-name is the name of the local node): >> select cat_name from nonstop_sqlmx _node-name.system_schema.
Querying SQL/MX Metadata Displaying All Schemas in a Catalog Displaying All Schemas in a Catalog This example displays a list of all schemas in a catalog: >> set schema nonstop_sqlmx_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.
Querying SQL/MX Metadata DDLKS000 MODULE00 MVS00000 MVSTIN00 PARTNS00 RIUU0000 TBLPRI00 TRGUSD00 VCOLTC00 VWTBLU00 DDLKS001 MODULE01 MVS00001 MVSTIN01 PARTNS01 RIUU0001 TBLPRI01 TRGUSD01 VCOLTC01 VWTBLU01 Displaying the Attributes of a Catalog DDPLKS00 MPPART00 MVSCOL00 MVSUSE00 REFCON00 ROUTIN00 TBLTEX00 TRIGS000 VCOLU000 DDPLKS01 MPPART01 MVSCOL01 MVSUSE01 REFCON01 ROUTIN01 TBLTEX01 TRIGS001 VCOLU001 KYCLU000 MVRGRP00 MVSJCL00 OBJECT00 REPLIC00 TBLCON00 TRGCU000 VCOLTA00 VIEWS000 KYCLU001 MVRGRP01
Querying SQL/MX Metadata • • • • • • Displaying a Schema UID Locating System Defaults Schema Tables on page 8-16 Displaying All Tables in a Schema on page 8-33 Displaying All Views in a Schema on page 8-38 Displaying the Schema Version on page 8-65 Displaying the System Schema Version on page 8-65 Displaying the Object Schema Version (OSV) on page 8-65 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_krypton.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.
Querying SQL/MX Metadata Displaying All Objects in a Schema Displaying All Objects in a Schema This example selects information from the CATSYS, SCHEMATA, and OBJECTS tables to display a list of all the objects within the SALES schema in the SAMDBCAT catalog. You can use a similar set of SELECT statements to display all the views or indexes on a node. >> set schema samdbcat.definition_schema_version_3000; --- SQL operation complete.
Querying SQL/MX Metadata Displaying the Attributes of a Schema ODETAIL ODETAIL_545675672_5188 ODETAIL_793765672_5188 ORDERS ORDERS_347711272_5188 ORDERS_917521272_5188 ORDREP PARTS PARTS_386351772_5188 PARTS_836161772_5188 XCUSTNAM XORDCUS XORDREP XPARTDES TA CN CN TA CN CN TA TA CN CN IX IX IX IX BT PK NN BT NN PK VI BT NN PK IX IX IX IX User Table User Table User Table User Table User Table User Table User Table User Table User Table User Table User Table User Table User Table User Table Displaying
Querying SQL/MX Metadata Displaying Table Information >> select substring(schema_name, 1, 30) as schema_name, schema_owner from schemata s, catsys c where cat_name = 'SAMDBCAT' and s.cat_uid = c.
Querying SQL/MX Metadata Displaying All Tables in a Catalog Displaying All Tables in a Catalog This example displays all base table objects in the catalog SAMDBCAT: >> set schema samdbcat.definition_schema_version_3000; --- 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.
Querying SQL/MX Metadata VWS VW_COL_TBL_COLS VW_COL_USAGE VW_TBL_USAGE HISTOGRAMS HISTOGRAMS_FREQ_VALS HISTOGRAM_INTERVALS MVS_TABLE_INFO_UMD MVS_UMD MVS_USED_UMD PARTLOC PARTSUPP SUPPLIER T1 DEPT EMPLOYEE HISTOGRAMS HISTOGRAMS_FREQ_VALS HISTOGRAM_INTERVALS JOB MVS_TABLE_INFO_UMD MVS_UMD MVS_USED_UMD PROJECT CUSTOMER HISTOGRAMS HISTOGRAMS_FREQ_VALS HISTOGRAM_INTERVALS MVS_TABLE_INFO_UMD MVS_UMD MVS_USED_UMD ODETAIL ORDERS PARTS Displaying All Tables in a Schema DEFINITION_SCHEMA_VERSION_3000 DEFINITION_S
Querying SQL/MX Metadata Displaying All DDL Locks on a Table where c.cat_uid = s.cat_uid and c.cat_name = 'SAMDBCAT' and s.schema_uid = o.schema_uid and s.schema_name = 'SALES' and o.
Querying SQL/MX Metadata Displaying All DDL Locks on a Table ) for read uncommitted access order by l.object_uid; LOCK_NAME ------------------------CUSTOMER_LOCK UTIL ---IM STATUS (EXPR) ----------- ---------------------4 \DMR15.
Querying SQL/MX Metadata Displaying the Attributes of a Table Displaying the Attributes of a Table This example displays some of the attributes of the base tables defined in the SAMDBCAT.SALES schema: >> set schema samdbcat.definition_schema_version_3000; --- SQL operation complete.
Querying SQL/MX Metadata Displaying All Tables on a System Displaying All Tables on a System Displaying all tables on a node (system) is a two-step process. To display all tables on a node, you must: 1. Run a query that displays all the catalogs on a node (see Displaying All Catalogs Visible on a Node on page 8-24). 2. Run a query for each catalog that displays all the tables for that catalog (see Displaying All Tables in a Catalog on page 8-32).
Querying SQL/MX Metadata Displaying All Views in a Schema Displaying All Views in a Schema This example displays all views in the schema SALES in the catalog SAMDBCAT: >>set schema samdbcat.definition_schema_version_3000; --- SQL operation complete. >>select substring (object_name, 1, 25) as view_name, object_name_space as name_space from nonstop_sqlmx_dmr15.system_schema.catsys c, nonstop_sqlmx_dmr15.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-39 Displaying All Attributes of an SQL/MP Alias Name on page 8-40 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_3000; --- 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-46 Displaying All Columns in an Index on page 8-54 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.
Querying SQL/MX Metadata Displaying All Indexes for a Table 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_3000; --- SQL operation complete. >> select substring (o1.object_name, 1, 40) as index_name from samdbcat.definition_schema_version_3000.access_paths a, samdbcat.definition_schema_version_3000.objects o1 where a.
Querying SQL/MX Metadata Displaying All DDL Locks on an Index Displaying All DDL Locks on an Index This example (when run on the node on which the metadata is located) displays the DDL locks on the index SAMDBCAT.SALES.XCUSTNAM: >> set schema samdbcat.definition_schema_version_3000; --- 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.
Querying SQL/MX Metadata Displaying the Attributes of an Index Displaying the Attributes of an Index This example (when run on the node on which the metadata is located) displays some of the attributes for the index SAMDBCAT.SALES.XCUSTNAM: >> set schema samdbcat.definition_schema_version_3000; --- 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_3000; --- 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_3000.access_paths a, samdbcat.
Querying SQL/MX Metadata Displaying Partition Information Displaying Partition Information Topics in this subsection: • • Displaying All Partitions for a Table or Index on page 8-46 Displaying Attributes of a Partition on page 8-47 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.
Querying SQL/MX Metadata Displaying Attributes of a Partition the MXCI SHOWLABEL command with the DETAIL option to display a list of partitions for a table or an index. For examples of the SHOWDDL and SHOWLABEL commands, see the SQL/MX Reference Manual. Displaying Attributes of a Partition This example displays attributes for the partitions for the table SAMDBCAT.SALES.CUSTOMER. To display additional fields, select different fields from the PARTITIONS table: >> set schema samdbcat.
Querying SQL/MX Metadata Displaying Constraint Information Displaying Constraint Information Topics in this subsection: • • • Determining Whether a Table Has Constraints on page 8-48 Displaying All Constraints on a Table on page 8-49 Displaying the Attributes of a Constraint on page 8-50 Related topics: • • • Displaying All Columns in a Primary Key or Unique Constraint on page 8-55 Displaying All Columns in a NOT NULL Constraint on page 8-56 Displaying All Columns in a Referential Integrity Constrain
Querying SQL/MX Metadata Displaying All Constraints on a Table ) ) 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_3000; --- SQL operation complete. >> select substring (o1.object_name, 1, 30) as constraint_name, case (t.
Querying SQL/MX Metadata Displaying the Attributes of a Constraint Displaying the Attributes of a Constraint This example displays some of the attributes of the constraints in the table SAMDBCAT.SALES.CUSTOMER. To display additional fields, select the fields from the TBL_CONSTRAINTS table: >> set schema samdbcat.definition_schema_version_3000; --- SQL operation complete. >> select t.constraint_uid, t.table_uid, case t.
Querying SQL/MX Metadata • • • Displaying Column Information Displaying All Columns in a Referential Integrity Constraint on page 8-56 Displaying the Attributes of a Column on page 8-58 Displaying All Tables Containing a Selected Column on page 8-59 Related topic: • Displaying All Privileges for a Column on page 8-63 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.
Querying SQL/MX Metadata Displaying All Columns in a Table Displaying All Columns in a Table This example displays all columns in the table SAMDBCAT.PERSNL.EMPLOYEE: >> set schema samdbcat.definition_schema_version_3000; --- 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_dmr15.system_schema.
Querying SQL/MX Metadata Displaying All Columns in a View Displaying All Columns in a View This example displays all columns in the view SAMDBCAT.SALES.CUSTLIST: >> set schema samdbcat.definition_schema_version_3000; --- 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_dmr15.system_schema.
Querying SQL/MX Metadata Displaying All Columns in an Index Displaying All Columns in an Index This example displays all columns in the index SAMDBCAT.SALES.XCUSTNAM in the schema SAMDBCAT.SALES: >> set schema samdbcat.definition_schema_version_3000; --- 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.
Querying SQL/MX Metadata Displaying All Columns in a Primary Key or Unique Constraint Displaying All Columns in a Primary Key or Unique Constraint This example displays all columns with primary key or unique constraints for the table SAMDBCAT.SALES.ODETAIL; two columns of this table make up the primary key: >> set schema samdbcat.definition_schema_version_3000; >> select substring(c.column_name, 1, 15) as column_name, c.column_number as col_num, case t.
Querying SQL/MX Metadata Displaying All Columns in a NOT NULL Constraint Displaying All Columns in a NOT NULL Constraint This example displays all columns with NOT NULL constraints for the table SAMDBCAT.SALES.ODETAIL: >>set schema samdbcat.definition_schema_version_3000; >> select substring(c.column_name, 1, 15) as column_name, c.column_number as col_num, case o2.
Querying SQL/MX Metadata Displaying All Columns in a Referential Integrity Constraint >> select substring(c.column_name, 1, 15) as column_name, c.column_number as col_num, r.update_rule, substring(o2.object_name , 1, 24) as constraint_name from cols c, objects o1, objects o2, key_col_usage k, ref_constraints r, tbl_constraints t where c.object_uid = t.table_uid and k.constraint_uid = t.constraint_uid and r.constraint_uid = t.constraint_uid and k.column_number = c.column_number and t.table_uid = o1.
Querying SQL/MX Metadata Displaying the Attributes of a Column Displaying the Attributes of a Column This example displays some of the attributes of the columns in the table SAMDBCAT.PERSNL.EMPLOYEE. To display additional attributes, select different fields from the COLS table: >> set schema samdbcat.definition_schema_version_3000; --- 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-61 Displaying All Privileges for a View on page 8-62 Displaying All Privileges for a Column on page 8-63 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.
Querying SQL/MX Metadata Displaying All Privileges for a Table Displaying All Privileges for a Table This example displays all privileges for the table SAMDBCAT.SALES.ORDERS: >>set schema samdbcat.definition_schema_version_3000; --- SQL operation complete.
Querying SQL/MX Metadata Displaying All Privileges for a View Displaying All Privileges for a View This example displays all privileges for the view SAMDBCAT.SALES.CUSTLIS: >>set schema samdbcat.definition_schema_version_3000; --- SQL operation complete.
Querying SQL/MX Metadata Displaying All Privileges for a Column 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_3000; --- SQL operation complete.
Querying SQL/MX Metadata Displaying Object Integrity and Consistency Displaying Object Integrity and Consistency The mxtool VERIFY command detects and reports inconsistencies between the information contained in the metadata, the resource forks, and the file labels. It does not verify data integrity. This command is often used together with the mxtool INFO command, which displays various aspects of a database.
Querying SQL/MX Metadata Displaying the Schema Version Displaying the Schema Version This example displays the version number of a specified schema: >> select s.schema_version from nonstop_sqlmx_node.system_schema.schemata s, nonstop_sqlmx_node.system_schema.catsys c where c.cat_name = 'catalog-name' and c.cat_uid = s.cat_uid and s.schema_name = 'schema-name'; Displaying the System Schema Version This example displays the version number of the system schema: >> select s.
Querying SQL/MX Metadata Displaying the Object Feature Version (OFV) and s.schema_uid = o.schema_uid and o.object_name = 'object-name' and o.
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 Objects to an SQL/MX Database Adding, Altering, and Dropping SQL/MX Database Objects 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 Constraints Adding, Altering, and Dropping SQL/MX Database Objects 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.
Altering Objects in an SQL/MX Database Adding, Altering, and Dropping SQL/MX Database Objects 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.
Dropping Objects From an SQL/MX Database Adding, Altering, and Dropping SQL/MX Database Objects 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 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_3000; 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.
Reorganizing SQL/MX Tables and Maintaining Data Using MODIFY to Manage Range-Partitioned Tables and Indexes schema_name = 'SALES' and cat_uid = (select cat_uid from nonstop_sqlmx_dmr15.system_schema.catsys where cat_name = 'SAMDBCAT' ) ) for read uncommitted access order by l.object_uid; LOCK_NAME ----------------------CUSTOMER_LOCK UTIL ----IM STATUS ----------4 PROCESS_ID ---------------\DMR15.$:2:619:1565916 As shown in the example, the query can return DDL lock name and process ID information.
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.
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 2. The value of some floating-point numbers may not be exactly the same (though very close). This is due to an internal conversion to binary format and then back to floating point ASCII format for logging to the error log file. 3. Any string which includes the field delimiter, the row delimiter, or the field-qualifier character may appear differently.
Reorganizing SQL/MX Tables and Maintaining Data Recommended Practices for Improving import Performance If the table is not empty or has dependent indexes, import continues with the normal load operation using TMF transactions. For more information about import command features and options, see the SQL/MX Reference Manual.
Reorganizing SQL/MX Tables and Maintaining Data Recommended Practices for Improving import Performance 1. Make sure the data in the input file is presorted according to the clustering key order if a clustering key is present. Presorting the input file data prevents external sorts and extra block splits. When DP2 inserts unordered data into a partition, import performance is slowed. 2. Use the CONTROL QUERY DEFAULT statement to set the UPD_ORDERED attribute to OFF.
Reorganizing SQL/MX Tables and Maintaining Data Recommended Practices for Improving import Performance If you are running NonStop SQL/MX on a single node, you have these options for using import with range-partitioned tables: • • Put the input data into several files, each of which is destined for a different partition. Run an import instance for each partition. Put the input data into several files, each of which is destined for all the partitions that reside on a particular disk.
Reorganizing SQL/MX Tables and Maintaining Data Using import to Load Partitions Managing Indexes to Improve import Performance A given table with one or more indexes takes much longer to load with import than does the same table without indexes. When you perform an import operation on an SQL/MX table with indexes, you should first drop all indexes, and then re-create them after the operation completes: 1. Perform a SHOWDDL command on the table and save the SHOWDDL output. 2. Drop all indexes on the table.
Reorganizing SQL/MX Tables and Maintaining Data Using import to Load Partitions can also specify the start and end positions of the range partition based on the key column data in the input file. However, you cannot use these options to specify an actual key value at the command line. • For hash-partitioned tables, import loads all hash partitions in the destination table in a single operation.
Reorganizing SQL/MX Tables and Maintaining Data • Support for restarting import Run multiple instances of the import command—one for each partition in the destination table—to load data into a partitioned table by using a separate input file for each partition. Each of the input files contains the data for each partition. For example, you might specify the three import commands for the partitioned EMPLOYEE table as: /usr/bin:import corpcat.persnl.employee -I empfile1 -T 500 /usr/bin:import corpcat.
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.
Reorganizing SQL/MX Tables and Maintaining Data DUP versus FASTCOPY the fastcopy operation. Because the source index SI2 has no equivalent target index, it does not participate in the fastcopy operation. FASTCOPY TABLE ST TO TT INDEXES EXPLICIT; The following command copies all the rows from source index SI1 to the target index TI1. FASTCOPY INDEX SI1 to TI1; The base table TT can be accessed only after completing the fastcopy operation (copying of indexes SI1 to TI1).
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 Any active transaction that does transaction work on a data volume incurs a unilateral abort when the CPU for the primary DP2 process fails. This is fundamental to high performance with audited tables and is true for Enscribe, NonStop SQL/MP, and NonStop SQL/MX. The transaction abort is initiated by the backup DP2 process during an unplanned takeover. TMF might also initiate an abort for some transactions.
Managing Database Applications SQL/MX and SQL/MP Differences in Recovery Action for Read-Only Queries the WHERE clause of the SELECT statement. Using this technique, the program can reopen the cursor and continue the query from the point where the error was encountered.
Managing Database Applications Reasons Not to Move a Program With Compiled Modules However, there are few circumstances under which you can compile a program’s modules on a development system and then move the program and modules to a production system without recompiling the modules there. See Reasons Not to Move a Program With Compiled Modules on page 11-4.
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 For more information about generating locally placed modules or globally placed modules in a C, C++, or COBOL programming environment, including full command syntax examples, see the SQL/MX Programming Manual for C and COBOL. Securing User Modules Because the SQL/MX executor runs in privileged mode and runs query plans in the compiled user modules, it is important to properly control access to globally placed modules and locally placed modules.
Managing Database Applications • Securing User Modules Performs this OSS command: chmod a+rwxt /usr/tandem/sqlmx/USERMODULES After InstallSqlmx runs, you might want to perform some or all of these tasks: 1. To make sure that the sticky bit of the USERMODULES directory has been turned on, enter: ls -ld /usr/tandem/sqlmx/USERMODULES The output should look like this: drwxrwxrwt 1 SUPER.SUPER SUPER 4096 Apr 1 06:26 /user/tandem/sqlmx/USERMODULES/ 2. Only the SUPER.
Managing Database Applications Securing User Modules Proper security of the local directory requires that: • • • The owner of the local directory be a trusted user. The sticky bit of the local directory be set. The compiled locally placed modules be writable only by the owner. To secure the locally placed module directory: 1. Create the local directory by issuing the OSS command: mkdir -p /usr/local-directory 2.
Managing Database Applications Checking Module Dependencies with DISPLAY USE OF 8. Make sure that all future modules that you create are writable only by you or the owner of the local directory owner by entering: umask 022 This command makes module files (and OSS files in general) that the user creates readable by all but writable only by the module owner and creator. 9.
Managing Database Applications Checking Module Dependencies with DISPLAY USE OF Displaying All Modules and Dependent Objects When you omit the module clause and the object clause, DISPLAY USE OF returns information about all modules and dependent objects. >>DISPLAY USE OF; Module: Object: Object: Object: Object: Object: CAT.SCH.CONSTRAINTM CAT.SCH.T24 CAT.SCH.T25 CAT.SCH.T26 CAT.SCH.T27 CAT.SCH.T28 Module: Object: Object: Object: Object: CAT.SCH.CURSOMEM \NODE1.DATA10.ORDERS.T01 CAT.SCH.T02 CAT.SCH.
Managing Database Applications Module: Object: Object: Object: Object: Object: Checking Module Dependencies with DISPLAY USE OF CAT.SCH.CONSTRAINTM CAT.SCH.T24 CAT.SCH.T25 CAT.SCH.T26 CAT.SCH.T27 CAT.SCH.T28 Display source SQL file name for a given module >>DISPLAY USE OF SOURCE 'CAT.ALL.MODULE1'; Module: CAT.ALL.MODULE1 Source Name: /E/NODE11/usr/user2/constraint.
Managing Database Applications Checking Module Dependencies with DISPLAY USE OF Display source SQL file for a given module in a given OSS directory >>DISPLAY USE OF MODULE_DIR '/usr/user1/all/modulestorage' SOURCE 'CAT.ALL.MODULE1'; Module: CAT.ALL.MODULE1 Source Name: /E/NODE11/usr/user2/constraint1.sql Display all modules and their corresponding source SQL files in a given OSS directory >>DISPLAY USE OF MODULE_DIR '/usr/user1/all/modulestorage' SOURCE; Module: CAT.ALL.
Managing Database Applications Grouping Applications and Modules to Run Multiple DISPLAY USE OF Operations pattern you specified in the MODULE_DIR clause. If modules that match the pattern are not found in the local directory, an error is returned and searching does not extend beyond the local directory. For more information about the syntax of the MODULE_DIR clause of DISPLAY USE OF, see the SQL/MX Reference Manual.
Managing Database Applications Grouping Applications and Modules to Run Multiple DISPLAY USE OF Operations ----- End of File Processing Checks ----0 errors, 0 warnings in SQL C file "/user/smith/tmp/tmp/DUO/t01.sql" 2. Repeat Step 1 for t02.sql and t03.sql: % mxsqlc t02.sql -g moduleGroup=INVENTORYAPP % mxsqlc t03.sql -g moduleGroup=INVENTORYAPP 3. Process the applications to generate annotated application executables, referring to the SQL/MX Programming Manual for C and COBOL for details.
Managing Database Applications Grouping Applications and Modules to Run Multiple DISPLAY USE OF Operations 7. Check the SQL compiled module files using SHIPMENTAPP: % ls /usr/tandem/sqlmx/USERMODULES/*SHIPMENTAPP^*^^ /usr/tandem/sqlmx/USERMODULES/T01CAT.T01SCH.SHIPMENTAPP^T11MO D^^ /usr/tandem/sqlmx/USERMODULES/T01CAT.T01SCH.SHIPMENTAPP^T12MO D^^ 8. Use DISPLAY USE OF to display the modules and SQL/MX objects used by the inventory applications: >>display use of module '*.*.
Managing Database Applications Removing Modules 10. Perform Step 9 for the remainder of the applications: % % % % /user/smith/tmp/tmp/DUO: /user/smith/tmp/tmp/DUO: /user/smith/tmp/tmp/DUO: /user/smith/tmp/tmp/DUO: mxCompileUserModule mxCompileUserModule mxCompileUserModule mxCompileUserModule -g -g -g -g moduleLocal moduleLocal moduleLocal moduleLocal t02.exe t03.exe t11.exe t12.exe 11. Verify how the local modules are generated: /user/smith/tmp/tmp/DUO: ls -l *.*.
Managing Database Applications Converting Globally Placed Modules to Locally Placed Modules Before you remove a module file: 1. Verify that no other applications use modules that you will remove. 2. Verify that the modules (or the application source files) have been backed up. This gives you the option of restoring or rebuilding modules at a later time. To remove a module from its directory, use the OSS rm command. For example, to delete the globally placed module file cat.sch.
Managing Database Applications Converting Globally Placed Modules to Locally Placed Modules You can convert applications that use globally placed modules to those that use locally placed modules in these environments: • • Converting Applications in a Development Environment on page 11-28 Converting Applications in a Production Environment on page 11-28 Converting Applications in a Development Environment To build the application in a development environment as if it were a new version of the applicatio
Managing Database Applications Managing Module Files and Their Applications During Fallback From SQL/MX Release 3.0 The -g moduleLocal option of mxCompileUserModule generates a locally placed module in the temporary directory. Using the temporary directory prevents disruptions to the application and prevents overwriting of any existing modules. c. Note the names of the module files that are generated in the temporary directory. d. Delete the contents of the temporary directory.
Managing Database Applications Backing Up and Restoring Programs locations (such as another disk, magnetic tape, or another system). As a rule, you should back up programs before you make major changes to the database. For information about the strategies and procedures for backing up and restoring database files, see Using Backup and Restore 2 for SQL/MX Database Recovery on page 5-27.
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.
Performing Recovery Operations Recovering Tables b. Use CTOEDIT to transfer this file to the Guardian space: $DATA01 SUBVOL 15>ctoedit ddlfile,employee c. Use MXGNAMES with the -showddl option, specifying the target Guardian file from Step b: $DATA01 SUBVOL 16>$system.zmxtools.mxgnames showddl=employee -tmf (--Table HRDATA.PERSNL.EMPLOYEE--& $DATA10.ZSDT5356.NG59FJ*,& --Index XEMPNAME ON HRDATA.PERSNL.EMPLOYEE--& $DATA10.ZSDT5356.JN9VFJ*,& --Index XEMPDEPT ON HRDATA.PERSNL.EMPLOYEE--& $DATA10.ZSDT5356.
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.
Performing Recovery Operations >>> >>> Recovering Tables $DATA10.ZDST5356.NPFH8J01), INVALID OFF,& RELEASED OFF, SERIAL 70 Note. If, for any reason, the dumps were completely removed from the TMF catalog, you would need to add them again, using the TMFCOM ADD DUMPS command. In this command, you would also set the INVALID and RELEASED attributes to OFF. 8. Recover the table and its indexes. Caution. Objects to be recovered are spread across different disk volumes and subvolumes.
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 Indices and Index Partitions You can restore one or more table partitions, either implicitly, by restoring associated tables, or explicitly, by using the PARTITION restore object: BR> RESTORE $tape-drive, MX (TPART cat1.sch1.table1 PARTITION (part1)), PARTONLY ON; BR> RESTORE $tape-drive, MX (TPART cat1.sch1.table1 PARTITION (part1, part2)) BR+>, PARTONLY ON; Caution. Use care when using the PARTONLY object in Backup and Restore 2 operations for partitioned tables.
Performing Recovery Operations • • • • Repairing Damaged SQL/MX Metadata and Objects Referential integrity constraints SPJs Triggers Views For information about MXCI OBEY command files, see the SQL/MX Reference Manual. To use the EDIT files as MXCI OBEY command files, you must first modify them slightly. You must qualify the locations of objects specified in each CREATE command to indicate the catalog and table associated with the object.
Performing Recovery Operations • • • Strategies for Repairing Damaged Metadata and Objects Section 5, Planning Database Security and Recovery Section 8, Querying SQL/MX Metadata The SQL/MX Reference Manual FIXUP Use the mxtool FIXUP command to perform limited repair operations that enable you to fix problems, such as inconsistencies between metadata and labels, by adjusting the label or resource fork. FIXUP does not repair all problems with inconsistencies, however.
Performing Recovery Operations Identifying Problem Data and Objects VERIFY Use the mxtool VERIFY utility to check consistency within the database between metadata, resource forks, and file labels. For information about using VERIFY, see Using VERIFY to Check Data Consistency on page 12-30. Correcting Problems Manually You can correct some problems in metadata directly by manually executing inserts, updates, selects, and deletes.
Performing Recovery Operations Identifying Problem Data and Objects Table 12-1. Label Data Associated With SQL/MX Objects (page 1 of 2) Label Field Contents Data fork name This name matches the external file label name and is of the form ZSDXXXXXX.YYYYYY00. Resource fork name This name is similar to the data fork name, except it ends in “01” (for example, ZSDXXXXXX.YYYYYY01). File code The file code that is used by NonStop SQL/MX. Unlike with SQL/MP, you cannot choose your own file codes.
Performing Recovery Operations Identifying Problem Data and Objects Table 12-1. Label Data Associated With SQL/MX Objects (page 2 of 2) Label Field Contents Maximum packed record length The maximum byte-length of packed records. Flags Flags that indicate whether the object is an SQL/MX format object, and whether the object is a resource fork, audited, in the middle of a DDL operation, left in the crash-open state, broken, offline, or SQLsecured.
Performing Recovery Operations Identifying Problem Data and Objects Table 12-2. Run-time metadata Stored in the Resource Fork Run-time metadata Contents Constraint information Contains entries for droppable check constraints. This information is needed for similarity-checking to determine whether plans need to be recompiled because of a new or deleted check constraint. Trigger information Indicates whether or not a trigger is enabled.
Performing Recovery Operations • Identifying Problem Data and Objects ° ANSI name. The ANSI name stored in the resource fork must match that stored in metadata. ° ANSI namespace. The ANSI namespace stored in the resource fork must match that stored in metadata. ° Partition map. The number of partitions defined in the PARTITIONS table must match the number of partitions in the partition map array in the resource fork. ° Version information.
Performing Recovery Operations • • • • Using FIXUP to Correct Problem Data and Objects You must be logged on using the super ID, be owner of the schema where the object being verified resides, or have SELECT privilege on the object being verified. VERIFY obtains read-only locks on metadata while verifying an object. Other operations that read metadata can run concurrently.
Performing Recovery Operations Using FIXUP to Correct Problem Data and Objects While performing database operations, information between metadata, DP2 labels, and resource forks can become inconsistent. For example, a failed TMF transaction might leave a file in the broken state, or a software bug might not set the redefinition timestamp correctly. When such an inconsistency is detected, it must be fixed to guarantee proper execution of NonStop SQL/MX.
Performing Recovery Operations • • • • Using FIXUP to Correct Problem Data and Objects Audit bit Broken bit Corrupt bit Redefinition timestamp Changing the Audit Bit SQL/MX tables are always created as audited. In some cases, you might need to use FIXUP to turn off the audit bit to run a low-level DP2 utility operation, such as TANDUMP.
Performing Recovery Operations Examples of Using FIXUP For example, this command creates table A with three partitions: Create table tableA (col1 int not null primary key, col2 char (20)) Partition (add first key (1000) location $vol1, add first key (2000) location $vol2); Table A also has a dependent index that contains two partitions: Create index tableA_index1 on tableA (col2) Partition (add first key ("m" location $vol1)); The three partitions on tableA must have the same redefinition timestamp.
Performing Recovery Operations • • • Examples of Using FIXUP \LOCAL.$DATA02.ZSD1123U.SUEIFO00 \LOCAL.$DATA01.ZSD1123U.SUEIGO00 \REMOTE.$DATA01.ZSD1123U.SUEIHO00 CREATE TABLE cat.sch.table1 (Col1 int not null primary key, Col2 int not null, Col3 timestamp not null no default, Col4 char (20)) Partition (Add first key (1000) location \local.$data01 name part2, Add first key (2000) location \remote.$data01 name part3) Location \local.$data02 name part1; 2.
Performing Recovery Operations Using GOAWAY to Delete Damaged Objects Example 2: Correcting Mismatched Timestamps Several of the timestamps on table1 do not match the value on the label. To fix the problem, change the redefinition timestamps: mxtool FIXUP TABLE cat.sch.table1 -rt -d Hewlett-Packard NonStop(TM) SQL/MX MXTOOL Utility 3.0 (c) Copyright 2003, 2004-2010 Hewlett-Packard Development Company, LP.
Performing Recovery Operations ALTER DISK, ALTNAME and ALTER DISK, VOLNAME store production SQL/MX database files. Use the drive for a test database or for temporary space for sort files. You must ensure that you can make the volume inactive and revive the drive back to its original mirrored configuration with no effect on the original database. Use the ABORT DISK command on a volume to put a particular volume out of operation.
Performing Recovery Operations ALTER DISK, LABEL 2. Restore the files to the disk volume, mapping the old volume names to the new volume names in the RESTORE command. 3. Map the objects to the new catalogs. 4. Verify the database by using the mxtool VERIFY utility. See the SQL/MX Reference Manual for command syntax and functionality. 5. SQL compile all programs with new DEFINEs to revalidate the programs. For more information, see the SQL/MX Programming Manual for C and COBOL. ALTER DISK, LABEL Caution.
Performing Recovery Operations START DISK and STOP DISK labels the disk. To recover an initialized disk volume, follow the steps in ALTER DISK, LABEL on page 12-39. START DISK and STOP DISK Use the STOP DISK command on a mirrored volume pair to make one half of the pair inactive. The active disk drive of the mirrored pair continues to maintain the current database, without the protection of mirroring.
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 3.0, 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 3.0 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 3.0 • • • • • 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 an SQL/MX Distributed Database • Managing Network Security The CAT_REFERENCES table on \N2 and \N3 contains two rows for the catalog CAT1, one for \N1 and another for the local node.
Managing an SQL/MX Distributed Database Maintaining Local Autonomy in a Network for SQL/MX Release 3.0 Users of a distributed node must have remote passwords for remote network access. The access privileges assigned by GRANT to users for SQL/MX objects on the local node also apply to those users when the objects are located on a remote node or are accessed from a remote node to which a user has remote access authorization. Maintaining Local Autonomy in a Network for SQL/MX Release 3.
Managing an SQL/MX Distributed Database • Maintaining Local Autonomy in a Network for SQL/MX Release 3.0 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 3.0 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.
Managing an SQL/MX Distributed Database Maintaining Local Autonomy in a Network for SQL/MX Release 3.0 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.
Managing an SQL/MX Distributed Database Maintaining Local Autonomy in a Network for SQL/MX Release 3.0 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.
Managing an SQL/MX Distributed Database Maintaining Local Autonomy in a Network for SQL/MX Release 3.0 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 3.0 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.
Managing an SQL/MX Distributed Database Creating a Distributed SQL/MX Database Table 13-6. Consequences of Network Node Loss, Reconfiguration 3 Node CAT_1 and CAT_2 User Data Present? CAT_1 and CAT_2 Metadata Present? CAT_1 and CAT_2 Applications Present? \A Yes Yes for CAT_1 Yes, accesses CAT_1 and CAT_2 From nodes \B and \C, you cannot perform DDL operations on objects partitioned across node \A.
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 query plans and modules as these are outside the range of the oldest supported plan version (3000). 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 3.0 nodes and SQL/MX Release 2.x 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.
Measuring Performance SHOWLABEL Command SHOWLABEL Command The MXCI SHOWLABEL command displays file-label information for SQL/MX objects. (SHOWLABEL does not support SQL/MP objects or SQL/MP aliases.) For performance statistics, use SHOWLABEL to determine index levels and extent information. This example returns detailed information about the EMPLOYEE table. Use the DETAIL option to display information about partitions and indexes.
Measuring Performance SET STATISTICS and DISPLAY STATISTICS Commands has failed) Primary Extent Size: 16 Pages Secondary Extent Size: 64 Pages Max Extents: 160 Extents Allocated: 1 EOF: 12288 Index Levels: 1 Record Expression Label Length: 13536 Security Label Length: 120 Key Encode Length: 2 Key Columns: 0 ASC Partitioning Scheme: RP Partition Array - 1 partition[s] IndexMap Array - 2 index[es] Index[0]: \DMR15.$SYSTEM.ZSDG5WVN.W3LB7T00 Index columns: 2 ASC , 1 ASC , 0 ASC Index[1]: \DMR15.$SYSTEM.
Measuring Performance SET STATISTICS and DISPLAY STATISTICS Commands SET STATISTICS and DISPLAY STATISTICS provide statistics for statements made against either SQL/MP or SQL/MX objects. To obtain statistics, use either of these commands in your MXCI session: Command Type this command. . . SET STATISTICS ON; Before a statement DISPLAY STATISTICS; After a statement Suppose that this is the last DML command issued: SELECT * FROM SAMDBCAT.SALES.
Measuring Performance Measure Performance Measurement Tool You can display statistics for this statement by using the DISPLAY STATISTICS command: DISPLAY STATISTICS; Start Time End Time Elapsed Time Compile Time Execution Time Table Name 2003/12/11 18:18:24.489 2003/12/11 18:18:24.677 00:00:00.188 00:00:00.046 00:00:00.142 Records Accessed SAMDBCAT.PERSNL.
Measuring Performance Measure Performance Measurement Tool You can collect performance statistics for SQL/MX objects by using these Measure entities: • • • SQLPROC provides information about an SQL process. There is one SQLPROC counter record per SQL process selected. SQLSTMT provides information about all SQL statements within an SQL process. There is one SQLSTMT counter record per SQL statement of a selected SQL process.
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.
Measuring Performance SQL/MX Measurement Models HP NonStop SQL/MX Installation and Management Guide—640325-001 14-12
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 Managing SQL/MX Buffer Space Managing SQL/MX Buffer Space This subsection contains this information: • • • • • • How DP2 Manages and Reuses Query Plan Fragments on page 15-9 Causes and Symptoms of Query Plan Fragment Reuse Failures on page 15-9 Reduction of Plan Fragment Size for Unique Queries on page 15-10 Using SCF STATS DISK to Monitor SQL/MX Statistics and Reuse Failures on page 15-10 Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute on page 15-12 Strate
Enhancing SQL/MX Database Performance How DP2 Manages and Reuses Query Plan Fragments cache function. For information about configuring the SQL/MX buffer, see Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute on page 15-12. How DP2 Manages and Reuses Query Plan Fragments A query plan is created whenever an SQL statement is compiled. Depending on the statement, the query plan can execute against a single nonpartitioned table, several partitions of a table, or several tables in a multi-table join.
Enhancing SQL/MX Database Performance • • Reduction of Plan Fragment Size for Unique Queries Planned or unplanned takeovers by the backup DP2 CPU have generated reuse failures. A planned takeover, such as from using SCF PRIMARY or by automatic switch due to a path error, checkpoints only SQL/MX session data that is in use at the time of the takeover. An unplanned takeover, such as from primary CPU failure or softdown failure, ensures that there will be no SQL/MX session data in the new primary CPU.
Enhancing SQL/MX Database Performance In Use................... Using SCF STATS DISK to Monitor SQL/MX Statistics and Reuse Failures 53621 Failed FST....... Failed ID........ 12 1929 SCF - T9082H01 - (04DEC06) (15NOV06) - 12/06/2010 17:52:01 System \DMR11 (C) 1986 Tandem (C) 2006 Hewlett Packard Development Company, L.P. (Invoking \DMR11.$SYSTEM.SYSTEM.SCFCSTM) 1-> stats disk $*,sqlmx STORAGE - Stats DISK \DMR11.$SYSTEM SQL/MX Statistics: Session Data bytes....... 81920 KB Total Sessions...........
Enhancing SQL/MX Database Performance Using SCF ALTER DISK to Resize the SQLMXBUFFER Attribute 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. For example, an unsuccessful attempt might result because one or more 4 KB blocks of the query fragment space has been stolen by another query. When this happens, the missing query plan must be reloaded.
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
Index B environment 1-1 moving programs from development to production 11-3 moving to remote node 11-8 reasons not to move a program with compiled modules 11-4 running on a remote node 11-9 writing to recover from temporary service organizations 11-1 Archiving SQL/MX objects 5-30 ASCII character set 7-23 Attributes altering 9-23 column, specifying 7-32 Audit bit, changing 12-34 Audit trails 12-2 AUDITCOMPRESS attribute altering index attributes 9-20 altering table attributes 9-24 audit trail data 7-22 Aud
Index C BROKEN flag 12-33 Broken partitions, recovering 12-33 Buffer size, effect on query performance 15-14 C C preprocessor 3-17 Cache size 15-14 CACHE_HISTOGRAMS 6-3 CACHE_HISTOGRAMS_ REFRESH_INTERVAL 6-3 CALLS counter, Measure 14-10 CASCADE option 12-11 CAST function 7-24 Catalogs adding 9-4 CATALOGS table, displaying contents of 8-42 creating 7-7 dropping 9-28 querying metadata about 8-23/8-26 recovering 12-6 SCF ALTER DISK issues 12-38 SCF ALTER DISK, LABEL issues 12-39 Changing catalog, schema, an
Index D adding columns 9-23 adding tables 9-15 creating table partitions 7-16 description of 7-11 CREATE VIEW statement 7-39 Creating catalogs 7-7 Creating constraints 7-37 Creating table partitions 7-16 C++ preprocessor 3-17 D Damaged objects, deleting 12-37 Data checking 1-4, 15-18 integrity constraints 15-19 partitioned 7-16 validity 1-4 views and consistency 4-11 Data consistency, checking 12-30 Data problems correcting 12-32 identifying 12-27 Data types ASCII 7-25 character 7-24 datetime 7-24 defaul
Index D DDL operation limits 15-6 DDL statements 1-13 DDL_DEFAULT_LOCATIONS using to distribute primary range partitions 7-19 Decoupled keys 7-16 DEFAULT clause 7-30 DEFAULT NULL clause 7-31 Default settings altering 9-22 ANSI compliance 6-8 description of 6-1 setting and updating 6-1 SYSTEM_DEFAULTS table 6-2 Defaults See System defaults DEFINE names 1-7 Defining table columns 7-23 Definitional integrity 1-4 DELETES counter 14-11 dependent objects 11-19 Disks erasing 12-39 labeling 12-39 message operatio
Index E DROP command, CASCADE option 12-11 Dropping aliases 9-33 Dropping catalogs 9-28 Dropping columns 9-28 Dropping constraints 9-29 Dropping indexes 9-30 Dropping objects authorization requirements 9-26 summary 9-26 Dropping partitions description of 9-31 example of 9-32 Dropping schemas 9-32 Dropping SQL/MP aliases 9-33 Dropping stored procedures 9-34 Dropping table data 9-36 Dropping tables 9-34 Dropping triggers 9-36 Dropping views 9-37 Dump See also Backing up file recovery 12-2 DUP copying tables
Index G SQL/MX 3-8 structure 4-3, 4-5 FILE, Measure entity access costs 14-11 description of 14-6 statistics 14-7 FIRST KEY clause 7-32 FIXUP command, mxtool broken objects 12-32 description of 12-26 examples of 12-20, 12-21, 12-35 timestamps 12-11 FLOAT data type 7-26 FLOATTYPE 6-3 FUP RELOAD command 10-2 STATUS command 10-3, 10-4 SUSPEND command 10-4 Using RELOAD to generate more accurate query plans 15-17 G GOAWAY command, mxtool damaged objects 12-37 description of 12-26 Guardian files physical names
Index J benefits of 9-8 description of 4-12 OR operations 4-18 sort operations 4-18 ordering rows 4-18 parallel maintenance 15-17 partitioning 15-19 primary key 4-16 querying metadata about 8-46/8-50 unique 4-14 Index-only access 4-17 Indices restoring 12-24 INFO command, mxtool description of 12-26 Guardian files 8-3, 12-32 INITIALIZE DISK command, SCF 12-39 Installing SPRs for H-series RVUs 3-3 Installing Visual Query Planner 3-16 InstallSqlmx command options 3-21 error messages 3-22 phases of 3-19 synt
Index M CALLS counter 14-10 description of 14-5 DISK-READS counter 14-10, 14-11 ELAPSED-BUSY-TIME counter 14-10 ELAPSED-RECOMPILE-TIME counter 14-10 FILE entity 14-6, 14-7, 14-11 index creation 9-9 LOCK-WAITS counter 14-10, 14-11 MESSAGES counter 14-10 MESSAGES-BYTES counter 14-10 OPENS counter 14-9 RECOMPILES counter 14-10 RECORDS-ACCESSED counter 14-10, 14-11 RECORDS-USED counter 14-10, 14-11 SQLPROC entity 14-6 SQLSTMT entity 14-6 SQL-NEWPROCESS counter 14-9 SQL-NEWPROCESS-TIME counter 14-9 SQL-OBJ-REC
Index M schema feature version, displaying 8-70 schema owner, displaying 8-35 schema UID, displaying 8-27 schema version, displaying 8-70 schemas in a catalog, displaying 8-25 schemas on a node, displaying 8-28 SQL/MP alias name attributes, displaying 8-45 SQL/MP names in a schema, displaying 8-44 SQL/MX version, displaying 8-69 system schema tables, locating 8-21 system schema version, displaying 8-70 table attributes, displaying 8-41, 8-42 tables containing a column, displaying 8-64 tables in a catalog,
Index N EDIT files A-2 examples of A-4 input files A-2 output files A-2 SHOWDDL file A-2 SQL names file A-2 mxlangman.jar file 3-12 mxlangman.
Index P Objects altering 9-18 archiving 5-7 naming guidelines for 4-1 restoring 12-22 SCF ALTER DISK, LABEL issues 12-39 OLTP 7-41 Online dumps 12-10 Online help downloading from NTL at doc.hp.
Index Q alternate setting 6-4 ANSI compliance 6-8 Privileges querying metadata about 8-65/8-68 required to execute utilities 1-12 PROCESSH entity 14-8 Production system, steps for moving applications to 11-7 Program files moving to a production system 11-3 moving without compiled modules 11-5, 11-7 Programs backing up and restoring 11-29 constraints 15-18 description of 11-1, 14-9 distributing across nodes 11-8 integrating new columns 9-6 versioning issues for a distributed database environment 11-8 Prohi
Index S FROMARCHIVE option 12-2, 12-10 Guardian names 12-3 indexes 12-13 MAP NAMES parameter 12-6 TOFIRSTPURGE option 12-10 Recovery BROKEN flag, resetting 12-33 broken partition 12-33 catalog 12-6 consistent files 12-33 crash-open files 12-1 disk or node failure 12-1 EDIT files 7-22 file 12-2 from SCF commands 12-37 indexes 12-22 planning 5-1 RESETBROKEN option 12-33 SQL/MX objects 5-8 tables description of 5-8 partitioned 12-13 with indexes 12-13 TMF 5-20, 12-2 tools 12-1 views 12-22 volume 12-2 Redefin
Index S Security globally placed modules 11-16 locally placed modules 11-17 planning 5-1 Safeguard 5-4 views 7-40 SELECT statement views 15-19 Sequential cache 15-14 SERVER object, OPEN command 2-4 Servers constraints 15-18 opening 2-4 SET STATISTICS ON command 14-1, 14-3 Setting DEFINEs 11-11 Setting SQL/MP aliases 11-11 Setting the national character set 3-22 SHOWDDL command CREATE INDEX statement 12-22 DDL syntax 5-8 description of 8-4 example of 12-18 OBEY command file 5-9 output files 12-25 recovery
Index S subvolume and file naming 7-2 using InstallSqlmx 3-18 verifying software versions of Java components 2-3 verifying the versions of executable files 3-15 SQL/MX buffer causes and symptoms of reuse failures 15-9 how DP2 reuses plan fragments 15-9 managing 15-8 plan fragment size reduction 15-10 reducing reuse failures 15-13 using SCF ALTER DISK to resize the buffer 15-12 using SCF STATS DISK to monitor reuse failures 15-10 SQL/MX C 3-17 SQL/MX COBOL 3-17 SQL/MX compilation compile and recompile stat
Index T PRIMARY_KEY_CONSTRAINT_DROP PABLE_OPTION 6-4 QUERY_CACHE 6-4 QUERY_CACHE_ STATEMENT_PINNING 6-5 QUERY_CACHE_MAX_ VICTIMS 6-4 QUERY_CACHE_REQUIRED_ PREFIX_KEYS 6-5 READONLY_CURSOR 6-8 REF_CONSTRAINT_NO_ ACTION_LIKE_RESTRICT 6-6, 6-8 SAVE_DROPPED_TABLE_DDL 6-6 SCRATCH_DISKS 6-6 SCRATCH_DISKS_ EXCLUDED 6-6 SCRATCH_DISKS_ PREFERRED 6-7 setting and updating 6-1 TEMPORARY_TABLE_ HASH_PARTITIONS 6-7 TIMEOUT 6-7 UDR_JAVA_OPTIONS 6-7 System module files 3-13 System schema tables, locating 8-11/?? SYSTEM_DE
Index U RELEASED attribute 12-10 operations that affect TMF online dumps 5-15 preinstallation tasks 3-2 RECOVER FILES command description of 12-2 example of 12-19 examples of 12-5 indexes 12-13 MXGNAMES utility 12-3 partitions 12-10 recovery catalogs 12-6 database 5-20 dropped SQL/MX objects 5-20 files 12-2, 12-3 network considerations 13-25 volumes 1-3, 5-12, 12-1, 12-2 re-creating online dumps 5-15 specifying attributes 5-13 SQL/MX requirements for 5-12 START command 12-2 starting 2-3 transaction backou
Index W creating 7-39, 9-17 defining 4-11 description of 4-11 dropping 9-37 performance issues 15-19 querying metadata about 8-42/8-43 recovering 12-22 restoring 12-24 securing 7-39, 7-40 Visual Query Planner, installing 3-16 Volume recovery, TMF 1-3, 5-12, 12-2 Volumes labeling 12-39 mirrored 5-6 removing 12-40 Safeguard protection 5-4 SCF ALTER DISK, LABEL command 12-39 SCF STOP DISK and START DISK 12-40 W WITH SHARED ACCESS option 15-4 WRITEs 14-11 Z ZCLIDLL 3-6 ZCLIPDLL 3-6 ZMXSTMPL file 3-14 HP No
Index Z HP NonStop SQL/MX Installation and Management Guide—640325-001 Index-20
Legal Notices Legal Notices 1 What’s New in This Manual xv Manual Information xv New and Changed Information About This Manual xvii Audience xvii Organization xvii Related Documentation xviii Notation Conventions xx HP Encourages Your Comments xv xxiii 1.
3. Installing NonStop SQL/MX Software Requirements 2-2 Verifying Correct Versions of Independent Software Products Installing the DDL Licensing Product (T0394) 2-3 Starting TMF 2-3 2-3 3.
5. Planning Database Security and Recovery SYSKEY 4-5 Partitioning Key 4-5 Foreign Key 4-5 The Key-Sequenced File Structure 4-5 Types of Key-Sequenced File Access 4-6 Key-Sequenced Tree Structure 4-6 Planning Table and Index Partitioning 4-8 Range Partitioning and Hash Partitioning 4-8 When to Use Range Partitioning 4-9 When to Use Hash Partitioning 4-9 Determining a Database Layout 4-10 Using SQL/MX Tables 4-10 Using Views 4-11 Determining When to Use Indexes 4-12 5.
6.
8.
8.
9.
10.
11.
12.
13.
14. Measuring Performance Using Remote Application Processes 13-23 Changing Network Environments 13-24 Managing Mixed Versions of NonStop SQL/MX 13-25 14. Measuring Performance SQL/MX Tools for Gathering Statistics 14-1 SHOWLABEL Command 14-2 SET STATISTICS and DISPLAY STATISTICS Commands Measure Performance Measurement Tool 14-5 Statistics and Reports for NonStop SQL/MX 14-7 SQL/MX Measurement Models 14-9 14-3 15.
Index Using the MXGNAMES Utility A-1 MXGNAMES Input Files A-2 MXGNAMES Output Files A-2 MXGNAMES Examples A-4 Index HP NonStop SQL/MX Installation and Management Guide—640325-001 13
Index HP NonStop SQL/MX Installation and Management Guide—640325-001 14
Examples Examples HP NonStop SQL/MX Installation and Management Guide—640325-001 1
Examples HP NonStop SQL/MX Installation and Management Guide—640325-001 2
Tables 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 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.
Tables HP NonStop SQL/MX Installation and Management Guide—640325-001 2
Figures Figures Figure 4-1. Figure 8-1. Figure 8-2. Figure 14-1.
Figures HP NonStop SQL/MX Installation and Management Guide—640325-001 2
