HP 3000 Manuals HP 3000 Manuals
HP 3000 MPE/iX Computer Systems ALLBASE/SQL Database Administration Guide HP Part No. 36216-90005 Printed in U.S.A. Edition Seventh Edition E0897
Copyright (c) 1987--1989, 1991--1994, 1997 by Hewlett-Packard Company. The information contained in this document is subject to change without notice. Hewlett-Packard makes no warranty of any kind with regard to this material, including, but not limited to, the implied warranties of merchantability or fitness for a particular purpose. Hewlett-Packard shall not be liable for errors contained herein or for direct, indirect, special, incidental or consequential damages in connection with the furnishing or use of this material. Hewlett-Packard assumes no responsibility for the use or reliability of its software on equipment that is not furnished by Hewlett-Packard. This document contains proprietary information which is protected by copyright. All rights reserved. Reproduction, adaptation, or translation without prior written permission is prohibited, except as allowed under the copyright laws. Restricted Rights Legend Use, duplication, or disclosure by the U.S. Government is subject to restrictions as set forth in subparagraph (c) (1) (ii) of the Rights in Technical Data and Computer Software clause at DFARS 252.227-7013. Rights for non-DOD U.S. Government Departments and Agencies are as set forth in FAR 52.227-19 (c) (1,2). Hewlett-Packard Company 3000 Hanover Street Palo Alto, CA 94304 U.S.A. Printed August 1997 Printing History The following table lists the printings of this document, together with the respective release dates for each edition. The software version indicates the version of the software product at the time this document was issued. Many product releases do not require changes to the document. Therefore, do not expect a one-to-one correspondence between product releases and document editions. Edition Date Software Version --------------------------------------------------------------------------------------- First Edition December 1987 36216-02A.01.00 Second Edition October 1988 36216-02A.12.00 Third Edition October 1989 36216-02A.20.00 Fourth Edition December 1990 36216-02A.E1.00 Fifth Edition June 1992 36216-02A.F0.00 Sixth Edition April 1994 36216-02A.G0.00 Seventh Edition August 1997 36216-02A.G2.00 [REV BEG][REV END] ALLBASE/SQL Documents Title Customer Order Number -------------------------------------------------------------------------------------- ALLBASE/NET User's Guide 36216-90031 ALLBASE/SQL Advanced Application Programming Guide 36216-90100 ALLBASE/SQL C Application Programming Guide 36216-90023 ALLBASE/SQL COBOL Application Programming Guide 36216-90006 ALLBASE/SQL Database Administration Guide 36216-90005 ALLBASE/SQL FORTRAN Application Programming Guide 36216-90030 ALLBASE/SQL Message Manual 36216-90009 ALLBASE Pascal Application Programming Guide 36216-90007 ALLBASE/SQL Performance and Monitoring Guidelines 36216-90102 ALLBASE/SQL Reference Manual 36216-90001 ISQL Reference Manual for ALLBASE/SQL and IMAGE/SQL 36216-90096 Up and Running with ALLBASE/SQL 36389-90011 ODBCLINK/SE Reference Manual 36217-90403 Preface This manual describes how to design, create, and maintain ALLBASE/SQL databases on HP 3000 computers running under the MPE/iX operating system. ALLBASE/SQL is Hewlett-Packard's proprietary relational database management product. MPE/iX, Multiprogramming Executive with Integrated POSIX, is the latest in a series of forward-compatible operating systems for the HP 3000 line of computers. In HP documentation and in talking with HP 3000 users, you will encounter references to MPE XL, the direct predecessor of MPE/iX. MPE/iX is a superset of MPE XL. All programs written for MPE XL will run without change under MPE/iX. You can continue to use MPE XL system documentation, although it may not refer to features added to the operating system to support POSIX (for example, hierarchical directories). This manual contains advanced information about ALLBASE/SQL database administration. It is intended for experienced users of SQL and SQL application programmers. Topics are discussed in separate chapters, as follows: * Chapter 1, "DBA Tasks and Tools," presents the basic concepts and terms relating to ALLBASE/SQL database administration. * Chapter 2, "Logical Design," shows how to create a database schema prior to creating an ALLBASE/SQL DBEnvironment. * Chapter 3, "Physical Design," describes the physical files used in ALLBASE/SQL, and shows how to calculate the amount of space required for various database objects. * Chapter 4, "DBEnvironment Configuration and Security," details the steps in creating a new DBEnvironment. * Chapter 5, "Database Creation and Security," shows how to create specific database objects. * Chapter 6, "Backup and Recovery," describes procedures for routine backup and for recovery when needed. * Chapter 7, "Maintenance," presents the tasks required for modifying a DBEnvironment once it has been in operation. * Chapter 8, "System Catalog," contains a complete description of all the system catalog pseudotables and views. The following appendixes contain additional reference material. * Appendix A describes system limits. * Appendix B presents a table of the authorities required to execute ALLBASE/SQL commands. * Appendix C is a summary of SQL syntax. * Appendix D is a summary of ISQL syntax. * Appendix E is a list of locks obtained on the system catalog by various SQL commands. * Appendix F contains the syntax of SQLUtil commands. * Appendix G contains the syntax of SQLGEN commands. * Appendix H contains the syntax of SQLMigrate commands. * Appendix I contains the syntax of SQLAudit commands. Most of the examples in this manual are based on the tables, views, and other objects in the sample DBEnvironment PartsDBE. For complete information about PartsDBE, refer to the ALLBASE/SQL Reference Manual, appendix C. What's New in this Release [REV BEG] G.1 and G.2 New Features The following table highlights the new or changed functionality added in G.1 and G.2 releases, and shows you where each feature is documented. New Features in ALLBASE/SQL Releases G.1 and G.2 ----------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in... | | (Category) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | New operand to | Adds an operand to concatenate | ALLBASE/SQL Reference Manual, | | concatenate | character or binary strings in | "Expressions." | | strings | an expression. New operand: | | | (Standards) | || | | | | | | ----------------------------------------------------------------------------------------------- | | | | | RENAME Column or | Adds capability of defining a | ALLBASE/SQL Reference Manual, RENAME | | Table | new name for an existing table | COLUMN and RENAME TABLE in "SQL | | (Usability) | or column in a DBEnvironment. | Statements." | | | You cannot rename a table or | | | | column that has check | | | | constraints or an IMAGE/SQL | | | | table. New commands: RENAME | | | | COLUMN, RENAME TABLE. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | CAST function | Adds the CAST function to | ALLBASE/SQL Reference Manual, "Cast" | | added to | allow explicitly converting | in "Expressions." | | Expression syntax | from one data type to another. | | | (Usability) | It allows conversion between | | | | compatible data types and | | | | between normally incompatible | | | | data types such as CHAR and | | | | INTEGER. New Expression | | | | function: CastFunction. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Syntax added to | Automates execution of COMMIT | ALLBASE/SQL Reference Manual, VALIDATE | | VALIDATE | WORK after each module or | in "SQL Statements."[REV END] | | (Usability, | procedure is validated when | | | Performance) | WITH AUTOCOMMIT is used. All | | | | sections are revalidated | | | | whether valid or invalid when | | | | FORCE is used. This can | | | | reduce log space and shared | | | | memory requirements for the | | | | VALIDATE statement. New | | | | syntax for VALIDATE: FORCE, | | | | WITH AUTOCOMMIT. | | | | | | ----------------------------------------------------------------------------------------------- Table Front-0. New Features in ALLBASE/SQL Releases G.1 and G.2 (cont.) -------------------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in | | (Category) | | | | | | | -------------------------------------------------------------------------------------------------------- | [REV BEG] | | | | | | | | Syntax added to | Automates execution of COMMIT | ALLBASE/SQL Reference Manual, DELETE | | DELETE | WORK at the beginning of the | in "SQL Statements." | | (Usability, | DELETE and after each batch of | | | Performance) | rows is deleted when WITH | | | | AUTOCOMMIT is used. Reduces | | | | log-space and shared-memory | | | | requirements. WITH AUTOCOMMIT | | | | cannot be used in some cases | | | | (see the DELETE statement). | | | | New syntax for DELETE: WITH | | | | AUTOCOMMIT. | | | | | | -------------------------------------------------------------------------------------------------------- | | | | | Decimal | Increases maximum precision | ALLBASE/SQL Reference Manual, "Decimal | | operations | from 18 to 27. | Operations" in "Data Types." | | (Usability) | | | | | | | -------------------------------------------------------------------------------------------------------- | | | | | Terminate a query | Allows termination of a query | ALLBASE/SQL Reference Manual, | | (Usability, | for a connection or | TERMINATE QUERY, SET SESSION, SET | | Performance) | transaction. New statement: | TRANSACTION in "SQL Statements." | | | TERMINATE QUERY. New syntax | | | | for SET SESSION, SET | | | | TRANSACTION. | | | | | | -------------------------------------------------------------------------------------------------------- | | | | | Terminate a | Allows stopping of a given | ALLBASE/SQL Reference Manual, | | transaction | transaction. New statement: | TERMINATE TRANSACTION, SET SESSION, | | (Usability, | TERMINATE TRANSACTION. New | SET TRANSACTION in "SQL Statements." | | Performance) | syntax for SET SESSION, SET | | | | TRANSACTION. | | | | | | -------------------------------------------------------------------------------------------------------- | | | | | Timeout enhanced | Allows specifying the action | ALLBASE/SQL Reference Manual, SET | | to allow | when a timeout expires. New | SESSION in "SQL Statements." | | specifying what | attributes for SET SESSION and | | | is rolled back or | SET TRANSACTION: TERMINATION | | | terminated | AT LEVEL, USER TIMEOUT, ON | | | (Usability, | TIMEOUT ROLLBACK. | | | Performance) | | | | | | | -------------------------------------------------------------------------------------------------------- | | | | | Allow or disallow | Grants or revokes the ability | ALLBASE/SQL Reference Manual, GRANT, | | SQLMON for users. | to run SQLMON for specific | REVOKE in "SQL Statements."[REV END] | | (Usability) | users. New attribute for | | | | GRANT and REVOKE: MONITOR. | | | | | | -------------------------------------------------------------------------------------------------------- Table Front-0. New Features in ALLBASE/SQL Releases G.1 and G.2 (cont.) -------------------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in | | (Category) | | | | | | | -------------------------------------------------------------------------------------------------------- | [REV BEG] | | | | | | | | Allow or disallow | Grants or revokes the ability | ALLBASE/SQL Reference Manual, GRANT, | | authority to | to create modules for specific | REVOKE in "SQL Statements." | | create modules. | users. New attributes for | | | (Usability) | GRANT and REVOKE: INSTALL. | | | | | | -------------------------------------------------------------------------------------------------------- | | | | | Script for | Provides SQLINSTL script for | SQLINSTL file; Communicator 3000 | | migration to a | migration to a new release of | MPE/iX Release 5.5 (Non-Platform | | new release | ALLBASE/SQL. Read the SQLINSTL | Software Release C.55.00), | | (Usability, | file on your system for more | "ALLBASE/SQL Enhancements"; | | Tools) | information. | ALLBASE/SQL Database Administration | | | | Guide in "SQLINSTL" section of the | | | | "DBA Tasks and Tools" chapter. | | | | | -------------------------------------------------------------------------------------------------------- | | | | | GENPLAN on a | Obtains an access plan of a | ALLBASE/SQL Reference Manual, GENPLAN | | section | stored static query by | in "SQL Statements." | | (Usability) | specifying the module and | | | | section number. Changed | | | | syntax: GENPLAN. | | | | | | -------------------------------------------------------------------------------------------------------- | | | | | POSIX support | Starting with G.1, the | Communicator 3000 MPE/iX Release 5.5 | | (Tools) | ALLBASE/SQL preprocessor | (Non-Platform Software Release | | | (PSQLCOB) supports | C.55.00), "ALLBASE/SQL Enhancements." | | | preprocessing and generation | | | | of Microfocus COBOL source | | | | code under POSIX (Portable | | | | Operating System Interface). | | | | | | -------------------------------------------------------------------------------------------------------- | | | | | Terminate a | Terminates one or more | ALLBASE/SQL Reference Manual, | | user's | connections for a user. New | TERMINATE USER in "SQL Statements." | | connections | syntax for TERMINATE USER: CID | | | (Connectivity) | ConnectionID. | | | | | | -------------------------------------------------------------------------------------------------------- | | | | | Run Queue Control | Allows running HPDADVR in D | Communicator 3000 MPE/iX Release 5.5 | | for ALLBASE/NET | queue for an MPE/iX session or | (Non-Platform Software Release | | (Connectivity) | HP-UX connection or C queue | C.55.00), "ALLBASE/SQL Enhancements." | | | for an MPE/iX job connection. | | | | New environment variable: | | | | HPSQLJOBTYPE. | | | | | | -------------------------------------------------------------------------------------------------------- | | | | | PC ODBC 16-bit | ODBCLINK/SE allows | ODBCLINK/SE Reference Manual | | and 32-bit | connectivity to ALLBASE and | | | support | IMAGE/SQL servers from a PC | | | (Connectivity, | running MS Windows using ODBC. | | | Client/server) | | | | | | | -------------------------------------------------------------------------------------------------------- | | | | | Year 2000 | Provides the JCW | "Date/Time Functions" in the | | solution | HPSQLSPLITCENTURY to use in | "Expressions" chapter of the | | (Standards) | setting a value between 0 and | ALLBASE/SQL Reference Manual | | | 99. This value is used to | | | | change the century part of the | | | | DATE and DATETIME functions to | | | | override the default of 19. | | | | | | -------------------------------------------------------------------------------------------------------- [REV END] G.0 Features The following table highlights the new or changed functionality in release G.0, and shows you where each feature is documented. New Features in ALLBASE/SQL Release G.0 ----------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in... | | (Category) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Stored procedures | Provides additional stored | ALLBASE/SQL Reference Manual, "SQL | | (Usability) | procedure functionality for | Statements" and "Using Procedures" in | | | application programs. Allows | "Constraints, Procedures and Rules;" | | | declaration of a procedure | ALLBASE/SQL Advanced Application | | | cursor and fetching of | Programming Guide, "Using Procedures | | | multiple rows within a | in Application Programs." | | | procedure to applications. | | | | New statement: ADVANCE. | | | | Changed syntax: CLOSE, CREATE | | | | PROCEDURE, DECLARE CURSOR, | | | | DESCRIBE, EXECUTE, EXECUTE | | | | PROCEDURE, FETCH, OPEN. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Case | Adds an optional attribute to | ALLBASE/SQL Reference Manual, | | insensitivity | the character and varchar type | "Comparison Predicate" in "Search | | (Usability) | column attributes of tables. | Conditions," CREATE TABLE in "SQL | | | Allows search and compare of | Statements." | | | these columns in a case | | | | insensitive manner. Four new | | | | SQLCore data types are added. | | | | Changed syntax: ALTER TABLE, | | | | CREATE TABLE. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Support for 1023 | Increases the maximum number | ALLBASE/SQL Reference Manual, CREATE | | columns | of columns per table or view | TABLE and CREATE VIEW in "SQL | | (Usability) | to 1023. Increases maximum | Statements;" ALLBASE/SQL Database | | | sort columns and parameters in | Administration Guide, "ALLBASE/SQL | | | a procedure to 1023. | Limits" appendix. | | | | | ----------------------------------------------------------------------------------------------- | | | | | ISQL HELP | Gives help for entire command | ISQL Reference Manual for ALLBASE/SQL | | improvements | instead of only the verb. | and IMAGE/SQL, HELP in "ISQL | | (Usability) | | Commands." | | | | | ----------------------------------------------------------------------------------------------- | | | | | EXTRACT command | Extracts modules from the | ISQL Reference Manual for ALLBASE/SQL | | (Usability) | database and stores them in a | and IMAGE/SQL, "Using Modules" in | | | module file. Allows for | "Using ISQL for Database Tasks," | | | creation of a module file at | EXTRACT, INSTALL in "ISQL Commands." | | | any time based on the current | | | | DBEnvironment without | | | | preprocessing. New command: | | | | EXTRACT. Changed syntax: | | | | INSTALL. | | | | | | ----------------------------------------------------------------------------------------------- Table Front-0. New Features in ALLBASE/SQL Release G.0 (cont.) ----------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in | | (Category) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | New SQLGEN | Generates SQL statements | ALLBASE/SQL Database Administration | | GENERATE | necessary to recreate modified | Guide, "SQLGEN Commands" appendix. | | parameters | access plans for module | | | (Usability) | sections. New syntax for | | | | GENERATE: DEFAULTSPACE, | | | | MODOPTINFO, PARTITION, | | | | PROCOPTINFO, SPACEAUTH. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Row level locking | Permits multiple transactions | ALLBASE/SQL Reference Manual, | | (Usability) | to read and update a table | "Concurrency Control through Locks and | | | concurrently because locking | Isolation Levels;" ALLBASE/SQL | | | is done at row level. Since | Database Administration Guide, | | | the transaction will obtain | "Effects of Page and Row Level | | | more locks, the benefits must | Locking" in "Physical Design." | | | be weighed against the costs. | | | | (Previously documented in an | | | | addendum after F.0 release.) | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Increased number | Removes the limitation of 240 | ALLBASE/SQL Database Administration | | of users | users supported by | Guide, "ALLBASE/SQL Limits" appendix. | | (Usability) | pseudotables. (Maximum is | | | | system session limits: 2000 | | | | on HP-UX; 1700 on MPE/iX.) | | | | | | ----------------------------------------------------------------------------------------------- | | | | | POSIX support | Improves application | ALLBASE/SQL Advanced Application | | (Usability) | portability across MPE/iX and | Programming Guide, "POSIX Preprocessor | | | HP-UX. Enhances the | Invocation" in "Using the | | | ALLBASE/SQL preprocessors to | Preprocessor." | | | run under POSIX (Portable | | | | Operating System Interface) on | | | | MPE/iX. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Application | Provides the use of threads in | ALLBASE/SQL Advanced Application | | thread support | an application. Allows | Programming Guide, "Using the | | (Performance, | ALLBASE/SQL to be used in an | Preprocessor." | | Usability) | application threaded | | | | environment on MPE/iX. | | | | Application threads are light | | | | weight processes that share | | | | some resources and last for | | | | the duration of a transaction. | | | | Threaded applications reduce | | | | the overhead of context | | | | switching and improve the | | | | performance of OpenTP | | | | applications. | | | | | | ----------------------------------------------------------------------------------------------- Table Front-0. New Features in ALLBASE/SQL Release G.0 (cont.) ----------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in | | (Category) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | High Availability | Provides a collection of | ALLBASE/SQL Reference Manual, "SQL | | | features to keep systems | Statements;" ALLBASE/SQL Database | | | available nonstop including: | Administration Guide, "Maintaining a | | | Partial STORE and RESTORE, | Nonstop Production System" in | | | Partial rollforward recovery, | "Maintenance" chapter and "SQLUtil" | | | DBEFiles in different groups | appendix. | | | (MPE/iX), detaching and | | | | attaching database objects, | | | | CHECKPOINT host variable, | | | | changing log files, console | | | | messages logged to a file, | | | | generating fewer log records | | | | by using TRUNCATE TABLE to | | | | delete rows, and new system | | | | catalog information. See the | | | | following features for new and | | | | changed syntax. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Partial | Supports partial rollforward | ALLBASE/SQL Database Administration | | rollforward | recovery through PARTIAL | Guide, "Backup and Recovery" chapter | | recovery | option on SETUPRECOVERY. Used | and SETUPRECOVERY PARTIAL in "SQLUtil" | | (High | to recover specific DBEFiles | appendix. | | Availability) | while allowing access to other | | | | DBEFiles. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Partial STORE and | Gives more flexibility in | ALLBASE/SQL Database Administration | | RESTORE | backup and recovery strategies | Guide, "Backup and Recovery" chapter | | (High | by allowing partial store and | and "SQLUtil" appendix. | | Availability) | restore of DBEFiles, | | | | DBEFileSets or combinations of | | | | both. See "New and changed | | | | SQLUtil commands for increased | | | | availability" later in this | | | | table. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | DBEFile group | Manages DBEFiles so they can | ALLBASE/SQL Reference Manual, CREATE | | change on MPE/iX | be placed in a particular | DBEFile in "SQL Statements;" | | (High | group or on a particular | ALLBASE/SQL Database Administration | | Availability) | volume (MPE/iX). Use either | Guide, "Maintaining a Nonstop | | | CREATE DBEFILE or MOVEFILE. | Production System" in "Maintenance" | | | | chapter and MOVEFILE in "SQLUtil" | | | | appendix. | | | | | ----------------------------------------------------------------------------------------------- | | | | | Detaching and | Detaches or attaches a DBEFile | ALLBASE/SQL Database Administration | | attaching | or DBEFileSet from the | Guide, "Maintaining a Nonstop | | database objects | DBEnvironment. This is useful | Production System" in "Maintenance" | | (High | for data that is accessed | chapter and DETACHFILE, ATTACHFILE in | | Availability) | infrequently such as tables | "SQLUtil" appendix. | | | containing historical data | | | | only. New SQLUtil commands: | | | | DETACHFILE, ATTACHFILE. | | | | | | ----------------------------------------------------------------------------------------------- Table Front-0. New Features in ALLBASE/SQL Release G.0 (cont.) ----------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in | | (Category) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | New and changed | Adds support for high | ALLBASE/SQL Database Administration | | SQLUtil commands | availability and System | Guide, "SQLUtil" appendix. | | for increased | Management Intrinsics. | | | availability | Intended for non-stop, | | | (High | continuously available | | | Availability) | operations. New SQLUtil | | | | commands: ATTACHFILE, | | | | CHANGELOG, DETACHFILE, RESTORE | | | | PARTIAL, STORE PARTIAL, | | | | STOREINFO, STOREONLINE | | | | PARTIAL, WRAPDBE. | | | | Modified SQLUtil commands: | | | | MOVEFILE, RESTORE, RESTORELOG, | | | | SHOWDBE, SETUPRECOVERY, STORE, | | | | STORELOG, STOREONLINE. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | List files on | Lists physical names of files | ALLBASE/SQL Database Administration | | backup device | stored on backup device with | Guide, "Backup and Recovery" chapter | | (High | new SQLUtil command: | and STOREINFO in "SQLUtil" appendix. | | Availability) | STOREINFO. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Log file | Allows changing log files, | ALLBASE/SQL Reference Manual, | | improvements | switching of console messages | CHECKPOINT in "SQL Statements;" | | (High | to a file, and gives advance | ALLBASE/SQL Database Administration | | Availability) | warning for log full. | Guide, "Maintaining a Nonstop | | | Increased maximum size of a | Production System" in "Maintenance" | | | single DBE log file to 4 | chapter, CHANGELOG in "SQLUtil" | | | gigabytes. A DBEnvironment | appendix, and "ALLBASE/SQL Limits" | | | can have up to 34 log files | appendix. | | | configured. Changed syntax: | | | | CHECKPOINT. New SQLUtil | | | | command: CHANGELOG. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | New SET SESSION | Provides additional | ALLBASE/SQL Reference Manual, SET | | and SET | flexibility and improved | SESSION and SET TRANSACTION in "SQL | | TRANSACTION | performance. Allows setting | Statements." | | statements | and changing transaction and | | | (Standards, | session attributes. | | | Performance) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | FIPS flagger | Meets Federal Information | ALLBASE/SQL Reference Manual, DECLARE | | (Standards) | Processing Standard (FIPS) | CURSOR in "SQL Commands" and | | | 127.1 flagger support. Flags | "Standards Flagging Support" appendix; | | | non-standard statement or | ALLBASE/SQL Advanced Application | | | extension. Invoked with a | Programming Guide, "Flagging | | | flagger option in the | Non-Standard SQL with the FIPS | | | preprocessor command line or | Flagger;" ISQL Reference Manual for | | | the SET FLAGGER command in | ALLBASE/SQL and IMAGE/SQL, SET in | | | ISQL. Updatability rules are | "ISQL Commands." | | | different when flagger is | | | | invoked. New syntax: DECLARE | | | | CURSOR, WHENEVER. Changes to C | | | | and COBOL host variable | | | | declaration. | | | | | | ----------------------------------------------------------------------------------------------- Table Front-0. New Features in ALLBASE/SQL Release G.0 (cont.) ----------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in | | (Category) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Optimizer | Uses a more efficient | ALLBASE/SQL Performance and Monitoring | | enhancement | algorithm that significantly | Guidelines, "Optimization" in "Basic | | (Performance) | reduces the time to generate | Concepts in ALLBASE/SQL Performance." | | | the access plan. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Access plan | Allows modification of access | ALLBASE/SQL Reference Manual, SETOPT | | modification | plans for stored section to | in "SQL Statements;" ALLBASE/SQL | | (Performance) | optimize performance. View | Database Administration Guide, | | | the plan with | SYSTEM.SETOPINFO in "System Catalog." | | | SYSTEM.SETOPTINFO. New | | | | statement: SETOPT. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Syntax added to | Specifies that the | ALLBASE/SQL Reference Manual, VALIDATE | | disable access | optimization information in | in "SQL Statements; ISQL Reference | | plan optimization | the module file is not to be | Manual for ALLBASE/SQL and IMAGE/SQL," | | (Performance, | used. Changed syntax: | EXTRACT, INSTALL in "ISQL Commands." | | Usability) | EXTRACT, INSTALL, VALIDATE. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Application | Provides enhancements to | ALLBASE/SQL Reference Manual, "Names" | | Development | improve preprocessing | and "SQL Statements;" ALLBASE/SQL | | Concurrency | performance when | Advanced Application Programming | | (Performance, | simultaneously accessed by | Guide, "Using the Preprocessor;" ISQL | | Usability) | multiple users. Page or row | Reference Manual for ALLBASE/SQL and | | | level locking on any system | IMAGE/SQL, "ISQL Commands;" | | | base table and processing | ALLBASE/SQL Database Administration | | | without storing sections. See | Guide, "Database Creation and | | | the related features in this | Security" and "System Catalog." | | | table. | | | | New SQL parameter: SET | | | | DEFAULT DBEFileSet. SQL | | | | changed syntax: ALTER TABLE, | | | | GRANT, REVOKE, UPDATE | | | | STATISTICS. ISQL changed | | | | syntax: INSTALL. Changed | | | | SYSTEM and CATALOG view. New | | | | STOREDSECT tables. Special | | | | owners HPRDBSS and STOREDSECT. | | | | Changed syntax for Full | | | | Preprocessing Mode. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | System Catalog | Provides greater concurrency | ALLBASE/SQL Reference Manual, "Names;" | | tables | by allowing users to specify | ALLBASE/SQL Database Administration | | (Performance) | table, page, or row level | Guide, "System Catalog." | | | locking of any system table | | | | owned by STOREDSECT through | | | | the ALTER TABLE statement. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Preprocessors | Allows optional specification | ALLBASE/SQL Advanced Application | | (Performance) | of a DBEFileSet for storage of | Programming Guide, "Using the | | | sections. Allows | Preprocessor." | | | preprocessing without storing | | | | sections in DBEnvironment. | | | | | | ----------------------------------------------------------------------------------------------- Table Front-0. New Features in ALLBASE/SQL Release G.0 (cont.) ----------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in | | (Category) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | I/O performance | Optimizes I/O for initial | ALLBASE/SQL Reference Manual, "SQL | | improvement | load, index build, serial | Statements." | | (Performance) | scans, internal data | | | | restructuring, file activity, | | | | pseudo mapped files and | | | | temporary files. See the | | | | following features for new and | | | | changed syntax. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | TRUNCATE TABLE | Deletes all rows in a | ALLBASE/SQL Reference Manual, TRUNCATE | | statement | specified table leaving its | TABLE in "SQL Statements." | | (Performance) | structure intact. Indexes, | | | | views, default values, | | | | constraints, rules defined on | | | | the table, and all | | | | authorizations are retained. | | | | TRUNCATE TABLE is faster than | | | | the DELETE statement and | | | | generates fewer logs. New | | | | statement: TRUNCATE TABLE. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | New scans | Reads tables with a new | ALLBASE/SQL Performance and Monitoring | | (Performance) | parallel sequential scan. The | Guidelines, "Using Parallel Serial | | | tables are partitioned and | Scans" in "Guidelines on Query | | | files are read in a round | Design." | | | robin fashion to allow OS | | | | prefetch to be more effective. | | | | Allows the I/O for a serial | | | | scan to spread across multiple | | | | disc drives. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Load performance | Improves performance with new | ALLBASE/SQL Reference Manual, SET | | improvement | SET and SET SESSION | SESSION in "SQL Statements." | | (Performance) | attributes, a new binary | | | | search algorithm, and deferred | | | | allocation of HASH pages. New | | | | attributes for SET SESSION | | | | statement: FILL, PARALLEL | | | | FILL. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | ISQL enhanced to | Uses new parameters of the | ISQL Reference Manual for ALLBASE/SQL | | improve the | ISQL SET command to set load | and IMAGE/SQL, SET in "ISQL Commands." | | performance of | buffer size and message | | | LOADs | reporting. Improves load | | | (Performance) | performance. Choose a | | | | procedure, command file, or | | | | new ISQL command to set | | | | constraints deferred, lock | | | | table exclusively, and set row | | | | level DML atomicity. Changed | | | | syntax: SET (see the | | | | following feature). | | | | | | ----------------------------------------------------------------------------------------------- Table Front-0. New Features in ALLBASE/SQL Release G.0 (cont.) ----------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in | | (Category) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Modified SET | Provides better performance | ISQL Reference Manual for ALLBASE/SQL | | options | for LOADs and UNLOADs. | and IMAGE/SQL, SET in "ISQL Commands;" | | (Performance) | Specify buffer size, status | ALLBASE/SQL Performance and Monitoring | | | reporting for LOAD/UNLOAD or | Guidelines, "Initial Table Loads" in | | | exclusive lock for data table. | "Guidelines on Logical and Physical | | | AUTOSAVE row limit increased | Design." | | | to 2147483647. New and | | | | changed SET options: | | | | LOAD_BUFFER, LOAD_ECHO, | | | | AUTOLOCK, AUTOSAVE. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | SQLMON | Monitors the activity of | ALLBASE/SQL Performance and Monitoring | | (Tools) | ALLBASE/SQL DBEnvironment. | Guidelines, chapters 6-9. | | | Provides information on file | | | | capacity, locking, I/O, | | | | logging, tables, and indexes. | | | | Summarizes activity for entire | | | | DBEnvironment or focuses on | | | | individual sessions, programs, | | | | or database components. | | | | Provides read-only | | | | information. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Audit | Provides a series of features | ALLBASE/SQL Reference Manual, "SQL | | (Tools) | to set up an audit | Statements;" ALLBASE/SQL Database | | | DBEnvironment which generates | Administration Guide, "DBEnvironment | | | audit log records that you can | Configuration and Security" chapter | | | analyze with the new SQLAudit | and "SQLAudit" appendix. | | | utility for security or | | | | administration. Includes the | | | | ability to set up partitions. | | | | See ALLBASE/SQL Database | | | | Administration Guide for | | | | SQLAudit commands. Modified | | | | statements: ALTER TABLE, | | | | CREATE TABLE, START DBE NEW, | | | | START DBE NEWLOG. New | | | | statements: CREATE PARTITION, | | | | DROP PARTITION, DISABLE AUDIT | | | | LOGGING, ENABLE AUDIT LOGGING, | | | | LOG COMMENT. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Wrapper | Creates a DBEnvironment to | ALLBASE/SQL Reference Manual, "Wrapper | | DBEnvironments | wrap around the log files | DBEnvironments" in "Using | | (Tools) | orphaned after a hard crash of | ALLBASE/SQL;" ALLBASE/SQL Database | | | DBEnvironment. New SQLUtil | Administration Guide, WRAPDBE in | | | command: WRAPDBE. | "SQLUtil." | | | | | ----------------------------------------------------------------------------------------------- | | | | | HP PC API is now | PC API is an application | HP PC API User's Guide for ALLBASE/SQL | | bundled with | programming interface that | and IMAGE/SQL. | | ALLBASE/SQL. | allows tools written with | | | | either the GUPTA or the ODBC | | | | interface to access | | | | ALLBASE/SQL and IMAGE/SQL from | | | | a PC. | | | | | | ----------------------------------------------------------------------------------------------- Table Front-0. New Features in ALLBASE/SQL Release G.0 (cont.) ----------------------------------------------------------------------------------------------- | | | | | Feature | Description | Documented in | | (Category) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Increased memory | Increases memory up to 50,000 | ALLBASE/SQL Reference Manual, | | for MPE/iX (HP-UX | data buffer pages and 2,000 | STARTDBE, STARTDBE NEW, and START DBE | | shared memory | run time control block pages. | NEWLOG in "SQL Statements;" | | allocation is | Increases the limits | ALLBASE/SQL Database Administration | | unchanged) | significantly allowing | Guide, "ALLBASE/SQL Limits" appendix. | | (Performance) | allocation of enough data | | | | buffer pages to keep the | | | | entire DBEnvironment in memory | | | | if desired for performance. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | ALLBASE/NET | Improves performance of | ALLBASE/NET User's Guide, "Setting up | | enhancements | ALLBASE/NET, allows more | ALLBASE/NET." | | (Connectivity, | client connections on server | | | Performance) | system, and reduces number of | | | | programs on MPE/iX. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | ALLBASE/NET | Adds option ARPA. Adds option | ALLBASE/NET User's Guide, "Setting up | | commands and | NUMSERVERS to check status of | ALLBASE/NET" and "NETUtil Reference." | | options for | listeners and number of | | | MPE/iX | network connections. Changed | | | (Connectivity, | syntax: ANSTART, ANSTAT, | | | Usability) | ANSTOP. Changed NETUtil | | | | commands: ADD ALIAS, CHANGE | | | | ALIAS. | | | | | | ----------------------------------------------------------------------------------------------- | | | | | ALLBASE/NET and | ALLBASE/NET listener for | ALLBASE/NET User's Guide, "Setting up | | NetWare | NetWare now works with the | ALLBASE/NET." | | (Connectivity) | 3.11 version of Novell's | | | | NetWare for UNIX (HP | | | | NetWare/iX). | | | | | | ----------------------------------------------------------------------------------------------- | | | | | Changed | Adds SM or AM (in the | ALLBASE/NET User's Guide, "Setting up | | restrictions for | specified account) to | ALLBASE/NET." | | executing NETUtil | MANAGER.SYS for adding, | | | commands for | changing, or deleting users | | | MPE/iX | for MPE/iX. | | | (Connectivity, | | | | Usability) | | | | | | | ----------------------------------------------------------------------------------------------- | | | | | ARPA is only | Remote database access | ALLBASE/NET User's Guide, "Setting up | | TCP/IP interface | applications that specify NS | ALLBASE/NET" and "NETUtil Reference." | | for data | will not work if the client | | | communication | and/or server machine is an HP | | | through | 9000 Series 700/800 running | | | ALLBASE/NET | HP-UX 10.0 or greater. Server | | | beginning with | Node Name entry must be | | | HP-UX 10.0 | changed from NS node name to | | | (Connectivity) | ARPA host name. For the | | | | NETUsers file, the "Client | | | | Node Name" must be changed | | | | from the NS node name to the | | | | ARPA host name. New NETUtil | | | | commands: MIGRATE USER, | | | | MIGRATE ALIAS. | | | | | | ----------------------------------------------------------------------------------------------- Conventions UPPERCASE In a syntax statement, commands and keywords are shown in uppercase characters. The characters must be entered in the order shown; however, you can enter the characters in either uppercase or lowercase. For example: COMMAND can be entered as any of the following: command Command COMMAND It cannot, however, be entered as: comm com_mand comamnd italics In a syntax statement or an example, a word in italics represents a parameter or argument that you must replace with the actual value. In the following example, you must replace filename with the name of the file: COMMAND filename punctuation In a syntax statement, punctuation characters (other than brackets, braces, vertical bars, and ellipses) must be entered exactly as shown. In the following example, the parentheses and colon must be entered: (filename):(filename) underlining Within an example that contains interactive dialog, user input and user responses to prompts are indicated by underlining. In the following example, yes is the user's response to the prompt: Do you want to continue? >> yes { } In a syntax statement, braces enclose required elements. When several elements are stacked within braces, you must select one. In the following example, you must select either ON or OFF: COMMAND { ON } { OFF} [ ] In a syntax statement, brackets enclose optional elements. In the following example, OPTION can be omitted: COMMAND filename [OPTION] When several elements are stacked within brackets, you can select one or none of the elements. In the following example, you can select OPTION or parameter or neither. The elements cannot be repeated. COMMAND filename [ OPTION ] [ parameter] Conventions (continued) [...] In a syntax statement, horizontal ellipses enclosed in brackets indicate that you can repeatedly select the element(s) that appear within the immediately preceding pair of brackets or braces. In the example below, you can select parameter zero or more times. Each instance of parameter must be preceded by a comma: [,parameter][...] In the example below, you only use the comma as a delimiter if parameter is repeated; no comma is used before the first occurrence of parameter: [parameter][,...] |...| In a syntax statement, horizontal ellipses enclosed in vertical bars indicate that you can select more than one element within the immediately preceding pair of brackets or braces. However, each particular element can only be selected once. In the following example, you must select A, AB, BA, or B. The elements cannot be repeated. { A} |...| { B} ... In an example, horizontal or vertical ellipses indicate where portions of an example have been omitted. In a syntax statement, the space symbol shows a required blank. In the following example, parameter and parameter must be separated with a blank: (parameter) (parameter) The symbol indicates a key on the keyboard. For example, RETURN represents the carriage return key or Shift represents the shift key. CTRLC CTRL followed by an uppercase character indicates a control character. For example, CTRLY means that you press the control key and the Y key simultaneously.