HP NonStop SQL/MX Installation and Management Guide Abstract This manual describes how to install and manage HP NonStop™ SQL/MX. Product Version NonStop SQL/MX Releases 2.0 and 2.1 Supported Release Version Updates (RVUs) This publication supports G06.23 and all subsequent G-series RVUs until otherwise indicated by its replacement publication.
Document History Part Number Product Version Published 523722-001 NonStop SQL/MX Release 1.8 November 2002 523723-001 NonStop SQL/MX Release 2.0 April 2004 523723-002 NonStop SQL/MX Release 2.0 August 2004 523723-003 NonStop SQL/MX Releases 2.0 and 2.1 June 2005 523723-004 NonStop SQL/MX Releases 2.0 and 2.
HP NonStop SQL/MX Installation and Management Guide Index Figures What’s New in This Manual xvii Manual Information xvii New and Changed Information Tables xvii About This Manual xxiii Audience xxiii Organization xxiii Related Documentation xxiv Notation Conventions xxviii 1.
1. Introduction to SQL/MX Database Management (continued) Contents 1. Introduction to SQL/MX Database Management (continued) SQL/MX Environment 1-15 OSS Environment 1-16 Privileges Required to Execute SQL/MX Utilities MXCS 1-16 NonStop Server for Java 1-16 JDBC/MX 1-17 Database Management Tasks 1-17 SQL/MX Database Management Tools 1-17 1-16 2.
3. Installing NonStop SQL/MX (continued) Contents 3. Installing NonStop SQL/MX (continued) Falling Back to Release 1.8 3-22 Using the InstallSqlmx Script 3-23 The Phases of InstallSqlmx 3-23 InstallSqlmx Syntax 3-25 InstallSqlmx Options 3-25 InstallSqlmx Error Messages 3-26 4.
5. Planning Database Security and Recovery (continued) Contents 5.
. Creating an SQL/MX Database Contents 7.
7. Creating an SQL/MX Database (continued) Contents 7.
8. Querying SQL/MX Metadata (continued) Contents Displaying All Schemas Visible on a Node 8-27 8.
Contents 8. Querying SQL/MX Metadata (continued) Displaying the Attributes of a Column 8-56 Displaying All Tables Containing a Selected Column 8-57 8.
9. Adding, Altering, and Dropping SQL/MX Database Objects (continued) Contents Altering Views 9-26 9.
Contents 10. Reorganizing SQL/MX Tables and Maintaining Data (continued) 10.
Contents 11. Managing Database Applications (continued) 11.
Contents 12. Performing Recovery Operations (continued) 12. Performing Recovery Operations (continued) ABORT DISK 12-36 ALTER DISK, ALTNAME and ALTER DISK, VOLNAME ALTER DISK, LABEL 12-37 INITIALIZE DISK 12-38 START DISK and STOP DISK 12-38 12-36 13.
14. Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 (continued) Contents 14. Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 (continued) Using the migrate Utility to Migrate SQL/MP Metadata From Release 1.8 14-5 System Requirements for Migrating SQL/MP Metadata 14-6 Before Migrating SQL/MP Metadata 14-6 Considerations for Falling Back From Release 2.1 or Release 2.
. Enhancing SQLMX Database Performance (continued) Contents 16.
Index Contents Index Figures Figure 4-1. Figure 8-1. Figure 15-1. Key-Sequenced B-Tree Structure 4-7 SQL/MX Metadata Table Structure 8-5 Measure Entities and Program Structures 15-5 Tables Table 1-1. Table 1-2. Table 1-3. Table 1-4. Table 3-1. Table 3-2. Table 3-3. Table 5-1. Table 5-2. Table 6-1. Table 6-2. Table 6-3. Table 6-4. Table 8-1. Table 9-1. Table 9-2. Table 9-3. Table 9-4. Table 9-5. Table 9-6. Table 10-1. Table 12-1. Table 12-2. Table 13-1. Table 13-2. Table 13-3. Table 13-4. Table 13-5.
Tables Contents HP NonStop SQL/MX Installation and Management Guide —523723-004 xvi
What’s New in This Manual Manual Information HP NonStop SQL/MX Installation and Management Guide Abstract This manual describes how to install and manage HP NonStop™ SQL/MX. Product Version NonStop SQL/MX Releases 2.0 and 2.1 Supported Release Version Updates (RVUs) This publication supports G06.23 and all subsequent G-series RVUs until otherwise indicated by its replacement publication.
What’s New in This Manual • • • • • • • Changes in the June 2005 Revision of the Manual Updated the description under Moving Programs From Development to Production on page 11-3 to specify that when moving a program from a development system to a production system, module definitions must be compiled on the production system. Removed the subsection, Moving a Program With Compiled Modules, and replaced it with the new subsection, Reasons Not to Move a Program With Compiled Modules on page 11-3.
What’s New in This Manual Section 3, Installing NonStop SQL/MX Changes in the June 2005 Revision of the Manual Clarified that InstallSqlmx does not currently support user aliases of SUPER.SUPER in Using the InstallSqlmx Script on page 3-23. Updated the information about installing the DP2 fallback SPRs in Preinstallation Tasks on page 3-1.
What’s New in This Manual Section 11, Managing Database Applications Changes in the June 2005 Revision of the Manual Added information for SQL/MX Release 2.1 in Migrating Applications to Release 2.x on page 11-1. Also added Migrating SQL/MX Applications From Release 2.0 to Release 2.1 on page 11-3. Added Versioning Issues for a Distributed Database Environment on page 11-8. Added Migrating SQL/MX Nodes to Obtain Improved Query Plan Performance on page 11-15. Added information for SQL/MX Release 2.
What’s New in This Manual Appendix A, Falling Back From NonStop SQL/MX Release 2.1 or Release 2.0 Changes in the June 2005 Revision of the Manual Added information for SQL/MX Release 2.1 in Fallback Considerations on page A-2. Added Falling Back From Release 2.1 to Release 2.0 on page A-3. Added information for SQL/MX Release 2.1 in Preparing to Fall Back From Release 2.1 or Release 2.0 to Release 1.8 on page A-3.
What’s New in This Manual Changes in the June 2005 Revision of the Manual HP NonStop SQL/MX Installation and Management Guide— 523723-004 xxii
About This Manual This manual describes how to install NonStop SQL/MX Releases 2.0 and 2.1 on an HP NonStop server and discusses other issues related to managing an SQL/MX system. Audience This manual is intended for database administrators and application programmers who are using NonStop SQL/MX through MXCI—the SQL/MX conversational interface—or as embedded SQL to access databases.
Related Documentation About This Manual Section 10, Reorganizing SQL/MX Tables and Maintaining Data Describes how to reorganize SQL/MX tables and maintain the data. Section 11, Managing Database Applications Describes how to manage SQL/MX applications. Section 12, Performing Recovery Operations Describes how to perform recovery operations on an SQL/MX database. Section 13, Managing an SQL/MX Distributed Database Describes methods for managing an SQL/MX distributed database.
Related Documentation About This Manual Reference Manuals SQL/MX Reference Manual Describes the syntax of SQL/MX statements, MXCI commands, functions, and other SQL/MX language elements. SQL/MX Connectivity Service Administrative Command Reference Describes the SQL/MX administrative command library (MACL) available with the SQL/MX conversational interface (MXCI). DataLoader/MX Reference Manual Describes the features and functions of the DataLoader/MX product, a tool to load SQL/MX databases.
Related Documentation About This Manual NSM/web Installation Guide Describes how to install NSM/web and troubleshoot NSM/web installations. Online Help The SQL/MX Online Help consists of: Reference Help Overview and reference entries from the SQL/MX Reference Manual. Messages Help Individual messages grouped by source from the SQL/MX Messages Manual. Glossary Help Terms and definitions from the SQL/MX Glossary.
Related Documentation About This Manual This figure shows the manuals in the SQL/MX library: Programming Manuals Introductory Guides SQL/MX Comparison Guide for SQL/MP Users SQL/MX Quick Start SQL/MX Programming Manual for C and COBOL SQL/MX Programming Manual for Java Reference Manuals SQL/MX Reference Manual SQL/MX Messages Manual SQL/MX Glossary SQL/MX Connectivity Service Administrative Command Reference DataLoader/MX Reference Manual Specialized Guides SQL/MX Installation and Management G
Notation Conventions About This Manual Notation Conventions Hypertext Links Blue underline is used to indicate a hypertext link within text. By clicking a passage of text with a blue underline, you are taken to the location described. For example: This requirement is described under Backup DAM Volumes and Physical Disk Drives on page 3-2. General Syntax Notation This list summarizes the notation conventions for syntax presentation in this manual. UPPERCASE LETTERS.
General Syntax Notation About This Manual { } Braces. A group of items enclosed in braces is a list from which you are required to choose one item. The items in the list can be arranged either vertically, with aligned braces on each side of the list, or horizontally, enclosed in a pair of braces and separated by vertical lines. For example: LISTOPENS PROCESS { $appl-mgr-name } { $process-name } ALLOWSU { ON | OFF } | Vertical Line.
General Syntax Notation About This Manual a blank line. This spacing distinguishes items in a continuation line from items in a vertical list of selections.
1 Introduction to SQL/MX Database Management This section provides an introduction to managing an SQL/MX database. For information about managing an SQL/MP database, see the SQL/MP Installation and Management Guide. Managing an SQL/MX relational database typically involves managing sets of continuously active programs, as well as the database itself.
Introduction to SQL/MX Database Management SQL/MX Database Features The database management environment for NonStop SQL/MX includes: • • The SQL/MX relational database management system (RDBMS) 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, se
Introduction to SQL/MX Database Management Database Protection and Recovery Database Protection and Recovery An SQL/MX database is protected by: • • • The HP NonStop Transaction Management Facility (TMF), which audits database changes and provides automatic 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.
Introduction to SQL/MX Database Management Data Integrity For more information about database protection and recovery, see the TMF information in Section 5, Planning Database Security and Recovery and Section 12, Performing Recovery Operations. 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.
Introduction to SQL/MX Database Management Multiple Character Sets read, SKIP CONFLICT, and STABLE. These access modes are maintained by shared or exclusive locks on rows, sets of rows, partitions, and tables. For more information about access options, see the SQL/MX Reference Manual. 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.
Introduction to SQL/MX Database Management High Availability When tables and indexes are partitioned across multiple disks, NonStop SQL/MX can use a different process for each partition during query execution. This approach reduces the time for scans and other set-oriented processing by a factor equivalent to the number of partitions when there is no contention in the processor-controller-disk path; that is, when every participating disk is primary to a different processor.
Introduction to SQL/MX Database Management Naming Database Objects Naming Database Objects ANSI Logical Names Names for SQL/MX objects are organized hierarchically. Database objects exist in schemas, which are themselves contained in catalogs. Catalogs are collections of schemas. Schema names must be unique within a given catalog. You name tables, views, and other SQL/MX objects by using a three-part ANSI logical name: catalog-name.schema-name.
Introduction to SQL/MX Database Management DEFINE Names DEFINE Names Use class MAP DEFINE names as logical names for SQL/MP tables or views in DML statements. When NonStop SQL/MX compiles such statements, it replaces the DEFINE name in the statement with the associated Guardian physical name. You can create DEFINE names within MXCI or names can be inherited from the TACL process or the OSS shell. You cannot assign DEFINE names to SQL/MX tables or views.
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 SQL/MX Database Object Types 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 Versioning Issues Versioning Issues Versioning is the ability of SQL/MX software to interoperate between different releases. It enables nodes in an SQL/MX database distributed across an Expand network to run different releases of the SQL/MX software. It ensures that nodes with compatible software versions can interoperate and that nodes with incompatible software versions do not interoperate.
Software Versioning Introduction to SQL/MX Database Management run-time libraries (SRLs) or system libraries on a node. Only one instance of this software can exist on a node. The system software contains APIs that can obtain all the earliest supported releases that correspond to the MXV. These APIs can also obtain the object schema versions (OSVs) of objects on which the system software must operate. NonStop SQL/MX uses these OSVs to perform version checking.
Introduction to SQL/MX Database Management Versioning Guidelines for Release 2.1 and Release 2.0 Versioning Guidelines for Release 2.1 and Release 2.0 Query Execution Plans • • You cannot perform queries—through embedded SQL or dynamic SQL—between a node running Release 2.1 or Release 2.0 and a node running Release 1.8. However, these nodes can use Release 1.8 query plans and modules that have been compiled on a Release 1.
Introduction to SQL/MX Database Management SQL/MX Environment back from Release 2.1 to Release 2.0, you must recompile all version 1400 user modules. • You cannot determine the version of a user module. However, when you run a program with a user module that was compiled on a later release, you get a versioning error indicating that the module is of a later version and needs to be recompiled. For more information about falling back from Release 2.1 to Release 2.
Introduction to SQL/MX Database Management OSS Environment SQL/MP tables. MXCI commands are SQL/MX extensions that typically affect the 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.
Introduction to SQL/MX Database Management JDBC/MX For more information about the NonStop Server for Java 4, see the NonStop Server for Java Programmer’s Reference. For instructions on how to install NonStop Server for Java 4, see the README file on the product CD. JDBC/MX To develop and execute Java programs that perform SQL operations and SPJs, you must install the JDBC Driver for NonStop SQL/MX (JDBC/MX), product version 3.1, on your NonStop system. JDBC/MX 3.1 conforms where applicable to the JDBC 3.
Introduction to SQL/MX Database Management SQL/MX Database Management Tools Prepared SQL Compiled once and then executed any number of times within the current session. Embedded-only SQL/MX Embedded in SQL programs written in C, C++, COBOL, or Java. 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.
Introduction to SQL/MX Database Management SQL/MX Database Management Tools Table 1-4. NonStop Tools for SQL/MX Database Management (page 2 of 2) Program Description FCHECK Checks internal consistency of structured files and reports consistency errors. 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.
Introduction to SQL/MX Database Management SQL/MX Database Management Tools HP NonStop SQL/MX Installation and Management Guide—523723-004 1- 20
2 Preparing to Install NonStop SQL/MX Before you install NonStop SQL/MX, check that you have met these preinstallation requirements as they apply to your installation: • • • • • Hardware Requirements on page 2-2 Software Requirements on page 2-3 Independent Software Products on page 2-4 DDL Licensing Product (T0394) on page 2-4 Starting TMF on page 2-4 HP NonStop SQL/MX Installation and Management Guide—523723-004 2 -1
Preparing to Install NonStop SQL/MX Hardware Requirements Hardware Requirements Before you install NonStop SQL/MX, your HP NonStop S-series system must meet these minimum requirements: Note. To run NonStop SQL/MX, you need an NonStop S-series system that is configured with Open System Services (OSS). The memory requirements for each processor depend on application usage, the number of data volumes configured to each processor, and other factors.
Software Requirements Preparing to Install NonStop SQL/MX Software Requirements To use Release 2.1 or Release 2.0, you must install the site update tape (SUT) for the G06.23 or a later RVU on your NonStop S-series system. See the G06.nn Software Installation and Upgrade Guide for detailed information about installing the NonStop system. The G06.23 or later RVU contains the correct product versions for these key software products that are used with NonStop SQL/MX: Note.
Independent Software Products Preparing to Install NonStop SQL/MX Independent Software Products If you intend to develop and execute Java programs that perform SQL operations or stored procedures in Java (SPJs) on your NonStop system, you must order and install these independent products: Product Version* Software Product Installation Manual T2766V10 NonStop Server for Java, based on the Java 4 platform, Standard Edition, SDK 1.4.1 README on product CD T1225V31 JDBC Driver for SQL/MX, version 3.
Preparing to Install NonStop SQL/MX Starting TMF started for transaction processing. You can request status from TMFCOM, the TMF command interface, by entering the TMFCOM command at the operating-system command interpreter prompt and then issuing specific STATUS commands through TMFCOM. If you plan to use a system management program to operate TMF, you can use TMFSERVE, a TMF process that provides access to TMF by using the Subsystem Programmatic Interface (SPI).
Preparing to Install NonStop SQL/MX HP NonStop SQL/MX Installation and Management Guide—523723-004 2 -6 Starting TMF
3 Installing NonStop SQL/MX Summary of Installation Tasks Before you install NonStop SQL/MX Release 2.1 or Release 2.0, read Using the InstallSqlmx Script on page 3-23 to familiarize yourself with the InstallSqlmx script and the functions it performs during SQL/MX installation. Note. You can also install NonStop SQL/MP from the H06.04 or later H-series RVU. To initialize NonStop SQL/MP, you must use the INITIALIZE SQL command from SQLCI. For more information, see the SQL/MP Reference Manual.
Installing NonStop SQL/MX Preinstallation Tasks superseding SPR) on the Release 1.8 system and test it sufficiently for production use: ° ° T9053APO, if the system uses the online disk remirror feature T9053APG, if the node does not use the online disk remirror feature When you migrate to the G06.23 or later RVU, if a fallback becomes necessary at a later time, you can fall back to the tested environment. For information about falling back from Release 2.1 or Release 2.
Installing NonStop SQL/MX Installing NonStop SQL/MX When you run InstallSqlmx, make sure to specify the -nofcheck option. Installing NonStop SQL/MX 1. Log on to the NonStop server by using SUPER.SUPER (user ID 255,255). 2. Install the SUT. Follow all instructions provided in the associated software release document and any instructions that come with the SUT. For information about installing the SUT, see the G06.nn Software Installation and Upgrade Guide. 3.
Installing NonStop SQL/MX Installing NonStop SQL/MX OSS environment is case-sensitive, so enter all commands in the appropriate case. You must be a super ID user to execute the InstallSqlmx script. Caution. InstallSqlmx creates the anchor file and assigns it a security of “N----.” The super ID should not change this setting. Before running the InstallSqlmx script, you might want to set your terminal type to VT320 (from OutsideView) to obtain the most readable block-mode screen displays. 7.
Other Installation Tasks Installing NonStop SQL/MX aliases and Release 1.8 defaults entries and SPJs will not work in your Release 2.1 or Release 2.0 environment. 10. You are now ready to start using NonStop SQL/MX. Proceed to Other Installation Tasks.
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 G06.23 or later SUT: Note. In these steps, note that these files are installed only if you install 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 G06.
Installing NonStop SQL/MX • • • Overview of Automatic File Installations T1230PAX, which contains the SQL/MX UDR server files mxudr.mak, mxudr.oby, mxudr.tlo, and mxudrlink T1231PAX, which contains the SQL/MX language manager files mxlangman.jar and mxlangman.policy T1232PAX, which contains the NonStop SQLJ product file sqlj.jar 3. The SUT installation automatically installs the public SRLs ZCLIPSRL and ZCLISRL to the $SYSTEM.SYSnn subvolume.
Installing NonStop SQL/MX Verify That Files Are Licensed 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. COPYOSS installs these files in the OSS directory /usr/tandem/sqlmx/lib: • • f. sqlcli.o sqlj.
Summary of Installed SQL/MX Files Installing NonStop SQL/MX ZCLIPSRL resides in $SYSTEM.SYSnn. To find out if a file (for example, MXCMP) has been licensed, enter: FILEINFO MXCMP 2. Verify that each of these file shows an L after the file code. The L confirms that the file is licensed. For example: $SYSTEM.
Summary of Installed SQL/MX Files Installing NonStop SQL/MX File Name Description Where Installed MXUDR The executable file of the SQL/MX UDR server, which is a layer of software between the SQL/MX executor and JVM that supports SPJs in NonStop SQL/MX. $SYSTEM.SYSTEM For more information on SPJs, see the SQL/MX Guide to Stored Procedures in Java0. MXUTP The executable file for the materialized view feature, which is not supported with Release 2.1 or Release 2.0. $SYSTEM.
Summary of Installed SQL/MX Files Installing NonStop SQL/MX File Name Description Where Installed mxCompileUserModule The command-line tool used to produce compiled module definitions for embedded applications. For more information, see the SQL/MX Programming Manual for C and COBOL and the SQL/MX Programming Manual for Java. /usr/tandem/sqlmx/bin mxexportddl The OSS file for the mxexportDDL utility. See Using Import Catalog Tools on page 3-22.
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 File Name Description Where Installed mxudr.tlo A library that contains code for the SQL/MX UDR server and the SQL/MX language manager, both of which facilitate the use of SPJs in NonStop SQL/MX. For more information on SPJs, see the SQL/MX Guide to Stored Procedures in Java. /usr/tandem/sqlmx/udr mxudrlink A Korn shell script that invokes the mxudr.mak makefile to relink the SQL/MX UDR server executable, MXUDR.
Summary of Installed SQL/MX Files Installing NonStop SQL/MX Run-Time Libraries File Name Description Where Installed ZCLIPSRL Privileged layer of the CLI and the SQL/MX file system code. $SYSTEM.SYSnn ZCLISRL A library containing CLI calls. CLI is an interface between a host language and an SQL implementation that allows host language programs to execute SQL statements and to perform related operations, like the retrieval of diagnostics information. $SYSTEM.
Summary of Installed SQL/MX Files Installing NonStop SQL/MX System Module Files These internal module files are installed in the directory /usr/tandem/sqlmx/SYSTEMMODULES: Caution. Do not delete these module files or move them to a different location. NonStop SQL/MX uses these files to read information from and perform updates to its internal tables. NONSTOP_SQLMX_NSK. MXCS_SCHEMA.CATANSIMX The user module file that contains the queries that support ODBC catalog APIs NONSTOP_SQLMX_NSK. MXCS_SCHEMA.
Summary of Installed SQL/MX Files Installing NonStop SQL/MX User Module Files These user module files are installed in the directory /usr/tandem/sqlmx/USERMODULES: Caution. NonStop SQL/MX uses these files to read information from and perform updates to its internal tables. Before you delete or move these user module files to another OSS directory, see Converting Globally Placed Modules to Locally Placed Modules on page 11-23 and the SQL/MX Programming Manual for C and COBOL. NONSTOP_SQLMX_NSK.SCH.
Verifying Executable File Versions Installing NonStop SQL/MX Interface Description Files File Name Description Where Installed sqlcli.o Interface description file used by SQL/MX preprocessor (mxsqlco) for COBOL. /usr/tandem/sqlmx/lib Verifying Executable File Versions 1. Use the VPROC command to display the version of specified executables installed on your system. 2. Verify that the displayed version information is correct for the specified executable.
Installing NonStop SQL/MX Verifying Java Component Software Versions Verifying Java Component Software Versions If you intend to develop and execute Java programs that perform SQL operations or SPJs, verify that you have installed the correct product versions of these necessary software products and files: • • • • NonStop Server for Java JDBC/MX driver SQLJ product (required only for SQLJ programs) SQL/MX UDR server (required only for SPJs) For information about verifying the software versions of these
Installing NonStop SQL/MX Starting MXCI Starting MXCI MXCI runs as an OSS process and must be started from within the OSS environment. Note. Check that the file mxci in the OSS directory /usr/tandem/sqlmx/bin is secured for read and execute access to your node. If it is not secured for execute access, you cannot start it. If necessary, use the OSS command chmod to change the options. To start MXCI: 1. At a TACL prompt, start the OSS shell by entering: osh You are now in the OSS environment. 2.
Installing NonStop SQL/MX Installing the SQL/MX Online Help workstation or NonStop system console provided by HP. If a TSM workstation or system console is not available, view the help files on a personal computer running Microsoft Internet Explorer 4.0 or later and any of these operating systems: • • • Windows NT 4.0 Windows 2000 Windows XP Even though online help is available from several sources, HP recommends downloading it from the NTL Web site whenever possible: http://www.hp.
Installing NonStop SQL/MX Installing the Windows-Hosted SQL/MX C and C++ Preprocessor 3. Run MXHELP.EXE and then click Unzip to extract the help files. By default, WinZip unzips the help in the C:\NonStop SQL-MX folder. If you prefer, click Browse and select another folder. 4. To view the help, double-click one of the .chm files: • • • Glossary.chm Messages.chm Reference.chm Consider creating shortcuts to these files on your desktop or in your Start Menu Programs folder. Delete MXHELP.EXE if needed.
Installing NonStop SQL/MX Using Import Catalog Tools For information about running the Windows-hosted SQL/MX COBOL preprocessor, see the SQL/MX Programming Manual for C and COBOL. Using Import Catalog Tools mxexportddl is the only Import Catalog tool available for Release 2.1 or Release 2.0. Its primary purpose is to provide a file describing the SQL/MX compilation environment for a table or a single schema. This file is used by service providers to evaluate reported problems with the SQL/MX compiler.
Installing NonStop SQL/MX Using the InstallSqlmx Script Using the InstallSqlmx Script The InstallSqlmx script: • • • Verifies that all processors on the system are valid processor types for Release 2.1 or Release 2.0. If InstallSqlmx detects a problem, it generates an Install error message that identifies the processor or processors that do not meet hardware requirements. For information about this error message, see the SQL/MX Messages Manual. For information about hardware requirements for Release 2.
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. For file name and default location information, see Overview of Automatic File Installations on page 3-6.
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 -n national_charset_name Use this option to specify the name of the national character set for NCHAR/NCHAR VARYING. The value for the -n option can be ISO88591, UCS2, KANJI, or KSC5601.
4 Understanding and Planning SQL/MX Tables An understanding of the types of organizations of SQL/MX tables and their key-sequenced file structure is essential for effective use and functioning of the database.
Understanding and Planning SQL/MX Tables Avoid Renaming Nodes Avoid Renaming Nodes After an SQL/MX database node has been installed and initialized, do not attempt to change the node name. The node name is recorded in system metadata entries and file labels throughout the database. The node number not recorded anywhere. Avoid Changing Object Names or Moving Database Objects You should develop and implement an object-naming strategy that prohibits changing object names or moving named objects.
Understanding and Planning SQL/MX Tables Avoid Putting Same-Named Catalogs on Multiple Distributed Nodes Avoid Putting Same-Named Catalogs on Multiple Distributed Nodes If you create catalogs with the same name on two or more nodes, each node can access only the objects in the local catalog. You cannot join the tables or indexes in the same-named catalogs on different nodes.
Understanding and Planning SQL/MX Tables Types of Keys Used in SQL/MX Tables and Indexes page 4-5 discusses the structure of key-sequenced files. An understanding of the key-sequenced file structure can help you plan the best use of disk storage space when sizing your SQL/MX database, implementing economical table-access methods, and analyzing various performance trade-offs. This understanding is also essential for anyone using the FCHECK utility to operate on physical file structures.
Understanding and Planning SQL/MX Tables Partitioning Key Partitioning Key You specify a partitioning key through the PARTITION BY clause of the CREATE TABLE or CREATE INDEX statement. The FIRST KEY option of the PARTITION clause specifies the beginning of the range for a range-partitioned table or index partition. The FIRST KEY clause specifies the lowest values in the partition for columns stored in ascending order and the highest values for columns stored in descending order.
Understanding and Planning SQL/MX Tables Key-Sequenced Tree Structure When NonStop SQL/MX reads a key-sequenced file by its clustering key, each read operation retrieves the record containing the next sequentially higher key value. When NonStop SQL/MX reads the file through an alternate index, each operation randomly accesses the table. If you do not use an index, access occurs by the clustering key.
Key-Sequenced Tree Structure Understanding and Planning SQL/MX Tables Figure 4-1 illustrates a sample tree structure for a key-sequenced file. Figure 4-1.
Understanding and Planning SQL/MX Tables Planning Table and Index Partitioning block if not at the end of the file, and gives the index block a pointer to the new data block. 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 placement on a particular disk. The user has no control over row-to-partition mapping. 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).
Understanding and Planning SQL/MX Tables Determining a Database Layout than the partitioning key. This approach is useful when you want to co-locate hash partitions of related tables and indexes. Advantages of Hash Partitioning • • Hash partitioning automatically provides balanced and even distribution of data across available disks, helping to prevent skewing.
Understanding and Planning SQL/MX Tables • • Using Views All other SQL/MX objects (views, indexes, constraints, and so on) are directly or indirectly dependent on the tables. Recovery and management methods are simplified.
Understanding and Planning SQL/MX Tables • Determining When to Use Indexes A view can be secured separately from the underlying tables or views. Users of the view do not need to have access privileges to these underlying objects to access the view.
Understanding and Planning SQL/MX Tables Determining When to Use Indexes When compiling a statement, NonStop SQL/MX selects the query execution plan for a statement by choosing the best access path to the data. If an index exists, NonStop SQL/MX evaluates using the index. Indexes give the optimizer more possible access options. 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.
Understanding and Planning SQL/MX Tables Determining When to Use Indexes The query can be satisfied by accessing only AINDEX, which contains all the columns requested. This type of index-only retrieval can be effective on both unique and nonunique indexes.
Understanding and Planning SQL/MX Tables • • • • Determining When to Use Indexes The result is presented in a certain order or grouped according to certain columns, such as queries that use a DISTINCT, GROUP BY, ORDER BY, or UNION clause, which can require a sort operation if indexes are not available. An application has many queries that reference a column in a table. All the necessary information can be obtained from the index (index-only access). The column is an argument of the MIN or MAX function.
Understanding and Planning SQL/MX Tables Determining When to Use Indexes the item. For example, if the index includes VARCHAR columns, the actual stored record length would be a few bytes larger for each VARCHAR column than the defined column length. • • • • • • 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.
Understanding and Planning SQL/MX Tables Determining When to Use Indexes In calculating the length of an index key, consider the extra bytes added to the length of any column that is not specified as NOT NULL NOT DROPPABLE. For more 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 ORDER BY UNION without ALL 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 B, C), there are three key prefixes: A, AB, and ABC. The prefix ABC corresponds to the full key, the other prefixes form a partial key. In this example, COL1 and COL2 are key prefixes from two different indexes.
Understanding and Planning SQL/MX Tables Determining When to Use Indexes HP NonStop SQL/MX Installation and Management Guide—523723-004 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-13 Managing Permissions for Files in Guardian Space on page 11-13 Securing User Modules on page 11-17 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 these: • • • u (user/own
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-2. Using Guardian names with TMF, RDF, and Measure For NonStopn SQL/MX Release 2.1 or Release 2.
Planning Database Security and Recovery Recovering Dropped Database Objects Recovering Dropped Database Objects The method for recovering an accidentally dropped SQL/MX object depends on whether that object is a catalog, table, index, or view. Recovery of a single view or index is usually a straightforward operation. Recovery of a table or catalog, however, can be complex and difficult if there are multiple dependent objects.
Planning Database Security and Recovery • Recovering Dropped Database Objects 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 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 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. You gain greater reliability by keeping extra generations of online dumps, but the site needs additional tape management for the online dumps and audit trails.
Operations That Impact TMF Online Dumps Planning Database Security and Recovery Table 5-1 summarizes SQL/MX operations that can invalidate online dumps. Table 5-1. SQL/MX Operations That Impact TMF Online Dumps (page 1 of 2) Operation Option Effect Recovery Strategy CREATE INDEX POPULATE (or no option specified) Does not invalidate any existing online dumps. Make online dumps of all partitions of the new index to provide TMF file recovery protection.
Using the TMF DUMP FILES Command Planning Database Security and Recovery Table 5-1. SQL/MX Operations That Impact TMF Online Dumps (page 2 of 2) Operation RESTORE Option Effect Recovery Strategy MOVE (simple move) Creates a new partition and deletes the existing partition, invalidating online dumps. Make a new online dump of the moved partition to retain TMF file recovery protection. REUSE Does not invalidate any online dumps of the reused partition. No action needed.
Planning Database Security and Recovery Using the TMF DUMP FILES Command For example, if an SQL/MX table to be dumped has the definition: CREATE TABLE CAT.SCH.T056T11 ( WREN INT NO DEFAULT -- NOT NULL NOT DROPPABLE , JUNCO INT NO DEFAULT -- NOT NULL NOT DROPPABLE , BLACKBIRD INT DEFAULT NULL , CONSTRAINT CAT.SCH.T056T11_104569472_0002 PRIMARY KEY (WREN ASC, JUNCO ASC) NOT DROPPABLE , CONSTRAINT CAT.SCH.T056T11_104569472_0001 CHECK (CAT.SCH.T056T11.WREN IS NOT NULL AND CAT.SCH.T056T11.
Planning Database Security and Recovery TMF Considerations in Using MXCI The corresponding DUMP FILES command to dump only the files in this table would be: DUMP FILES & ( & $DATA1.ZSDADLON.NSPD2Q00,& $DATA2.ZSDADLON.N0FK2Q00,& $DATA3.ZSDADLON.N75R2Q00,& $DATA4.ZSDADLON.OFVY2Q00,& $DATA5.ZSDADLON.ONL42Q00,& $DATA1.ZSDADLON.R70U5Q00,& $DATA2.ZSDADLON.SFQ15Q00,& $DATA3.ZSDADLON.SNG85Q00,& $DATA1.ZSDADLON.NSPD2Q01,& $DATA2.ZSDADLON.N0FK2Q01,& $DATA3.ZSDADLON.N75R2Q01,& $DATA4.ZSDADLON.OFVY2Q01,& $DATA5.
Planning Database Security and Recovery Using TMF In Recovery Operations These commands and statements make up a complete user-defined TMF transaction: • • • ° ° ° ° ° BEGIN WORK; SELECT...; INSERT...; DELETE...; COMMIT WORK; For many utility statements, the utility operation generates the appropriate number of TMF transactions for the operation, reducing the overhead associated with TMF audit trails and ensuring that the necessary locks are acquired for the operations.
Planning Database Security and Recovery Using RDF to Set Up a Backup System Using RDF to Set Up a Backup System The RDF subsystem monitors changes made to a production database on a local (primary) system and maintains a copy of that database on a remote (backup) system. Because it applies changes to the backup database as soon as they are detected on the primary system, the RDF subsystem keeps the backup database continuously up to date with changes made by business applications on the primary system.
Planning Database Security and Recovery Configuring Disk Volumes for RDF Configuring Disk Volumes for RDF You perform disk volume configuration for RDF as you do for SQL/MX databases. HP recommends that volume names match exactly on the primary and backup nodes. If they do not, they can be remapped for RDF with the RDF SET VOLUME configuration command.
Planning Database Security and Recovery Using Backup and Restore 2 to Create Offline RDF Backup Databases Using Backup and Restore 2 to Create Offline RDF Backup Databases Use Backup and Restore 2 to specify: • • An RDF backup catalog name that is different from the primary catalog, using the TGT CATALOG syntax The physical location of SQL/MX objects as they are restored, using the LOCATION option to restore an SQL/MX file to a different location Using the Target Catalog Option of RESTORE You should sp
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 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 Database Recovery Using Backup and Restore 2 for 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 Database Recovery Table 5-2 describes how SQL/MX and OSS objects are managed by Backup and Restore 2. For more information, see the Backup and Restore 2 Manual. Table 5-2. Backup and Restore of SQL/MX and OSS Objects (page 1 of 3) SQL/MX or OSS Object BACKUP Command RESTORE Command Catalog Explicitly backs up the named catalog and all subordinate objects. See Backing Up Catalogs on page 5-32.
Planning Database Security and Recovery Using Backup and Restore 2 for 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 Constraints Unless you specify the CONSTRAINTS EXCLUDED option, all SQL/MX constraints are automatically backed up when you back up their catalog, schema, or table. See Constraints on page 5-33. All constraints except REFERENTIAL INTEGRITY can be restored with Backup and Restore 2.
Planning Database Security and Recovery Frequency of Backups Table 5-2. Backup and Restore of SQL/MX and OSS Objects (page 3 of 3) SQL/MX or OSS Object BACKUP Command RESTORE Command OSS program executable file or user module Does not automatically back up program executables or user modules. You back up program files or executables by specifying OSS backup objects. See Backing Up and Restoring Program Executables and User Modules on page 5-35.
Planning Database Security and Recovery Backup Objects Hierarchy Daily Backups To provide a high degree of protection, you can use Backup and Restore 2 to perform daily backups. Then the maximum amount of data lost from a failure never exceeds one working day. A daily backup should either be a full backup of all database objects or a limited backup of specific objects. If you use limited daily backups, you should also perform periodic full backups, as explained in Periodic Full Backups.
Planning Database Security and Recovery SQL/MX Objects That Are Backed Up Explicitly SQL/MX Objects That Are Backed Up Explicitly Backing Up Catalogs SQL/MX catalogs are backed up only when specified, not by default. When you back up a catalog, all of its subordinate objects (for example, schemas and tables) are backed up as well unless they are specifically excluded.
Planning Database Security and Recovery SQL/MX Objects That Are Backed Up Implicitly You can use the BACKUP job option PARTONLY only when a database has partitioned tables and indexes. PARTONLY enables you to back up and restore individual components of a partitioned database. (For more information about this and other job options, see the Backup and Restore 2 Manual.) You cannot use the PARTONLY option with the INDEXES INCLUDED option. Caution.
Planning Database Security and Recovery SQL/MX Objects That Are Not Restored by BRCOM SQL/MX Objects That Are Not Restored by BRCOM You cannot use Backup and Restore 2 to restore these SQL/MX objects: • • • • • GRANT and REVOKE privileges Referential integrity constraints SPJs Triggers Views However, except for GRANT and REVOKE privileges, you can capture the DDL for the CREATE statements executed for these objects and use this information to manually re-create these objects after a Restore operation.
Planning Database Security and Recovery Backing Up and Restoring OSS Program Files and SPJs Backing Up and Restoring OSS Program Files and SPJs Backing Up and Restoring Program Executables and User Modules Program executables and user modules in the SQL/MX environment are stored in OSS directories.
Using the RESTORE SHOWDDL ON Option to ReCreate Objects Planning Database Security and Recovery For detailed information about backing up and restoring OSS objects, see the Backup and Restore 2 Manual. Using the RESTORE SHOWDDL ON Option to Re-Create Objects Every backup operation captures the DDL from all SQL/MX CREATE statements that have been executed. This capture always occurs; there is no backup job option to prevent it.
6 Reviewing and Setting System Defaults NonStop SQL/MX uses system-defined default settings for attributes associated with compiling and executing queries. The system-defined default settings, which are hard-coded settings, are optimal in most cases. Under some circumstances, however, you might want to override a specific system-defined default setting.
Reviewing and Setting System Defaults Consequences of Inserting Rows Into the SYSTEM_DEFAULTS Table The statements in this example insert a default into the SYSTEM_DEFAULTS table on \MYSYS to set the default setting for the transaction isolation level: SET SCHEMA nonstop_sqlmx_mysys.
New System Defaults for Release 2.0 Reviewing and Setting System Defaults New System Defaults for Release 2.0 Table 6-1 lists the system defaults that are new for Release 2.0. For a complete list of system defaults and their default values, see the SQL/MX Reference Manual. Table 6-1. New System Defaults for Release 2.0 (page 1 of 3) Default Description CACHE_HISTOGRAMS_ REFRESH_INTERVAL Controls interval at which histograms are refreshed.
New System Defaults for Release 2.0 Reviewing and Setting System Defaults Table 6-1. New System Defaults for Release 2.0 (page 2 of 3) Default Description NOT_NULL_CONSTRAINT_ DROPPABLE_OPTION Default for DROPPABLE (ON) or NOT DROPPABLE for NOT NULL constraint. PM_OFFLINE_TRANSACTION_ GRANULARITY Number of rows that are copied in an offline MODIFY transaction. PM_ONLINE_TRANSACTION_ GRANULARITY Number of rows that are copied in an online MODIFY transaction.
New System Defaults for Release 2.0 Reviewing and Setting System Defaults Table 6-1. New System Defaults for Release 2.0 (page 3 of 3) Default Description SCRATCH_FREESPACE_ THRSHOLD_PERCENT Indicates how much free space, as a percentage, is left on a disk as a threshold. TEMPORARY_TABLE_HASH_ PARTITIONS Controls the partitioning scheme for triggers temporary tables. UPD_ABORT_ON_ERROR Controls whether an error that occurs during the performance of an insert, update, or delete causes an abort.
New System Default for Release 2.1 Reviewing and Setting System Defaults New System Default for Release 2.1 Table 6-2 describes the DDL_DEFAULT_LOCATIONS system default, which is new for Release 2.1. For information on using DDL_DEFAULT_LOCATIONS to specify one or more default volumes for the primary range partition in a CREATE statement that does not include a LOCATION clause, see Using DDL_DEFAULT_LOCATIONS to Distribute Primary Range Partitions on page 7-17. Note.
Reviewing and Setting System Defaults Default Settings to Review After Installing NonStop SQL/MX Default Settings to Review After Installing NonStop SQL/MX Alternative Settings For Selected System Defaults Table 6-3 lists the system defaults that you should consider changing after you install NonStop SQL/MX and before you run your applications. Table 6-3.
Alternative Settings For Selected System Defaults Reviewing and Setting System Defaults Table 6-3. Alternative Settings for Selected System Defaults (page 2 of 5) System Default Default Value Alternative Setting NAMETYPE The default value is ANSI. The value NSK is appropriate if the system uses physical Guardian names (NSK). The NSK setting applies to the resolution of unqualified SQL/MP object names. NATIONAL_CHARSET The default value is UCS2.
Reviewing and Setting System Defaults Alternative Settings For Selected System Defaults Table 6-3. Alternative Settings for Selected System Defaults (page 3 of 5) System Default Default Value Alternative Setting QUERY_CACHE_REQUIRED_ PREFIX_KEYS The default value is 255. A value greater than the number of columns in a composite key indicates all columns of the key are required.
Alternative Settings For Selected System Defaults Reviewing and Setting System Defaults Table 6-3. Alternative Settings for Selected System Defaults (page 4 of 5) System Default Default Value Alternative Setting REF_CONSTRAINT_NO_ ACTION_LIKE_RESTRICT The default value is SYSTEM. This default affects the behavior of a new referential constraint specified with NO ACTION.
Reviewing and Setting System Defaults Alternative Settings For Selected System Defaults Table 6-3. Alternative Settings for Selected System Defaults (page 5 of 5) System Default Default Value Alternative Setting SCRATCH_DISKS_ PREFERRED The default is for the system to determine what disks to use. Set to a list of scratch disk volumes, where each item in the list has the form \node.$volume, and the items in the list are separated by a comma (,).
Settings to Achieve ANSI Compliance Reviewing and Setting System Defaults Settings to Achieve ANSI Compliance Table 6-4 lists the system default settings required to achieve ANSI compliance. If these settings are used, all except these will have a significant negative impact on the performance of your database: • • • NAMETYPE RECOMPILATION_WARNINGS REF_CONSTRAINT_NO_ACTION_LIKE_RESTRICT Table 6-4.
7 Creating an SQL/MX Database When you install and initialize NonStop SQL/MX on a system node, NonStop SQL/MX creates the system catalog and several system schema. After the installation is complete, you can create your own user catalogs and schemas. Creating a catalog updates the system schema. The definition schema for a user catalog is created when you create the first schema in that catalog. For more information, see Creating SQL/MX Metadata on page 7-6.
Creating an SQL/MX Database • • • • Preparing to Create Your SQL/MX Database Use either valid SQL/MP aliases or Guardian names for SQL/MP objects. Make sure that all SQL/MP objects comply with the Guardian security conventions as described in the SQL/MP Installation and Management Guide. You can mix SQL/MP and SQL/MX tables in the same DML statement. You can join an SQL/MP and SQL/MX table, create a union of them, and insert data from an SQL/MP table into an SQL/MX table and visa versa.
Creating an SQL/MX Database • • SQL/MX Data File Naming Guidelines The subvolume name must be exactly eight characters long. All Guardian files representing data in a particular schema must have the same subvolume name regardless of the volume on which they reside. This subvolume name must match the subvolume name indicated in the SCHEMA_SUBVOLUME column of the system schema table NONSTOP_SQLMX_system-name.SYSTEM_SCHEMA.SCHEMATA.
Creating an SQL/MX Database Designating Wild Cards in SQL/MX Schema Names Designating Wild Cards in SQL/MX Schema Names An SQL/MX schema has a designated subvolume in which every partition belonging to that schema must reside. Therefore, in general, a Guardian wild card of the form $*.subvolume.* can serve as convenient shorthand for every Guardian file in the SQL/MX schemas whose schema subvolume is subvolume. Note.
Creating an SQL/MX Database Correcting File Name Problems name of the corresponding primary schema. If you are not creating an RDF backup of the database, you should omit the optional subvolume, in which case NonStop SQL/MX generates a new and unique subvolume name for the schema. In all cases, the schema subvolume name is written to the SCHEMA_SUBVOLUME column of the system schema table NONSTOP_SQLMX_system-name.SYSTEM_SCHEMA.SCHEMATA.
Creating an SQL/MX Database Creating SQL/MX Metadata For details and examples, see the information about MODIFY in the SQL/MX Reference Manual. Creating SQL/MX Metadata NonStop SQL/MX creates system metadata and user metadata tables that maintain records of the information that defines SQL/MX objects and track the use of those objects by other SQL/MX objects. When NonStop SQL/MX is installed and initialized on a node, the system catalog, NONSTOP_SQLMX_nodename, and its schemas are automatically created.
Creating an SQL/MX Database • Catalog Naming Cannot contain any other catalogs. Catalog Naming The catalog name is an SQL identifier that represents the first part of the three-part ANSI logical name of the form catalog.schema.name. The catalog’s SQL identifier must be unique among catalogs on the node. 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.
Creating an SQL/MX Database Reserved Catalogs metadata. For more information, see Section 13, Managing an SQL/MX Distributed Database. Reserved Catalogs Catalog names beginning with NonStop_SQLMX_ are reserved for the first part of the schema names and metadata tables that reside in the system catalog. You are not allowed to create or drop catalogs with these reserved names. Creating Schemas After you have created a catalog, you can begin to create its schemas.
Creating an SQL/MX Database Access Privileges for SQL/MX Database Objects This example creates a schema that is owned by the user gdavis and located on the volume $DATA08, and can only be performed by a super ID user: CREATE SCHEMA mycat.myschema AUTHORIZATION “gdavis” LOCATION $DATA08; The authorization ID must be the current authorization ID unless it is a super ID user. A super ID user can specify any currently valid authorization ID as the owner of the schema.
Creating an SQL/MX Database Granting and Revoking Access Privileges to SQL/MX Database Objects Granting and Revoking Access Privileges to SQL/MX Database Objects Use the GRANT statement to grant access privileges to one or more users for an SQL/MX table or view. You identify users with authorization IDs, which are valid Guardian user names enclosed in double quotes.
Creating an SQL/MX Database Creating SQL/MX Tables Creating SQL/MX Tables Tables are the foundation of an SQL/MX database. All data physically resides in tables. When you create a table with the CREATE TABLE statement, you specify the definition of each data column and the attributes of the physical file in which the table is to be stored. Carefully consider the file attributes to ensure that the table meets the needs of your application. Note.
Creating an SQL/MX Database • • Creating and Using Keys Partitioning key. See Creating and Using a Partitioning Key on page 7-14 and Creating Indexes for SQL/MX Tables on page 7-31. Partially decoupling the clustering and partitioning keys. See Partially Decoupling the Clustering Key and the Partitioning Key on page 7-15. Creating and Using a Primary Key A primary key is the column or set of columns that defines a unique key for a table.
Creating an SQL/MX Database Creating and Using Keys Create a clustering key by using one of these methods: • • • Use STORE BY key-column-list in CREATE TABLE to specify the columns that compose the clustering key. The key columns must be specified as NOT NULL NOT DROPPABLE and cannot have a combined length of more than 247 bytes. Use STORE BY PRIMARY KEY to base the clustering key on the primary key columns.
Creating and Using Keys Creating an SQL/MX Database NonStop SQL/MX generates a SYSKEY column and appends that to the clustering key. CREATE TABLE SALES.
Creating an SQL/MX Database Creating Table Partitions When you create range partitioned tables, the set of columns you specify for the partitioning key can be identical to or a subset of the clustering key columns (excluding the SYSKEY, if present), and you can specify the columns in any order.
Creating an SQL/MX Database Creating Table Partitions reduce the time required for table scans by a factor almost equal to the number of partitions. Note. SQL/MX tables must have a user-defined clustering key to have partitions. You can partition any table or index provided its clustering key is not just the SYSKEY. 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 Table Partitions Partitions require no special access procedures. NonStop SQL/MX manages partition access for you automatically. Partitions enable NonStop SQL/MX to more readily process queries in parallel. Partitions allow you to have tables larger than the size of a single disk volume. Partitioning the indexes of a table enables NonStop SQL/MX to take maximum advantage of parallel index updates.
Creating an SQL/MX Database Creating Table Partitions Special Considerations for Decision Support Systems (DSS) Applications DSS applications typically require periodic addition and deletion of data. Further, access must be well balanced. For NonStop SQL/MX, the partitioning key can be different from the clustering key. The choice of leftmost primary key column—and the partitioning strategy—can greatly affect access.
Creating an SQL/MX Database Additional Guidelines for Creating Tables Example for Creating an SQL/MX Table With a Single Hash Partition CREATE TABLE cat1.sch1.ordersh (location char(16) not null not droppable, ordernumber integer unsigned not null not droppable, ordertime timestamp, primary key (location, ordernumber) not droppable) location $DATA01 hash partition; Example for Creating an SQL/MX Table With Multiple Hash Partitions CREATE TABLE cat1.sch1.
Creating an SQL/MX Database Managing Table Data that has the NO CLEARONPURGE attribute, the system deallocates disk space, but does not physically destroy the data in that disk space. This approach improves the performance of the drop statement by reducing the number of writes to the disk, but when that disk space is allocated to a new file, other users might be able to read data left by the object that previously used the space.
Creating an SQL/MX Database Managing Table Data When you define a column, you specify the column name, data type, and, optionally, other column attributes. To define columns for a table, you can specify the column definitions in the CREATE TABLE statement or in the ALTER TABLE with the ADD COLUMN clause. However, HP recommends that you avoid using the ALTER TABLE with the ADD clause if possible insofar as table performance is degraded by adding columns.
Creating an SQL/MX Database • Managing Table Data For compatibility of SQL/MX data types, two data types are comparable if a value of one data type can be compared to a value of another data type. Two data types are compatible if a value of one data type can be assigned to a column of the other data type. Compatible data types are also comparable. Any numeric data type can be compared with all other numeric data types in DML comparison expressions.
Managing Table Data Creating an SQL/MX Database Defining Character Data NonStop SQL/MX includes both fixed-length character data and variable-length character data. The data types for character data are: Fixed-length characters CHAR PIC NCHAR, NATIONAL CHAR, or NATIONAL CHARACTER Variable-length characters CHAR VARYING VARCHAR NCHAR VARYING or NATIONAL CHAR VARYING For NonStop SQL/MX, the maximum row size for fixed-length and variable-length character columns is 4040 bytes.
Managing Table Data Creating an SQL/MX Database Restrictions on Using Character Set Data Use only ISO88591 characters in these contexts: ISO88591 Field Where Found BY partitioning-column CREATE INDEX, CREATE TABLE CHECK constraint text CREATE TABLE, ALTER TABLE Column default values CREATE TABLE, ALTER TABLE, CREATE VIEW Column HEADING text CREATE TABLE, ALTER TABLE FIRST KEY values CREATE TABLE, CREATE INDEX, MODIFY TABLE sql-datatype character string data types CREATE PROCEDURE SQL/MX name
Managing Table Data Creating an SQL/MX Database When NonStop SQL/MX performs arithmetic operations on operands that have mixed data types, the data type allowing the largest value is used to evaluate the numbers. For instance, if a REAL number is used and REAL is the data type that allows the largest value, all other numeric data types are converted first to REAL and then used in the expression.
Managing Table Data Creating an SQL/MX Database 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 • • • • • • • • • • The range of fields defined for an INTERVAL column can limit the value stored, as shown: COLUMN_1 INTERVAL YEAR COLUMN_2 INTERVAL HOUR (3) COLUMN_3 INTERVAL YEAR TO MONTH COLUMN_4 INTERVAL DAY TO MINUTE The fields in a datetime or INTERVAL value have this implied order: YEAR, MONTH, DAY, HOUR, MINUTE, SECOND.
Creating an SQL/MX Database Managing Table Data Using Default and Null Values In NonStop SQL/MX, a null value is a marker that indicates that a column in a specified row has no value. The null value is not treated as a normal data value. It serves only as a placeholder necessary for certain relational operations. To an application interacting with a database, the null value indicates “unknown” or “do not know.
Creating an SQL/MX Database ° • • Managing Table Data If the number of digits to the left of the decimal point exceeds the number in the length (or length minus scale, if you specified scale for the column), NonStop SQL/MX issues an error message. ° For a datetime column, literal must be datetime literal with a precision that matches the precision of the column. ° For an INTERVAL column, literal must be an INTERVAL literal that has the range of INTERVAL fields defined for the column.
Creating an SQL/MX Database Managing Table Data this type of column as NOT NULL. If you define the unique key through a unique constraint, the column must be NOT NULL. Unique multicolumn indexes can contain columns with null values. The same rule applies as for single-column unique indexes; that is, the index can have at most one row of all null values in the columns. Null values are treated as all other values and therefore cause duplication of rows in the same way.
Creating an SQL/MX Database Creating Indexes for SQL/MX Tables Understanding Data Type Correspondence With Host Languages For information about data type correspondence between NonStop SQL/MX and the host programming languages used for embedded SQL/MX, see the SQL/MX Programming Manual for C and COBOL and the SQL/MX Programming Manual for Java. Creating Indexes for SQL/MX Tables An index provides an alternate access path to a table that differs from the inherent access path (clustering key).
Creating an SQL/MX Database Creating an Index Creating an Index To create an index, use the CREATE INDEX statement. If the underlying table contains data, the creation process automatically loads the index unless you specify NO POPULATE. When you define an alternate index, first consider the column-related guidelines described under Defining Table Columns on page 7-20.
Creating an SQL/MX Database • • • • • • Partitioning Indexes Indexes can be updated in parallel by the disk process while the table is being updated. To take full advantage of parallel updating, you should create a table’s indexes on separate disk volumes, with each disk volume configured for a separate processor. The performance effects of parallel updates are discussed in Section 16, Enhancing SQLMX Database Performance.
Creating an SQL/MX Database Partitioning Indexes Use the MODIFY INDEX utility to manage range-partitioned and hash-partitioned indexes. For detailed information and examples, see the information on using MODIFY to manage table and index partitions in Section 10, Reorganizing SQL/MX Tables and Maintaining Data. An important consideration when you partition indexes is whether to co-locate the index partitions with the base table partitions.
Creating an SQL/MX Database Creating Constraints on SQL/MX Tables Creating Constraints on SQL/MX Tables Constraints are rules that protect the integrity of data in a table by restricting the values in a particular column or set of columns to those that meet the conditions of the constraints. NonStop SQL/MX enforces the constraint criteria when the constraint is created, when table rows are updated and or inserted, and when table rows are deleted.
Creating an SQL/MX Database Naming Constraints If you define several NOT NULL NOT DROPPABLE constraints on a table, NonStop SQL/MX automatically combines them into one constraint. This approach ensures that, at run time, only one constraint check is performed. If you perform a SHOWDDL, all constraint information is shown as one constraint. Combining constraints is implemented only for NOT NULL NOT DROPPABLE constraints.
Creating an SQL/MX Database Guidelines for Creating Constraints When working with large tables, you should create the supporting index for each table before you create the constraint. You precreate the index by issuing a CREATE [UNIQUE] INDEX on the columns you want to specify in the constraint. UNIQUE is required for the PRIMARY KEY and UNIQUE constraints. Then you issue the ALTER TABLE ADD CONSTRAINT statement.
Creating an SQL/MX Database Rules for Creating and Accessing Views subqueries— from several SQL/MX tables. You cannot use columns from SQL/MP tables. Use the CREATE VIEW statement to define new column names for a view, instead of using the column names from the underlying tables. When using the view, applications use the view-defined column names. Applications can also use views to rename, reorder, and project subsets of columns from one or more tables.
Creating an SQL/MX Database Creating Triggers Creating Triggers Use the CREATE TRIGGER statement to create triggers on SQL/MX tables. A trigger is a mechanism that sets up the database to perform certain actions automatically in response to the occurrence of specified events. To create a trigger, you must own its schema or be the super ID user. You must also own the schema where the subject table resides and have appropriate privileges on the referenced table.
Creating an SQL/MX Database ° ° • • • • • • • ° Align UCS2 character types on two-byte boundaries. Align numeric data types as follows: ° ° ° ° Database Design Guidelines for Improving OLTP Performance Align smallint on two-byte boundaries. Align int on four-byte boundaries. Align largeint on eight-byte boundaries. Use fillers as required to attain proper alignment: ° Recommended: CREATE TABLE t1, where column a is SMALLINT NOT NULL, c is SMALLINT NOT NULL, and b is INT NOT NULL.
8 Querying SQL/MX Metadata Use the commands described in this section to obtain the information you need to manage a database.
Commands for Displaying Information Querying SQL/MX Metadata NonStop SQL/MX stores information about object structures in file labels, resource forks, and metadata, as follows: File labels Contain file structure information and security settings. Use the MXCI SHOWLABEL command to obtain file-label information. Most of the information in file labels is the same as the metadata.
Querying SQL/MX Metadata mxtool INFO Command For detailed information and examples of this command, see the SQL/MX Reference Manual. mxtool INFO Command Use the mxtool INFO command to display information about a database, including SQL/MX-related information for a given Guardian file set list. Because access to metadata is open to all users, anyone can run the mxtool INFO command. For detailed information and examples of this command, see the SQL/MX Reference Manual.
Querying SQL/MX Metadata SHOWDDL Command SHOWDDL Command Use the MXCI SHOWDDL command to display the DDL syntax equivalent to the syntax that was used to create an SQL/MX table, view, or SPJ and its dependent objects. This command also supports SQL/MP alias names. You can choose to display the DDL for the underlying SQL/MP object and its dependent objects either in SQL/MX or in SQL/MP DDL syntax. The command is especially useful for displaying a thorough description of an object as it exists in metadata.
SQL/MX Metadata Tables Querying SQL/MX Metadata SQL/MX Metadata Tables Figure 8-1 shows the SQL/MX metadata table structure. Figure 8-1. SQL/MX Metadata Table Structure Catalog Schemas Note: User catalogs are not shown.
SQL/MX Metadata Tables Querying SQL/MX Metadata Figure 8-1 on page 8-5 for that schema. User catalogs do not contain the other metadata schemas shown in Figure 8-1 on page 8-5. • There is one set of statistics tables per schema. These are not shown in Figure 8-1 on page 8-5. Table 8-1 lists the SQL/MX metadata tables located in the system catalog, including the clustering keys for each table.
SQL/MX Metadata Tables Querying SQL/MX Metadata Table 8-1.
SQL/MX Metadata Tables Querying SQL/MX Metadata Table 8-1.
SQL/MX Metadata Tables Querying SQL/MX Metadata Table 8-1.
SQL/MX Metadata Tables Querying SQL/MX Metadata Table 8-1.
SQL/MX Metadata Tables Querying SQL/MX Metadata Table 8-1.
SQL/MX Metadata Tables Querying SQL/MX Metadata Table 8-1.
SQL/MX Metadata Tables Querying SQL/MX Metadata Table 8-1.
Understanding ANSI External and Internal Names Querying SQL/MX Metadata Understanding ANSI External and Internal Names ANSI SQL objects have both external and internal names. When objects are created in NonStop SQL/MX, they are given external names. However, when the same objects are stored in the metadata, they are stored by their internal name. You need to be aware of external and internal naming when performing metadata queries.
Displaying System Schema Information Querying SQL/MX Metadata for read uncommitted access order by object_name; Note that "my "" catalog" is in external format, and 'SALES' and 'my " catalog' are in internal format. The query does not work correctly if the internal and external names are incorrect.
Querying SQL/MX Metadata • Locating System Schema Tables This example uses the Guardian FILES command from a TACL prompt to list the locations of the associated Guardian files for system schema tables on a local node. The FUP INFO, DETAIL command shows the equivalent ANSI names. 1> fup copy $system.zsqlmx.mxanchor SQLMX_MetaData_Loc=$data08 2> volume $data08.zsd0 3> files $DATA08.
Locating System Schema Tables Querying SQL/MX Metadata • This example also uses TACL commands to locate the MXCS schema tables on ZSD1. 1> fup copy $system.zsqlmx.mxanchor SQLMX_MetaData_Loc=$DATA08 2> volume $data08.zsd1 3> files $DATA08.ZSD1 ASSOC200 DTQSFJ00 PX1KHJ00 Z9FPGJ00 ASSOC201 DTQSFJ01 PX1KHJ01 Z9FPGJ01 B7NMCJ00 ENVIRO00 Q4DNDJ00 B7NMCJ01 ENVIRO01 Q4DNDJ01 DATASO00 NAME2I00 RESOUR00 DATASO01 NAME2I01 RESOUR01 4> fup info assoc200, detail $DATA08.ZSD1JHD.
Locating System Defaults Schema Tables Querying SQL/MX Metadata • This is an example from the OSS prompt: # cat /G/system/zsqlmx/mxanchor SQLMX_MetaData_Loc=$data08 # ls /G/data08/zsd0 alluid00 catsys00 alluid01 catsys01 catref00 schema00 catref01 schema01 schrep00 schrep01 # mxtool info \$data08.zsd0.alluid00 NonStop SQL/MX MXTOOL Utility 2.0 (c) Copyright 2003 Hewlett-Packard Development Company, LP. All Rights Reserved. File Name: \FIGARO.$DATA08.ZSD0.
Querying SQL/MX Metadata Locating System Defaults Schema Tables SMDtable: F File Organization: Key-sequenced Block Length: 4096 File Code: 563 AuditCompress: T Broken: F Buffered: T ClearOnPurge: F Corrupt: F (If T, a Utility operation is in progress or has failed) CrashLabel: F CrashOpen: F IncompletePartBoundChg: F (If T, a Utility operation is in progress or has failed) RedoNeeded: F RollfwdNeeded: F UndoNeeded: F UnreclaimedSpace: F (If T, a Utility operation is in progress or has failed) Primary Exte
Locating System Defaults Schema Tables Querying SQL/MX Metadata (select schema_uid from nonstop_sqlmx_figaro.system_schema.schemata where schema_name = 'SYSTEM_DEFAULTS_SCHEMA' and cat_uid = -- get catalog uid for CAT (select cat_uid from nonstop_sqlmx_figaro.system_schema.catsys where cat_name = 'NONSTOP_SQLMX_FIGARO' ) ) for read uncommitted access; TABLE_NAME -----------------SYSTEM_DEFAULTS SYSTEM ------\FIGARO DATA_SOURCE ----------$DATA08 FILE_SUFFIX ----------------ZSDA984F.
Locating MXCS Schema Tables Querying SQL/MX Metadata Locating MXCS Schema Tables On a local node, the anchor file $SYSTEM.ZSQLMX.MXANCHOR contains a pointer to the location of the system catalog. The system catalog contains the MXCS_SCHEMA, which contains tables used by MXCS and the user metadata tables. The MXCS schema tables are located on the same volume as the system schema tables, but in the subvolume ZSD1. The underlying Guardian names of the tables in this schema have preassigned names.
Displaying Catalog Information Querying SQL/MX Metadata Displaying Catalog Information This subsection discusses: • • • • • • Displaying a Catalog UID on page 8-22 Displaying All Catalogs Visible on a Node on page 8-23 Determining Whether a Catalog is Local or Remote on page 8-23 Displaying All Schemas in a Catalog on page 8-24 Displaying the Guardian Location of the Metadata Tables for a Catalog on page 8-24 Displaying the Attributes of a Catalog on page 8-25 Related topic: • Displaying All Tables i
Displaying All Catalogs Visible on a Node Querying SQL/MX Metadata Displaying All Catalogs Visible on a Node The CATSYS table contains one (and only one) row for each catalog that is visible on that node. This example displays a list of all the visible catalogs on a local node (where node-name is the name of the local node): >> select cat_name from nonstop_sqlmx _node-name.system_schema.
Displaying All Schemas in a Catalog Querying SQL/MX Metadata Displaying All Schemas in a Catalog This example displays a list of all schemas in a catalog: >> set schema nonstop_sqlmx_figaro.system_schema; --- SQL operation complete. >> select substring(schema_name, 1, 30) as schema_name, schema_uid from schemata s, catsys c where cat_name = 'SAMDBCAT' and s.cat_uid = c.
Displaying the Attributes of a Catalog Querying SQL/MX Metadata MVS00000 MVSTIN00 PARTNS00 RIUU0000 TBLPRI00 TRGUSD00 VCOLTC00 VWTBLU00 MVS00001 MVSTIN01 PARTNS01 RIUU0001 TBLPRI01 TRGUSD01 VCOLTC01 VWTBLU01 MVSCOL00 MVSUSE00 REFCON00 ROUTIN00 TBLTEX00 TRIGS000 VCOLU000 MVSCOL01 MVSUSE01 REFCON01 ROUTIN01 TBLTEX01 TRIGS001 VCOLU001 MVSJCL00 OBJECT00 REPLIC00 TBLCON00 TRGCU000 VCOLTA00 VIEWS000 MVSJCL01 OBJECT01 REPLIC01 TBLCON01 TRGCU001 VCOLTA01 VIEWS001 Displaying the Attributes of a Catalog This
Displaying a Schema UID Querying SQL/MX Metadata • • • • • Displaying All Tables in a Schema on page 8-32 Displaying All Views in a Schema on page 8-36 Displaying the Schema Version on page 8-62 Displaying the System Schema Version on page 8-63 Displaying the Object Schema Version (OSV) on page 8-63 The SCHEMATA table on each node is a system schema table that describes all the schemas visible from the node.
Querying SQL/MX Metadata Displaying All Schemas Visible on a Node Displaying All Schemas Visible on a Node This example selects information from the CATSYS and SCHEMATA tables to display a list of all the schemas visible on a node: >> set schema nonstop_sqlmx_figaro.system_schema; --- SQL operation complete. >> select substring(schema_name,1,30) as schema_name, substring(cat_name,1,20) as cat_name, node_name from schemata s, catsys c, cat_references cr where s.cat_uid=c.cat_uid and c.cat_uid=cr.
Displaying All Objects in a Schema Querying SQL/MX Metadata Displaying All Objects in a Schema This example selects information from the CATSYS, SCHEMATA, and OBJECTS tables to display a list of all the objects within the SALES schema in the SAMDBCAT catalog. You can use a similar set of SELECT statements to display all the views or indexes on a node. >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete.
Displaying the Attributes of a Schema Querying SQL/MX Metadata Displaying the Attributes of a Schema This example displays most of the attributes (in other words, SCHEMA_OWNER, SCHEMA_VERSION, SCHEMA_SUBVOLUME, and others) for the schema SAMDBCAT. SALES: >> set schema nonstop_sqlmx_figaro.system_schema; --- SQL operation complete. >> select substring(s.schema_name, 1, 15) as name, s.schema_owner as owner, s.schema_version as version, s.schema_subvolume as subvol, case s.
Querying SQL/MX Metadata Displaying Table Information Displaying Table Information Topics in this subsection: • • • • • Displaying All Tables in a Catalog on page 8-31 Displaying All Tables in a Schema on page 8-32 Displaying All DDL Locks on a Table on page 8-33 Displaying the Attributes of a Table on page 8-34 Displaying All Tables on a System on page 8-35 Related topics: • • • • • • • • • • Locating System Schema Tables on page 8-15 Locating System Defaults Schema Tables on page 8-18 Determining W
Displaying All Tables in a Catalog Querying SQL/MX Metadata Displaying All Tables in a Catalog This example displays all base table objects in the catalog SAMDBCAT: >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select substring (object_name, 1, 25) as table_name, substring(schema_name, 1, 30) as schema_name from nonstop_sqlmx_figaro.system_schema.catsys c, nonstop_sqlmx_figaro.system_schema.schemata s, objects o where c.cat_uid = s.cat_uid and c.
Displaying All Tables in a Schema Querying SQL/MX Metadata DEPT EMPLOYEE HISTOGRAMS HISTOGRAM_INTERVALS JOB PROJECT CUSTOMER HISTOGRAMS HISTOGRAM_INTERVALS ODETAIL ORDERS PARTS PERSNL PERSNL PERSNL PERSNL PERSNL PERSNL SALES SALES SALES SALES SALES SALES Displaying All Tables in a Schema This example displays all table objects in the schema SALES and catalog SAMDBCAT. Table objects include base tables, views, MP aliases, and SPJs: >> set schema samdbcat.
Displaying All DDL Locks on a Table Querying SQL/MX Metadata Displaying All DDL Locks on a Table This example displays the DDL locks on the table SAMDBCAT.SALES.CUSTOMER: >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select substring (o1.object_name from 1 for 25) as lock_name, l.operation as util, l.status, substring (l.process_id from 1 for 25) from objects o, objects o1, ddl_locks l where o.object_name = 'CUSTOMER' and o.object_uid = l.base_object_uid and l.
Displaying the Attributes of a Table Querying SQL/MX Metadata Displaying the Attributes of a Table This example displays some of the attributes of the base tables defined in the SAMDBCAT.SALES schema: >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete.
Querying SQL/MX Metadata Displaying All Tables on a System Displaying All Tables on a System Displaying all tables on a node (system) is a two-step process. To display all tables on a node, you must: 1. Run a query that displays all the catalogs on a node (see Displaying All Catalogs Visible on a Node on page 8-23). 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-31).
Displaying All Views in a Schema Querying SQL/MX Metadata Displaying All Views in a Schema This example displays all views in the schema SALES in the catalog SAMDBCAT: >>set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >>select substring (object_name, 1, 25) as view_name, object_name_space as name_space from nonstop_sqlmx_figaro.system_schema.catsys c, nonstop_sqlmx_figaro.system_schema.schemata s, objects o where c.cat_uid = s.cat_uid and c.cat_name = 'SAMDBCAT' and s.
Querying SQL/MX Metadata Displaying Information About SQL/MP Aliases Displaying Information About SQL/MP Aliases Topics in this subsection: • • Displaying All SQL/MP Alias Names in a Schema on page 8-37 Displaying All Attributes of an SQL/MP Alias Name on page 8-38 The MP_PARTITIONS table contains the partition names of SQL/MP tables that have SQL/MP aliases. For detailed information about all metadata tables, see Table 8-1 on page 8-6 or the SQL/MX Reference Manual.
Querying SQL/MX Metadata Displaying All Attributes of an SQL/MP Alias Name Displaying All Attributes of an SQL/MP Alias Name This example displays some of the attributes for the SQL/MP aliases in the SAMDBCAT.SALES schema: >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select substring(object_name,1,25) as SQLMP_Alias, m.mppartition_name from nonstop_sqlmx_figaro.system_schema.catsys c, nonstop_sqlmx_figaro.system_schema.
Querying SQL/MX Metadata Determining Whether a Table Has Indexes Related topics: • • Displaying All Partitions for a Table or Index on page 8-44 Displaying All Columns in an Index on page 8-52 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. For detailed information about all metadata tables, see Table 8-1 on page 8-6 or the SQL/MX Reference Manual.
Displaying All Indexes for a Table Querying SQL/MX Metadata NUM_INDEXES -------------------1 Displaying All Indexes for a Table This example (when run on the node on which the metadata is located) displays all indexes for a given table: >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select substring (o1.object_name, 1, 40) as index_name from samdbcat.definition_schema_version_1200.access_paths a, samdbcat.definition_schema_version_1200.objects o1 where a.
Displaying All DDL Locks on an Index Querying SQL/MX Metadata Displaying All DDL Locks on an Index This example (when run on the node on which the metadata is located) displays the DDL locks on the index SAMDBCAT.SALES.XCUSTNAM: >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select substring (o1.object_name from 1 for 25) as lock_name, l.operation as util, l.status, substring (l.process_id from 1 for 25) from objects o, objects o1, ddl_locks l where o.
Displaying the Attributes of an Index Querying SQL/MX Metadata Displaying the Attributes of an Index This example (when run on the node on which the metadata is located) displays some of the attributes for the index SAMDBCAT.SALES.XCUSTNAM: >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select substring(o1.
Querying SQL/MX Metadata Displaying the State of Indexes for a Table Displaying the State of Indexes for a Table This example displays the state of the indexes for the table SAMDBCAT.SALES.CUSTOMER: >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select substring (o1.object_name, 1, 40) as index_name, case a.valid_data when 'Y' then 'populated' else 'unpopulated' end as index_status from samdbcat.definition_schema_version_1200.access_paths a, samdbcat.
Displaying Partition Information Querying SQL/MX Metadata Displaying Partition Information Topics in this subsection: • • Displaying All Partitions for a Table or Index on page 8-44 Displaying Attributes of a Partition on page 8-45 The PARTITIONS table, located in the schema DEFINITION_SCHEMA_VERSION_version-number of each catalog, describes all partitions in a catalog. For detailed information about all metadata tables, see Table 8-1 on page 8-6 or the SQL/MX Reference Manual.
Displaying Attributes of a Partition Querying SQL/MX Metadata the MXCI SHOWLABEL command with the DETAIL option to display a list of partitions for a table or an index. For examples of the SHOWDDL and SHOWLABEL commands, see the SQL/MX Reference Manual. Displaying Attributes of a Partition This example displays attributes for the partitions for the table SAMDBCAT.SALES.CUSTOMER. To display additional fields, select different fields from the PARTITIONS table: >> set schema samdbcat.
Querying SQL/MX Metadata Displaying Constraint Information Displaying Constraint Information Topics in this subsection: • • • Determining Whether a Table Has Constraints on page 8-46 Displaying All Constraints on a Table on page 8-47 Displaying the Attributes of a Constraint on page 8-48 Related topics: • • • Displaying All Columns in a Primary Key or Unique Constraint on page 8-53 Displaying All Columns in a NOT NULL Constraint on page 8-54 Displaying All Columns in a Referential Integrity Constrain
Displaying All Constraints on a Table Querying SQL/MX Metadata ) ) for read uncommitted access; NUM_CONSTRAINTS -------------------2 Displaying All Constraints on a Table This example displays all constraints for a given table. >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select substring (o1.object_name, 1, 30) as constraint_name, case (t.
Displaying the Attributes of a Constraint Querying SQL/MX Metadata Displaying the Attributes of a Constraint This example displays some of the attributes of the constraints in the table SAMDBCAT.SALES.CUSTOMER. To display additional fields, select the fields from the TBL_CONSTRAINTS table: >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select t.constraint_uid, t.table_uid, case t.
Querying SQL/MX Metadata • • • Displaying Column Information Displaying All Columns in a Referential Integrity Constraint on page 8-55 Displaying the Attributes of a Column on page 8-56 Displaying All Tables Containing a Selected Column on page 8-57 Related topic: • Displaying All Privileges for a Column on page 8-61 The COLS table, located in the schema DEFINITION_SCHEMA_VERSION_version-number of each catalog, describes the columns in all tables and views in that catalog.
Displaying All Columns in a Table Querying SQL/MX Metadata Displaying All Columns in a Table This example displays all columns in the table SAMDBCAT.PERSNL.EMPLOYEE: >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select substring(column_name, 1, 25) as column_name, column_number from cols c, objects o where c.object_uid = o.object_uid and o.object_name = 'EMPLOYEE' and o.object_type = 'BT' and o.schema_uid = (select schema_uid from nonstop_sqlmx_figaro.system_schema.
Displaying All Columns in a View Querying SQL/MX Metadata Displaying All Columns in a View This example displays all columns in the view SAMDBCAT.SALES.CUSTLIST: >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select substring(column_name, 1, 25) as column_name, column_number from cols c, objects o where c.object_uid = o.object_uid and o.object_name = 'CUSTLIST' and o.object_type = 'VI' and o.schema_uid = (select schema_uid from nonstop_sqlmx_figaro.system_schema.
Displaying All Columns in an Index Querying SQL/MX Metadata Displaying All Columns in an Index This example displays all columns in the index SAMDBCAT.SALES.XCUSTNAM in the schema SAMDBCAT.SALES: >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select substring(c.column_name, 1, 25) as column_name, c.column_number as base_table_column, ac.system_added_column from access_paths ap, access_path_cols ac, cols c, objects o where ap.access_path_uid = o.object_uid and ap.
Displaying All Columns in a Primary Key or Unique Constraint Querying SQL/MX Metadata Displaying All Columns in a Primary Key or Unique Constraint This example displays all columns with primary key or unique constraints for the table SAMDBCAT.SALES.ODETAIL; two columns of this table make up the primary key: >> set schema samdbcat.definition_schema_version_1200; >> select substring(c.column_name, 1, 15) as column_name, c.column_number as col_num, case t.
Displaying All Columns in a NOT NULL Constraint Querying SQL/MX Metadata Displaying All Columns in a NOT NULL Constraint This example displays all columns with NOT NULL constraints for the table SAMDBCAT.SALES.ODETAIL: >>set schema samdbcat.definition_schema_version_1200; >> select substring(c.column_name, 1, 15) as column_name, c.column_number as col_num, case o2.
Displaying All Columns in a Referential Integrity Constraint Querying SQL/MX Metadata Displaying All Columns in a Referential Integrity Constraint This example displays all columns with referential integrity constraints for the table SAMDBCAT.SALES.ODETAIL: >> alter table samdbcat.sales.odetail add constraint p1 foreign key (partnum) references samdbcat.sales.parts(partnum); >> set schema samdbcat.definition_schema_version_1200; >> select substring(c.column_name, 1, 15) as column_name, c.
Displaying the Attributes of a Column Querying SQL/MX Metadata Displaying the Attributes of a Column This example displays some of the attributes of the columns in the table SAMDBCAT.PERSNL.EMPLOYEE. To display additional attributes, select different fields from the COLS table: >> set schema samdbcat.definition_schema_version_1200; --- SQL operation complete. >> select substring(c.column_name, 1, 15) as column_name, c.column_number as col_num, c.sql_data_type, case c.
Querying SQL/MX Metadata Displaying All Tables Containing a Selected Column Displaying All Tables Containing a Selected Column This example displays all tables in schema SAMDBCAT.SALES that contain the column ORDERNUM and displays the SQL data type of the column: >> select substring(column_name, 1, 20) as column_name, sql_data_type, substring(object_name, 1, 20) as table_name from cols c, objects o where c.object_uid = o.object_uid and column_name = 'ORDERNUM' and o.object_type = 'BT' and o.
Querying SQL/MX Metadata Displaying Information About Privileges Displaying Information About Privileges Topics in this subsection: • • • Displaying All Privileges for a Table on page 8-59 Displaying All Privileges for a View on page 8-60 Displaying All Privileges for a Column on page 8-61 The TBL_PRIVILEGES table, located in the schema DEFINITION_SCHEMA_VERSION_version-number of each catalog, describes the privileges for all tables and views in that catalog.
Displaying All Privileges for a Table Querying SQL/MX Metadata Displaying All Privileges for a Table This example displays all privileges for the table SAMDBCAT.SALES.ORDERS: >>set schema samdbcat.definition_schema_version_1200; --- SQL operation complete.
Displaying All Privileges for a View Querying SQL/MX Metadata Displaying All Privileges for a View This example displays all privileges for the view SAMDBCAT.SALES.CUSTLIS: >>set schema samdbcat.definition_schema_version_1200; --- SQL operation complete.
Displaying All Privileges for a Column Querying SQL/MX Metadata Displaying All Privileges for a Column This example displays all privileges for the column EMPNUM in the table SAMDBCAT.PERSNL.EMPLOYEE: >>set schema samdbcat.definition_schema_version_1200; --- SQL operation complete.
Querying SQL/MX Metadata Displaying Object Integrity and Consistency Displaying Object Integrity and Consistency The mxtool VERIFY command detects and reports inconsistencies between the information contained in the metadata, the resource forks, and the file labels. It does not verify data integrity. This command is often used together with the mxtool INFO command, which displays various aspects of a database.
Querying SQL/MX Metadata Displaying the System Schema Version 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.schema_version from nonstop_sqlmx_node.system_schema.schemata s, nonstop_sqlmx_node.system_schema.catsys c where c.cat_name = 'NONSTOP_SQLMX_node' and c.cat_uid = s.cat_uid and s.
Querying SQL/MX Metadata Displaying the Object Feature Version (OFV) HP NonStop SQL/MX Installation and Management Guide—523723-004 8- 64
9 Adding, Altering, and Dropping SQL/MX Database Objects After you create an SQL/MX database, you can assume the database is consistent and the application data is valid. Database management operations that add, alter, or drop database objects must maintain this same level of data consistency and validity, and must be planned carefully.
Adding, Altering, and Dropping SQL/MX Database Objects Adding Objects to an SQL/MX Database Adding Objects to an SQL/MX Database Table 9-1 summarizes the objects you can add to an SQL/MX database and identifies the statement, command, or utility required for each add operation. Table 9-1.
Adding, Altering, and Dropping SQL/MX Database Objects Authorization Requirements for Adding Database Objects Authorization Requirements for Adding Database Objects Table 9-2 describes the authorization requirement for each add operation. Table 9-2. Authorization Requirements for Adding Database Objects Operation Authorization Requirements Adding Catalogs Any user on a node can use the CREATE CATALOG statement to add a catalog to the node.
Adding, Altering, and Dropping SQL/MX Database Objects Adding Catalogs Adding Catalogs To add a catalog, use the CREATE CATALOG statement. A catalog is owned by the user ID that created it, although catalog ownership does not imply authorization over schemas or objects in that catalog. Each of the schemas described in a catalog has an owner. A catalog can contain multiple schemas, each possibly owned by a different user. A catalog cannot contain other catalogs.
Adding, Altering, and Dropping SQL/MX Database Objects Adding Columns Adding Columns 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 Java 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. SQL applications that are running while you make these changes will still undergo automatic recompilation.
Adding, Altering, and Dropping SQL/MX Database Objects Adding Constraints Example of Adding a Column to a Table This 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 Java. For information about using DISPLAY USE OF, see Checking Module Dependencies With DISPLAY USE OF on page 11-20 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.
Adding, Altering, and Dropping SQL/MX Database Objects Adding Indexes There is no restriction on the number of indexes per table, and there is no restriction on the number of partitions an index supports, but HP recommends a maximum of 512 partitions. Consider these 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.
Adding, Altering, and Dropping SQL/MX Database Objects Adding Indexes 4. Enter an UPDATE STATISTICS statement to update the statistical information stored in the catalog and get statistics on the new index. 5. SQL compile (recompile) any SQL applications that use the table with the EXPLAIN option to determine whether the index is the chosen path. 6. Test the same queries against the tables by using DISPLAY STATISTICS to obtain the new statistical information. 7. Determine any improvement in performance.
Adding, Altering, and Dropping SQL/MX Database Objects Adding Partitions to Tables and Indexes 7. Enter an UPDATE STATISTICS statement to update the statistical information stored in the catalog and get statistics on the new index. 8. 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 and the SQL/MX Programming Manual for Java. 9.
Adding, Altering, and Dropping SQL/MX Database Objects • • Adding Partitions to Tables and Indexes A certain subset of a remote table’s data is accessed more frequently at the local node than from a remote node, partitioning the table so that the frequently accessed portion of the data resides on the local node can increase local performance. Queries are processed in parallel, partitioning a table or index is often required.
Adding, Altering, and Dropping SQL/MX Database Objects Adding Schemas the SQL/MX Programming Manual for C and COBOL and the SQL/MX Programming Manual for Java. 8. Make new TMF online dumps of all partitions of the table or index. For information about redistributing rows across partitions, see Section 10, Reorganizing SQL/MX Tables and Maintaining Data. Example of Adding a Hash Partition to a 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 name must match the subvolume name indicated in the system schema column SCHEMA_SUBVOLUME, described in the system schema table SCHEMATA. NonStop SQL/MX does not prevent you from specifying a subvolume name that is already in use by another schema. However, HP strongly recommends that each schema use a different subvolume. This avoids problems when RDF and other features are needed.
Adding, Altering, and Dropping SQL/MX Database Objects Adding Stored Procedures in Java (SPJs) Adding Stored Procedures in Java (SPJs) Use the CREATE PROCEDURE statement to add an SPJ to your SQL/MX database. For information and examples, see the SQL/MX Guide to Stored Procedures in Java. Adding Tables Use the CREATE TABLE statement to add a table to your SQL/MX database. The name for the new table must be unique among names of tables, views, SQL/MP aliases, and procedures within its schema.
Adding, Altering, and Dropping SQL/MX Database Objects Adding Triggers For information about explicit and automatic recompilation, see the SQL/MX Programming Manual for C and COBOL and the SQL/MX Programming Manual for Java. For information about using DISPLAY USE OF, see Checking Module Dependencies With DISPLAY USE OF on page 11-20 and the SQL/MX Reference Manual. 6. Query the system metadata to ensure the new table name is unique among existing table, view, SQL/MP alias and SPJ names in the schema. 7.
Adding, Altering, and Dropping SQL/MX Database Objects Adding Views 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 and the SQL/MX Programming Manual for Java.
Adding, Altering, and Dropping SQL/MX Database Objects Altering Objects in an SQL/MX Database Altering Objects in an SQL/MX Database Table 9-3 summarizes the database objects that you can alter and identifies the statements, commands, or utilities to use. Table 9-3.
Adding, Altering, and Dropping SQL/MX Database Objects • Authorization Requirements for Altering Database Objects SQL/MX Guide to Stored Procedures in Java Authorization Requirements for Altering Database Objects Table 9-4 describes the authorization requirement for each alter operation. Table 9-4. Authorization Requirements for Altering Database Objects Operation Authorization Requirements Altering Indexes To alter an index with the ALTER INDEX statement, you must own its schema or be the super ID.
Adding, Altering, and Dropping SQL/MX Database Objects Altering Indexes Altering 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 Altering Partitions 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. Determine the name of the partition you want to alter. 3.
Adding, Altering, and Dropping SQL/MX Database Objects Altering Stored Procedures in Java (SPJs) Steps for Altering 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 SQL/MP alias you want to alter. 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 Altering Tables Altering Table Columns You cannot alter table columns directly. You can alter them indirectly, however, by creating a new table with the different column sizes or data type definitions: 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 a name for the new table. 3.
Adding, Altering, and Dropping SQL/MX Database Objects Altering Tables Altering Table Constraints You cannot alter constraints, but you can change them by dropping an existing constraint or by adding a new constraint to the table. Constraints reside in definition only; therefore, they have no physical or security attributes to alter. Adding a Table Constraint See Adding Constraints on page 9-7. Dropping a Table Constraint See Dropping Constraints on page 9-30.
Adding, Altering, and Dropping SQL/MX Database Objects Altering Triggers 4. Enter the ALTER 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 and the SQL/MX Programming Manual for Java. Examples of Altering Table File Attributes These examples demonstrate altering the file attributes of a table: >> ALTER TABLE CAT.SCH.
Adding, Altering, and Dropping SQL/MX Database Objects Altering Views Steps for Altering a Trigger 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 trigger you want to alter. 3. Enter the ALTER TRIGGER statement. For more information and examples of altering triggers, see the SQL/MX Reference Manual.
Adding, Altering, and Dropping SQL/MX Database Objects Dropping Objects From an SQL/MX Database Dropping Objects From an SQL/MX Database Table 9-5 summarizes the objects that you can remove from the database and the statements, commands, and utilities to use. All these operations are discussed in this subsection except for dropping damaged SQL objects, which is described in Using GOAWAY to Delete Damaged Objects on page 12-35. Table 9-5.
Adding, Altering, and Dropping SQL/MX Database Objects Authorization Requirements for Dropping Database Objects Authorization Requirements for Dropping Database Objects Table 9-6 describes the authorization requirement for each drop operation. 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.
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 and the SQL/MX Programming Manual for Java 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 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 2. Determine the name of the table for which you want to drop the index. 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 and the SQL/MX Programming Manual for Java 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 The partition for a range-partitioned object must be empty. All partitions must be available when you use the MODIFY utility on an SQL/MX table or index. 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.
Adding, Altering, and Dropping SQL/MX Database Objects • • Dropping Schemas You must own the schema or be the super ID. You must have remote passwords for any nodes to which the catalog of the schema has been registered. 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 SQL/MP Aliases Dropping SQL/MP Aliases Use the DROP SQLMP ALIAS statement to drop an SQL/MP alias from a schema (remove its mapping from an SQL/MX ANSI name to a physical SQL/MP file name). The DROP SQLMP ALIAS statement does not cause the underlying SQL/MP object to be dropped. Similarly, dropping an underlying SQL/MP object does not cause any SQL/MP aliases to be dropped. Those aliases remain unchanged and orphaned.
Adding, Altering, and Dropping SQL/MX Database Objects Dropping Tables Dropping Tables To remove a table and its dependent objects from the database, use the DROP TABLE statement. Use the PURGEDATA utility to remove only the data from a table and its dependent objects and leave the objects intact. For more information, see the SQL/MX Reference Manual. Dropping Tables and Their Data To drop a table and its data, use the DROP TABLE statement.
Adding, Altering, and Dropping SQL/MX Database Objects Dropping Tables Steps for Dropping a Table and its Data 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 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 Triggers If PURGEDATA fails in response to a process, processor, or system error, it uses the RECOVER utility to recover the operation. If the PURGEDATA operation cannot be canceled, RECOVER returns an error. For more information about the PURGEDATA utility, see Using PURGEDATA to Delete Data From Tables on page 10-35 and the SQL/MX Reference Manual. Steps for Dropping a Table’s Data Only 1. Start an MXCI session.
Adding, Altering, and Dropping SQL/MX Database Objects Dropping Views 4. Enter the DROP TRIGGER 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 and the SQL/MX Programming Manual for Java. For more information and examples of dropping triggers, see the SQL/MX Reference Manual.
Adding, Altering, and Dropping SQL/MX Database Objects Dropping Views 4. Enter the DROP VIEW 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 and the SQL/MX Programming Manual for Java. For more information and examples of dropping views, see the SQL/MX Reference Manual.
10 Reorganizing SQL/MX Tables and Maintaining Data Sometimes you might need to restructure the data in an SQL/MX table by reloading or reorganizing the table. NonStop SQL/MX offers several tools to aid the restructuring task: • • • • • • • • Purge DDL files for dropped tables from the directory at /usr/tandem/sqlmx/ddl to provide more space for DROP operations. See Purging Dropped Tables From the DDL Directory on page 10-1. Choose a method for reorganizing the data.
Reorganizing SQL/MX Tables and Maintaining Data Choosing a Reorganization Method have to be recovered. The DDL directory contains timestamp information for each DDL file that identifies the time and date when a table was dropped. If you do not want NonStop SQL/MX to save the DDL when you drop a table, you can turn this feature off by setting the SAVE_DROPPED_TABLE_DDL control query to “OFF.” For more information, see the SQL/MX Reference Manual.
Reorganizing SQL/MX Tables and Maintaining Data • • • Determining the Status of a Reorganization With FUP STATUS During the operation, performance can be degraded if the data blocks have become physically disorganized. Disorganization can occur as a result of INSERT, UPDATE, or DELETE operations performed over a period of time. You can, however, control the amount of degradation by using the command’s RATE option. The higher the rate, the faster the reload occurs, but the more performance degrades.
Reorganizing SQL/MX Tables and Maintaining Data 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, DUP, and PURGEDATA 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.ZSDAWVHH.
Reorganizing SQL/MX Tables and Maintaining Data • • • • Consequences of a Failed Utility Operation import (for the fast load option only). See Using import to Load and Append Input Data Into SQL/MX Tables on page 10-22. POPULATE INDEX DUP. See Using DUP to Copy Tables Into Tables on page 10-34. PURGEDATA. See Using PURGEDATA to Delete Data From Tables on page 10-35. A DDL lock is obtained when any of these utilities starts. When the utility operation completes, the DDL lock is removed.
Reorganizing SQL/MX Tables and Maintaining Data Performing Recovery on Failed Utility Operations 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.schema_uid = (select schema_uid from nonstop_sqlmx_system.system_schema.schemata where schema_name = ‘SALES’ and cat_uid = (select cat_uid from nonstop_sqlmx_system.system_schema.catsys where cat_name = ‘SAMDCAT’ ) ) for read uncommmitted access order by l.
Reorganizing SQL/MX Tables and Maintaining Data Structure Changing Operations That Can Run With Active or Inactive DDL Locks Present and perform a FUP RELOAD operation if the MODIFY request completes but its ORSERV process does not complete and is not running. At the very least, a process with the same process ID stored in the metadata exists. For more information about recovering a failed MODIFY request, see Recovering a Failed MODIFY Request and Resetting Flags on page 10-13.
Reorganizing SQL/MX Tables and Maintaining Data • Using MODIFY to Manage Table and Index Partitions Creating or dropping views on table that has dependent indexes with active or inactive locks Operations are allowed to continue for views because creating and dropping views do not change the structure of the dependent tables or the dependent table’s indexes. This contrasts with NonStop SQL/MP, for which structure information is stored about views in the dependent table’s label.
Reorganizing SQL/MX Tables and Maintaining Data • • • MODIFY and EMS Messages Adding, splitting, merging, moving, and dropping partitions for range-partitioned tables and indexes. See Using MODIFY to Manage Range-Partitioned Tables and Indexes on page 10-14. Adding, moving, and dropping partitions for hash-partitioned tables and indexes. See Using MODIFY to Manage Hash-Partitioned Tables and Indexes on page 10-18. Managing system-clustered tables and indexes.
Reorganizing SQL/MX Tables and Maintaining Data MODIFY and Table Reloading Specifying the Number of Rows Per Transaction You can specify the number of rows to be copied in a transaction with the CONTROL QUERY DEFAULT statement or by inserting an entry into the SYSTEM_DEFAULTS table. For offline partition operations, use the PM_OFFLINE_TRANSACTION_GRANULARITY attribute. For online partition operations, use the PM_ONLINE_TRANSACTION_GRANULARITY attribute.
Reorganizing SQL/MX Tables and Maintaining Data • • • • MODIFY and Table Reloading You specify an offline ADD request (that is, a prefix of a suffix split operation), and the specified key range contains data. You specify an online ADD request using the WITH SHARED ACCESS clause. You specify a MOVE request for a move-partition-boundary operation, and the specified key range contains data but does not include the entire partition.
Reorganizing SQL/MX Tables and Maintaining Data Recovering a Failed MODIFY Request and Resetting Flags Recovering a Failed MODIFY Request and Resetting Flags The UNRECLAIMEDSPACE (F) flag or the INCOMPLETE SQLDDL OPERATION (D) flag can be left set when a MODIFY operation fails to complete successfully.
Using MODIFY to Manage Range-Partitioned Tables and Indexes Reorganizing SQL/MX Tables and Maintaining Data where cat_name = 'SAMDBCAT' ) ) for read uncommitted access order by l.object_uid; LOCK_NAME ----------------------CUSTOMER_77179541_2217 UTIL ----PI STATUS ----------4 PROCESS_ID ---------------\FIGARO.$:3:321:2235264 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 these partition management operations for range partitions: • • • • • • • Adding a new empty partition. You can add range partitions online. You can add one range partition at a time to a hash-partitioned table, but you must do this offline. Dropping an existing empty partition. Moving an existing partition to a new location.
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 Example of Dropping an Existing Hash Partition and Rebalancing Data Orders suddenly decline, and you decide to free up some of the disk resources used by the index. Use this MODIFY command to drop the fourth partition and redistribute its data to the remaining partitions: MODIFY INDEX cat2.sch2.
Reorganizing SQL/MX Tables and Maintaining Data Using import to Load and Append Input Data Into SQL/MX Tables Example of Moving an Entire System-Clustered Partition to a New Location You suddenly need to use $DATA09 for other purposes. You decide to move the system-clustered table ORDERSSC to a different volume. Use this MODIFY TABLE command to move the table to the volume $DATA03: MODIFY TABLE cat4.sch4.
Reorganizing SQL/MX Tables and Maintaining Data Summary of import Options Summary of import Options Table 10-1 describes the most important import command options. Table 10-1. import Command Options import Option Description -D Disables all triggers before the data is inserted, which prevents trigger actions from occurring. -F and -C Specifies the first row and the number of rows to import from the input file. The first row is designated 0 (zero).
Reorganizing SQL/MX Tables and Maintaining Data Other import Features Other import Features • import automatically turns auditing off for all partitions of an empty table without indexes. Auditing is turned back on after the import operation completes or if it fails for any reason. This enables import to take advantage of fast loading techniques and to avoid TMF transaction issues. Turning off auditing for a table invalidates online dumps.
Reorganizing SQL/MX Tables and Maintaining Data Recommended Practices for Improving import Performance Running import on Empty Tables You can import data into a table most quickly by using the fast load technique, which import automatically uses when the table meets all these conditions and the transaction size is not specified: • • • • It is empty. It has no indexes. It has no droppable primary key, unique key, or foreign key constraints. It has no enabled triggers.
Reorganizing SQL/MX Tables and Maintaining Data • • Recommended Practices for Improving import Performance The amount of data being imported. When you import a small amount of data (for example, less than 500,000 rows), you can either perform the import operation in parallel or in a single stream. When you import large amounts of data, you need to examine the data being imported and the partitions into which it will be inserted.
Reorganizing SQL/MX Tables and Maintaining Data Recommended Practices for Improving import Performance Importing Data Into Multiple Hash Partitions Because imported data cannot be presorted for hash-partitioned tables, using a single import instance for a hash-partitioned table is generally more efficient than running multiple instances in parallel. If you do run parallel instances, keep the transaction size to a minimum to avoid lock escalation to the table level.
Reorganizing SQL/MX Tables and Maintaining Data Using import to Load Partitions 6. Run POPULATE INDEX to load the data into the indexes. For information about CREATE INDEX and the NO POPULATE option, see the SQL/MX Reference Manual. Managing Triggers to Improve import Performance Triggers degrade import performance by causing triggered actions to be performed during the operation. Before you perform an import operation on a table with triggers, use the import -D option to disable all triggers.
Reorganizing SQL/MX Tables and Maintaining Data • Examples of Using import to Load an SQL/MX Table 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 single input file. For each import command, specify the number of input rows (records), the number of the first record to import, and the transaction size. For example, suppose that you partition the EMPLOYEE table into three partitions.
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using import to Load an SQL/MX Table Example 1: Importing Data From a Delimited Input Data File In this example, the input file, COINPUT, contains records like this: 1,"Test 2,"Test 3,"Test 4,"Test 5,"Test 6,"Test 7,"Test 8,"Test String String String String String String String String 1","111-222-3333","222-333-4444" 2","111-222-3333","222-333-4444" 3","111-222-3333","222-333-4444" 4","111-222-3333","222-333-4444" 5","111-222-3333","222-333-444
Reorganizing SQL/MX Tables and Maintaining Data Using import to Append Data to Tables or Partitions 3,tandem,408-285-5000,408-285-2227 4,diyatech,510-111-2222,510-222-3333 Suppose that you want to import data into the COMPANY table from the fixed-width and delimited COINPUT input file by using a format file.
Reorganizing SQL/MX Tables and Maintaining Data Using import to Append Data to Tables or Partitions Use the import command to append data to multiple partitions of a table. The DataLoader/MX product can help you streamline this task. DataLoader/MX is a nonprivileged batch program that provides a library of command routines for loading and maintaining SQL/MX tables. DataLoader/MX can be customized to perform data-format transformation while using the import utility for append operations.
Reorganizing SQL/MX Tables and Maintaining Data • • Using import to Append Data to Tables or Partitions Run one instance of the import command to load data into a partitioned table by using a single input file. Use DataLoader/MX to read input data. Start each DataLoader/MX process as a named process before entering the import command. Use the process name as the input file for the import command.
Reorganizing SQL/MX Tables and Maintaining Data Using DUP to Copy Tables Into Tables Using DUP to Copy Tables Into Tables DUP is a syntax-based utility you can execute from MXCI to copy a source table—and optionally its index and constraints—into an existing target table. For more information about the DUP utility, see the SQL/MX Reference Manual. Guidelines for Using DUP • • • • • You must have select privileges for the source table and must own the schema where the target table will reside.
Reorganizing SQL/MX Tables and Maintaining Data Examples of Using DUP to Copy Tables DDL lock mechanism. You can examine metadata to get the latest information. The DUP operation has the option to log these progress reports to an OSS text file. See Section 8, Querying SQL/MX Metadata for instructions on how to access information from the DDL_LOCKS table. For more information about DDL lock considerations for the DUP utility, see the SQL/MX Reference Manual.
Reorganizing SQL/MX Tables and Maintaining Data • • • • • Examples of Using PURGEDATA to Delete Table Data PURGEDATA records operation progress steps in the DDL_LOCKS metadata table. Users can query this table to determine the PURGEDATA operation’s progress. For more information about PURGEDATA operation steps and their progress states, see the SQL/MX Reference Manual. For more information about querying for DDL_LOCK information, see Section 8, Querying SQL/MX Metadata.
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. This section addresses these application management tasks: • • • • • • • Migrating Applications to Release 2.
Managing Database Applications • Migrating SQL/MP Applications to Release 2.x Migrating Release 1.8 applications to run successfully in a Release 2.x environment In the first scenario, only C, C++, or COBOL applications would be migrated, not SQLJ applications. SQLJ applications are not supported by an SQL/MP engine. For information about migrating an entire database to the Release 2.1 or Release 2.0 environment, see Section 14, Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0.
Managing Database Applications Migrating SQL/MX Applications From Release 2.0 to Release 2.1 For information about how to recustomize and SQL compile SQLJ applications, see the SQL/MX Programming Manual for Java. Note. If you recompile a Release 1.8 application in the Release 2.x environment, that application cannot query SQL/MP tables on Release 1.8 nodes. The recompiled application can query SQL/MP tables on Release 2.x nodes only. You must upgrade remote Release 1.8 nodes to Release 2.1 or Release 2.
Managing Database Applications Moving a Program Without Compiled Modules The similarity check determines if the query execution plan of a statement is still operable. If the similarity check fails (or is disabled), the SQL/MX executor, by default, invokes the SQL/MX compiler to automatically recompile the query execution plan. The automatically compiled plan is not saved for subsequent executions of the same program or for multiple concurrent executions of the same program.
Managing Database Applications • • Moving a Program Without Compiled Modules The SQL statements in the program refer to database objects on the development system that differ in table structure (for example, indexes and partitions) and distribution of data from those on the production system. Differences in the data contained in the same tables on the development and production systems cause differences in the statistics that the SQL/MX compiler uses to optimize the plan.
Managing Database Applications Moving a Program Without Compiled Modules subvolume on the command line during SQL compilation of the module definition: mxCompileUserModule -d MP_VOLUME=\$vol MP_SUBVOLUME=subvol myprog.exe mxcmp -d MP_VOLUME=\$vol MP_SUBVOLUME=subvol sqlprog.m • • For hard-coded logical names of SQL/MX objects, make sure that catalog, schema, and object names on the development system are the same as those on the production system.
Managing Database Applications • • • Moving a Program Without Compiled Modules The development node catalogs and schemas are not visible from the production node and vice versa. To ensure this, do not use the REGISTER CATALOG statement from either node to create an empty catalog reference on the other node. For more information, see Using REGISTER CATALOG to Access Objects on Remote Nodes on page 11-9. The program is a single file named myprog.exe for a C, C++, or COBOL program and MyProg.
Managing Database Applications ° Distributing Programs Across Nodes 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.exe Alternately, run the mxcmp command to SQL compile a module definition file: mxcmp sqlprog.
Managing Database Applications • • • Moving Applications to a Remote Node If you compile an application from a Release 2.0 node that queries a Release 2.1 node, the query plan version will be the same as the earliest node version in the distributed database (1200).
Managing Database Applications Maintaining Local Autonomy for Programmatic Queries catalog that contains the objects must be visible from the local node before you can process and run the program. Use the REGISTER CATALOG statement to create an empty catalog reference on the local node that enables local programs to query the catalog’s objects on a remote node or nodes. Use the UNREGISTER CATALOG statement to remove the empty catalog reference from the local node.
Managing Database Applications • Ensuring Proper Name Resolution In a distributed SQL/MX database environment, statically compiled queries in SQL programs can execute successfully when: ° Either the node that stores a given query object’s metadata is available or the query does not use late name resolution of ANSI names or automatic recompilation. ° The data that a given program query attempts to access is stored on available disks on the local node or on available remote nodes.
Managing Database Applications Setting SQL/MP Aliases SET DEFMODE ON is set, and then use the DEFINE commands appropriate for that environment. In the OSS environment, use the OSS add_define command to add class MAP DEFINEs for SQL/MP tables or views: add_define =define-name class=map file=\\node.\$volume.subvol.table Note. Precede the backslash (\) in the node name and the dollar sign ($) in the volume name with the OSS shell escape character (\).
Managing Database Applications Assigning Permissions for Running Database Applications Assigning Permissions for Running Database Applications Managing Permissions for Files in OSS Space For SQL applications and user modules stored in OSS space, you control access to the files with the OSS commands that manage OSS files and directories, including: • • • Displaying and setting file and directory permissions Setting the user mask Changing owners and groups You use permission codes to specify the types of
Managing Database Applications Maintaining Query Execution Plan Validity For more information, see the Guardian User’s Guide and the Safeguard User’s Guide. Maintaining Query Execution Plan Validity A query execution plan defines the semantics and execution characteristics for a single compiled SQL statement. Statically compiled applications typically have many query execution plans. The query execution plans of an application are stored in an SQL/MX user module.
Managing Database Applications Recompiling a Module If a plan requires automatic recompilation, you should explicitly recompile the module. See Recompiling a Module on page 11-15. Recompiling a Module Periodically, depending on changes to the database or the occurrence of automatic recompilation, you might need to recompile the modules of an application. Explicitly recompiling modules generates optimal query execution plans and prevents the performance cost of automatic recompilation.
Managing Database Applications • Producing Locally Placed Modules or Globally Placed Modules Managing Module Files and Their Applications During Fallback From Release 2.1 or Release 2.0 on page 11-25 In previous releases of NonStop SQL/MX, all user modules were globally placed modules located in the /usr/tandem/sqlmx/USERMODULES directory. For Release 2.0 and Release 2.1, applications written in C, C++, or COBOL can use either globally placed modules or locally placed modules.
Managing Database Applications Securing User Modules mxcmp -g moduleGlobal | -g moduleLocal To maintain compatibility with C, C++, and COBOL applications created in earlier releases, the Release 2.1 or Release 2.0 SQL compiler produces a globally placed module unless instructed to produce a locally placed module.
Managing Database Applications Securing User Modules If the USERMODULES directory already exists when the InstallSqlmx script is run (for example, after Release 2.1 or Release 2.0 has already been installed), the script: • • • Does not attempt to re-create the USERMODULES directory. Does not attempt to change ownership of the USERMODULES directory. Performs this OSS command: chmod a+rwxt /usr/tandem/sqlmx/USERMODULES After InstallSqlmx runs, you might want to perform some or all of these tasks: 1.
Securing User Modules Managing Database Applications Securing Locally Placed Modules You secure locally placed modules from the local directory in which they reside. The objective should be to assign the responsibility and accountability for safeguarding the integrity of each locally placed module to its own creator and owner. Proper security of the local directory requires that: • • • The owner of the local directory be a trusted user. The sticky bit of the local directory be set.
Managing Database Applications Checking Module Dependencies With DISPLAY USE OF 7. Alternatively, the SUPER.SUPER user can make sure that all modules in the local directory can be written to only by their respective owners by entering: chmod 0644 /usr/local-directory/* 8.
Managing Database Applications Module: Object: Object: Object: Object: CAT.SCH.CURSOMEM \NODE1.DATA10.ORDERS.T01 CAT.SCH.T02 CAT.SCH.T03 CAT.SCH.T04 Module: Object: Object: Object: Object: CAT.SCH.TESTMEM CAT.SCH.T15 CAT.SCH.T16 CAT.SCH.T17 CAT.SCH.
Managing Database Applications Removing Modules 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. Removing Modules You should prepare carefully before you remove module files. At best, removing a module file can free up disk space.
Managing Database Applications Converting Globally Placed Modules to Locally Placed Modules 8809. For more information, see the SQL/MX Programming Manual for C and COBOL and the SQL/MX Messages Manual. Deleted Modules of SQLJ Programs If you try to run an SQLJ program that refers to a deleted module, the SQLJ run time either returns an SQL exception or dynamically executes the SQL statements in the program, depending on the setting of the -missingSQLObject command-line option during customization.
Managing Database Applications Converting Globally Placed Modules to Locally Placed Modules 4. When you deploy the application in the production environment, copy any compilation scripts that replace the previous compilation scripts. 5. If you recompile the modules in the production environment, change the compilation script so that it is consistent with the changes made in Step 2. 6. Remove the shared global copies of the now unused modules from the /usr/tandem/sqlmx/USERMODULES directory. 7.
Managing Database Applications Managing Module Files and Their Applications During Fallback From Release 2.1 or Release 2.0 5. Depending on the application environment, use the appropriate management commands to drain the currently executing applications, and restart the applications using the copy in the local environment. Caution. If a module is used by more than one application and at least one of those applications continues to use globally placed modules, do not remove the global copy of the module.
Managing Database Applications Managing Module Files and Their Applications During Fallback From Release 2.1 or Release 2.0 For instructions on falling back, see Appendix A, Falling Back From NonStop SQL/MX Release 2.1 or Release 2.0. Falling Back Release 2.1 or Release 2.0 Applications and Their Globally Placed Modules From Release 2.1 or Release 2.0 Applications that were compiled from Release 2.1 or Release 2.0 and their globally placed modules need to fall back as follows: 1.
Managing Database Applications Managing Module Files and Their Applications During Fallback From Release 2.1 or Release 2.0 If you are falling back to an earlier version of Release 1.8, you need to manage your locally placed modules and their applications accordingly: • • Falling Back Release 1.8 Applications and Their Locally Placed Modules From Release 2.1 or Release 2.0 on page 11-27 Falling Back Release 2.x Applications and Their Locally Placed Modules From Release 2.1 or Release 2.
Managing Database Applications Backing Up and Restoring Programs SQL compiling the application as specified in the SQL/MX Programming Manual for C and COBOL. 4. If a module definition file was not found for a Release 2.1 or Release 2.0 application in Step 1, preprocess the application’s source file from Release 1.8 to generate a module definition file. 5. From Release 1.8, host-language compile the Release 1.8 annotated source file created in Step 4.
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 If necessary, you can recover files to new locations by specifying different systems, volumes, subvolumes, or file IDs (for example, to recover from a hardware failure). Caution. If you are using SQL/MP aliases, you might need to update your alias definitions after you recover a SQL/MP file to a new location. For more information, see the SQL/MX Reference Manual.
Performing Recovery Operations Recovering Files to New Locations If you attempt to recover without creating the target object, the restore operation fails with Error 9037 as shown in this example, and the object is not recovered: NonStop TMF on \PLUTO *0202* RECOVER FILES [58] OnLineRestore Process #1 OnlineDumpMgmt: *ERROR* TMF-9037: $DATA17.ZSDT5356.J24Z5J00: File System error 11 occurred attempting to retrieve the SQL file label from disk.
Performing Recovery Operations Recovering Metadata perform the recovery to obtain a copy of your source objects. Transactions can be active against the source objects at the time of the recovery. Caution. If you use the MAP NAMES option of the RECOVER FILES command to recover files to a new location, you must immediately make new online dumps of the target data files recovered.
Performing Recovery Operations Recovering Database Objects Recovering Database Objects Note. Even though TMF supports recovery to different volumes and different systems, SQL/MX database objects cannot be recovered to different disk volumes or to systems that have different node names and numbers. To completely recover systems that contain SQL/MX database objects, the backup and recovery system must have the same disk volume name, node name, and node number as the primary system.
Performing Recovery Operations Recovering SPJs For information about the VERIFY and FIXUP commands, see the SQL/MX Reference Manual. Recovering SPJs To recover an SPJ, reexecute the CREATE PROCEDURE statement. No special action is done to recover SPJs; there is no provision for preserving DDL from a DROP PROCEDURE operation. Security on the re-created SPJ must be explicitly readded.
Performing Recovery Operations Recovering Tables Recovering Table Privileges NonStop SQL/MX does not save the DDL for GRANT and REVOKE statements, so if you accidentally drop a table that includes security information created by these statements, you must restore the table’s security attributes in the table’s metadata before you can recover the table data, labels, and resource forks from a TMF online dump. For more information, see Example: Recovering Table Privileges on page 12-11.
Performing Recovery Operations Recovering Tables For most cases, the recommended approach for recovering a table and its indexes involves these tasks: • • Determine what dependent objects (views, indexes, and other tables) might have been dropped along with the table. Use MXCI to re-create the table, its indexes, and any privileges that you have granted or revoked. If the SAVE_DROPPED_TABLE_DDL attribute has remained set ON, use the saved DDL information for re-creating the objects.
Performing Recovery Operations • Recovering Tables SQL compile any SQL/MX modules that access this table. Example: Recovering Table Privileges Suppose that this DDL was saved when a table was dropped accidentally: CREATE TABLE CAT.SCH.T039UC3 ( A INT NO DEFAULT -- NOT NULL NOT DROPPABLE , CONSTRAINT CAT.SCH.T039UC3_100345126_0022 CHECK (CAT.SCH.T039UC3.A IS NOT NULL) NOT DROPPABLE ) LOCATION \NSK.$DATA4.ZSDADHFW.
Recovering Tables Performing Recovery Operations 4. Use a licensed copy of MXCI to execute the DELETE and INSERT statements. For example: delete from CAT.DEFINITION_SCHEMA_VERSION_1200.TBL_PRIVILEGES where TABLE_UID =(table-uid-subquery); delete from CAT.DEFINITION_SCHEMA_VERSION_1200.COL_PRIVILEGES where TABLE_UID =(table-uid-subquery); insert values (-2,'S (-2,'S (-2,'S (-2,'S (-2,'S into CAT.DEFINITION_SCHEMA_VERSION_1200.
Performing Recovery Operations Recovering Tables Tables With Partitions Recovery of partitioned tables requires special attention. After you have re-created the partitions and recovered them with TMF, the timestamps in the catalogs may be wrong for every partition. You can use a single mxtool FIXUP command to correct the timestamps for all partitions. Tables With Triggers When a dropped table with triggers is recovered, the trigger temporary tables are re-created from scratch.
Performing Recovery Operations Recovering Tables ( DEPTNUM ASC ) LOCATION \KRYPTON.$DATA10.ZSDT5356.NPFH8J00 NAME KRYPTON_DATA10_ZSDT5356_NPFH8J00 ; --- SQL operation complete. At some point, you discover that someone 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.
Recovering Tables Performing Recovery Operations a. Retrieve the saved DDL: /user/tandem/sqlmx/ddl: cp HRDATA.PERSNL.EMPLOYEE20031114-160629.ddl/G/DATA01/SUBVOLS/ddlfile 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.
Performing Recovery Operations Recovering Tables With the SAVE_DROPPED_TABLE_DDL attribute set ON, the script for re-creating the table is saved in the OSS directory /usr/tandem/sqlmx/ddl in the file 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.
Performing Recovery Operations Recovering Tables Exit the current MXCI session, which will close these newly created files. Note. If you use the SHOWDDL output to re-create the table and its indexes, execute all CREATE INDEX and ALTER TABLE statements in the exact order indicated. Doing so ensures that all indexes are re-created properly and can be recovered. 5. Use the GRANT statement to re-create access privileges for the table and its indexes. Note.
Performing Recovery Operations Recovering Tables Because the objects now exist, you can also enter the ANSI name of the table directly into the command: $DATA01 SUBVOL 16>$system.zmxtools.mxgnames HRDATA.PERSNL.EMPLOYEE -tmf Note. The ALTER DUMPS command does not allow partial wildcarding of the input file names. Therefore, you will need to fully expand the file names from the MXGNAMES output before using them in the ALTER DUMPS command. TMF 16>ALTER DUMPS ($DATA10.ZDST5356.JN9V5J00, & >>> $DATA10.
Performing Recovery Operations Recovering Tables TMF does not update or insert entries in the SQL catalog during a RECOVER FILES operation for an SQL/MX object. As it performs the recovery, TMF will attempt to automatically synchronize the objects’ create and redefinition timestamps between the catalog and the file label on disk.
Performing Recovery Operations Recovering Tables Verifying resource fork for partition: \KRYPTON.$DATA10.ZSDT5356.JN9V5J00 Verifying Partition Map: no partition map exists Object verification complete for: HRDATA.PERSNL.EMPLOYEE If no objects are identified as inconsistent, the recovery is complete and you should go to Step 13. Otherwise, proceed to Step 10. 10.
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. 13. Update the statistics for the recovered table.
Performing Recovery Operations Restoring Catalogs Restoring Catalogs When you restore a catalog, all its dependent objects (for example, schemas, tables, and indexes) are also restored.
Performing Recovery Operations Restoring Indexes You can restore one or more table partitions, either implicitly, by restoring associated tables, or explicitly, by using the PARTITION restore object: BR> RESTORE $tape-drive, MX (TPART cat1.sch1.table1 PARTITION (part1)), PARTONLY ON; BR> RESTORE $tape-drive, MX (TPART cat1.sch1.table1 PARTITION (part1, part2)) BR+>, PARTONLY ON; Caution. Exercise care when using the PARTONLY object in Backup and Restore 2 operations for partitioned tables.
Performing Recovery Operations Repairing Damaged SQL/MX Metadata and Objects Repairing Damaged SQL/MX Metadata and Objects Each SQL/MX file consists of two physical Guardian files: the data fork and the resource fork. Normally, when a data fork is dropped, DP2 automatically drops the corresponding resource fork. In some cases, however, either a resource fork or a data fork can become an orphan, requiring your intervention and repair.
Performing Recovery Operations Strategies for Repairing Damaged Metadata and Objects For information about using FIXUP, see Using FIXUP to Correct Problem Data and Objects on page 12-31. GOAWAY Use the mxtool GOAWAY command to remove SQL/MX format file labels for both the resource and data forks associated with a Guardian file. Use GOAWAY to remove objects that are broken or cannot be dropped by normal DDL operations. GOAWAY does not remove corresponding metadata entries.
Performing Recovery Operations Identifying Problem Data and Objects Identifying Problem Data and Objects NonStop SQL/MX keeps information about object structures in metadata, resource forks, and DP2 labels. DP2 labels consist of file structure information and security settings. Resource forks contain specific SQL/MX information that includes, among other things, the ANSI name, partition maps, row and key information, and system metadata location.
Performing Recovery Operations Identifying Problem Data and Objects Table 12-1. Label Data Associated With SQL/MX Objects (page 2 of 2) Label Field Contents Protection FIle security setting, specified as *sql. NonStop SQL/MX supports ANSI GRANT and REVOKE security and does not use Guardian security of the form RWEP. The Guardian protection for all SQL/MX format objects is set to “AAAA.” Do not use this field to determine the security attributes for the object.
Performing Recovery Operations Identifying Problem Data and Objects Table 12-2. Run-time metadata Stored in the Resource Fork Run-time metadata Contents ANSI name A three-part name of the form catalog.schema.object, where each part can be up to 128 characters long. ANSI namespace Indicates the space the ANSI name belongs to. In NonStop SQL/MX, tables and indexes have separate namespaces; indexes and tables in the same schema can have the same ANSI names.
Performing Recovery Operations Identifying Problem Data and Objects Using SHOWLABEL to Retrieve Run-Time Metadata You can also use the SHOWLABEL command to retrieve run-time metadata from both the labels and the resource fork of a Guardian file. For command syntax and example outputs, see the SQL/MX Reference Manual.
Performing Recovery Operations Identifying Problem Data and Objects This VERIFY feature compares information saved in the DP2 file label with similar information stored in metadata and returns inconsistencies.
Performing Recovery Operations Using FIXUP to Correct Problem Data and Objects you can use the MXCI SHOWLABEL command to get more details for a specific Guardian file. See the SQL/MX Reference Manual for command syntax and example outputs. Guardian files have a set of DP2 labels attached to them that contain basic information, such as file type, extent sizes, security, and timestamps. A separate entity, a resource fork, is attached to an SQL/MX file.
Performing Recovery Operations Using FIXUP to Correct Problem Data and Objects Recovering Consistent Files When a disk volume or network node fails, files that are open at the time of failure are left in a questionable state. In many cases, files are inconsistent because they were actively involved in interrupted database transactions. In other cases, files marked as questionable are actually consistent.
Performing Recovery Operations Using FIXUP to Correct Problem Data and Objects been turned on, you could turn on the audit bit and potentially use the new table. Of course, you would have to verify that the data was loaded correctly. However, checking the validity of the table can be less difficult than dropping and performing the DUP operation a second time. Changing the Broken Bit Like the broken flag, the broken bit is set by DP2 when it detects that something is wrong with the partition.
Performing Recovery Operations Examples of Using FIXUP partition of a table). If the restored partition has a different redefinition timestamp than the rest of the object, use FIXUP to set the redefinition timestamp to the correct value. Examples of Using FIXUP Suppose that table1 is located in the catalog cat and the schema sch and has three partitions. Two of the partitions are located on the local node, \local.
Performing Recovery Operations Using GOAWAY to Delete Damaged Objects Example 1: Repairing a Broken Partition Partition \LOCAL.$DATA02.ZSD1123U.SUEIFO00 is marked broken. To fix the problem, turn off the audit bit, use TANDUMP to fix the problem, reset the broken bit, and turn the audit bit back on: mxtool FIXUP LABEL \LOCAL.$DATA02.ZSD1123U.SUEIFO00 -a=off Determine the problem and use TANDUMP to fix it. mxtool FIXUP LABEL \LOCAL.$DATA02.ZSD1123U.SUEIFO00 -rb mxtool FIXUP LABEL \LOCAL.$DATA02.ZSD1123U.
Performing Recovery Operations • • • • ABORT DISK ° ° ° , ALTNAME , LABEL , VOLNAME INITIALIZE DISK RENAME DISK STOP DISK START DISK ABORT DISK The ABORT DISK command puts a disk into the STOPPED state, substate HARDDOWN, but leaves the disk process running. Do not use the stopped drive to store production SQL/MX database files. Use the drive for a test database or for temporary space for sort files.
Performing Recovery Operations ALTER DISK, LABEL 1. Identify all the SQL/MX objects to be renamed and all dependencies. Produce hard-copy reports containing this information. 2. Create an EDIT file containing CREATE CATALOG and ALTER TABLE statements to re-create the catalogs and reset the security of the catalog tables. 3. Back up the volume by using a file-mode BACKUP command. 4. Use ALTER DISK, ALTNAME to rename the disk.
Performing Recovery Operations INITIALIZE DISK 2. Check that dependent objects residing on other volumes have also been recovered and re-create objects as necessary. 3. Verify the database by using the mxtool VERIFY utility. For command syntax and operating guidelines, see the SQL/MX Reference Manual. 4. Make new TMF online dumps of SQL/MX objects that reside on the volume. If the entire object was re-created, take dumps of the entire object even if one or more partitions reside on different volumes.
Performing Recovery Operations START DISK and STOP DISK state. The other mirrored set continues the active database, but the inactive mirrors also contain a set of consistent SQL/MX objects. You can also use STOP DISK to bring down the active database and START DISK to bring up the saved database in a database swapping technique. This technique might be useful for testing scenarios. As long as you bring each set of mirrors down and then up together, each copy of the database continues to be consistent.
Performing Recovery Operations START DISK and STOP DISK HP NonStop SQL/MX Installation and Management Guide—523723-004 12 -40
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 processors 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 NonStop SQL/MX Release 2.1 or Release 2.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 a Release 2.1 or Release 2.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 Release 2.1 or Release 2.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 Network Security Managing an SQL/MX Distributed Database • The CAT_REFERENCES table on \N2 and \N3 contains two rows for the catalog CAT1, one for \N1 and another for the local node.
Managing an SQL/MX Distributed Database Maintaining Local Autonomy in a Network 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.
Managing an SQL/MX Distributed Database • Maintaining Local Autonomy in a Network 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. For example, the table ORDERS is range-partitioned by the column ORDER NUMBER.
Maintaining Local Autonomy in a Network Managing an SQL/MX Distributed Database configurations with respect to their ability to minimize the consequences of node loss on surviving network nodes. Network Outage Failure Scenario Suppose that you have an SQL/MX database that is distributed across three nodes (\A, \B, and \C). The database includes two user catalogs, CAT_1 and CAT_2, which were created on node \B and have been registered for nodes \A and \C.
Maintaining Local Autonomy in a Network Managing an SQL/MX Distributed Database Table 13-3. Consequences of Network Node Loss, Original Configuration Node CAT_1 and CAT_2 User Data Present? CAT_1 and CAT_2 Metadata Present? CAT_1 and CAT_2 Applications Present? \B Yes Yes Yes From nodes \A and \C, you cannot perform DDL or utility operations on CAT_1 or CAT_2. You cannot perform dynamically-compiled or automatically-recompiled DML operations on any CAT_1 or CAT_2 objects.
Maintaining Local Autonomy in a Network Managing an SQL/MX Distributed Database revised configuration, node \B becomes more of a single point of failure for the distributed database. Table 13-4. Consequences of Network Node Loss, Reconfiguration 1 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 No The same consequences occur as were identified in the node \A row in Table 13-3 on page 13-14.
Maintaining Local Autonomy in a Network Managing an SQL/MX Distributed Database Table 13-5. Consequences of Network Node Loss, Reconfiguration 2 Node CAT_1 and CAT_2 User Data Present? CAT_1 and CAT_2 Metadata Present? CAT_1 and CAT_2 Applications Present? \A Yes Yes for CAT_1 Yes for those parts that mainly access CAT_1 From nodes \B and \C, you cannot perform DDL or utility operations on any of the CAT_2 catalog’s objects, which have partitions on node \A.
Maintaining Local Autonomy in a Network Managing an SQL/MX Distributed Database and queries that access object data in CAT_2. Likewise, an outage of node \B does not prevent metadata access by applications and queries that access object data in CAT_1. If CAT_1 and CAT_2 are tightly interrelated and if most application programs and queries access objects in both catalogs, the configuration described in Table 13-5 on page 13-17 would instead look like the configuration shown in Table 13-6. Table 13-6.
Managing an SQL/MX Distributed Database Creating a Distributed SQL/MX Database nodes for all operations except the execution of statically compiled queries. This configuration is not recommended.
Managing an SQL/MX Distributed Database Distributing the SQL/MX Database Objects for objects to specified users. These access privileges extend to remote nodes provided the users have remote network access to those nodes. You can use the LOCATION clause in a DDL statement to specify different nodes or disks on which to create an object. Example—Creating Local Views on Local and Remote Tables 1. From the local node \A, create the catalog CAT1: CREATE CATALOG CAT1; 2.
Managing an SQL/MX Distributed Database Altering Distributed Objects 1. Make sure that the catalog for the table and index is registered on the remote node \B: REGISTER CATALOG CAT1 ON \B.$DATA02; 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.
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 Supporting Replicated Data Through Indexes Supporting Replicated Data Through Indexes Use indexes to replicate data. NonStop SQL/MX also supports the use of triggers to replicate data from one table to another and the development of publish/subscribe applications that replicate data.
Managing an SQL/MX Distributed Database Changing Network Environments change, and the needs of the node with respect to the database or application can change. Many of these changes do not affect the SQL/MX database or environment and need not concern you if you are a system manager. Certain changes, however, can cause problems or affect the SQL/MX environment and should be anticipated. Consider these changes on a case-by-case basis: • • • • • • A new node is added to the network.
Managing an SQL/MX Distributed Database Managing Mixed Releases of NonStop SQL/MX recovery. Another important reason for keeping TRANSACTIONS OFF is to provide the system administrator time for additional recovery operations, as needed, before the applications can start transaction activity. For additional information, see the TMF Planning and Configuration Guide. • A number of situations can cause severe problems with the consistency of an SQL/MX database. Various techniques can resolve these problems.
Managing an SQL/MX Distributed Database Managing Mixed Releases of NonStop SQL/MX HP NonStop SQL/MX Installation and Management Guide—523723-004 13 -26
14 Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 This section describes how to migrate SQL/MP data from an SQL/MP or NonStop SQL/MX Release 1.8 environment to Release 2.1 or Release 2.0. The procedures in this section include steps to increase the success of fallback, if falling back is necessary. Appendix A, Falling Back From NonStop SQL/MX Release 2.1 or Release 2.0 contains fallback information.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 • • • • • • Planning for Migration SQL/MP and SQL/MX software can reside on the same node. However, multiple releases of NonStop SQL/MX cannot reside on the same node. Only one release of SQL/MX software can reside on a node. For example, if Release 2.1 is installed on a node, Release 2.0 or Release 1.8 cannot be installed at the same time. Migration from Release 1.8 to Release 2.1 or Release 2.0 is largely a manual process.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 Planning to Migrate Applications Planning to Migrate Applications Planning activities for migrating applications depend on your environment and the types of applications you are currently using. Before attempting to migrate data, review these references. For more information about See Migrating SQL/MP applications or Release 1.8 applications to Release 2.1 or Release 2.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 Planning for Fallback This approach effectively separates the installation phase from the feature development and utilization phase. If fallback becomes necessary, it is most likely to occur during the installation phase, before you have started using new features. Planning for Fallback It might be necessary to install fallback SPRs on the Release 1.8 system before migrating to G06.23 (or a later RVU) and Release 2.1 or Release 2.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 Using the migrate Utility to Migrate SQL/MP Metadata From Release 1.8 2. Use the procedures in Section 3, Installing NonStop SQL/MX to install Release 2.1 or Release 2.0 on nodes where the SQL/MP database resides. 3. Create the target database. For more information, see Section 7, Creating an SQL/MX Database. Note that you can use the SHOWDDL command to display the SQL/MX syntax of an SQL/MP table.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 System Requirements for Migrating SQL/MP Metadata and PARAMS tables, and ODBC metadata from the ZOAS2DS, ZODS, ZOENV, ZONAME2ID, and ZORES tables. It copies this metadata to the Release 2.1 or Release 2.0 metadata tables identified in Table 14-1 on page 14-5. The catalog-name (cat) prefix provided with the DEFINITION_SCHEMA tables identifies the catalog in which the DEFINITION_SCHEMA table resides. Note.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 Considerations for Falling Back From Release 2.1 or Release 2.0 4. Use the SCRIPT option of the migrate EXECUTE command to move entries from the specified SQL/MP metadata to the SQL/MX metadata by executing the edited script. Considerations for Falling Back From Release 2.1 or Release 2.0 You perform SQL/MP metadata migration as part of the database migration from NonStop SQL/MP to Release 2.1 or Release 2.0.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 Steps for Migrating MPALIAS Metadata From Release 1.8 to Release 2.1 or Release 2.0 Steps for Migrating MPALIAS Metadata From Release 1.8 to Release 2.1 or Release 2.0 Note. Before you use the migrate utility: • • Create the user catalogs and schemas in the Release 2.1 or Release 2.0 database. For catalog and schema naming guidelines, see the SQL/MX Reference Manual.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 Steps for Migrating MPALIAS Metadata From Release 1.8 to Release 2.1 or Release 2.0 The command displays this result: Hewlett-Packard Nonstop (TM) SQL/MX Metadata Migration Utility 2.0 (c) Copyright 2003 Hewlett-Packard Development Company, LP. SHOW: The following SQLMP ALIASes exist in the MP catalog... CAT.SCH.ATABLE is mapped to \POOH.$SPJ01.SPJQA.ATABLE CAT.SCH.ATESTTAB is mapped to \POOH.$SPJ01.SPJQA.ATESTTAB CAT.SCH.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 • Steps for Migrating MPALIAS Metadata From Release 1.8 to Release 2.1 or Release 2.0 References to catalogs or schemas that do not exist in the SQL/MX environment 5. If you find discrepancies, use the SHOW SCRIPT command of the migrate utility to generate a script file in which you can fix the discrepancies: /usr/tandem/sqlmx/bin: migrate SHOW MPALIAS SCRIPT mpalias_script.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 Steps for Migrating DEFAULTS Metadata From Release 1.8 to Release 2.1 or Release 2.0 The migrate EXECUTE command captures information about the metadata entries that fail to migrate to Release 2.1 or Release 2.0 and logs this information in the error log file. ==******************************************************************* == Error occurred: 2 CREATE SQLMP ALIAS ACC_SAMDBCAT.INVENT.PARTSUPP \POOH.$ODBC97.ACC.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 Steps for Migrating ODBC Metadata From Release 1.8 to Release 2.1 or Release 2.0 Note. Because there are no discrepancies to report for the DEFAULTS table, it is not necessary to use the PRELIMINARY_REPORT command to display discrepancies. Use the SHOW SCRIPT command to generate a script file for editing purposes. 3.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 Steps for Migrating ODBC Metadata From Release 1.8 to Release 2.1 or Release 2.0 2. To determine which system-level default settings exist in the ODBC metadata tables in the SQL/MP system catalog, use the SHOW ODBC command of the migrate utility and direct this information to a file: /usr/tandem/sqlmx/bin: migrate SHOW ODBC > odbc_info.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 Steps for Migrating SPJs From Release 1.8 to Release 2.1 or Release 2.0 Steps for Migrating SPJs From Release 1.8 to Release 2.1 or Release 2.0 Note. Before you use the migrate utility: • • Create the user catalogs and schemas in the Release 2.1 or Release 2.0 database. For catalog and schema naming guidelines, see the SQL/MX Reference Manual.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.0 Tools for Migrating Data For Enscribe files with other data types, the default format of data as read from the Enscribe file is not compatible with import. In this case, DataLoader/MX must be customized by modifying the user exit routines, and the required data conversion must be specified by the user. The transformations that are required depend on the Enscribe output format and the import input format.
Migrating an SQL/MP Database to NonStop SQL/MX Release 2.1 or Release 2.
15 Measuring Performance During the life of an SQL application, you might need to measure the performance of all, or part, of the application. Several NonStop software products can provide statistical information about performance. Collecting these statistics requires an in-depth understanding of the system, the layout of the database tables, and the use of the application programs. You usually gather statistics when you are: • Running a performance benchmark.
SHOWLABEL Command Measuring Performance SHOWLABEL Command The MXCI SHOWLABEL command displays file-label information for SQL/MX objects. (SHOWLABEL does not support SQL/MP objects or SQL/MP aliases.) For performance statistics, use SHOWLABEL to determine index levels and extent information. This example returns detailed information about the EMPLOYEE table. Use the DETAIL option to display information about partitions and indexes.
SET STATISTICS and DISPLAY STATISTICS Commands Measuring Performance Max Extents: 160 Extents Allocated: 1 EOF: 12288 Index Levels: 2 Record Expression Label Length: 14280 Security Label Length: 120 Key Columns: 0 ASC Partitioning Scheme: RP Partition Array - 0 partition[s] IndexMap Array - 2 index[es] Index[0]: \KINGPIN.$TX0101.ZSDFM5XP.WR9ZNQ00 Index columns: 2 ASC , 1 ASC , 0 ASC Index[1]: \KINGPIN.$TX0101.ZSDFM5XP.
Measure Performance Measurement Tool Measuring Performance You can display statistics for this statement by using the DISPLAY STATISTICS command: DISPLAY STATISTICS; Start Time End Time Elapsed Time Compile Time Execution Time Table Name 2003/12/11 18:18:24.489 2003/12/11 18:18:24.677 00:00:00.188 00:00:00.046 00:00:00.142 Records Accessed SAMDBCAT.PERSNL.
Measure Performance Measurement Tool Measuring Performance You can collect performance statistics for SQL/MX objects by using these Measure entities: • • • SQLPROC provides information about an SQL process. There is one SQLPROC counter record per SQL process selected. SQLSTMT provides information about all SQL statements within an SQL process. There is one SQLSTMT counter record per SQL statement of a selected SQL process.
Measuring Performance Statistics and Reports for NonStop SQL/MX Statistics and Reports for NonStop SQL/MX Use the Measure entities to gather statistics on an SQL/MX database and application programs. After gathering the statistics, you can generate reports about the statistics. Information gathered by the entities is described next. SQLPROC Statistics The SQLPROC report provides information about specific statistics concerning recompilations, NEWPROCESS calls, and opens of an SQL process.
Measuring Performance • • • • • SQL/MX Measurement Models 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). In NonStop SQL/MX, there is no file I/O waiting as there is for NonStop SQL/MP.
Measuring Performance SQL/MX Measurement Models Three types of statistics are discussed next: • • • Startup cost of an application program Execution cost of a running process Database access costs for SQL tables and indexes Startup Cost Use the following counters to analyze the startup cost of an application program. These statistics are gathered by the SQLPROC entity. • • • • • • SQL-STATEMENT-RECOMPILES contains the number of statement recompiles done on this process.
Measuring Performance SQL/MX Measurement Models Note that the first time a statement in a procedure executes after measurement has been started, a setup time is included for allocating all the SQLSTMT counters for the procedure. • • • • • • • • • DISK-READS stores the number of physical disk I/O operations performed for this statement. RECOMPILES stores the number of times the statement was recompiled. For valid statements, this number should be zero.
Measuring Performance SQL/MX Measurement Models always be the same or greater than RECORDS-USED. The ratio between RECORDS-USED/RECORDS-ACCESSED is the selectivity of the statement. A query is most efficient when the number of records used is the same or slightly lower than the number of records accessed. If the number of records accessed is much larger than the number used, the query is accessing many unnecessary rows. You can create an index to improve the selectivity.
16 Enhancing SQLMX Database Performance To achieve maximum performance, you must provide sufficient hardware to handle the throughput and size of the application database. In addition to hardware, many factors affect the performance of a database and application. Some factors are system dependent, others are application dependent. The factors discussed in this section are specific performance issues that can arise in an installed and operating SQL/MX environment.
Enhancing SQLMX Database Performance Understanding the Implications of Concurrency The number and type of queries used in an SQL/MX environment influence the performance of the database. For a detailed discussion of how to formulate queries to improve query performance while retrieving the desired output, see the SQL/MX Query Guide. For information on enhancing SQL/MP database performance, see the SQL/MP Installation and Management Guide.
Enhancing SQLMX Database Performance Minimizing Contention violate, the constraint before adding it. Until this validation operation is completed, the table data can be read but not updated. ° • • Creating an index ° During a CREATE INDEX operation, you can run static SELECT queries on the table but cannot perform recompilation. During a POPULATE INDEX operation, you can do both. Access to the table metadata is locked for the duration of the CREATE INDEX operation.
Enhancing SQLMX Database Performance Minimizing Contention MODIFY TABLE cat1.sch1.table1 ADD WHERE KEY = value ('San Jose') TO LOCATION $DATA12 WITH SHARED ACCESS COMMIT; When you specify the WITH SHARED ACCESS option with MODIFY, these actions occur: 1. Initialization and load. Reads from source partitions use browse access and so do not obtain locks. Writes to target partitions do obtain locks.
Enhancing SQLMX Database Performance Avoiding Contention Between DDL or Utility Operations must be retained on the system or on backup media until the MODIFY operation completes. Audit trails should not be automatically deleted before the MODIFY operation completes. If the audit fix-up process does not find audit files online, the system prompts the operator (on the system console) to restore the audit trails. If there are no backed-up audit trails, the request fails.
Enhancing SQLMX Database Performance Other Operational Considerations You can also get this SQL/MX error message: 1134 A concurrent utility or DDL is being performed on object object-name, its parent, or one of its dependencies. That operation must complete before the requested operation can run. For detailed information about this and other SQL/MX error messages, see the SQL/MX Messages Manual. Other Operational Considerations In general, SQL/MX utility operations follow a three-step process: 1.
Enhancing SQLMX Database Performance Keeping Statistics Current that must be changed during the operation. The lock timeout value is currently 60 seconds and cannot be changed. In a similar way, certain other statements or commands present concurrency issues that can affect the result of the operation. When you are duplicating, backing up, or moving data from one object to another, these functions do not require sustained exclusive access to the source objects.
Enhancing SQLMX Database Performance • • Using SQLMXBUFFER to Improve Database Performance Changes to UPDATE STATISTICS that affect cardinality estimates. As a result, the Release 2.1 or Release 2.0 optimizer traverses different plans in the search space than does the Release 1.8 optimizer. More aggressive tuning of undesirable plans by the optimizer. As a result, the current optimizer does not consider some Release 1.8 query plans.
Enhancing SQLMX Database Performance Setting the SCF SQLMXBUFFER Attribute Setting the SCF SQLMXBUFFER Attribute For data volumes with NonStop SQL/MX, you can change the size of the segment data area by using the SQLMXBUFFER attribute. The volume must be in the STOPPED state to alter the SQLMXBUFFER attribute. From SCF, set SQLMXBUFFER as: ALTER $volume, SQLMXBUFFER n (where n is the units in megabytes) The minimum SQLMXBUFFER size is 1 MB, and the maximum is 768 MB.
Enhancing SQLMX Database Performance Optimizing Index Use kernel-managed swap file use, how to monitor swap file usage by process, and how to change your KMSF configuration. To identify and avoid possible memory contention between either the application or the master executor and other system components in process space such as QIO, see the discussion on configuring the QIO subsystem in the QIO Configuration and Management Manual.
Enhancing SQLMX Database Performance Using Co-located Indexes To take maximum advantage of parallel index updates, put a table’s indexes on separate volumes and configure them on separate processors to eliminate contention of parallel operations on indexes serviced by the same disk process. Using Co-located Indexes NonStop SQL/MX supports co-located indexes, where an index is partitioned across the same disk volumes as its underlying table.
Enhancing SQLMX Database Performance Performing FUP RELOADs to Generate More Accurate Query Plans The optimizer requests sequential prefetch for all scan operations expected to read sequentially for more than a few blocks. When sequential prefetch is used, the disk process attempts to read a group of several consecutive blocks with a single I/O operation.
Enhancing SQLMX Database Performance • Checking Data Integrity Perform FUP RELOADs on tables and indexes after an unusually large number of INSERT, UPDATE, and DELETE operations have been performed on them. Partitions are automatically reloaded after many partition management operations have been performed on them. Checking Data Integrity NonStop SQL/MX provides data integrity checking when constraints are defined for a table.
Enhancing SQLMX Database Performance ° Creating Logical Views of Data When NonStop SQL/MX verifies the constraints on the input data, the potential message traffic between servers and requesters might be increased when error messages are generated on invalid data. Creating Logical Views of Data Logical views of the database are groupings of data different from the physical database.
Enhancing SQLMX Database Performance Matching Block Split Operation to Table Usage Matching Block Split Operation to Table Usage In a table with key-sequenced organization, when an INSERT operation causes a data block to overflow, the disk process makes room for the new row by splitting the block and transferring some of its contents to a newly allocated block. The disk process can use one of two methods to split a block: • • Split the block in the middle.
Enhancing SQLMX Database Performance Matching Block Split Operation to Table Usage HP NonStop SQL/MX Installation and Management Guide—523723-004 16 -16
A Falling Back From NonStop SQL/MX Release 2.1 or Release 2.0 You should plan for a potential fallback to a previous database environment when you are planning the installation of Release 2.1 or Release 2.0. If necessary, you can fall back from Release 2.1 or Release 2.0 to any of these database environments: • • • Release 1.8 NonStop SQL/MP Enscribe You can also fall back from Release 2.1 to Release 2.
Falling Back From NonStop SQL/MX Release 2.1 or Release 2.0 Fallback Considerations Fallback Considerations • • • • • • • • • • To ensure you are able to fall back successfully, you must install certain SPRs before you attempt to fall back. For more information, see these references: ° ° ° Section 3, Installing NonStop SQL/MX G06.nn Release Version Update Compendium Readme file for the RVU to which you would fall back Falling back to a release of NonStop SQL/MX earlier than Release 1.
Falling Back From NonStop SQL/MX Release 2.1 or Release 2.0 Falling Back From Release 2.1 to Release 2.0 reinstalling Release 1.8. For information about these commands, see the SQL/MX Reference Manual. Falling Back From Release 2.1 to Release 2.0 If you migrate from Release 2.0 to Release 2.1, you might find it necessary to fall back to Release 2.0. To fall back from Release 2.1 to Release 2.0: 1. Reinstall Release 2.0. For installation instructions, see Section 3, Installing NonStop SQL/MX. 2.
Falling Back From NonStop SQL/MX Release 2.1 or Release 2.0 NonStop ODBC/MX Server Configuration Issues these changes after falling back to Release 1.8, you must manually make equivalent changes to the Release 1.8 metadata tables after falling back. NonStop ODBC/MX Server Configuration Issues Because the NonStop ODBC/MX Server configuration is stored in different tables, you do not have to create the tables after fallback.
Falling Back From NonStop SQL/MX Release 2.1 or Release 2.0 Drop Release 2.1 or Release 2.0 Objects these schemas and all subordinate objects. For complete command syntax and descriptions, see the SQL/MX Reference Manual. Note. If you encounter problems using MXCI to drop Release 2.1 or Release 2.0 objects, you can use the mxtool GOAWAY command instead. For information about this command, see the SQL/MX Reference Manual. a. Drop all Release 2.1 or Release 2.0 schemas and their subordinate objects.
Falling Back From NonStop SQL/MX Release 2.1 or Release 2.0 Load a Previous RVU The InstallSqlMx installation script creates an anchor file that contains the locations of system metadata tables. This simple text file resides in Guardian space in $SYSTEM.ZSQLMX.MXANCHOR. To remove this file: a. Log on using the super ID (user ID 255,255). b. At a TACL prompt, enter: PURGE $SYSTEM.ZSQLMX.MXANCHOR Caution. When you fall back from Release 2.1 or Release 2.0 to Release 1.
Falling Back From NonStop SQL/MX Release 2.1 or Release 2.0 ° Install Compatible Versions of Independent Products To view the online help for DIRCHECK, enter $system.zutil.dircheck help. To view the online help for the DIRCHECK DELETE command, enter $system.zutil.dircheck help delete. 3. Rebuild the disk free-space table by using the SCF CONTROL DISK $volume, REBUILDDFS command. For more information about CONTROL DISK, see the SCF Reference Manual for the Storage Subsystem.
Falling Back From NonStop SQL/MX Release 2.1 or Release 2.0 Recompile Release 1.8 Applications b. Run the mxinit script by entering: run mxinit [ -noFcheck ] mxinit determines the location and name of the SQL/MP system catalog. The -noFcheck option disables FCHECK from being run. By default, FCHECK is run on all volumes to check for SQL/MP tables with added columns.
Falling Back From NonStop SQL/MX Release 2.1 or Release 2.0 Falling Back to Enscribe 3. Resume using NonStop SQL/MP. If for some reason you decide to return to a previous RVU, these manuals provide useful information: • • The G06.nn Software Installation and Upgrade Guide provides instructions for falling back to a previous RVU. The SQL/MP Installation and Management Guide provides instructions for installing, configuring, and maintaining NonStop SQL/MP.
Falling Back From NonStop SQL/MX Release 2.1 or Release 2.
B 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-36 Access paths alternate 7-31 clustering key 7-31 Access privileges for SQL/MX objects 7-9 Accessing an SQL/MP database from SQL/MX 7-1 ADD COLUMN clause 7-21 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
B Index Applications (continued) environment 1-1 migrating 11-1 moving 11-3 moving to remote node 11-9 running on a remote node 11-9 Archiving SQL/MX objects 5-30 ASCII character set 7-21 Attributes altering 9-24 column, specifying 7-30 Audit bit, changing 12-32 Audit trails 12-2 AUDITCOMPRESS attribute altering index attributes 9-20 altering table attributes 9-24 audit trail data 7-19 Audited objects, archiving 5-7 Authorization, Safeguard 5-4 Automatic recompilation avoiding 16-14 gathering statistics 1
C Index Buffer 16-8 Buffer size, effect on query performance 16-11 C C preprocessor 3-23 Cache size 16-11 CACHE_HISTOGRAMS 6-7 CACHE_HISTOGRAMS_ REFRESH_INTERVAL 6-3, 6-7 CALLS counter, Measure 15-8 Capacity planning 14-3 CASCADE option 12-10 CAST function 7-22 Catalogs adding 9-4 CATALOGS table, displaying contents of 8-35 creating 7-6 dropping 9-29 querying metadata about 8-22/8-25 recovering 12-6 SCF ALTER DISK issues 12-36 SCF ALTER DISK, LABEL issues 12-37 Changing the database 9-1, 9-18 Character d
D Index CREATE TABLE statement adding columns 9-23 adding tables 9-16 creating table partitions 7-15 description of 7-11 CREATE VIEW statement 7-37 Creating catalogs 7-6 Creating constraints 7-35 Creating table partitions 7-15 C++ preprocessor 3-23 D Damaged objects, deleting 12-35 Data checking 1-4, 16-13 integrity constraints 16-13 partitioned 7-15 replicated 13-24 validity 1-4 views and consistency 4-11 Data consistency, checking 12-29 Data problems correcting 12-31 identifying 12-26 Data types ASCII
D Index DDL locks (continued) performing recovery on failed utility operations 10-7 using a query to obtain DDL lock and process ID information 10-6 DDL operation limits 16-5 DDL statements 1-17 DDL_DEFAULT_LOCATIONS description of 6-6 using to distribute primary range partitions 7-17 Decoupled keys 7-15 DEFAULT clause 7-28 DEFAULT NULL clause 7-29 Default settings altering 9-22 ANSI compliance 6-12 description of 6-1 general considerations 6-7 new for Release 2.0 6-3 new for Release 2.
E Index Distributing programs across nodes 11-8 DML statements 1-17 DOUBLE PRECISION data type 7-24 DP2 changing for fallback A-1 fallback SPRs 3-2 file labels 8-2 DP2_CACHE_4096_BLOCKS 6-3, 6-7 DROP command, CASCADE option 12-10 Dropping aliases 9-35 Dropping catalogs 9-29 Dropping columns 9-29 Dropping constraints 9-30 Dropping indexes 9-31 Dropping objects authorization requirements 9-27 summary 9-27 Dropping partitions description of 9-32 example of 9-33 Dropping schemas 9-33 Dropping SQL/MP aliases 9
G Index Fallback (continued) to Enscribe (continued) overview A-1 to NonStop SQL/MP A-1, A-8 to Release 1.8 considerations A-2 description of A-3 independent products A-7 initialization A-7 metadata A-3 overview A-1 recompiling A-8 RVU A-2 tasks A-4 to SQL/MP A-8 FCHECK description of 1-19 performed by InstallSqlmx 3-26 used during fallback to Release 1.
H Index GOAWAY command, mxtool damaged objects 12-35 description of 12-25 Guardian files physical names 1-7 retrieving information 12-30 H Hardware changing or moving 15-1 requirements 2-2, 14-1 Header files 3-18 HEADING attribute 7-30 HIST_NO_STATS_REFRESH_ INTERVAL 6-3 HIST_SCRATCH_VOL 6-7 HIST_SECURITY_WARNINGS 6-3 I IEEE floating point 14-1 import appending data 10-33 to multiple partitions in parallel 10-32 to tables 10-31 command options 10-22 DataLoader/MX use of 14-14, 14-15 guidelines for loadi
J Index InstallSqlmx (continued) using the script 3-25 Integrity, definitional 1-4 INTERACTIVE_ACCESS 6-3 Interface description files 3-19 Internal names, SQL objects 8-14 INTERVAL data type 7-25 INVALID attribute, TMF online dumps 12-10 Invalid programs 15-8 ISO88591 6-8 ISOLATION_LEVEL, ANSI compliance 6-12 Item names 7-21 J JDBC A-1 JDBC/MX driver description of 1-17 installation 3-4 K Key prefix 4-18 Keys clustering, indexes 7-31 index 4-16 primary, null values 7-29 KEYTAG column 4-16 Key-sequenced
M Index Measure (continued) SQL-NEWPROCESS counter 15-8 SQL-NEWPROCESS-TIME counter 15-8 SQL-OBJ-RECOMPILE-TIME counter 15-8 SQL-STATEMENT-RECOMPILES counter 15-8 SQL-STATEMENT-RECOMPILESTIME counter 15-8 statistics reports 15-6 Measuring performance 15-1/15-10 Memory management considerations 16-9 MESSAGES counter, Measure 15-9 Messages files 3-18 MESSAGE-BYTES counter, Measure 15-9 Metadata 8-2, 8-5 Metadata migration description of 14-7 fallback considerations 14-7 metadata tables 14-5 migrating DEFAUL
M Index Metadata, querying (continued) SQL/MP names in a schema, displaying 8-37 SQL/MX release, displaying 8-62 system schema tables, locating 8-21 system schema version, displaying 8-63 table attributes, displaying 8-34, 8-35 tables containing a column, displaying 8-57 tables in a catalog, displaying 8-31 tables in a schema, displaying 8-32 view attributes, displaying 8-36 views in a schema, displaying 8-36 Migrating nodes for improved query plan performance 11-15 Migration basics 14-1 Enscribe database
N Index MXCI, starting 3-21 MXCMP file 3-11 MXCMP_PLACES_LOCAL_MODULES 63 MXCS 1-16 MXESP file 3-11 MXGNAMES EDIT files B-2 examples of B-4 input files B-2 output files B-2 SHOWDDL file B-2 SQL names file B-2 MXHELP 3-22 mxlangman.jar file 3-14 mxlangman.
O Index NOT NULL clause 7-28, 7-29 NOT_NULL_CONSTRAINT_ DROPPABLE_OPTION alternate setting 6-8 ANSI compliance 6-12 description of 6-4 NSM/web display of metadata 8-1 NTL Web site 3-22 NULL clause 7-28 Null values 7-21, 7-28 NUMERIC data type columns 7-22 O Object name resolution 11-11 Object namespaces 1-8 Objects altering 9-18 archiving 5-7 naming guidelines for 4-1 restoring 12-21 SCF ALTER DISK, LABEL issues 12-37 ODBC A-1 OLTP 7-39 Online disk remirror feature 3-2 Online dumps 12-10 Online help down
Q Index Performance (continued) OLTP 7-39 partitioning data 7-15, 16-14 partitions 9-11 planning for 14-3 queries 16-1 statistics 15-1 testing 9-9 tools for analyzing 15-1 VARCHAR columns 7-22 views 16-14 Periodic backup 5-31 Physical file structure 4-3 Platform.
R Index R RDF configuring disk volumes 5-22 creating a backup database with identical file names 5-24 creating an online backup database 5-24 creating backup catalogs 5-22 creating backup schemas 5-22 populating an online backup database 5-25 recovering dropped tables 5-25 retrieving saved DDL 5-26 setting up a backup system 5-21 synchronizing a backup database 5-25 using Backup and Restore to create offline backup databases 5-23 using Guardian names for SQL/MX objects 5-21 using TGT CATALOG option of RES
S Index Replicated data 13-24 Reports, Measure statistics 15-6 Requesters 16-13 Resource fork description of 8-2 physical files 12-24 run-time metadata 12-27 RESTORE command 12-22 Rows, ordering using an index 4-14 Running applications on a remote node 11-9 Run-time library files 3-16 Run-time metadata, retrieving 12-29 S S72000 processor requirement 14-1 Safeguard, used for SQL/MX file security 5-4 Sample database migration 3-2 SAVE_DROPPED_TABLE_DDL alternate setting 6-10 description of 6-4 recovery 5-
S Index sqlcli.h file 3-18 SQLHIST file 3-17, 3-18 sqlj.
S Index SQL/MX UDR server 3-20 SRLs 3-7 START DISK command, SCF 12-38 START TMF command 12-2 Startup cost, SQL/MX 15-8 Statements, execution costs 15-8 Statistics displaying 15-3 execution 15-8 FILE entity 15-6 Measure collection 15-4/15-10 MXCI STATISTICS option 15-3 performance 15-1 PROCESSH entity 15-7 program execution 15-8 reports, Measure 15-6 SQLPROC report 15-6 SQLSTMT report 15-6 updating 12-10 STATUS command 10-4 STOP DISK command, SCF 12-38 Stored procedures adding 9-15 altering 9-22 dropping 9
T Index System defaults (continued) PRIMARY_KEY_CONSTRAINT_DROP PABLE_OPTION 6-8 QUERY_CACHE 6-8 QUERY_CACHE_ STATEMENT_PINNING 6-9 QUERY_CACHE_MAX_ VICTIMS 6-8 QUERY_CACHE_REQUIRED_ PREFIX_KEYS 6-9 READONLY_CURSOR 6-12 REF_CONSTRAINT_NO_ ACTION_LIKE_RESTRICT 6-4, 6-10, 6-12 SAVE_DROPPED_TABLE_ DDL 6-4 SAVE_DROPPED_TABLE_DDL 6-10 SCRATCH_DISKS 6-10 SCRATCH_DISKS_ EXCLUDED 6-10 SCRATCH_DISKS_ PREFERRED 6-11 setting and updating 6-1 TEMPORARY_TABLE_ HASH_PARTITIONS 6-11 TEMPORARY_TABLE_HASH_ PARTITIONS 6-5
U Index TMF subsystem (continued) creating and managing online dumps 5-14 description of 5-10 determining a level of data protection 5-13 determining size requirements 5-13 DUMP FILES command 12-1 fallback A-1 file recovery 5-12, 12-1, 12-2 guidelines for configuring 5-13 INFO DUMPS, OBEYFORM command 5-8, 5-9 invalidation of online dumps 5-15 online dumps 5-11 INVALID attribute 12-10 performing 5-9 RELEASED attribute 12-10 operations that affect TMF online dumps 5-15 preinstallation tasks 3-3 RECOVER FILE
V Index V VARCHAR columns, performance issues 7-22 VARCHAR_PARAM_DEFAULT_SIZE 6-5, 6-11 VERIFY utility, mxtool description of 8-3, 12-29 example of 12-19, 12-20 recovered objects 12-10 Verifying software 3-19 Versioning guidelines falling back 1-14 query execution plans 1-14 SQL/MX applications 1-15 SQL/MX database objects 1-15 issues 1-12, 11-8 principles 1-12 software versioning 1-12 version identification 1-13 Versions executable files 3-19 querying metadata about 8-62/8-63 Views adding 9-17 altering 9
Z Index HP NonStop SQL/MX Installation and Management Guide—523723-004 Index -22
