ALLBASE/SQL Reference Manual HP 3000 MPE/iX Computer Systems Manufacturing Part Number: 36216-90216 E0300 U.S.A.
Notice 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.
Contents 1. Introduction ALLBASE/SQL Components . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Utility Programs . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . ALLBASE/SQL Databases. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Logical Concepts . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Grantable Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .76 Ownership. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .77 Default Owner Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .78 Ownership Privileges . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents EXISTS Predicate . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Correlated Versus Noncorrelated Subqueries . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Outer Joins . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Using GENPLAN to Display the Access Plan . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Read Committed (RC) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .175 Read Uncommitted (RU) . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .176 Details of Locking . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .177 Lock Granularities . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Overflow and Truncation . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Underflow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Type Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Null Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .244 SQL Syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .244 Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .244 Examples. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Description. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . EXISTS Predicate. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents ADVANCE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .297 Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .297 SQL Syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .297 Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Authorization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . BEGIN DECLARE SECTION . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQL Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents SQL Syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .327 Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .327 Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .328 Authorization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Authorization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . CREATE SCHEMA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .376 Authorization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .377 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .377 DELETE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . DROP DBEFILESET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQL Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .405 DROP TABLE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .406 Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .406 SQL Syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Authorization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . EXECUTE IMMEDIATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Parameters — Grant RUN or EXECUTE Authority . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .438 Authorization — Grant RUN or EXECUTE Authority . . . . . . . . . . . . . . . . . . . . . . . . . . . .438 SQL Syntax — Grant CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority . 438 Parameters — Grant CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority . . 438 Description — Grant CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority . .
Contents Examples . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Labeled Statement . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . SQL Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .476 SQL Syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .476 Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .476 Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents SQL Syntax . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487 Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487 Description. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 487 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents HostVariableSpecification — Without BULK Option. . . . . . . . . . . . . . . . . . . . . . . . . . . . . .500 FromSpec . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .500 TableSpec . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .500 SQL Syntax — Select Statement Level. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Description. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Authorization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Scope . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .550 SQL Syntax. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .550 Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .550 Description . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Parameters . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Description. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Authorization. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Description — LongColumnIOString . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .590 Authorization . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .591 Example . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .591 VALIDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents CREATE SCHEMA . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . CREATE TABLE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . CREATE TEMPSPACE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . CREATE VIEW . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents RENAME TABLE. . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .610 RESET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .610 RETURN . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .611 REVOKE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents LIST HISTORY . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . LIST INSTALL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . LIST SET . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . LOAD . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Contents Non-Standard Syntax Rules . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . .
Figures Figure 1-1. . Components of ALLBASE/SQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 42 Figure 1-2. . How Tables, DBEFiles, and DBEFileSets Are Related . . . . . . . . . . . . . . . . . 46 Figure 1-3. . Databases and DBEFileSets . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 46 Figure 1-4. . Elements of an ALLBASE/SQL DBEnvironment . . . . . . . . . . . . . . . . . . . . . 47 Figure 3-1. . Range of Complex Query Types. . . . . . . . . . . . .
Figures 32
Tables 33
Tables 34
Preface This manual contains basic information about ALLBASE/SQL as well as in-depth information about ALLBASE/SQL data types and statements. The first three chapters are for all readers, including new users of ALLBASE/SQL. The remaining chapters are for experienced SQL users and SQL application programmers. The titles of the chapters are as follows: • Chapter 1 , “Introduction,” presents the components of ALLBASE/SQL and introduces fundamental ALLBASE/SQL concepts and terms.
ALLBASE Manuals The following is a list of the documentation titles for this MPE release of ALLBASE.
New Features in G1, G2 and G3 The following table highlights the new or changed functionality added in G1, G2 and G3 releases, and shows you where each feature is documented. Ver. Feature (Category) Description Documented in... G3 String Functions (Usability) The supported SQL syntax has been enhanced to include the following string manipulation functions: UPPER, LOWER, POSITION, INSTR, TRIM, LTRIM, AND RTRIM.
Ver. Feature (Category) Description Documented in... G1 RENAME Column or Table (Usability) Adds capability of defining a new name for an existing table or column in a DBEnvironment. You cannot rename a table or column that has check constraints or an IMAGE/SQL table. New commands: RENAME COLUMN, RENAME TABLE. ALLBASE/SQL Reference Manual, RENAME COLUMN and RENAME TABLE in “SQL Statements.
Ver. Feature (Category) Description Documented in... G1 Terminate a query (Usability, Performance) Allows termination of a query for a connection or transaction. New statement: TERMINATE QUERY. New syntax for SET SESSION, SET TRANSACTION. ALLBASE/SQL Reference Manual, TERMINATE QUERY, SET SESSION, SET TRANSACTION in “SQL Statements.” G1 Terminate a transaction (Usability, Performance) Allows stopping of a given transaction. New statement: TERMINATE TRANSACTION.
Ver. Feature (Category) Description Documented in... G1 POSIX support (Tools) Starting with G1, the ALLBASE/SQL preprocessor (PSQLCOB) supports preprocessing and generation of Microfocus COBOL source code under POSIX (Portable Operating system Interface). Communicator 3000 MPE/iX Release 5.5 (Non-Platform Software Release (C.55.00), “ALLBASE/SQL Enhancements.” G1 Terminate a user’s connections (Connectivity) Terminates one or more connections for a user.
Introduction 1 Introduction This manual describes ALLBASE/SQL, which you use to create, maintain, and access relational database environments. SQL stands for Structured Query Language, a language for accessing a relational database.
Introduction ALLBASE/SQL Components ALLBASE/SQL Components ALLBASE/SQL consists of several distinct components, which are shown in Figure 1-1.. Figure 1-1. Components of ALLBASE/SQL To access data with ALLBASE/SQL, you use ALLBASE/SQL statements, which conform to industry standards for SQL statements for relational databases. You can submit SQL statements interactively or in application programs as described here: • Interactively, you use ISQL (Interactive SQL) to key in statements at a terminal.
Introduction ALLBASE/SQL Components Utility Programs In addition, these utility programs help you perform the necessary maintenance tasks: • SQLUtil assists with file maintenance, backup, and recovery. • SQLGEN generates statements for re-creating a given DBEnvironment. • SQLMigrate lets you move DBEnvironments between releases of ALLBASE/SQL. • SQLCheck checks the integrity of a DBEnvironment. • SQLMON helps you monitor DBEnvironment performance.
Introduction ALLBASE/SQL Databases ALLBASE/SQL Databases The largest unit in ALLBASE/SQL is the DBEnvironment, which can be seen logically as a collection of database objects or physically as a group of files. Objects are database structures. Logical Concepts Logically, the DBEnvironment is a structure which contains one or more relational databases. In ALLBASE/SQL, a database is a set of tables, views, and other objects that have the same owner. The data in a relational database is organized in tables.
Introduction ALLBASE/SQL Databases created implicitly when you create objects that have a class name as owner name. Refer to Chapter 2 , “Using ALLBASE/SQL,” in this manual and to the chapter “Logical Design” in the ALLBASE/SQL Database Administration Guide for additional information about authorization groups and classes. To use data in a database, you need to specify the names of the tables and views you need.
Introduction ALLBASE/SQL Databases Figure 1-2. How Tables, DBEFiles, and DBEFileSets Are Related A DBEFileSet specifies the files that contain data for one or more tables associated with the DBEFileSet. These tables do not have to be in the same database. Figure 1-3. illustrates that, while a DBEFileSet can contain data for all the tables in a database, a DBEFileSet can also contain data for some of the tables in a database, or for tables in more than one database.
Introduction ALLBASE/SQL Databases A DBEnvironment, illustrated in Figure 1-4., houses the DBEFiles for one or more ALLBASE/SQL databases, plus the following, which contain information for all databases in the DBEnvironment: • A DBECon file. This file contains information about the DBEnvironment configuration, such as the size of various buffers and other startup parameters. The name of the DBECon file is the same as the name of the DBEnvironment. • A system catalog.
Introduction ALLBASE/SQL Data Access ALLBASE/SQL Data Access The DBEnvironment determines both what data can be accessed in a transaction and what data can be recovered. Following a failure, a transaction can be recovered, or all data can be recovered, as follows: • A transaction is one or more SQL statements that together perform a unit of work on one or more databases in a DBEnvironment. Work done within a transaction can be made permanent (committed) or undone (rolled back).
Introduction Using Queries Using Queries After connecting to a DBEnvironment, you use queries to retrieve data from database tables. A query is a statement in which you describe the data you want to retrieve. In ALLBASE/SQL, a query is performed by using the SELECT statement. For example: SELECT FROM WHERE OR PartName, SalesPrice PurchDB.Parts PartNumber = '1123-P-01' PartNumber = '1133-P-01' The result of a query is called a query result.
Introduction ALLBASE/SQL Objects ALLBASE/SQL Objects The following structures play a significant role in the use of an ALLBASE/SQL database and are known as database objects: • Tables • Views • Columns (in tables and views) • Authorization groups • Indexes (on tables) • Hash structures (for tables) • Constraints • Rules (on tables) • Procedures • DBEFiles • DBEFileSets • TempSpaces • Modules Many of the SQL statements let you create and then create and manipulate objects as described below: • Data in table
Introduction ALLBASE/SQL Users ALLBASE/SQL Users ALLBASE/SQL users fall into the three categories as described here. One person may do all the tasks within these categories. • Application programmers. These users write application programs that access ALLBASE/SQL databases. They embed SQL statements in source code to manipulate data. Programmers then use the preprocessor that supports their programming language.
Introduction SQL Language Structure SQL Language Structure SQL statements begin with a verb and can include clauses or names. For example: SELECT PartNumber | | | | | | | | statement | verb | | column name FROM PurchDB.Parts | | | | | owner | | | name | | | | | | table | | name | | | +----------------+ | | FROM clause Statements always contain a verb, one or more words that describe the action of the statement. A statement can also contain one or more clauses.
Introduction Using Comments within SQL Statements Using Comments within SQL Statements You can initiate comments within any SQL statement or ISQL prompt either by prefixing each line of the comment with two hyphens or with the combination of slashes and asterisks at the beginning and end of the comments: SELECT FROM WHERE AND * PurchDB.SupplyPrice PartNumber = '1723-AD-01' DeliveryDays < 30 --This statement selects values from the SupplyPrice table based on --part number and delivery days.
Introduction SQL Statement Categories SQL Statement Categories Writing queries is the basis of data manipulation in ALLBASE/SQL. All users employ the SELECT statement for this purpose. SQL has several other general-purpose statements, and also has statements specifically for use by application programmers or database administrators. The SQL statements are functionally summarized inTable 1-1. For the commands in each category, refer to Table 10-1., “SQL Statement Summary.” Table 1-1.
Introduction SQL Statement Categories Table 1-1. SQL Statement Categories Group Database administration statements Procedure statements Category Purpose Authorization Statements for controlling DBEnvironment access. DBEnvironment configuration and use Statements for controlling DBEnvironments. Space management Statements for managing DBEFiles used for tables and indexes; statements for managing temporary space for sorting. Logging Statements for managing log files.
Introduction Error Conditions in ALLBASE/SQL Error Conditions in ALLBASE/SQL When you issue an SQL statement, error messages are returned if the statement cannot be carried out as intended. In an interactive session with ISQL, the messages are displayed on your terminal. In application programs, you access the message buffer directly by using the SQLEXPLAIN statement.
Introduction Native Language Support Only DML statements can be checked for errors at the row level of atomicity. Refer to the SET DML ATOMICITY statement in Chapter 12 , “SQL Statements S - Z,” for complete details. Deferring Error Checking beyond the Statement Level Sometimes statement level atomicity is too narrow for your needs. For operations involving more than one table, it may be useful to defer error checking until all tables are updated.
Introduction Native Language Support You can use native language characters in a wide variety of places, including these: • Character literals • Values stored in host variables for CHAR or VARCHAR data (but not as variable names) • ALLBASE/SQL object names If your system has the proper message files installed, ALLBASE/SQL displays prompts, messages and banners in the language you select; and it displays system dates and time according to local customs.
Using ALLBASE/SQL 2 Using ALLBASE/SQL This chapter shows how to use SQL statements for the following basic tasks: • Creating DBEnvironments • Starting and Terminating a DBE Session • Creating Physical Storage • Defining How Data is Stored and Retrieved • Understanding Data Access Paths • Controlling Database Access • Manipulating Data • Managing Transactions • Auditing DBEnvironments (including setting up partitions) • Using Wrapper DBEnvironments • Using SQLAudit • Application Programming • Using Multiple
Using ALLBASE/SQL Creating DBEnvironments Creating DBEnvironments Before you can create a database, you must first configure a DBEnvironment. You use the START DBE NEW statement, optionally specifying startup parameters to override those assigned by default.
Using ALLBASE/SQL Creating DBEnvironments If you want to specify the name of the DBEnvironment in a native language, then the native language you specify in the LANG clause must be covered by the same character set as the language designated as the current language at the operating system level. The current language can be different from that of the DBEnvironment.
Using ALLBASE/SQL Starting and Terminating a DBE Session Starting and Terminating a DBE Session A DBE session is the period between establishing and terminating a connection to a DBEnvironment by a user or a program. You must be in a DBE session to execute any of the SQL statements except the START DBE or CONNECT statements. You can establish either a single-user DBE session or a multiuser DBE session for a DBEnvironment.
Using ALLBASE/SQL Creating Physical Storage Creating Physical Storage To create physical storage, you use data definition statements to create the following storage areas: • DBEFileSets • DBEFiles • TempSpace File space for tables and indexes is managed by adding and dropping DBEFiles from DBEFileSets. DBEFiles are units of physical storage and DBEFileSets are logical collections of DBEFiles.
Using ALLBASE/SQL Defining How Data is Stored and Retrieved Defining How Data is Stored and Retrieved To create database objects, you use data definition statements to define the following: • Tables • Views • Indexes • Constraints • Procedures • Rules Creating a Table When you define a table, use the CREATE TABLE statement to accomplish the following tasks: 1. Establish an automatic locking mode and default access authorities. 2. Name the table. 3. Describe the columns. 4.
Using ALLBASE/SQL Defining How Data is Stored and Retrieved • PUBLIC mode allows multiple transactions to concurrently read and update a table. Locking is done at the page level. • PUBLICROW mode allows multiple transactions to concurrently read and update a table. Locking is done at the row level, which permits greater concurrency than PUBLIC mode. ALLBASE/SQL automatically uses the locking mode in the table definition whenever you access a table.
Using ALLBASE/SQL Defining How Data is Stored and Retrieved • Character string data types: CHAR(n) VARCHAR(n) • Date/time data types: DATE TIME DATETIME INTERVAL • Binary string data types: BINARY(n) VARBINARY(n) LONG BINARY(n) LONG VARBINARY(n) When you define a column to be of a certain data type, ALLBASE/SQL ensures that each value stored in the column is in the range for the data type.
Using ALLBASE/SQL Defining How Data is Stored and Retrieved You cannot use the DEFAULT option for a LONG data type column. Specifying a DBEFileSet The table rows are stored in the DBEFiles previously associated with the DBEFileSet named in the IN clause of the CREATE TABLE statement. If you do not specify a DBEFileSet, rows for the table are stored in the SYSTEM DBEFileSet. For best performance, explicitly specify a DBEFileSet other than the SYSTEM DBEFileSet.
Using ALLBASE/SQL Defining How Data is Stored and Retrieved 4. Specification of WITH CHECK OPTION, if desired The following example contains numbers that refer to the view components listed above: CREATE VIEW AS SELECT FROM WHERE 1 | HiPrice (PartNum, Price) PartNumber, SalesPrice PurchDB.Parts SalesPrice > 1000 --2 --|--3 --- View names are governed by the same rules as table names.
Using ALLBASE/SQL Defining How Data is Stored and Retrieved PartsDBE: CREATE PROCEDURE PurchDB.DelSupply(Part CHAR(16) NOT NULL) AS BEGIN DELETE FROM PurchDB.SupplyPrice WHERE PartNumber = :Part; END The procedure definition includes a parameter declaration. The parameter Part accepts a value into the procedure at run time. You execute the procedure with a statement like the following example: EXECUTE PROCEDURE PurchDB.
Using ALLBASE/SQL Understanding Data Access Paths Understanding Data Access Paths In creating a database, you must consider not only the arrangement of data, but also the ways in which the data will be accessed during data manipulation operations. The four following access methods are supported directly by ALLBASE/SQL: • Serial access • Indexed access • Hashed access • TID access For indexed access, you must create a named index, or unique or referential constraint on a table.
Using ALLBASE/SQL Understanding Data Access Paths more of the columns in the query. If an index is available and if the optimizer decides that using the index is the fastest way to access the data, ALLBASE/SQL looks up the key values in the index first, then goes directly to the pages containing table data. For example, in the following query, assume that PurchDB.Parts contains a large number of rows and that a unique index exists on the PartNumber column: isql=> SELECT PartName, SalesPrice FROM PurchDB.
Using ALLBASE/SQL Understanding Data Access Paths predicate only if the comparison operator is =, >, >=, <, or <= . For example: WHERE SupplyPrice = :SupplyPrice — WHERE Column1 BETWEEN (Column2 or Constant or HostVariable) AND (Column2 or Constant or HostVariable). For example: WHERE OrderNumber BETWEEN '1123-P-01' AND '1243-MU-01' • Some queries which use the MIN or MAX aggregate function on an indexed column as follows are optimizable: — MIN/MAX column is the first column of a nonhashed index.
Using ALLBASE/SQL Understanding Data Access Paths uniqueness; duplicate values cannot exist in the hash key column(s). A well-chosen hash key, like a good index key, provides the optimizer with the choice of a potentially faster data access method than a serial scan. Create a hash structure at the time you create a table. In addition to the components of a table definition, a hash structure definition includes: 1. Columns that define the hash key 2.
Using ALLBASE/SQL Understanding Data Access Paths Hash structures operate like unique indexes; that is, they enforce the uniqueness of each key in the table. If you attempt to insert a duplicate key, ALLBASE/SQL will return an error message. Differences between Hashed and Indexed Access Hashing may provide faster access than B-tree lookups for many types of common queries, and it does not require the overhead of additional file space required by B-tree indexes.
Using ALLBASE/SQL Controlling Database Access Controlling Database Access ALLBASE/SQL uses authorities to determine who can issue which SQL statements and who can execute programs that access databases in a DBEnvironment. For complete details about security schemes refer to the ALLBASE/SQL Database Administration Guide.
Using ALLBASE/SQL Controlling Database Access DBA Authority When a DBEnvironment is configured, DBA authority is automatically given to the login name of the DBECreator. A user with DBA authority (also referred to as the DBA) has extensive control over data in a DBEnvironment. The DBA can issue almost all the SQL statements and execute all the programs that access the DBEnvironment. The two SQL statements that only a DBECreator can issue are, START DBE NEWLOG and START DBE RECOVER.
Using ALLBASE/SQL Controlling Database Access grantee is a group. The following statement grants UPDATE authority to Amanda, who can then grant that authority to individual users or a class: GRANT UPDATE ON Marketing.Forecast TO AMANDA@DBMS WITH GRANT OPTION; Users with a grantable privilege can only revoke privileges they have granted and chains they have caused.
Using ALLBASE/SQL Controlling Database Access named Salary to that group when creating the table: CREATE GROUP Managers CREATE TABLE Managers.Salary... When you refer in an SQL statement to a table, a view, a module, or an authorization group, you specify both the owner's name and the name of the object. If you own the object, however, you can omit the owner's name. When WOLFGANG@DBMS retrieves information from the Parts table, for example, he must specify the owner name.
Using ALLBASE/SQL Controlling Database Access • Group members have ownership privileges over all objects owned by their group. • Group members have all privileges granted to the group. • Table owners can add columns to the table or drop the table. They can add and drop constraints. They can create and drop indexes for the table. They can grant and revoke authorities for the table, and transfer their ownership to another owner.
Using ALLBASE/SQL Controlling Database Access Classes A class is a special category of owner that is neither a conventional DBEUserID nor a group. You may wish to assign ownership of objects to a class when you do not want any individual or group to have automatic access to them. With class ownership, the DBA controls all authorities, because objects that belong to a class can be created and maintained only by the DBA.
Using ALLBASE/SQL Manipulating Data Manipulating Data Most users of ALLBASE/SQL are primarily interested in manipulating data in DBEnvironments. Data manipulation consists of following operations: • Selecting data • Inserting data into tables • Updating rows in tables • Deleting rows In order to select data, you create queries, which are fully described in the next chapter. The other types of data manipulation are presented briefly in the next sections.
Using ALLBASE/SQL Manipulating Data You can copy rows from one or more tables or views into another table by using a form of the INSERT statement (often called a type 2 Insert) in which you specify the following items: 1. A table or view name 2. A SELECT statement Note that the numbers in the next example refer to the items listed above: 1 | INSERT INTO PurchDB.Drives SELECT * FROM PurchDB.
Using ALLBASE/SQL Managing Transactions The following example illustrates the DELETE statement and its two components: DELETE FROM PurchDB.Parts WHERE PartNumber = '9999-AJ' --1 --2 Only a single table name or view name can be specified. Only certain views can be used to delete rows, as described under “Updatability of Queries” in Chapter 3 , “SQL Queries.” The WHERE clause is optional. You omit it if you want to delete all the rows in a table or view.
Using ALLBASE/SQL Managing Transactions database is left in an inconsistent state. To enforce data integrity, the three inserts are contained in a single transaction. If any one insert fails, then the entire transaction fails and none of the other inserts takes effect. The following example shows how this transaction might be coded: BEGIN WORK INSERT INTO PurchDB.Parts ... If the insert into PurchDB.Parts fails then ROLLBACK else INSERT INTO PurchDB.SupplyPrice ... If the insert into PurchDB.
Using ALLBASE/SQL Managing Transactions Starting Transactions A transaction is initiated with either an implicit or explicit BEGIN WORK statement.
Using ALLBASE/SQL Managing Transactions Using ROLLBACK WORK The ROLLBACK WORK statement ends the transaction and undoes all data modifications made since the BEGIN WORK statement, unless it references a savepoint. (See the discussion of savepoints in the following section.) The ROLLBACK WORK statement is issued automatically by ALLBASE/SQL under the following conditions: • A non-archive log file becomes full. • A RELEASE statement is issued before the end of the transaction. • A system failure occurs.
Using ALLBASE/SQL Managing Transactions After a rollback to a savepoint has been executed, use the COMMIT WORK statement to make the changes that were not rolled back permanent. If you want to rollback the entire transaction, issue the ROLLBACK statement without a savepoint. Savepoints are suitable for transactions that perform several operations, any of which may need to be rolled back. In the following example, a travel agency is booking tour reservations for 15 people.
Using ALLBASE/SQL Managing Transactions statement issued within a transaction has no effect on the present transaction, instead it takes effect for the next transaction and remains in effect for the duration of the session, unless reset by a subsequent BEGIN WORK, SET TRANSACTION, SET CONSTRAINTS, SET DML ATOMICITY, or SET SESSION statement.
Using ALLBASE/SQL Managing Transactions selected has been committed to the database. You know that the default isolation level for a session is RR, but RR does not provide the concurrency you need. At the beginning of the session, you set the isolation level to RC (read committed) for all transactions in the session, as follows: . . . SET SESSION ISOLATION LEVEL RC . . . Note that each transaction starts implicitly. In this example, there is no need for any BEGIN WORK statements.
Using ALLBASE/SQL Managing Transactions aborted. The timeout action can also be set to abort the command being processed instead of the entire transaction. Set the timeout limit for the DBEnvironment with the STARTDBE statement or the SQLUtil ALTDBE command. To specify a timeout limit for a particular session, use the SET USER TIMEOUT statement. Both SET SESSION and SET TRANSACTION have parameters to specify which action the system should take when a timer expires.
Using ALLBASE/SQL Auditing DBEnvironments Careful use of savepoints can decrease the amount of time locks are held, and reduces the need to resubmit transactions because part of a transaction was unsuccessful. Set the maximum number of transactions (MaxTransactions) and timeout limit parameters correctly. If MaxTransactions is too low, transactions will wait for no reason. However, the overall throughput of the DBEnvironment may be reduced if MaxTransactions is too high.
Using ALLBASE/SQL Using Wrapper DBEnvironments Using Wrapper DBEnvironments A wrapper DBEnvironment is a DBEnvironment created to wrap around the log files orphaned after a hard crash of a DBEnvironment. Wrapping log files means associating the files with a wrapper DBEnvironment. After a DBEnvironment becomes inaccessible, its log files are not associated with any DBEnvironment. These orphaned log files are then also inaccessible.
Using ALLBASE/SQL Application Programming Application Programming To use SQL statements in an application program, you embed the statements in source code, then use the ALLBASE/SQL preprocessor that supports the source language. Preprocessor The ALLBASE/SQL preprocessor performs the following tasks: • Checks the syntax of SQL statements embedded in an application program.
Using ALLBASE/SQL Application Programming Authorization ALLBASE/SQL authorization governs who can preprocess and execute a program that accesses a DBEnvironment as described here: • To preprocess a program, you need DBA or CONNECT authority and the authorities needed to execute all activities against the database that are executed by the program. The module stored for the program is owned by the login name of the individual who invokes the preprocessor.
Using ALLBASE/SQL Using Multiple Connections and Transactions with Timeouts :PartNameInd When host variables are used in an application outside of an embedded SQL statement, the host variable name is not prefixed by a colon. Multiple-Row Manipulations Programmatic SELECTs and INSERTs can operate only on a row at a time unless you use a cursor or the BULK option of the SELECT, INSERT, or FETCH statement. A cursor is a pointer that you advance one row at a time.
Using ALLBASE/SQL Using Multiple Connections and Transactions with Timeouts Connecting to DBEnvironments With multi-connect functionality, you can specify a connection name each time you connect to a DBEnvironment by means of one of the following statements: • CONNECT • START DBE • START DBE NEW • START DBE NEWLOG For example, in ISQL, the following CONNECT statement establishes a connection to PartsDBE and assigns a connection name for this connection: isql=> CONNECT TO 'PartsDBE' AS 'Parts1'; In an appl
Using ALLBASE/SQL Using Multiple Connections and Transactions with Timeouts Setting Timeout Values Be sure to set a timeout value when using multiple connections to avoid undetected deadlocks and undetected wait conditions. An undetected deadlock is possible when multi-transaction mode is used in conjunction with more than one DBEnvironment with multiple applications accessing the same DBEnvironments at the same time.
Using ALLBASE/SQL Using Multiple Connections and Transactions with Timeouts The following general example shows how to set user timeout values: 1. Put multi-transaction mode in effect. SET MULTITRANSACTION ON 2. Connect to the PartsDBE DBEnvironment. CONNECT TO 'PartsDBE' AS 'Parts1' 3. Set the timeout value for the PartsDBE connection to an appropriate number of seconds. In this case, the application will wait five minutes for system resources when accessing the PartsDBE DBEnvironment.
Using ALLBASE/SQL Using Multiple Connections and Transactions with Timeouts . . . If DBERR 2825 is returned, the transaction has timed out, and you must take appropriate action. Further discussion of timeout functionality is provided in the ALLBASE/SQL Advanced Application Programming Guide. Setting the Transaction Mode The SET MULTITRANSACTION statement allows you to switch between single-transaction mode and multi-transaction mode.
Using ALLBASE/SQL Using Multiple Connections and Transactions with Timeouts 5. End the PartsDBE transaction. isql=> COMMIT WORK; 6. Set the current connection to Sales1. isql=> SET CONNECTION 'Sales1'; 7. Begin a transaction for SalesDBE. isql=> BEGIN WORK RC; isql=> SELECT PartNumber, Sales > FROM Owner.Sales > WHERE PartNumber = '1123-P-20'; . . . 8. End the SalesDBE transaction.
Using ALLBASE/SQL Using Multiple Connections and Transactions with Timeouts 4. Begin a transaction for PartsDBE. BEGIN WORK RC OPEN PartsCursor BULK FETCH INTO PartsCursor :PartsArray, :StartIndex, :NumberOfRows 5. If there are qualifying rows, set the current connection to Parts2. SET CONNECTION 'Parts2' 6. Begin a transaction for Parts2DBE. BEGIN WORK RC At this point, there are two active transactions. BULK INSERT INTO VALUES PurchDB2.Orders2 (:PartsArray, :StartIndex, :NumberOfRows) 7.
Using ALLBASE/SQL Using Multiple Connections and Transactions with Timeouts attempted. From a menu, the user chooses to view account information and specifies an account number. Before giving this information, the application logs the fact that the user is requesting it. The following pseudocode example illustrates how you might code two simultaneous transactions, each one accessing BankDBE using host variables: 1. Put multi-transaction mode in effect.
Using ALLBASE/SQL Using Multiple Connections and Transactions with Timeouts Set the current connection to Bank1. SET CONNECTION 'Bank1' 5. Return from the subroutine to complete the open transaction: . . . OPEN BankCursor BULK FETCH INTO . . . BankCursor :BankArray, :StartIndex, :NumberOfRows Disconnecting from DBEnvironments The DISCONNECT statement provides a means of closing one or all active connections within an application.
Using ALLBASE/SQL Using Multiple Connections and Transactions with Timeouts 4. End the transaction that was initiated for the Parts1 connection and terminate the connection. COMMIT WORK DISCONNECT 'Parts1' 5. Set the current connection to 'Sales1'. SET CONNECTION 'Sales1' 6. Begin transaction for SalesDBE. BEGIN WORK RC . . . 7. Set the current connection to Accounting1. SET CONNECTION 'Accounting1' 8. Begin transaction for Accounting1. BEGIN WORK RC . . . 9.
Using ALLBASE/SQL Administering a Database Administering a Database Activities that protect and maintain a DBEnvironment and its databases are collectively referred to as database administration.
Using ALLBASE/SQL Understanding the System Catalog definitions are stored in the system catalog. As database activities occur, most of the information in the catalog is updated automatically, so the system catalog provides an up-to-date source of information on a DBEnvironment. Immediately following an UPDATE STATISTICS statement, the views in the system catalog, summarized in Table 2-2, are a source of up-to-date information on a DBEnvironment and the structure and use of its databases.
Using ALLBASE/SQL Understanding the System Catalog Table 2-2. System Views View Name Purpose SYSTEM.PARTITION Contains partition information. SYSTEM.PLAN Stores the result of one GENPLAN for each session. SYSTEM.PROCAUTH Identifies users and groups and the procedures they can execute. SYSTEM.PROCEDURE Describes each procedure. SYSTEM.PROCEDUREDEF Contains the definition of each procedure. SYSTEM.PROCRESULT Describes procedure result columns. SYSTEM.RULE Describes each rule. SYSTEM.
Using ALLBASE/SQL Understanding the System Catalog 108 Chapter 2
SQL Queries 3 SQL Queries This chapter describes SQL queries, through which you access the data in database tables. The following sections are presented: • Using the SELECT Statement • Simple Queries • Complex Queries • Using GENPLAN to Display the Access Plan • Updatability of Queries The other kinds of data manipulation, using the INSERT, UPDATE, and DELETE statements, were presented in the chapter “Using ALLBASE/SQL.
SQL Queries Using the SELECT Statement Using the SELECT Statement Use the SELECT statement to compose queries. The SELECT statement consists of the following components: 1. Select list 2. INTO clause 3. FROM clause 4. WHERE clause 5. GROUP BY clause 6. HAVING clause 7. ORDER BY clause The select list and FROM clause are required; all other components of this statement are optional. The following example does not contain an INTO clause.
SQL Queries Using the SELECT Statement The select list identifies the columns you want in the query result. In the above example, the (EXPR) column contains the vendor count specified as COUNT(VendorNumber) in the select list. Computations of this kind are called aggregate functions, which are defined in the “Expressions” chapter. The count function counts rows, in this case rows that satisfy the conditions set up in the SELECT statement clauses.
SQL Queries Simple Queries Simple Queries A simple query contains a single SELECT statement and typically has a simple comparison predicate in the WHERE clause. The SELECT statement can be used to retrieve data from single tables or from multiple tables. To retrieve data from multiple tables, you join the tables on a common column value. In the following example, ALLBASE/SQL joins rows from the PurchDB.SupplyPrice and PurchDB.
SQL Queries Simple Queries ALLBASE/SQL creates a row for the query result whenever a part number in table PurchDB.Parts matches a part number in table PurchDB.SupplyPrice, for example: PurchDB.Parts: PARTNUMBER PARTNAME SALESPRICE -------------------------------------------1123-P-01 Central processor 500.00 . . . PurchDB.SupplyPrice: PARTNUMBER VENDORNUMBER ... DISCOUNTQTY ---------------------------------------------1123-P-01 9002 1 1123-P-01 9003 5 1123-P-01 9007 3 1123-P-01 9008 5 . . .
SQL Queries Simple Queries ALLBASE/SQL joins a row in q.PurchDB.Parts to a row in p.PurchDB.Parts having a part number of 1133-P-01 whenever the SalesPrice value in q.PurchDB.Parts matches that for 1133-P-01. The query result for this self-join appears as follows: ----------------------|-------------PARTNUMBER |SALESPRICE ----------------------|-------------1133-P-01 | 200.00 1323-D-01 | 200.00 1333-D-01 | 200.00 1523-K-01 | 200.
SQL Queries Simple Queries In the example above, NULLs are included in the tables to show the difference between the behavior of NULLs in the production of the Cartesian product and the behavior of NULLs when a common column is specified in the WHERE clause join predicate. Consider the following query: SELECT p.PartNumber, PartName, c.PartNumber, Color FROM Parts p, Colors c WHERE p.PartNumber = c.PartNumber The query result for the query is as follows: SELECT p.PartNumber, PartName, c.
SQL Queries Complex Queries Complex Queries In addition to the simple queries shown in the previous section, you can create complex queries, which may contain more than one SELECT statement. At the highest level, a query is a SELECT statement, which consists of a query expression followed by an optional ORDER BY clause. At the next lower level, you can combine different query blocks into a single query expression with the UNION operator.
SQL Queries Complex Queries You can create a complex query by using the following: • UNION operator, which allows you to take the union of all rows returned by several query blocks in one SELECT statement. • Subqueries (also known as nested queries), which allow you to embed a query block within the search condition of an outer SELECT statement.
SQL Queries Complex Queries ORDER BY PartNumber ---------------PARTNUMBER ---------------1123-P-01 1133-P-01 1143-P-01 1153-P-01 1223-MU-01 1233-MU-01 1323-D-01 1333-D-01 1343-D-01 1523-K-01 1623-TD-01 1823-PT-01 Note that no rows are duplicated. When the UNION statement is not qualified by the ALL statement, all duplicate rows are removed from the query result. Notice that the ORDER BY clause must be at the end of the SELECT statement.
SQL Queries Complex Queries Note that you could get the same information in other ways. For example, you could use two separate queries. Alternatively, you could use two predicates in the search condition joined by the OR operator as follows: SELECT PartNumber FROM PurchDB.
SQL Queries Complex Queries The indicated duplicate rows would have been removed if the example contained the UNION statement instead of UNION ALL. Subqueries A subquery, also known as a nested query, is a query block that is completely embedded in a predicate. A subquery may appear within the search condition which is a part of the WHERE or HAVING clause of a SELECT, INSERT, UPDATE or DELETE statement. It is like any other query expression, except that it cannot contain a UNION operator.
SQL Queries Complex Queries The comparison operators shown here are allowable: = <> < > <= >= The quantifier is one of these three keywords: ALL ANY SOME The value list is of this form: (Val1, Val2, ..., Valn) Using the ANY or SOME Quantifier with a Value List With the ANY or SOME quantifier (ANY and SOME are synonymous), the predicate is true if any of the values in the value list or subquery relate to the expression as indicated by the comparison operator.
SQL Queries Complex Queries Using ANY or SOME with a Subquery You can also use the subquery form of the quantified predicate. If you wanted to distribute some of the business you have been giving vendor 9004, you might want to find vendor numbers for each vendor supplying at least one part supplied by vendor 9004. The following query returns this information: SELECT DISTINCT VendorNumber FROM PurchDB.SupplyPrice WHERE PartNumber = ANY (SELECT PartNumber FROM PurchDB.
SQL Queries Complex Queries avoid large stockpiles of these parts, you want to find vendors whose discount is not dependent on the purchase of such large quantities. The following query uses two subqueries and an ALL quantifier to retrieve the information you want: SELECT VendorNumber, PartNumber, DiscountQty FROM PurchDB.SupplyPrice WHERE DiscountQty < ALL (SELECT DiscountQty FROM PurchDB.SupplyPrice WHERE VendorNumber = 9010) AND PartNumber IN (SELECT PartNumber FROM PurchDB.
SQL Queries Complex Queries Using the IN Predicate with a Value List If you wanted to obtain the numbers of all vendors who supplied a given list of parts, the following query could be used: SELECT DISTINCT FROM WHERE IN VendorNumber PurchDB.SupplyPrice PartNumber ('1143-P-01', '1323-D-01', '1333-D-01', '1723-AD-01', '1733-AD-01') -----------VENDORNUMBER -----------9004 9007 9008 9009 . . .
SQL Queries Complex Queries is false. In the following example, suppose you need to determine the names of all vendors who currently supply parts: SELECT v.VendorName FROM PurchDB.Vendors v WHERE EXISTS (SELECT * FROM PurchDB.SupplyPrice sp WHERE sp.VendorNumber = v.VendorNumber) -----------------------------VENDORNAME -----------------------------Remington Disk Drives Dove Computers Space Management Systems Coupled Systems Underwood Inc. Pro-Litho Inc.
SQL Queries Complex Queries Correlated Versus Noncorrelated Subqueries In many cases, it is possible to execute the subquery just once, and obtain a result which is passed to the outer query for its use. Here is an example: SELECT * FROM PurchDB.SupplyPrice WHERE PartNumber = (SELECT PartNumber FROM PurchDB.Parts WHERE PartName = 'Cache Memory Unit') This kind of subquery is a noncorrelated subquery.
SQL Queries Complex Queries Outer Joins Using Explicit JOIN syntax Outer joins may be constructed using the explicit JOIN syntax of the SELECT statement (see the “SELECT” section of the “SQL Statements” chapter). In a two table outer join, the first table listed in the FROM clause of the SELECT statement is considered the left hand table and the second is considered the right hand table.
SQL Queries Complex Queries . . . --------------------------------------------------------------------Number of rows selected is 16 U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] , or e[nd] > e When you use the ON clause of the JOIN syntax, it must contain, at a minimum, the predicate which specifies the join condition. Other predicates may be placed within the SELECT statement, but their location is critical as the following examples show. Additional predicates may be placed in the ON clause.
SQL Queries Complex Queries SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp RIGHT JOIN PurchdB.Vendors v ON sp.VendorNumber = v.VendorNumber WHERE VendorState = 'CA' ORDER BY PartNumber DESC SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp RIGHT... ----------------+------------------------------+-------------------PARTNUMBER |VENDORNAME |VENDORCITY ----------------+------------------------------+-------------------|SemiTech Systems |San Jose |Kinki Cable Co.
SQL Queries Complex Queries keyword NATURAL may be used so long as the specification of the ON clause join predicate is omitted. This technique may be used when joining more than two tables, as in the query shown below: SELECT PartName, DeliveryDays, VendorName FROM PurchDB.Parts NATURAL RIGHT JOIN PurchDB.SupplyPrice NATURAL RIGHT JOIN PurchDB.Vendors ORDER BY PartName DESC SELECT PartName, DeliveryDays, VendorName FROM PurchDB.Parts NATURAL RIGHT...
SQL Queries Complex Queries SELECT FROM WHERE AND DISTINCT v.VendorName PurchDB.Vendors v, PurchDB.SupplyPrice sp v.VendorNumber = sp.VendorNumber sp.UnitPrice < 100.00 The next example shows this query joined to the previous one by the UNION ALL statement. It also shows the use of character constants to indicate which rows result from which query block. SELECT FROM WHERE AND DISTINCT v.VendorName, 'supplies parts under $100' PurchDB.Vendors v, PurchDB.SupplyPrice sp v.VendorNumber = sp.VendorNumber sp.
SQL Queries Complex Queries But, the action of the query is subtle. The natural left join preserves the parts from the Parts table that is not supplied by any vendor. This supplies the left hand component for the simulated symmetric outer join. However, although the natural right join preserves the three vendors from the vendors table who do not supply parts (the right hand component for the simulated symmetric outer join), it eliminates the unmatched parts from the Parts table.
SQL Queries Complex Queries The result of the above query follows: SELECT PartName, PartNumber, VendorName FROM PurchDB.Parts NATURAL LEFT... ------------------------------+----------------+--------------------PARTNAME |PARTNUMBER |VENDORNAME ------------------------------+----------------+--------------| |Kinki Cable Co.
SQL Queries Using GENPLAN to Display the Access Plan Using GENPLAN to Display the Access Plan When a statement is executed in ISQL or is preprocessed in an application program, the optimizer attempts to generate the most efficient path to the desired data.
SQL Queries Using GENPLAN to Display the Access Plan SELECT Operation, TableName, IndexName, QueryBlock, Step, Level FROM System.
SQL Queries Updatability of Queries Updatability of Queries INSERT, UPDATE and DELETE operations may be performed through views or as qualified by search conditions provided the views or search conditions are based on updatable queries. UPDATE WHERE CURRENT and DELETE WHERE CURRENT operations may be performed through cursors provided the cursors are based on updatable queries.
Constraints, Procedures, and Rules Using Integrity Constraints 4 Constraints, Procedures, and Rules In addition to the basic tables and indexes in a DBEnvironment, ALLBASE/SQL lets you create database objects known as constraints, procedures, and rules, which provide for a high degree of data consistency and integrity inside the DBEnvironment without the need for extensive application programming.
Constraints, Procedures, and Rules Using Integrity Constraints constraints are not allowed. Neither UNIQUE nor PRIMARY KEY columns can contain null values--they must be defined as NOT NULL. The following syntax is used to define a unique constraint on an individual column or column list at the table level: {UNIQUE PRIMARY KEY} (ColumnName [,...]) [CONSTRAINT ConstraintID] ConstraintID is the name of the constraint. It is not necessary to name the constraint.
Constraints, Procedures, and Rules Using Integrity Constraints The Referencing Table A referential constraint is placed on columns which are dependent on other columns (in the referenced table). You can create a referential constraint at either the table level or the column level. Referencing columns need not be NOT NULL. The following syntax is used to define a referential constraint at the table level in the CREATE TABLE statement for a referencing table: FOREIGN KEY(FKColumnName [,...
Constraints, Procedures, and Rules Using Integrity Constraints If a check constraint is added to an existing table, data already in the table is verified to ensure that the check constraint is satisfied. A constraint error occurs if the constraint is not satisfied; the ALTER TABLE statement adding the constraint fails. The check is also performed when the INSERT or UPDATE statement is executed. A DELETE statement never causes a check constraint error.
Constraints, Procedures, and Rules Using Integrity Constraints However, the same constraint defined at the table level is defined as follows: CREATE PUBLIC TABLE RecDB.Events CHECK (Date >= '1990-01-01') Check Constraint CONSTRAINT Check_No_Old_Events (SponsorClub CHAR(15), Event CHAR(30), Date DATE DEFAULT CURRENT_DATE, Time TIME, Coordinator CHAR(20), FOREIGN KEY (Coordinator, SponsorClub) REFERENCES RecDB.
Constraints, Procedures, and Rules Using Integrity Constraints 2. Clubs sponsoring the events are listed in the Clubs table 3. Events cannot be scheduled earlier than the current date. CREATE PUBLIC TABLE RecDB.Clubs (ClubName CHAR(15) NOT NULL PRIMARY KEY CONSTRAINT Clubs_PK, ClubPhone SMALLINT, Activity CHAR(18)) IN RecFS; CREATE PUBLIC TABLE RecDB.
Constraints, Procedures, and Rules Using Integrity Constraints The Events table contains information about events. The combination of values in the Coordinator and SponsorClub columns of the Events table must be either be null or match the combination of values in the MemberName and Club columns of the Members table, as shown by the Events_FK constraint. The Members table contains the names of members and clubs. A member can be in more than one club.
Constraints, Procedures, and Rules Using Integrity Constraints Order Table Values Result 8 Members 'Martha Mitchell', 'Energetics', 1605 Valid 9 Events 'Energetics', 'advanced stretching', '1986-12-04', '15:30:00', 'Martha Mitchell' Violates check constraint which states that an event's date must be later or the same as January 1, 1990 10 Events 'Energetics', 'advanced stretching', '1990-01-01','15:30:00','Martha Mitchell' Valid Values cannot be inserted into Members or Events without the r
Constraints, Procedures, and Rules Using Procedures In addition, you can temporarily use the SET DML ATOMICITY statement to set the DML error checking level to row level. However, you must handle partially processed statements yourself, as statements that get errors will not undo their partial execution. Constraint error checking is part of general error checking but you can override the checking level by setting constraint checking to deferred.
Constraints, Procedures, and Rules Using Procedures • Error Handling in Procedures • Using RAISE ERROR in Procedures • Recommended Coding Practices for Procedures Understanding Procedures Procedures (defined either in ISQL or through applications) can include many of the operations available inside application programs.
Constraints, Procedures, and Rules Using Procedures Executing Procedures You execute the procedure using an EXECUTE PROCEDURE statement. The EXECUTE PROCEDURE statement can be issued directly in ISQL or in an application program, or the EXECUTE PROCEDURE clause can appear inside a CREATE RULE statement. The following shows an invocation of a procedure in an ISQL session: isql=> EXECUTE PROCEDURE > ManufDB.FailureList (USER, CURRENT_DATETIME, > '1984-06-14 11:13:15.
Constraints, Procedures, and Rules Using Procedures application, and if a transaction is not already in progress, a transaction is begun. If a transaction is already in progress at the time EXECUTE PROCEDURE is issued, and the procedure issues either a COMMIT or a ROLLBACK statement to end the transaction, the entire transaction, including the portion in the application, is affected. In all cases, it is important to document procedures carefully.
Constraints, Procedures, and Rules Using Procedures In procedures that are invoked by execution of rules, the following statements result in an error: BEGIN WORK COMMIT WORK ROLLBACK WORK ROLLBACK WORK TO SAVEPOINT SAVEPOINT Another set of statements is provided for use only within procedures: Assignment (=) BEGIN...END DECLARE Variable GOTO IF...THEN...ELSEIF...ELSE...ENDIF Labeled Statements PRINT RETURN WHILE...DO...ENDWHILE Inside procedures, statements are terminated with a semicolon (;).
Constraints, Procedures, and Rules Using Procedures If you wish to return values to a calling application program, specify the parameter for OUTPUT in both the CREATE PROCEDURE and EXECUTE PROCEDURE statements. If no input value is required for a parameter, specify OUTPUT ONLY. Note that no OUTPUT option is allowed in the EXECUTE PROCEDURE statement in ISQL nor in the EXECUTE PROCEDURE clause of the CREATE RULE statement.
Constraints, Procedures, and Rules Using Procedures Using Built-in Variables in Procedures The following built-in variables can be used in error handling: Table 4-1. Built-in Variables in Procedures Variable Data Type Description ::sqlcode INTEGER DBERR number returned after the execution of an SQL statement, 0 if no errors. ::sqlerrd2 INTEGER Number of rows processed in an SQL statement. ::sqlwarn0 CHAR(1) Set to “W” if an SQL warning was detected.
Constraints, Procedures, and Rules Using Procedures the query results. Further information regarding procedure cursors is found in the “Using Procedures in Application Programs” chapter of the ALLBASE/SQL Advanced Application Programming Guide and in this manual in the following section, “Using a Procedure Cursor in ISQL,” and under related syntax statements (ADVANCE, CLOSE, CREATE PROCEDURE, DECLARE CURSOR, DESCRIBE, EXECUTE, EXECUTE IMMEDIATE EXECUTE PROCEDURE, FETCH, OPEN).
Constraints, Procedures, and Rules Using Procedures IF ::sqlcode = 0 THEN IF :SalesPrice < 1000. THEN UPDATE PurchDB.Parts SET SalesPrice = :SalesPrice*:Percentage WHERE CURRENT OF C1; ELSEIF :SalesPrice >= 1000. THEN UPDATE PurchDB.Parts SET SalesPrice = :SalesPrice*(:Percentage - .
Constraints, Procedures, and Rules Using Procedures When you execute the procedure, the following is displayed: execute procedure purchdb.partno2; ----------------+------------------------------+-----------------PARTNUMBER |PARTNAME |SALESPRICE ----------------+------------------------------+-----------------1123-P-01 |Central Processor | 500.00 1133-P-01 |Communication Processor | 200.00 1143-P-01 |Video Processor | 180.00 1153-P-01 |Graphics Processor | 220.
Constraints, Procedures, and Rules Using Procedures The syntax for the WHENEVER is as follows: WHENEVER (SQLERROR SQLWARNING NOT FOUND}{STOP CONTINUE GOTO [:]Label GO TO [:]Label} The STOP option causes the current transaction to be rolled back, and the procedure's execution is terminated.
Constraints, Procedures, and Rules Using Procedures the RETURN statement causes an immediate return from the procedure following the error. Following the return from a procedure, an application program can retrieve the messages from raised errors by using the SQLEXPLAIN statement.
Constraints, Procedures, and Rules Using Rules ROLLBACK statement upon completion of work and before termination; or it should be entirely contained within a transaction, that is, it should not contain any COMMIT or ROLLBACK statements. • If the procedure executes any COMMIT or ROLLBACK statements, it should be treated as a transaction. This means that the last statement accessing the DBEnvironment within the procedure should be a COMMIT WORK or a ROLLBACK WORK statement.
Constraints, Procedures, and Rules Using Rules Understanding Rules Rules allow you to define generalized constraints by invoking procedures whenever specified operations are performed on a table. The rule fires, that is, invokes a procedure, each time the specified operation (such as INSERT, UPDATE, or DELETE) is performed and the rule's search condition is satisfied. Rules tie procedures to particular kinds of data manipulation statements on a table.
Constraints, Procedures, and Rules Using Rules Techniques for Using Procedures with Rules One common use of the rule-and-procedure combination is to enforce integrity within a DBEnvironment. This can be done in different ways, depending on your needs.
Constraints, Procedures, and Rules Using Rules Executing the Chained Set of Procedures and Rules Whenever a user performs a DELETE operation on PurchDB.Parts, the procedures and rules are executed on each row of each table for the identified part number in the following order: 1. Delete from Parts table. 2. Fire rule RemovePart. 3. Invoke procedure RemovePart. 4. Delete from Inventory table. 5. Delete from SupplyPrice table. 6. Fire rule RemoveVendPart. 7. Invoke procedure RemoveVendPart. 8.
Constraints, Procedures, and Rules Using Rules Open the first cursor: OPEN SupplyCursor; FETCH SupplyCursor INTO :VendPartNum; WHILE ::sqlerrd2 = 1 DO DELETE FROM PurchDB.OrderItems WHERE VendPartNumber = :VendPartNum; Open the second cursor: OPEN BatchCursor; FETCH BatchCursor INTO :BatchStamp; WHILE ::sqlerrd2 = 1 DO DELETE FROM ManufDB.TestData WHERE BatchStamp = :BatchStamp; FETCH BatchCursor INTO :BatchStamp; ENDWHILE; CLOSE BatchCursor; DELETE FROM ManufDB.
Constraints, Procedures, and Rules Using Rules procedure, or the name of the rule that invoked the procedure (if the procedure is only fired by one rule). Suppose the following rule executes whenever a user attempts to delete a row in the Vendors table: CREATE RULE PurchDB.CheckVendor AFTER DELETE FROM PurchDB.Vendors EXECUTE PROCEDURE PurchDB.DelVendor (OLD.VendorNumber); The procedure PurchDB.
Constraints, Procedures, and Rules Using Rules statement is also undone. In application programs, you use SQLEXPLAIN to retrieve the messages generated by RAISE ERROR and other SQL statements. Enabling and Disabling Rules Rule processing takes place by default in the DBEnvironment.
Constraints, Procedures, and Rules Using Rules Then the procedure can be coded with IF statements like the following: if :Flag = 0 then commit work; endif; The flag check ensures that the rule will not execute statements that would cause it to generate an error when the procedure is invoked by a rule, while user calls can commit or roll back changes automatically.
Constraints, Procedures, and Rules Using Rules a statement within an invoked procedure which chained another rule), an error will occur, and the user's statement will be undone. Changing Session Attributes Procedures should avoid the following statements, which change the attributes of transactions or sessions: • SET CONSTRAINTS • DISABLE RULES • ENABLE RULES • SET PRINTRULES • SET USER TIMEOUT If you include one of these statements in a procedure invoked by a rule, consider its effect carefully.
Constraints, Procedures, and Rules Using Rules Differences between Rules and Integrity Constraints Rules are similar to integrity constraints in that when a rule is created, all existing INSERT, UPDATE, and DELETE statements will be affected by the rule (if the statement type is appropriate to the rule). Rules are viewed as changes to the table definition, and so all existing sections depending on the table are invalidated when a rule is created.
Concurrency Control through Locks and Isolation Levels 5 Concurrency Control through Locks and Isolation Levels Concurrency control is the process of regulating access to the same data by multiple transactions operating in the same DBEnvironment. Without regulation, a database could easily become inconsistent or corrupt. Consider what can happen if two or more concurrent users access the same data without any concurrency control.
Concurrency Control through Locks and Isolation Levels Defining Transactions Defining Transactions Concurrency control in ALLBASE/SQL operates at the level of the transaction, which identifies an individual user's unit of work within a multiuser DBEnvironment. As mentioned in a previous chapter, transactions are bounded by BEGIN WORK and COMMIT WORK statements. If you omit the BEGIN WORK statement, ALLBASE/SQL issues one automatically, using the RR (repeatable read) isolation level.
Concurrency Control through Locks and Isolation Levels Understanding ALLBASE/SQL Data Access Understanding ALLBASE/SQL Data Access Concurrent access to data by multiple users is facilitated by the use of a shared data buffer for all users of an ALLBASE/SQL DBEnvironment. Understanding how this buffer is used can clarify many concurrency issues. A DBEnvironment running in multiuser mode is accessed by multiple processes, as shown in Figure 5-2. Figure 5-2.
Concurrency Control through Locks and Isolation Levels Understanding ALLBASE/SQL Data Access promotes quick access to the same pages of data by different transactions, because a page may not have to be read in from disk if it is already in the buffer. When you issue a query, you request a specific set of rows and columns from different tables in a database. The content of this set of rows and columns is the query result.
Concurrency Control through Locks and Isolation Levels Use of Locking by Transactions Use of Locking by Transactions Transactions obtain locks to avoid the possible interference of one transaction with another. This is important when you use PUBLIC or PUBLICROW tables, which can be accessed by many concurrent users of a DBEnvironment.
Concurrency Control through Locks and Isolation Levels Use of Locking by Transactions example, if someone else is updating a row of user data on page A of a PUBLIC table, your transaction must wait until the update is committed before reading rows from page A into your tuple buffer. During query processing on PUBLICROW tables, the underlying row to which a cursor points is locked, and the page on which the row resides is also locked (with an intent lock, explained in "Types of Locks", below).
Concurrency Control through Locks and Isolation Levels Use of Locking by Transactions reduction of throughput that it is obviously not desirable in most situations. Another cost of locking is the use of shared memory resources. Each lock requires the use of some runtime control block space. The more locks used by a transaction, the more memory required for control blocks. This is especially important for PUBLICROW tables, which usually require more locks than PUBLIC tables.
Concurrency Control through Locks and Isolation Levels Defining Isolation Levels between Transactions Defining Isolation Levels between Transactions Isolation level is the degree to which a transaction is separated from all other concurrent transactions.
Concurrency Control through Locks and Isolation Levels Defining Isolation Levels between Transactions may have been modified by another transaction. At the CS level, share locks on data (whether at the row or page level) are released as soon as the associated rows are no longer in the tuple buffer. Exclusive locks are held until the transaction ends with a COMMIT WORK or ROLLBACK WORK statement.
Concurrency Control through Locks and Isolation Levels Defining Isolation Levels between Transactions • You can retrieve only rows that have been committed by some transaction or modified by your own transaction. • Other transactions can write on the page on which the transaction has a cursor positioned, because locks are released as soon as data is read. • If an update is done on a page, the lock is retained until the transaction ends with a COMMIT WORK or ROLLBACK WORK statement.
Concurrency Control through Locks and Isolation Levels Details of Locking Details of Locking To promote the greatest concurrency, ALLBASE/SQL supports a variety of granularities and lock types. Granularity is the size of the object locked. Lock type is the severity of locking provided. Compatibility refers to the ability of different transactions to hold locks at the same time on the same object. Lock Granularities The use of different granularities of locking promotes a high level of concurrency.
Concurrency Control through Locks and Isolation Levels Details of Locking Figure 5-4. also portrays a query that accesses two pages of a table. Figure 5-4. Row Versus Page Level Locking With row level locking, only the rows containing data scanned for the query are locked. All other rows can be locked by other transactions. With page level locking, the same query locks an entire page, even if the page contains row(s) not used by the query. Table size can affect concurrency at the page level.
Concurrency Control through Locks and Isolation Levels Details of Locking in mind that the system can actually acquire several page or row locks, one at a time, before the data is exposed to the user. In effect, the user's transaction obtains and releases locks on sets of pages or rows at a time as it moves through a query result. This is because data from many pages and rows can be required to fill the 12K tuple buffer.
Concurrency Control through Locks and Isolation Levels Details of Locking transaction wishes to lock the table in exclusive mode. The IS lock on the table would prevent the other transaction from locking the table in exclusive mode. Without the use of higher granularity locks, ALLBASE/SQL would have to search all page or row locks to determine whether the exclusive lock request could be granted. Figure 5-5. shows the use of an intention lock at the table level and share locks on the page level.
Concurrency Control through Locks and Isolation Levels What Determines Lock Types page. Note that S and X locks on the same page are not compatible. When locks are not compatible, the second access request must wait until the lock acquired by the first access request is released. Weak Locks Intention exclusive locks are called weak locks when there is no other lock at a finer level of granularity on the object being locked.
Concurrency Control through Locks and Isolation Levels What Determines Lock Types • Type of SQL statement. • Locking structure implicit at CREATE TABLE time. • Use of the LOCK TABLE statement. • Optimizer's choice of a scan type. • Choice of isolation level. • Updatability of cursors or views used to access data. • Use of sorting. Type of SQL Statement Specific SQL statements imply particular kinds of data access. Statements such as SELECT and FETCH, which merely read data, request share locks.
Concurrency Control through Locks and Isolation Levels What Determines Lock Types PUBLICROW and PUBLIC tables allow concurrent users to access the table for both reads and writes but they increase the chances of deadlock, because concurrent transactions can be waiting for each other to release locks. PUBLICROW tables obtain locks at the row level, which affords more concurrency than with PUBLIC tables, at the possible cost of obtaining more locks.
Concurrency Control through Locks and Isolation Levels What Determines Lock Types the first page of a table and reads each page, looking for rows that qualify for the query result, until it arrives at the end of the table. An index scan looks up the page locations of those rows that qualify for the query result in an index which you have separately created. A hash scan accesses an individual row by calculating the row's primary page location from a value supplied in the query's predicate.
Concurrency Control through Locks and Isolation Levels What Determines Lock Types minimize the chance of deadlocks. The kind of lock obtained at different isolation levels depends on the other factors that determine locks--scan type, kind of SQL statement, and implicit table type. A simplified summary of locks obtained on PUBLIC tables and their indexes appears in Table 5-3.. Hash and TID scans are omitted. Table 5-3.
Concurrency Control through Locks and Isolation Levels What Determines Lock Types A simplified summary of locks obtained on PUBLICROW tables appears in Table 5-4. Hash and TID scans are omitted. Table 5-4.
Concurrency Control through Locks and Isolation Levels What Determines Lock Types the update corresponds to an index delete followed by an index insert. What should you lock if there is no higher? Lock an imaginary tuple which has the highest possible key. Note that locks are placed at the tuple level for PUBLICROW or at the page level for PUBLIC tables.
Concurrency Control through Locks and Isolation Levels Scope and Duration of Locks Scope and Duration of Locks In general, the length of a transaction affects concurrency. Long transactions hold locks longer, which increases the chances that another transaction is waiting for a lock. Short transactions are "in and out" quickly, which means they are less likely to interfere with other transactions.
Concurrency Control through Locks and Isolation Levels Examples of Obtaining and Releasing Locks Examples of Obtaining and Releasing Locks The following sections present a few scenarios that show how locks are obtained and released within concurrent transactions. Simple Example of Concurrency Control through Locking The following scenario illustrates in a simple way how locks are obtained and released. It is based on the sample DBEnvironment PartsDBE, which is fully described in Appendix C.
Concurrency Control through Locks and Isolation Levels Examples of Obtaining and Releasing Locks Figure 5-7. Lock Requests 1: Waiting for Exclusive Lock Figure 5-8.
Concurrency Control through Locks and Isolation Levels Examples of Obtaining and Releasing Locks Figure 5-9. Lock Requests 3: Share Locks Granted Sample Transactions Using Isolation Levels The following sections show typical situations in which different isolation levels affect the behavior of your transactions when using the sample DBEnvironment PartsDBE. Example of Repeatable Read The following scenario illustrates the operation of the RR isolation level: 1.
Concurrency Control through Locks and Isolation Levels Examples of Obtaining and Releasing Locks > SET ContactName = 'Harry Jones' > WHERE VendorNumber = 9001; Transaction 2 now must wait for an IX lock on the Vendors table because an IX lock is not compatible with the S lock already held by transaction 1. Transaction 2 also must obtain an X lock on the page containing data for vendor 9001. 4. User 1 now issues the following statement: isql=> COMMIT WORK; 5.
Concurrency Control through Locks and Isolation Levels Examples of Obtaining and Releasing Locks Example of Read Committed The following scenario illustrates the operation of the RC isolation level in concurrent transactions in the sample DBEnvironment PartsDBE. Most of the details are the same as for the CS example just presented: 1.
Concurrency Control through Locks and Isolation Levels Resolving Conflicts among Concurrent Transactions Resolving Conflicts among Concurrent Transactions Several kinds of conflict can occur between transactions that are contending for access to the same data. The following three are typical cases: • One transaction has locked an object that another transaction needs and is in a wait state.
Concurrency Control through Locks and Isolation Levels Resolving Conflicts among Concurrent Transactions Deadlocks The second kind of conflict is known as a deadlock between two transactions. This happens when two transactions both need data or indexes that the other already has locked. Deadlocks involving system catalog pages are also possible. ALLBASE/SQL detects and resolves deadlocks when they occur.
Concurrency Control through Locks and Isolation Levels Resolving Conflicts among Concurrent Transactions Figure 5-10. Deadlock The use of PRIVATE tables ensures there will be no deadlock on the same table, because access to the table is serialized. However, deadlock across two or more tables is common with PUBLICREAD and PRIVATE tables that are accessed by different transactions in different order.
Concurrency Control through Locks and Isolation Levels Resolving Conflicts among Concurrent Transactions Avoiding Deadlock The tradeoff between deadlock and throughput is one of the central issues in concurrency control. It is important to minimize the number of deadlocks while permitting the greatest possible concurrent access to database tables. Avoiding Deadlock by Using the Same Order of Execution To avoid deadlock among multiple tables, be sure to have all transactions access them in the same order.
Concurrency Control through Locks and Isolation Levels Resolving Conflicts among Concurrent Transactions Undetectable Deadlock Applications that connect to multiple DBEnvironments may encounter deadlocks that cannot be detected and resolved by ALLBASE/SQL. An example follows: Transaction 1: Transaction 2: Transaction 1: Transaction 2: SET CONNECTION 'DBE1'; UPDATE TABLEA SET COL1 = 5; Obtains X table lock. SET CONNECTION 'DBE2'; UPDATE TABLEB SET COL1 = 7; Obtains X table lock.
Concurrency Control through Locks and Isolation Levels Monitoring Locking with SQLMON Monitoring Locking with SQLMON SQLMON is an online diagnostic tool that monitors the activity of your DBEnvironment. In addition to providing information on file capacity, I/O, logging, tables, and indexes, SQLMON displays information on the locks currently held in your DBEnvironment. SQLMON is fully documented in the ALLBASE/SQL Performance and Monitoring Guidelines.
Concurrency Control through Locks and Isolation Levels Monitoring Locking with SQLMON Table 5-5.
Names 6 Names This chapter contains general rules for names used in ALLBASE/SQL commands. Syntactically, names used in ALLBASE/SQL commands fall into several categories.
Names Basic Names Basic Names The syntax rules in this chapter apply to most SQL names. Names that are required to conform to the following rules can be classified as basic names: • A basic name can be up to 20 bytes in length. • A name can be made up of any combination of letters (A to Z), decimal digits (0 to 9), $, #, @, or underscore (_). However, the first character cannot be an underscore or a decimal digit.
Names Native Language Object Names Native Language Object Names All the object names in a DBEnvironment can be represented in the DBEnvironment language or in NATIVE 3000. The following rules for object names are the same as for ASCII: • The length of an object name is specified as a number of bytes. Note that this would mean a maximum of 20 characters for a table name in English and 10 in Chinese, because Chinese is represented in a two-byte character set.
Names Authorization Names Authorization Names An authorization name identifies an owner name defined in the AUTHORIZATION clause of the CREATE SCHEMA statement. Authorization names must be unique within the DBEnvironment. There cannot be another owner, authorization group, or grantor with the same name on the system when the CREATE SCHEMA statement is issued.
Names Host Variable Names Host Variable Names Host variables are used to pass information between an application program and ALLBASE/SQL. They are ordinary application program variables that happen to be used in SQL commands. A host variable name must be preceded by a colon (:) when used in an SQL command. When used elsewhere in an application program, no colon should be used. Host variable names must conform to ALLBASE/SQL's rules for basic names; however, they are allowed to be up to 30 bytes in length.
Names DBEFile and Log File Identifiers DBEFile and Log File Identifiers DBEFiles and log files have logical names which conform to the rules for ALLBASE/SQL basic names. DBEFile and log file names are stored in the system catalog. In addition to logical names, the physical DBEFiles and log files are referred to in the SQL syntax by system file names. If the group and account are not given, ALLBASE/SQL assumes the name specified is in the current group and account.
Data Types 7 Data Types Every value in SQL belongs to some data type. A data type is associated with each value retrieved from a table, each constant, and each value computed in an expression. This chapter discusses data types.
Data Types Type Specifications Type Specifications All the data in a column must be of the same type. Specify the data type for each column when you create a table or when you add a column to an existing table. The ALLBASE/SQL data types and the values you can specify for data of each type are shown in Table 7-1. Table 7-1. ALLBASE/SQL Data Types Group Data Type Description Alphanumeric CHAR[ACTER][(n)] String of fixed length n, where n is an integer from 1 to 3996 bytes. The default size is CHAR (1).
Data Types Type Specifications Table 7-1. ALLBASE/SQL Data Types Group Data Type Description FLOAT(p) or REAL Short (32-bit) floating point number. This is an approximate numeric value consisting of an exponent and a mantissa. The precision, p, is a positive integer that specifies the number of significant binary digits in the mantissa. The value of p can be from 1 to 24. The default (using REAL) is 24. The range of negative numbers that can be represented is −3.402823E+38 to −1.175495E−38.
Data Types Type Specifications Your choice of data types can affect the following: • How values are used in expressions. Some operations can be performed only with data of a certain type. For example, arithmetic operations are limited to numeric and date/time data types, such as INTEGER, SMALLINT, FLOAT, DECIMAL, DATE, TIME, DATETIME, or INTERVAL. Pattern matching with the LIKE predicate can be performed only with string data, that is, data of types CHAR or VARCHAR.
Data Types Value Comparisons Value Comparisons When you compare a CHAR and a VARCHAR string, ALLBASE/SQL pads the shorter string with ASCII blanks to the length of the longer string. The two strings are equal if the characters in the shorter string match those in the longer string and if the excess characters in the longer string are all blank. If a case sensitive CHAR column is compared to a CHAR column that is not case sensitive, both columns are treated as case sensitive.
Data Types Overflow and Truncation Overflow and Truncation Some operations can result in data overflow or truncation. Overflow results in loss of data on the left. Truncation results in loss of data on the right. Overflow or truncation can occur in several instances as follows: • During arithmetic operations, for example, when multiplication results in a number larger than the maximum value allowable in its type. Arithmetic operations are defined inChapter 8 , “Expressions.
Data Types Type Conversion Type Conversion ALLBASE/SQL converts the type of a value in the following situations: • Including values of different types in the same expression. • Moving data from a host variable to a column or a column to a host variable of a different type. The valid type combinations are shown in Table 7-1. Table 7-3.
Data Types Type Conversion • Truncation of the fractional part of a value occurs during these conversions: DECIMAL to SMALLINT or INTEGER DECIMAL to DECIMAL when the target scale is smaller than the source scale FLOAT to INTEGER, SMALLINT, DECIMAL, or REAL REAL to INTEGER, SMALLINT, or DECIMAL • Truncation can occur during these conversions if the target is too small: DATE, TIME, DATETIME or INTERVAL to VARCHAR or CHAR CHAR to VARCHAR, BINARY or VARBINARY VARCHAR to CHAR, BINARY or VARBINARY VA
Data Types Null Values Null Values A null value is a special value that indicates the absence of a value. Any column in a table or parameter or local variable in a procedure, regardless of its data type, can contain null values unless you specify NOT NULL for the column when you create the table or the procedure. NULL is used as a placeholder for a value that is missing or unknown.
Data Types Decimal Operations Decimal Operations The precision (p) and scale (s) of a DECIMAL result depend on the operation used to derive it. The following rules define the precision and scale that result from arithmetic operations on two decimal values having precisions p1 and p2 and respective scales s1 and s2. Rules are also provided for the resulting precision and scale of aggregate functions that operate on a single expression having a precision of p1 and a scale of s1.
Data Types Date/Time Operations Date/Time Operations DATE, TIME, DATETIME, or INTERVAL values may only be assigned to a column with a matching data type or to a fixed or variable length character string column or host variable. Otherwise an error condition is generated. All rules regarding assignment to a character string are also true for date/time assignment to a character string variable or column.
Data Types Date/Time Operations INTERVAL values: UPDATE ManufDB.TestData SET LabTime = '5 04:23:00.000' WHERE TestEnd = '19:30:00' Note that the radix of DATE and TIME data is seconds, whereas the radix of DATETIME and INTERVAL data is milliseconds. Date/time data types can also be converted to formats other than the default formats by the date/time functions described in Chapter 8 , “Expressions.
Data Types Date/Time Operations Table 7-5. Arithmetic Operations on Date/Time Data Types a. b. c. d. Operanda Operator Operand b Result Type STRINGd − TIME INTERVAL STRINGb + TIME TIME TIME − STRINGd INTERVAL TIME + STRINGd TIME STRINGb +,− INTERVAL INTERVAL INTERVAL +,− STRINGb INTERVAL The format for string should be DATE. The format for string should be INTERVAL. The format for string should be DATETIME. The format for string should be TIME.
Data Types Binary Operations Date/Time Data Types and Aggregate Functions You can use the aggregate functions MIN, MAX, and COUNT in queries on columns of type DATE, TIME, DATETIME, and INTERVAL. SUM and AVG can be done on INTERVAL data types only. Binary Operations BINARY or VARBINARY values may be assigned to a column with a matching data type or to a fixed or variable length character string host variable.
Data Types Long Operations Long Operations LONG columns in ALLBASE/SQL enable you to store a very large amount of binary data in your database and to reference that data using a column name. You might use LONG columns to store text files, software application code, voice data, graphics data, facsimile data, or test vectors. Storing data in the database gives you the the advantages of ALLBASE/SQL's recoverability, concurrency control, locking strategies, and indexes on related columns.
Data Types Long Operations The next statement specifies that data for the new LONG column, PartModule, will be stored in PartPictureSet: ALTER TABLE PurchDB.PartsIllus ADD PartModule LONG VARBINARY(50000) IN PartPictureSet Since LONG data for PartMap will be stored in the same DBEFileSet as its related table, PartsIllus, it goes to PartsIllusSet. ALTER TABLE PurchDB.
Data Types Long Operations Using INSERT with No Specified File Options In this example, data from the file hammer.tools becomes the contents of the LONG column PartPicture. The output device is the file hammer. If this file already exists when the SELECT or FETCH statement is issued, it is not overwritten or appended to, and an error is generated. INSERT INTO PurchDB.PartsIllus VALUES ('hammer' 100, '
Data Types Long Operations Using INSERT with Heap Space Input and Output You have the option of using a heap address to indicate the location of input data. Output datamay be directed toa heap address generated by ALLBASE/SQL at output time. In the next example, 4000 bytes of data flow from heap address 1230 to the PartsIllus table, and when this data is selected or fetched, it goes to the heap address: INSERT INTO PurchDB.
Data Types Long Operations UPDATE PurchDB.PartsIllus SET PartPicture = 'newhammer' WHERE PartName = 'hammer' Using UPDATE to Change Stored Data Only Here the stored data in LONG column PartPicture is replaced with data from the file ../tools/newhammer. Assuming the original output device was named hammer, when you select or fetch the PartPicture column, the updated output still goes to a file named hammer. UPDATE PurchDB.PartsIllus SET PartPicture = '
Data Types Native Language Data Native Language Data Character data in the DBEnvironment can be represented in the native language specified by the DBEnvironment language. When native language character columns are created, they follow the same rules as CHAR and VARCHAR columns. For character columns, size is defined in bytes. Thus a column defined as CHAR (20) could hold 20 characters in ASCII or 10 characters in Japanese Kanji. Numeric data must be in ASCII representation.
Expressions 8 Expressions This chapter discusses value specification.
Expressions Expression Expression An expression can consist of a primary or several primaries connected by arithmetic operators. A primary is a signed or unsigned value derived from one of the items listed in the SQL syntax below.
Expressions Expression Parameters +, − designate unary plus and unary minus. Unary plus assigns the primary a positive value. Unary minus assigns the primary a negative value. Default is positive. ColumnName is the name of a column from which a value is to be taken; column names are defined in the "Names" chapter.
Expressions Expression ProcedureParameter contains a value that is passed into or out of a procedure. Built-inVariable is one of the following built-in variables used for error handling: • • • • • • • ::sqlcode ::sqlerrd2 ::sqlwarn0 ::sqlwarn1 ::sqlwarn2 ::sqlwarn6 ::activexact The first six of these have the same meaning that they have as fields in the SQLCA in application programs. Note that in procedures, sqlerrd2 returns the number of rows processed for all host languages.
Expressions Expression Description • Arithmetic operators can be used between numeric values, that is, those with data types of FLOAT, REAL, INTEGER, SMALLINT, or DECIMAL. Refer to the "Data Types" chapter for rules governing the resulting precision and scale of DECIMAL operations. • Arithmetic operators can also be used between DATE, TIME, DATETIME, and INTERVAL values. Refer to the "Data Types" chapter for rules on the valid operations and the resulting data types.
Expressions Expression are evaluated. For more information, refer to the chapter, "Data Types." • If the value of an indicator variable is less than zero, the value of the corresponding host variable is considered to be NULL. NOTE To be consistent with the standard SQL and to support portability of code, it is strongly recommended that you use a −1 to indicate a NULL value. However, ALLBASE/SQL interprets all negative indicator variable values as indicating a NULL value in the corresponding host variable.
Expressions Expression table: BULK INSERT INTO PurchDB.OrderItems VALUES (?,?,?,?) See the syntax descriptions for each DML statement, and for the PREPARE, DESCRIBE, EXECUTE, and OPEN statements for details of dynamic parameter usage. Example The result length of PartNumber || VendPartNumber is 32 in this example. CREATE TABLE PurchDB.
Expressions Add Months Function Add Months Function The Add Months function uses the keyword ADD_MONTHS to apply the addition operation to a DATE or DATETIME expression. It is different from a simple addition operator in that it adjusts the day field in the DATE or DATETIME value to the last day of the month if adding the months creates an invalid date (such as '1989-02-30').
Expressions Add Months Function example, '1989-02-31'). In this case, the day is set to the last day of the month for that year, and ALLBASE/SQL generates a warning indicating the adjustment. • If either parameter is NULL, ADD_MONTHS will evaluate to NULL also. Example In this example, rows are returned which comprise the batch stamp and test date that have a pass quantity less than 48. A warning is generated because 7 months added to the '1984-07-30' date results in an invalid date, '1985-02-30'.
Expressions Aggregate Functions Aggregate Functions Aggregate functions specify a value computed using data described in an argument. The argument, enclosed in parentheses, is an expression. The value of the expression is computed using each row that satisfies a SELECT statement. Aggregate functions can be specified in the select list and the HAVING clause. Refer to the explanation of the SELECT statement for more details.
Expressions Aggregate Functions SUM finds the total of all values in the argument. NULL values are ignored. SUM can be applied to numeric data types and INTERVAL only. When applied to FLOAT or REAL, the result is FLOAT. When applied to INTEGER or SMALLINT, the result is INTEGER. When applied to DECIMAL, the result is DECIMAL. When applied to INTERVAL, the result is INTERVAL. COUNT * counts all rows in all columns, including rows containing NULL values. The result is INTEGER.
Expressions CAST Function CAST Function The CAST function converts data from one data type to another. The CAST function can be used anywhere a general expression is allowed. CAST is supported inside functions that support expressions including aggregate functions. CAST also takes general expressions including nested functions as input. Scope SQL Data Manipulation Statements SQL Syntax { CAST ( { Expression NULL} { AS ,} DataType [,FormatSpec]) } Parameters Expression is the value to be converted.
Expressions CAST Function Source Data Type Target Data Type ENa ANb VC CHAR (n) B VB DATE TIME DT I TID ENa Yc Yc Yd Yd Ed Ed Nd N N N N ANb Yc Yc Yd Yd Ed Ed N N N N N VARCHAR(n) Yd Yd Yc Yc Yc Yc Yc Yc Yc Yc Ed CHAR(n) Yd Yd Yc Yc Yc Yc Yc Yc Yc Yc Ed BINARY Ed Ed Yc Yc Yc Yc Ed Ed Ed Ed Ed VARBINARY(n) Ed Ed Yc Yc Yc Yc Ed Ed Ed Ed Ed DATE Ec Ec Yc Yc Ed Ed Yc N N N N TIME Ec Ec Yc Yc Ed Ed N Yc N N
Expressions CAST Function • If both source and target data type are character strings, the language of the result string is the same as the source. • If the source data type is a character string and the target data type is a numeric, then the source value must only contain a character representation of a number. The result of the conversion is the numeric value that string represented. If the source value is not a numeric string, an error occurs.
Expressions CAST Function should be done by nesting the CAST functions so that the numeric value is first converted to a character string, and then converted to the date/time data type. • Converting a date/time data type to: • A character type with CAST is equivalent to using the TO_CHAR date/time function. All the same rules apply. • An INTEGER is equivalent to using the TO_INTEGER date/time function.
Expressions CAST Function Assume there is only one row qualified for the following query. EXEC SQL select PartNumber, CAST(UnitPrice,BINARY(8)) INTO :hostvar1, :hostvar2 FROM PurchDB.SupplyPrice WHERE VendorNumber BETWEEN 9000 AND 9020; 6. You will see the DECIMAL constant shown as: 99.99 SELECT PartNumber, CAST(99.99,VARCHAR(10)) FROM PurchDB.
Expressions Constant Constant A constant is a specific numeric, character, or hexadecimal value. Scope SQL Data Manipulation Statements SQL Syntax { IntegerValue FloatValue FixedPointValue ‘CharacterString’ OxHexadecimalString} Parameters IntegerValue is a signed or unsigned whole number compatible with INTEGER or SMALLINT data types, for example: -16746 155 5 FloatValue is a signed or unsigned floating point number compatible with the FLOAT or REAL data types, for example: .
Expressions Current Functions Current Functions Current functions return a value that represents a current DATE, TIME, or DATETIME. The value returned is a string with the format of a DATE, TIME, or DATETIME data type. Scope SQL Data Manipulation Statements SQL Syntax {CURRENT_DATE CURRENT_TIME CURRENT_DATETIME} Description • CURRENT_DATE returns the current date as a string of the form 'YYYY-MM-DD', where YYYY represents the year, MM is the month, and DD is the day.
Expressions Date/Time Functions Date/Time Functions The following text describes the two types of date/time conversion functions: • The input functions convert character values into date/time values. With TO_DATE, TO_TIME, TO_DATETIME, and TO_INTERVAL you can enter date/time values in a format other than the default format. • The output functions convert date/time values out to integer or character values.
Expressions Date/Time Functions be a CHAR or VARCHAR data type. DateTimeExpression is a Date/Time expression. See the "Expression" section of this chapter for more details on the syntax. The expression must be a DATE, TIME, DATETIME, or INTERVAL data type. FormatSpecification specifies the format of ColumnName or CharacterValue. Refer to the syntax for FormatSpecification later in this section. Format elements are presented in the "Description" section below.
Expressions Date/Time Functions ProcedureParameter contains a value that is passed into or out of a procedure. ::Built-inVariable is one of the following built-in variables used for error handling: • ::sqlcode • ::sqlerrd2 • ::sqlwarn0 • ::sqlwarn1 • ::sqlwarn2 • ::sqlwarn6 • ::activexact The first six of these have the same meaning that they have as fields in the SQLCA in application programs. ::activexact indicates whether a transaction is in progress or not.
Expressions Date/Time Functions The Z prefix and Q and D are only allowed for the function TO_CHAR. If YY is used without CC, the default CC is 19. The following elements are for representing alphabetic characters: MONTH Name of month MON Abbreviated name of month DAYOFWEEK Name of day DAY Abbreviated name of day -/:.
Expressions Date/Time Functions ./:., Punctuation marks reproduced in value (includes spaces) "string" Quoted string reproduced in value Delimiting punctuation marks must be the same in the value parameter and the format specification parameter. • The TO_DATETIME function and the TO_CHAR function on TIME expressions use the date/time default format 'YYYY-MM-DD HH:MI:SS.FFF'.
Expressions Date/Time Functions DATE or DATETIME column. Refer to the Add Months Function for further information. Examples 1. Date format In the example below, the format MM/DD/YY is used to enter a date instead of using the default format, which is YYYY-MM-DD: INSERT INTO ManufDB.TestData(batchstamp, testdate) VALUES (TO_DATETIME ('07/02/89 03:20.000', 'MM/DD/YY HH12:MI.
Expressions Long Column Functions Long Column Functions Long column functions return information from the long column descriptor. Scope SQL Data Manipulation Statements SQL Syntax { OUTPUT_DEVICE(LongColumnName) OUTPUT_NAME(LongColumnName) } Parameters OUTPUT_DEVICE returns an integer value indicating the output device type stored in the long column descriptor for LongColumnName.
Expressions Long Column Functions Examples 1. OUTPUT_DEVICE example Change the PartPicture output device name to NewHammer in any row whose output device type for PartPicture is a system file. UPDATE PartsIllus SET PartPicture = '> NewHammer' WHERE OUTPUT_DEVICE(PartPicture) = 1 2. OUTPUT_NAME example Select the output device name of the PartPicture column for any row with a PartNumber of 100.
Expressions String Functions String Functions String functions return partial values or attributes of character and BINARY (including LONG) string data. With the G3 release of ALLBASE/SQL and IMAGE/SQL, the supported SQL syntax has been enhanced to include the following string manipulation functions: UPPER, LOWER, POSITION, INSTR, TRIM, LTRIM and RTRIM.
Expressions String Functions function, except that the syntax is slightly different. Syntax [INST (stringexpr1,stringexpr2 [,n[,m]])] LTRIM LTRIM function trims the characters specified in charset from the beginning of the string stringexpr. Syntax [LTRIM (charset,stringexpr)] RTRIM RTRIM function trims the characters specified in charset from the end of the string stringexpr.
Expressions String Functions Example 4 SELECT * FROM SYSTEM.TABLE WHERE NAME = LTRIM ('?*', 'VENDORS?*???***') AND OWNER = 'PURCHDB'; Returns the system table entry for PURCHDB.VENDORS Example 5 SELECT TRIM (BOTH '?*' FROM '??**?*hello ?* world???*') FROM SYSTEM.TABLE WHERE NAME = 'VENDORS'; Returns 'hello ?* world'.
Expressions String Functions Description • The string functions can appear in an expression, a select list, or a search condition of an SQL data manipulation statement. • The string functions can be applied to any string data type, including binary and long column data types. • The string returned by the SUBSTRING function is truncated if (StartPosition + Length -1) is greater than the length of the StringExpression. Only (Length StartPosition +1) bytes is returned, and a warning is issued.
Expressions String Functions 2.
Expressions TID Function TID Function Used in a select list, the TID function returns the database address of a row (or rows for BULK SELECT) of a table or an updatable view. Used in a WHERE clause, the TID function takes a row address as input and allows direct access to a single row of a table or an updatable view. Scope SQL Data Manipulation Statements SQL Syntax TID([ [Owner.]TableName [Owner.
Expressions TID Function pseudotables. • In order to assure optimization (through the use of TID access) the expressions in the WHERE clause of a single query block must be ANDed together. No OR is allowed. In addition, only the following TID expressions can be optimized: TID([ [Owner.]TableName [Owner.]ViewName CorrelationName ]) = {Constant HostVariableName [[INDICATOR]:IndicatorVariable] ? :LocalVariable :ProcedureVariable } • Only equal and not equal comparison operators are supported.
Expressions TID Function Example isql=> SELECT tid(), PartNumber > FROM PurchDB.Parts; select tid(), PartNumber from PurchDB.
Search Conditions 9 Search Conditions This chapter discusses search condition clauses and the predicates used in them. The following sections are presented: • Search Condition • BETWEEN Predicate • Comparison Predicate • EXISTS Predicate • IN Predicate • LIKE Predicate • NULL Predicate • Quantified Predicate A search condition specifies criteria for choosing rows to select, update, delete, insert, permit in a table, or fire rules on.
Search Conditions Search Condition Search Condition A search condition is a single predicate or several predicates connected by the logical operators AND or OR. A predicate is a comparison of expressions that evaluates to a value of TRUE, FALSE, or unknown. If a predicate evaluates to TRUE for a row, the row qualifies for the select, update, or delete operation. If the predicate evaluates to FALSE or unknown for a row, the row is not operated on.
Search Conditions Search Condition LikePredicate determines whether an expression contains a particular character string pattern. NullPredicate determines whether a value is null. QuantifiedPredicate determines whether an expression bears a particular relationship to a specified set. (SearchCondition) is one of the above predicates, enclosed in parentheses. Description • Predicates in a search condition are evaluated as follows: • Predicates in parentheses are evaluated first.
Search Conditions BETWEEN Predicate BETWEEN Predicate A BETWEEN predicate determines whether a value is equal to or greater than a second value and equal to or less than a third value. The predicate evaluates to true if a value falls within the specified range. If the NOT option is used, the predicate evaluates to true if a value does not fall within the specified range.
Search Conditions Comparison Predicate Comparison Predicate A comparison predicate compares two expressions using a comparison operator. The predicate evaluates to TRUE if the first expression is related to the second expression as specified in the comparison operator. Scope SQL Data Manipulation Statements SQL Syntax Expression { = <> > >= < <=} [ Expression SubQuery ] Parameters Expression specifies a value used to identify columns, screen rows, or define new column values.
Search Conditions Comparison Predicate the two expressions are compared using the NLS collation rules. The case insensitive NLS comparison is done by using the NLSCANMOVE and NLSCOLLATE intrinsics. The same ASCII characters in upper and lower case are equivalent. Accent characters (extended character) in upper and lower case are also equivalent. However, an accent character may not be the same as its ASCII equivalent, depending on the specific language collation table.
Search Conditions EXISTS Predicate EXISTS Predicate An EXISTS predicate tests for the existence of a row satisfying the search condition of a subquery. The predicate evaluates to TRUE if at least one row satisfies the search condition of the subquery. Scope SQL Data Manipulation Statements SQL Syntax EXISTS SubQuery Parameters SubQuery A subquery is a nested query. The syntax of subqueries is presented in the description of the SELECT statement in Chapter 12 , “SQL Statements S Z.
Search Conditions IN Predicate IN Predicate An IN predicate compares an expression with a list of specified values or a list of values derived from a subquery. The predicate evaluates to TRUE if the expression is equal to one of the values in the list. If the NOT option is used, the predicate evaluates to TRUE if the expression is not equal to any of the values in the list.
Search Conditions IN Predicate running the program. USER behaves like a CHAR(20) constant, with trailing blanks if the login name has fewer than 20 characters. CurrentFunction indicates the value of the current DATE, TIME, or DATETIME. Integer indicates a value of type INTEGER or SMALLINT. Float indicates a value of type FLOAT. Decimal indicates a value of type DECIMAL. CharacterString specifies a CHAR, VARCHAR, DATE, TIME, DATETIME, or INTERVAL value.
Search Conditions IN Predicate The first six of these have the same meaning that they have as fields in the SQLCA in application programs. Note that in procedures, sqlerrd2 returns the number of rows processed for all host languages. However, in application programs, sqlerrd3 is used in COBOL, Fortran, and Pascal, while sqlerr2 is used in C. ::activexact indicates whether a transaction is in progress or not.
Search Conditions IN Predicate If the indicator variable is >= 0 and PartNumber is one of '1123-P-01', '1733-AD-01', or :PartNumber, then the predicate evaluates to true. If the indicator variable is < 0, the rows containing the part numbers 1123-P-01 and 1733-AD-01 are selected; but no rows will be selected based upon the value in :PartNumber. EXEC SQL SELECT FROM WHERE IN Chapter 9 PartNumber PurchDB.
Search Conditions LIKE Predicate LIKE Predicate A LIKE predicate determines whether an expression contains a given pattern. The predicate evaluates to TRUE if an expression contains the pattern. If the NOT option is used, the predicate evaluates to TRUE if the expression does not contain the pattern.
Search Conditions LIKE Predicate % represents any string of zero or more characters; for example, THOMAS and TOM both satisfy the predicate NAME LIKE '%O%'. The _ and % symbols can be used multiple times and in any combination in a pattern. You cannot use these symbols literally within a pattern unless the ESCAPE clause appears, and the escape character precedes them. Note that they must be ASCII and not your local representations.
Search Conditions LIKE Predicate ProcedureParameter2 contains the escape character that is passed into or out of a procedure. ? indicates a dynamic parameter in a prepared SQL statement. The value of the parameter is supplied when the statement is executed. Description • If an escape character is not specified, then the _ or % in the pattern continues to act as a wildcard. No default escape character is available.
Search Conditions NULL Predicate NULL Predicate A NULL predicate determines whether a primary has the value NULL. The predicate evaluates to true if the primary is NULL. If the NOT option is used, the predicate evaluates to true if the primary is not NULL.
Search Conditions NULL Predicate LocalVariable contains a value in a procedure. ProcedureParameter contains a value that is passed into or out of a procedure. Built-inVariable is one of the following built-in variables used for error handling: • • • • • • • ::sqlcode ::sqlerrd2 ::sqlwarn0 ::sqlwarn1 ::sqlwarn2 ::sqlwarn6 ::activexact The first six of these have the same meaning that they have as fields in the SQLCA in application programs.
Search Conditions NULL Predicate Description The primary may be of any data type except LONG BINARY or LONG VARBINARY. Example Vendors with no personal contact named are identified. SELECT * FROM PurchDB.
Search Conditions Quantified Predicate Quantified Predicate A quantified predicate compares an expression with a list of specified values or a list of values derived from a subquery. The predicate evaluates to true if the expression is related to the value list as specified by the comparison operator and the quantifier. Scope SQL Data Manipulation Statements SQL Syntax Expression{= <> > >= < <=} {ALL ANY SOME} {SubQuery (ValueList)} Parameters Expression An expression specifies a value to be obtained.
Search Conditions Quantified Predicate SubQuery A subquery is a nested query. Subqueries are presented fully in the description of the SELECT statement. ValueList defines a list of values to be compared against the expression's value. The syntax for ValueList is: { USER CurrentFunction [ + - ] {Integer Float Decimal} ‘CharacterString’ OxHexadecimalString :HostVariable [[INDICATOR]:IndicatorVariable] ? :Local Variable :ProcedureParameter ::Built-inVariable LongColumnFunction StringFunction USER } [, ..
Search Conditions Quantified Predicate IndicatorVariable1 names an indicator variable, an input host variable whose value determines whether the associated host variable contains a NULL value: >= 0 the value is not NULL <0 the value is NULL LocalVariable contains a value in a procedure. ProcedureParameter contains a value that is passed into or out of a procedure. ? indicates a dynamic parameter in a prepared SQL statement. The value of the parameter is supplied when the statement is executed.
Search Conditions Quantified Predicate Example Get supplier numbers for suppliers who supply at least one part in a quantity greater than every quantity in which supplier S1 supplies a part. SELECT DISTINCT SP.SNO FROM SP WHERE SP.QTY > ALL ( SELECT SP.QTY FROM SP WHERE SP.SNO = 'S1') An alternative, possibly faster form of the query is: SELECT DISTINCT SP.SNO FROM SP WHERE SP.QTY > (SELECT MAX(SP.QTY) FROM SP WHERE SP.
Search Conditions Quantified Predicate 282 Chapter 9
SQL Statements A - D SQL Statement Summary 10 SQL Statements A - D Chapters 10, 11 and 12 describe all the SQL statements in alphabetical order, giving syntax, parameters, descriptions, authorization requirements, and examples for each statement. Examples often consist of groups of statements so you can see how each statement is related to other statements functionally. SQL Statement Summary SQL statements fall into four groups.
SQL Statements A - D SQL Statement Summary Table 10-1. SQL Statement Summary Group Category Tables Views Rules Statement Statement Use ALTER TABLE Adds to a table new columns and constraints, or drops constraints from a table, and assigns a table to a partition or removes it from a partition. RENAME COLUMN Defines a new name for an existing column. RENAME TABLE Defines a new name for an existing table. CREATE TABLE Defines a table and assigns it to a partition.
SQL Statements A - D SQL Statement Summary Table 10-1. SQL Statement Summary Group Category Statement Statement Use INSERT Adds a row to a single table or view. SELECT Retrieves data from one or more tables or views. UPDATE Changes the values of one or more columns in all rows of a specific table or view that satisfy a search condition. DROP MODULE Deletes a preprocessed module. EXECUTE Executes dynamically preprocessed statements. EXECUTE IMMEDIATE Defines and executes dynamic statements.
SQL Statements A - D SQL Statement Summary Table 10-1. SQL Statement Summary Group Category Statement Statement Use Executing procedures EXECUTE PROCEDURE Invokes a procedure. Other RAISE ERROR Causes a user-defined error to occur and specifies the error number and text to be raised. CREATE TABLE Defines the automatic locking strategy and implicit authority grants used for a table. LOCK TABLE Locks a table, explicitly overriding ALLBASE/SQL's automatic locking strategy.
SQL Statements A - D SQL Statement Summary Table 10-1. SQL Statement Summary Group Category Statement Statement Use BULK INSERT Inserts multiple rows into a single table. (See INSERT.) BULK SELECT Retrieves multiple rows not associated with a cursor. (See SELECT.) Cursor management ADVANCE Advances a procedure cursor. CLOSE Closes a cursor currently in the open state. DECLARE CURSOR Associates a cursor with a specific SELECT or EXECUTE PROCEDURE statement.
SQL Statements A - D SQL Statement Summary Table 10-1. SQL Statement Summary Group Category Statement WHENEVER Statement Use Specifies an action to be taken depending on the outcome of an SQL statement. Dynamically preprocessed queries Status messages DESCRIBE Obtains information about the results of a dynamic statement. EXECUTE EXECUTE IMMEDIATE PREPARE Refer to general-purpose statements. SQLEXPLAIN Retrieves a message describing the status of SQL statement execution.
SQL Statements A - D SQL Statement Summary Table 10-1. SQL Statement Summary Group Category Statement Statement Use STOP DBE Terminates all DBE sessions and causes a checkpoint to be taken, recovering log file space if nonarchive logging is in effect. TERMINATE QUERY Terminates a running Query. TERMINATE TRANSACTION Stops the transaction. TERMINATE USER Stops the DBE session for a specific user. DBEnvironment settings ENABLE RULES Turns rule checking on for the current DBEnvironment session.
SQL Statements A - D SQL Statement Summary Table 10-1. SQL Statement Summary Group Category Statement Statement Use SET DEFAULT DBEFILESET Sets a default DBEFileSet. DROP DBEFILESET Removes the definition of a DBEFileSet from the system catalog. Temporary sort space CREATE TEMPSPACE Defines and creates a temporary storage space. DROP TEMPSPACE Removes the definition of a temporary storage space from the system catalog.
SQL Statements A - D SQL Statement Summary Table 10-1. SQL Statement Summary Group Category Statement Statement Use Audit logging DISABLE AUDIT LOGGING Disables current audit logging for a session. Log comment LOG COMMENT Enters a user comment in the log file. ENABLE AUDIT LOGGING Enables audit logging for a session after being disabled. Recovery Rollback Rollforward START DBE Rolls back transactions that were incomplete the last time the DBEnvironment was shut down.
SQL Statements A - D SQL Statement Summary Table 10-1. SQL Statement Summary Group 292 Category Statement Statement Use Label Labels a statement in a procedure. IF Allows conditional execution of one or more statements within a procedure. RETURN Permits an exit from a procedure with an optional return code. WHILE Allows looping within a procedure.
SQL Statements A - D ADD DBEFILE ADD DBEFILE The ADD DBEFILE statement updates a row in SYSTEM.DBEFile to show the DBEFileSet with which the file is associated. Scope ISQL or Application Program SQL Syntax ADD DBEFILE DBEFileName TO DBEFILESET DBEFileSetName Parameters DBEFileName is the name of a DBEFile previously defined and created by the CREATE DBEFILE statement. DBEFileSetName is the name of a previously defined DBEFileSet. You can use the CREATE DBEFILESET statement to define DBEFileSets.
SQL Statements A - D ADD DBEFILE DBEFile to store rows of the index is created: CREATE DBEFILE ThatDBEFile WITH PAGES = 4, NAME = 'ThatFile', TYPE = INDEX ADD DBEFILE ThatDBEFile TO DBEFILESET Miscellaneous When the index is subsequently dropped, its file space can be assigned to another DBEFileSet.
SQL Statements A - D ADD TO GROUP ADD TO GROUP The ADD TO GROUP statement adds one or more users or groups, or a combination of users and groups, to an authorization group. Scope ISQL or Application Program SQL Syntax ADD {DBEUserID GroupName ClassName}[,...]TO GROUP TargetGroupName Parameters DBEUserID identifies a user to be added. You cannot specify the name of the DBECreator. GroupName identifies a group to be added. ClassName identifies a class to be added.
SQL Statements A - D ADD TO GROUP GRANT SELECT, UPDATE (BinNumber,QtyOnHand,LastCountDate) ON PurchDB.Inventory TO Warehse These two users will be able to start DBE sessions on PartsDBE, retrieve data from table PurchDB.Inventory, and update three columns in the table. ADD Clem, George TO GROUP Warehse Clem will no longer have any of the authorities associated with group Warehse. REMOVE Clem FROM GROUP Warehse Because this group does not own any database objects, it can be deleted.
SQL Statements A - D ADVANCE ADVANCE The ADVANCE statement is a procedure cursor manipulation statement. It is used in conjunction with procedures having one or more multiple row result sets to advance the position of an opened procedure cursor to the first or next query result set and to initialize information in the associated sqlda_type and sqlformat_type data structures.
SQL Statements A - D ADVANCE known from the DESCRIBE RESULT statement. • The ADVANCE statement cancels any current, active query result set. It can be used as an efficient way to throw away any unread rows resulting from the most recently executed multiple row result set SELECT statement in the procedure. The execution of the procedure continues with the next statement. Control returns to the application when the next multiple row result set statement is executed, or when procedure execution terminates.
SQL Statements A - D ALTER DBEFILE ALTER DBEFILE The ALTER DBEFILE statement changes the TYPE attribute of a DBEFile. Scope ISQL or Application Program SQL Syntax ALTER DBEFILE DBEFileName SET TYPE = {TABLE INDEX MIXED} Parameters DBEFileName specifies the DBEFile to be altered. TYPE = specifies the new setting of the DBEFile's TYPE attribute. The following are valid settings: TABLE Only data (table, LONG column, or HASH) pages can be stored in the DBEFile.
SQL Statements A - D ALTER DBEFILE Example CREATE DBEFILE ThisDBEFile WITH PAGES = 4, NAME = 'ThisFile', TYPE = TABLE CREATE DBEFILESET Miscellaneous ADD DBEFILE ThisDBEFile TO DBEFILESET Miscellaneous The DBEFile is used to store rows of a new table.
SQL Statements A - D ALTER TABLE ALTER TABLE The ALTER TABLE statement is used to add one or more new columns or constraints, to drop one or more constraints, or to reassign the table audit partition. This statement is also used to change the type of table access, updatability, and locking strategies. New columns are appended following already existing columns of a table. New column definitions must either allow null values or provide default values if the table is not empty.
SQL Statements A - D ALTER TABLE CREATE TABLE statement. CLUSTERING ON CONSTRAINT specifies that the named unique or referential constraint specified within the Column Definition be managed through a clustered index structure rather than nonclustered. The unique constraint's unique column list, or referential constraint's referencing column list, becomes the clustered key. ConstraintID specifies the unique or referential constraint on which clustering is to be applied.
SQL Statements A - D ALTER TABLE constraint was defined. SQL Syntax—SetTypeSpecification SET TYPE {PRIVATE PUBLICREAD PUBLIC PUBLICROW }[RESET AUTHORITY PRESERVE AUTHORITY] Parameters—SetTypeSpecification PRIVATE enables the table to be used by only one transaction at a time. Locks are applied at the table level. This is the most efficient option for tables that do not need to be shared because ALLBASE/SQL spends less time managing locks.
SQL Statements A - D ALTER TABLE SQL Syntax—SetPartitionSpecification SET PARTITION {PartitionName DEFAULT NONE } Parameters—SetPartitionSpecification PartitionName specifies the new partition of the table. DEFAULT specifies the new partition of the table to be the default partition of the DBEnvironment. If the default partition number is later changed, that change will automatically be recorded the next time an INSERT, UPDATE, or DELETE operation is executed on the table.
SQL Statements A - D ALTER TABLE • When altering the type of an existing table, you can also specify the option to preserve existing authority on the table or change the authority to the default for the new table type. If you specify RESET AUTHORITY, the following changes are made to the table authority: Table 10-2.
SQL Statements A - D ALTER TABLE Authorization You can issue this statement if you have ALTER or OWNER authority for the table or if you have DBA authority. To define added referential constraints, the table owner must have REFERENCES authority on the referenced table and referenced columns, own the referenced table, or have DBA authority. To specify a DBEFileSetName for a long column, the table owner must have TABLESPACE authority on the referenced DBEFileSet.
SQL Statements A - D Assignment (=) Assignment (=) The assignment statement is used in a procedure to assign a value to a local variable or procedure parameter. Scope Procedures only SQL Syntax {:LocalVariable :ProcedureParameter}= Expression; Parameters LocalVariable identifies the local variable to which a value is being assigned. The variable name has a : prefix. Local variables are declared in the procedure definition using the DECLARE statement.
SQL Statements A - D Assignment (=) Example :msg = 'Vendor number found in "Orders" table.'; :SalesPrice = :OldPrice; :NewPrice = :SalesPrice*.
SQL Statements A - D BEGIN BEGIN The BEGIN statement is a compound statement and defines a group of statements within a procedure. Scope Procedures only SQL Syntax BEGIN [Statement;][...] END; Parameters Statement is the statement or statements between the begin and end of the statement. Description • This statement can be used to improve readability. Authorization Anyone can use the BEGIN statement. Example CREATE PROCEDURE PurchDB.
SQL Statements A - D BEGIN ARCHIVE BEGIN ARCHIVE The BEGIN ARCHIVE statement in conjunction with the COMMIT ARCHIVE statement starts a new archive log file before a static backup is done to a DBEnvironment. However, this method is no longer recommended. The recommended approach to initiate archive logging and dynamic backup is to use the SQLUtil STOREONLINE command. Scope ISQL or Application Programs SQL Syntax BEGIN ARCHIVE Description Use of the BEGIN ARCHIVE statement is no longer recommended.
SQL Statements A - D BEGIN DECLARE SECTION BEGIN DECLARE SECTION The BEGIN DECLARE SECTION preprocessor directive indicates the beginning of the host variable eclaration section in an application program. Scope Application Programs Only SQL Syntax BEGIN DECLARE SECTION Description • This directive cannot be used interactively. • Use this directive in conjunction with the END DECLARE SECTION directive. Authorization You do not need authorization to use the BEGIN DECLARE SECTION statement.
SQL Statements A - D BEGIN WORK BEGIN WORK The BEGIN WORK statement begins a transaction and, optionally, sets one or more transaction attributes. Scope ISQL, Application Programs, or Procedures SQL Syntax BEGIN WORK [Priority][RR CS RC RU ][LABEL {‘LabelString’ :HostVariable}][[PARALLEL NO ]FILL] Parameters Priority is an integer from 0 to 255 specifying the priority of the transaction. Priority 127 is assigned if you do not specify a priority. ALLBASE/SQL uses the priority to resolve a deadlock.
SQL Statements A - D BEGIN WORK marked uniquely, allowing the DBA to easily identify the transaction being executed by any user at any moment. HostVariable is a host variable containing the LabelString. is used to optimize I/O performance when loading data and creating indexes. FILL PARALLEL FILL is used to optimize I/O performance for multiple, concurrent loads to the same table. The PARALLEL FILL option must be in effect for each load.
SQL Statements A - D BEGIN WORK • The following sequences of statements must be in the same transaction in a program: PREPARE and EXECUTE PREPARE, DESCRIBE, OPEN, FETCH USING DESCRIPTOR, EXECUTE, and CLOSE OPEN, FETCH, DELETE WHERE CURRENT, UPDATE WHERE CURRENT, and CLOSE (unless KEEP CURSOR is used) • To end your transaction, you must issue a COMMIT WORK or ROLLBACK WORK statement.
SQL Statements A - D BEGIN WORK 2. BEGIN WORK and set attributes Begin the transaction and set priority, isolation level, label name, and fill option: BEGIN WORK 32 CS LABEL 'xact1' FILL . . . Execute SQL statements. . . . Work is committed and the transaction ends. COMMIT WORK Begin another transaction and set priority, isolation level, and label name. Note that since a fill option is not specified, the default (NO FILL) is in effect. BEGIN WORK 64 RC LABEL 'xact2' . . . Execute SQL statements. . . .
SQL Statements A - D CHECKPOINT CHECKPOINT The CHECKPOINT statement causes an ALLBASE/SQL system checkpoint to be taken. Scope ISQL or Application Programs SQL Syntax CHECKPOINT [:HostVariable :LocalVariable :ProcedureParameter] Parameters HostVariable identifies an output host variable used to communicate the amount of log space available for use. The host variable is an integer. LocalVariable contains a value in a procedure.
SQL Statements A - D CHECKPOINT • For a brief interval while a checkpoint is being taken, SQL statements that modify the DBEnvironment continue to be accepted but their processing is temporarily suspended. This suspension occurs for the amount of time needed to write the log buffers and changed pages to permanent storage. Retrieval from the DBEnvironment is not suspended during a checkpoint. • Contents of the log buffer are also written to the log file(s) when a COMMIT WORK is executed.
SQL Statements A - D CHECKPOINT A program retrieves the number of free blocks of log space available. In a Pascal application program, declare a host variable. EXEC SQL begin declare section; hstfblk : integer; EXEC SQL end declare section; Submit a checkpoint with host variable to obtain free log space available.
SQL Statements A - D CLOSE CLOSE The CLOSE statement is used to close an open cursor. Scope Application Programs or Procedures SQL Syntax CLOSE CursorName [USING {[SQL]DESCRIPTOR {SQLDA Areaname} :HostVariable [[INDICATOR]:Indicator][,...]}] Parameters CursorName designates the open cursor to be closed. USING defines where to place return status and output parameters after closing a dynamic procedure cursor.
SQL Statements A - D CLOSE Description • When it applies to a select cursor (one that is declared for a SELECT statement), the CLOSE statement can be issued in an application program or in a procedure. When it applies to a procedure cursor (one that is declared for an EXECUTE PROCEDURE statement), the CLOSE statement can be issued only in an application program. • The CLOSE statement cannot be used in ISQL. • CLOSE returns an error if the cursor is not in the open state.
SQL Statements A - D CLOSE Statements for displaying a row to a user and accepting a new QtyOnHand value go here. The new value is stored in :NewQty. UPDATE PurchDB.Inventory SET QtyOnHand = :NewQty WHERE CURRENT OF NewQtyCursor . . .
SQL Statements A - D COMMIT ARCHIVE COMMIT ARCHIVE The COMMIT ARCHIVE statement in conjunction with the BEGIN ARCHIVE statement starts a new archive log file before a static backup is done to a DBEnvironment. However, this method is no longer recommended. The recommended approach to initiate archive logging and do a dynamic backup is to use the SQLUtil STOREONLINE command.
SQL Statements A - D COMMIT WORK COMMIT WORK The COMMIT WORK statement ends the current transaction. All changes made during the transaction are committed (made permanent). Scope ISQL or Application Programs SQL Syntax COMMIT WORK [RELEASE] Parameters RELEASE terminates your DBE session after the changes made during the transaction are committed. Specifying RELEASE has the same effect as issuing a COMMIT WORK statement followed by a RELEASE statement.
SQL Statements A - D COMMIT WORK Example Transaction begins. BEGIN WORK statement-1 SAVEPOINT :MyVariable statement-2 statement-3 Work of statements 2 and 3 is undone. ROLLBACK WORK TO :MyVariable Work of statement 1 is committed; the transaction ends.
SQL Statements A - D CONNECT CONNECT The CONNECT statement initiates a connection with a given DBEnvironment. This connection is the current connection. Any SQL statements issued apply to the current connection. Scope ISQL or Application Programs SQL Syntax CONNECT TO {‘DBEnvironmentName’ :HostVariable1 }[AS {‘ConnectionName’ :HostVariable2 }] [USER {‘UserID’ :HostVariable3}[USING :HostVariable4]] Parameters DBEnvironmentName identifies the DBEnvironment to be used.
SQL Statements A - D CONNECT Description • ALLBASE/SQL creates an implicit, brief transaction when the CONNECT statement is issued. • When the value of the autostart flag is ON, the CONNECT statement initiates a single-user DBE session if the DBECon file user mode is currently set to single and no other user is accessing the DBEnvironment. A multiuser DBE session is established if the DBECon file user mode is currently set to MULTI.
SQL Statements A - D CREATE DBEFILE CREATE DBEFILE The CREATE DBEFILE statement defines and creates a DBEFile and places a row describing the file in SYSTEM.DBEFile. A DBEFile is a file that stores tables, indexes, hash structures, and/or LONG data.
SQL Statements A - D CREATE DBEFILE rounded based on the DBEFileIncrSize provided. If you omit the MAXPAGES clause, the value defaults to the ALLBASE/SQL DBEFile maximum size. specifies the setting of the DBEFile's TYPE attribute. The following are valid settings: TYPE = TABLE Only data pages (table, HASH, or LONG) can be stored in the DBEFile. INDEX Only index pages can be stored in the DBEFile. MIXED A mixture of data and index pages can be stored in the DBEFile.
SQL Statements A - D CREATE DBEFILE • DBEFiles that contain hash tables are not expanded even though they were specified as expandable when created. Authorization You must have DBA authority to use this statement. hpdb must have write permission in the directory where the DBEFile will reside.
SQL Statements A - D CREATE DBEFILESET CREATE DBEFILESET The CREATE DBEFILESET statement defines a DBEFileSet. A DBEFileSet is a group of related DBEFiles; as such, it serves as a mechanism for allocating and deallocating file space for tables. Scope ISQL or Application Programs SQL Syntax CREATE DBEFILESET DBEFileSetName Parameters DBEFileSetName specifies the name to be given to the new DBEFileSet. Two DBEFileSets in the same DBEnvironment cannot have the same name.
SQL Statements A - D CREATE DBEFILESET Authorization You must have DBA authority to use this statement. Example The DBEFile is used to store rows of a new table.
SQL Statements A - D CREATE GROUP CREATE GROUP The CREATE GROUP statement defines a new authorization group. Scope ISQL or Application Programs SQL Syntax CREATE GROUP [Owner.]GroupName Parameters [Owner.]GroupName specifies the group name to be assigned to the new authorization group. The group name must conform to the syntax rules for basic names, described in the "Names" chapter. You can specify the owner of the new group if you have DBA authority.
SQL Statements A - D CREATE GROUP Example CREATE GROUP Warehse GRANT CONNECT TO Warehse GRANT SELECT, UPDATE (BinNumber,QtyOnHand,LastCountDate) ON PurchDB.Inventory TO Warehse These two users will be able to start DBE sessions for PartsDBE, retrieve data from table PurchDB.Inventory, and update three columns in the table. ADD Clem, George TO GROUP Warehse Clem will no longer have any of the authorities associated with group Warehse.
SQL Statements A - D CREATE INDEX CREATE INDEX The CREATE INDEX statement creates an index on one or more columns of a table and assigns a name to the new index. Scope ISQL or Application Programs SQL Syntax CREATE [UNIQUE][CLUSTERING]INDEX [Owner.]Indexname ON [Owner.]TableName ({ColumnName[ASC DESC]}[,...]) Parameters UNIQUE prohibits duplicates in the index. If UNIQUE is specified, each possible combination of index key column values can occur in only one row of the table.
SQL Statements A - D CREATE INDEX descending index. It is the same index as ascending. Therefore, SELECT statements that require data to be retrieved in descending order must specify ORDER BY columnID DESC. Description • If the table does not contain any rows, the CREATE INDEX statement enters the definition of the index in the system catalog and allocates a root page for it.
SQL Statements A - D CREATE INDEX Example This unique index ensures that all part numbers are unique. CREATE UNIQUE INDEX PurchDB.PartNumIndex ON PurchDB.Parts (PartNumber) This clustering index causes rows for order items associated with one order to be stored physically close together. CREATE CLUSTERING INDEX OrderItemIndex ON PurchDB.
SQL Statements A - D CREATE PARTITION CREATE PARTITION The CREATE PARTITION statement defines a partition to be used for audit logging purposes. Scope ISQL or Application Programs SQL Syntax CREATE PARTITION PartitionName WITH ID = PartitionNumber Parameters PartitionName specifies the logical name to be given to the new partition. Two partitions in the same DBEnvironment cannot have the same name. PartitionName may not be DEFAULT or NONE. PartitionNumber is an integer specifying the partition number.
SQL Statements A - D CREATE PARTITION for a DBEnvironment. However, the partition information is only used in audit log records. Thus, partition information will not be utilized in logging until the DBEnvironment has audit logging enabled. • Data partition information (including the default partition) appears in the system view SYSTEM.PARTITION. If the default partition is set to NONE, or is never defined, no row appears in SYSTEM.PARTITION for it.
SQL Statements A - D CREATE PROCEDURE CREATE PROCEDURE The CREATE PROCEDURE statement defines a procedure for storage in a DBEnvironment. A procedure may subsequently be executed through the firing of a rule by an INSERT, UPDATE, or DELETE statement, or by using the EXECUTE PROCEDURE statement or a procedure cursor. Scope ISQL or Application Programs SQL Syntax CREATE PROCEDURE [Owner.]ProcedureName [LANG = ProcLangName] [(ParameterDeclaration [, ParameterDeclaration][...
SQL Statements A - D CREATE PROCEDURE • IF...THEN...ELSEIF...ELSE...ENDIF • WHILE...DO...
SQL Statements A - D CREATE PROCEDURE Parameters—ParameterDeclaration ParameterName is the name assigned to a parameter in the procedure. No two parameters in the procedure can be given the same name. You can define no more than 1023 parameters in a procedure. ParameterType indicates what type of data the parameter will contain. The ParameterType cannot be a LONG data type. For a list of data types, refer to the "Data Types" chapter. ParameterLanguage specifies the language for the parameter.
SQL Statements A - D CREATE PROCEDURE Description • A procedure may be created through ISQL or through an application program. • A procedure result set is the set of rows returned by a procedure SELECT, FETCH, or REFETCH statement. • A select cursor (one declared for a SELECT statement) opened in an application program (i.e, outside the procedure) cannot be accessed within the procedure. However, a procedure can open and access its own select cursors.
SQL Statements A - D CREATE PROCEDURE • Built-in variables cannot be referenced in any SQL statement. They may be referenced in ASSIGNMENT, IF, WHILE, RETURN, and PRINT statements. Refer to the section "Using Procedures" in the chapter "Constraints, Procedures, and Rules" for more explanation of built-in variables. • Control flow and status statements, local variable declarations, parameter or local variable assignments, and labeled statements are allowed only within procedures.
SQL Statements A - D CREATE PROCEDURE • Indicator variables are not allowed or needed inside procedures. However, you can include an indicator variable with a host variable in supplying a value to a parameter in EXECUTE PROCEDURE, DECLARE CURSOR, OPEN, or CLOSE statements. Indicator variables specified for output host variables in CLOSE, DECLARE CURSOR, or EXECUTE PROCEDURE statements are set by ALLBASE/SQL.
SQL Statements A - D CREATE PROCEDURE 2. INSERT CREATE PROCEDURE PurchDB.ReportMonitor (Name CHAR(20) NOT NULL, Owner CHAR(20) NOT NULL, Type CHAR(10) NOT NULL) AS BEGIN INSERT INTO PurchDB.ReportMonitor VALUES (:Type, CURRENT_DATETIME, USER, :Name, :Owner); RETURN ::sqlcode; IN PurchFS; END 3.
SQL Statements A - D CREATE RULE CREATE RULE The CREATE RULE statement defines a rule and associates it with specific kinds of data manipulation on a particular table. The rule definition specifies the name of a procedure to be executed when the rule fires. Scope ISQL or Application Programs SQL Syntax CREATE RULE [Owner.]RuleName AFTER StatementType [,...][ON OF FROM INTO }[Owner.]TableName [REFERENCING {OLD AS OldCorrellationName NEW AS NewCorrelationName}[...
SQL Statements A - D CREATE RULE FiringCondition and ParameterValue to refer to the old values of the row (before it was changed by the DELETE or UPDATE statement). The default OldCorrelationName is OLD. If the StatementType is INSERT, an OldCorrelationName will refer to the new values of the row, since no old values are available.
SQL Statements A - D CREATE RULE Description • A rule may be created through ISQL or through an application program. • When a rule is created, information about the rule is stored in the system catalog, and may be examined through the following system views: SYSTEM.RULE, SYSTEM.RULECOLUMN, and SYSTEM.RULEDEF. • The FiringCondition and ParameterValue can reference both the unchanged and the changed values of the row being considered for the firing of a rule.
SQL Statements A - D CREATE RULE • If a CurrentFunction is used within the FiringCondition or a ParameterValue, it will be evaluated at the time of the statement that fires the rule. • Any value returned by the procedure with a RETURN statement is ignored by the rule and not returned to the statement firing the rule. • An EXECUTE PROCEDURE call from within a rule is different from one issued as a regular SQL statement.
SQL Statements A - D CREATE RULE Next, create three rules that invoke the procedure with parameters: CREATE RULE PurchDB.InsertReport AFTER INSERT TO PurchDB.Reports EXECUTE PROCEDURE PurchDB.ReportMonitor (NEW.ReportName, NEW.ReportOwner, 'INSERT') IN PurchDBFileSet; CREATE RULE PurchDB.DeleteReport AFTER DELETE FROMPurchDB.Reports EXECUTE PROCEDURE PurchDB.ReportMonitor (OLD.ReportName, OLD.ReportOwner, 'DELETE') IN PurchDBFileSet; CREATE RULE PurchDB.UpdateReport AFTER UPDATE TO PurchDB.
SQL Statements A - D CREATE SCHEMA CREATE SCHEMA The CREATE SCHEMA statement creates a schema and associates an authorization name with it. The schema defines a database containing tables, views, indexes, procedures, rules, and authorization groups with the same owner name. Entries are created in the system catalog views upon completion of the execution of this statement.
SQL Statements A - D CREATE SCHEMA AddToGroup adds one or more users, authorization groups, or combination of users and authorization groups to an authorization group. For complete syntax, refer to the ADD TO GROUP syntax. GrantStatement specifies the type of authorities for a table, view, or module. For complete syntax, refer to the GRANT syntax. Description • Note that a comma or semicolon is not allowed between the object definitions in the CREATE SCHEMA syntax.
SQL Statements A - D CREATE SCHEMA CREATE PUBLIC TABLE Events (SponsorClub CHAR(15), Event CHAR(30), Date DATE DEFAULT CURRENT_DATE, Time TIME, Coordinator CHAR(20), FOREIGN KEY (Coordinator, SponsorClub) REFERENCES Members (MemberName, Club) CONSTRAINT Events_FK) IN RecFS Chapter 10 353
SQL Statements A - D CREATE TABLE CREATE TABLE The CREATE TABLE statement defines a table. It also defines the locking strategy that ALLBASE/SQL uses automatically when the table is accessed and in some cases automatically issues a GRANT statement. It can also define the storage structure of the table and restrictions or defaults placed on values which the table's columns can hold. You can also use this statement to assign a table to a partition for audit logging purposes.
SQL Statements A - D CREATE TABLE TO PUBLIC. This gives any user with CONNECT authority the ability to read and modify the table as well as to alter the table and create indexes on it. To change this grant, use the REVOKE statement and the GRANT statement. The locking strategy remains unchanged, even if you change the grant. PUBLICROW enables the table to be read and updated by concurrent transactions. The locking unit is a row (tuple) in PUBLICROW tables.
SQL Statements A - D CREATE TABLE separately in another section below. UNIQUE HASH ON specifies a hash structure for the table. Only UNIQUE HASH structures may be created, and updates on hash key columns are not permitted (you must first delete, then insert the row with the new key value). HashColumnName specifies a column defined in the table that is to participate in the hash key of this table. PrimaryPages specifies the number of pages used as primary hash buckets.
SQL Statements A - D CREATE TABLE SQL Syntax—Column Definition ColumnName{ColumnDataType LongColumnType [IN DBEFileSetName2]} [LANG = ColumnLanguageName] [[NOT]CASE SENSITIVE] [DEFAULT{Constant USER NULL CurrentFunction}] [NOT NULL [{UNIQUE PRIMARY KEY} [CONSTRAINT ConstraintID]] REFERENCES RefTableName [(RefColumnName)][CONSTRAINT ConstraintID]] [...] CHECK (SearchCondition) [CONSTRAINT ConstraintID] [IN DBEFileSetName3] ][...
SQL Statements A - D CREATE TABLE UNIQUE | PRIMARY KEY specifies a unique constraint placed on the column. The table level constraint { UNIQUE | PRIMARY KEY } (ColumnName) is equivalent. See the discussion on table level unique constraints below. REFERENCES specifies a Referential Constraint placed on the column. This is equivalent to the table level constraint FOREIGN KEY (ColumnName) REFERENCES RefTableName [(RefColumnName)]. See the discussion on table level referential constraint below.
SQL Statements A - D CREATE TABLE Parameters—Referential Constraint (Table Level) FKColumnName [,...] identifies the referencing column list. Each referencing column shall be a column defined in the referencing table, and the same column name shall not be identified more than once. The number of referencing and referenced columns would be the same. The maximum number of columns in a referencing column list is 15. None of the columns may be a LONG data type.
SQL Statements A - D CREATE TABLE PRIVATE is assumed. For complete information on locking, refer to the chapter "Concurrency Control through Locks and Isolation Levels." • For nonhash tables, CREATE TABLE simply enters the new table's definition into the system catalog. Until you insert a row into the new table, the table does not occupy any storage. For hash tables, the number of primary pages is allocated at CREATE TABLE time.
SQL Statements A - D CREATE TABLE • Unique constraint indexes use space in this table's DBEFileSet; but referential constraint virtual indexes use space in the referenced table's DBEFileSet. • If the HASH or CLUSTERING ON CONSTRAINT clause is used without a constraint name, the PRIMARY KEY of the table is used. If a PRIMARY KEY is not defined, an error results. • At most 15 columns may be used in a unique or referential constraint key.
SQL Statements A - D CREATE TABLE • If no partition is specified, the table is placed in the DEFAULT partition. • To specify that a table is in no partition, the partition NONE can be specified. • Partitions can be created and tables placed in them without DATA audit logging being enabled for a DBEnvironment. However, the partition information is only used in audit log records.
SQL Statements A - D CREATE TABLE Now only the DBA and members of authorization group Accounting can access the table. Later, the accounting department manager is given control. TRANSFER OWNERSHIP OF PurchDB.SupplyPrice TO MgrAcct 2. Creating a table using constraints and LONG columns In this example, the tables are created with the PUBLIC option so as to be accessible to any user or program that can start a DBE session. RecDB.
SQL Statements A - D CREATE TABLE 3. Creating a table with a hash structure BEGIN WORK Statements to create a DBEFile and add it to a DBEFileSet should be in the same transaction as the statement to create the hash structure. This makes it impossible for other transactions to use the new DBEFile for hashing before the hash structure is created. CREATE DBEFILE PurchHashF1 WITH PAGES = 120, NAME = 'PurchHF1', TYPE = TABLE ADD DBEFILE PurchHashF1 TO DBEFILESET PurchFS CREATE PUBLIC TABLE PurchDB.
SQL Statements A - D CREATE TEMPSPACE CREATE TEMPSPACE The CREATE TEMPSPACE statement defines and creates a temporary storage space known as a TempSpace. A TempSpace is a location where ALLBASE/SQL creates temporary files to store temporary data when performing a sort, if disk space permits.
SQL Statements A - D CREATE TEMPSPACE file) should fit within the space available in the partition where the TempSpace is located. Use the HP-UX bdf command to determine the space available in a partition. • The location and characteristics of the TempSpace are stored in the system catalog. TempSpace files are physically created only when needed. When the TempSpace is no longer needed (the present task completes), the temporary file or files are deleted and the space is available for use again.
SQL Statements A - D CREATE VIEW CREATE VIEW The CREATE VIEW statement creates a view of a table, another view, or a combination of tables and views. Scope ISQL or Application Programs SQL Syntax CREATE VIEW [Owner.]ViewName [(ColumnName[,...])] AS QueryExpression [IN DBEFileSetName] [WITH CHECK OPTION [CONSTRAINT ConstraintID]] Parameters [Owner.]ViewName is the name to be assigned to the view. One owner cannot own more than one view with the same name.
SQL Statements A - D CREATE VIEW ConstraintID is the optional name of the view check constraint. Description • A view definition with * in the select list generates a view that refers to all the columns that exist in the base table(s) at the time the view is created. Adding new columns to the base tables does not cause these columns to be added to the view. • A view is said to be updatable when you can use it in DELETE, UPDATE, or INSERT statements to modify the base table.
SQL Statements A - D CREATE VIEW constraint. • You cannot use an ORDER BY clause when defining a view. • If the IN DBEFileSetName clause is specified, but the view owner does not have SECTIONSPACE authority for the specified DBEFileSet, a warning is issued and the default SECTIONSPACE DBEFileSet is used instead. (Refer to syntax for the GRANT statement and the SET DBEFILESET statement.
SQL Statements A - D CREATE VIEW If the table on which the view is based has any check constraints of its own, these conditions are checked along with the WITH CHECK OPTION of the view. CREATE VIEW RecDB.EventView (Event, Date) AS SELECT RecDB.Event, RecDB.Date FROM RecDB.
SQL Statements A - D DECLARE CURSOR DECLARE CURSOR The DECLARE CURSOR statement associates a cursor with a specified SELECT or EXECUTE PROCEDURE statement. Scope Application Programs and Procedures SQL Syntax DECLARE CursorName [IN DBEFileSetName]CURSOR FOR { {QueryExpression SelectStatementName}[FOR UPDATE OF {ColumnName}[,...] FOR READ ONLY] ExecuteProcedureStatement ExecuteStatementName } Parameters CursorName is the name assigned to the newly declared cursor.
SQL Statements A - D DECLARE CURSOR ExecuteProcedureStatement is a static EXECUTE PROCEDURE statement. It determines the rows and columns of the query result set or sets to be processed by means of a procedure cursor. The rows defined when you open and advance the cursor are called the active set of the cursor. ExecuteStatementName is specified when declaring a procedure cursor for a dynamically preprocessed EXECUTE PROCEDURE statement. It is the StatementName specified in the related PREPARE statement.
SQL Statements A - D DECLARE CURSOR view whose view definition contains a subquery. • For select cursors only, use the UPDATE statement with the CURRENT OF option to update columns; you can update the columns identified in the FOR UPDATE OF clause of the DECLARE CURSOR statement. The restrictions that govern updating via a select cursor are described above. • For select cursors only, use the DELETE WHERE CURRENT statement to delete a row in the active set.
SQL Statements A - D DECLARE CURSOR Examples 1. Deleting with a cursor The active set of this cursor will contain values for the OrderNumber stored in :OrdNum. DECLARE DeleteItemsCursor CURSOR FOR SELECT ItemNumber,OrderQty FROM PurchDB.OrderItems WHERE OrderNumber = :OrdNum Statements setting up a FETCH-DELETE WHERE CURRENT loop appear here. OPEN DeleteItemsCursor Statements for displaying values and requesting whether the user wants to delete the associated row go here.
SQL Statements A - D DECLARE CURSOR 3. Bulk fetching In some instances, using the BULK option is more efficient than advancing the cursor a row at a time through many rows, especially when you want to operate on the rows with non-ALLBASE/SQL statements. DECLARE ManyRows CURSOR FOR SELECT * FROM PurchDB.Inventory OPEN ManyRows BULK FETCH ManyRows INTO :Rows, :Start, :NumRow 4.
SQL Statements A - D DECLARE Variable DECLARE Variable The DECLARE Variable statement lets you define a local variable within a procedure. Local variables are used only within the procedure. Scope Procedures only SQL Syntax DECLARE { LocalVariable}[,...] VariableType {LANG = VariableLangName] [DEFAULT {Constant NULL CurrentFunction}][NOT NULL] Parameters LocalVariable specifies the name of the local variable. A variable name may not be the same as a parameter name in the same procedure.
SQL Statements A - D DECLARE Variable default value is longer than the target column, it is truncated. • For a BINARY column, if the specified default value is shorter in length than the target column, it is padded with zeroes. For a BINARY or VARBINARY column, if the specified default value is longer than the target column, it is truncated. Authorization Anyone can use the DECLARE statement in a procedure.
SQL Statements A - D DELETE DELETE The DELETE statement deletes a row or rows from a table. Scope ISQL or Application Programs SQL Syntax DELETE [WITH AUTOCOMMIT]FROM {[Owner.]TableName [Owner.]ViewName} [WHERE SearchCondition Parameters WITH AUTOCOMMIT executes a COMMIT WORK automatically at the beginning of the DELETE statement and also after each batch of rows is deleted. [Owner.]TableName designates a table from which any rows satisfying the search condition are to be deleted. [Owner.
SQL Statements A - D DELETE • The search condition is effectively executed for each row of the table or view before any row is deleted. If the search condition contains a subquery, each subquery in the search condition is effectively executed for each row of the table or view and the results used in the application of the search condition to the given row. If any executed subquery contains an outer reference to a column of the table or view, the reference is to the value of that column in the given row.
SQL Statements A - D DELETE statement (including execution of any procedure invoked due to a rule firing), the statement and any procedures invoked by any rules have no effect, regardless of the current DML ATOMICITY. Nothing has been altered in the DBEnvironment as a result of this statement or the rules it fired. Error messages are returned in the normal way. • When the WITH AUTOCOMMIT clause is not used, rows that qualify according to the SearchCondition are deleted internally in batches by ALLBASE/SQL.
SQL Statements A - D DELETE WHERE CURRENT DELETE WHERE CURRENT The DELETE WHERE CURRENT statement deletes the current row of an active set. The current row is the row pointed to by a cursor after the FETCH or REFETCH statement is issued. Scope Application Programs SQL Syntax DELETE FROM {[Owner.]TableName [Owner.]ViewName} WHERE CURRENT OF CursorName Parameters [Owner.]TableName designates the table from which you are deleting a row. [Owner.]ViewName designates a view based on a single table.
SQL Statements A - D DELETE WHERE CURRENT • If a referential constraint should be violated during processing of the DELETE statement, the row is not deleted (unless error checking is deferred and the violation is corrected before you COMMIT WORK). Refer to the discussion of the SET CONSTRAINTS statement in this chapter for more information.
SQL Statements A - D DELETE WHERE CURRENT table or you must have DBA authority. If you specify the name of a view, you must have DELETE or OWNER authority for that view or you must have DBA authority. Also, the owner of the view must have DELETE or OWNER authority with respect to the view's base tables, or the owner must have DBA authority. Example The active set of this cursor will contain values for the OrderNumber stored in :OrdNum.
SQL Statements A - D DESCRIBE DESCRIBE The DESCRIBE statement is used in an application program to pass information about a dynamic statement between the application and ALLBASE/SQL. It must refer to a statement preprocessed with the PREPARE statement.
SQL Statements A - D DESCRIBE Description • This statement cannot be used in ISQL, in COBOL and FORTRAN programs, or in procedures. • If StatementName refers to a SELECT statement, the DESCRIBE statement with the (default) OUTPUT option sets the sqld field of the associated sqlda_type data structure to the number of columns in the query result and sets the associated sqlformat_type data structure to each column's name, length, and data type.
SQL Statements A - D DESCRIBE • Detailed descriptions of how to use this statement are found in the "Using Dynamic Operations" chapters of the ALLBASE/SQL C Application Programming Guide and the ALLBASE/SQL Advanced Application Programming Guide, and in the "Using Parameter Substitution in Dynamic Statements" chapter and the "Using Procedures in Application Programs" chapter of the ALLBASE/SQL Advanced Application Programming Guide.
SQL Statements A - D DESCRIBE statement for input: DESCRIBE INPUT Dynamic1 USING SQL DESCRIPTOR SqldaIn If dynamic input parameters are present, the appropriate data buffer or host variables must be loaded with the values of any dynamic parameters.
SQL Statements A - D DESCRIBE The sqld of sqlda is set to 2, sqlmproc to 2, and sqloparm to 0. a. If sqldaout.sqlmproc <> 0 then, use procedure cursor processing statements to process multiple row result set(s) from the procedure. DESCRIBE RESULT cmd USING sqldaresult . . . DECLARE Dynamic1Cursor CURSOR FOR cmd OPEN Dynamic1Cursor USING sqldain . . . FETCH Dynamic1Cursor using DESCRIPTOR sqldaresult . . . CLOSE Dynamic1Cursor USING sqldaout . . . b.
SQL Statements A - D DISABLE AUDIT LOGGING DISABLE AUDIT LOGGING The DISABLE AUDIT LOGGING statement stops audit logging for the DBEnvironment session. It allows you to avoid creating audit log records for SQL statements while hard resynchronization is performed. Scope ISQL or Application Programs SQL Syntax DISABLE AUDIT LOGGING Description • This statement disables audit logging in the current session only. It suspends the generation of audit log records for any statements issued during the session.
SQL Statements A - D DISABLE RULES DISABLE RULES The DISABLE RULES statement turns rule checking off for the current DBEnvironment session. The statement is for DBA use in testing the operation of rules. Scope ISQL or Application Programs SQL Syntax DISABLE RULES Description • DISABLE RULES turns rule invocation off in the DBEnvironment for the current session or until the ENABLE RULES statement is issued. • The statement only affects the current SID (session id). Other users are not affected.
SQL Statements A - D DISCONNECT DISCONNECT The DISCONNECT statement terminates a connection with a DBEnvironment or terminates all DBEnvironment connections established within an application or an ISQL session. Scope ISQL or Application Programs SQL Syntax DISCONNECT {‘ConnectionName’ ‘DBEnvironmentName’ :HostVariable ALL CURRENT } Parameters ConnectionName is a string literal identifying the name associated with this connection.
SQL Statements A - D DISCONNECT automatic transaction will not be started when executing a DISCONNECT statement. • Any connection name associated with a disconnected connection can be reused. • A DISCONNECT CURRENT statement is equivalent to a RELEASE statement.
SQL Statements A - D DROP DBEFILE DROP DBEFILE The DROP DBEFILE statement removes the row describing a DBEFile from the SYSTEM.DBEFile. Scope ISQL or Application Programs SQL Syntax DROP DBEFILE DBEFileName Parameters DBEFileName is the name of the DBEFile to be dropped.
SQL Statements A - D DROP DBEFILE When the index is subsequently dropped, its file space can be assigned to another DBEFileSet. REMOVE DBEFILE ThatDBEFile FROM DBEFILESET Miscellaneous ADD DBEFILE ThatDBEFile TO DBEFILESET SYSTEM ALTER DBEFILE ThisDBEFile SET TYPE = MIXED Now you can use this DBEFile to store an index later if you need one. All rows are later deleted from the table, so you can reclaim file space.
SQL Statements A - D DROP DBEFILESET DROP DBEFILESET The DROP DBEFILESET statement removes the definition of a DBEFileSet from the system catalog. Scope ISQL or Application Programs SQL Syntax DROP DBEFILESET DBEFileSetName Parameters DBEFileSetName is the name of the DBEFileSet to be dropped. Description • Before you can drop a DBEFileSet, you must use the REMOVE DBEFile statement to remove any DBEFiles associated with the DBEFileSet. • You cannot DROP a default DBEFileSet.
SQL Statements A - D DROP DBEFILESET When the index is subsequently dropped, its file space can be assigned to another DBEFileSet. REMOVE DBEFILE ThatDBEFile FROM DBEFILESET Miscellaneous CREATE DBEFILESET OtherDBEFileSet ADD DBEFILE ThatDBEFile TO DBEFILESET OtherDBEFileSet The following statement allows you to use ThisDBEFile to store an index later, if you need one. ALTER DBEFILE ThisDBEFile SET TYPE = MIXED If, later, all rows are deleted from the table, you can reclaim file space.
SQL Statements A - D DROP GROUP DROP GROUP The DROP GROUP statement removes the definition of an authorization group from the system catalog. Scope ISQL or Application Programs SQL Syntax DROP GROUP GroupName Parameters GroupName identifies the authorization group to be dropped. Description • You cannot drop an authorization group if it owns any tables, views, modules, or authorization groups.
SQL Statements A - D DROP GROUP These two users will be able to start DBE sessions, retrieve data from table PurchDB.Inventory, and update three columns in the table. ADD Clem, George TO GROUP Warehse Clem no longer has any of the authorities associated with group Warehse. REMOVE Clem FROM GROUP Warehse Because this group does not own any database objects, it can be deleted. George no longer has any of the authorities once associated with the group.
SQL Statements A - D DROP INDEX DROP INDEX The DROP INDEX statement deletes the specified index. Scope ISQL or Application Programs SQL Syntax DROP INDEX [Owner.]IndexName][FROM][Owner.]TableName] Parameters [Owner.]IndexName is the name of the index to be dropped. It may include the name of the owner of the table which has the index. [Owner.]TableName is the name of the table upon which the index was created.
SQL Statements A - D DROP INDEX Example DROP INDEX PartsOrderedIndex FROM PurchDB.OrderItems Alternatively: DROP INDEX PurchDB.PartsOrderedIndex If you discover that an index does not improve the speed of data access, you can delete it. If applications change, you can redefine the index.
SQL Statements A - D DROP MODULE DROP MODULE The DROP MODULE statement deletes any sections associated with preprocessed SQL statements from the ALLBASE/SQL system catalog. Scope ISQL or Application Programs SQL Syntax DROP MODULE [Owner.]ModuleName [PRESERVE] Parameters [Owner.]ModuleName identifies the module to be dropped. PRESERVE causes ALLBASE/SQL to retain the module's authorization records.
SQL Statements A - D DROP MODULE Examples 1. Dropping preprocessed application programs A module for the application program MyProg is created and stored in the system catalog by one of the preprocessors. GRANT RUN ON MyProg TO PUBLIC DROP MODULE MyProg PRESERVE Authorization information for MyProg is retained, but the module is deleted from the system catalog. You can re-preprocess MyProg and not have to redefine its authorization. 2.
SQL Statements A - D DROP PARTITION DROP PARTITION The DROP PARTITION statement removes the definition of a partition for audit logging purposes. Scope ISQL or Application Programs SQL Syntax DROP PARTITION PartitionName Parameters PartitionName specifies the name of the partition to be dropped. Description • The partition being dropped must not have any tables associated with it. Use the ALTER TABLE SET PARTITION statement to remove any tables associated with it before dropping the partition.
SQL Statements A - D DROP PROCEDURE DROP PROCEDURE The DROP PROCEDURE statement deletes the specified procedure. Scope ISQL or Application Programs SQL Syntax DROP PROCEDURE [Owner.]ProcedureName [PRESERVE] Parameters [Owner.]ProcedureName specifies the name of the procedure that is to be dropped. PRESERVE specifies that EXECUTE authorities associated with the procedure should be retained in the system catalog.
SQL Statements A - D DROP RULE DROP RULE The DROP RULE statement deletes the specified rule. Scope ISQL or Application Programs SQL Syntax DROP RULE [Owner.]RuleName [FROM TABLE [Owner.]TableName] Parameters [Owner.]RuleName identifies the rule to be dropped. [Owner.]TableName identifies the table the rule is defined on. Description • If a TableName is specified, the rule must exist on that table or an error will be returned. • If no TableName is specified, the rule is located and dropped.
SQL Statements A - D DROP TABLE DROP TABLE The DROP TABLE statement deletes the specified table, including any hash structure or constraints associated with it, all indexes, views, and rules defined on the table, and all authorizations granted on the table. Scope ISQL or Application Programs SQL Syntax DROP TABLE [Owner.]Tablename Parameters [Owner.]TableName identifies the table to be dropped. Description • The DROP TABLE statement may invalidate stored sections.
SQL Statements A - D DROP TABLE CREATE UNIQUE INDEX VendorPerfIndex ON VendorPerf (OrderNumber) CREATE VIEW VendorPerfView (OrderNumber, ActualDelivQty, Remarks) AS SELECT OrderNumber, ActualDelivQty, Remarks FROM VendorPerf Only the table creator and members of authorization group Warehse can update table VendorPerf. GRANT UPDATE ON VendorPerf TO Warehse The table, the index, and the view are all deleted; and the grant is revoked.
SQL Statements A - D DROP TEMPSPACE DROP TEMPSPACE The DROP TEMPSPACE statement removes the definition of a temporary storage space (TempSpace) from the system catalog. Scope ISQL or Application Programs SQL Syntax DROP TEMPSPACE TempSpaceName Parameters TempSpaceName is the name of the TempSpace to be dropped. Description • If a TempSpace is dropped while temporary files currently exist under the path name it specifies, those files remain until the sort using them completes.
SQL Statements A - D DROP VIEW DROP VIEW The DROP VIEW statement deletes the definition of the specified view from the system catalog, all authorization granted on the view, and any view that references the dropped view. Scope ISQL or Application Programs SQL Syntax DROP VIEW [Owner.]ViewName Parameters [Owner.]ViewName identifies the view to be dropped. Description • This statement does not affect the base tables on which the views were defined.
SQL Statements A - D DROP VIEW 410 Chapter 10
SQL Statements E - R ENABLE AUDIT LOGGING 11 SQL Statements E - R Chapter 10, 11 and 12 describe all the SQL statements in alphabetical order, giving syntax, parameters, descriptions, authorization requirements, and examples for each statement. Examples often consist of groups of statements so you can see how each statement is related to other statements functionally.
SQL Statements E - R ENABLE AUDIT LOGGING COMMIT WORK; Reenable audit logging and continue.
SQL Statements E - R ENABLE RULES ENABLE RULES The ENABLE RULES statement turns rule checking on for the current DBEnvironment session. DBAs use it to tune the DBEnvironment and test the operation of rules. Scope ISQL or Application Programs SQL Syntax ENABLE RULES Description • ENABLE RULES returns the DBEnvironment session to its default behavior of firing all applicable rules. • The statement only affects the current SID (session id). Other users are not affected.
SQL Statements E - R END DECLARE SECTION END DECLARE SECTION The END DECLARE SECTION preprocessor directive indicates the end of the host variable declaration section in an application program. Scope Application Programs Only SQL Syntax END DECLAARE SECTION Description • This directive cannot be used interactively. • Use this directive in conjunction with the BEGIN DECLARE SECTION directive. Authorization You do not need authorization to use the END DECLARE SECTION statement.
SQL Statements E - R EXECUTE EXECUTE The EXECUTE statement causes ALLBASE/SQL to execute a statement that has been dynamically preprocessed by means of the PREPARE statement. Scope ISQL or Application Programs SQL Syntax EXECUTE {StatementName [Owner.
SQL Statements E - R EXECUTE presented separately below. INPUT is the default for any EXECUTE statement and can be specified, as required, for any type of prepared statement containing input dynamic parameters. OUTPUT is only allowed when the prepared statement is an EXECUTE PROCEDURE statement. It can be used when the statement contains output dynamic parameters. INPUT AND OUTPUT is only allowed when the prepared statement is an EXECUTE PROCEDURE statement.
SQL Statements E - R EXECUTE IndicatorVariable names an indicator variable, whose value determines whether the associated host variable contains a NULL value: >=0 the value is not NULL <0 the value is NULL Description • There must be a one to one mapping of the input and/or output parameters in a prepared statement and its associated EXECUTE statement. • INPUT is the default for any EXECUTE statement and can be specified, as required, for any type of prepared statement.
SQL Statements E - R EXECUTE authorization the dynamically preprocessed statement itself requires. Examples 1. Interactive execution isql=> PREPARE Statistics(1) > FROM 'UPDATE STATISTICS FOR TABLE PurchDB.Orders' isql=> PREPARE Statistics(2) > FROM 'UPDATE STATISTICS FOR TABLE PurchDB.OrderItems' Two sections for module Statistics are stored in the system catalog. isql=> EXECUTE Statistics(1) The statistics for table PurchDB.Orders are updated.
SQL Statements E - R EXECUTE If the prepared statement could be an EXECUTE PROCEDURE statement (sqld = zero on DESCRIBE OUTPUT) with dynamic output parameters, you must describe it for output: DESCRIBE OUTPUT Dynamic1 USING SQL DESCRIPTOR SqldaOut If only dynamic input parameters are present, the appropriate data buffer or host variables must be loaded with the values of any dynamic parameters.
SQL Statements E - R EXECUTE IMMEDIATE EXECUTE IMMEDIATE The EXECUTE IMMEDIATE statement dynamically prepares and executes an SQL statement. Scope ISQL or Application Programs SQL Syntax EXECUTE IMMEDIATE {‘String’ :HostVariable} Parameters String is the ALLBASE/SQL statement to be executed. HostVariable identifies a character-string host variable containing the ALLBASE/SQL statement to be executed. Description • When used interactively, a host variable cannot be specified.
SQL Statements E - R EXECUTE PROCEDURE EXECUTE PROCEDURE The EXECUTE PROCEDURE statement invokes a procedure. Scope ISQL or Application Programs Syntax EXECUTE PROCEDURE [:ReturnStatusVariable = ][Owner.]ProcedureName [ ( [ActualParameter ][, [ActualParameter ]][...]) ] Parameters ReturnStatusVariable is an integer host variable, or, for a prepared EXECUTE PROCEDURE statement, a dynamic parameter, that receives the return status from the procedure.
SQL Statements E - R EXECUTE PROCEDURE one was defined when the procedure was created). However, if a ParameterName is specified, use of a comma by itself is disallowed. OUTPUT specifies that the caller wishes to retrieve the output value of the parameter. OUTPUT must also have been specified for the corresponding parameter in the CREATE PROCEDURE statement. If OUTPUT is not specified, no output value is returned to the caller.
SQL Statements E - R EXECUTE PROCEDURE invalid sections in procedures. • You can PREPARE and EXECUTE an EXECUTE PROCEDURE statement containing dynamic parameters. You can use EXECUTE PROCEDURE inside an EXECUTE IMMEDIATE statement, provided the EXECUTE PROCEDURE statement includes neither dynamic parameters nor host variables. • If you do not specify OUTPUT for a parameter declared as OUTPUT in the CREATE PROCEDURE statement, no value is returned.
SQL Statements E - R FETCH FETCH The FETCH statement advances the position of an opened cursor to the next row of the active set and copies selected columns into the specified host variables or data buffer. The row to which the cursor points is called the current row.
SQL Statements E - R FETCH variable declarations only. DESCRIPTOR The DESCRIPTOR identifier defines where to place rows selected in accord with a dynamically preprocessed SELECT or EXECUTE PROCEDURE statement that has been described by a DESCRIBE statement. For a select cursor, specify the same location (SQLDA, area name, or host variable) as you specified in the DESCRIBE statement.
SQL Statements E - R FETCH Parameters — non-BULK HostVariableSpecification HostVariable identifies the host variable corresponding to one column in the row fetched. Indicator names the indicator variable, an output host variable whose value depends on whether the host variable contains a null value.
SQL Statements E - R FETCH Examples 1. Static update A cursor for use in updating values in column QtyOnHand is declared and opened. DECLARE NewQtyCursor CURSOR FOR SELECT PartNumber,QtyOnHand FROM PurchDB.Inventory FOR UPDATE OF QtyOnHand OPEN NewQtyCursor Statements setting up a FETCH-UPDATE loop appear next. FETCH NewQtyCursor INTO :Num :Numnul, :Qty :Qtynul Statements for displaying a row to and accepting a new QtyOnHand value from a user go here. The new value is stored in :NewQty. UPDATE PurchDB.
SQL Statements E - R FETCH Fetch the selected data into the data buffer. Additional rows are fetched with each execution of the FETCH statement until all rows have been fetched. See the ALLBASE/SQL for more detailed examples. FETCH Dynamic1Cursor USING DESCRIPTOR SQLDA Close the cursor to free the active set. CLOSE Dynamic1Cursor 4. Dynamic select cursor using host variables Assume that host variable Dynam1 contains a SELECT statement. The statement stored in :Dynam1 is dynamically preprocessed.
SQL Statements E - R GENPLAN GENPLAN The GENPLAN statement places the access plan generated by the optimizer for a SELECT, UPDATE, or DELETE statement into the pseudotable SYSTEM.PLAN. You can then view the access plan by issuing the following statement from within the same transaction: isql=> SELECT * FROM SYSTEM.PLAN; Scope ISQL or Application Programs SQL Syntax GENPLAN [WITH (HostVariableDefinition)] FOR {SQLStatement MODULE SECTION [Owner.]ModuleName(Section Number) PROCEDURE SECTION [Owner.
SQL Statements E - R GENPLAN NOTE GENPLAN checks only for syntax errors. It does not check for mismatched data types or other errors that may occur. In order to guarantee complete error checking, do not include a statement in GENPLAN unless it has previously run without errors.
SQL Statements E - R GENPLAN Table 11-2. GENPLAN WITH Clause Data Types — Pascal Pascal Host Variable Data Type Declaration GENPLAN WITH Clause SQL Data Type DataName : char; CHAR DataName : array [1..n] of char; CHAR(n) DataName : packed array [1..n] of char; CHAR(n) DataName : string[n]; VARCHAR(n) DataName : smallint; SMALLINT DataName : integer; INTEGER DataName : longreal; FLOAT DataName : real; REAL Table 11-3.
SQL Statements E - R GENPLAN Table 11-4.
SQL Statements E - R GENPLAN procedure, with no regard to any branching or looping constructs in the procedure. • Multiple sessions may issue the GENPLAN statement at the same time because each session has its own individual copy of SYSTEM.PLAN. • See the section "Using GENPLAN to Display the Access Plan" in the "SQL Queries" chapter for information on how to interpret the plan. • You cannot use GENPLAN with the SYSTEM or CATALOG views.
SQL Statements E - R GENPLAN 2. SQL statement simulating use of host variables in an application for the following query taken from an application: EXEC SQL SELECT INTO FROM WHERE AND PartName, VendorNumber, UnitPrice :PartName, :VendorNumber, :UnitPrice PurchDB.Parts p, PurchDB.SupplyPrice sp p.PartNumber = sp.PartNumber p.PartNumber = :PartNumber Remove INTO clause when placing the statement into GENPLAN.
SQL Statements E - R GOTO GOTO The GOTO statement permits a jump to a labeled statement within a procedure. Scope Procedures only SQL Syntax {GOTO GO TO}{Label Integer} Parameters Label specifies an identifier label for branching within the procedure. Integer specifies an integer label for branching within the procedure. Description The label or integer referred to in a GOTO statement is followed by a colon and a statement. Authorization Anyone can use the GOTO statement.
SQL Statements E - R GRANT GRANT The GRANT statement gives specified authority to one or more users or authorization groups. The following forms of the GRANT statement are described individually: • Grant table or view authority. • Grant RUN or EXECUTE authority. • Grant CONNECT, DBA, INSTALL, MONITOR, or RESOURCE authority. • Grant SECTIONSPACE or TABLESPACE authority for a DBEFileSet.
SQL Statements E - R GRANT ALTER grants authority to add new columns. ALTER authority is not allowed for a view. INDEX grants authority to create and drop indexes. INDEX authority is not allowed for a view. UPDATE grants authority to change data in existing rows. A list of column names can be specified to grant UPDATE authority only for specific columns. Omitting the list of column names grants authority to update all columns.
SQL Statements E - R GRANT Parameters — Grant RUN or EXECUTE Authority RUN grants authority to execute a specified module created interactively or by using a preprocessor. [Owner.]ModuleName specifies the name of the module for which authority is to be granted. EXECUTE grants authority to execute a specified procedure. [Owner.]ProcedureName specifies the name of the procedure for which authority is to be granted.
SQL Statements E - R GRANT Reference Manual for more details. TO The TO clause specifies the users, authorization groups, and classes to be given the specified authority. You must specify a login name when specifying a DBEUserID. Granting DBA authority to a class is useful when program modules are owned by a class.
SQL Statements E - R GRANT DBEFileSet. When a user specifies the IN DBEFileSet clause in a CREATE TABLE statement for either the table or for a LONG column, the owner of the table is checked for TABLESPACE authority on the DBEFileSet. If the user does not have TABLESPACE authority, the default TABLESPACE DBEFileSet is used instead (See the SET DEFAULT DBEFILESET statement.) This applies even if the user has DBA authority. DBEFileSetName designates the DBEFileSet for which authority is to be granted.
SQL Statements E - R GRANT 2. Using the WITH GRANT OPTION clause Clem and George have the SELECT privilege on the Inventory table as well as the ability to grant the SELECT privilege on this table to other users or a class with the WITH GRANT OPTION clause or to a group or PUBLIC (without the WITH GRANT OPTION). GRANT SELECT ON PurchDB.Inventory TO Clem, George WITH GRANT OPTION 3.
SQL Statements E - R IF IF The IF statement is used to allow conditional execution of one or more statements within a procedure. Scope Procedures only SQL Syntax IF Condition THEN [Statement;[...]] [ELSEIF Condition THEN [Statement; [...]]] [ELSE [Statement; [...
SQL Statements E - R IF Example Create a procedure to enter orders into different tables according to the size of the order: CREATE PROCEDURE OrderEntry (PartName CHAR(20) NOT NULL, Quantity INTEGER NOT NULL) AS BEGIN IF :Quantity < 100 THEN INSERT INTO SmallOrders VALUES (:PartName, :Quantity); ELSE INSERT INTO LargeOrders VALUES (:PartName, :Quantity); ENDIF; END Execute the procedure with different parameters. The first execution adds a row to the LargeOrders table.
SQL Statements E - R INCLUDE INCLUDE The INCLUDE preprocessor directive is used in an application program to declare the SQLCA or the SQLDA. Scope Application Programs Only SQL Syntax INCLUDE {SQLCA [[IS]EXTERNAL] SQLDA } Parameters SQLCA and SQLDA identify data structures with special predefined meaning as follows: • SQLCA is an area for ALLBASE/SQL output messages concerning the status of each SQL statement. • SQLDA is an area for use in conjunction with dynamic preprocessing of SELECT statements.
SQL Statements E - R INSERT INSERT The INSERT command adds rows to a table. The following two forms of the INSERT command are described individually: • The form used to add rows having values you define. You can add a single row or (in an application program) you can insert multiple rows using the bulk facility. There is special syntax for prepared INSERT and BULK INSERT statements that use dynamic parameter substitution. • The form used to add rows defined by a SELECT command.
SQL Statements E - R INSERT CREATE VIEW commands, if no column name list exists. Character and date/time literals must be in single quotes. SingleRowValues defines column values when you insert a single row. The syntax for SingleRowValues is presented separately below and includes single row syntax for statements that do not use dynamic parameter substitution. BulkValues defines values when you use the BULK option.
SQL Statements E - R INSERT ignored.) NOTE ? To be consistent with the standard SQL and to support portability of code, it is strongly recommended that you use a −1 to indicate a null value. However, ALLBASE/SQL interprets all negative indicator variable values to mean a null value. is a place holder for a dynamic parameter in a prepared SQL statement in an application program. The value of the dynamic parameter is supplied at run time. LocalVariable contains a value in a procedure.
SQL Statements E - R INSERT LongColumnIOString specifies the input and output locations for the LONG data. The specification for this string is given below. SQL Syntax — LongColumnIOString <{[PathName/]FileName %SharedMemoryAddress} [{> >> >!}[PathName/]{FileName CharSting$ CharString$ CharString} >%{SharedMemoryAddress $ } ] Parameters — LongColumnIOString < [PathName/] FileName is the location of the input file. <% SharedMemoryAddress is the shared memory address where the input is located.
SQL Statements E - R INSERT Table 11-5.
SQL Statements E - R INSERT SQL Syntax — BulkValues The following syntax applies only to statements that do not use dynamic parameter substitution. :Buffer [,:StartIndex [,:NumberOfRows]] Parameters — BulkValues Buffer NOTE StartIndex is a host array or structure containing rows that are the input for the INSERT command. This array contains elements for each column to be inserted and indicator variables for columns that can contain null values.
SQL Statements E - R INSERT is truncated. Refer to Chapter 7 , “Data Types,” for information on overflow and truncation of other data types. • No error or warning condition is generated by ALLBASE/SQL when a character or binary string is truncated during an INSERT operation. • Using the INSERT command with views requires that the views be based on queries that are updatable. See "Updatability of Queries" in Chapter 3 , “SQL Queries.
SQL Statements E - R INSERT assigned in the INSERT statement, or by the default value of the column if it is not included in the INSERT statement. • When a rule is fired by this statement, the rule's procedure is invoked after the changes have been made to the database for that row and all previous rows. The rule's procedure, and any chained rules, will thus see the state of the database with the current partial execution of the statement.
SQL Statements E - R INSERT input. • The BULK option used with host variables is available for C, COBOL, and FORTRAN applications. • The BULK option used with an sqlda_type data structure is available for C and Pascal applications. Authorization — Insert Rows with SingleRowValues and Bulk Values If you specify the name of a table, you must have INSERT or OWNER authority for that table or you must have DBA authority.
SQL Statements E - R INSERT defined with the NOT NULL attribute. Therefore ensure either that select list items are not null for any NOT NULL target column, or that the NOT NULL target columns have default values defined for them. Description — INSERT Rows Defined by a SELECT Command (Type 2 Insert) • You cannot use the ORDER BY clause in a Type 2 Insert. • You cannot insert into a LONG column with this kind of INSERT operation.
SQL Statements E - R INSERT • Rules defined with a StatementType of INSERT will affect all kinds of INSERT statements performed on the rules' target tables. When the INSERT is performed, ALLBASE/SQL considers all the rules defined for that table with the INSERT StatementType. If the rule has no condition, it will fire for all rows affected by the statement and invoke its associated procedure with the specified parameters on each row. If the rule has a condition, it will evaluate the condition on each row.
SQL Statements E - R INSERT Examples 1. Single-row insert INSERT INTO PurchDB.Vendors VALUES (9016, 'Secure Systems, Inc.', 'John Secret', '454-255-2087', '1111 Encryption Way', 'Hush', 'MD', '00007', 'discount rates are carefully guarded secrets') A new row is added to the PurchDB.Vendors table. 2. Bulk insert BULK INSERT INTO PurchDB.
SQL Statements E - R INSERT A new row is added to the PurchDB.Parts table based on the prepared INSERT statement called CMD. Row values are provided at run time, and an EXECUTE statement using two host variables is required to complete the INSERT. EXECUTE CMD USING :PartNumber, :PartName; 5. Bulk insert using dynamic parameters with host variables PREPARE CMD FROM 'BULK INSERT INTO PurchDB.Parts (PartNumber, PartName) VALUES(?,?);' Multiple rows can be added to the PurchDB.Parts table.
SQL Statements E - R Labeled Statement Labeled Statement A Label identifies an SQL statement that can be referred to within the procedure. Scope Procedures only SQL Syntax Label:Statement Parameters Label is an integer or a name which conforms to the SQL syntax rules for a basic name. Statement is the statement within a procedure to be labeled. Description • A label may appear only at the start of a ProcedureStatement that is not part of a compound statement.
SQL Statements E - R Labeled Statement INSERT INTO table2 VALUES (:value1, :param2); UPDATE table3 SET column1 = CURRENT_DATE WHERE column2 = :value1; IF ::sqlerrd2 < 1 THEN INSERT INTO table3 VALUES (CURRENT_DATE, :value1); ENDIF; ENDWHILE; loopexit: CLOSE cursor1; RETURN 0; errorexit: PRINT 'Procedure terminated due to error:'; PRINT :: sqlcode; END; EXECUTE PROCEDURE Process19; Chapter 11 459
SQL Statements E - R LOCK TABLE LOCK TABLE The LOCK TABLE statement provides a means of explicitly acquiring a lock on a table, to override the automatic locking provided by ALLBASE/SQL in accord with the CREATE TABLE locking modes. Scope ISQL or Application Programs SQL Syntax LOCK TABLE [Owner.]TableName IN {SHARE [UPDATE] EXCLUSIVE }MODE Parameters [Owner.]TableName specifies the table to be locked. SHARE allows other transactions to read but not change the table during the time you hold the lock.
SQL Statements E - R LOCK TABLE • LOCK TABLE can be useful in avoiding deadlocks by locking tables in a predetermined order. • To ensure data consistency, all locks are held until the end of the transaction, at which point they are released. For this reason no UNLOCK statement is available or necessary. Authorization You can issue this statement if you have SELECT or OWNER authority for the table or if you have DBA authority. Examples 1.
SQL Statements E - R LOG COMMENT LOG COMMENT The LOG COMMENT statement permits the entry of comments into the ALLBASE/SQL DBELog file. These comments can be extracted using the Audit Tool. Scope ISQL or Application Programs SQL Syntax LOG COMMENT {‘String’ :HostVariable :ProcedureParameter :ProcedureLocalVariable ? } Parameters String specifies the comment as a constant character string (up to 3996 bytes) HostVariable specifies the comment to be logged as a host variable.
SQL Statements E - R LOG COMMENT Example Generate a comment audit log record. LOG COMMENT 'Select From Table PurchDB.Parts'; SELECT PartNo FROM PurchDB.
SQL Statements E - R OPEN OPEN The OPEN statement is used in an application program or a procedure to open a cursor, that is, make the cursor and its associated active set available to manipulate a query result. Scope Application Programs and Procedures Only SQL Syntax OPEN CursorName [KEEP CURSOR [WITH LOCKS WITH NOLOCKS]] [USING { [SQL]DESCRIPTOR {SQLDA AreaName} HostVariableName[[INDICATOR]:IndicatorVariable][,...]} ] Parameters CursorName specifies the cursor to be opened.
SQL Statements E - R OPEN HostVariableName specifies a host variable name that at run time contains the data value that is assigned to an input dynamic parameter specified in the parameter list of a prepared SELECT or EXECUTE PROCEDURE statement. Host variables must be specified in the same order as the dynamic parameters in the prepared statement they represent. There must be a one to one correspondence between host variable names and the dynamic parameters.
SQL Statements E - R PREPARE PREPARE The PREPARE statement dynamically preprocesses an SQL statement for later execution. Scope ISQL or Application Programs SQL Syntax PREPARE [REPEAT]{StatementName [Owner.]ModuleName [(SectionNumber)]} [IN DBEFileSetName]FROM {‘String’ :HostVariable} Parameters REPEAT specifies the use of semi-permanent sections for queries.
SQL Statements E - R PREPARE See related ALLBASE/SQL statements in this manual and the appropriate ALLBASE/SQL application programming guide for details of these programming methods. [Owner.]ModuleName [(SectionNumber)] This option of the PREPARE statement is used interactively; it cannot be used in an application program. This option specifies an identifier to be assigned to the statement being preprocessed.
SQL Statements E - R PREPARE Description • You cannot use the PREPARE statement to preprocess the following statements: ADVANCE CLOSE DECLARE CURSOR DISCONNECT EXTRACT OPEN ROLLBACK WORK SET TRANSACTION STOP DBE TERMINATE USER BEGIN DECLARE SECTION COMMIT WORK DELETE WHERE CURRENT END DECLARE SECTION FETCH PREPARE SET CONNECTION SETOPT SQLEXPLAIN WHENEVER BEGIN WORK CONNECT DESCRIBE EXECUTE INCLUDE RELEASE SET SESSION START DBE UPDATE WHERE CURRENT • You cannot interactively prepare a SELECT statement.
SQL Statements E - R PREPARE Two sections for module Statistics are stored in the system catalog. EXECUTE Statistics(1) The statistics for table PurchDB.Orders are updated. EXECUTE Statistics(2) The statistics for table PurchDB.OrderItems are updated. DROP MODULE Statistics Both sections of the module are deleted. 2.
SQL Statements E - R PREPARE Load related dynamic parameter data into the input data buffer. FETCH Dynamic1Cursor USING DESCRIPTOR SqldaOut . . .
SQL Statements E - R PRINT PRINT The PRINT statement is used inside a procedure to store the content of user-defined strings, local variables, parameters, or built-in variables in the message buffer for display by ISQL or an application program. Scope Procedures only SQL Syntax PRINT {‘Constant’ :LocalVariable :Parameter ::Built-inVariable}; Parameters Constant is a string literal. LocalVariable is a local variable declared within the procedure.
SQL Statements E - R PRINT Description • The results of any PRINT statements issued during the execution of a procedure are placed in the ALLBASE/SQL message buffer, and may be displayed like other messages. In an application program, they can be retrieved with SQLEXPLAIN upon exiting the procedure. • The message number 5000 is used for all PRINT statements. Authorization Anyone can issue the PRINT statement.
SQL Statements E - R PRINT In COBOL: IF SQLCODE IS NOT ZERO OR SQLWARN0 = "W" PERFORM M100-DISPLAY-MESSAGE UNTIL SQLCODE IS ZERO AND SQLWARN0 = "W". . . . M100-DISPLAY-MESSAGE. EXEC SQL SQLEXPLAIN :SQLMESSAGE END-EXEC. DISPLAY SQLMESSAGE. M100-EXIT. EXIT.
SQL Statements E - R RAISE ERROR RAISE ERROR The RAISE ERROR statement causes an error to occur and causes the given error number to be put into the ALLBASE/SQL message buffer, together with the given error text. This statement is most useful within procedures invoked by rules, to cause the rule to fail and the statement firing the rule to have no effect. The effect of RAISE ERROR is to return with an error status; this statement can never "execute successfully.
SQL Statements E - R RAISE ERROR Parameters and local variables are only used within procedures. Host variables are only used within embedded SQL. Dynamic parameters are only used within dynamic SQL. Description • RAISE ERROR is for user-defined errors. The errors returned are application specific. • If ErrorNumber or ErrorText is NULL, an error is returned and the message is not generated. • ErrorNumber, if specified, must be greater than 0.
SQL Statements E - R REFETCH REFETCH The REFETCH statement allows Read Committed (RC) and Read Uncommitted (RU) transactions to acquire intended-for-update locks on data objects and to revalidate data before an update operation is issued. A refetch should always be done in RC and RU transactions before updating data to avoid update anomalies. Scope Application Programs Only SQL Syntax REFETCH CursorName INTO {:HostVariable [[ INDICATOR] :Indicator]}[,...] Parameters CursorName identifies a cursor.
SQL Statements E - R REFETCH • Because UPDATE WHERE CURRENT does not accept a DESCRIPTOR clause for input values, the REFETCH statement does not support the USING DESCRIPTOR clause found in the FETCH statement. • No BULK option is available. • This statement cannot be used interactively or in procedures. • If there is no current row during a REFETCH, you receive the following message in the SQLCODE: Row not found. Authorization You do not need authorization to use REFETCH.
SQL Statements E - R REFETCH Read Input. If an update is needed: begin read updatevalue; EXEC SQL REFETCH C1 INTO :Host2; if SQLCA.sqlcode <> 0 then begin EXEC SQL SQLEXPLAIN :sqlmessage; write sqlmessage; goto 1000; end; if Host1 = Host2 then EXEC SQL UPDATE T1 SET Col1 = updatevalue WHERE CURRENT OF C1; else write "data changed to ", Host2; end; 1000: until SQLCA.
SQL Statements E - R RELEASE RELEASE The RELEASE statement terminates your DBE session. Scope ISQL or Application Programs SQL Syntax RELEASE Description • A ROLLBACK is performed on any transactions in progress. • Any locks still held are released. Any cursors still open are closed, including kept cursors. • If the AUTOSTART option is in effect and your session is the only one in process, a RELEASE statement forces a checkpoint.
SQL Statements E - R REMOVE DBEFILE REMOVE DBEFILE The REMOVE DBEFILE statement removes the name of the DBEFileSet that the DBEFile was associated with from SYSTEM.DBEFile. Scope ISQL or Application Programs SQL Syntax REMOVE DBEFILE DBEFileName FROM DBEFILESET DBEFileSetName Parameters DBEFileName is the name of the DBEFile to be removed. The DBEFile must be empty (contain no tables, long data, or indexes). DBEFileSetName is the name of the DBEFileSet with which the DBEFile is currently associated.
SQL Statements E - R REMOVE DBEFILE NAME = 'ThatFile', TYPE = INDEX ADD DBEFILE ThatDBEFile to DBEFILESET Miscellaneous When the index is subsequently dropped, its file space can be assigned to another DBEFileSet. REMOVE DBEFILE ThatDBEFile FROM DBEFILESET Miscellaneous ADD DBEFILE ThatDBEFile TO DBEFILESET SYSTEM ALTER DBEFILE ThisDBEFile SET TYPE = MIXED Now you can use this DBEFile to store an index later if you need one. All rows are later deleted from the table, so you can reclaim file space.
SQL Statements E - R REMOVE FROM GROUP REMOVE FROM GROUP The REMOVE FROM GROUP statement removes one or more users or authorization groups from membership in a specified authorization group. Scope ISQL or Application Programs SQL Syntax REMOVE {DBEuserID GroupName ClassName}[,...]FROM GROUP [Owner.]TargetGroupName Parameters DBEUserID identifies a user to be removed from the specified authorization group. If you specify several names, any invalid names are ignored but valid names are removed.
SQL Statements E - R REMOVE FROM GROUP Example CREATE GROUP Warehse GRANT CONNECT TO Warehse GRANT SELECT, UPDATE (BinNumber,QtyOnHand,LastCountDate) ON PurchDB.Inventory TO Warehse ADD Clem, George TO GROUP Warehse These two users now are able to start DBE sessions on PartsDBE and PurchDB.Inventory, and to update three columns in the table. REMOVE Clem FROM GROUP Warehse Clem no longer has any of the authorities associated with group Warehse.
SQL Statements E - R RENAME COLUMN RENAME COLUMN The RENAME COLUMN statement defines a new name for an existing column in the DBEnvironment. Scope Application Programs SQL Syntax RENAME COLUMN [Owner.]TableName.ColumnName TO NewColumnName Parameters [Owner.]TableName.ColumnName designates the table column to be renamed. NewColumnName is the new column name.
SQL Statements E - R RENAME TABLE RENAME TABLE The RENAME TABLE statement defines a new name for an existing table in the DBEnvironment. Scope Application Programs SQL Syntax RENAME TABLE [Owner.]TableName TO NewTableName Parameters [Owner.]TableName designates the table to be renamed. NewTableName is the new table name. Description • All indexes, columns, default columns, constraints, referential authorization, rules, and user authorities tables dependent on a renamed table will be renamed.
SQL Statements E - R RESET RESET The RESET statement resets ALLBASE/SQL accounting and statistical data. Scope ISQL or Application Program SQL Syntax RESET {SYSTEM.ACCOUNT [FOR USER {* DBEUserID}] SYSTEM.COUNTER } Parameters SYSTEM.ACCOUNT is specified to reset accounting data for one user's DBE session or for all active sessions. * specifies all active sessions. This is the default if the FOR USER clause is omitted. DBEUserID identifies the user of a specific DBE session. SYSTEM.
SQL Statements E - R RETURN RETURN The RETURN statement permits you to exit from a procedure with an optional return code. Scope Procedures only SQL Syntax RETURN [ReturnStatus]; Parameters ReturnStatus is an integer value that is returned to the caller. The syntax is: {INTEGER :LocalVariable :ProcedureParameter ::Built-inVariable} Description • The RETURN statement causes the execution of the procedure to halt and causes control to return to the invoking user, application program, or rule.
SQL Statements E - R RETURN On returning from the procedure, test SQLCODE and Status both to determine whether an error occurred inside the procedure. if(sqlca.
SQL Statements E - R REVOKE REVOKE The REVOKE statement takes away authority that was previously granted by means of the GRANT statement.The following forms of the REVOKE statement are described individually: • Revoke table or view authority. • Revoke RUN or EXECUTE authority. • Revoke CONNECT, DBA, INSTALL, MONITOR, or RESOURCE authority. • Revoke SECTIONSPACE or TABLESPACE authority for a DBEFileSet.
SQL Statements E - R REVOKE UPDATE revokes authority to change data in existing rows. A list of column names can be specified to revoke UPDATE authority for only those columns if the columns were named in a GRANT statement UPDATE clause. Omitting the list of column names revokes authority to update all columns. REFERENCES revokes authority to reference columns in the table from foreign keys in another table.
SQL Statements E - R REVOKE SQL Syntax — Revoke RUN or EXECUTE or Authority REVOKE [RUN ON [Owner.]ModuleName EXECUTE ON PROCEDURE [Owner.] ProcedureName] FROM {{DBEUserID GroupName ClassName}[,...] PUBLIC } Parameters--Revoke RUN or EXECUTE Authority RUN revokes authority to access the DBEnvironment using the specified module. [Owner.]ModuleName specifies the module for which RUN authority is to be revoked. EXECUTE revokes authority to execute the specified procedure. [Owner.
SQL Statements E - R REVOKE RESOURCE revokes authority to create tables and authorization groups. FROM The FROM clause specifies the users, authorization groups, and classes whose authority is to be revoked. Description — Revoke CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority • The REVOKE statement may invalidate stored sections. Refer to the VALIDATE statement and to the ALLBASE/SQL Database Administration Guide for additional information on the validation of stored sections.
SQL Statements E - R REVOKE • If a REVOKE SECTIONSPACE statement completes successfully, the STOREDSECT table for the specified DBEFileSet is automatically dropped if it is empty and if no other user has SECTIONSPACE authority on the DBEFileSet. • The execution of this statement causes modification to the HPRDBSS.SPACEAUTH system catalog table. Refer to the ALLBASE/SQL Database Administration Guide "System Catalog" chapter.
SQL Statements E - R REVOKE DROP TABLE VendorPerf 3. Using CASCADE The DBA grants Clem privileges with the ability to grant them to others. Now Clem has all privileges on the Inventory table as well as the authority to grant any of the privileges to individual users or a class. GRANT ALL ON PurchDB.Inventory TO Clem WITH GRANT OPTION Clem grants Amanda all privileges on the Inventory table as well as the authority to grant any of the privileges to individual users or a class. GRANT ALL ON PurchDB.
SQL Statements E - R ROLLBACK WORK ROLLBACK WORK The ROLLBACK WORK statement undoes changes you have made to the DBEnvironment during the current transaction, releases locks held by the transaction, and closes cursors opened during the transaction. Other transactions active in this session are not affected.
SQL Statements E - R ROLLBACK WORK since the designated savepoint are undone. If any cursors opened with the KEEP CURSOR option were active in this transaction, the statement fails and the rollback is not done. In an application program or procedure, all open cursors are automatically closed. Any savepoints defined more recently than the designated savepoint are lost and become invalid. The designated savepoint is still valid and can be specified in a future ROLLBACK WORK statement.
SQL Statements S - Z SAVEPOINT 12 SQL Statements S - Z Chapters 10, 11 and 12 describe all the SQL statements in alphabetical order, giving syntax, parameters, descriptions, authorization requirements, and examples for each statement. Examples often consist of groups of statements so you can see how each statement is related to other statements functionally. SAVEPOINT The SAVEPOINT statement defines a savepoint within a transaction.
SQL Statements S - Z SAVEPOINT Description • Specify the savepoint number in the TO clause of a ROLLBACK WORK statement to roll back to a savepoint. • If a procedure invoked by a rule executes a COMMIT WORK statement, an error occurs. Authorization You do not need authorization to use the SAVEPOINT statement. Example Transaction begins. BEGIN WORK statement-1 SAVEPOINT :MyVariable statement-2 statement-3 Work of statements 2 and 3 is undone.
SQL Statements S - Z SELECT SELECT The SELECT statement retrieves data from one or more tables or views. The retrieved data is presented in the form of a table, called the result table or query result. The explanation of SQL Select syntax is broken down into several levels for easier understanding. An overview of the syntax at each of these levels is presented here starting with the Select Statement Level and continuing through the syntax for the FromSpec.
SQL Statements S - Z SELECT HostVariableSpecification — Without BULK Option {:HostVariable [ [ INDICATOR] :Indicator] ) [,...] FromSpec {TableSpec (FromSpec) FromSpec NATURAL [INNER LEFT [OUTER] RIGHT [OUTER]] JOIN {TableSpec (FromSpec)} FromSpec [INNER LEFT [OUTER] RIGHT [OUTER]] JOIN {TableSpec (FromSpec)}{ON SearchCondition3 USING (ColumnList)}} TableSpec [Owner.
SQL Statements S - Z SELECT QueryExpression is a complex expression specifying what is to be selected. The query expression is made up of one or more query blocks, as described in the chapter "SQL Queries." ORDER BY sorts the result table rows in order by specified columns. Specify the sort key columns in order from major sort key to minor sort key. You can specify as many as 1023 columns. The column specified in the ORDER BY parameter must be one of the columns appearing in the SELECT list.
SQL Statements S - Z SELECT Parameters — Subquery Level QueryExpression is the basic syntax of a query or SELECT statement. The query expression in a subquery may not contain any UNION or UNION ALL operations. Description — Subquery Level • Subqueries are used to retrieve data that is then used in evaluating a search condition. For example, get supplier numbers for the suppliers who supply the maximum quantity of part 'P1'. SELECT SP.SNO FROM SP WHERE SP.PNO = 'P1' AND SP.QTY = ( SELECT MAX(SP.
SQL Statements S - Z SELECT SELECT SP1.SNO FROM SP SP1 WHERE SP1.QTY = (SELECT MAX(SP2.QTY) FROM SP SP2 WHERE SP1.PNO = SP2.PNO) Note that the reference to SP1.PNO in the WHERE clause of the subquery is an outer reference. In this case, because both the outer query and the subquery refer to table SP, correlation names SP1 and SP2 are assigned to make the distinction between the outer and normal references.
SQL Statements S - Z SELECT — The union is derived by first inserting each row of T1 and each row of T2 into a result table and then eliminating any redundant rows unless ALL is specified. — The result of the union inherits the column names specified for T1. — The maximum number of query blocks within a query expression is 16. — Data types of corresponding columns in T1 and T2 must be comparable.
SQL Statements S - Z SELECT Table 12-1.
SQL Statements S - Z SELECT DISTINCT ensures that each row in the query result is unique. All null values are considered equal. You cannot specify this option if the select list contains an aggregate function with DISTINCT in the argument. This option cannot be used for a select list longer than 255 items. Avoid DISTINCT in subqueries since the query result is not changed, and it hinders rather than helping performance. SelectList tells how the columns of the result table are to be derived.
SQL Statements S - Z SELECT when aggregate functions are specified in the select list and you want to apply the function to groups of rows. You can specify as many as 1023 columns, unless the select list contains an aggregate function with the DISTINCT option, in which case you can specify as many as 254 columns. The syntax for the group column list in the GROUP BY clause follows: { [Owner.]TableName. CorrelationName.]ColumnName}[,...
SQL Statements S - Z SELECT computed by evaluating the specified expression for each row of the result table. The expression can be of any complexity. For example, it can simply designate a single column of one of the tables or views specified in the FROM clause, or it can involve aggregate functions, multiple columns, and so on. When you specify one or more aggregate functions in a select list, the only other entity you can specify is the name(s) of the column(s) you group by. [ [Owner.]Table.
SQL Statements S - Z SELECT Indicator names an indicator variable, an output host variable whose value (see following) depends on whether the host variable contains a null value: 0 the column's value is not NULL −1 the column's value is NULL >0 is truncated; the number indicates the data length before truncation The order of the host variables must match the order of their corresponding items in the select list.
SQL Statements S - Z SELECT one used to describe a three or more table outer join. NATURAL indicates that for both inner and outer joins, columns which are common to two tables being joined will be coalesced into a single column when the query result is returned. Also, ALLBASE/SQL will automatically identify and use the columns common to both tables to execute the join. When using the keyword NATURAL you do not use an ON SearchCondition3 clause or a USING (ColumnList) clause to specify the join columns.
SQL Statements S - Z SELECT qualified. The second type of predicate limits, for the associated join only, the rows which participate in the inner part of the join. Rows which are excluded from the inner part of the join will be added to those preserved in the outer part of the join. This predicate follows all general rules for search conditions as specified in the "Search Conditions" chapter.
SQL Statements S - Z SELECT an expression involving only constants. • In addition to specifying how the result columns are derived, the select list also controls their relative position from left to right in the result table. The first result column specified by the select list becomes the leftmost column in the result table. • The maximum number of columns in a query result is 1024, except when the query contains the DISTINCT option or is within a UNION query expression.
SQL Statements S - Z SELECT SearchCondition3 to limit the rows returned from the join, each column name used must unambiguously reference a column in one of the tables being joined, or must be an outer reference (as in the case of nested subqueries). • For a SELECT *, the columns contained in the result of the join are the same as the columns of the Cartesian product of the tables being joined. • For any other SELECT, the columns displayed will be those specified in the select list, in the order specified.
SQL Statements S - Z SELECT • If this result table is used as input to a NATURAL....JOIN clause or a JOIN USING (ColumnList) clause, and the column appearing twice in the result table is named as a join column in the JOIN USING (ColumnList) clause or is selected by ALLBASE/SQL as the join column in the NATURAL JOIN, an error will result. This happens because it is impossible to specify which of the two common columns in the result table is to participate in the following join.
SQL Statements S - Z SELECT • The select list aggregate functions are computed for each group. • ALLBASE/SQL allocates sort file space in /tmp, by default, or in the space specified using the CREATE TEMPSPACE statement. The space is deallocated once the statement completes. • The query block is considered updatable if, and only if, it satisfies the following conditions: — No DISTINCT, GROUP BY, or HAVING clause is specified in the outermost SELECT clause, and no aggregates appear in the select list.
SQL Statements S - Z SELECT SELECT PartNumber, AVG(UnitPrice) FROM PurchDB.SupplyPrice GROUP BY PartNumber The query result is the same as the query result for the previous SELECT statement, except it contains rows only for parts that can be delivered in fewer than 20 days. SELECT FROM GROUP BY HAVING PartNumber, AVG(UnitPrice) PurchDB.SupplyPrice PartNumber MAX(DeliveryDays) < 20 3. Joining This join returns names and locations of California suppliers.
SQL Statements S - Z SELECT cursor, you can use the BULK option to retrieve multiple rows. BULK SELECT * INTO :Items, :Start, :NumRow FROM PurchDB.Inventory 5. UNION Option Retrieves all rows from two Parts tables into a single query result ordered by PartNumber. PartNumber and PartValue are comparable; SalesPrice and Price are comparable. SELECT FROM UNION SELECT FROM ORDER BY PartNumber, SalesPrice P1988.Parts PartValue, Price P1989.Parts PartNumber 6.
SQL Statements S - Z SELECT Insert into table T, supplier names of each supplier who does not supply any part. INSERT INTO SELECT FROM WHERE T (SNO) SNO S NOT EXISTS (SELECT * FROM SP WHERE SP.SNO = S. SNO) Delete all suppliers from the supplier table who do not supply any parts. DELETE FROM S WHERE NOT EXISTS ( SELECT * FROM SP WHERE SP.SNO = S.
SQL Statements S - Z SET CONNECTION SET CONNECTION The SET CONNECTION statement sets the current connection within the list of connected DBEnvironments. Any SQL statements issued apply to the current connection. Scope ISQL or Application Programs SQL Syntax SET CONNECTION {‘ConnectionName’ :HostVariable } Parameters ConnectionName is a string literal identifying the name associated with this connection. This name must be unique for each DBEnvironment connection within an application or an ISQL session.
SQL Statements S - Z SET CONNECTION Example Establish two connections: CONNECT TO :PartsDBE AS 'Parts1' CONNECT TO :SalesDBE AS 'Sales1' At this point, Sales1 is the current connection. . . .
SQL Statements S - Z SET CONSTRAINTS SET CONSTRAINTS The SET CONSTRAINTS statement sets the UNIQUE, REFERENTIAL or CHECK constraint error checking mode. Scope ISQL or Application Programs SQL Syntax SET ConstraintType [,...]CONSTRAINTS {DEFERRED IMMEDIATE} Parameters ConstraintType identifies the type of constraint that is to be affected by the statement.
SQL Statements S - Z SET CONSTRAINTS the COMMIT WORK statement to be executed. • If errors remain when you COMMIT WORK, no matter to what level DML atomicity is set, error checking is done at the transaction level and the entire transaction will be rolled back. • When no constraint errors exist, SET CONSTRAINTS IMMEDIATE succeeds, and error checking thereafter occurs at the level in effect from the SET DML ATOMICITY statement.
SQL Statements S - Z SET CONSTRAINTS SET REFERENTIAL CONSTRAINTS IMMEDIATE You can correct the constraint errors so you can successfully COMMIT WORK. If you do not, the COMMIT WORK will roll back the entire transaction because of the remaining violations. Issue error correction statements, here. Constraint error checking is set to IMMEDIATE by the COMMIT WORK statement or a ROLLBACK WORK statement.
SQL Statements S - Z SET DEFAULT DBEFILESET SET DEFAULT DBEFILESET The SET DEFAULT statement is used to set the default DBEFileSet for stored sections or for tables and long columns associated with a DBEnvironment. Before initial issue of this statement, the SYSTEM DBEFileSet is the default. Scope ISQL or Application Programs SQL Syntax SET DEFAULT {SECTIONSPACE TABLESPACE } TO DBEFILESET DBEFileSetName FOR PUBLIC Parameters SECTIONSPACE sets the default DBEFileSet for stored sections.
SQL Statements S - Z SET DEFAULT DBEFILESET Authorization You must have DBA authority to set a DBEFileSet default.
SQL Statements S - Z SET DML ATOMICITY SET DML ATOMICITY The SET DML ATOMICITY statement sets the general error checking level in data manipulation statements. Scope ISQL or Application Programs SQL Syntax SET DML ATOMICITY AT {ROW STATEMENT} LEVEL Parameters ROW specifies that general error checking occurs at the row level. The term general error checking refers to any errors, for example, arithmetic overflows or constraint violation errors.
SQL Statements S - Z SET DML ATOMICITY — At COMMIT WORK, work done by statements within the transaction that executed without error will be written to the DBEnvironment. Within statements which generated errors at a specific row, work done on rows prior to the row generating the error will be written to the DBEnvironment; no work will be done from the erroneous row, forward.
SQL Statements S - Z SET DML ATOMICITY You can set the level back to statement level before the transaction ends. . . . Other statements are listed here. . . . COMMIT WORK If you have not already set error checking back to statement level, it is automatically set back to statement level when the transaction ends.
SQL Statements S - Z SET MULTITRANSACTION SET MULTITRANSACTION When you are using multiconnect functionality, the SET MULTITRANSACTION statement provides the capability of switching between single-transaction mode and multitransaction mode. Scope ISQL or Application Programs SQL Syntax SET MULTITRANSACTION {ON OFF} Parameters ON enables multiple implied or explicit BEGIN WORK statements to be active across the set of connected DBEnvironments. This is termed multitransaction mode.
SQL Statements S - Z SET MULTITRANSACTION Example Put single-transaction mode in effect: SET MULTITRANSACTION OFF Put multitransaction mode in effect: SET MULTITRANSACTION ON 530 Chapter 12
SQL Statements S - Z SETOPT SETOPT The SETOPT statement modifies the access optimization plan used by queries. Scope ISQL or Application Programs Syntax — SETOPT SETOPT {CLEAR GENERAL {ScanAccess JoinAlgorithm}[,...] BEGIN {GENERAL {ScanAccess JoinAlgorithm}}[,...] END Syntax — Scan Access [NO]{SERIALSCAN INDEXSCAN HASHSCAN SORTINDEX} Syntax — Join Algorithm [NO] {NESTEDLOOP NLJ SORTMERGE SMJ} Parameters CLEAR specifies that the access plan set by any previous SETOPT statement is to be cleared.
SQL Statements S - Z SETOPT Description • Use the SETOPT statement when you want to override the default access plan used in queries. • The SETOPT statement affects only those queries in the current transaction. When the transaction ends, the settings specified by SETOPT are cleared. • To view the plan specified by SETOPT, query the SYSTEM.SETOPTINFO view. • Use the GENPLAN command in ISQL to display the current access plan. • NLJ is equivalent to NESTEDLOOP, and SMJ is equivalent to SORTMERGE.
SQL Statements S - Z SETOPT The following two SETOPT statements are equivalent. SETOPT GENERAL HASHSCAN, NO SORTMERGE SETOPT BEGIN GENERAL HASHSCAN; GENERAL NO SORTMERGE; END In the following two SELECT statements, an index scan is performed upon the PartNumber because the PartNumber column is referenced in the ORDER BY and GROUP BY clauses. SETOPT GENERAL SORTINDEX SELECT PartNumber, UnitPrice FROM PurchDB.SupplyPrice ORDER BY PartNumber, UnitPrice SELECT PartNumber, AVG (UnitPrice) FROM PurchDB.
SQL Statements S - Z SET PRINTRULES SET PRINTRULES The SET PRINTRULES statement specifies whether rule names and statement types are to be issued as messages when the rules are fired during a DBEnvironment session. Scope ISQL or Application Programs SQL Syntax SET PRINTRULES [ON OFF] Parameters ON specifies that rule name and statement type should be issued as a message when the rule is fired. OFF specifies that rule name and statement type should not be issued as a message when the rule is fired.
SQL Statements S - Z SET PRINTRULES Authorization You must have DBA authority. Example The DBA enables the issuing of messages when rules fire. SET PRINTRULES ON The DBA issues statements that fire rules. INSERT INTO PurchDB.Parts VALUES (9213, 'Widget', 12.95) Rule PurchDB.InsertParts fired on INSERT statement. (DBWARN 2021) The DBA disables the issuing of messages when rules fire.
SQL Statements S - Z SET SESSION SET SESSION The SET SESSION statement sets one or more transaction attributes for the duration of a session to be applied to the next and subsequent transactions. These attributes include: isolation level, priority, user label, constraint checking mode, DML atomicity level, timeout rollback, user timeout, termination level, and fill options.
SQL Statements S - Z SET SESSION CS Cursor Stability. Means that your transaction uses locking strategies to assure cursor-level stability only. RC Read Committed. Means that your transaction uses locking strategies to ensure that you retrieve only rows that have been committed by some transaction. Read Uncommitted. Means that the transaction reads data without obtaining additional locks. Use the RU isolation level in applications in which the reading of uncommitted data is not of concern.
SQL Statements S - Z SET SESSION checking mode is reset to IMMEDIATE or the current transaction ends. IMMEDIATE specifies that constraint errors are checked when a statement executes. This is the default. STATEMENT specifies that error checking occurs at the statement level. This is the default. ROW specifies that error checking occurs at the row level. QUERY sets the action for timeouts or deadlocks to rollback the statement or query.
SQL Statements S - Z SET SESSION statements used to set transaction attributes. • When using RC or RU, you should verify the existence of a row before you issue an UPDATE statement. In application programs that employ cursors, you can use the REFETCH statement prior to updating. REFETCH is not available in ISQL. Therefore, you should use caution in employing RC and RU in ISQL if you are doing updates.
SQL Statements S - Z SET SESSION TRANSACTION, the whole transaction is aborted as a result of a timeout or deadlock. • When ON TIMEOUT ROLLBACK or DEADLOCK ROLLBACK is set to QUERY, only the SQL statement which has timed out will be rolled back. This means rolling back results of statements that modify the database and closing cursor for the cursor-related statements. (Cursor-related statements change the cursor position, and are not statements like UPDATE or DELETE WHERE CURRENT.
SQL Statements S - Z SET SESSION COMMIT WORK BEGIN WORK . . . Session isolation level CS takes effect. In the above example, the new transaction started on behalf of the user after the first COMMIT WORK has isolation level RU; cursor C1 has isolation RC; cursor C2 has isolation level CS; and cursor C3 has isolation level RU. Authorization You do not need authorization to use the SET SESSION statement.
SQL Statements S - Z SET TRANSACTION SET TRANSACTION The SET TRANSACTION statement sets one or more transaction attributes for a transaction. These attributes include: isolation level, priority, user label, constraint checking mode, timeout rollback, user timeout, termination level, and DML atomicity level.
SQL Statements S - Z SET TRANSACTION RC Read Committed. Means that your transaction uses locking strategies to ensure that you retrieve only rows that have been committed by some transaction. RU Read Uncommitted. Means that the transaction reads data without obtaining additional locks. REPEATABLE READ Same as RR. SERIALIZABLE Same as RR. CURSOR STABILITY Same as CS. READ COMMITTED Same as RC. READ UNCOMMITTED Same as RU.
SQL Statements S - Z SET TRANSACTION DEFERRED specifies that constraint errors are not checked until the constraint checking mode is reset to IMMEDIATE or the current transaction ends. IMMEDIATE specifies that constraint errors are checked when a statement executes. This is the default. STATEMENT specifies that error checking occurs at the statement level. This is the default. ROW specifies that error checking occurs at the row level.
SQL Statements S - Z SET TRANSACTION REFETCH statement prior to updating. REFETCH is not available in ISQL. Therefore, you should use caution in employing RC and RU in ISQL if you are doing updates. • Within a transaction, different isolation levels can be set for different DML statements.
SQL Statements S - Z SET TRANSACTION started on behalf of the user inherits the most recent transaction attributes of the old transaction. However, the KEEP cursor(s) inherit the isolation level attribute of the old transaction at the time the cursor(s) were opened. For example: BEGIN WORK RC . . . OPEN C1 KEEP CURSOR ... . . . SET TRANSACTION ISOLATION LEVEL CS . . . OPEN C2 KEEP CURSOR ... . . . SET TRANSACTION ISOLATION LEVEL RU . . . COMMIT WORK . . . OPEN C3 . . .
SQL Statements S - Z SET TRANSACTION Example Declare multiple cursors DECLARE C1 CURSOR FOR SELECT BranchNo FROM Branches WHERE TellerNo > :TellerNo DECLARE C2 CURSOR FOR SELECT BranchNo FROM Tellers WHERE BranchNo = :HostBranchNo FOR UPDATE OF Credit DECLARE C3 CURSOR FOR SELECT * FROM PurchDB.Parts Set the isolation level to RC. SET TRANSACTION ISOLATION LEVEL RC, PRIORITY 100, LABEL 'xact1' . . . Implicit BEGIN WORK with transaction isolation level RC. OPEN C1 FETCH C1 INTO :HostBranchNo1 . . .
SQL Statements S - Z SET USER TIMEOUT SET USER TIMEOUT The SET USER TIMEOUT statement specifies the amount of time the user will wait if the requested database resource is unavailable. Scope ISQL or Application Programs SQL Syntax SET USER TIMEOUT [TO] {{TimeoutValue :HostVariable}[SECONDS MINUTES] DEFAULT MAXIMUM } Parameters TimeoutValue is an integer literal greater than or equal to zero. If the TimeoutValue is not qualified by MINUTES, SECONDS is assumed.
SQL Statements S - Z SET USER TIMEOUT • The TimeoutValue may not exceed the maximum timeout value set by the database administrator. • The database administrator may specify the maximum and default timeout values with the SQLUtil ALTDBE command, or with the following SQL statements: • START DBE • START DBE NEW • START DBE NEWLOG • You may view the current maximum and default timeout values with the SQLUtil SHOWDBE command. • The SET USER TIMEOUT statement is not allowed in the PREPARE statement.
SQL Statements S - Z SQLEXPLAIN SQLEXPLAIN The SQLEXPLAIN statement places a message describing the meaning of a return code into a host variable. The text of messages comes from the ALLBASE/SQL message catalog. Scope Application Programs Only SQL Syntax SQLEXPLAIN :HostVariable Parameters HostVariable identifies a host variable used to hold an ALLBASE/SQL exception message. The message describes the meaning of a return code.
SQL Statements S - Z SQLEXPLAIN Example INCLUDE SQLCA SQLStatement1 The host variable named :Message contains a message characterizing the execution of SQLStatement1.
SQL Statements S - Z START DBE START DBE The START DBE statement establishes a connection with a given DBEnvironment and establishes a set of startup parameters that apply to this and all subsequent connections until all connections to the DBEnvironment have been terminated. Any startup parameters not explicitly specified are taken from the DBECon file. The changes are only temporary for START DBE parameters; use START DBE NEW to specify the start up parameters to be stored in the DBECon file in a new DBE.
SQL Statements S - Z START DBE See the "ALLBASE/SQL Limits" Appendix in the ALLBASE/SQL Database Administration Guide. LogBufferPages specifies the number of 512-byte log buffer pages to be used. You can request from 24 to 1024 log buffer pages, limited by the amount of storage available. The default number of log buffer pages is 24. MaxTransactions specifies the maximum number of concurrent transactions that can be concurrently active. You can specify a value in the range from 2 to 240.
SQL Statements S - Z START DBE CONNECT statement. • Timeout values set in the START DBE statement remain in effect only as long as there is a session established for connected DBEnvironments and do not modify the values stored in the DBECon file. • If no MAXIMUM TIMEOUT limit is specified, the MAXIMUM TIMEOUT limit stored in the DBECon file remains in effect. If no DEFAULT TIMEOUT value is specified, the DEFAULT TIMEOUT value stored in the DBECon file remains in effect.
SQL Statements S - Z START DBE NEW START DBE NEW The START DBE NEW statement configures and establishes a connection with a new DBEnvironment. It establishes a set of startup parameters that apply to this and all subsequent connections until all connections to the DBEnvironment have been terminated. Startup parameters are also stored in the DBECon file. Scope ISQL or Application Programs SQL Syntax — START DBE NEW START DBE ‘DBEnvironmentName’ [AS ‘ConnectionName’][MULTI]NEW [{DUAL AUDIT}|...
SQL Statements S - Z START DBE NEW ConnectionName must be unique for each DBEnvironment connection within an application. If a ConnectionName is not specified, DBEnvironmentName is the default. ConnectionName cannot exceed 128 bytes. MULTI indicates the DBEnvironment can be accessed by multiple users simultaneously. If omitted, the DBEnvironment can be accessed only in single-user mode. DUAL LOG causes ALLBASE/SQL to maintain two separate logs, preferably on different media.
SQL Statements S - Z START DBE NEW TimeoutValue is an integer literal greater than zero. If the TimeoutValue is not qualified by MINUTES, SECONDS is assumed. If representing seconds, TimeoutValue must be in the range of 1 to 2,147,483,647. If representing minutes, TimeoutValue must be in the range of 1 to 35,791,394. ControlBlockPages specifies the number of runtime control blocks to be allocated. The value specified is stored in the DBECon file.
SQL Statements S - Z START DBE NEW the LOG COMMENT statement returns an error. DATA is the default element. It causes audit log records to be done for any data operations (INSERT, UPDATE, or DELETE) on tables that are in an audit partition of the DBEnvironment other than NONE. (Tables can be specified to be in partition NONE and thus not participate in the audit logging process.
SQL Statements S - Z START DBE NEW AUDIT ELEMENTS as described above. DBEFile0Definition is a clause that provides the information ALLBASE/SQL needs to automatically create DBEFile0 and add it to the SYSTEM DBEFileSet. The syntax for this clause is presented separately below. If DBEFile0Definition is omitted, ALLBASE/SQL assumes the following: DBEFILE0 DBEFILE DBEFILE0 WITH PAGES = 150, NAME = 'DBEFile0' By default, DBEFile0 resides in the same directory as the DBECon file.
SQL Statements S - Z START DBE NEW SQL Syntax — DBELogDefinition LOG DBEFILE DBELog1ID [AND DBELog2ID] WITH PAGES = DBELogSize, NAME = 'SystemFileName2' [AND 'SystemFileName3'] Parameters — DBELogDefinition LOG DBEFILE describes the two log files if the DUAL LOG option is specified, or a single log file otherwise. If you give information for two log files but omit the DUAL LOG option, the information for the second log file is ignored.
SQL Statements S - Z START DBE NEW • The following additional parameters are stored in the DBECon file: • The autostart flag determines how DBE sessions are started. If the value of autostart is ON, a DBE session can be established by using the CONNECT statement. If the value of autostart is OFF, the START DBE statement must be used to start up a DBEnvironment; if the START DBE statement contains the MULTI option, other users establish DBE sessions with the CONNECT statement. Autostart is on by default.
SQL Statements S - Z START DBE NEW DBEFile0 may be changed. Refer to the ALLBASE/SQL Database Administration Guide for additional information. • If AUDIT LOG is specified, the clauses AUDIT NAME, DEFAULT PARTITION, and MAXPARTITIONS must also be specified. Further, if no AUDIT ELEMENTS are specified, DATA is used as a default. If no COMMENT PARTITION is specified, DEFAULT is assumed. The DEFAULT PARTITION or the COMMENT PARTITION can be specified as NONE.
SQL Statements S - Z START DBE NEWLOG START DBE NEWLOG The START DBE NEWLOG statement establishes a connection with a given DBEnvironment and creates one or two new log files for that DBEnvironment. It establishes a set of startup parameters that apply to this and all subsequent connections until all connections to the DBEnvironment have been terminated. Any start up parameters not explicitly specified are taken from the DBECon file except the enabling of audit logging.
SQL Statements S - Z START DBE NEWLOG be unique for each DBEnvironment connection within an application. If a ConnectionName is not specified, DBEnvironmentName is the default. ConnectionName cannot exceed 128 bytes. MULTI indicates the DBEnvironment can be accessed after log initialization in multiuser mode. ARCHIVE causes ALLBASE/SQL to initialize a new log in archive mode. If you omit this parameter, the log starts in nonarchive mode.
SQL Statements S - Z START DBE NEWLOG ControlBlockPages specifies the number of runtime control blocks to be allocated. Any value specified here temporarily overrides the value specified in the DBECon file. You can specify a value from 17 to 2,000 pages for this parameter. The default is 37 pages. The total number of data buffer pages and runtime control block pages cannot exceed 256 Mbytes. DefaultPartitionNumber Specifies the default partition number for the DBEnvironment.
SQL Statements S - Z START DBE NEWLOG or DELETE) on tables that are in an audit partition of the DBEnvironment other than NONE. (Tables can be specified to be in partition NONE and thus not participate in the audit logging process.
SQL Statements S - Z START DBE NEWLOG SQL Syntax — NewLogDefinition LOG DBEFILE DBELog1ID [AND DBELog2ID] WITH PAGES = DBELogSize, NAME = 'SystemFileName1' [AND 'SystemFileName2'] Parameters — NewLogDefinition LOG DBEFILE describes the two log files if the DUAL LOG option is specified, or a single log file otherwise. DBELog1ID and DBELog2ID are the basic names identifying the log files. DBELogSize specifies the number of 512-byte pages in one log file.
SQL Statements S - Z START DBE NEWLOG • If no MAXIMUM TIMEOUT limit is specified, the MAXIMUM TIMEOUT limit stored in the DBECon file remains in effect. If no DEFAULT TIMEOUT value is specified, the DEFAULT TIMEOUT value stored in the DBECon file remains in effect. • If MAXIMUM TIMEOUT = NONE, infinity (no timeout) is assumed. If DEFAULT TIMEOUT = MAXIMUM, the value of MAXIMUM TIMEOUT is assumed. The DEFAULT TIMEOUT value may not exceed the MAXIMUM TIMEOUT value.
SQL Statements S - Z START DBE NEWLOG • If an audit parameter is not specified in the statement, the audit parameter remains unchanged. The parameters AUDIT NAME, DEFAULT PARTITION, MAXPARTITIONS, COMMENT PARTITION, and AUDIT ELEMENTS can be changed at any time through the START DBE NEWLOG statement. • If AUDIT LOG is not specified in this statement, the default is that it is disabled.
SQL Statements S - Z START DBE NEWLOG New log files are reinitialized and audit logging is enabled. START DBE '../sampledb/PartsDBE' MULTI NEWLOG DUAL AUDIT LOG, AUDIT NAME = 'PrtsDBE1', DEFAULT PARTITION = 1, MAXPARTITIONS = 20, DATA AUDIT ELEMENTS, LOG DBEFILE PartsDBELog1 AND PartsDBELog2 WITH PAGES = 1000, NAME = 'PartsLg1' AND 'PartsLg2' You must create additional log files with the SQLUtil ADDLOG command.
SQL Statements S - Z STOP DBE STOP DBE The STOP DBE statement concludes ALLBASE/SQL operations and shuts down DBEnvironment operations. Scope ISQL or Application Programs SQL Syntax STOP DBE Description • Any transactions in progress are aborted, but their changes are not backed out until the START DBE statement is processed. • A checkpoint is taken. • Any locks still held are released. Any cursors still open are closed. Authorization You must have DBA authority to use this statement.
SQL Statements S - Z TERMINATE QUERY TERMINATE QUERY The TERMINATE QUERY statement terminates a running QUERY. Scope ISQL or Application Programs SQL Syntax TERMINATE QUERY FOR {CID ConnectionID XID TransactionID} Parameters CIDConnectionID identifies a specific connection in which the 'query' to be terminated is running. XIDTransactionID identifies a specific transaction in which the 'query' to be terminated is running. Description • A 'query' in this case refers to a command being executed.
SQL Statements S - Z TERMINATE TRANSACTION TERMINATE TRANSACTION The TERMINATE TRANSACTION statement terminates a given transaction. Scope ISQL or Application Programs SQL Syntax TERMINATE TRANSACTION FOR {CID ConnectionID XID TransactionID} Parameters CID ConnectionID identifies the specific connection in which the transaction to be terminated is running. XID TransactionID identifies a specific transaction to be terminated.
SQL Statements S - Z TERMINATE USER TERMINATE USER The TERMINATE USER statement terminates one or more DBE sessions associated with your user name or another user name. Scope ISQL or Application Programs SQL Syntax TERMINATE USER {DBEUserID SessionID CID ConnectionID} Parameters DBEUserID identifies the user to terminate all sessions for. Users currently on the system appear in the system view SYSTEM.USER. SessionID identifies a specific session to be terminated.
SQL Statements S - Z TERMINATE USER Example User1 starts a DBE session SessionID1 CONNECT TO '../sampledb/PartsDBE' User1 starts a DBE session SessionID2 CONNECT TO '../sampledb/PartsDBE' User2 starts a DBE session SessionID3 CONNECT TO '../sampledb/PartsDBE' User2 starts a DBE session SessionID4 CONNECT TO '../sampledb/PartsDBE' Both of User1's DBE sessions terminate. Either User1 or a DBA can enter this statement. TERMINATE USER User1 One of User2's DBE sessions terminates.
SQL Statements S - Z TRANSFER OWNERSHIP TRANSFER OWNERSHIP The TRANSFER OWNERSHIP statement makes a different user or authorization group or class name the owner of a table, view, procedure, or authorization group. Scope ISQL or Application Programs SQL Syntax TRANSFER OWNERSHIP OF {[TABLE][Owner.]TableName [VIEW][Owner.]ViewName PROCEDURE [Owner.]ProcedureName GROUP GroupName } TO NewOwnerName Parameters [TABLE][Owner.]TableName is the name of a table to transfer.
SQL Statements S - Z TRANSFER OWNERSHIP Transfers of ownership for tables involving referential constraints are subject to the following additional considerations: • The new owner must have the REFERENCES or DBA authorities necessary to allow ownership of a table containing such constraints. If the new owner does not have the needed authorities, the transfer is not allowed. • The name of any constraint or rule defined on the table must not already be in use by the new owner.
SQL Statements S - Z TRUNCATE TABLE TRUNCATE TABLE The TRUNCATE TABLE statement deletes all rows from the specified table. Scope ISQL, Application Programs, or Stored Procedures SQL Syntax TRUNCATE TABLE [Owner.]TableName Parameters [Owner.]TableName identifies the table whose rows are deleted. Description • Use the TRUNCATE TABLE when you want to delete all rows from a table, yet leave the table's structure intact.
SQL Statements S - Z TRUNCATE TABLE Example The following statement deletes all rows from the PurchDB.Parts table: TRUNCATE TABLE PurchDB.
SQL Statements S - Z UPDATE UPDATE The UPDATE statement updates the values of one or more columns in all rows of a table or in rows that satisfy a search condition. Scope ISQL or Application Programs SQL Syntax UPDATE {[Owner.]TableName [Owner.]ViewName) SET {ColumnName = {Expression ‘LongColumnIOString’ NULL } } [,...] [WHERE SearchCondition ] Parameters [Owner.]TableName specifies the table to be updated. [Owner.]ViewName specifies a view; the table on which the view is based is updated.
SQL Statements S - Z UPDATE the table before updating any row. Each subquery in the search condition is effectively executed for each row of the table, and the results used in the application of the search condition to the given row. If any executed subquery contains an outer reference to a column of the table, the reference is to the value of that column in the given row.
SQL Statements S - Z UPDATE column. If the rule has no condition, it will fire for all rows affected by the statement and invoke its associated procedure with the specified parameters on each row. If the rule has a condition, it will evaluate the condition on each row. The rule will fire on rows for which the condition evaluates to TRUE and invoke the associated procedure with the specified parameters for each row.
SQL Statements S - Z UPDATE >> specifies that output is appended to the following file name. If the file does not exist, it is created. >! specifies that output is placed in the following file name. If the file already exists, it is overwritten. >% SharedMemoryAddress is the shared memory address where the output is placed. >%$ is the shared memory address, determined by ALLBASE/SQL, where the output is placed.
SQL Statements S - Z UPDATE or your program is running. • The output device cannot be overwritten with a SELECT or FETCH statement unless you use the INSERT or UPDATE statement with the overwrite option. • LONG columns cannot be used as follows: • In a WHERE clause. • In a type II INSERT statement. • Remotely through ALLBASE/NET. • As hash or B-tree index key columns. • In a GROUP BY, ORDER BY, DISTINCT, or UNION clause. • In an expression. • In a subquery. • In aggregate functions (AVG, SUM, MIN, MAX).
SQL Statements S - Z UPDATE STATISTICS UPDATE STATISTICS The UPDATE STATISTICS statement updates the system catalog to reflect a table's current characteristics, such as the number of rows and average row size. ALLBASE/SQL uses these statistics to choose an optimal way to process a query. Scope ISQL or Application Programs SQL Syntax UPDATE STATISTICS FOR TABLE {[Owner.]TableName SYSTEM.SystemViewName} Parameters [Owner.]TableName identifies a table. SYSTEM.SystemViewName identifies a system view.
SQL Statements S - Z UPDATE STATISTICS • The only views this statement works for are system views. Refer to the ALLBASE/SQL Database Administration Guide for a description of the system views. • UPDATE STATISTICS cannot be used with pseudotables — SYSTEM.ACCOUNT, SYSTEM.CALL, SYSTEM.COUNTER, SYSTEM.TRANSACTION, and SYSTEM.USER. • You may find it convenient to use the VALIDATE statement after an UPDATE STATISTICS.
SQL Statements S - Z UPDATE WHERE CURRENT UPDATE WHERE CURRENT The UPDATE WHERE CURRENT statement updates the values of one or more columns in the current row associated with a cursor. The current row is the row pointed to by a cursor after the FETCH or REFETCH statement is issued. Scope Application Programs Only SQL Syntax UPDATE {[Owner.]TableName [Owner.]ViewName} SET { ColumnName = {Expression ‘LongColumnIOString’ NULL }}[,...] WHERE CURRENT OF CursorName Parameters [Owner.
SQL Statements S - Z UPDATE WHERE CURRENT Description • This statement cannot be used interactively and should not be used in conjunction with rows fetched using the BULK FETCH statement. • For constraint violations, the error handling behavior depends on the setting of the SET CONSTRAINTS statement. Refer to the discussion of this statement in this chapter. • No error or warning condition is generated by ALLBASE/SQL when a character or binary string is truncated during an UPDATE operation.
SQL Statements S - Z UPDATE WHERE CURRENT will not fire any otherwise applicable rules. When a subsequent ENABLE RULES is issued, applicable rules will fire again, but only for subsequent UPDATE WHERE CURRENT statements, not for those rows processed when rule firing was disabled.
SQL Statements S - Z UPDATE WHERE CURRENT Description — LongColumnIOString • The input device must have a permission allowing the login user to access it. For example, if the file belongs to the login user, permission must be at least 400. If the file belongs to another user, in a different group, permission must be at least 004. • When an output device has been specified and it exists prior to a SELECT or FETCH statement, ALLBASE/SQL does not change the file's owner or permission.
SQL Statements S - Z UPDATE WHERE CURRENT • In an expression. • In a subquery. • In aggregate functions (AVG, SUM, MIN, MAX). • As columns to which integrity constraints are assigned. • With the DEFAULT option of the CREATE or ALTER TABLE statements. • If no input device is specified, only output information of LONG columns is reset. • If no output device is specified, only value is reset.
SQL Statements S - Z VALIDATE VALIDATE The VALIDATE statement validates modules and procedures that have already been created. Scope ISQL or Application Programs SQL Syntax VALIDATE [FORCE DROP SETOPTINFO] {MODULE { {[Owner.]ModuleName} [,...] {SECTION [ Owner.]ModuleName (SectionNumber)} [,...] } PROCEDURE { {[Owner.]ProcedureName} [,...] {SECTION [Owner.]ProcedureName (SectionNumber)} [,...
SQL Statements S - Z VALIDATE STATISTICS, since UPDATE STATISTICS will invalidate stored sections. If you issue both statements during a period of low activity for the DBEnvironment, the optimizer will have current statistics on which to base its calculations, with minimal performance degradation. • A temporary section cannot be validated. • Users can specify the access plan of a query with the SETOPT statement.
SQL Statements S - Z VALIDATE Examples 1. Validating sections in a module ALLBASE/SQL validates sections at preprocessing time and run time. To validate a section before running your application, you can use the VALIDATE statement. To find the names of modules with invalid sections, use ISQL to query the SYSTEM.SECTION view. isql=> SELECT Name, Section FROM System.Section > WHERE valid = 0 and stype = 0; SELECT Name, Section FROM System.
SQL Statements S - Z WHENEVER WHENEVER WHENEVER is a directive used in an application program or a procedure to specify an action to be taken depending on the outcome of subsequent SQL statements. Scope Application Programs and Procedures Only SQL Syntax WHENEVER {SQLERROR SQLWARNING NOT FOUND } {STOP CONTINUE GOTO [:]Label GO TO [:]Label} Parameters SQLERROR refers to a test for the condition SQLCODE < 0. SQLWARNING refers to a test for the condition SQLWARN0 = 'W'.
SQL Statements S - Z WHENEVER whether or not you use the WHENEVER directive. • This directive cannot be used interactively or with dynamic parameters. Authorization You do not need authorization to use the WHENEVER directive. Example Execution of the program terminates if the CONNECT TO statement cannot be executed successfully. INCLUDE SQLCA . . . WHENEVER SQLERROR STOP CONNECT TO '.../sampledb/PartsDBE' . . . If a row does not qualify, control is passed to the statement labeled 9000. INCLUDE SQLCA . .
SQL Statements S - Z WHILE WHILE The WHILE statement is used to allow looping within a procedure. Scope Procedures only SQL Syntax WHILE Condition DO [Statement; [...]] ENDWHILE; Parameters Condition specifies anything that is allowed in a search condition except subqueries, column references, host variables, dynamic parameters, aggregate functions, string functions, date/time functions involving column references, long column functions, or TID functions.
SQL Statements S - Z WHILE OPEN QtyCursor; WHILE ::sqlcode <> 100 DO FETCH QtyCursor INTO :PartName, :Quantity PRINT :PartName; PRINT :Quantity; ENDWHILE; CLOSE QtyCursor; END; EXECUTE PROCEDURE ShowOrders; 598 Chapter 12
SQL Syntax Summary A SQL Syntax Summary This listing of SQL syntax differs from the previous version in the following ways: • The braces { } and brackets [ ] are shown in standard type size. • Commands and keywords are shown in BOLD UPPERCASE characters ADD DBEFILE ADD DBEFILE DBEFileName TO DBEFILESET DBEFileSetName ADD TO GROUP ADD {DBEUserID GroupName ClassName }[,...
SQL Syntax Summary AddConstraintSpecification ADD CONSTRAINT ({UniqueConstraint ReferentialConstraint CheckConstraint }[,...]) [CLUSTERING ON CONSTRAINT [ConstraintID1]] DropConstraintSpecification DROP CONSTRAINT {(ConstraintID [,...
SQL Syntax Summary CHECKPOINT CHECKPOINT [:HostVariable :LocalVariable :ProcedureParameter] CLOSE CLOSE CursorName [USING {[SQL] DESCRIPTOR {SQLDA Areaname} :HostVariable [[INDICATOR]:Indicator][,...
SQL Syntax Summary CREATE PARTITION CREATE PARTITION PartitionName WITH ID = PartitionNumber CREATE PROCEDURE CREATE PROCEDURE [Owner.]ProcedureName [LANG = ProcLangName] [(ParameterDeclaration [, ParameterDeclaration][...])] [WITH RESULT ResultDeclaration [, ResultDeclaration ][...]] AS BEGIN [ProcedureStatement][...
SQL Syntax Summary CREATE TABLE CREATE [PRIVATE PUBLICREAD PUBLIC PUBLICROW ]TABLE [Owner.]TableName [LANG = TableLanguageName] ({ColumnDefinition UniqueConstraint ReferentialConstraint CheckConstraint }[,...]) [UNIQUE HASH ON (HashColumnName [,...
SQL Syntax Summary CREATE VIEW CREATE VIEW [Owner.]ViewName [(ColumnName[,...])] AS QueryExpression [IN DBEFileSetName] [WITH CHECK OPTION [CONSTRAINT ConstraintID]] DECLARE CURSOR DECLARE CursorName [IN DBEFileSetName] CURSOR FOR { {QueryExpression SelectStatementName}[FOR UPDATE OF {ColumnName}[,...] FOR READ ONLY ] ExecuteProcedureStatement } ExecuteStatementName DECLARE Variable DECLARE { LocalVariable}[,...
SQL Syntax Summary DISCONNECT DISCONNECT {‘ConnectionName’ ‘DBEnvironmentName’ :HostVariable ALL CURRENT } DROP DBEFILE DROP DBEFILE DBEFileName DROP DBEFILESET DROP DBEFILESET DBEFileSetName DROP GROUP DROP GROUP GroupName DROP INDEX DROP INDEX [Owner.]IndexName [FROM [Owner.]TableName] DROP MODULE DROP MODULE [Owner.]ModuleName [PRESERVE] DROP PARTITION DROP PARTITION PartitionName DROP PROCEDURE DROP PROCEDURE [Owner.]ProcedureName [PRESERVE] DROP RULE DROP RULE [Owner.
SQL Syntax Summary ENABLE AUDIT LOGGING ENABLE AUDIT LOGGING ENABLE RULES ENABLE RULES END DECLARE SECTION END DECLARE SECTION EXECUTE EXECUTE{StatementName [Owner.
SQL Syntax Summary Non-BULK HostVariableSpecification {:HostVariable [[INDICATOR]:Indicator ] } [,...] GENPLAN GENPLAN [WITH (HostVariableDefinition)] FOR {SQLStatement MODULE SECTION [Owner.]ModuleName(Section Number) PROCEDURE SECTION [Owner.]ProcedureName(Section Number)} GOTO {GOTO GO TO}{Label Integer} GRANT GRANT {ALL [PRIVILEGES] {SELECT INSERT DELETE ALTER INDEX UPDATE [({ColumnName}[,...])] REFERENCES [({ColumnName}[,...])]}|,...|} ON {[Owner.]TableName [Owner.
SQL Syntax Summary Grant DBEFileSet Authority GRANT {SECTIONSPACE TABLESPACE } [,...] ON DBEFILESET DBEFileSetName TO {DBEUserID GroupName ClassName PUBLIC } [,...] IF IF Condition THEN [Statement;[...]] [ELSEIF Condition THEN [Statement; [...]]] [ELSE [Statement; [...]]] ENDIF; INCLUDE INCLUDE {SQLCA [[IS]EXTERNAL] SQLDA } INSERT - 1 [BULK]INSERT INTO { [Owner.]TableName [Owner.]ViewName} [({ColumnName}[,...
SQL Syntax Summary LongColumnIOString <{[PathName/]FileName %SharedMemoryAddress} [{> >> >!}[PathName/]{FileName CharSting$ CharString$ CharString} >%{SharedMemoryAddress $ } ] BulkValues :Buffer [ ,:StartIndex [, :NumberOfRows] ] Dynamic Parameter Substitution (? [,...] ) INSERT - 2 INSERT INTO {[Owner.]TableName [Owner.]ViewName}[(ColumnName [,...])] QueryExpression Labeled Statement Label: Statement LOCK TABLE LOCK TABLE [Owner.
SQL Syntax Summary PREPARE PREPARE [REPEAT]{StatementName [Owner.]ModuleName [(SectionNumber)]} [IN DBEFileSetName]FROM {‘String’ :HostVariable} PRINT PRINT {‘Constant’ :LocalVariable :Parameter ::Built-inVariable}; RAISE ERROR RAISE ERROR [ErrorNumber] [MESSAGE ErrorText] REFETCH REFETCH CursorName INTO {:HostVariable [[ INDICATOR] :Indicator]}[,...] RELEASE RELEASE REMOVE DBEFILE REMOVE DBEFILE DBEFileName FROM DBEFILESET DBEFileSetName REMOVE FROM GROUP REMOVE {DBEuserID GroupName ClassName}[,...
SQL Syntax Summary RETURN RETURN [ReturnStatus]; REVOKE Revoke Table or View Authority REVOKE {ALL [PRIVILEGES] [SELECT INSERT DELETE ALTER INDEX UPDATE [({ColumnName}[,...])] REFERENCES [({ColumnName}[,...])]]|,...|} ON {[Owner.]TableName [Owner.}ViewName } FROM {DBEUserID GroupName ClassName PUBLIC }[,...][CASCADE] Revoke RUN or EXECUTE Authority REVOKE [RUN ON [Owner.]ModuleName EXECUTE ON PROCEDURE [Owner.] ProcedureName] FROM {{DBEUserID GroupName ClassName}[,...
SQL Syntax Summary SAVEPOINT SAVEPOINT [:HostVariable :LocalVariable :ProcedureParameter] SELECT Select Statement Level [BULK]QueryExpression [ORDER BY {ColumnID [ASC DESC]}[,...]] Subquery Level (QueryExpression) Query Expression Level {QueryBlock (QueryExpression)}[UNION [ALL]{QueryBlock (QueryExpreession)}][...] Query Block Level SELECT [ALL DISTINCT] SelectList [INTO HostVariableSpecification] FROM FromSpec [,...
SQL Syntax Summary FromSpec {TableSpec (FromSpec) FromSpec NATURAL [INNER LEFT [OUTER] RIGHT [OUTER]] JOIN {TableSpec (FromSpec) } FromSpec [INNER LEFT [OUTER] RIGHT [OUTER]] JOIN {TableSpec (FromSpec)}{ON SearchCondition3 USING (ColumnList) } } TableSpec [Owner.] TableName [CorrelationName] SET CONNECTION SET CONNECTION {‘ConnectionName’ } :HostVariable SET CONSTRAINTS SET ConstraintType [,...
SQL Syntax Summary Scan Access [NO]{SERIALSCAN INDEXSCAN HASHSCAN SORTINDEX } Join Algorithm [NO] {NESTEDLOOP NLJ SORTMERGE SMJ } SET PRINTRULES SET PRINTRULES [ON OFF] SET SESSION SET SESSION {ISOLATION LEVEL {RR CS RC RU REPEATABLE READ SERIALIZABLE CURSOR STABILITY READ COMMITTED READ UNCOMMITTED :HostVariable1 } PRIORITY {Priority :HostVariable2} LABEL {‘LabelString’ :HostVariable3} ConstraintType [,...
SQL Syntax Summary SET TRANSACTION SET TRANSACTION {ISOLATION LEVEL {RR CS RC RU REPEATABLE READ SERIALIZABLE CURSOR STABILITY READ COMMITTED READ UNCOMMITTED :HostVariable1 } PRIORITY {Priority :HostVariable2} LABEL {‘LabelString’ :HostVariable3} ConstraintType [,...
SQL Syntax Summary START DBE START DBE ‘DBEnvironmentName’ [AS ‘ConnectionName’][MULTI] [BUFFER = (DataBufferPages, LogBufferPages) TRANSACTION = MaxTransactions MAXIMUM TIMEOUT = {TimeoutValue [SECONDS MINUTES] NONE } DEFAULT TIMEOUT = {TimeoutValue [SECONDS MINUTES] MAXIMUM } ]|,...| RUN BLOCK = ControlBlockPages START DBE NEW START DBE ‘DBEnvironmentName’ [AS ‘ConnectionName’][MULTI]NEW [{DUAL AUDIT}|...
SQL Syntax Summary START DBE NEWLOG START DBE ‘DBEnvironmentName’ [AS ‘ConnectionName’][MULTI]NEWLOG [{ARCHIVE DUAL AUDIT}|...
SQL Syntax Summary TERMINATE USER TERMINATE USER {DBEUserID SessionID CID ConnectionID} TRANSFER OWNERSHIP TRANSFER OWNERSHIP OF {[TABLE][Owner.]TableName [VIEW][Owner.]ViewName PROCEDURE [Owner.]ProcedureName GROUP GroupName } TO NewOwnerName TRUNCATE TABLE TRUNCATE TABLE [Owner.]TableName UPDATE UPDATE {[Owner.]TableName [Owner.]ViewName } SET { ColumnName = { Expression ‘LongColumnIOString’ NULL } } [,...
SQL Syntax Summary LongColumnIOString { [<{[PathName/]FileName %SharedMemoryAddress}] [{> >> >!}[PathName/]{FileName CharString$ CharString$ CharString} >% {SharedMemoryAddress $ } ] } |...| VALIDATE VALIDATE [FORCE DROP SETOPTINFO] {MODULE { {[Owner.]ModuleName} [,...] {SECTION [Owner.]ModuleName (SectionNumber)} [,...] } PROCEDURE { {[Owner.]ProcedureName} [,...] {SECTION [Owner.]ProcedureName (SectionNumber)} [,...
SQL Syntax Summary 620 Appendix A
ISQL Syntax Summary B ISQL Syntax Summary ISQL is the interactive interface to ALLBASE/SQL. Some, but not all, ALLBASE/SQL statements can be entered interactively as ISQL commands. CHANGE C[HANGE]Delimiter OldString Delimiter NewString Delimiter [@] DO DO [CommandNumber CommandString] EDIT ED[IT][FileName] END EN[D] ERASE ER[ASE]FileName EXIT EX[IT] EXTRACT EXTRACT{ MODULE[Owner.]ModuleName[,...] SECTION [Owner.]ModuleName(SectionNumber) [,...
ISQL Syntax Summary HOLD HO[LD]{SQLStatement ISQLCommand}[EscapeCharacter;{SQLStatement ISQLCommand}][...] INFO IN[FO]{[Owner.]TableName [Owner.]ViewName} INPUT INP[UT]{[Owner.]TableName [Owner.]ViewName} (ColumnName[,ColumnName][...]) { (Value[,Value][...])[ROLLBACK WORK COMMIT WORK] } [...
ISQL Syntax Summary ExternalInputSpec { ColumnName StartingLocation Length [NullIndicator] } [...]E[ND] [FormatType] RECALL REC[ALL]{C[URRENT] F[ILE]FileName H[ISTORY] CommandNumber} REDO RED[O][ CommandNumber CommandString] Subcommands B D E H I L R X +[n] -[n] Break Delete Exit Help Insert List Replace Execute Forward n Backward n Return Next Line RENAME REN[AME]OldFileName NewFileName SELECTSTATEMENT SelectStatement;[PA[USE];][BrowseOption;][...
ISQL Syntax Summary ES[CAPE] Character EXIT[_ON_DBERR] ON | OFF EXIT_ON_DBWARN ON | OFF FL[AGGER] FlaggerName F[RACTION] Length N[ULL] [Character] OU[TPUT] FileName OW[NER] OwnerName LOAD_B[UFFER] BufferSize PA[GEWIDTH] PageWidth PR[OMPT] PromptString SQLGEN SQLG[EN] SQLUTIL SQLU[TIL] START STA[RT][CommandFileName][ (Value[,Value][...] ) ] STORE STO[RE] FileName [R[EPLACE] ] SYSTEM {S[YSTEM] ! }[HP-UXCommand] UNLOAD U[NLOAD] TO { E[XTERNAL] I[NTERNAL]}OutputFileName FROM {[Owner.]TableName [Owner.
Sample DBEnvironment C Sample DBEnvironment The DBEnvironment used in examples throughout the ALLBASE/SQL manual set is called PartsDBE. Your installation package includes the necessary files to create a working version of this DBEnvironment so that users can try the examples while learning about the features of ALLBASE/SQL. Also included is a set of sample applications that access PurchDB, the main database in PartsDBE.
Sample DBEnvironment Installing the Files for PartsDBE Installing the Files for PartsDBE You can install the files for setting up PartsDBE when you install ALLBASE/SQL or at a later time. Refer to the pamphlet entitled ALLBASE/SQL Release Notes for installation instructions. This pamphlet accompanies the media for the software.
Sample DBEnvironment Setting Up PartsDBE Setting Up PartsDBE Before beginning, change into the directory where you want to create PartsDBE. Use an empty directory if possible. Then choose one of the following two methods for setting up PartsDBE: • Using SQLSetup • Using Setup SQLSetup is a sample database setup tool which simplifies the process of installing PartsDBE in your work space. Setup is a lower-level script called by SQLSetup. Using SQLSetup Run SQLSetup by issuing the proper command.
Sample DBEnvironment Setting Up PartsDBE Option 3 creates just the sample program set. Option 4 creates a database schema by calling SQLGEN. Option 5 displays the schema once it has been created. Option 6 lets you purge the sample DBEnvironment and programs. Choose the Help option to see more information about SQLSetup, or choose 0 to exit. Creating PartsDBE To create PartsDBE, choose option 1 from the SQLSetup menu.
Sample DBEnvironment Listings of ISQL Command Files Listings of ISQL Command Files Both SQLSetup and setup use a group of ISQL command files to create and load local copies of PartsDBE. These files, located in /usr/lib/allbase/hpsql/sampledb, are as follows: • STARTDBE, an ISQL command file containing the START DBE command. • CREATABS, an ISQL command file containing SQL commands.
Sample DBEnvironment STARTDBE Command File STARTDBE Command File /*This file creates the PartsDBE DBEnvironment with MULTI user mode and dual logging.
Sample DBEnvironment CREATABS Command File CREATABS Command File /* The following commands create the Purchasing Department's DBEFileSet with two DBEFiles.
Sample DBEnvironment CREATABS Command File /* The following commands create a DBEFileSet with one DBEFile for storage of long field data in the PurchDB.Reports table .*/ CREATE DBEFILESET FileFS; CREATE DBEFILE FileData WITH PAGES=50, NAME='FileData', TYPE=TABLE; ADD DBEFILE FileData TO DBEFILESET FileFS; /* The following commands create the two tables that comprise the ManufDB database. */ CREATE PUBLIC TABLE ManufDB.
Sample DBEnvironment CREATABS Command File DiscountQty IN PurchFS; SMALLINT) CREATE PUBLIC TABLE PurchDB.Vendors (VendorNumber INTEGER VendorName CHAR(30) ContactName CHAR(30), PhoneNumber CHAR(15), VendorStreet CHAR(30) VendorCity CHAR(20) VendorState CHAR(2) VendorZipCode CHAR(10) VendorRemarks VARCHAR(60) ) IN PurchFS; CREATE PUBLIC TABLE PurchDB.Orders (OrderNumber INTEGER VendorNumber INTEGER, OrderDate CHAR(8) ) IN OrderFS; CREATE PUBLIC TABLE PurchDB.
Sample DBEnvironment CREATABS Command File CREATE VIEW PurchDB.VendorStatistics (VendorNumber, VendorName, OrderDate, OrderQuantity, TotalPrice) AS SELECT PurchDB.Vendors.VendorNumber, PurchDB.Vendors.VendorName, OrderDate, OrderQty, OrderQty * PurchasePrice FROM PurchDB.Vendors, PurchDB.Orders, PurchDB.OrderItems WHERE PurchDB.Vendors.VendorNumber = PurchDB.Orders.VendorNumber AND PurchDB.Orders.OrderNumber = PurchDB.OrderItems.
Sample DBEnvironment LOADTABS Command File LOADTABS Command File /* This file loads each of the two tables in the ManufDB /* database, the six tables in the PurchDB database, /* and the three tables in the RecDB database with data. */ */ */ LOAD FROM EXTERNAL sampledb/SupplyBa TO ManufDB.SupplyBatches VENDPARTNUMBER 1 16 BATCHSTAMP 18 23 MINPASSRATE 43 8 ? END N; COMMIT WORK; !echo Table SupplyBatches successfully loaded!; LOAD FROM EXTERNAL sampledb/TestData TO ManufDB.
Sample DBEnvironment LOADTABS Command File DeliveryDays 55 5 ? DiscountQty 60 5 ? END N; COMMIT WORK; !echo Table SupplyPrice successfully loaded!; LOAD FROM EXTERNAL OrderNumber VendorNumber OrderDate END N; COMMIT WORK; !echo Table Orders sampledb/Orders TO PurchDB.Orders 1 10 11 10 ? 21 8 ? successfully loaded!; LOAD FROM EXTERNAL sampledb/OrderIte TO PurchDB.
Sample DBEnvironment LOADTABS Command File N; COMMIT WORK; !echo Table Members successfully loaded!; LOAD FROM EXTERNAL SponsorClub Event Date Time Coordinator END N; COMMIT WORK; !echo Table Events sampledb/Events TO RecDB.Events 1 15 ? 20 30 ? 50 10 ? 62 8 ? 71 20 ? successfully loaded!; INSERT INTO PURCHDB.
Sample DBEnvironment CREAINDEX Command File CREAINDEX Command File /* This file creates the indexes for the PurchDB database */ /* and then updates the statistics for each of the tables. */ CREATE UNIQUE INDEX PartNumIndex ON PurchDB.Parts (PartNumber); CREATE CLUSTERING INDEX PartToNumIndex ON PurchDB.SupplyPrice (PartNumber); CREATE INDEX PartToVendIndex ON PurchDB.SupplyPrice (VendorNumber); CREATE UNIQUE INDEX VendPartIndex ON PurchDB.
Sample DBEnvironment CREASEC Command File CREASEC Command File /* This file sets up authorities for the PurchDB and RecDB databases.*/ /* The DBA for the sampledb DBEnvironment is the DBEUserID John. */ REVOKE REVOKE REVOKE REVOKE REVOKE REVOKE REVOKE REVOKE REVOKE REVOKE REVOKE REVOKE ALL ALL ALL ALL ALL ALL ALL ALL ALL ALL ALL ALL ON ON ON ON ON ON ON ON ON ON ON ON ManufDB.SupplyBatches FROM PUBLIC; ManufDB.TestData FROM PUBLIC; PurchDB.Parts FROM PUBLIC; PurchDB.Inventory FROM PUBLIC; PurchDB.
Sample DBEnvironment CREASEC Command File GRANT GRANT GRANT GRANT GRANT GRANT GRANT ALL ON ALL ON ALL ON ALL ON ALL ON SELECT SELECT PurchDB.SupplyPrice TO PurchDBMaint; PurchDB.Vendors TO PurchDBMaint; PurchDB.Orders TO PurchDBMaint; PurchDB.Reports TO PurchDBMaint; PurchDB.OrderItems TO PurchDBMaint; ON PurchDB.VendorStatistics TO PurchDBMaint; ON PurchDB.PartInfo TO PurchDBMaint; /* The following commands create the Purchasing Department's */ /* group.
Sample DBEnvironment CREASEC Command File /* /* /* group. This group has SELECT, INSERT, DELETE, and UPDATE*/ authority for the Orders and OrderItems tables of the */ PurchDB database. */ CREATE GROUP Receiving; ADD Al TO GROUP Receiving; ADD Sue TO GROUP Receiving; ADD Martha TO GROUP Receiving; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.Orders TO Receiving; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.OrderItems TO Receiving; /* The following commands create the Warehouse Department's /* group.
Sample DBEnvironment CREASEC Command File ADD Stacey TO GROUP AccountsPayable; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.SupplyPrice TO AccountsPayable; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.Vendors TO AccountsPayable; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.Orders TO AccountsPayable; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.OrderItems TO AccountsPayable; /* The following commands create the group called Purch.
Sample DBEnvironment CREASEC Command File DELETE, UPDATE ON ManufDB.SupplyBatches TO Manuf; GRANT SELECT, INSERT, DELETE, UPDATE ON ManufDB.TestData TO Manuf; GRANT CONNECT TO Manuf; /* The following commands GRANT specific authorities to /* specific DBEUserIDs. */ */ GRANT GRANT GRANT GRANT SELECT SELECT SELECT UPDATE ON GRANT UPDATE ON GRANT UPDATE ON PurchDB.Vendors TO Tom; ON PurchDB.VendorStatistics TO Tom; ON PurchDB.PartInfo TO Tom; (BinNumber,QtyOnHand,LastCountDate) PurchDB.
Sample DBEnvironment CREASEC Command File TO DBEUsers; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.Orders TO DBEUsers; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.OrderItems TO DBEUsers; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.VendorStatistics TO DBEUsers; GRANT SELECT, INSERT, DELETE, UPDATE ON RecDB.Members TO DBEUsers; GRANT SELECT, INSERT, DELETE, UPDATE ON RecDB.Clubs TO DBEUsers; GRANT SELECT, INSERT, DELETE, UPDATE ON RecDB.
Sample DBEnvironment Data in the Sample DBEnvironment Data in the Sample DBEnvironment There are three databases in the DBEnvironment PartsDBE-- ManufDB, PurchDB, and RecDB. Use the SELECT command to retrieve all the data in every table in each database, as shown in the following sections.
Sample DBEnvironment ManufDB.SupplyBatches Table ManufDB.SupplyBatches Table isql=> select * from manufdb.supplybatches; select * from manufdb.supplybatches; ----------------+--------------------------+----------------VENDPARTNUMBER |BATCHSTAMP |MINPASSRATE ----------------+--------------------------+----------------7310 |1984-06-19 08:45:33.123 | 0.99 8113 |1984-06-14 11:13:15.437 | 0.93 790805 |1984-07-02 14:54:07.984 | 0.95 70250 |1984-07-22 09:06:23.319 | 0.97 9040 |1984-07-09 16:07:17.394 | 0.
Sample DBEnvironment ManufDB.TestData Table ManufDB.TestData Table select * from manufdb.testdata; --------------------------+------------+----------+----------+ BATCHSTAMP |TESTDATE |TESTSTART |TESTEND | --------------------------+------------+----------+----------+ 1984-06-19 08:45:33.123 |1984-06-23 |08:12:19 |13:23:01 | 1984-06-14 11:13:15.437 |1984-06-17 |08:05:02 |14:01:27 | 1984-07-02 14:54:07.984 |1984-07-05 |14:03:21 |19:33:54 | 1984-07-22 09:06:23.
Sample DBEnvironment PurchDB.Inventory Table PurchDB.Inventory Table select partnumber,binnumber,qtyonhand,lastcountdate from purchdb.
Sample DBEnvironment PurchDB.
Sample DBEnvironment PurchDB.OrderItems Table PurchDB.OrderItems Table select ordernumber,itemnumber,vendpartnumber from purchdb.
Sample DBEnvironment PurchDB.OrderItems Table 30521| 3|790805 30522| 1|13350 --------------------------------------------------------------------Number of rows selected is 47 U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] , or e[nd]> select purchaseprice,orderqty,itemduedate,receivedqty from purchdb.orderitems; --------------+--------+-----------+----------PURCHASEPRICE |ORDERQTY|ITEMDUEDATE|RECEIVEDQTY --------------+--------+-----------+----------2000.00| 5|19840621 | 3 565.
Sample DBEnvironment PurchDB.OrderItems Table 1990.00| 3|19840711 | 3 475.00| 5|19840727 | 4 1295.00| 3|19840716 | 2 80.00| 15|19840716 | 13 200.
Sample DBEnvironment PurchDB.Orders Table PurchDB.Orders Table isql=> select * from purchdb.
Sample DBEnvironment PurchDB.Parts Table PurchDB.Parts Table select * from purchdb.parts; ----------------+------------------------------+-------------PARTNUMBER |PARTNAME |SALESPRICE ----------------+------------------------------+-------------1123-P-01 |Central Processor | 500.00 1133-P-01 |Communication Processor | 200.00 1143-P-01 |Video Processor | 180.00 1153-P-01 |Graphics Processor | 220.00 1223-MU-01 |Cache Memory Unit | 80.00 1233-MU-01 |Main Memory Unit | 300.
Sample DBEnvironment PurchDB.Reports Table PurchDB.Reports Table select reportname, reportowner, filedata from purchdb.
Sample DBEnvironment PurchDB.SupplyPrice Table PurchDB.SupplyPrice Table isql=> select partnumber, vendornumber,vendpartnumber from purchdb.
Sample DBEnvironment PurchDB.
Sample DBEnvironment PurchDB.SupplyPrice Table 295.00| 30| 3 305.00| 15| 10 100.00| 15| 8 95.00| 20| 9 105.00| 15| 15 195.00| 15| 10 210.00| 20| 25 200.00| 30| 20 190.00| 15| 25 195.00| 15| 21 205.00| 30| 18 200.00| 20| 17 2000.00| 20| 15 1950.00| 30| 18 1295.00| 20| 20 1300.00| 20| 5 1310.00| 20| 3 345.00| 15| 1 335.00| 15| 19 645.00| 15| 22 700.00| 20| 15 650.00| 15| 16 195.00| 15| 5 200.00| 30| 3 1800.00| 15| 50 1650.00| 30| 35 260.00| 15| 10 230.00| 15| 13 250.00| 20| 11 240.00| 20| 12 250.
Sample DBEnvironment PurchDB.Vendors Table PurchDB.Vendors Table select vendornumber,vendorname,contactname from purchdb.vendors; ------------+------------------------------+------------------------VENDORNUMBER|VENDORNAME |CONTACTNAME ------------+------------------------------+------------------------9001|Remington Disk Drives |Debra Thomason 9002|Dove Computers |Peter B. Galvin 9003|Space Management Systems |Stacey Wolf 9004|Coupled Systems |Micki Sue Ding 9005|Underwood Inc.
Sample DBEnvironment PurchDB.Vendors Table Number of rows selected is 18 U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] , or e[nd]> select vendorcity,vendorstate,vendorzipcode from purchdb.
Sample DBEnvironment RecDB.Clubs Table RecDB.Clubs Table select * from recdb.
Sample DBEnvironment RecDB.Events Table RecDB.Events Table select * from recdb.
Sample DBEnvironment RecDB.Members Table RecDB.Members Table select * from recdb.
Sample DBEnvironment Sample Program Files Sample Program Files The following table contains a list of sample program files located in the /usr/lib/allbase/hpsql/programs directory. All programs except those marked with an asterisk (*) are fully discussed in the ALLBASE/SQL application programming guides. Table C-1. Sample Programs in /usr/lib/allbase/hpsql/programs C COBOL FORTRAN Pascal Description cex2 cobex2 forex2 pasex2 Single-row SELECT into host variables from PurchDB.
Standards Flagging Support Introduction D Standards Flagging Support Introduction The United States government has adopted ANSI X3.135-1989, Database Language SQL, as the database language to be used by all federal departments and agencies. This SQL standard, known as Federal Information Processing Standard 127.1 (FIPSPUB 127.1), requires that an option be provided which flags all features or extensions that do not conform to the SQL language or are processed in a nonconforming manner. FIPS 127.
Standards Flagging Support Non-standard Statements and Extensions Non-standard Statements and Extensions The following tables contain ALLBASE/SQL statements and extensions and indicate whether they are compliant with FIPS 127.1. If the ALLBASE/SQL statement is not compliant, the extensions to that statement are not compliant and are therefore not included in the table. A compliant statement may also have non-compliant extensions. These extensions are shown as non-compliant in the table. Table D-1.
Standards Flagging Support Non-standard Statements and Extensions Table D-1. ALLBASE/SQL FIPS 127.1 Compliance ALLBASE/SQL Statement CREATE TABLE CREATE TABLE Appendix D FIPS 127.1 Compliant Statement? Only when used in CREATE SCHEMA Only when used in CREATE SCHEMA Extension to Statement FIPS 127.
Standards Flagging Support Non-standard Statements and Extensions Table D-1. ALLBASE/SQL FIPS 127.1 Compliance ALLBASE/SQL Statement FIPS 127.1 Compliant Statement? CREATE TEMPSPACE NO CREATE VIEW Only when used in CREATE SCHEMA DECLARE CURSOR 668 YES Extension to Statement FIPS 127.
Standards Flagging Support Non-standard Statements and Extensions Table D-1. ALLBASE/SQL FIPS 127.1 Compliance ALLBASE/SQL Statement FIPS 127.1 Compliant Statement? Extension to Statement FIPS 127.
Standards Flagging Support Non-standard Statements and Extensions Table D-1. ALLBASE/SQL FIPS 127.1 Compliance ALLBASE/SQL Statement FIPS 127.1 Compliant Statement? GENPLAN NO GRANT Only when used in CREATE SCHEMA 670 Extension to Statement FIPS 127.
Standards Flagging Support Non-standard Statements and Extensions Table D-1. ALLBASE/SQL FIPS 127.1 Compliance ALLBASE/SQL Statement FIPS 127.1 Compliant Statement? INCLUDE NO INSERT YES BulkValues NO INSERT SingleRowValues YES LOCK TABLE NO LOG COMMENT NO OPEN YES Appendix D Extension to Statement FIPS 127.
Standards Flagging Support Non-standard Statements and Extensions Table D-1. ALLBASE/SQL FIPS 127.1 Compliance ALLBASE/SQL Statement FIPS 127.1 Compliant Statement? PREPARE NO RAISE ERROR NO REFETCH NO RELEASE NO REMOVE DBEFILE NO REMOVE FROM GROUP NO RENAME NO RESET NO REVOKE NO ROLLBACK WORK YES SAVEPOINT NO SELECT YES 672 Extension to Statement FIPS 127.
Standards Flagging Support Non-standard Statements and Extensions Table D-1. ALLBASE/SQL FIPS 127.1 Compliance ALLBASE/SQL Statement FIPS 127.
Standards Flagging Support Non-standard Statements and Extensions Table D-1. ALLBASE/SQL FIPS 127.1 Compliance ALLBASE/SQL Statement UPDATE WHERE CURRENT WHENEVER 674 FIPS 127.1 Compliant Statement? YES YES Extension to Statement FIPS 127.
Standards Flagging Support Non-Standard Data Types Non-Standard Data Types The following data types are not FIPS compliant. They are used in CREATE TABLE and ALTER TABLE column definitions.
Standards Flagging Support Non-Standard Expression Extensions Non-Standard Expression Extensions The following use of extensions in an expression is not FIPS compliant: TID DynamicParameters OUTER JOIN NATURAL JOIN STRING_LENGTH SUBSTRING OUTPUT_DEVICE OUTPUT_NAME CURRENT_DATE CURRENT_TIME CURRENT_DATETIME TO_CHAR TO_DATE TO_TIME TO_DATETIME TO_INTERVAL ADD_MONTHS TO_INTEGER CAST Concatenation (||) 676 Appendix D
Standards Flagging Support Non-Standard Syntax Rules Non-Standard Syntax Rules ALLBASE/SQL supports certain non-FIPS compliant extensions to the standard FIPS syntax rules listed in the ANSI SQL/89 document. The section number, the status rule number, and the FIPS SQL syntax rule for each non-FIPS compliant extension are listed below. Note that in some cases no flagger warning is generated for these exceptions. Table D-2.
Standards Flagging Support Non-Standard Syntax Rules Table D-2. FIPS Syntax Rules and ALLBASE/SQL Exceptions Section # Status Rule # FIPS SQL Syntax Rule ALLBASE SQL Extension 8.11 SR 8b An UPDATE ... WHERE CURRENT of a character column must be a character string of length less than or equal to the column. Compatible data and truncation are allowed. (No flagger warning is generated). 8.11 SR 8c In an UPDATE ... WHERE CURRENT, only an exact numeric value or NULL may be put in an exact numeric column.
Index A access plan defined, 429 modifying with SETOPT, 531 access to databases multiple connections, 95 types, 42 active connection defined, 103 active set in DECLARE CURSOR, 372 in FETCH, 424 in OPEN, 464 in REFETCH, 476 actual parameter using in procedures, 149 ADD DBEFILE syntax, 293 ADD TO GROUP syntax, 295 ADD_MONTHS in an expression, 228 adding ADD TO GROUP, 295 column to table, 301 constraint to table, 301 DBEFiles, 293 members to authorization group, 295 rows, 81, 445 ADVANCE syntax, 297 aggregate
Index conversions rules, 504 long data type defined, 208 storage requirements, 210 built-in variable differences from local variables, 151 similar to SQLCA elements, 151 using in procedures, 151 BULK operations FETCH, 424 INSERT, 445 SELECT, 499 use of, 95 C C preprocessor defined, 42 caller of a procedure recommended practices for, 156 Cartesian product defined, 114 CASCADE explained, 77 case sensitive comparison predicate, 266 comparisons, 211 CATALOG owner of catalog views, 206 catalog views explained,
Index explained, 126 CREATE DBEFILE syntax, 327 CREATE DBEFILESET syntax, 330 CREATE GROUP syntax, 332 CREATE INDEX syntax, 334 CREATE PARTITION syntax, 337 CREATE PROCEDURE explained, 146 syntax, 339 CREATE RULE invoking a procedure through, 147 syntax, 346 using, 158 CREATE SCHEMA syntax, 351 CREATE TABLE LANG = clause, 67 syntax, 354 CREATE TEMPSPACE syntax, 365 CREATE VIEW syntax, 367 creating audit DBE, 91 authorization groups, 332 constraints, 354 databases, 60 DBEFile, 327 DBEFileSets, 330 DBEnviron
Index in ALTER DBEFile, 299 in CREATE DBEFILE, 327 in DROP DBEFILE, 393 in REMOVE DBEFile, 480 rules for, 201 DBEFile type in ALTER DBEFile, 299 in CREATE DBEFILE, 327 DBEFile0 defined, 47 naming, 559 DBEFiles adding, 293 altering type of, 299 creating, 327 defined, 45 dropping, 393, 480 for data, 45 for indexes, 45 incrementing size, 327 purging, 393 relation to DBEFileset, 45 removing from DBEFileSet, 393, 395, 480 size range, 327 type, 327 using, 327 DBEFileSet authorization in CREATE PROCEDURE, 344, 35
Index DISABLE RULES syntax, 390 using, 163 disabling audit logging, 389 DISCONNECT syntax, 391 DISCONNECT CURRENT no current connection following, 104 disconnecting from DBEnvironments, 103 displaying access plan, 133, 429 DISTINCT in SELECT, 499, 505 DML ATOMICITY setting in SET TRANSACTION, 542 DML atomicity setting, 526 DO in procedures, 597 DROP DBEFILE syntax, 393 DROP DBEFILESET syntax, 395 DROP GROUP syntax, 397 DROP INDEX syntax, 399 DROP MODULE syntax, 401 DROP PARTITION syntax, 403 DROP PROCEDURE
Index defined, 208 storage requirements, 210 FORCE VALIDATE parameter, 592 FOREIGN KEY in CREATE TABLE, 354 formal parameter using in procedures, 149 Fortran preprocessor defined, 42 free log space checkpoint host variable, 316 FROM in simple queries, 111 fully qualified name, 204 G generating log comment, 462 GENPLAN explained, 133 syntax, 429 with SYSTEM and CATALOG views, 433, 435 GRANT MONITOR authority, 199 syntax, 436 WITH GRANT OPTION explained, 76 GRANT ON DBEFILESET syntax, 439 grantable privilege
Index operations with values, 217 storage requirements, 210 values in arithmetic expressions, 218 IS lock explained, 179 isolation level defined, 185 setting in BEGIN WORK, 312 setting in SET TRANSACTION, 542 ISQL defined, 42 EXECUTE PROCEDURE in, 153 using to issue statements, 87 IX lock explained, 179 J join algorithm specified by SETOPT, 531 asymmetric, 127 in complex queries, 116 inner and outer, 509 natural, 512 nested loop, 531 not using explicit join syntax, 514 outer join, 130 sort merge, 531 symme
Index hash key size, 361 host variable names, 416 host variables, 465 items for DISTINCT option, 505 length of index key, 361 log file size, 567 number of partitions, 555, 557, 565 PrimaryPages, 356 tables per query, 506 TempSpace, 365 timeout, 553, 564, 568 maxpartitions in START DBE NEW, 555 in START DBE NEWLOG, 563 MaxTransactions, 553 message buffer in procedures, 155 catalog, 550 error number, 474 for PRINT statements, 472 number 5000, 472 MICROFOCUS COBOL preprocessor, 42 minimum PrimaryPages, 356 mi
Index and dropping authorization groups, 397 creating objects, 77 how it is assigned, 77 of objects, 77 transferring, 77, 576 P page buffers flushing, 316 page level locking, 177 pages deadlocks, 195 in DBEFiles, 327 in TempSpaces, 365 PARALLEL FILL option setting in BEGIN WORK, 312 parameter in procedures, 149 naming rules, 205 using in procedures, 146 partition creating, 91, 337 dropping, 403 instance, 91 setting with ALTER TABLE, 301 understanding, 91 Pascal preprocessor defined, 42 pattern matching in
Index syntax (SELECT), 499 R RAISE ERROR in procedures, 155 in procedures invoked by rules, 162 syntax, 474 RC isolation level explained, 175 in SET SESSION, 537 in SET TRANSACTION, 543 read committed (RC) explained, 175 read committed isolation level in BEGIN WORK, 312 in SET SESSION, 537 in SET TRANSACTION, 542 read uncommitted (RU) explained, 176 read uncommitted isolation level in BEGIN WORK, 312 in SET SESSION, 537 in SET TRANSACTION, 543 REAL conversions rules, 504 data type defined, 208 storage requ
Index SAVEPOINT in a procedure, 147 setting, 497 syntax, 497 using, 495 scale defined, 208 in DECIMAL operations, 216 scoping transaction and session attributes, 87 search condition compatible predicates, 263 defined, 111 definition, 261 in complex queries, 116 in DELETE, 378 subquery in, 120 syntax, 262 type conversion in, 263 use for, 261 value extensions in, 263 SearchCondition in CREATE TABLE, 359 section authorization in PREPARE, 468 defined, 93 invalidation by TRUNCATE TABLE, 578 invalidation through
Index using TempSpace, 63 space management for tables and indexes, 63 in CREATE DBEFILESET, 330 special authorities revoking, 491 names, 206 special predicates EXISTS predicate, 267 IN predicate, 268 quantified predicate, 278 SQL defined, 41 language structure, 52 naming rules, 201 usage, 41, 60 SQL statement ADD DBEFile, 293 ADD TO GROUP, 295 ADVANCE, 297 ALTER DBEFILE, 299 ALTER TABLE, 301 BEGIN, 309 BEGIN ARCHIVE, 310 BEGIN DECLARE SECTION, 311 BEGIN WORK, 312 BULK FETCH, 424 BULK INSERT, 445 BULK SELEC
Index syntax, 563 starting a DBE session using CONNECT, 325 using START DBE, 552 startup parameters defined in START DBE NEW, 560 in START DBE NEW, 60 statement level constraint error checking, 521 DML atomicity, 526 error enforcement explained, 144 STOP DBE syntax, 571 stopping ALLBASE/SQL using STOP DBE, 571 session using DISCONNECT, 391 storage allocation defined, 45 storage audit element in START DBE NEW, 555 in START DBE NEWLOG, 563 Storage Manager defined, 42 storage requirements for specific data ty
Index SET TRANSACTION statement, 542 SET USER TIMEOUT statement, 548 START DBE NEWLOG, 568 value setting, 97 values in DBECon file, 552 transaction attributes setting in BEGIN WORK, 312 setting in SET TRANSACTION, 542 transaction level constraint checking errors, 521 transactions aborted, 571 and data consistency, 48 and multiple connections, 95 and recovery, 48 and timeouts, 95 automatic rollback of, 495 committing, 82 defined, 48, 82 effect of terminating, 323 implicit vs.
Index updatable, 136 uses for, 67 WITH CHECK OPTION, 141 W WHENEVER in procedures, 154 syntax, 595 WHERE and joins, 514 in SELECT, 506 in simple queries, 111 WHILE syntax, 597 WITH CHECK OPTION in CREATE VIEW, 368 view, 141 WITH GRANT OPTION explained, 76 syntax in GRANT, 436 wrapdbe command wrapperDBE, 92 wrapperDBE archive logging, 92 audit information, 92 definition, 92 roll forward, 92 wrapdbe command, 92 writer of a procedure recommended practices, 156 X X (EXCLUSIVE) locks, 460 explained, 179 Index
Index 694 Index
