ALLBASE/SQL Reference Manual (36216-90212)

ManualsBrandsHP ManualsSoftwareMPE/iX 6.5 Operating System

Summary of content (660 pages)

PAGE 1
HP 3000 MPE/iX Computer Systems ALLBASE/SQL Reference Manual ABCDE HP Part No. 36216-90001 Printed in U.S.A.
PAGE 2
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 tness 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.
PAGE 3
Printing History The following table lists the printings of this document, together with the respective release dates for each edition. The software version indicates the version of the software product at the time this document was issued. Many product releases do not require changes to the document. Therefore, do not expect a one-to-one correspondence between product releases and document editions.
PAGE 4
ALLBASE/SQL Manuals Title ALLBASE/NET User's Guide ALLBASE/SQL Advanced Application Programming Guide ALLBASE/SQL C Application Programming Guide ALLBASE/SQL COBOL Application Programming Guide ALLBASE/SQL Database Administration Guide ALLBASE/SQL FORTRAN Application Programming Guide ALLBASE/SQL Message Manual ALLBASE Pascal Application Programming Guide ALLBASE/SQL Performance and Monitoring Guidelines ALLBASE/SQL Reference Manual HP PC API User's Guide for ALLBASE/SQL and IMAGE/SQL ISQL Reference Manual
PAGE 5
Preface This manual presents the syntax and semantics of SQL (Structured Query Language) on HP 3000 computers running under the MPE/iX operating system. ALLBASE/SQL is Hewlett-Packard's proprietary relational database management product. MPE/iX, Multiprogramming Executive with Integrated POSIX, is the latest in a series of forward-compatible operating systems for the HP 3000 line of computers.
PAGE 6
What's New in this Edition G.1 and G.2 New Features The following table highlights the new or changed functionality added in G.1 and G.2 releases, and shows you where each feature is documented. New Features in ALLBASE/SQL Releases G.1 and G.2 Feature (Category) Description New operand to Adds an operand to concatenate concatenate strings character or binary strings in an expression. New operand: jj (Standards) Documented in . . . ALLBASE/SQL Reference Manual , \Expressions.
PAGE 7
New Features in ALLBASE/SQL Releases G.1 and G.2 (continued) Feature (Category) Syntax added to DELETE (Usability, Performance) Description Documented in . . . Automates execution of COMMIT ALLBASE/SQL Reference Manual , DELETE in \SQL Statements." WORK at the beginning of the DELETE and after each batch of rows is deleted when WITH AUTOCOMMIT is used. Reduces log-space and shared-memory requirements. WITH AUTOCOMMIT cannot be used in some cases (see the DELETE statement).
PAGE 8
New Features in ALLBASE/SQL Releases G.1 and G.2 (continued) Feature (Category) Description Documented in . . . Allow or disallow authority to create modules. (Usability) Grants or revokes the ability to create modules for speci c users. New attributes for GRANT and REVOKE: INSTALL. ALLBASE/SQL Reference Manual , GRANT, REVOKE in \SQL Statements." Script for migration to a new release (Usability, Tools) Provides SQLINSTL script for migration to a new release of ALLBASE/SQL.
PAGE 9
G.0 New Features The following table highlights the new or changed functionality in release G.0, and shows you where each feature is documented. New Features in ALLBASE/SQL Release G.0 Feature (Category) Description Documented in . . . Stored procedures (Usability) Provides additional stored procedure functionality for application programs. Allows declaration of a procedure cursor and fetching of multiple rows within a procedure to applications. New statement: ADVANCE.
PAGE 10
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) Description Documented in . . . New SQLGEN GENERATE parameters (Usability) Generates SQL statements necessary to recreate modi ed access plans for module sections. New syntax for GENERATE: DEFAULTSPACE, MODOPTINFO, PARTITION, PROCOPTINFO, SPACEAUTH. ALLBASE/SQL Database Administration Guide , \SQLGEN Commands" appendix.
PAGE 11
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) High Availability Description Provides a collection of features to keep systems available nonstop including: Partial STORE and RESTORE, Partial rollforward recovery, DBEFiles in di erent groups (MPE/iX), detaching and attaching database objects, CHECKPOINT host variable, changing log les, console messages logged to a le, generating fewer log records by using TRUNCATE TABLE to delete rows, and new system catalog information.
PAGE 12
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) New and changed SQLUtil commands for increased availability (High Availability) Description Documented in . . . Adds support for high availability ALLBASE/SQL Database Administration Guide , \SQLUtil" appendix. and System Management Intrinsics. Intended for non-stop, continuously available operations. New SQLUtil commands: ATTACHFILE, CHANGELOG, DETACHFILE, RESTORE PARTIAL, STORE PARTIAL, STOREINFO, STOREONLINE PARTIAL, WRAPDBE.
PAGE 13
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) Description Documented in . . . Optimizer enhancement (Performance) ALLBASE/SQL Performance and Uses a more e cient algorithm that signi cantly reduces the time Monitoring Guidelines , \Optimization" in \Basic Concepts in ALLBASE/SQL to generate the access plan. Performance." Access plan modi cation (Performance) Allows modi cation of access plans for stored section to optimize performance. View the plan with SYSTEM.SETOPTINFO.
PAGE 14
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) I/O performance improvement (Performance) Description Optimizes I/O for initial load, index build, serial scans, internal data restructuring, le activity, pseudo mapped les and temporary les. See the following features for new and changed syntax. Documented in . . . ALLBASE/SQL Reference Manual , \SQL Statements." Deletes all rows in a speci ed table ALLBASE/SQL Reference Manual , TRUNCATE TRUNCATE TABLE in \SQL Statements.
PAGE 15
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) Description Documented in . . . Modi ed SET options (Performance) Provides better performance for LOADs and UNLOADs. Specify bu er size, status reporting for LOAD/UNLOAD or exclusive lock for data table. AUTOSAVE row limit increased to 2147483647. New and changed SET options: LOAD BUFFER, LOAD ECHO, AUTOLOCK, AUTOSAVE.
PAGE 16
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) Description Increased memory for MPE/iX (HP-UX shared memory allocation is unchanged) (Performance) Increases memory up to 50,000 data bu er pages and 2,000 run time control block pages. Increases the limits signi cantly allowing allocation of enough data bu er pages to keep the entire DBEnvironment in memory if desired for performance.
PAGE 17
Conventions UPPERCASE In a syntax statement, commands and keywords are shown in uppercase characters. The characters must be entered in the order shown; however, you can enter the characters in either uppercase or lowercase. For example: COMMAND can be entered as any of the following: command Command COMMAND It cannot, however, be entered as: comm italics comamnd In a syntax statement or an example, a word in italics represents a parameter or argument that you must replace with the actual value.
PAGE 18
Conventions (continued) [ ... ] In a syntax statement, horizontal ellipses enclosed in brackets indicate that you can repeatedly select the element(s) that appear within the immediately preceding pair of brackets or braces. In the example below, you can select parameter zero or more times. Each instance of parameter must be preceded by a comma: [,parameter][...
PAGE 19
Contents 1. Introduction ALLBASE/SQL Components . . . . . . . . . . . . . Utility Programs . . . . . . . . . . . . . . . . . ALLBASE/SQL Databases . . . . . . . . . . . . . . Logical Concepts . . . . . . . . . . . . . . . . . Physical Concepts . . . . . . . . . . . . . . . . . ALLBASE/SQL Data Access . . . . . . . . . . . . . Using Queries . . . . . . . . . . . . . . . . . . . ALLBASE/SQL Objects . . . . . . . . . . . . . . . ALLBASE/SQL Users . . . . . . . . . . . . . . . . SQL Language Structure . . . .
PAGE 20
Creating Procedures . . . . . . . . . . . . . . . . . Creating Rules . . . . . . . . . . . . . . . . . . . Understanding Data Access Paths . . . . . . . . . . . . Serial Access . . . . . . . . . . . . . . . . . . . . Indexed Access . . . . . . . . . . . . . . . . . . . Hashed Access . . . . . . . . . . . . . . . . . . . Di erences between Hashed and Indexed Access . . . . When to Use a Hash Structure . . . . . . . . . . . . TID Access . . . . . . . . . . . . . . . . . . . . Controlling Database Access . . .
PAGE 21
Setting the Current Connection . . . . . . . . . . . . . . . Setting Timeout Values . . . . . . . . . . . . . . . . . . Setting the Transaction Mode . . . . . . . . . . . . . . . Using Single-Transaction Mode . . . . . . . . . . . . . . Using Multi-Transaction Mode with Multiple DBEnvironments Using Multi-Transaction Mode with One DBEnvironment . . Disconnecting from DBEnvironments . . . . . . . . . . . . Administering a Database . . . . . . . . . . . . . . . . . . Understanding the System Catalog . . . . .
PAGE 22
Procedures and Transaction Management . . . . . . Using SQL Statements in Procedures . . . . . . . . Specifying Parameters . . . . . . . . . . . . . . Using Local Variables in Procedures . . . . . . . . Using Built-in Variables in Procedures . . . . . . . Queries inside Procedures . . . . . . . . . . . . . Using a Simple SELECT . . . . . . . . . . . . . Using a Select Cursor . . . . . . . . . . . . . . Using a Procedure Cursor in ISQL . . . . . . . . . Error Handling in Procedures Not Invoked by Rules . .
PAGE 23
Locking Structure Implicit at CREATE TABLE Time . . . . . . Use of the LOCK TABLE Statement . . . . . . . . . . . . . Choice of a Scan Type . . . . . . . . . . . . . . . . . . . . Choice of Isolation Level . . . . . . . . . . . . . . . . . . . Neighbor Locking . . . . . . . . . . . . . . . . . . . . . Updatability of Cursors or Views . . . . . . . . . . . . . . . Use of Sorting . . . . . . . . . . . . . . . . . . . . . . . Scope and Duration of Locks . . . . . . . . . . . . . . . . . .
PAGE 24
7. Data Types Type Speci cations . . . . . . . . . . . . . . . . . . . . . . . . . Value Comparisons . . . . . . . . . . . . . . . . . . . . . . . . . Over ow and Truncation . . . . . . . . . . . . . . . . . . . . . . . Under ow . . . . . . . . . . . . . . . . . . . . . . . . . . . . . Type Conversion . . . . . . . . . . . . . . . . . . . . . . . . . . Null Values . . . . . . . . . . . . . . . . . . . . . . . . . . . . Decimal Operations . . . . . . . . . . . . . . . . . . . . . . . . .
PAGE 25
10. SQL Statements SQL Statement Summary . . . ADD DBEFILE . . . . . . ADD TO GROUP . . . . . ADVANCE . . . . . . . . ALTER DBEFILE . . . . . ALTER TABLE . . . . . . Assignment (=) . . . . . . . BEGIN . . . . . . . . . . BEGIN ARCHIVE . . . . . BEGIN DECLARE SECTION BEGIN WORK . . . . . . . CHECKPOINT . . . . . . . CLOSE . . . . . . . . . . COMMIT ARCHIVE . . . . COMMIT WORK . . . . . CONNECT . . . . . . . . CREATE DBEFILE . . . . CREATE DBEFILESET . . . CREATE GROUP . . . . . CREATE INDEX . . . . . .
PAGE 26
EXECUTE IMMEDIATE . . . EXECUTE PROCEDURE . . . FETCH . . . . . . . . . . . GENPLAN . . . . . . . . . GOTO . . . . . . . . . . . GRANT . . . . . . . . . . . IF . . . . . . . . . . . . . INCLUDE . . . . . . . . . . INSERT . . . . . . . . . . . Labeled Statement . . . . . . LOCK TABLE . . . . . . . . LOG COMMENT . . . . . . OPEN . . . . . . . . . . . . PREPARE . . . . . . . . . . PRINT . . . . . . . . . . . RAISE ERROR . . . . . . . REFETCH . . . . . . . . . . RELEASE . . . . . . . . . . REMOVE DBEFILE . . .
PAGE 27
VALIDATE . . . . . . . . . . . . . . . . . . . . . . . . . . . . WHENEVER . . . . . . . . . . . . . . . . . . . . . . . . . . . WHILE . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 10-304 10-307 10-309 A. SQL Syntax Summary B. ISQL Syntax Summary C. Sample DBEnvironment Setting Up PartsDBE . . . . . . . Using SQLSetup . . . . . . . . Creating PartsDBE . . . . . . . Using CREASQL . . . . . . . . Listings of ISQL Command Files . . STARTDBE Command File . . . CREATABS Command File . . .
PAGE 28
Figures 1-1. 1-2. 1-3. 1-4. 3-1. 4-1. 5-1. 5-2. 5-3. 5-4. 5-5. 5-6. 5-7. 5-8. 5-9. 5-10. 9-1. C-1. Components of ALLBASE/SQL . . . . . . . . . . . . . . How Tables, DBEFiles, and DBEFileSets Are Related . . . . . Databases and DBEFileSets . . . . . . . . . . . . . . . . Elements of an ALLBASE/SQL DBEnvironment . . . . . . . Range of Complex Query Types . . . . . . . . . . . . . . Referential Constraints in a Set of Tables . . . . . . . . . . Transactions over Time . . . . . . . . . . . . . . . . . .
PAGE 29
Tables 1-1. 2-1. 2-2. 4-1. 5-1. 5-2. 5-3. 5-4. 5-5. 7-1. 7-2. 7-3. 7-4. 7-5. 8-1. 8-2. 10-1. 10-2. 10-3. 10-4. 10-5. 10-6. 10-7. C-1. D-1. D-2. SQL Statement Categories . . . . . . . . . . . . . . . . . . . . Transaction Attribute Scope . . . . . . . . . . . . . . . . . . . System Views . . . . . . . . . . . . . . . . . . . . . . . . . Built-in Variables in Procedures . . . . . . . . . . . . . . . . . Lock Compatibility Matrix . . . . . . . . . . . . . . . . . . . .
PAGE 30
PAGE 31
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.
PAGE 32
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. ISQL is the interactive interface to ALLBASE/SQL. Programmatically, you embed statements in a C, COBOL, FORTRAN, or Pascal application program.
PAGE 33
Utility Programs In addition, these utility programs help you perform the necessary maintenance tasks: SQLUtil assists with le 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. SQLVer checks the version strings of the ALLBASE/SQL les.
PAGE 34
The data in a relational database is organized in tables. A table is a two-dimensional structure of columns and rows: The Parts Table ----------------+------------------------------+---------PARTNUMBER |PARTNAME |SALESPRICE ----------------+------------------------------+---------1123-P-01 |Central Processor | 500.00 1133-P-01 |Communication Processor | 200.00 1143-P-01 |Video Processor | 180.
PAGE 35
If you have been granted the proper authorization, you access databases by rst connecting to the DBEnvironment in which they reside: CONNECT TO 'DBEnvironmentName' Physical Concepts Physically, the DBEnvironment is a collection of les for one or more logical databases. A DBEFile is an MPE XL le. Most les in a DBEnvironment are DBEFiles. Data in the tables of logical databases is stored in one or more DBEFiles.
PAGE 36
Figure 1-3. Databases and DBEFileSets A DBEnvironment, illustrated in Figure 1-3, houses the DBEFiles for one or more ALLBASE/SQL databases, plus the following, which contain information for all databases in the DBEnvironment: A DBECon le. This le contains information about the DBEnvironment con guration, such as the size of various bu ers and other startup parameters. The name of the DBECon le is the same as the name of the DBEnvironment. A system catalog.
PAGE 37
Most database users need not be concerned with the physical aspects of ALLBASE/SQL databases beyond knowing which DBEnvironment contains the databases they want to access. ALLBASE/SQL Data Access The DBEnvironment determines both what data can be accessed in a transaction and what data can be recovered.
PAGE 38
ALLBASE/SQL Objects The following structures play a signi cant 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 tables and views Columns within tables and views Grant authorities
PAGE 39
Database administrators. These individuals, referred to as DBAs, are responsible for the creation and maintenance of ALLBASE/SQL DBEnvironments. They use SQL statements, usually via ISQL, to perform the following tasks: De ne DBEnvironments, grant and revoke authorities, add and drop DBEFiles, alter tables, de ne indexes, and de ne views using SQL, ISQL, or preprocessed programs. Alter the con guration of a DBEnvironment, move or purge DBEFiles, and back up DBEnvironments using SQLUtil.
PAGE 40
The syntax of SQL is fully described in chapters 7-10 of this manual. Using Comments within SQL Statements You can initiate comments within any SQL statement or ISQL prompt either by pre xing 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.
PAGE 41
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 speci cally for use by application programmers or database administrators. The SQL statements are functionally summarized in Table 1-1. For the commands in each category, refer to Table 10-1, \SQL Statement Summary." Table 1-1.
PAGE 42
Table 1-1. SQL Statement Categories (continued) Group Database administration statements Category Authorization Purpose Statements for controlling DBEnvironment access. DBEnvironment con guration and Statements for controlling DBEnvironments. use Space management Statements for managing DBEFiles used for tables and indexes; statements for managing temporary space for sorting. Logging Statements for managing log les. DBEnvironment statistics management Statements related to the system catalog.
PAGE 43
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 bu er directly by using the SQLEXPLAIN statement.
PAGE 44
example, if you are loading two tables that have a referential relationship that is circular| that is, each table references a primary key element in the other table|then you must defer constraint error checking until both tables are loaded; otherwise any attempt to load a row would result in a constraint error.
PAGE 45
2. Set the MPE job control word NLUSERLANG to the number (LangNum ) of the native language you use. Use the following MPE XL command: SETJCW NLUSERLANG = LangNum This language then becomes the current language . (If NLUSERLANG is not set, the current language is NATIVE-3000.) 3. Use the LANG = LanguageName option of the START DBE NEW statement to specify the language of a DBEnvironment when you create it. Run the MPE XL utility program NLUTIL.PUB.
PAGE 46
PAGE 47
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 De ning 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 Connections and Transactions with Timeouts A
PAGE 48
Creating DBEnvironments Before you can create a database, you must rst con gure a DBEnvironment. You use the START DBE NEW statement, optionally specifying startup parameters to override those assigned by default.
PAGE 49
language. See \Native Language Support" in the \Introduction" chapter for information about specifying a native language as the current language. Initial Privileges When a DBEnvironment is con gured, ALLBASE/SQL grants the following initial privileges: DBECreator status. The logon name that issues the START DBE NEW statement is the DBECreator. Users with this status can use all the SQLUtil statements to maintain the DBEnvironment. DBA authority. The DBECreator is given DBA authority.
PAGE 50
Sessions without Autostart When the autostart ag has the value of OFF, a DBA must issue the START DBE statement to make a DBEnvironment accessible. For example: START DBE 'PartsDBE.SomeGrp.SomeAcct' The START DBE statement illustrated above initiates a single-user session for the DBEnvironment. To make multiuser access possible, the MULTI option is speci ed as follows: START DBE 'PartsDBE.SomeGrp.
PAGE 51
view SYSTEM.TEMPSPACE. A TempSpace is referred to by a unique name. If a TempSpace is not de ned, sorting is done in the current group. Defining How Data is Stored and Retrieved To create database objects, you use data de nition statements to de ne the following: Tables Views Indexes Constraints Procedures Rules Creating a Table When you de ne a table, use the CREATE TABLE statement to accomplish the following tasks: 1. Establish an automatic locking mode and default access authorities. 2.
PAGE 52
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 de nition whenever you access a table. You can use the LOCK TABLE statement to override automatic locking. You can use the ALTER TABLE statement to permanently change the implicit locking mode.
PAGE 53
Date/time data types: DATE TIME DATETIME INTERVAL Binary string data types: BINARY(n ) VARBINARY(n ) LONG BINARY(n ) LONG VARBINARY(n ) When you de ne 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. Some data types (CHAR(n ), VARCHAR(n ), BINARY(n ), VARBINARY(n ), LONG BINARY(n ) and LONG VARBINARY(n )) require a column length. CHAR(n ) has a default length of 1; VARCHAR(n ) does not.
PAGE 54
Specifying Native Language Tables and Columns Use the LANG = TableLanguageName option in the CREATE TABLE statement to specify a language other than the DBEnvironment's language. You can only specify NATIVE-3000 or the current native language of the DBEnvironment. CREATE TABLE NewTable LANG = "NATIVE-3000" (Column1 char(20), Column2 char(10)) You must use double quotes around the name \NATIVE-3000" because it contains a hyphen. Normally, native language names do not require quotes.
PAGE 55
want to rename the columns. You enclose the names in parentheses, but omit data types, which depend on the types of the columns in the base tables. The derivation of the view is a SELECT statement. In the previous example, the view is derived from the PurchDB.Parts table. Each row in the view contains a part number and a price; only rows for parts costing more than $1000 can be accessed through this view. Unlike a table de nition, a view de nition does not require that you specify where to store rows.
PAGE 56
Creating Rules Once a table is de ned, you can create a rule that will execute a procedure whenever a speci c ring condition is met. For example, you can de ne a rule that will execute a procedure to delete rows from the SupplyPrice table whenever a speci c part is dropped from the Parts table in the sample DBEnvironment PartsDBE: CREATE RULE PurchDB.RemovePart AFTER DELETE FROM PurchDB.Parts EXECUTE PROCEDURE PurchDB.
PAGE 57
Serial Access Serial access does not require the existence of any special object in addition to the table itself. If ALLBASE/SQL chooses serial access when you issue a query, it starts reading data from the rst page in the table and continues to the end. Serial access is probably the best access method when you intend to read all the data in the table. For example, an application that updates every row in a table in exactly the same way would perform best using a serial scan.
PAGE 58
Predicates are optimizable, which means that the use of an index is considered in choosing an access path for the data. The following predicates are optimizable when all the data types within them are the same; in the case of DECIMAL data, the precisions and scales of the values must be the same: WHERE Column1 ComparisonOperator Column2 , in which ComparisonOperator is one of the following: =, >, >=, <, or <=.
PAGE 59
Hashed Access Hashed access requires you to specify hashing when you create the table, before loading data. Because a hash structure is speci ed as part of the table de nition, you do not assign a name to it, as you do with an index. However, you must identify speci c key columns and a number of primary pages for data storage. ALLBASE/SQL determines the placement of rows based on speci c unique key values.
PAGE 60
isql=> SELECT * FROM PurchDB.Vendors > WHERE VendorNumber = 9002; However, it would not consider hash access for the following: isql=> SELECT * FROM PurchDB.Vendors > WHERE VendorNumber > 9002 > ORDER BY VendorName; 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.
PAGE 61
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 .
PAGE 62
and de ne DBEFiles and DBEFileSets. In addition, only a DBA can issue statements that control objects owned by a class name; for example, only DBAs can drop or issue grants for a table owned by a class name. Grants All authorities except OWNER authority can be granted by using the GRANT statement. The GRANT statement gives authorities to individual users, to authorization groups, or to all users.
PAGE 63
a parent by the DBA with the BY clause of the GRANT statement. For more information on orphaned privileges, refer to \Using the WITH GRANT OPTION Clause" in the chapter \Database Creation and Security" in the ALLBASE/SQL Database Administration Guide . Ownership The following six objects have owners associated with them: Tables Views Authorization groups Modules Procedures Rules These objects can be owned by an individual, an authorization group, or a class; but an object can have only one owner at a time.
PAGE 64
Default Owner Rules In several statements, when a name is speci ed, such as table name, rule name, group name, or index name, speci cation of the owner name is optional. The method of determining the default owner when no owner is speci ed is as follows: If the name is within a CREATE PROCEDURE statement (except for the procedure name itself), and it is not within a CREATE SCHEMA statement in that procedure, then the default owner is the procedure's owner.
PAGE 65
Module owners can execute, validate, and drop their modules. They can grant and revoke RUN authority for their modules. Ownership of modules cannot be transferred. Procedure owners can drop their procedures. They can grant and revoke EXECUTE authority for their procedures, and they can transfer ownership to another owner. Rule owners can drop their rules. The rule owner must be the same as the owner of the table the rule is de ned upon.
PAGE 66
After creating objects owned by the class, you must grant the speci c authorities you wish users or groups to have. Suppose you have a group PurSta consisting of DBEUserIDs for members of the Purchasing department. You could grant authorities to the group as follows: GRANT SELECT, UPDATE ON PurchDB.Parts to PurStaff; Differences between Groups and Classes You create a group explicitly by using the CREATE GROUP statement.
PAGE 67
Only a single table name or view name can be speci ed. Only certain views can be used to insert rows into a base table, as described under \Updatability of Queries" in the \SQL Queries" chapter. The column names can be omitted if you are going to put a value into every column in the row. Otherwise, you name the columns you want to assign values to, enclosing the column names in parentheses and separating multiple column names with commas. Columns not named are assigned their default values.
PAGE 68
Deleting Data You use the DELETE statement to delete entire rows. This statement has two components as follows: 1. A table or view name 2. A WHERE clause 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 speci ed. Only certain views can be used to delete rows, as described under \Updatability of Queries" in the \SQL Queries" chapter. The WHERE clause is optional.
PAGE 69
Ensuring Logical Data Integrity The data in the database must be accurate and consistent. For example, adding a part to the warehouse inventory entails inserting a row into three tables: PurchDB.Parts, PurchDB.SupplyPrice, and PurchDB.Inventory. All three inserts must succeed, or else the database is left in an inconsistent state. To enforce data integrity, the three inserts are contained in a single transaction.
PAGE 70
Starting Transactions A transaction is initiated with either an implicit or explicit BEGIN WORK statement.
PAGE 71
A system failure occurs. When the system is up again, and a START DBE statement is issued, incomplete transactions are rolled back. ALLBASE/SQL chooses the transaction as the victim when breaking a deadlock. The session is terminated by a TERMINATE USER command. The ROLLBACK WORK statement should be issued explicitly to maintain data integrity.
PAGE 72
SAVEPOINT Savepoint number is 1. An attempt to make 15 hotel reservations fails because the designated hotel is full. ROLLBACK WORK TO 1 SAVEPOINT Savepoint number is 2. Make 15 hotel reservations at another hotel. COMMIT WORK Scoping of Transaction and Session Attributes A set of attributes is associated with each transaction and user session.
PAGE 73
Table 2-1.
PAGE 74
SELECT * FROM PurchDB.OrderItems WHERE VendPartNumber = '2310' COMMIT WORK . . . SELECT * FROM PurchDB.Vendors WHERE VendorNumber = 1234 COMMIT WORK . . . SELECT * FROM PurchDB.SupplyPrice WHERE VendorNumber = 1234 AND VendPartNumber = '2310' COMMIT WORK . . . For more information on isolation levels, refer to the \Concurrency Control through Locks and Isolation Levels" chapter in this manual.
PAGE 75
total number of BEGIN WORK, COMMIT WORK, and ROLLBACK WORK statements executed in the DBEnvironment maximum number of transactions con gured which sessions have active or waiting transactions which sessions have executed BEGIN WORK, COMMIT WORK, and ROLLBACK WORK statements See the ALLBASE/SQL Performance and Monitoring Guidelines for more information on SQLMON. Tips on Transaction Management Keep transactions short.
PAGE 76
The Audit Tool, SQLAudit, is introduced below. SQLAudit is fully described in the ALLBASE/SQL Database Administration Guide . The ALLBASE/SQL Database Administration Guide describes how to create audit DBEnvironments and how to select records for audit. The \SQL Statements" chapter of this manual contains the detailed syntax to create audit DBEnvironments and partitions.
PAGE 77
Using SQLAudit SQLAudit is an ALLBASE/SQL utility program that can be used in conjunction with audit DBEnvironments to view the changes that have been made to the DBEnvironment. You use SQLAudit to audit only committed transactions. For security reasons, you need DBA authorization to use SQLAudit. Refer to the \DBA Tasks and Tools" chapter of the ALLBASE/SQL Database Administration Guide for a full description of SQLAudit.
PAGE 78
Preprocessed programs receive messages from ALLBASE/SQL through the SQL Communication Area, called the SQLCA. Information is sent to ALLBASE/SQL through the SQL Description Area, called the SQLDA. These structures and the above statements are explained in detail along with examples in the ALLBASE/SQL application programming guides.
PAGE 79
In the SELECT and FETCH statements the indicator variable can be an output host variable and indicate that a value in the associated host variable is null or a column value is truncated. Host variable names are pre xed with a colon (:) when embedded in an SQL statement. :PartNumber :PartName :PartNameInd When host variables are used in an application outside of an embedded SQL statement, the host variable name is not pre xed by a colon.
PAGE 80
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 application program, you can use either a string or, as in the following example, a host
PAGE 81
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.
PAGE 82
BEGIN WORK RC SELECT PartNumber, PartName, SalesPrice FROM PurchDB.Parts WHERE PartNumber BETWEEN 20000 AND 21000 If DBERR 2825 is returned, the transaction has timed out, and your application must take appropriate action. .. . 8. Set the current connection to Sales1. SET CONNECTION 'Sales1' 9. Begin a transaction for SalesDBE. If this transaction waits for system resources more than 30 seconds, it will timeout and return an error message to the application.
PAGE 83
3. Set the current connection to Parts1. isql=> SET CONNECTION 'Parts1'; 4. Begin a transaction for PartsDBE. isql=> BEGIN WORK RC; isql=> SELECT PartNumber, PartName, SalesPrice > FROM PurchDB.Parts > WHERE PartNumber BETWEEN 20000 AND 21000; . . . 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.
PAGE 84
SET CONNECTION 'Parts1' 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 PurchDB2.Orders2 VALUES (:PartsArray, :StartIndex, :NumberOfRows) 7. Test the sqlcode eld of the sqlca.
PAGE 85
1. Put multi-transaction mode in e ect. SET MULTITRANSACTION ON DECLARE CURSOR FOR SELECT FROM WHERE BankCursor TransactionType, DollarAmount, BankNumber Accounts AccountNumber = :AccountNumber 2. Connect two times to BankDBE. Be sure to specify an appropriate timeout value for each connection. CONNECT TO 'BankDBE' AS 'Bank2' SET USER TIMEOUT 30 SECONDS CONNECT TO 'BankDBE' AS 'Bank1' SET USER TIMEOUT 30 SECONDS The user enters an account number. 3. Begin a transaction for the Bank1 connection.
PAGE 86
Disconnecting from DBEnvironments The DISCONNECT statement provides a means of closing one or all active connections within an application. An active connection is a connection established within the application that has not been released, stopped, or disconnected. Your application might require that all connections be terminated when the application completes. In some cases, it might be desirable to terminate a speci c connection at another point in the application.
PAGE 87
9. End both open transactions and disconnect the two active connections. Note that the COMMIT WORK statement is issued for the current connection's transaction. COMMIT WORK SET CONNECTION 'Sales1' COMMIT WORK DISCONNECT ALL Note that following the execution of a DISCONNECT CURRENT statement, no current connection exists. To establish a current connection following a DISCONNECT CURRENT statement, you must either establish a connection or set the connection.
PAGE 88
When a DBEnvironment is rst con gured, the information in the system catalog describes the system tables and views themselves. As database objects are de ned, their de nitions 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.
PAGE 89
Table 2-2. System Views (continued) View Name Purpose SYSTEM.PARAMDEFAULT Describes the default value of each parameter de ned with a non-NULL default. SYSTEM.PARAMETER Describes each parameter of each procedure. SYSTEM.PARTITION Contains partition information. SYSTEM.PLAN Stores the result of one GENPLAN for each session. SYSTEM.PROCAUTH Identi es users and groups and the procedures they can execute. SYSTEM.PROCEDURE Describes each procedure. SYSTEM.
PAGE 90
PAGE 91
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." Using the SELECT Statement Use the SELECT statement to compose queries.
PAGE 92
The result is presented in the form of a table, called a query result. The result table (shown next) for this example has two columns: part numbers and a count of vendors who supply each part. The query result has rows only for parts that can be delivered in fewer than 25 days by more than two suppliers. The rows are ordered in ascending order by PartNumber.
PAGE 93
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.
PAGE 94
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 . . . Any row containing a null part number is excluded from the join, as are rows that have a part number value in one table, but not the other. You can also join a table to itself.
PAGE 95
For a two or more table join, if you do not use a join predicate in the ON SearchCondition3 clause or the WHERE clause, or if there are no common columns with which to join the tables in a natural join, the result of the join is the Cartesian product. In the simplest case, for a two table join, the Cartesian product is the set of rows which contains every possible combination of each row in the rst table concatenated with each row in the second table.
PAGE 96
The query result for the query is as follows: SELECT p.PartNumber, PartName, c.PartNumber, Color FROM Parts p, Colors c.... ---------------+------------+----------------+------------------PARTNUMBER |PARTNAME |PARTNUMBER |COLOR ---------------+------------+----------------+------------------3 |NULL | 3|Green The only rows selected for the query result are those rows for which the join predicate (p.PartNumber = c.PartNumber) evaluates to true.
PAGE 97
Figure 3-1. Range of Complex Query Types 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.
PAGE 98
UNION Queries A SELECT statement can consist of several query blocks connected by UNION or UNION ALL statements. Each individual SELECT statement returns a query result which is a set of rows selected from a speci ed table or tables. The union of these query results is presented as a table that consists of all rows appearing in one or more of the original query results. If only the UNION statement is used, all duplicate rows are removed from the nal set of rows.
PAGE 99
query expressions that make up the overall statement. Only the nal query result can be ordered. If the UNION ALL statement is used in the previous query, the result can contain duplicate rows.
PAGE 100
SELECT PartNumber, 'deliverydays >= 30' FROM PurchDB.SupplyPrice WHERE DeliveryDays >= 30 UNION ALL SELECT PartNumber, 'supplied by 9002 FROM PurchDB.
PAGE 101
Special Predicates The three types of special predicate are listed here: The quanti ed predicate (ALL, ANY, or SOME), used to compare the value of an expression with some or all of the values of an operand. The IN predicate, used to check for inclusion of an expression in a set of values. The EXISTS predicate, used to check for the existence of a value in an operand.
PAGE 102
SELECT PartNumber, VendorNumber FROM PurchDB.SupplyPrice WHERE PartNumber = ANY ('1343-D-01', '1623-TD-01', '1723-AD-01', '1733-AD-01') AND NOT VendorNumber = 9011 ----------------+-----------PARTNUMBER |VENDORNUMBER ----------------+-----------1343-D-01 | 9001 1623-TD-01 | 9015 1723-AD-01 | 9004 1723-AD-01 | 9012 1723-AD-01 | 9015 1733-AD-01 | 9004 1733-AD-01 | 9012 The quanti er ANY is used to determine whether PurchDB.SupplyPrice contains any of the part numbers in the value list.
PAGE 103
equal to at least one part number in T2." This query returns the sales price of all the parts in T1 if T2 has more than one part. A less ambiguous form using EXISTS is as follows: SELECT T1.SalesPrice FROM T1 WHERE EXISTS (SELECT T2.PartNumber FROM T2 WHERE T2.PartNumber <> T1.PartNumber) Using the ALL Quantifier With the ALL quanti er, the predicate is true only if all of the values in the value list or subquery relate to the expression as indicated by the comparison operator.
PAGE 104
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 . . .
PAGE 105
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. Eve Computers Jujitsu Microelectronics Latin Technology KellyCo Inc. Morgan Electronics Seminational Co. Seaside Microelectronics Educated Boards Inc. Proulx Systems Inc.
PAGE 106
In other cases, however, it is necessary to evaluate a subquery once for every row in the outer query, as in the following: SELECT v.VendorName FROM PurchDB.Vendors v WHERE NOT EXISTS (SELECT * FROM PurchDB.SupplyPrice sp WHERE sp.VendorNumber = v.VendorNumber) The predicate in the subquery references the column value v.VendorNumber, which is de ned by the outer query block.
PAGE 107
technique for creating a symmetric outer join using the UNION operator is described later in the section, \Symmetric Outer Joins Using the UNION Operator." A left outer join obtains the rows from both tables for which there is a matching value in the common column or columns (the inner part) and the rows from the left hand table for which there is no match in the right hand table (the outer part). Each unmatched row from the left hand table is extended with the columns coming from the right hand table.
PAGE 108
SELECT FROM RIGHT JOIN ON AND ORDER BY PartNumber, VendorName, VendorCity Purchdb.SupplyPrice sp PurchdB.Vendors v sp.VendorNumber = v.VendorNumber VendorState = 'CA' PartNumber DESC SELECT PartNumber, VendorName, VendorCity FROM Purchdb.SupplyPrice sp RIGHT... ----------------+------------------------------+-------------------PARTNUMBER |VENDORNAME |VENDORCITY ----------------+------------------------------+-------------------|Underwood Inc.
PAGE 109
If you want the inner part of the query to contain all vendors who do supply parts and are located in California while the outer part contains all vendors who do not supply parts, regardless of location, use the query shown below. SELECT FROM RIGHT JOIN ON WHERE OR ORDER BY PartNumber, VendorName, VendorCity Purchdb.SupplyPrice sp PurchdB.Vendors v sp.VendorNumber = v.
PAGE 110
Suppose you want to create a list of vendors who either supply some part with a unit price less than $100 or else do not supply any parts at all. To do this, merge two separate queries with a UNION ALL statement, as in the following examples. The rst query shown here selects the names of vendors who do not supply parts: SELECT v.VendorName FROM PurchDB.Vendors v WHERE NOT EXISTS (SELECT * FROM PurchDB.SupplyPrice sp WHERE sp.VendorNumber = v.
PAGE 111
Symmetric Outer Join Using the UNION Operator Since the syntax does not support a symmetric outer join, you might try to simulate a symmetric outer join using the left outer join syntax in combination with the right outer join syntax. Intuitively, the following query might seem correct: SELECT FROM NATURAL LEFT JOIN NATURAL RIGHT JOIN ORDER BY PartName, PartNumber, VendorName, VendorCity Purchdb.Parts Purchdb.SupplyPrice Purchdb.
PAGE 112
sets of rows that satisfy the inner join, but the union operation eliminates the duplicate rows unless UNION ALL is speci ed. The result of the above query follows: SELECT PartName, PartNumber, VendorName FROM PurchDB.Parts NATURAL LEFT... ------------------------------+----------------+-----------------------------PARTNAME |PARTNUMBER |VENDORNAME ------------------------------+----------------+-----------------------------| |Kinki Cable Co.
PAGE 113
Displaying a Query Access Plan To display the access plan generated by the optimizer, showing the columns in the order most useful to you, execute the following statement: isql=> SELECT Operation, TableName, IndexName, QueryBLock, Step, Level > FROM System.Plan; SELECT Operation, TableName, IndexName, QueryBlock, Step, Level FROM System.
PAGE 114
shows the hierarchy of the operations so you can easily graph the operations as an execution tree. This is normally necessary only when your HP Service Representative is evaluating a query. LEVEL Updatability of Queries INSERT, UPDATE and DELETE operations may be performed through views or as quali ed by search conditions provided the views or search conditions are based on updatable queries.
PAGE 115
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.
PAGE 116
Additionally, PRIMARY KEY can be speci ed only once per table. Duplicate unique constraints are not allowed. Neither UNIQUE nor PRIMARY KEY columns can contain null values|they must be de ned as NOT NULL. The following syntax is used to de ne a unique constraint on an individual column or column list at the table level: 2 UNIQUE (ColumnName , . . . PRIMARY KEY 3 2 ) CONSTRAINT ConstraintID 3 ConstraintID is the name of the constraint. It is not necessary to name the constraint.
PAGE 117
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 de ne a referential constraint at the table level in the CREATE TABLE statement for a referencing table: 2 3 , ... ) FOREIGN KEY (FKColumnName 2 2 REFERENCES RefTableName (RefColumnName , . . .
PAGE 118
If a check constraint is added to an existing table, data already in the table is veri ed to ensure that the check constraint is satis ed. A constraint error occurs if the constraint is not satis ed; 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.
PAGE 119
CREATE PUBLIC TABLE RecDB.Events CHECK (Date >= '1990-01-01') 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.Members (MemberName, Club) CONSTRAINT Events_FK) IN RecFS; Check Constraint This table level constraint could also be de ned after the Date or Time column, or at any point in the parenthesized list.
PAGE 120
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.Members (MemberName CHAR(20) NOT NULL, Club CHAR(15) NOT NULL, MemberPhone SMALLINT, PRIMARY KEY (MemberName, Club) CONSTRAINT Members_PK, FOREIGN KEY (Club) REFERENCES RecDB.Clubs CONSTRAINT Members_FK) IN RecFS; CREATE PUBLIC TABLE RecDB.
PAGE 121
The Members table contains the names of members and clubs. A member can be in more than one club. For every Coordinator/SponsorClub pair of values exists a corresponding MemberName/Club match. The Clubs table contains information about clubs. For every club entry in the Members table, a corresponding entry must exist in the Clubs table, as shown by the Members FK constraint. Inserting Rows in Tables Having Constraints There are two ways you can insert data in tables having constraints.
PAGE 122
be inserted into the Members FK column Club because that column also participates in Members PK|and therefore was declared NOT NULL. With the second method, you can also perform these inserts in one transaction, deferring constraint checking to the end of the transaction. While you are inserting data, constraint error violations are not reported because they will be resolved by the time the COMMIT WORK statement is executed.
PAGE 123
Using Procedures An ALLBASE/SQL procedure consists of control ow and status statements together with SQL statements that are stored as sections in the system catalog for later execution at the user's request or through the ring of a rule. You can create a procedure through ISQL or through an application program; and you can execute the procedure through ISQL, through an application program, or through rules that are created separately.
PAGE 124
If the procedure is invoked through a rule, the rule owner needs EXECUTE or OWNER authority for the procedure or DBA authority. Creating Procedures The following is a very simple example of procedure creation: CREATE PROCEDURE ManufDB.FailureList (Operator CHAR(20) NOT NULL, FailureTime DATETIME NOT NULL, BatchStamp DATETIME NOT NULL) AS BEGIN INSERT INTO ManufDB.
PAGE 125
isql=> CREATE RULE AFTER INSERT TO ManufDB.TestData > WHERE PassQty < TestQty > EXECUTE PROCEDURE > ManufDB.FailureList(USER, CURRENT_DATETIME, BATCHSTAMP); isql=> In this case, the invocation of the procedure takes place when an INSERT operation is performed on ManufDB.TestData for a batch of parts in which there were some failures. When executing the procedure from within a rule, you can refer to the names of columns in the table on which the rule is triggered.
PAGE 126
ADVANCE BEGIN DECLARE SECTION BULK statements CLOSE USING COMMIT WORK RELEASE CONNECT CREATE PROCEDURE (including inside CREATE SCHEMA) DECLARE CURSOR for EXECUTE PROCEDURE DESCRIBE DISCONNECT END DECLARE SECTION EXECUTE EXECUTE IMMEDIATE EXECUTE PROCEDURE GENPLAN INCLUDE OPEN USING PREPARE RELEASE ROLLBACK WORK RELEASE SET CONNECTION SET DML ATOMICITY SET MULTITRANSACTION SET SESSION SET TRANSACTION SQLEXPLAIN START DBE STOP DBE In procedures that are invoked by execution of rules, the following statement
PAGE 127
Specifying Parameters A parameter represents a value that is passed between a procedure and an invoking application or rule. You de ne formal parameters with the CREATE PROCEDURE statement. When executing a procedure directly, you pass input parameter values in the EXECUTE PROCEDURE statement, and output parameter values are returned when the procedure terminates.
PAGE 128
you cannot use host variables from the application within the body of the procedure de nition nor can you use local variables in the application. Since the application's host variables cannot be directly accessed from within the procedure, you must use local variables or parameters in the INTO clause of any FETCH, REFETCH, or SELECT statement within a procedure. Then, if necessary, you transfer data to a calling application through output parameters.
PAGE 129
application to manipulate a procedure cursor. After issuing such a statement, the application should examine the appropriate elds of the SQLCA to determine status and handle any errors. Queries inside Procedures Within a procedure, you can declare parameters or local variables to process either single row or multiple row query results. Multiple row query results within a procedure must be processed one row at a time, by means of a select cursor.
PAGE 130
::sqlcode = 0 to ensure that the SELECT was successful before inserting data into the PurchDB.Discounts table. Using a Select Cursor If your procedure must process a set of rows one at a time, you can use a cursor to loop through the set and perform desired operations, as in the following: CREATE PROCEDURE PurchDB.DiscountAll(Percentage DECIMAL(4,2)) AS BEGIN DECLARE SalesPrice DECIMAL(6,2); DECLARE C1 CURSOR FOR SELECT SalesPrice FROM PurchDB.
PAGE 131
For example, create a procedure as follows: CREATE PROCEDURE PurchDB.PartNo2 AS BEGIN SELECT * FROM PurchDB.Parts WHERE PartNumber LIKE '11%'; SELECT PartNumber, BinNumber, QtyOnHand FROM PurchDB.Inventory WHERE PartNumber LIKE '11%'; END; When you execute the procedure, the following is displayed: execute procedure purchdb.
PAGE 132
Error Handling in Procedures Not Invoked by Rules You must provide explicit mechanisms for error handling inside procedures. The techniques you use for this depend on whether or not the procedure is invoked by the ring of a rule. This section describes error handling within a procedure that is not invoked by a rule. For information about error handling in procedures invoked by rules, see the section \Error Handling in Procedures Invoked by Rules," below.
PAGE 133
Integer divide by zero. (DBERR 2601) Error occurred executing procedure PURCHDB.DISCOUNT statement 2. (DBERR 2235) Error occurred during evaluation of the condition in an IF or WHILE statement or the expression in a parameter or variable assignment. Procedure execution terminated.
PAGE 134
Whether the procedure is expected to have COMMIT or ROLLBACK statements. Whether the procedure is expected to be atomic. The following practices are suggested to ensure that a procedure will always execute as expected: Procedure execution should not span transaction boundaries.
PAGE 135
Understanding Rules Rules allow you to de ne generalized constraints by invoking procedures whenever speci ed operations are performed on a table. The rule res, that is, invokes a procedure, each time the speci ed operation (such as INSERT, UPDATE, or DELETE) is performed and the rule's search condition is satis ed. Rules tie procedures to particular kinds of data manipulation statements on a table. This permits data processing to be carried out by the DBEnvironment itself.
PAGE 136
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 di erent ways, depending on your needs.
PAGE 137
1. 2. 3. 4. 5. 6. 7. 8. 9. 10. 11. Delete from Parts table. Fire rule RemovePart . Invoke procedure RemovePart . Delete from Inventory table. Delete from SupplyPrice table. Fire rule RemoveVendPart . Invoke procedure RemoveVendPart . Delete from OrderItems table. Delete from SupplyBatches table. Fire rule RemoveBatchStamp . Delete from TestData table.
PAGE 138
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.SupplyBatches WHERE VendPartNumber = :VendPartNum; FETCH SupplyCursor INTO :VendPartNum; ENDWHILE; CLOSE SupplyCursor; DELETE FROM PurchDB.
PAGE 139
BEGIN DECLARE rows INTEGER NOT NULL; SELECT COUNT(*) INTO :rows FROM PurchDB.Orders WHERE VendorNumber = :VendorNumber; IF :rows <> 0 THEN RAISE ERROR 1 MESSAGE 'Vendor number exists in the "Orders" table.'; ENDIF; SELECT COUNT(*) INTO :rows FROM PurchDB.SupplyPrice WHERE VendorNumber = :VendorNumber; IF :rows <> 0 THEN RAISE ERROR 1 MESSAGE 'Vendor number exists in "SupplyPrice" table.'; ENDIF; END; PurchDB.DelVendor checks for the existence of the use of a vendor number in two tables: PurchDB.
PAGE 140
Special Considerations for Procedures Invoked by Rules Procedures operate somewhat di erently when invoked by rules than when invoked directly by a user. The di erences are most pronounced in several areas: Transaction handling. E ects of rule chaining. Invalidation of sections. Changing session attributes. Performance considerations.
PAGE 141
conditions are mutually exclusive or not. The rules are checked for each row an INSERT, DELETE, or UPDATE statement a ects. If multiple rules can a ect a single row, the order of their execution is not guaranteed to be xed if the section is ever revalidated. To avoid potential problems, it is best to ensure that rules a ecting the same statement have mutually exclusive WHERE conditions or that the order of execution of the procedures they invoke is unimportant.
PAGE 142
DISABLE RULES can be used to ensure that the rule depth of 20 is not exceeded, if the chain of rule dependencies is understood well enough for the appropriate placement of this statement. SET PRINTRULES ON and SET PRINTRULES OFF a ect the printing of rule names of rules not yet red, or of rows not yet checked. Performance Considerations The placement of conditions on execution of statements within the ring of a rule should be examined carefully.
PAGE 143
Concurrency Control through Locks and Isolation 5 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. For example, one user could delete a row while another user is in the process of updating it.
PAGE 144
Defining Transactions Concurrency control in ALLBASE/SQL operates at the level of the transaction, which identi es 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.
PAGE 145
Understanding ALLBASE/SQL Data Access Concurrent access to data by multiple users is facilitated by the use of a shared data bu er for all users of an ALLBASE/SQL DBEnvironment. Understanding how this bu er 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. Multiuser DBEnvironment A single data bu er services the needs of all users of the DBEnvironment.
PAGE 146
when bu er space is needed for some other page. The use of the bu er also promotes quick access to the same pages of data by di erent transactions, because a page may not have to be read in from disk if it is already in the bu er. When you issue a query, you request a speci c set of rows and columns from di erent tables in a database. The content of this set of rows and columns is the query result. For every query, ALLBASE/SQL maintains a cursor, which is a pointer to a row in the query result.
PAGE 147
Basics of Locking The following are the two basic requirements of locking: Read operations on data pages must acquire share locks before data can be retrieved. Write operations on data pages must obtain exclusive locks before data is modi ed. Lock types are described in more detail in a later section. When a lock is obtained, the transaction ID (a number), the name of the object locked, and the type of lock acquired are stored in a lock list in shared memory.
PAGE 148
Locks on Index Pages B-tree indexes on PRIVATE and PUBLICREAD user tables are never locked, because concurrency control on the index is already achieved via the table level locks that are always acquired on these tables. B-tree indexes on PUBLIC or PUBLICROW user tables are not locked for read operations, but they are locked with intention exclusive (IX) page locks for write operations.
PAGE 149
Defining Isolation Levels between Transactions Isolation level is the degree to which a transaction is separated from all other concurrent transactions. Four levels are possible, shown here in order from most to least restrictive: Repeatable read (RR)|the default Cursor stability (CS) Read committed (RC) Read uncommitted (RU) In general, you should choose the least restrictive possible isolation level for your needs in order to achieve the most concurrency.
PAGE 150
No other transactions can modify the row on which the transaction has a cursor positioned. A shared lock is kept on the row or page that the cursor is currently pointing to. When the cursor is advanced to the next page of data and nothing has been updated on the previous page, the lock on that previous page is released. If an update is done on a data page, the exclusive lock on that page is retained until the transaction ends with a COMMIT WORK or ROLLBACK WORK statement.
PAGE 151
Use the RC isolation level for improved concurrency, especially in transactions which include a long duration of time between fetches. When you must update following a FETCH statement using the RC isolation level, use the REFETCH statement rst, which obtains and holds locks on the current page, thus letting you verify the continued existence of the data you are interested in.
PAGE 152
page level for update operations and are not locked at all on reads. Table, page, and row level locking are illustrated in Figure 5-3 and Figure 5-4. Figure 5-3 portrays a query that accesses two pages of a table. Figure 5-3. Page Versus Table Level Locking With page level locking, pages containing data scanned for the query are locked. All other pages can be locked by other transactions.
PAGE 153
Table size can a ect concurrency at the page level. For example, if a small table occupies only one page, then the e ect of a page level lock is the same as locking the entire table. In the case of small tables where frequent access is needed by multiple transactions, row level locking can provide the best concurrency. After issuing an UPDATE STATISTICS statement on a table, you can query the SYSTEM.TABLE view to determine how many pages it occupies.
PAGE 154
being modi ed by any other transaction, then ALLBASE/SQL internally requests a SIX lock on the table. After an SIX lock has been granted on a PUBLIC table, no additional locks are acquired when a page is read, but an X page lock is acquired when a page is written. After an SIX lock has been granted on a PUBLICROW table, no additional locks are acquired when a row is read, but an IX page lock and an X row lock are acquired when a row is written. Exclusive (X): Prevents any access by other users.
PAGE 155
Lock Compatibility Table Table 5-1 shows the compatibility of di erent lock types. A Y (yes) at the intersection of a row and column in the table indicates that two locks are compatible at the same level of granularity; a blank space indicates that they are not compatible. Table 5-1.
PAGE 156
What Determines Lock Types ALLBASE/SQL locks one or more of the following three objects: Tables. Rows or pages of tables or entire tables are locked when you execute SQL statements referencing them. PCRs. Pages of PCRs (indexes that support referential constraints) are locked when ALLBASE/SQL updates a key value. Indexes. Pages of indexes are locked when ALLBASE/SQL updates an index. System tables. Rows or pages in one or more system tables are locked when you execute any SQL statement.
PAGE 157
Locking Structure Implicit at CREATE TABLE Time Table Table 5-2 shows the general locking structure used for a table depending on the type of locking assigned when the table is created. For clarity, the table shows only the locks obtained for index scans. (Scan type is described in a later section.) Table 5-2.
PAGE 158
You can upgrade the implicit locking mode of a table to a more severe level by using the LOCK TABLE statement. Thus, you can lock a PUBLIC, PUBLICROW, or PUBLICREAD table in EXCLUSIVE mode. However, you cannot downgrade the implicit locking mode. If you attempt to lock a PRIVATE table in SHARE mode, the LOCK TABLE statement has no e ect.
PAGE 159
specifying the query of interest. Then perform a query on the SYSTEM.PLAN view in the system catalog to display the optimizer's choices. For more information, refer to the section \Using GENPLAN to Display the Access Plan" in the \SQL Queries" chapter. If you are reading a large table, and if you do not expect it to be updated by anyone while your transaction is running, you can avoid excessive overhead in shared memory from locks obtained on each page by using the LOCK TABLE statement in SHARE mode.
PAGE 160
Table 5-4.
PAGE 161
Updatability of Cursors or Views When a transaction uses cursors or views to access and manipulate data, the kinds of locks obtained depend partly on whether the cursors or views are updatable according to the rules presented under \Updatability of Queries" in the \SQL Queries" chapter. Table 5-3 shows the locks obtained on updatable views and on updatable cursors declared FOR UPDATE; they are listed in the \Read for Update" column in the table.
PAGE 162
Figure 5-6. Scope and Duration of Share Locks for Different Isolation Levels 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.
PAGE 163
Transactions 3 and 4, executing concurrently, each need a share lock on page A. Transactions 3 and 4 wait, because of an upcoming exclusive lock request. User 1 issues the following statement: isql=> COMMIT WORK; Transaction 1 terminates, so transaction 2 obtains its exclusive lock on page A. Transactions 3 and 4 still wait. User 2 issues the following statement: isql=> COMMIT WORK; Transaction 2 terminates, so transactions 3 and 4 both obtain share locks on page A.
PAGE 164
Figure 5-8. Lock Requests 2: Waiting for Share Locks Figure 5-9.
PAGE 165
Sample Transactions Using Isolation Levels The following sections show typical situations in which di erent isolation levels a ect 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. Two users each issue the following CONNECT statement (assume they are connecting from a di erent group and account than the one containing PartsDBE): isql=> CONNECT TO 'PartsDBE.SomeGrp.
PAGE 166
> WHERE VendorNumber = 9001; Transaction 2 now waits for an exclusive lock on a page in the Vendors table, because transaction 1 still has a cursor positioned on that page. 4. User 1 now exits from the ISQL browser, but does not issue a COMMIT WORK statement. 5. Transaction 2 can now complete the update, because transaction 1's cursor is no longer positioned on the page that transaction 2 wishes to update. 6.
PAGE 167
isql=> UPDATE PurchDB.Vendors SET ContactName = 'Rogers, Joan' > WHERE VendorNumber = 9005; 3. User 2 then sets the RU isolation level for transaction 2 and issues a query: isql=> BEGIN WORK RU; isql=> SELECT * FROM PurchDB.Vendors WHERE VendorNumber = 9005; User 2 sees the desired row in the ISQL browser, where the contact name for vendor 9005 is Rogers, Joan , even though user 1 has not issued a COMMIT WORK statement. In other words, user 2 has read uncommitted data.
PAGE 168
You can set the amount of time a transaction will wait for a lock by using the SET USER TIMEOUT statement, or by setting a default timeout value using the ALTDBE command in SQLUtil. If no timeout value is set as a default, the transaction will wait until the resource is released. Consult your database administrator about default timeout values. Deadlocks The second kind of con ict is known as a deadlock between two transactions.
PAGE 169
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 di erent transactions in di erent order.
PAGE 170
Avoiding Deadlock The tradeo 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.
PAGE 171
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; SET CONNECTION 'DBE2'; UPDATE TABLEB SET COL1 = 7; SET CONNECTION 'DBE2'; SELECT * FROM TABLEB; SET CONNECTION 'DBE1'; SELECT * FROM TABLEA; Obtains X table lock. Obtains X table lock. Waits. Waits|Undetectable Deadlock.
PAGE 172
Monitoring Tasks Table 5-5 summarizes the monitoring tasks related to locking you can perform with SQLMON: Table 5-5.
PAGE 173
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.
PAGE 174
In addition, application programs must be capable of distinguishing double-quoted names. To prevent any possible con ict, minimize the use of double-quoted basic names.
PAGE 175
Owner Names Owner names can be one of the following: DBEUserID Group name Class name Authorization Names An authorization name identi es an owner name de ned 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.
PAGE 176
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.
PAGE 177
DBEFile and Log File Identifiers DBEFiles and log les have logical names which conform to the rules for ALLBASE/SQL basic names. DBEFile and log le names are stored in the system catalog. In addition to logical names, the physical DBEFiles and log les are referred to in the SQL syntax by system le names. If the group and account are not given, ALLBASE/SQL assumes the name speci ed is in the current group and account. System le names are always enclosed in single quotation marks in SQL commands.
PAGE 178
PAGE 179
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. The following sections are presented: Type Speci cations Value Comparisons Over ow and Truncation Under ow Type Conversion Null Values Decimal Operations Date/Time Operations Binary Operations Long Operations Native Language Data A data type de nes a set of values.
PAGE 180
Table 7-1. ALLBASE/SQL Data Types Group Data Type Alphanumeric CHAR[ACTER][(n )] Numeric Description String of xed length n , where n is an integer from 1 to 3996 bytes. The default size is CHAR (1). The keyword CHARACTER is a synonym for CHAR. VARCHAR(n ) String of variable length no greater than n , where n must be an integer from 1 to 3996 bytes.
PAGE 181
Table 7-1. ALLBASE/SQL Data Types (continued) Group Date/Time Binary Data Type Description DATE String of form 'YYYY-MM-DD', where YYYY represents the calendar year, MM is the month, and DD is the day of the month. DATE is in the range from '0000-01-01' to '9999-12-31'. TIME String of the form 'HH:MI:SS: where HH represents hours, MI is minutes, and SS is seconds. TIME is in the range from '00:00:00' to '23:59:59'. DATETIME String of the form 'YYYY-MM-DD HH:MI:SS.
PAGE 182
The following table contains the storage requirements of the various data types. Table 7-2. Data Type Storage Requirements Type Storage Required CHAR (n) n bytes (where n must be an integer from 1 to 3996) VARCHAR (n) n bytes (where n must be an integer from 1 to 3996) DECIMAL (p[,s]) 4 bytes (where p <= 7) or 8 bytes (where 7 < p <= 15) or 12 bytes (where 15 < p <= 23) or 16 bytes (where p > 23) FLOAT 8 bytes REAL 4 bytes INTEGER 4 bytes.
PAGE 183
the greatest value. ALLBASE/SQL attempts to convert CHAR or VARCHAR strings to the default date/time format before performing the comparison. When you compare a BINARY and a VARBINARY hexadecimal string, ALLBASE/SQL pads the shorter binary string with binary zeroes to the length of the longer string. When comparing two BINARY or VARBINARY hexadecimal strings having di erent lengths, ALLBASE/SQL compares the excess binary digits of the longer binary string with binary zeroes.
PAGE 184
Underflow Under ow occurs when a FLOAT or a REAL value is too close to zero to be represented by the hardware. Under ow always causes an error. Type Conversion ALLBASE/SQL converts the type of a value in the following situations: Including values of di erent types in the same expression. Moving data from a host variable to a column or a column to a host variable of a di erent type. The valid type combinations are shown in Table 7-3. Table 7-3.
PAGE 185
SMALLINT to DECIMAL Over ow of the integer part and truncation of the fractional part of a number can occur during a FLOAT-to-DECIMAL conversion, because ALLBASE/SQL aligns the decimal points.
PAGE 186
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.
PAGE 187
Addition and Subtraction p = MIN(27, MAX (p1 0s 1 , p2 0 s ) + MAX(s , s )+ 1) 2 1 2 s = MAX (s1,s2) Multiplication p = MIN (27, p1 + p2 ) s = MIN (27, s1 + s2 ) Division p = 27 s = 27 0 MIN (27, p1 0s 1 + s2 ) where p1 and s1 describe the numerator operand, and p2 and s2 describe the denominator operand.
PAGE 188
Examples INSERT DATETIME, DATE, TIME and INTERVAL values: INSERT INTO ManufDB.TestData (BatchStamp, TestDate, TestStart, TestEnd, LabTime, PassQty, TestQty) VALUES ('1984-08-19 08:45:33.123', '1984-08-23', '08:12:19', '13:23:01', '5 10:35:15.700', 49, 50) SELECT DATE and TIME values: SELECT TestDate, TestStart FROM ManufDB.TestData WHERE TestDate = '1984-08-23' DATETIME and INTERVAL values: SELECT BatchStamp, LabTime FROM ManufDB.
PAGE 189
Table 7-5.
PAGE 190
You can also use the Add Months function to add or subtract from the month portion of the DATE or DATETIME column. In the result, the day portion is una ected, only the month and, if necessary, the year portions are a ected. However, if the addition of the month causes an invalid day (such as 89-02-30), then a warning message is generated and the value is truncated to the last day of the month.
PAGE 191
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 les, 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.
PAGE 192
Since LONG data for PartMap will be stored in the same DBEFileSet as its related table, PartsIllus, it goes to PartsIllusSet. ALTER TABLE PurchDB.PartsIllus ADD PartMap LONG VARBINARY(70000) Defining Input and Output with the LONG Column I/O String The INSERT and UPDATE statements use the LONG column I/O string to de ne the various input and output parameters for any LONG column. You need to understand this string in order to input, change, or retrieve LONG data.
PAGE 193
INSERT INTO PurchDB.PartsIllus VALUES ('hammer' 100, '< hammer.tools >hammer') Using INSERT with the Overwrite Option When you want to reuse an existing output device le when the inserted data is later selected or fetched, specify the overwrite option. Here if le wrench already exists at INSERT time, it is overwritten: INSERT INTO PurchDB.PartsIllus VALUES ('hammer', 100, '< hammer.tools >!wrench') Using INSERT with the Append Option You can append LONG data to an existing le.
PAGE 194
Using SELECT with LONG Column Data The concept of how data is retrieved di ers from that of non-LONG columns. The output portion of the LONG column I/O string (rather than the data itself) is obtained with the SELECT or FETCH statement. The LONG data goes to a le or heap space. In this example, the SELECT statement places the LONG data from the PartPicture column in a le or in heap space, as speci ed in the LONG column I/O string when the PartPicture column was inserted or updated.
PAGE 195
You may want to change the output le name but not the LONG data associated with a particular column. Here newhammer becomes the output device name. When LONG column PartPicture is SELECTed or FETCHed, output is appended to the le newhammer. UPDATE PurchDB.PartsIllus SET PartPicture = '>>newhammer' WHERE PartName = 'hammer' Using UPDATE with Heap Space Input and Output You may decide to use heap space as your input device. Output data may be directed to a heap address.
PAGE 196
Native Language Data Character data in the DBEnvironment can be represented in the native language speci ed 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 de ned in bytes. Thus a column de ned as CHAR (20) could hold 20 characters in ASCII or 10 characters in Japanese Kanji. Numeric data must be in ASCII representation.
PAGE 197
8 Expressions This chapter discusses value speci cation.
PAGE 198
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.
PAGE 199
Expression Parameters +, 0 ColumnName USER 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. is the name of a column from which a value is to be taken; column names are de ned in the \Names" chapter.
PAGE 200
Expression ProcedureParameter Built-inVariable AddMonthsFunction AggregateFunction Constant DateTimeFunction CurrentFunction LongColumnFunction StringFunction CASTFunction (Expression) * / + 0 jj TIDFunction 8-4 Expressions contains a value that is passed into or out of a procedure.
PAGE 201
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.
PAGE 202
Expression Note To be consistent with the standard SQL and to support portability of code, it is strongly recommended that you use a 01 to indicate a NULL value. However, ALLBASE/SQL interprets all negative indicator variable values as indicating a NULL value in the corresponding host variable.
PAGE 203
Expression Example The result length of PartNumber jj VendPartNumber is 32 in this example. CREATE TABLE PurchDB.SupplyPrice (Part Number CHAR(16) NOT CASE SENSITVE not null unique, VendorNumber INTEGER VendPartNumber CHAR(16) lang=german, UnitPrice DECIMAL (10,2), Delivery Days SMALLINT, DiscountQty SMALLINT) SELECT PartNumber || VendPartNumber, UnitPrice from PurchDB.
PAGE 204
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 di erent from a simple addition operator in that it adjusts the day eld 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').
PAGE 205
Add Months Function Description The Add Months function adds a duration of months to a DATE or DATETIME expression. Only the month portion of the value is a ected, and, if necessary, the year portion. The day portion of the date is unchanged unless the result would be invalid (for 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.
PAGE 206
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 satis es a SELECT statement. Aggregate functions can be speci ed in the select list and the HAVING clause. Refer to the explanation of the SELECT statement for more details.
PAGE 207
Aggregate Functions SUM COUNT * COUNT ColumnName ALL DISTINCT nds 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. counts all rows in all columns, including rows containing NULL values. The result is INTEGER.
PAGE 208
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 3 2 AS DataType ,FormatSpec ) , Parameters Expression DataType is the value to be converted.
PAGE 209
CAST Function Table 8-1.
PAGE 210
CAST Function If the source value is not a numeric string, an error occurs. If the target data type is CHAR(n), and the source data type is an exact numeric, the result is a character representation of that exact numeric. If the source value is less than zero, the rst character of the result is a minus sign. Otherwise, the rst character is a number or a decimal point. If the length of the resulted string is less than n, then blanks are added on the right.
PAGE 211
CAST Function portion of the date/time column. If the source data type of CAST is date/time data type, and the target data type is INTEGER, all rules for TO INTEGER to convert date/time into INTEGER will be applied. The FormatSpec must be used to specify a single component of the date/time data type (i.e. HH, MM, SS, DAYS, etc.). Other numeric types are also allowed using CAST.
PAGE 212
Constant A constant is a speci c numeric, character, or hexadecimal value. Scope SQL Data Manipulation Statements SQL Syntax 8 IntegerValue > > > > < FloatValue 9 > > > > = > > 'CharacterString' > > : > > > > ; FixedPointValue 0xHexadecimalString 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 oating point number compatible with the FLOAT or REAL data types, for example: .
PAGE 213
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 8 < CURRENT_DATE : 9 = 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.
PAGE 214
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. With TO CHAR you can specify an output format for a date/time column value other than the default format.
PAGE 215
Date/Time Functions FormatSpeci cation speci es the format of ColumnName or CharacterValue . Refer to the syntax for FormatSpeci cation later in this section. Format elements are presented in the \Description" section below.
PAGE 216
Date/Time Functions ::sqlwarn6 ::activexact The rst six of these have the same meaning that they have as elds in the SQLCA in application programs. ::activexact indicates whether a transaction is in progress or not. For additional information, refer to the application programming guides and to the chapter \Constraints, Procedures, and Rules." Description If the format speci cation is optional and it is not supplied, the proper default format is used.
PAGE 217
Date/Time Functions 'DAYOFWEEK' 'Dayofweek' 'dayofweek' 'dAyOfWeEk' --- 7 --- 7 --- 7 --- 7 MONDAY Monday monday error condition Time format is used by the TO TIME function and by the TO CHAR functions on TIME expressions. The default format is 'HH:MI:SS'.
PAGE 218
Date/Time Functions Literals for date/time data types which do not specify all elements of the date/time value are expanded and lled as described below: INTERVAL is zero lled on the left and the right. DATE, TIME, and DATETIME are left- lled with the current values from the system clock, and right- lled with appropriate portions of the default '0000-01-01 00:00:00.000'.
PAGE 219
Date/Time Functions 2. Time format INSERT INTO ManufDB.TestData(teststart, batchstamp) VALUES (TO_TIME('01:53 a.m.','HH12:MI a.m.'), TO_DATETIME('12.01.84 02.12 AM', 'DD.MM.YY HH12.MI AM')) 3. Datetime format UPDATE ManufDB.TestData SET batchstamp = TO_DATETIME('12.01.84 02.12 AM', 'DD.MM.YY HH12.MI AM') WHERE batchstamp = TO_DATETIME('11.01.84 1.11 PM', 'DD.MM.YY HH12.MI PM') 4. Interval format UPDATE ManufDB.TestData SET labtime = TO_INTERVAL('06 10:12:11.111', 'DAYS HH:MI:SS.
PAGE 220
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 .
PAGE 221
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 le. 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.
PAGE 222
String Functions String functions return partial values or attributes of character and BINARY (including LONG) string data. Scope SQL Data Manipulation Statements SQL Syntax STRING_LENGTH (StringExpression) SUBSTRING (StringExpression,StartPosition,Length) Parameters STRING_LENGTH StringExpression returns an integer indicating the length of the parameter. If StringExpression is a xed length string type, STRING_LENGTH will return the xed length.
PAGE 223
String Functions If Length is a simple constant, the substring returned has a maximum length equal to the value of the constant.
PAGE 224
String Functions Examples 1. STRING LENGTH example In the SELECT statement below, the PartsIllus table is searched for any row whose PartPicture contains more than 10000 bytes of data, and whose PartName is longer than 10 bytes. CREATE TABLE PartsIllus (PartName VARCHAR(16), PartNumber INTEGER, PartPicture LONG VARBINARY(1000000) in PartPictureSet) IN PartsIllusSet SELECT FROM WHERE AND PartNumber, PartName PartsIllus STRING_LENGTH(PartPicture) > 10000 STRING_LENGTH(PartName) > 10 2.
PAGE 225
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 3 3 22 Owner. 3TableName 2 (4 Owner.
PAGE 226
TID Function Description The TID function can be used with user tables and updatable views and with system base tables and system views. It cannot be used with non-updatable views (those containing JOIN, UNION, GROUP BY, HAVING, or aggregate functions) nor on system 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.
PAGE 227
TID Function Example isql=> SELECT tid(), PartNumber > FROM PurchDB.Parts; select tid(), PartNumber from PurchDB.
PAGE 228
PAGE 229
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 Quanti ed Predicate A search condition speci es criteria for choosing rows to select, update, delete, insert, permit in a table, or re rules on.
PAGE 230
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 quali es for the select, update, or delete operation. If the predicate evaluates to FALSE or unknown for a row, the row is not operated on.
PAGE 231
Search Condition Description Predicates in a search condition are evaluated as follows: Predicates in parentheses are evaluated rst. NOT is applied to each predicate. AND is applied next, left to right. OR is applied last, left to right. When a predicate contains an expression that is null, the value of the predicate is unknown. Logical operations on such a predicate result in the following values, where a question mark (?) represents the unknown value: Figure 9-1.
PAGE 232
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 speci ed range. If the NOT option is used, the predicate evaluates to true if a value does not fall within the speci ed range. Note that the second value must be less than or equal to the third value for BETWEEN to possibly be TRUE and for NOT BETWEEN to possibly be FALSE.
PAGE 233
Comparison Predicate Comparison Predicate A comparison predicate compares two expressions using a comparison operator. The predicate evaluates to TRUE if the rst expression is related to the second expression as speci ed in the comparison operator.
PAGE 234
Comparison Predicate Description Character strings are compared according to the HP eight-bit ASCII collating sequence for ASCII data, or the collation rules for the native language of the DBEnvironment for NLS data. Column data would either be ASCII data or NLS data depending on how the column was declared upon its creation. Constants are ASCII data or NLS data depending on whether you are using NLS or not.
PAGE 235
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 satis es 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 the \SQL Statements" chapter.
PAGE 236
IN Predicate An IN predicate compares an expression with a list of speci ed 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.
PAGE 237
IN Predicate USER USER evaluates to the DBEUserID. In ISQL, it evaluates to the DBEUserID of the ISQL user. From an application program, it evaluates DBEUserID of the individual 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. indicates a value of type INTEGER or SMALLINT. indicates a value of type FLOAT. indicates a value of type DECIMAL.
PAGE 238
IN Predicate Built-inVariable is one of the following built-in variables used for error handling: ::sqlcode ::sqlerrd2 ::sqlwarn0 ::sqlwarn1 ::sqlwarn2 ::sqlwarn6 ::activexact The rst six of these have the same meaning that they have as elds 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.
PAGE 239
IN Predicate Example Get part numbers of parts whose weight is 12, 16, or 17. SELECT P.PNO FROM P WHERE P.WEIGHT IN (12, 16, 17) Get the names of suppliers who supply part number 'P2'. SELECT S.SNAME FROM S WHERE S.SNO IN (SELECT SP.SNO FROM SP WHERE SP.SNO = 'P2') 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.
PAGE 240
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.
PAGE 241
LIKE Predicate HostVariable1 EscapeChar HostVariable2 LocalVariable2 ProcedureParameter2 ? 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. identi es the host variable in which the pattern is stored.
PAGE 242
LIKE Predicate Example Vendors located in states beginning with an A are identi ed. SELECT VendorName FROM PurchDB.Vendors WHERE VendorState LIKE 'A%' Vendors whose names begin with ACME are identi ed. SELECT VendorName FROM PurchDB.
PAGE 243
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.
PAGE 244
NULL Predicate Built-inVariable AddMonthsFunction AggregateFunction Constant ConversionFunction CurrentFunction LongColumnFunction StringFunction TIDFunction (Expression) NOT is one of the following built-in variables used for error handling: ::sqlcode ::sqlerrd2 ::sqlwarn0 ::sqlwarn1 ::sqlwarn2 ::sqlwarn6 ::activexact The rst six of these have the same meaning that they have as elds in the SQLCA in application programs.
PAGE 245
Quantified Predicate Quantified Predicate A quanti ed predicate compares an expression with a list of speci ed 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 speci ed by the comparison operator and the quanti er.
PAGE 246
Quantified Predicate ValueList de nes a list of values to be compared against the expression's value.
PAGE 247
Quantified Predicate HostVariable LocalVariable ProcedureParameter ? identi es the host variable containing the column value. 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 contains a value in a procedure. contains a value that is passed into or out of a procedure. indicates a dynamic parameter in a prepared SQL statement.
PAGE 248
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.
PAGE 249
10 SQL Statements This chapter describes 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. General-purpose statements are used programmatically, interactively, and in procedures.
PAGE 250
Table 10-1. SQL Statement Summary (continued) Group General-purpose statements (continued) Category Statement Statement Use Data de nition Databases CREATE SCHEMA De nes a database and associates it with an authorization name. Indexes CREATE INDEX De nes an index for a table based on one or more of its columns. DROP INDEX Deletes an index.
PAGE 251
Table 10-1. SQL Statement Summary (continued) Group General-purpose statements (continued) Category Partitions Statement CREATE PARTITION Statement Use De nes a partition for audit logging in the DBEnvironment. DROP PARTITION Deletes a partition. Data manipulation Transaction management DELETE Deletes one or more rows from a single table or view. INSERT Adds a row to a single table or view. SELECT Retrieves data from one or more tables or views.
PAGE 252
Table 10-1. SQL Statement Summary (continued) Group Category Statement SET SESSION Sets transaction attributes for a session. SET TRANSACTION Sets execution attributes for a transaction. Executing procedures EXECUTE PROCEDURE Invokes a procedure. Other RAISE ERROR Causes a user-de ned error to occur and speci es the error number and text to be raised. Concurrency CREATE TABLE De nes the automatic locking strategy and implicit authority grants used for a table.
PAGE 253
Table 10-1. SQL Statement Summary (continued) Group Application programming statements (continued) Category Bulk manipulations Cursor management Preprocessor directives Statement Statement Use BULK FETCH Retrieves multiple rows from an active set associated with a cursor. (See FETCH.) BULK INSERT Inserts multiple rows into a single table. (See INSERT.) BULK SELECT Retrieves multiple rows not associated with a cursor. (See SELECT.) ADVANCE Advances a procedure cursor.
PAGE 254
Table 10-1. SQL Statement Summary (continued) Group Category Statement Statement Use Application programming statements (continued) WHENEVER Dynamically DESCRIBE preprocessed queries Database administration statements Obtains information about the results of a dynamic statement. EXECUTE EXECUTE IMMEDIATE PREPARE Refer to general-purpose statements. Status messages SQLEXPLAIN Retrieves a message describing the status of SQL statement execution.
PAGE 255
Table 10-1. SQL Statement Summary (continued) Group Database administration statements (continued) Category STOP DBE Statement Statement Use Terminates all DBE sessions and causes a checkpoint to be taken, recovering log le space if nonarchive logging is in e ect. TERMINATE QUERY Terminates a running Query. TERMINATE TRANSACTION Stops the transaction. TERMINATE USER Stops the DBE session for a speci c user.
PAGE 256
Table 10-1. SQL Statement Summary (continued) Group Category Database administration statements (continued) DBEFileSets CREATE DBEFILESET De nes a DBEFileSet. DBEFileSets SET DEFAULT DBEFILESET Sets a default DBEFileSet. DROP DBEFILESET Removes the de nition of a DBEFileSet from the system catalog. CREATE TEMPSPACE De nes and creates a temporary storage space. DROP TEMPSPACE Removes the de nition of a temporary storage space from the system catalog.
PAGE 257
Table 10-1. SQL Statement Summary (continued) Group Category Database administration statements (continued) Dual logging START DBE NEW Causes ALLBASE/SQL to maintain two separate, identical logs, when used with the DUAL LOG option. Makes audit logging e ective when used with AUDIT LOG option. Audit logging DISABLE AUDIT LOGGING Disables current audit logging for a session. Log comment LOG COMMENT Enters a user comment in the log le.
PAGE 258
Table 10-1. SQL Statement Summary (continued) Group Procedure statements Category General statements Statement Assignment (=) Statement Use Assigns a value to a local variable or parameter in a procedure. DECLARE Variable De nes a local variable within a procedure. Control ow statements 10-10 SQL Statements PRINT Stores information to be displayed by ISQL or an application program. BEGIN Begins a single statement or group of statements within a procedure.
PAGE 259
ADD DBEFILE ADD DBEFILE The ADD DBEFILE statement updates a row in SYSTEM.DBEFile to show the DBEFileSet with which the le is associated. Scope ISQL or Application Program SQL Syntax ADD DBEFILE DBEFileName TO DBEFILESET DBEFileSetName Parameters DBEFileName DBEFileSetName is the name of a DBEFile previously de ned and created by the CREATE DBEFILE statement. is the name of a previously de ned DBEFileSet. You can use the CREATE DBEFILESET statement to de ne DBEFileSets.
PAGE 260
ADD DBEFILE When the index is subsequently dropped, its le space can be assigned to another DBEFileSet. REMOVE DBEFILE ThatDBEFile FROM DBEFILESET Miscellaneous FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF ADD DBEFILE ThatDBEFile TO DBEFILESET SYSTEM ALTER DBEFILE ThisDBEFile SET TYPE = MIXED All rows are later deleted from the table, so you can reclaim le space.
PAGE 261
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 8 9 < DBEUserID =2 3 GroupName , . . . TO GROUP TargetGroupName : ; ClassName Parameters DBEUserID GroupName ClassName TargetGroupName identi es a user to be added. You cannot specify the name of the DBECreator. identi es a group to be added. identi es a class to be added.
PAGE 262
ADD TO 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 on PartsDBE, retrieve data from table PurchDB.Inventory, and update three columns in the table.
PAGE 263
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 rst or next query result set and to initialize information in the associated sqlda type and sqlformat type data structures.
PAGE 264
ADVANCE For a procedure that returns multiple row results of a single format, if the procedure was created with the WITH RESULT clause, it is unnecessary to issue an ADVANCE statement to get format information for each result set, since the format is already known from the DESCRIBE RESULT statement. The ADVANCE statement cancels any current, active query result set.
PAGE 265
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 = 8 9 < TABLE = : INDEX ; MIXED Parameters DBEFileName TYPE = speci es the DBEFile to be altered. speci es 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. INDEX Only index pages can be stored in the DBEFile.
PAGE 266
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.
PAGE 267
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 de nitions must either allow null values or provide default values if the table is not empty. Added columns may specify constraints.
PAGE 268
ALTER TABLE Parameters|AddColumnSpecification ColumnDe nition The syntax of ColumnDe nition is presented under the CREATE TABLE statement. speci es that the named unique or referential constraint speci ed within the Column De nition 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.
PAGE 269
ALTER TABLE SQL Syntax|DropConstraintSpecification DROP CONSTRAINT 2 (ConstraintID , . . . ConstraintID 3 ) Parameters|DropConstraintSpecification ConstraintID is the name of the constraint optionally de ned when the constraint was de ned.
PAGE 270
ALTER TABLE If RESET AUTHORITY is speci ed, the option automatically issues GRANT ALL on Owner .TableName to PUBLIC. Otherwise, the authority on the table remains unchanged. is used to indicate that the authority on the table should be changed to re ect the new table type. If not speci ed, the authority on the table remains unchanged. is used to indicate that the authority currently in e ect on the table should be preserved. This is the default.
PAGE 271
ALTER TABLE The ALTER TABLE statement can be used to change the type of an existing table. Changing the type of a table rede nes the locking strategy that ALLBASE/SQL uses when the table is accessed. You can decide whether to use page or row level locking for your applications. No other transaction can access the table until the transaction that issued the ALTER TABLE statement has committed. The type of a table is changed permanently when you issue a COMMIT WORK statement.
PAGE 272
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 de ne 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.
PAGE 273
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 = Expression; :ProcedureParameter Parameters LocalVariable ProcedureParameter Expression identi es the local variable to which a value is being assigned. The variable name has a : pre x. Local variables are declared in the procedure de nition using the DECLARE statement.
PAGE 274
Assignment (=) Authorization Anyone can use the assignment statement in a procedure de nition. Example :msg = 'Vendor number found in "Orders" table.'; :SalesPrice = :OldPrice; :NewPrice = :SalesPrice*.
PAGE 275
BEGIN BEGIN The BEGIN statement is a compound statement and de nes a group of statements within a procedure. Scope Procedures only SQL Syntax 2 BEGIN Statement; 32 3 . . . 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.
PAGE 276
BEGIN ARCHIVE If you do not have TURBO STORE and must do a static backup of your DBEnvironment, you can use the BEGIN ARCHIVE statement in conjunction with and immediately prior to the COMMIT ARCHIVE statement to start a new archive log le before a DBEnvironment is backed up. However, if you have TURBO STORE, the recommended approach to initiate archive logging is to use the SQLUtil STOREONLINE command, or to use the ARCHIVE option with the START DBE NEWLOG statement.
PAGE 277
BEGIN ARCHIVE Example A single-user DBE session is established. START DBE 'PartsDBE.SomeGrp.SomeAcct' Note Always use these two statements together in sequence. FFFFFFFFFFFFFFFFFFFFFFFFFFFFFF BEGIN ARCHIVE COMMIT ARCHIVE ALLBASE/SQL creates an archive record. The DBEnvironment is shut down. Then the SQLUtil STORE command is used to make an archival copy of the DBEnvironment.
PAGE 278
BEGIN DECLARE SECTION The BEGIN DECLARE SECTION preprocessor directive indicates the beginning of the host variable declaration 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.
PAGE 279
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 2 3 RR 2 36 CS 7 7 BEGIN WORK Priority 6 4 RC 5 RU LABEL 'LabelString' :HostVariable PARALLEL FILL NO Parameters Priority RR CS RC RU LabelString is an integer from 0 to 255 specifying the priority of the transaction. Priority 127 is assigned if you do not specify a priority.
PAGE 280
BEGIN WORK HostVariable FILL PARALLEL FILL NO FILL allowing the DBA to easily identify the transaction being executed by any user at any moment. is a host variable containing the LabelString . is used to optimize I/O performance when loading data and creating indexes. is used to optimize I/O performance for multiple, concurrent loads to the same table. The PARALLEL FILL option must be in e ect for each load. turns o the FILL or PARALLEL FILL option for the duration of the transaction.
PAGE 281
BEGIN WORK To end your transaction, you must issue a COMMIT WORK or ROLLBACK WORK statement. Otherwise, locks set by your transaction are held until a STOP DBE, DISCONNECT, RELEASE, or TERMINATE USER statement is processed. If the maximum number of concurrent DBEnvironment transactions has been reached, the application is placed on a wait queue. If the application times out while waiting, an error occurs. Default and maximum timeout values are speci ed at the DBEnvironment level.
PAGE 282
BEGIN WORK Begin another transaction and set priority, isolation level, and label name. Note that since a ll option is not speci ed, the default (NO FILL) is in e ect. FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF BEGIN WORK 64 RC LABEL 'xact2' . . . Execute . . . SQL statements. Work is committed and the transaction ends.
PAGE 283
CHECKPOINT CHECKPOINT The CHECKPOINT statement causes an ALLBASE/SQL system checkpoint to be taken. Scope ISQL or Application Programs SQL Syntax 2 3 :HostVariable 5 4 CHECKPOINT :LocalVariable :ProcedureParameter Parameters HostVariable LocalVariable ProcedureParameter identi es an output host variable used to communicate the amount of log space available for use. The host variable is an integer. contains a value in a procedure. contains a value that is passed into or out of a procedure.
PAGE 284
CHECKPOINT and changed pages to permanent storage. Retrieval from the DBEnvironment is not suspended during a checkpoint. Contents of the log bu er are also written to the log le(s) when a COMMIT WORK is executed. When you submit a START DBE statement, ALLBASE/SQL processes all log records created since the last checkpoint record. Therefore taking a checkpoint just before stopping the DBE reduces the amount of time that is needed when a DBEnvironment is started up.
PAGE 285
CLOSE CLOSE The CLOSE statement is used to close an open cursor. Scope Application Programs or Procedures SQL Syntax 2 CLOSE CursorName 4 USING 8 <2 : 3 SQL DESCRIPTOR :HostVariable 22 SQLDA AreaName 32 3 INDICATOR :Indicator , . . . 93 = 5 3; Parameters CursorName USING HostVariable Indicator DESCRIPTOR SQLDA AreaName designates the open cursor to be closed. de nes where to place return status and output parameters after closing a dynamic procedure cursor.
PAGE 286
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.
PAGE 287
CLOSE Examples Declare and open a cursor for use in updating values in column QtyOnHand. 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 a user and accepting a new QtyOnHand value go here. The new value is stored in :NewQty. UPDATE PurchDB.
PAGE 288
COMMIT ARCHIVE If you do not have TURBO STORE and you must do a static backup, you can use the COMMIT ARCHIVE statement following the BEGIN ARCHIVE statement to start a new archive log le before a DBEnvironment is backed up. However if you have TURBO STORE, the recommended approach to initiate archive logging is to use the SQLUtil STOREONLINE command or to use the ARCHIVE option with the START DBE NEWLOG statement.
PAGE 289
COMMIT ARCHIVE Authorization You must have DBA authority to use this statement. Example A single-user DBE session is established. START DBE 'PartsDBE.SomeGrp.SomeAcct' ALLBASE/SQL creates an archive record. Note Always use these two statements together in sequence. BEGIN ARCHIVE FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF COMMIT ARCHIVE STOP DBE The DBEnvironment is shut down. Then the SQLUtil STORE command is used to make an archival copy of the DBEnvironment.
PAGE 290
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 2 COMMIT WORK RELEASE 3 Parameters RELEASE terminates your DBE session after the changes made during the transaction are committed. Specifying RELEASE has the same e ect as issuing a COMMIT WORK statement followed by a RELEASE statement.
PAGE 291
COMMIT WORK Authorization You do not need authorization to use the COMMIT WORK statement. 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.
PAGE 292
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.
PAGE 293
CONNECT Description ALLBASE/SQL creates an implicit, brief transaction when the CONNECT statement is issued. When the value of the autostart ag is ON, the CONNECT statement initiates a single-user DBE session if the DBECon le user mode is currently set to single and no other user is accessing the DBEnvironment. A multiuser DBE session is established if the DBECon le user mode is currently set to MULTI.
PAGE 294
CREATE DBEFILE The CREATE DBEFILE statement de nes and creates a DBEFile and places a row describing the le in SYSTEM.DBEFile. A DBEFile is a le that stores tables, indexes, hash structures, and/or LONG data.
PAGE 295
CREATE DBEFILE TYPE = Volume speci es the setting of the DBEFile's TYPE attribute. The following are valid settings: 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. identi es the volume where the DBEFile will reside. Description You use this statement to create all DBEFiles except DBEFile0, which is created when a START DBE NEW statement is processed.
PAGE 296
CREATE DBEFILE Authorization You must have DBA authority to use this statement. Example Create a DBEFile on a speci c volume with a di erent group name than the DBECon resides on. CREATE DBEFILE ThisDBEFile FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF WITH PAGES = 4, NAME = 'ThisFile.
PAGE 297
CREATE DBEFILESET CREATE DBEFILESET The CREATE DBEFILESET statement de nes a DBEFileSet. A DBEFileSet is a group of related DBEFiles; as such, it serves as a mechanism for allocating and deallocating le space for tables. Scope ISQL or Application Programs SQL Syntax CREATE DBEFILESET DBEFileSetName Parameters DBEFileSetName speci es the name to be given to the new DBEFileSet. Two DBEFileSets in the same DBEnvironment cannot have the same name.
PAGE 298
CREATE DBEFILESET Authorization You must have DBA authority to use this statement. Example The DBEFile is used to store rows of a new table.
PAGE 299
CREATE GROUP CREATE GROUP The CREATE GROUP statement de nes a new authorization group. Scope ISQL or Application Programs SQL Syntax 2 3 CREATE GROUP Owner. GroupName Parameters [Owner .]GroupName speci es 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.
PAGE 300
CREATE GROUP Description When you create an authorization group, its owner name and group name are entered into the system catalog. You can then refer to the group in the ADD TO GROUP, REMOVE FROM GROUP, GRANT, REVOKE, TRANSFER OWNERSHIP, and DROP GROUP statements. Authorization You must have RESOURCE or DBA authority to use this statement.
PAGE 301
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 2 32 3 2 3 CLUSTERING INDEX Owner. IndexName ON 3 2 3 2 ASC , ... ) Owner. TableName ( ColumnName DESC CREATE UNIQUE Parameters UNIQUE CLUSTERING prohibits duplicates in the index. If UNIQUE is speci ed, each possible combination of index key column values can occur in only one row of the table.
PAGE 302
CREATE INDEX ASC | DESC speci es the order of the index to be either ascending or descending, respectively. The default is ascending. Specifying DESC does not create a 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.
PAGE 303
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.
PAGE 304
CREATE PARTITION The CREATE PARTITION statement de nes a partition to be used for audit logging purposes. Scope ISQL or Application Programs SQL Syntax CREATE PARTITION PartitionName WITH ID = PartitionNumber Parameters PartitionName PartitionNumber speci es 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. is an integer specifying the partition number.
PAGE 305
CREATE PARTITION 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 de ned, no row appears in SYSTEM.PARTITION for it. The DROP PARTITION and CREATE PARTITION statements are used to change the partition number assigned to a partition other than the default partition. The START DBE NEWLOG statement is used to change the partition number of the default partition.
PAGE 306
CREATE PROCEDURE The CREATE PROCEDURE statement de nes a procedure for storage in a DBEnvironment. A procedure may subsequently be executed through the ring 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 2 3 2 = ProcLangName . ProcedureName LANG CREATE PROCEDURE Owner 32 3 3 2 2 . . . , ParameterDeclaration ) 33 ( ParameterDeclaration 32 2 2 . ..
PAGE 307
CREATE PROCEDURE ProcedureStatement Speci es a statement in the procedure body. The statement may be any one of the following: Local variable declaration (see DECLAREVariable ). Parameter or local variable assignment (see Assignment). Compound statement. A compound statement has the following syntax: BEGIN [Statement;] [...] END; Control ow and status statements IF . . . THEN . . . ELSEIF . . . ELSE . . . ENDIF WHILE . . . DO . . .
PAGE 308
CREATE PROCEDURE SQL Syntax|ParameterDeclaration 2 LANG = ParameterLanguage ParameterName ParameterType 32 32 2 33 DEFAULT DefaultValue NOT NULL OUTPUT ONLY 3 2 Parameters|ParameterDeclaration ParameterName ParameterType ParameterLanguage DefaultValue NOT NULL OUTPUT is the name assigned to a parameter in the procedure. No two parameters in the procedure can be given the same name. You can de ne no more than 1023 parameters in a procedure. indicates what type of data the parameter will contain.
PAGE 309
CREATE PROCEDURE SQL Syntax|ResultDeclaration 2 ResultType LANG = ResultLanguage 32 NOT NULL 3 Parameters|ResultDeclaration ResultType ResultLanguage NOT NULL indicates the data type of a result column in a query result for a query or queries in the procedure. The \Data Types" chapter describes the data types available in ALLBASE/SQL. speci es the language of the result column. A LANG may only be speci ed for a result column with a character data type.
PAGE 310
CREATE PROCEDURE ::sqlwarn2 ::sqlwarn6 ::activexact The rst six of these have the same meaning that they have as elds 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, sqlerrd(3) is used in COBOL and Fortran, sqlerrd[3] is used in Pascal, and sqlerr[2] is used in C. ::activexact indicates whether a transaction is in progress or not.
PAGE 311
CREATE PROCEDURE An attempt to execute a CREATE PROCEDURE statement containing a WITH RESULT clause but no multiple row result set causes an error and the procedure is not created. When a procedure with single format multiple row result sets is created using the WITH RESULT clause, the format speci ed in this clause is stored in the system catalog PROCRESULT table.
PAGE 312
CREATE PROCEDURE Examples 1. DELETE CREATE PROCEDURE ManufDB.RemoveBatchStamp (BatchStamp DATETIME NOT NULL) AS BEGIN DELETE FROM ManufDB.TestData WHERE BatchStamp = :BatchStamp; IF ::sqlcode < > 0 THEN PRINT 'Delete failed.'; ENDIF; END; 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.
PAGE 313
CREATE RULE CREATE RULE The CREATE RULE statement de nes a rule and associates it with speci c kinds of data manipulation on a particular table. The rule de nition speci es the name of a procedure to be executed when the rule res. Scope ISQL or Application Programs SQL Syntax 2 3 CREATE RULE Owner. RuleName AFTER 9 8 ON > > > > =2 3 3< OF 2 Owner .TableName StatementType , . . . FROM > > > > ; : INTO 3 3 2 OLD AS OldCorrelationName 2 . ..
PAGE 314
CREATE RULE OldCorrelationName NewCorrelationName FiringCondition speci es the correlation name to be used within the 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.
PAGE 315
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 ring of a rule.
PAGE 316
CREATE RULE An EXECUTE PROCEDURE call from within a rule is di erent from one issued as a regular SQL statement. Within a rule, you cannot specify host variables, local variables, procedure parameters, or dynamic parameters as parameter values, since host variables are not accessible from the rule. Also, the key word OUTPUT cannot be speci ed, since a procedure called from a rule cannot return any values.
PAGE 317
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.Reports EXECUTE PROCEDURE PurchDB.
PAGE 318
CREATE SCHEMA The CREATE SCHEMA statement creates a schema and associates an authorization name with it. The schema de nes 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.
PAGE 319
CREATE SCHEMA CreateGroup AddToGroup GrantStatement de nes an authorization group. For complete syntax, refer to the CREATE GROUP syntax. 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. speci es the type of authorities for a table, view, or module. For complete syntax, refer to the GRANT syntax.
PAGE 320
CREATE SCHEMA Example In the following example, RecDB is the AuthorizationName (owner name). All the tables created here are owned by RecDB; it is not necessary to repeat the owner name for each creation statement.
PAGE 321
CREATE TABLE CREATE TABLE The CREATE TABLE statement de nes a table. It also de nes 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 de ne 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.
PAGE 322
CREATE TABLE The locking strategy remains unchanged, even if you change the grant. enables the table to be read and updated by concurrent transactions. In general, a transaction locks a page in share mode before reading it and in exclusive mode before updating it. PUBLIC PUBLICROW [Owner .]TableName TableLanguageName ColumnDe nition UniqueConstraint ReferentialConstraint 10-74 SQL Statements This option automatically issues the statement GRANT ALL ON TableName TO PUBLIC.
PAGE 323
CREATE TABLE CheckConstraint UNIQUE HASH ON HashColumnName PrimaryPages HASH ON CONSTRAINT ConstraintID IN PARTITION PartitionName DEFAULT NONE CLUSTERING ON CONSTRAINT only if each row in the referencing table contains either a NULL in a referencing column, or values in the rows of the referencing columns equal the values in the rows of the referenced columns. The syntax of a ReferentialConstraint is presented separately in another section below. de nes a check constraint for the table.
PAGE 324
CREATE TABLE IN DBEFileSetName1 causes the index and data pages in which table information is stored to be allocated from DBEFiles associated with the speci ed DBEFileSet. (Names of available DBEFileSets are recorded in the SYSTEM.DBEFILESET view.) If a DBEFileSet name is not speci ed, the table is created in the default TABLESPACE DBEFileSet. (Refer to syntax for the SET DEFAULT DBEFILESET statement.
PAGE 325
CREATE TABLE CASE SENSITIVE DEFAULT NOT NULL UNIQUE j PRIMARY KEY REFERENCES CHECK SearchCondition ConstraintID DBEFileSetName3 NATIVE-3000 or the language of the DBEnvironment. The default is the language of the DBEnvironment. indicates that upper and lower case letters stored in the column are not considered equivalent. If the column is de ned as NOT CASE SENSITIVE, then its upper and lower case letters are considered equivalent. The default is CASE SENSITIVE.
PAGE 326
CREATE TABLE SQL Syntax|Unique Constraint (Table Level) 2 UNIQUE ( ColumnName , . . . PRIMARY KEY 3 2 ) CONSTRAINT ConstraintID 3 Parameters|Unique Constraint (Table Level) UNIQUE PRIMARY KEY ColumnName [, . . . ] ConstraintID Each ColumnName shall identify a column of the table, and the same column shall not be identi ed more than once. Also, NOT NULL shall be speci ed for each column in this unique constraint column list.
PAGE 327
CREATE TABLE SQL Syntax|Check Constraint (Table Level) 2 CHECK (SearchCondition) CONSTRAINT ConstraintID 3 2 IN DBEFileSetName3 3 Parameters|Check Constraint (Table Level) CHECK SearchCondition ConstraintID DBEFileSetName3 speci es a check constraint. speci es a boolean expression for the check constraint. The result of the boolean expression must not be false for any row of the table. The result may be unknown if a column that is part of the expression is NULL.
PAGE 328
CREATE TABLE Upper and lower case extended characters are treated as equivalent. They are compared to the collation table of a speci c language regardless of case. If no DEFAULT clause is given for a column in the table, an implicit DEFAULT NULL is assumed. Any INSERT statement, which does not include a column for which a default has been declared, causes the default value to be inserted into that column for all rows inserted.
PAGE 329
CREATE TABLE You can use the same set of foreign key columns to reference two di erent primary keys. The maximum size of a hash key is the same as a user-de ned index key, which is determined in the following formula: (NumberOfHashColumns+2)* 2 + SumKeyColumnLengths + 8 <= 1024 An extra 2 bytes must be added for each column that allows NULLS or is a VARCHAR datatype. At most 16 columns are allowed in a hash structure key.
PAGE 330
CREATE TABLE Authorization You must have RESOURCE or DBA authority to use this statement. To de ne referential constraints, the table owner must have REFERENCES authority on the referenced table and referenced columns, own the referenced table, or have DBA authority for the life of the referential constraint.
PAGE 331
CREATE TABLE CREATE PUBLICROW TABLE RecDB.Members (MemberName CHAR(20) NOT NULL, Club CHAR(15) NOT NULL, MemberPhone SMALLINT, PRIMARY KEY (MemberName, Club) CONSTRAINT Members_PK, FOREIGN KEY (Club) REFERENCES RecDB.Clubs (ClubName) CONSTRAINT Members_FK) IN RecFS CREATE PUBLICROW TABLE RecDB.
PAGE 332
CREATE TEMPSPACE The CREATE TEMPSPACE statement de nes and creates a temporary storage space known as a TempSpace. A TempSpace is a location where ALLBASE/SQL creates temporary les to store temporary data when performing a sort, if disk space permits.
PAGE 333
CREATE TEMPSPACE To delete the de nition of a TempSpace, use the DROP TEMPSPACE statement. Authorization You must have DBA authority to use this statement. Example TempSpace temporary les are created in SomeGrp.SomeAcct when SQL Statements require sorting. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx CREATE TEMPSPACE ThisTempSpace WITH MAXFILEPAGES = 360, LOCATION = 'SomeGrp.
PAGE 334
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 2 3 2 2 , . .. CREATE VIEW Owner2. ViewName ( ColumnName 3 IN DBEFileSetName AS QueryExpression 33 2 2 WITH CHECK OPTION CONSTRAINT ConstraintID 3 ) 3 Parameters [Owner .]ViewName ColumnName is the name to be assigned to the view. One owner cannot own more than one view with the same name.
PAGE 335
CREATE VIEW DBEFileSetName ConstraintID speci es the DBEFileSet to be used for storing the section associated with the view. If not speci ed, the default SECTIONSPACE DBEFileSet is used. (Refer to syntax for the SET DEFAULT DBEFILESET statement.) is the optional name of the view check constraint. Description A view de nition 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 .
PAGE 336
CREATE VIEW To drop a constraint on a view, you must drop the view and recreate it without the constraint. You cannot use an ORDER BY clause when de ning a view. If the IN DBEFileSetName clause is speci ed, but the view owner does not have SECTIONSPACE authority for the speci ed 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.
PAGE 337
CREATE VIEW 2. The following view is updatable because it is created from one table. When the table is updated through the view, column values in the SET or VALUES clause are checked against the WHERE clause in the view de nitions. 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.
PAGE 338
DECLARE CURSOR The DECLARE CURSOR statement associates a cursor with a speci ed SELECT or EXECUTE PROCEDURE statement. Scope Application Programs and Procedures SQL Syntax 3 2 DECLARE CursorName IN DBEFileSetName 8CURSOR FOR 92 8 QueryExpression FOR UPDATE OF ColumnName , . . .
PAGE 339
DECLARE CURSOR ExecuteProcedureStatement ExecuteStatementName 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 de ned when you open and advance the cursor are called the active set of the cursor. is speci ed when declaring a procedure cursor for a dynamically preprocessed EXECUTE PROCEDURE statement. It is the StatementName speci ed in the related PREPARE statement.
PAGE 340
DECLARE CURSOR For select cursors only, use the UPDATE statement with the CURRENT OF option to update columns; you can update the columns identi ed 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. Use the CLOSE statement when you are nished operating on the active set or (for a procedure cursor) set(s).
PAGE 341
DECLARE CURSOR Examples 1. Deleting with a cursor The active set of this cursor will contain values for the OrderNumber stored in :OrdNum. xxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxxx DECLARE DeleteItemsCursor CURSOR FOR SELECT ItemNumber,OrderQty FROM PurchDB.
PAGE 342
DECLARE CURSOR 4. Dynamically preprocessed SELECT If you know in advance that the statement to be dynamically preprocessed is not a SELECT statement, you can prepare it and execute it in one step. In other instances, it is more appropriate to prepare and execute the statement in separate operations. EXECUTE IMMEDIATE :Dynam1 The statement stored in :Dynam1 is dynamically preprocessed.
PAGE 343
DECLARE DECLARE Variable Variable The DECLARE Variable statement lets you de ne a local variable within a procedure. Local variables are used only within the procedure. Scope Procedures only SQL Syntax DECLARE 8 2 4 DEFAULT LocalVariable 8 < Constant : NULL 92 , . .. CurrentFunction 3 2 VariableType LANG = VariableLangName 93 = 2 5 NOT ; NULL 3 3 Parameters LocalVariable VariableType VariableLangName DEFAULT NOT NULL speci es the name of the local variable.
PAGE 344
DECLARE Variable For a BINARY column, if the speci ed default value is shorter in length than the target column, it is padded with zeroes. For a BINARY or VARBINARY column, if the speci ed default value is longer than the target column, it is truncated. Authorization Anyone can use the DECLARE statement in a procedure.
PAGE 345
DELETE DELETE The DELETE statement deletes a row or rows from a table. Scope ISQL or Application Programs SQL Syntax 2 3 DELETE WITH AUTOCOMMIT FROM 3 2 2 Owner . TableName 2 3 WHERE Owner. ViewName SearchCondition 3 Parameters WITH AUTOCOMMIT [Owner .]TableName [Owner .]ViewName WHERE SearchCondition executes a COMMIT WORK automatically at the beginning of the DELETE statement and also after each batch of rows is deleted.
PAGE 346
DELETE The search condition is e ectively 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 e ectively 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.
PAGE 347
DELETE and any procedures invoked by any rules will have no e ect, regardless of the current DML ATOMICITY. Nothing will have been altered in the DBEnvironment as a result of this statement or the rules it red. 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.
PAGE 348
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 3 2 Owner. 3TableName 2 WHERE Owner. ViewName CURRENT OF CursorName Parameters [Owner .]TableName [Owner .]ViewName CursorName designates the table from which you are deleting a row. designates a view based on a single table.
PAGE 349
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.
PAGE 350
DELETE WHERE CURRENT Authorization If you specify the name of a table, you must have DELETE or OWNER authority for that 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.
PAGE 351
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.
PAGE 352
DESCRIBE AreaName speci es the user de ned name of a data structure of sqlda type that is to be used to pass information about the prepared statement between the application and ALLBASE/SQL. Description This statement cannot be used in ISQL, in COBOL and FORTRAN programs, or in procedures.
PAGE 353
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 Pascal 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 .
PAGE 354
DESCRIBE If dynamic input parameters are present, the appropriate data bu er or host variables must be loaded with the values of any dynamic parameters.
PAGE 355
DESCRIBE a. If sqldaout.sqlmproc <> 0 then, use procedure cursor processing statements to process multiple row result set(s) from the procedure. FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF DESCRIBE RESULT cmd USING sqldaresult . . . DECLARE Dynamic1Cursor CURSOR FOR cmd OPEN Dynamic1Cursor USING sqldain . . . FETCH Dynamic1Cursor using DESCRIPTOR sqldaresult . . . CLOSE Dynamic1Cursor USING sqldaout . . . b.
PAGE 356
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.
PAGE 357
DISABLE RULES DISABLE RULES The DISABLE RULES statement turns rule checking o 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 o in the DBEnvironment for the current session or until the ENABLE RULES statement is issued. The statement only a ects the current SID (session id). Other users are not a ected.
PAGE 358
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 9 8 'ConnectionName' > > > > > > > = < 'DBEnvironmentName' > :HostVariable > > ALL > > : CURRENT > > > > ; Parameters ConnectionName 'DBEnvironmentName ' HostVariable ALL CURRENT is a string literal identifying the name associated with this connection.
PAGE 359
DISCONNECT Any connection name associated with a disconnected connection can be reused. A DISCONNECT CURRENT statement is equivalent to a RELEASE statement. Following a RELEASE or DISCONNECT CURRENT command, there is no current connection until a SET CONNECTION command is used to set the current connection to another existing connection, or a new connection is established by using the CONNECT, START DBE, START DBE NEW, or START DBE NEW LOG commands.
PAGE 360
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. Description Before dropping a DBEFile previously associated with a DBEFileSet via an ADD DBEFILE statement, you must use the DROP INDEX and DROP TABLE statements to empty the DBEFile, then use the REMOVE DBEFILE statement to remove the DBEFile from the DBEFileSet.
PAGE 361
DROP DBEFILE 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 le space. REMOVE DBEFILE ThisDBEFile FROM DBEFILESET Miscellaneous FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF DROP DBEFILE ThisDBEFile The DBEFileSet de nition can now be dropped.
PAGE 362
DROP DBEFILESET The DROP DBEFILESET statement removes the de nition 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. You must rst change the default to some other DBEFileSet.
PAGE 363
DROP DBEFILESET 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 le space. REMOVE DBEFILE ThisDBEFile FROM DBEFILESET Miscellaneous DROP DBEFILE ThisDBEFile If it is not a default DBEFileSet, you can now drop its de nition.
PAGE 364
DROP GROUP The DROP GROUP statement removes the de nition of an authorization group from the system catalog. Scope ISQL or Application Programs SQL Syntax DROP GROUP GroupName Parameters GroupName identi es the authorization group to be dropped. Description You cannot drop an authorization group if it owns any tables, views, modules, or authorization groups.
PAGE 365
DROP INDEX DROP INDEX The DROP INDEX statement deletes the speci ed index. Scope ISQL or Application Programs SQL Syntax 2 3 2 2 3 DROP INDEX Owner. IndexName FROM Owner. TableName 3 Parameters [Owner .]IndexName [Owner .]TableName is the name of the index to be dropped. It may include the name of the owner of the table which has the index. is the name of the table upon which the index was created.
PAGE 366
DROP INDEX Authorization You can issue this statement if you have INDEX or OWNER authority for the table or if you have DBA authority. 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 rede ne the index.
PAGE 367
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 2 3 2 DROP MODULE Owner. ModuleName PRESERVE 3 Parameters [Owner .]ModuleName PRESERVE identi es the module to be dropped. causes ALLBASE/SQL to retain the module's authorization records.
PAGE 368
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 FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF 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 rede ne its authorization. 2.
PAGE 369
DROP PARTITION DROP PARTITION The DROP PARTITION statement removes the de nition of a partition for audit logging purposes. Scope ISQL or Application Programs SQL Syntax DROP PARTITION PartitionName Parameters PartitionName speci es 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.
PAGE 370
DROP PROCEDURE The DROP PROCEDURE statement deletes the speci ed procedure. Scope ISQL or Application Programs SQL Syntax 2 3 2 DROP PROCEDURE Owner. ProcedureName PRESERVE 3 Parameters [Owner .]ProcedureName PRESERVE speci es the name of the procedure that is to be dropped. speci es that EXECUTE authorities associated with the procedure should be retained in the system catalog. Description If you do not specify PRESERVE, the EXECUTE authorities associated with the procedure are removed.
PAGE 371
DROP RULE DROP RULE The DROP RULE statement deletes the speci ed rule. Scope ISQL or Application Programs SQL Syntax 2 3 2 2 3 DROP RULE Owner. RuleName FROM TABLE Owner. TableName 3 Parameters [Owner .]RuleName [Owner .]TableName identi es the rule to be dropped. identi es the table the rule is de ned on. Description If a TableName is speci ed, the rule must exist on that table or an error will be returned. If no TableName is speci ed, the rule is located and dropped.
PAGE 372
DROP TABLE The DROP TABLE statement deletes the speci ed table, including any hash structure or constraints associated with it, all indexes, views, and rules de ned on the table, and all authorizations granted on the table. Scope ISQL or Application Programs SQL Syntax 2 3 DROP TABLE Owner. TableName Parameters [Owner .]TableName identi es the table to be dropped. Description The DROP TABLE statement may invalidate stored sections.
PAGE 373
DROP TABLE Example This table is private by default.
PAGE 374
DROP TEMPSPACE The DROP TEMPSPACE statement removes the de nition 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 les currently exist under the path name it speci es, those les remain until the sort using them completes. However, no further temporary les are created in that TempSpace.
PAGE 375
DROP VIEW DROP VIEW The DROP VIEW statement deletes the de nition of the speci ed 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 2 3 DROP VIEW Owner. ViewName Parameters [Owner .]ViewName identi es the view to be dropped. Description This statement does not a ect the base tables on which the views were de ned. The DROP VIEW statement can invalidate stored sections.
PAGE 376
ENABLE AUDIT LOGGING The ENABLE AUDIT LOGGING statement restarts audit logging for the DBEnvironment after a DISABLE AUDIT LOGGING has been performed. Scope ISQL or Application Programs SQL Syntax ENABLE AUDIT LOGGING Description This statement reenables audit logging in the current session only. This statement and DISABLE AUDIT LOGGING are not used to turn on and o the AUDIT LOG option speci ed for processing of all sessions in the DBEnvironment. These statements a ect your current session only.
PAGE 377
ENABLE RULES ENABLE RULES The ENABLE RULES statement turns rule checking on for the current DBEnvironment session. The statement is for DBA use in tuning the DBEnvironment and testing 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 ring all applicable rules. The statement only a ects the current SID (session id). Other users are not a ected.
PAGE 378
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 DECLARE 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.
PAGE 379
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 StatementName 3 2 3 EXECUTE 2 ) Owner.
PAGE 380
EXECUTE HostVariableSpeci cation INPUT OUTPUT INPUT AND OUTPUT Bu er speci es host variable(s) that hold dynamic parameter values at run time. The syntax of HostVariableSpeci cation is presented separately below. is the default for any EXECUTE statement and can be speci ed, as required, for any type of prepared statement containing input dynamic parameters. is only allowed when the prepared statement is an EXECUTE PROCEDURE statement.
PAGE 381
EXECUTE Parameters|HostVariableSpecification HostVariableName IndicatorVariable speci es a host variable name that at run time contains the data value that is assigned to a dynamic parameter de ned in a prepared statement. Host variables must be speci ed 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 in the prepared statement.
PAGE 382
EXECUTE Authorization In an application program, the EXECUTE statement does not require any special authorization. The user running the program must have whatever authorization is required by the dynamically preprocessed statement being executed. To use the EXECUTE statement in the interactive environment, you must have RUN or OWNER authority for the dynamically preprocessed statement or have DBA authority.
PAGE 383
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 bu er or host variables must be loaded with the values of any dynamic parameters.
PAGE 384
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 HostVariable is the ALLBASE/SQL statement to be executed. identi es a character-string host variable containing the ALLBASE/SQL statement to be executed. Description When used interactively, a host variable cannot be speci ed.
PAGE 385
EXECUTE PROCEDURE EXECUTE PROCEDURE The EXECUTE PROCEDURE statement invokes a procedure. Scope ISQL or Application Programs Syntax 32 2 3 . ProcedureName ReturnStatusVariable =3 32 Owner EXECUTE PROCEDURE :32 3 3 2 2 2 . . . 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.
PAGE 386
EXECUTE PROCEDURE created). However, if a ParameterName is speci ed, use of a comma by itself is disallowed. speci es that the caller wishes to retrieve the output value of the parameter. OUTPUT must also have been speci ed for the corresponding parameter in the CREATE PROCEDURE statement. OUTPUT If OUTPUT is not speci ed, no output value is returned to the caller. speci es that the caller wishes to retrieve the output value of the parameter and will not provide an input value.
PAGE 387
EXECUTE PROCEDURE If you attempt to execute a procedure that contains invalid sections, ALLBASE/SQL silently revalidates the sections. You can also use the VALIDATE statement to revalidate 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.
PAGE 388
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 speci ed host variables or data bu er. The row to which the cursor points is called the current row.
PAGE 389
FETCH The USING clause with a HostVariableSpeci cation allows non-BULK variable declarations only. The DESCRIPTOR identi er de nes 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 speci ed in the DESCRIBE statement.
PAGE 390
FETCH SQL Syntax|non-BULK HostVariableSpecification 8 :HostVariable 22 3 INDICATOR :Indicator 3 92 , ... 3 Parameters|non-BULK HostVariableSpecification HostVariable Indicator identi es the host variable corresponding to one column in the row fetched. names the indicator variable, an output host variable whose value depends on whether the host variable contains a null value.
PAGE 391
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.
PAGE 392
FETCH 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. PREPARE Dynamic1 FROM :Dynam1 De ne a cursor to be used to move through the query result row by row. DECLARE Dynamic1Cursor CURSOR FOR Dynamic1 Open the cursor to de ne rows of the active set. OPEN Dynamic1Cursor Fetch the selected data into the speci ed host variables.
PAGE 393
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 2 GENPLAN WITH (HostVariableDe nition ) 8 < SQLStatement : 2 3 3 FOR . ModuleName (Section Number) MODULE SECTION Owner 3 2 PROCEDURE SECTION Owner.
PAGE 394
GENPLAN Description The GENPLAN statement can only be used in ISQL. It cannot be used in an application, in a static SQL statement, or in dynamic preprocessing. 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.
PAGE 395
GENPLAN Table 10-4. 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 10-5.
PAGE 396
GENPLAN Table 10-6.
PAGE 397
GENPLAN 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. Authorization To execute GENPLAN, you must have DBA authority or the appropriate combination of SELECT, UPDATE, or DELETE authorities for the tables and views accessed by the included SQL statement.
PAGE 398
GENPLAN Remove INTO clause when placing the statement into GENPLAN. Generate the plan in ISQL: De ne input host variable names and compatible SQL data types in WITH clause. isql=> GENPLAN WITH (PartNumber char(16)) FOR > SELECT PartName, VendorNumber, UnitPrice > FROM PurchDB.Parts p, PurchDB.SupplyPrice sp > WHERE p.PartNumber = sp.PartNumber > AND p.PartNumber = :PartNumber; Display the plan: isql=> SELECT * FROM System.Plan; SELECT * FROM System.
PAGE 399
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 Integer speci es an identi er label for branching within the procedure. speci es 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.
PAGE 400
GRANT The GRANT statement gives speci ed 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.
PAGE 401
GRANT ALTER INDEX UPDATE REFERENCES [Owner .]TableName [Owner .]ViewName TO WITH GRANT OPTION BY grants authority to add new columns. ALTER authority is not allowed for a view. grants authority to create and drop indexes. INDEX authority is not allowed for a view. grants authority to change data in existing rows. A list of column names can be speci ed to grant UPDATE authority only for speci c columns. Omitting the list of column names grants authority to update all columns.
PAGE 402
GRANT SQL Syntax|Grant RUN or EXECUTE Authority GRANT 88 DBEUserID =2 > >< < > > : : 3 2 RUN ON Owner. ModuleName 3 2 TO EXECUTE9 ON PROCEDURE Owner. ProcedureName GroupName , . . . ; ClassName PUBLIC 9 3> > = > > ; Parameters|Grant RUN or EXECUTE Authority RUN [Owner .]ModuleName EXECUTE [Owner .]ProcedureName TO grants authority to execute a speci ed module created interactively or by using a preprocessor. speci es the name of the module for which authority is to be granted.
PAGE 403
GRANT Parameters|Grant CONNECT, DBA, INSTALL, MONITOR, or RESOURCE Authority CONNECT DBA RESOURCE MONITOR INSTALL TO grants authority to use the CONNECT statement. grants authority to issue any valid ALLBASE/SQL statement. A user with DBA authority is exempt from all authorization restrictions. grants authority to create tables and authorization groups. grants authority to run SQLMON. grants authority to INSTALL modules where the owner name equals the OwnerID .
PAGE 404
GRANT SQL Syntax|Grant DBEFileSet Authority GRANT SECTIONSPACE TABLESPACE 9 8 DBEUserID > > > > < GroupName =2 ClassName > > > > ; : , . .. 2 , ... 3 ON DBEFILESET DBEFileSetName TO 3 PUBLIC Parameters|Grant DBEFileSet Authority SECTIONSPACE grants authority to store sections in the speci ed DBEFileSet. A grant of SECTIONSPACE causes a check to see whether the STOREDSECT table has yet been created for the DBEFileSet. If there is no related STOREDSECT table, it is created.
PAGE 405
GRANT Authorization|Grant DBEFilesSet Authority To grant SECTIONSPACE or TABLESPACE, you must have DBA authority. If you have DBA authority, you can issue the GRANT statement for any DBEFileSet. Examples 1. Authorization groups CREATE GROUP Warehse GRANT CONNECT TO Warehse GRANT SELECT, UPDATE (BinNumber,QtyOnHand,LastCountDate) ON PurchDB.Inventory TO Warehse These two users now will be able to start DBE sessions for PartsDBE, retrieve data from table PurchDB.
PAGE 406
GRANT GRANT SECTIONSPACE ON DBEFILESET DBEFileSet1 TO PUBLIC; Grant the ability to store table and long column data in DBEFileSet2 to PUBLIC. GRANT TABLESPACE ON DBEFILESET DBEFileSet2 TO PUBLIC; 6. Grant authority to run SQLMON GRANT MONITOR TO HelperDBA; 7. Grant a DBEUserID the authority to create modules owned by a speci ed OwnerID .
PAGE 407
IF IF The IF statement is used to allow conditional execution of one or more statements within a procedure. Scope Procedures only SQL Syntax 2 2 33 ; ... 2 IF Condition THEN Statement 2 2 Statement; . . . ELSEIF Condition THEN 2 333 2 2 ENDIF; ELSE Statement; . . .
PAGE 408
IF Example Create a procedure to enter orders into di erent 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 di erent parameters. The rst execution adds a row to the LargeOrders table.
PAGE 409
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 SQLDA 22 3 IS EXTERNAL 3 Parameters SQLCA and SQLDA IS EXTERNAL identify data structures with special prede ned 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.
PAGE 410
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 de ne. 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 de ned by a SELECT command.
PAGE 411
INSERT SingleRowValues BulkValues ? CREATE TABLE or CREATE VIEW commands, if no column name list exists. Character and date/time literals must be in single quotes. de nes 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. de nes values when you use the BULK option.
PAGE 412
INSERT HostVariable IndicatorVariable To be consistent with the standard SQL and to support portability of code, it is strongly recommended that you use a 01 to indicate a null value. However, ALLBASE/SQL interprets all negative indicator variable values to mean a null value. Note ? LocalVariable ProcedureParameter Built-inVariable ConversionFunction CurrentFunction Integer Float Decimal CharacterString 10-164 contains a value in an application program being input to the expression.
PAGE 413
INSERT HexadecimalString speci es a BINARY or VARBINARY value. If the string is shorter than the target column, it is padded with binary zeroes; if it is longer than the target column, the string is truncated. speci es the input and output locations for the LONG data. The speci cation for this string is given below. LongColumnIOString SQL Syntax|LongColumnIOString < 2 2 FileName .Group .Account %HeapAddress:LengthofHeap 33 3393 2 2 98 28 = < > =< FileName .Group .
PAGE 414
INSERT Description|LongColumnIOString The input device must not be locked or have privilege security. An input device le can be a standard MPE/iX le with xed record size, valid blocking factor, valid le equations, ASCII/binary option, and user labels option. Any related output device le will have the same characteristics as the input device le.
PAGE 415
INSERT <0 Note StartIndex NumberOfRows the value is NULL To be consistent with the standard SQL and to support portability of code, it is strongly recommended that you use a 01 to indicate a null value. However, ALLBASE/SQL interprets all negative indicator variable values to mean a null value. is a host variable whose value speci es the array subscript denoting where the rst row to be inserted is stored in the array; default is the rst element of the array.
PAGE 416
INSERT the default behavior. For more information, it is important that you refer to the section \Error Conditions in ALLBASE/SQL" in the \Introduction" chapter, and the SET DML ATOMICITY or the SET CONSTRAINTS statements in this chapter. Rows being inserted must not cause the search condition of the table check constraint to be false and must cause the search condition of the view check constraint to be true.
PAGE 417
INSERT Parameters|DynamicParameterValues (? [, . . . ]) represents one or more host variables in a prepared INSERT statement. Each ? corresponds in sequential order to a column in the column name list of the prepared statement (even when BULK is used).
PAGE 418
INSERT SQL Syntax|INSERT Rows Defined by a SELECT Command (Type 2 Insert) 2 INSERT INTO 2 3 Owner. 3TableName Owner. ViewName 2 2 (ColumnName , . . . 3 3 ) QueryExpression Parameters|INSERT Rows Defined by a SELECT Command (Type 2 Insert) [Owner .]TableName [Owner .]ViewName ColumnName QueryExpression identi es the table to which data is to be added. identi es a view on a single table; the data is added to the table upon which the view is based.
PAGE 419
INSERT If ALLBASE/SQL detects an error during this kind of INSERT operation, error handling behavior is determined by the setting of the SET DML ATOMICITY and SET CONSTRAINTS statements. Refer to the discussion of these statements in this chapter. Using the INSERT command with views requires that the views be based on updatable queries. See \Updatability of Queries" in the \SQL Queries" chapter. A table on which a unique constraint is de ned cannot contain duplicate rows.
PAGE 420
INSERT completion of the rule. In other words, if the rule adds to, deletes from, or modi es this set, such changes are ignored. When a rule is red 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.
PAGE 421
INSERT 3. Insert using SELECT operation CREATE PUBLIC TABLE (VendorName PartNumber UnitPrice DeliveryDays VendorRemarks IN PurchFS PurchDB.CalifVendors CHAR(30) NOT NULL, CHAR(16) NOT NULL, DECIMAL(10,2), SMALLINT, VARCHAR(60) ) This table has the same column attributes as corresponding columns in PurchDB.SupplyPrice and PurchDB.Vendors. INSERT INTO PurchDB.CalifVendors SELECT VendorName, PartNumber, UnitPrice, DeliveryDays, VendorRemarks FROM PurchDB.Supplyprice, PurchDB.Vendors WHERE PurchDB.
PAGE 422
Labeled Statement A Label identi es an SQL statement that can be referred to within the procedure. Scope Procedures only SQL Syntax Label: Statement Parameters Label Statement is an integer or a name which conforms to the SQL syntax rules for a basic name. 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. It cannot appear with a local variable declaration or a WHENEVER directive.
PAGE 423
Labeled Statement Example CREATE PROCEDURE Process19 (param1 integer, param2 float) AS BEGIN DECLARE value1 integer; FFFFFFFFFFFFFFFFFFFFF WHENEVER sqlerror GOTO errorexit ; DECLARE cursor1 CURSOR FOR SELECT column1 FROM table1 WHERE column1 > :param1; OPEN cursor1; WHILE ::sqlcode < > 100 do FETCH cursor1 into :value1; IF ::sqlcode = 100 THEN FFFFFFFFFFFFFFFFFFF GOTO loopexit ; ENDIF; INSERT INTO table2 VALUES (:value1, :param2); UPDATE table3 SET column1 = CURRENT_DATE WHERE column2 = :value1; IF ::sqle
PAGE 424
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 2 3 LOCK TABLE Owner. TableName IN 2 SHARE UPDATE EXCLUSIVE 3 MODE Parameters [Owner .]TableName SHARE SHARE UPDATE EXCLUSIVE speci es the table to be locked.
PAGE 425
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.
PAGE 426
LOG COMMENT The LOG COMMENT statement permits the entry of comments into the ALLBASE/SQL DBELog le. These comments can be extracted using the Audit Tool.
PAGE 427
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 WITH LOCKS OPEN CursorName KEEP CURSOR WITH NOLOCKS 8 2 2 3 SQLDA < SQL DESCRIPTOR 4 USING AreaName 3 22 : HostVariableName INDICATOR :IndicatorVariable 32 , . ..
PAGE 428
OPEN HostVariableName IndicatorVariable speci es a host variable name that at run time contains the data value that is assigned to an input dynamic parameter speci ed in the parameter list of a prepared SELECT or EXECUTE PROCEDURE statement. Host variables must be speci ed 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.
PAGE 429
PREPARE PREPARE The PREPARE statement dynamically preprocesses an SQL statement for later execution. Scope ISQL or Application Programs SQL Syntax StatementName 3 2 3 PREPARE REPEAT 2 Owner. ModuleName (SectionNumber) 3 2 'String' IN DBEFileSetName FROM :HostVariable 2 3 Parameters REPEAT StatementName speci es the use of semi-permanent sections for queries.
PAGE 430
PREPARE with the INPUT clause. If dynamic parameters are present, the appropriate data bu er or host variables must be loaded with the values of any dynamic parameters before the statement can be executed. [Owner .]ModuleName [(SectionNumber )] See related ALLBASE/SQL statements in this manual and the appropriate ALLBASE/SQL application programming guide for details of these programming methods. This option of the PREPARE statement is used interactively; it cannot be used in an application program.
PAGE 431
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.
PAGE 432
PREPARE EXECUTE Statistics(2) The statistics for table PurchDB.OrderItems are updated. DROP MODULE Statistics Both sections of the module are deleted. 2. Programmatic use If you know in advance that the statement to be dynamically preprocessed is not a SELECT statement and does not contain dynamic parameters, you can prepare it and execute it in one step, as follows: EXECUTE IMMEDIATE :Dynam1 It may be more appropriate to prepare and execute the statement in separate operations.
PAGE 433
PRINT PRINT The PRINT statement is used inside a procedure to store the content of user-de ned strings, local variables, parameters, or built-in variables in the message bu er for display by ISQL or an application program. Scope Procedures only SQL Syntax PRINT 8 'Constant' > > < 9 > > = :Parameter > > : > > ; :LocalVariable ::Built-inVariable ; Parameters Constant LocalVariable Parameter Built-inVariable is a string literal. is a local variable declared within the procedure.
PAGE 434
PRINT Description The results of any PRINT statements issued during the execution of a procedure are placed in the ALLBASE/SQL message bu er, 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.
PAGE 435
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 bu er, 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 ring the rule to have no e ect. The e ect of RAISE ERROR is to return with an error status; this statement can never \execute successfully.
PAGE 436
RAISE ERROR If no ErrorText is given, the default is an empty string. 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-de ned errors. The errors returned are application speci c. If ErrorNumber or ErrorText is NULL, an error is returned and the message is not generated. ErrorNumber , if speci ed, must be greater than 0.
PAGE 437
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 8 :HostVariable 22 3 INDICATOR :Indicator 3 92 , . ..
PAGE 438
REFETCH 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. Example label 1000; var EXEC SQL INCLUDE SQLCA; EXEC SQL BEGIN DECLARE SECTION; sqlmessage : packed array [1..132] of char; host1, host2, updatevalue : integer; EXEC SQL END DECLARE SECTION; begin . . .
PAGE 439
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 e ect and your session is the only one in process, a RELEASE statement forces a checkpoint.
PAGE 440
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 DBEFileSetName is the name of the DBEFile to be removed. The DBEFile must be empty (contain no tables, long data, or indexes). is the name of the DBEFileSet with which the DBEFile is currently associated.
PAGE 441
REMOVE 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. When the table needs an index, one is created as follows: CREATE DBEFILE ThatDBEFile WITH PAGES = 4, NAME = 'ThatFile', TYPE = INDEX ADD DBEFILE ThatDBEFile to DBEFILESET Miscellaneous When the index is subsequently dropped, its le space can be assigned to another DBEFileSet.
PAGE 442
REMOVE FROM GROUP The REMOVE FROM GROUP statement removes one or more users or authorization groups from membership in a speci ed authorization group. Scope ISQL or Application Programs SQL Syntax REMOVE 8 9 < DBEUserID =2 3 2 3 GroupName , . . . FROM GROUP Owner. TargetGroupName : ; ClassName Parameters DBEUserID GroupName ClassName TargetGroupName identi es a user to be removed from the speci ed authorization group.
PAGE 443
REMOVE FROM GROUP Example CREATE GROUP Warehse GRANT CONNECT TO Warehse GRANT SELECT, UPDATE (BinNumber,QtyOnHand,LastCountDate) ON PurchDB.Inventory TO Warehse ADD CLEM@THOMAS, GEORGE@CRAMMER 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.
PAGE 444
RENAME COLUMN The RENAME COLUMN statement de nes a new name for an existing column in the DBEnvironment. Scope Application Programs SQL Syntax 2 3 RENAME COLUMN Owner. TableName.ColumnName TO NewColumnName Parameters [Owner .]TableName.ColumnName NewColumnName designates the table column to be renamed. is the new column name. Description All indexes, columns, default columns, constraints, referential authorization, rules, and user authorities tables dependent on a renamed column will be renamed.
PAGE 445
RENAME TABLE RENAME TABLE The RENAME TABLE statement de nes a new name for an existing table in the DBEnvironment. Scope Application Programs SQL Syntax 2 3 RENAME TABLE Owner. TableName TO NewTableName Parameters [Owner .]TableName NewTableName designates the table to be renamed. 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.
PAGE 446
RESET The RESET statement resets ALLBASE/SQL accounting and statistical data. Scope ISQL or Application Program SQL Syntax RESET 8 < : SYSTEM.ACCOUNT FOR USER SYSTEM.COUNTER * DBEUserID 9 = ; Parameters SYSTEM.ACCOUNT * DBEUserID SYSTEM.COUNTER is speci ed to reset accounting data for one user's DBE session or for all active sessions. speci es all active sessions. This is the default if the FOR USER clause is omitted. identi es the user of a speci c DBE session.
PAGE 447
RETURN RETURN The RETURN statement permits you to exit from a procedure with an optional return code. Scope Procedures only SQL Syntax 2 3 RETURN ReturnStatus ; Parameters ReturnStatus is an integer value that is returned to the caller.
PAGE 448
RETURN Example CREATE PROCEDURE Process10 (PartName CHAR(20) NOT NULL, Quantity INTEGER NOT NULL) AS BEGIN INSERT INTO SmallOrders VALUES (:PartName, :Quantity); IF ::sqlcode <> 0 THEN GOTO Errors; ENDIF; RETURN 0; Errors: PRINT 'There were errors.'; RETURN 1; END Call the procedure using a ReturnStatusVariable named Status: EXECUTE PROCEDURE :Status = Process10 ('Widget', 10) On returning from the procedure, test SQLCODE and Status both to determine whether an error occurred inside the procedure.
PAGE 449
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.
PAGE 450
REVOKE UPDATE REFERENCES [Owner .]TableName [Owner .]ViewName FROM CASCADE revokes authority to change data in existing rows. A list of column names can be speci ed 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. revokes authority to reference columns in the table from foreign keys in another table.
PAGE 451
REVOKE Authorization|Revoke Table or View Authority If you are DBA, the owner, or the grantor of table privileges and still have that grantability, you can issue the REVOKE statement and optionally the CASCADE option. SQL Syntax|Revoke RUN or EXECUTE or Authority 3 2 RUN ON Owner. ModuleName 3 2 REVOKE EXECUTE ON PROCEDURE Owner. ProcedureName 9 88 < DBEUserID =2 > > < > > : : GroupName , . . . ; ClassName PUBLIC FROM 9 3> > = > > ; Parameters|Revoke RUN or EXECUTE Authority RUN [Owner .
PAGE 452
REVOKE revokes authority to INSTALL modules where the owner name equals OwnerID . If the \AS OwnerID " clause is omitted, then revokes authority to INSTALL modules having any owner name. INSTALL Modules for an application are created and installed when that application is preprocessed using one of the SQL preprocessors. Modules can also be installed by using the ISQL INSTALL command. See the ISQL Reference Manual for ALLBASE/SQL and IMAGE/SQL for more details. revokes authority to run SQLMON.
PAGE 453
REVOKE Description|Revoke DBEFileSet Authority In order for the statement to complete successfully, the authority being revoked must have been previously granted to the speci c user. In addition, the DBEFileSet cannot be the current default for that user. When SECTIONSPACE authority is revoked, current stored section information for the DBEFileSet remains (and thus any section revalidation continues to use that DBEFileSet). No new sections for the user(s) whose authority was revoked can be placed there.
PAGE 454
REVOKE 2. Implicitly revoking authority The table is private by default. CREATE TABLE VendorPerf (OrderNumber INTEGER NOT NULL, ActualDelivDay SMALLINT, ActualDelivMonth SMALLINT, ActualDelivYear SMALLINT, ActualDelivQty SMALLINT Remarks VARCHAR(60) ) IN Miscellaneous CREATE UNIQUE INDEX VendorPerfIndex ON VendorPerf (OrderNumber) Only the table creator and members of authorization group Warehse can update table VendorPerf.
PAGE 455
REVOKE 5. Revoke INSTALL or MONITOR authority. Revoke from GEORGE@DBMS the ability to run SQLMON. REVOKE MONITOR FROM GEORGE@DBMS; Revoke from CLEM@DBMS the ability to create modules having any owner name. REVOKE INSTALL FROM CLEM@DBMS; Revoke from CLEM@DBMS the ability to create modules owned by JOHN@BROCK.
PAGE 456
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 a ected.
PAGE 457
ROLLBACK WORK When you specify the TO clause, all changes you have made to the DBEnvironment 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 de ned more recently than the designated savepoint are lost and become invalid.
PAGE 458
SAVEPOINT The SAVEPOINT statement de nes a savepoint within a transaction. DBEnvironment changes made after a savepoint can be undone at any time prior to the end of the transaction. A transaction can have multiple savepoints. Scope ISQL or Application Programs SQL Syntax 2 3 :HostVariable 5 4 SAVEPOINT :LocalVariable :ProcedureParameter Parameters HostVariable identi es an output host variable used to communicate the savepoint number. The host variable's value can be from 1 to (231 )01.
PAGE 459
SAVEPOINT Authorization You do not need authorization to use the SAVEPOINT statement. Example Transaction begins. BEGIN WORK statement-1 FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF SAVEPOINT :MyVariable statement-2 statement-3 Work of statements 2 and 3 is undone. ROLLBACK WORK TO :MyVariable Work of statement-1 is committed; transaction ends.
PAGE 460
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.
PAGE 461
SELECT HostVariableSpecification|With BULK Option 2 2 :Bu er ,:StartIndex ,:NumberOfRows 33 HostVariableSpecification|Without BULK Option 8 :HostVariable 22 3 INDICATOR :Indicator 3 92 , ...
PAGE 462
SELECT SQL Syntax|Select Statement Level 2 3 BULK QueryExpression ORDER BY ASC ColumnID DESC 2 , ... 3 Parameters|Select Statement Level is speci ed in an application program to retrieve multiple rows with a single execution of the SELECT statement. BULK QueryExpression ORDER BY ColumnID Do not use this option in select statements associated with a cursor. Instead, use the BULK option of the FETCH statement. is a complex expression specifying what is to be selected.
PAGE 463
SELECT When using this statement to select LONG columns, the name of the le is returned in the appropriate eld in the HostVariableSpeci cation speci ed within the QueryExpression . With the BULK option, if the output mode is speci ed with $, then each LONG column in each row accessed has a le with a unique name containing the LONG data retrieved. SQL Syntax|Subquery Level (QueryExpression) Parameters|Subquery Level QueryExpression is the basic syntax of a query or SELECT statement.
PAGE 464
SELECT called a correlated subquery. Because a correlated subquery depends on a value of the outer query, the subquery must be reevaluated for each new value of the outer query, as in the following example to get supplier numbers for those who supply the most parts for each part number. 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.
PAGE 465
SELECT Description|Query Expression Level For the following, assume that T1 is the result of the query block or query expression on the left of the UNION operator, and T2 is the result of the query block or query expression on the right of the UNION operator. (The same conditions must be met if there are additional UNION operators which include results from T3, . . . Tn .): T1 and T2 must have the same number of columns. (They may be derived from tables with varying numbers of columns.
PAGE 466
SELECT Table 10-7. Conversion Rules for Data in Query Expressions Data Type Character Numeric Source Columns One CHAR, one VARCHAR Result Column VARCHAR Comment Result has the length of the longer of the two source columns. One NATIVE CHAR, one NATIVE VARCHAR NATIVE VARCHAR Result has the length of the longer of the two source columns. One NATIVE CHAR, one NATIVE CHAR CHAR Result has the length of the longer of the two source columns.
PAGE 467
SELECT SQL Syntax|Query Block Level 3 2 ALL SELECT SelectList INTO HostVariableSpeci cation DISTINCT 2 3 , . . . FROM FromSpec 3 2 WHERE SearchCondition1 3 2 GROUP BY GroupColumnList 3 2 HAVING SearchCondition2 Parameters|Query Block Level ALL DISTINCT SelectList INTO HostVariableSpeci cation FROM FromSpec WHERE prevents elimination of duplicate rows from the result. If neither ALL nor DISTINCT is speci ed, the ALL option is assumed. ensures that each row in the query result is unique.
PAGE 468
SELECT Rows that do not satisfy SearchCondition1 are eliminated before groups are formed and aggregate functions are evaluated. When you are joining tables or views, the WHERE clause also speci es the condition(s) under which rows should be joined. You cannot join on a column in a view derived using a GROUP BY clause. If you omit a join condition, ALLBASE/SQL joins each row in each table in the FROM clause with each row in all other tables in the FROM clause.
PAGE 469
SELECT any executed subquery contains an outer reference to a column, then the reference is to the values of that column in the given group. Only grouping columns can be used as outer references in a subquery in SearchCondition2 . SQL Syntax|SelectList 8 *2 > 3 > > > Owner. Table.* > > < 9 > > > > > > = 2 CorrelationName.* , . .. Expression > > > > 3 3 2 2 > > > > > Owner. Table. ColumnName > > > ; : CorrelationName.ColumnName 3 Parameters|SelectList * [Owner .]Table .* CorrelationName .
PAGE 470
SELECT SQL Syntax|BULK HostVariableSpecification :Bu er [,:StartIndex [,:NumberOfRows] ] Parameters|BULK HostVariableSpecification Bu er StartIndex is a host array or structure that is to receive the output of the SELECT statement. This array contains elements for each column in the SelectList and indicator variables for columns that can contain null values. Whenever a column can contain nulls, an indicator variable must be included in the array de nition immediately after the de nition of that column.
PAGE 471
SELECT SQL Syntax|FromSpec 8 TableSpec > > > > (FromSpec) > > > > > > > < 2 3 INNER2 3 TableSpec 4 5 FromSpec NATURAL LEFT 2OUTER 3 JOIN (FromSpec) RIGHT OUTER > > 2 3 > > > INNER2 > 3 > ON SearchCondition3 TableSpec > > FromSpec 4 LEFT 2OUTER 3 5JOIN > > USING (ColumnList) (FromSpec) : RIGHT OUTER 9 > > > > > > > > > > > = > > > > > > > > > > > ; Parameters|FromSpec TableSpec identi es a table or view from which rows are selected.
PAGE 472
SELECT 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.
PAGE 473
SELECT occurrence in the FromSpec of the keyword JOIN, in the ON SearchCondition3 clause the column names speci ed on each side of the equality must be fully quali ed. 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.
PAGE 474
SELECT clause, the select list can contain only aggregate functions and columns referenced in the GROUP BY clause. A result column containing a xed value can be created by specifying a constant or 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 rst result column speci ed by the select list becomes the leftmost column in the result table.
PAGE 475
SELECT 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 speci ed in the select list, in the order speci ed. The result of the INNER JOIN. . . . ON SearchCondition3 contains the multiset of rows of the Cartesian Product of the tables being joined for which all predicates in SearchCondition3 are true. When you specify JOIN. . . .
PAGE 476
SELECT be named as a join column. To ensure this, make each join clause a NATURAL . . . JOIN or a JOIN . . . USING (ColumnList ), except for the nal join, which may contain these types or a JOIN . . . ON SearchCondition3 clause. Otherwise, ensure that each join clause is a JOIN . . . ON SearchCondition3 clause. To join tables, without using explicit JOIN syntax, list the tables in the FROM clause, and specify a join predicate in the WHERE clause.
PAGE 477
SELECT For noncursor UPDATE, DELETE, or INSERT, the view de nition, or the WHERE clause must not contain any subqueries referencing the target table in their FROM clause. Authorization If you specify the name of a table, you must have SELECT or OWNER authority for the table, or you must have DBA authority. If you specify the name of a view, you must have SELECT or OWNER authority for the view, or you must have DBA authority.
PAGE 478
SELECT SELECT FROM NATURAL JOIN WHERE ORDER BY PartNumber, VendorName, VendorNumber, VendorCity PurchDB.SupplyPrice PurchDB.Vendors VendorState = 'CA' PartNumber, VendorName This query joins table PurchDB.Parts to itself in order to determine which parts have the same sales price as part 1133-P-01. SELECT FROM WHERE AND q.PartNumber, q.SalesPrice PurchDB.Parts p, PurchDB.Parts q p.SalesPrice = q.SalesPrice p.
PAGE 479
SELECT Get supplier names for suppliers who provide at least one red part. SELECT SNAME FROM S WHERE SNO IN ( SELECT SNO FROM SP WHERE EXISTS (SELECT FROM WHERE AND PNO P P.PNO = SP.PNO COLOR = 'RED' )) Get supplier number for suppliers who supply the most parts. SELECT FROM GROUP BY HAVING SNO SP SNO COUNT(DISTINCT PNO) >= ALL ( SELECT COUNT(DISTINCT PNO) FROM SP GROUP BY SNO ) Insert into table T, supplier names of each supplier who does not supply any part.
PAGE 480
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 HostVariable 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.
PAGE 481
SET CONNECTION Authorization You do not need authorization to use the SET CONNECTION statement. Example Establish two connections: CONNECT TO :PartsDBE AS 'Parts1' CONNECT TO :SalesDBE AS 'Sales1' At this point, Sales1 is the current connection. .. .
PAGE 482
SET CONSTRAINTS The SET CONSTRAINTS statement sets the UNIQUE, REFERENTIAL or CHECK constraint error checking mode. Scope ISQL or Application Programs SQL Syntax 2 SET ConstraintType , . . . 3 CONSTRAINTS DEFERRED IMMEDIATE Parameters ConstraintType DEFERRED IMMEDIATE identi es the type of constraint that is to be a ected by the statement.
PAGE 483
SET CONSTRAINTS You have the option of correcting the error before issuing a COMMIT WORK or allowing 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 e ect from the SET DML ATOMICITY statement.
PAGE 484
SET CONSTRAINTS 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.
PAGE 485
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 TABLESPACE DBEFileSetName sets the default DBEFileSet for stored sections.
PAGE 486
SET DEFAULT DBEFILESET Authorization You must have DBA authority to set a DBEFileSet default.
PAGE 487
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 LEVEL STATEMENT Parameters ROW STATEMENT speci es that general error checking occurs at the row level. The term general error checking refers to any errors, for example, arithmetic over ows or constraint violation errors.
PAGE 488
SET DML ATOMICITY errors at a speci c 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. Unless you have a severe error (4008, 4009, or -14024 or greater), the transaction is not rolled back, and previous statements within the transaction are still in e ect. When a transaction ends, DML ATOMICITY remains at or is returned to STATEMENT level. The SET DML ATOMICITY statement is sensitive to savepoints.
PAGE 489
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 OFF enables multiple implied or explicit BEGIN WORK statements to be active across the set of connected DBEnvironments. This is termed multitransaction mode.
PAGE 490
SET MULTITRANSACTION Authorization You do not need authorization to use the SET MULTITRANSACTION statement.
PAGE 491
SETOPT SETOPT The SETOPT statement modi es the access optimization plan used by queries. Scope ISQL or Application Programs Syntax|SETOPT 8 CLEAR > > > > < 2 3 ScanAccess , . .. JoinAlgorithm SETOPT > > 3 2 ScanAccess > > : BEGIN ; . . .
PAGE 492
SETOPT SORTMERGE SMJ designates sort merge join. is equivalent to SORTMERGE. Description Use the SETOPT statement when you want to override the default access plan used in queries. The SETOPT statement a ects only those queries in the current transaction. When the transaction ends, the settings speci ed by SETOPT are cleared. To view the plan speci ed by SETOPT, query the SYSTEM.SETOPTINFO view. Use the GENPLAN command in ISQL to display the current access plan.
PAGE 493
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.
PAGE 494
SET PRINTRULES The SET PRINTRULES statement speci es whether rule names and statement types are to be issued as messages when the rules are red during a DBEnvironment session. Scope ISQL or Application Programs SQL Syntax SET PRINTRULES ON OFF Parameters speci es that rule name and statement type should be issued as a message when the rule is red. speci es that rule name and statement type should not be issued as a message when the rule is red. This is the default for all sessions.
PAGE 495
SET PRINTRULES Authorization You must have DBA authority. Example The DBA enables the issuing of messages when rules re. SET PRINTRULES ON The DBA issues statements that re 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 re.
PAGE 496
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 ll options.
PAGE 497
SET SESSION Parameters RR CS RC RU REPEATABLE READ SERIALIZABLE CURSOR STABILITY READ COMMITTED READ UNCOMMITTED HostVariable1 Priority HostVariable2 LabelString HostVariable3 Repeatable Read. Means that the transaction uses locking strategies to guarantee repeatable reads. RR is the default isolation level. Cursor Stability. Means that your transaction uses locking strategies to assure cursor-level stability only. Read Committed.
PAGE 498
SET SESSION ConstraintType identi es the types of constraints that are a ected by the DEFERRED and IMMEDIATE options.
PAGE 499
SET SESSION Description Detailed information about isolation levels is presented in the \Concurrency Control through Locks and Isolation Levels" chapter. You can issue the SET SESSION statement at any point in an application or ISQL session. Whether issued within or outside of a transaction, the attributes speci ed in a SET SESSION statement apply to the next and subsequent transactions.
PAGE 500
SET SESSION establish a savepoint, then issue the SET SESSION statement to change attribute(s )for the session, and then roll back to the savepoint, the transaction attribute(s) set after the savepoint are not undone. In this case, the attribute(s) would go into e ect for the next and subsequent transactions, just as if no rollback to savepoint had occurred.
PAGE 501
SET SESSION Authorization You do not need authorization to use the SET SESSION statement. Example The following example illustrates setting session level deferred constraint checking, DML atomicity, and the FILL option to enhance load performance within ISQL. COMMIT WORK; SET SET SET SET SET SET LOAD_BUFFER 65536; AUTOSAVE 3000 LOAD_ECHO AT_COMMIT; AUTOCOMMIT ON; AUTOLOCK ON; SESSION UNIQUE, REFERENTIAL, CHECK CONSTRAINTS DEFERRED, DML ATOMICITY AT ROW LEVEL, FILL; . . .
PAGE 502
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.
PAGE 503
SET TRANSACTION Parameters RR CS RC RU REPEATABLE READ SERIALIZABLE CURSOR STABILITY READ COMMITTED READ UNCOMMITTED HostVariable1 Priority HostVariable2 LabelString Repeatable Read. Means that the transaction uses locking strategies to guarantee repeatable reads. RR is the default isolation level. Cursor Stability. Means that your transaction uses locking strategies to assure cursor-level stability only. Read Committed.
PAGE 504
SET TRANSACTION HostVariable3 ConstraintType is a string host variable containing the LabelString . identi es the types of constraints that are a ected by the DEFERRED and IMMEDIATE options.
PAGE 505
SET TRANSACTION Description Detailed information about isolation levels is presented in the \Concurrency Control through Locks and Isolation Levels" chapter. You can issue the SET TRANSACTION statement at any point in an application or ISQL session. If the SET TRANSACTION statement is issued outside of an active transaction, its attribute(s) apply to the next transaction. If issued within a transaction, its attribute(s) apply to the current transaction.
PAGE 506
SET TRANSACTION ATOMICITY statement in this chapter for further information on statement and row level error checking. All transaction attributes are sensitive to savepoints. That is, if you establish a savepoint, then change the transaction attribute(s) by issuing a SET TRANSACTION statement, and then roll back to the savepoint, the transaction attribute(s) set after the savepoint are undone.
PAGE 507
SET TRANSACTION FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF SET TRANSACTION ISOLATION LEVEL RC, PRIORITY 100, LABEL 'xact1' . . . Implicit BEGIN WORK with transaction isolation level RC. OPEN C1 FETCH C1 INTO :HostBranchNo1 . . . Change isolation level to CS.
PAGE 508
SET USER TIMEOUT The SET USER TIMEOUT statement speci es the amount of time the user will wait if the requested database resource is unavailable. Scope ISQL or Application Programs SQL Syntax 2 SET USER TIMEOUT TO 3 9 8 TimeoutValue SECONDS > > > > = < :HostVariable DEFAULT > > : MAXIMUM MINUTES > > ; Parameters TimeoutValue HostVariable DEFAULT MAXIMUM is an integer literal greater than or equal to zero. If the TimeoutValue is not quali ed by MINUTES, SECONDS is assumed.
PAGE 509
SET USER TIMEOUT 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. A host variable is not permitted if the SET USER TIMEOUT statement is used in the EXECUTE IMMEDIATE statement.
PAGE 510
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 identi es a host variable used to hold an ALLBASE/SQL exception message. The message describes the meaning of a return code. ALLBASE/SQL puts a return code into the SQLCA after each SQL statement in a program is executed.
PAGE 511
SQLEXPLAIN Authorization You do not need authorization to use SQLEXPLAIN. Example INCLUDE SQLCA SQLStatement1 The host variable named :Message contains a message characterizing the execution of SQLStatement1.
PAGE 512
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 speci ed are taken from the DBECon le. 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 le in a new DBE.
PAGE 513
START DBE DataBu erPages speci es the number of 4096-byte data bu er pages to be used. Data bu er pages hold index and data pages. You can request up to 50,000 data bu er pages. The minimum number of data bu ers is 15. The default number is 100. The total number of data bu er pages and runtime control block pages cannot exceed 256 Mbytes.
PAGE 514
START DBE DBEnvironment in multiuser mode or vice versa. When you issue the CONNECT statement and autostart mode is on, ALLBASE/SQL executes a START DBE statement on your behalf if no sessions for the DBEnvironment are active. ALLBASE/SQL starts your session using all parameters in the current DBECon le. If autostart mode has a value of OFF, you always use the START DBE statement to start up a DBEnvironment. If the MULTI option is not speci ed, the DBEnvironment is started up in single-user mode.
PAGE 515
START DBE NEW START DBE NEW The START DBE NEW statement con gures 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 le. Scope ISQL or Application Programs SQL Syntax|START DBE NEW 2 ' START DBE 'DBEnvironmentName' AS 'ConnectionName 3 2 DUAL | . . .
PAGE 516
START DBE NEW Parameters|START DBE NEW DBEnvironmentName identi es the DBEnvironment name used in the CONNECT statement. This name also identi es the DBECon le that stores the values of all parameters speci ed in the START DBE NEW statement that are also used in the CONNECT statement. Name quali cation follows standard MPE/iX le naming conventions. DBEnvironmentName cannot exceed 36 bytes.
PAGE 517
START DBE NEW speci es the language for the DBEnvironment. If the name of the language contains a hyphen, use double quotes in specifying it, as in the following (C-FRENCH means Canadian French): LANG = "C-FRENCH" MaxTransactions MAXIMUM TIMEOUT DEFAULT TIMEOUT TimeoutValue ControlBlockPages speci es the maximum number of concurrent transactions to be supported. You can specify a value from 2 to 240. The default is 50.
PAGE 518
START DBE NEW number of partition instances the DBEnvironment is expected to track. AuditName AUDIT ELEMENTS For audit logging purposes, the number of partition instances is calculated as the sum of the number of DATA partitions and the number of elements (not counting the DATA element) speci ed in the AUDIT ELEMENTS clause. Specifying ALL audit elements (see below) includes 6 elements, implying that 6 partitions are used.
PAGE 519
START DBE NEW STORAGE includes audit logging of the following statements: CREATE DBEFILE DROP DBEFILE ADD DBEFILE TO DBEFILESET REMOVE DBEFILE FROM DBEFILESET CREATE TEMPSPACE DROP TEMPSPACE AUTHORIZATION includes audit logging of the following statements: GRANT REVOKE ADD TO GROUP REMOVE FROM GROUP includes audit logging of the creation and deletion of permanent sections or procedures.
PAGE 520
START DBE NEW SQL Syntax|DBEFile0Definition DBEFILE0 DBEFILE DBEFile0ID WITH PAGES = DBEFile0Size, NAME = 'SystemFileName1' Parameters|DBEFile0Definition DBEFILE0 DBEFILE DBEFile0ID DBEFile0Size SystemFileName1 describes a DBEFile known as DBEFile0, which contains the portion of the system catalog needed for activating a DBEnvironment, including de nitions of other DBEFiles. Each DBEnvironment must have a DBEFile0 associated with a unique SystemFileName , which is assigned in this clause.
PAGE 521
START DBE NEW Description When you issue this statement, ALLBASE/SQL creates a DBECon le with the same name as the DBEnvironmentName .
PAGE 522
START DBE NEW You can recon gure a DBEnvironment by using SQLUtil to alter DBECon le parameters. All parameters except the name of the DBECon le and DBEFile0 may be changed. Refer to the ALLBASE/SQL Database Administration Guide for additional information. If no MAXIMUM TIMEOUT limit is speci ed, or if MAXIMUM TIMEOUT = NONE, in nity (no timeout) is assumed. If no DEFAULT TIMEOUT value is speci ed, or if DEFAULT TIMEOUT = MAXIMUM, the value of MAXIMUM TIMEOUT is assumed.
PAGE 523
START DBE NEW START DBE 'PartsDBE' MULTI NEW FFFFFFFFFFFFFFFFFFFFFFFF DUAL AUDIT LOG, TRANSACTION = 5, RUN BLOCK = 500, FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF AUDIT NAME = 'PrtsDBE1', FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF DEFAULT PARTITION = 1, FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF COMMENT PARTITION = 2, FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF MAXPARTITIONS = 20, FFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFFF ALL AUDIT ELEMENTS, DBEFILE0 DBEFILE PartsDBE
PAGE 524
START DBE NEWLOG The START DBE NEWLOG statement establishes a connection with a given DBEnvironment and creates one or two new log les 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 speci ed are taken from the DBECon le except the enabling of audit logging.
PAGE 525
START DBE NEWLOG Parameters|START DBE NEWLOG DBEnvironmentName identi es the DBEnvironment in which you want to initialize one or two new log les. Unless you specify a group and account, ALLBASE/SQL assumes the name is in your current group and account. DBEnvironmentName cannot exceed 36 bytes. You can also use an MPE/iX back reference for DBEnvironmentName as shown in the following example: :FILE DBE = PartsDBE.SomeGrp.
PAGE 526
START DBE NEWLOG MaxTransactions MAXIMUM TIMEOUT DEFAULT TIMEOUT TimeoutValue ControlBlockPages speci es the maximum number of concurrent transactions to be supported. You can specify a value from 2 to 240. The default is 50. This value overrides the maximum value stored in the DBECon le. Any attempt to start a transaction beyond the maximum limit waits for the speci ed TIMEOUT and returns an error if TIMEOUT is exceeded.
PAGE 527
START DBE NEWLOG AuditName AUDIT ELEMENTS For audit logging purposes, the number of partition instances is calculated as the sum of the number of DATA partitions and the number of elements (except the DATA audit element) speci ed in the AUDIT ELEMENTS clause. Specifying ALL audit elements (see below) includes 6 elements, implying that 6 partitions are used. Set this value only as high as needed so that unnecessary space is not reserved unless you plan for more partitions or audit elements.
PAGE 528
START DBE NEWLOG STORAGE This includes audit logging of the following statements: CREATE DBEFILE DROP DBEFILE ADD DBEFILE TO DBEFILESET REMOVE DBEFILE FROM DBEFILESET CREATE TEMPSPACE DROP TEMPSPACE AUTHORIZATION This includes audit logging of the following statements: GRANT REVOKE ADD TO GROUP REMOVE FROM GROUP This includes audit logging of the creation and deletion of permanent sections. Permanent sections are created when a program is preprocessed, and are deleted by the DROP MODULE statement.
PAGE 529
START DBE NEWLOG SystemFileName1 and SystemFileName2 identify how the logs are known to the operating system. The logs are created in the same group and account as the DBECon le by default. You can specify a di erent group name for each log le, but the account name, if given, must be the same as that of the DBECon le. If a log le by the same name already exists, use SQLUtil to purge it before issuing the START DBE NEWLOG statement.
PAGE 530
START DBE NEWLOG Audit logging (chosen versus not) Audit name Audit elements Default partition Comment partition Maximum number of partitions You can recon gure a DBEnvironment by using SQLUtil to alter DBECon le parameters. All parameters except the audit information (logging, audit elements, name, default, comment and maximum partition), or the name of the DBECon le and DBEFile0 may be changed. Refer to the ALLBASE/SQL Database Administration Guide for additional information.
PAGE 531
START DBE NEWLOG Example The DBEnvironment is restored to a consistent state. Any transactions incomplete when the DBEnvironment was last shut down are rolled back, and work done by completed transactions is committed. START DBE 'PartsDBE.SomeGrp.SomeAcct' SQLUtil is used to delete the existing log les: PartsLg1 and PartsLg2. STOP DBE The log les are reinitialized. START DBE 'PartsDBE.SomeGrp.
PAGE 532
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. Example Two users establish DBE sessions.
PAGE 533
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 CID ConnectionID XID TransactionID identi es a speci c connection in which the 'query' to be terminated is running. identi es a speci c transaction in which the 'query' to be terminated is running. Description A 'query' in this case refers to a command being executed.
PAGE 534
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 XID TransactionID identi es the speci c connection in which the transaction to be terminated is running. identi es a speci c transaction to be terminated. Description The transaction in progress for the connection is terminated and any changes are backed out.
PAGE 535
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 8 < DBEUserID : 9 = SessionID ; CID ConnectionID Parameters DBEUserID SessionID CID ConnectionID identi es the user to terminate all sessions for. Users currently on the system appear in the system view SYSTEM.USER. identi es a speci c session to be terminated.
PAGE 536
TERMINATE USER Example User1 starts a DBE session SessionID1 CONNECT TO 'PartsDBE.SomeGrp.SomeAcct' User1 starts a DBE session SessionID2 CONNECT TO 'PartsDBE.SomeGrp.SomeAcct' User2 starts a DBE session SessionID3 CONNECT TO 'PartsDBE.SomeGrp.SomeAcct' User2 starts a DBE session SessionID4 CONNECT TO 'PartsDBE.SomeGrp.SomeAcct' Both of User1's DBE sessions terminate. Either User1 or a DBA can enter this statement.
PAGE 537
TRANSFER OWNERSHIP TRANSFER OWNERSHIP The TRANSFER OWNERSHIP statement makes a di erent 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 3 32 82 TABLE Owner . TableName > 32 3 >2 < 9 > > = Owner . ViewName 3 2 TO NewOwnerName Owner. ProcedureName > > ; GROUP GroupName VIEW PROCEDURE > > : Parameters [TABLE][Owner .]TableName [VIEW] [Owner .]ViewName PROCEDURE [Owner .
PAGE 538
TRANSFER OWNERSHIP Authorization You can transfer ownership of a table, view, procedure, or authorization group if you have OWNER authority for that table, view, procedure, or group, or if you have DBA authority. 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.
PAGE 539
TRUNCATE TABLE TRUNCATE TABLE The TRUNCATE TABLE statement deletes all rows from the speci ed table. Scope ISQL, Application Programs, or Stored Procedures SQL Syntax 2 3 TRUNCATE TABLE Owner. TableName Parameters [Owner .]TableName identi es 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. The TRUNCATE TABLE statement is faster than the DELETE statement and generates fewer log records.
PAGE 540
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 SET 2 8 < : 3 2 Owner . TableName 2 3 Owner. ViewName 99 8 = =2 < Expression , . .. ColumnName = 'LongColumnIOString' : WHERE SearchCondition NULL 3 3 ;; Parameters [Owner .]TableName [Owner .
PAGE 541
UPDATE Description If the WHERE clause is omitted, all rows of the table are updated as speci ed by the SET clause. If the WHERE clause is present, then the search condition is evaluated for each row of the table before updating any row. Each subquery in the search condition is e ectively executed for each row of the table, and the results used in the application of the search condition to the given row.
PAGE 542
UPDATE the UPDATE is performed, ALLBASE/SQL considers all the rules de ned for that table with the UPDATE StatementType and a matching column. If the rule has no condition, it will re for all rows a ected by the statement and invoke its associated procedure with the speci ed parameters on each row. If the rule has a condition, it will evaluate the condition on each row.
PAGE 543
UPDATE Parameters|LongColumnIOString FileName [GroupName [AccountName ] ] % HeapAddressValue :LengthOfHeap > >> >! >%$ $ is the location of the input le. is the heap address where the input is located and is of the speci ed length. speci es that output is placed in the following le. If the le already exists, it is not overwritten nor appended to, and an error is generated. speci es that output will be appended to the following le name. If the le does not exist, it is created.
PAGE 544
UPDATE The wildcard character, whether user speci ed or part of the default output device name, is an almost unique ve-byte, alphanumeric character string. The possibility of two identical wildcards being generated is remote. When a le is used as the LONG column input or output device and you do not specify a group and account name in the LONG column I/O string, the default is the group and account in which your program is running.
PAGE 545
UPDATE STATISTICS UPDATE STATISTICS The UPDATE STATISTICS statement updates the system catalog to re ect 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 2 UPDATE STATISTICS FOR TABLE 3 Owner. TableName SYSTEM.SystemViewName Parameters [Owner .]TableName identi es a table. SYSTEM.SystemViewName identi es a system view.
PAGE 546
UPDATE STATISTICS You may nd it convenient to use the VALIDATE statement after an UPDATE STATISTICS. 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. Authorization You can issue this statement if you have OWNER authority for the table or if you have DBA authority.
PAGE 547
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 SET 8 < : 3 2 Owner. 3TableName 2 Owner. ViewName 99 8 = =2 < Expression , ... ColumnName = 'LongColumnIOString' : NULL 3 ;; WHERE CURRENT OF CursorName Parameters [Owner .
PAGE 548
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.
PAGE 549
UPDATE WHERE CURRENT In a rule de ned with a StatementType of UPDATE, any column reference in the Condition or any ParameterValue that speci es the OldCorrelationName will refer to the value of the column before the SET clause assignment is performed on it. Any column reference that speci es the NewCorrelationName or TableName will refer to the value of the column after the SET clause assignment is performed on it.
PAGE 550
UPDATE WHERE CURRENT Description|LongColumnIOString The input device must not be locked or have privilege security. An input device le can be a standard MPE/iX le with xed record size, valid blocking factor, valid le equations, ASCII/binary option, and user labels option. Any related output device le will have the same characteristics as the input device le.
PAGE 551
UPDATE WHERE CURRENT Example 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.
PAGE 552
VALIDATE The VALIDATE statement validates modules and procedures that have already been created. Scope ISQL or Application Programs SQL Syntax FORCE VALIDATE DROP SETOPTINFO 3 92 3 8 82 , ... Owner. 2 ModuleName > > 92 3 8 MODULE > > > SECTION Owner. ModuleName (SectionNumber) , . . . > > > > > > < PROCEDURE 92 3 82 3 3 , ... Owner.2 ProcedureName 92 8 3 SECTION Owner. ProcedureName (SectionNumber) , . . .
PAGE 553
VALIDATE Description When you validate a module or procedure, all the sections within it are checked and validation is attempted. If an embedded SQL statement accesses an object that does not exist or that the module or procedure owner is not authorized to execute, then the corresponding section is marked invalid. You may nd it convenient to use the VALIDATE statement after an UPDATE STATISTICS, since UPDATE STATISTICS will invalidate stored sections.
PAGE 554
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 nd 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.
PAGE 555
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 8 < SQLERROR : 8 9> STOP => < 9 > > = CONTINUE 2 3 SQLWARNING ;> GOTO 2: 3Label > > > ; : NOT FOUND GO TO : Label Parameters SQLERROR SQLWARNING NOT FOUND STOP CONTINUE GOTO [:]Label refers to a test for the condition SQLCODE < 0.
PAGE 556
WHENEVER Description In an application, SQLCODE and SQLWARN0 are elds in the SQLCA or built-in variables. They are structures ALLBASE/SQL uses to return status information about SQL statements. In a procedure, ::sqlcode and ::sqlwarn0 are built-in variables. If the WHENEVER statement is not speci ed for a condition, the default action is CONTINUE.
PAGE 557
WHILE WHILE The WHILE statement is used to allow looping within a procedure. Scope Procedures only SQL Syntax 2 WHILE Condition DO Statement; 2 ... 33 ENDWHILE; Parameters Condition Statement speci es 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.
PAGE 558
WHILE Example Create and execute a procedure to display all the quantities in the LargeOrders table for a given part: CREATE PROCEDURE ShowOrders AS BEGIN DECLARE Quantity INTEGER; DECLARE PartName CHAR(16); DECLARE QtyCursor CURSOR FOR SELECT PartName, Quantity FROM LargeOrders; OPEN QtyCursor; WHILE ::sqlcode <> 100 DO FETCH QtyCursor INTO :PartName, :Quantity PRINT :PartName; PRINT :Quantity; ENDWHILE; CLOSE QtyCursor; END; EXECUTE PROCEDURE ShowOrders; 10-310 SQL Statements
PAGE 559
A SQL Syntax Summary ADD DBEFILE ADD DBEFILE DBEFileName TO DBEFILESET DBEFileSetName ADD TO GROUP ADD 9 8 < DBEUserID =2 3 GroupName , . . . TO GROUP TargetGroupName ; : ClassName ADVANCE ADVANCE CursorName 2 3 USING SQL DESCRIPTOR SQLDA AreaName ALTER DBEFILE ALTER DBEFILE DBEFileName SET TYPE = 8 9 < TABLE = : INDEX ; MIXED ALTER TABLE 2 3 ALTER TABLE Owner.
PAGE 560
AddConstraintSpecification 9 =2 8 < UniqueConstraint 3 ReferentialConstraint , . . . ) ; : CheckConstraint 33 2 2 CLUSTERING ON CONSTRAINT ConstraintID1 ADD CONSTRAINT ( DropConstraintSpecification DROP CONSTRAINT 2 (ConstraintID , . . .
PAGE 561
CHECKPOINT 3 2 :HostVariable 5 4 CHECKPOINT :LocalVariable :ProcedureParameter CLOSE 2 CLOSE CursorName 4 USING 8 <2 : 3 SQL DESCRIPTOR :HostVariable 22 SQLDA AreaName 3 INDICATOR :Indicator 32 , ...
PAGE 562
CREATE INDEX 2 32 3 3 2 CLUSTERING INDEX Owner. IndexName ON 3 2 3 2 ASC , . .. ) Owner. TableName ( ColumnName DESC CREATE UNIQUE CREATE PARTITION CREATE PARTITION PartitionName WITH ID = PartitionNumber CREATE PROCEDURE 2 3 2 = ProcLangName . ProcedureName LANG CREATE PROCEDURE Owner 32 3 3 2 2 . . . , ParameterDeclaration ) 33 ( ParameterDeclaration 32 2 2 . . . , ResultDeclaration WITH RESULT ResultDeclaration 32 3 2 3 2 AS BEGIN ProcedureStatement . . .
PAGE 563
CREATE TABLE 2 3 PRIVATE 6 PUBLICREAD 7 3 2 7TABLE Owner . CREATE 6 TableName 4 PUBLIC 5 PUBLICROW 3 2 LANG = TableLanguageName 9 8 ColumnDe > > < nition > > 3 UniqueConstraint =2 , ... ) ( ReferentialConstraint > > > > ; : CheckConstraint 2 3 3 2 , . . .
PAGE 564
Check Constraint (Table Level) 2 CHECK (SearchCondition) CONSTRAINT ConstraintID 3 2 IN DBEFileSetName3 3 CREATE TEMPSPACE CREATE 3 2 TEMPSPACE TempSpaceName WITH MAXFILEPAGES = MaxTempFileSize, LOCATION ='PhysicalLocation' CREATE VIEW 3 2 2 2 , . .. CREATE VIEW Owner2.
PAGE 565
DISABLE AUDIT LOGGING DISABLE AUDIT LOGGING DISABLE RULES DISABLE RULES DISCONNECT DISCONNECT 9 8 'ConnectionName' > > > > > > > > ' DBEnvironmentName ' = < :HostVariable > > > > ; > > ALL > > : CURRENT DROP DBEFILE DROP DBEFILE DBEFileName DROP DBEFILESET DROP DBEFILESET DBEFileSetName DROP GROUP DROP GROUP GroupName DROP INDEX 3 2 2 3 2 DROP INDEX Owner. IndexName FROM Owner. TableName 3 DROP MODULE 2 3 2 DROP MODULE Owner.
PAGE 566
DROP RULE 2 3 2 2 3 DROP RULE Owner. RuleName FROM TABLE Owner. TableName 3 DROP TABLE 3 2 DROP TABLE Owner. TableName DROP TEMPSPACE DROP TEMPSPACE TempSpaceName DROP VIEW 3 2 DROP VIEW Owner. ViewName ENABLE AUDIT LOGGING ENABLE AUDIT LOGGING ENABLE RULES ENABLE RULES END DECLARE SECTION END DECLARE SECTION EXECUTE StatementName 3 2 3 EXECUTE 2 ) Owner.
PAGE 567
EXECUTE IMMEDIATE EXECUTE IMMEDIATE 'String' :HostVariable EXECUTE PROCEDURE 32 2 3 . ProcedureName ReturnStatusVariable =3 32 Owner EXECUTE PROCEDURE :32 3 3 2 2 2 ...
PAGE 568
GRANT 2 3 8 PRIVILEGES ALL > > 8 > > SELECT > >> > > > > > > > > > INSERT > <> > > DELETE < GRANT ON 9 > > > > > > > > > > > = 9 > > > > > > > > > = |, . . . | > > ALTER > > > > > > > > > > > > > > INDEX > >> > > > 3 3 2 8 92 > > > > > > > > > > , . . . ColumnName ) UPDATE ( > > > > > > > 2 8 92 3 3 ; :> ; : REFERENCES ( ColumnName , . . . ) 9 8 DBEUserID > > 3 > > 2 =2 < 3 2 Owner . TableName GroupName 2 3 WITH GRANT , ... TO Owner.
PAGE 569
INCLUDE SQLCA SQLDA INCLUDE 22 3 IS EXTERNAL 3 INSERT - 1 3 2 Owner. 3TableName BULK INSERT INTO Owner . ViewName 3 3 2 8 92 ( ColumnName , . . .
PAGE 570
INSERT - 2 2 INSERT INTO 2 3 Owner. 3TableName Owner. ViewName 2 2 (ColumnName , . . . 3 3 ) QueryExpression Labeled Statement Label: Statement LOCK TABLE 2 3 LOCK TABLE Owner.
PAGE 571
RAISE ERROR 2 RAISE ERROR ErrorNumber 32 MESSAGE ErrorText 3 REFETCH REFETCH CursorName INTO 8 :HostVariable 22 3 INDICATOR :Indicator 3 92 , . .. 3 RELEASE RELEASE REMOVE DBEFILE REMOVE DBEFILE DBEFileName FROM DBEFILESET DBEFileSetName REMOVE FROM GROUP REMOVE 9 8 < DBEUserID =2 3 3 2 GroupName , . . . FROM GROUP Owner. TargetGroupName ; : ClassName RENAME COLUMN 2 3 RENAME COLUMN Owner. TableName.ColumnName TO NewColumnName RENAME TABLE 2 3 RENAME TABLE Owner.
PAGE 572
REVOKE Revoke Table or View Authority 2 3 9 8 ALL PRIVILEGES > > > > 3 2 > > > > SELECT > > > > > >6 > > 7 > > INSERT > > 7 6 > > = <6 7 7 6 DELETE REVOKE 6 7 > 6 ALTER 7|, . . . | > > > > 7 6 > > > > > 7 6 INDEX > > > > 3 3 2 8 92 7 6 > > > > 5 4 , . . . ColumnName ) UPDATE ( > > > > 2 8 92 3 3 ; : REFERENCES ( ColumnName , . . . ) 8 9 DBEUserID > > 3 > > 2 < =2 32 3 Owner . TableName GroupName 3 CASCADE , ... FROM ON 2 Owner.
PAGE 573
ROLLBACK WORK 2 ROLLBACK WORK 6 6 TO 6 6 4 93 > > =7 :HostVariable 7 7 :LocalVariable > > > > ;7 : :ProcedureParameter 5 8 SavePointNumber > > < RELEASE SAVEPOINT 3 2 :HostVariable 5 4 SAVEPOINT :LocalVariable :ProcedureParameter SELECT Select Statement Level 2 3 BULK QueryExpression ORDER BY ASC ColumnID DESC 2 , ... 3 Subquery Level (QueryExpression) Query Expression Level QueryBlock (QueryExpression) 2 UNION ALL 3 QueryBlock (QueryExpression) 2 . ..
PAGE 574
HostVariableSpecification|With BULK Option 2 2 :Bu er ,:StartIndex ,:NumberOfRows 33 HostVariableSpecification|Without BULK Option 8 :HostVariable 22 3 INDICATOR :Indicator 3 92 , ...
PAGE 575
SETOPT 8 CLEAR > > > > < 2 3 ScanAccess , . .. JoinAlgorithm SETOPT > > 3 2 ScanAccess > > : BEGIN ; . . .
PAGE 576
SET SESSION SET SESSION 8 9 8 9 > > > > RR > > > > > > > > > > > > > > > > CS > > > > > > > > > > > > > > > > RC > > > > > > > > > > > > > > > > > > > > RU > > > > > > > > > > < = > > REPEATABLE READ > > > > ISOLATION LEVEL > > > > > > > > SERIALIZABLE > > > > > > > > > > > > > > > > CURSOR STABILITY > > > > > > > > > > > > > > > > READ COMMITTED > > > > > > > > > > > > > > > > > > > > READ UNCOMMITTED > > > > > > > > > > : ; > > : HostVariable1 > > > > > > > > > > Priority > > > > PRIORITY > > > > :
PAGE 577
SET TRANSACTION SET TRANSACTION 8 9 9 8 > > RR > > > > > > > > > > > > > > > > CS > > > > > > > > > > > > > > > > > RC > > > > > > > > > > > > > > > > > > > RU > > > > > > > > > > < = > > REPEATABLE READ > > > > > ISOLATION LEVEL > > > > > > > SERIALIZABLE > > > > > > > > > > > > > > > > CURSOR STABILITY > > > > > > > > > > > > > > > > > > READ COMMITTED > > > > > > > > > > > > > > > > > > READ UNCOMMITTED > > > > > > > > > > : ; > > : HostVariable1 > > > > > > > > > > Priority > > > > PRIORITY > > >
PAGE 578
START DBE 2 32 ' MULTI START DBE 'DBEnvironmentName' AS ConnectionName 3 BUFFER = (DataBu erPages, LogBu erPages) 7 6 TRANSACTION = MaxTransactions 7 6 9 8 6 SECONDS = 7 < 7 6 TimeoutValue 7 6 MINUTES MAXIMUM TIMEOUT = 7 6 ;7 : 6 NONE 7|, . . . | 6 97 8 6 6 SECONDS = 7 < 7 6 TimeoutValue 7 6 DEFAULT TIMEOUT = MINUTES 6 ;7 : 5 4 MAXIMUM RUN BLOCK = ControlBlockPages 3 2 START DBE NEW 2 ' START DBE 'DBEnvironmentName' AS 'ConnectionName 3 2 DUAL 7 6 AUDIT | . . .
PAGE 579
DBEFile0Definition DBEFILE0 DBEFILE DBEFile0ID WITH PAGES = DBEFile0Size , NAME = 'SystemFileName1' DBELogDefinition 3 2 LOG DBEFILE DBELog1ID AND DBELog2ID WITH PAGES = DBELogSize, 2 3 NAME = 'SystemFileName2' AND 'SystemFileName3' START DBE NEWLOG 2 START DBE 'DBEnvironmentName' AS 'ConnectionName' 9 3 28 < ARCHIVE = 7 6 DUAL | . . .
PAGE 580
STOP DBE STOP DBE TERMINATE QUERY TERMINATE QUERY FOR CID ConnectionID XID TransactionID TERMINATE TRANSACTION TERMINATE TRANSACTION FOR CID ConnectionID XID TransactionID TERMINATE USER TERMINATE USER 8 < DBEUserID : 9 = SessionID ; CID ConnectionID TRANSFER OWNERSHIP TRANSFER OWNERSHIP OF 3 32 82 Owner . TABLE TableName > 3 32 > <2 9 > > = Owner . ViewName 3 2 TO NewOwnerName Owner.
PAGE 581
LongColumnIOString 33 2 2 9 8 FileName .Group .Account > > > > < > > > > % HeapAddress : LengthofHeap > > 3 3 2 2 > 9 8 9 8 3> = <2 = < > =< FileName .Group .Account | 7> 6 >> CharString$ > > > 7 6 > ;5> > > 4 : >! ;: CharString$CharString > > > > ; : . .. | >%$ UPDATE STATISTICS 2 UPDATE STATISTICS FOR TABLE 3 Owner. TableName SYSTEM.SystemViewName UPDATE WHERE CURRENT UPDATE SET 8 < : 3 2 Owner. 3TableName 2 Owner. ViewName 99 8 = =2 < Expression , ...
PAGE 582
WHENEVER WHENEVER 8 < SQLERROR : 9 > > = 8 9> STOP => < CONTINUE 2 3 SQLWARNING GOTO 2: 3Label > ;> > > ; : NOT FOUND GO TO : Label WHILE 2 WHILE Condition DO Statement; A-24 SQL Syntax Summary 2 ...
PAGE 583
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 2 3 2 C HANGE Delimiter OldString Delimiter NewString Delimiter @ 3 DO CommandNumber DO CommandString EDIT 2 ED IT 3 END 2 EN D 3 ERASE 2 3 ER ASE FileName EXIT 2 EX IT 3 EXTRACT 2 3 2 8 , < MODULE 2Owner. ModuleName 3 ... 3 2 SECTION Owner. ModuleName(SectionNumber) , . . .
PAGE 584
HELP 2 HE LP 8 @ 3< SQLStatement ISQLCommand : 33 92 2 = D2 ESCRIPTION 3 4 S YNTAX 5 2 3 ; E AMPLE HOLD 2 HO LD 3 SQLStatement ISQLCommand SQLStatement EscapeCharacter; ISQLCommand 2 . .. 3 INFO IN 2 3 2 3 Owner. 3TableName 2 FO Owner. ViewName INPUT 2 INP UT 3 3 2 2 32 3 2 Owner. 3TableName 2 (ColumnName ,ColumnName . . . ) Owner. ViewName (Value ,Value 32 ROLLBACK WORK COMMIT WORK 3 ... ) 2 . ..
PAGE 585
LIST SET 2 3 2 LI ST S ET 3 Option @ LOAD 2 3 3 2 E XTERNAL 2 3 InputFileName AT StartingRow LO AD P ARTIAL FROM I NTERNAL 3 2 3 2 Owner. 3TableName ExternalInputSpec 2 FOR NumberOfRows TO USING DescriptionFileName Owner. ViewName 3 2 Y2 ES3 PatternLocation Pattern N O 2 32 2 33 ExternalInputSpec 2 ColumnName StartingLocation Length NullIndicator 3 2 FormatType 3 2 3 2 . . .
PAGE 586
RENAME 2 3 REN AME OldFileName NewFileName SELECTSTATEMENT 2 2 3 32 SelectStatement; PA USE ; BrowseOption; SET 2 3 SE T Option OptionValue Options and Values AUTOC[OMMIT] ON | OFF AUTOL[OCK] ON | OFF AUTOS[AVE] NumberofRows C[ONTINUE] ON | OFF CONV[ERT] ASCII | EBCDIC | OFF EC[HO] ON | OFF ECHO_[ALL] ON | OFF EDITOR EditorName 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[U
PAGE 587
START 2 STA RT 32 2 32 CommandFileName (Value ,Value 32 3 3 . .. ) STORE 2 3 2 2 STO RE FileName R EPLACE 33 SYSTEM 2 SY STEM : 3 2 MPE/iXCommand 3 UNLOAD 2 3 E XTERNAL 2 3 OutputFileName U NLOAD TO NTERNAL I 3 9 82 < 2 Owner. 3TableName = ExternalOutputSpec FROM Owner. ViewName ; : "SelectCommand" 2 3 ExternalOutputSpec DescriptionFileName 2 FractionLength OutputLength 3 2 NullIndicator 3 2 ...
PAGE 588
PAGE 589
C Sample DBEnvironment The DBEnvironment used in examples throughout the ALLBASE/SQL manual set is called PartsDBE . Your installation package includes the necessary les 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.
PAGE 590
d Options for Setting Up ALLBASE/SQL Sample DBEnvironments a =============================================================== Choose one: 1. 2. 3. 4. 5. 6. 7. 0.
PAGE 591
Using CREASQL The following is an alternate method for setting up PartsDBE. Use the command le CREASQL to stream a job that sets up PartsDBE. First, copy the CREASQL stream le to your group and account with the following command: : FCOPY FROM=CREASQL.SAMPLEDB.SYS;TO=CREASQL;NEW 4Return5 Using an editor, modify CREASQL to include your password(s), user name, account name, and group name. Lines that need to be modi ed are shown in inverse display.
PAGE 592
STARTDBE Command File /*This file creates the PartsDBE DBEnvironment with MULTI user mode and dual logging.
PAGE 593
CREATABS Command File CREATABS Command File /* The following commands create the Purchasing Department's DBEFileSet with two DBEFiles. */ CREATE DBEFILESET PurchFS; CREATE DBEFILE PurchDataF1 WITH PAGES = 50, NAME = 'PurchDF1', TYPE = TABLE; CREATE DBEFILE PurchIndxF1 WITH PAGES = 50, NAME = 'PurchXF1', TYPE = INDEX; ADD DBEFILE PurchDataF1 TO DBEFILESET PurchFS; ADD DBEFILE PurchIndxF1 TO DBEFILESET PurchFS; /* The following commands create the Warehouse Department's DBEFileSet with two DBEFiles.
PAGE 594
CREATABS Command File 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.SupplyBatches (VendPartNumber CHAR(16) NOT NULL, BatchStamp DATETIME DEFAULT CURRENT_DATETIME NOT NULL PRIMARY KEY, MinPassRate FLOAT) IN WarehFS; CREATE PUBLIC TABLE ManufDB.TestData (BatchStamp DATETIME NOT NULL REFERENCES ManufDB.
PAGE 595
CREATABS Command File CREATE PUBLIC TABLE (OrderNumber VendorNumber OrderDate IN OrderFS; CREATE PUBLIC TABLE (OrderNumber ItemNumber VendPartNumber PurchasePrice OrderQty ItemDueDate ReceivedQty IN OrderFS; PurchDB.Orders INTEGER INTEGER, CHAR(8) ) NOT NULL, PurchDB.OrderItems INTEGER NOT NULL, INTEGER NOT NULL, CHAR(16), DECIMAL(10,2) NOT NULL, SMALLINT, CHAR(8), SMALLINT ) CREATE PUBLIC TABLE PurchDB.
PAGE 596
CREATABS Command File CREATE DBEFILESET RecFS; CREATE DBEFILE RecDataF1 WITH PAGES = 50, NAME = 'RecDF1', TYPE = MIXED; ADD DBEFILE RecDataF1 TO DBEFILESET RecFS; /* The following commands create three tables that comprise the RecDB database. */ 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.
PAGE 597
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 SUPPLYBA.SAMPLEDB.SYS TO ManufDB.SupplyBatches VENDPARTNUMBER 1 16 BATCHSTAMP 18 23 MINPASSRATE 43 8 ? END N; COMMIT WORK; :echo Table SupplyBatches successfully loaded!; LOAD FROM EXTERNAL TESTDATA.SAMPLEDB.SYS TO ManufDB.
PAGE 598
LOADTABS Command File LOAD FROM EXTERNAL OrderNumber VendorNumber OrderDate END N; COMMIT WORK; :echo Table Orders ORDERS.SAMPLEDB.SYS TO PurchDB.Orders 1 10 11 10 ? 21 8 ? successfully loaded!; LOAD FROM EXTERNAL ORDERITE.SAMPLEDB.SYS TO PurchDB.OrderItems OrderNumber 1 10 ItemNumber 11 10 VendPartNumber 21 16 ? PurchasePrice 37 12 OrderQty 49 5 ? ItemDueDate 54 8 ? ReceivedQty 62 5 ? END N; COMMIT WORK; :echo Table OrderItems successfully loaded!; LOAD FROM EXTERNAL VENDORS.SAMPLEDB.SYS TO PurchDB.
PAGE 599
LOADTABS Command File LOAD FROM EXTERNAL SponsorClub Event Date Time Coordinator END N; COMMIT WORK; :echo Table Events EVENTS.SAMPLEDB.SYS TO RecDB.Events 1 15 ? 20 30 ? 50 10 ? 62 8 ? 71 20 ? successfully loaded!; INSERT INTO PURCHDB.REPORTS VALUES ('Report1', 'FREE', '< REPORT1.SAMPLEDB.
PAGE 600
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.SupplyPrice (VendPartNumber); CREATE UNIQUE INDEX VendorNumIndex ON PurchDB.
PAGE 601
CREASEC Command File CREASEC Command File /* This file sets up authorities for the PurchDB and RecDB databases. */ /* The DBA for the sample DBEnvironment is the DBEUserID John@Brock. */ REVOKE REVOKE REVOKE REVOKE REVOKE REVOKE REVOKE REVOKE REVOKE REVOKE ALL ALL ALL ALL ALL ALL ALL ALL ALL ALL ON ON ON ON ON ON ON ON ON ON PurchDB.Parts FROM PUBLIC; PurchDB.Inventory FROM PUBLIC; PurchDB.SupplyPrice FROM PUBLIC; PurchDB.Vendors FROM PUBLIC; PurchDB.Orders FROM PUBLIC; PurchDB.
PAGE 602
CREASEC Command File ADD Karen@Thomas TO GROUP Purchasing; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.Inventory TO Purchasing; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.SupplyPrice TO Purchasing; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.Vendors TO Purchasing; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.Orders TO Purchasing; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.OrderItems TO Purchasing; /* The following commands create the Receiving Department's /* group.
PAGE 603
CREASEC Command File /* The following commands create the Warehouse Department's /* group. This group has SELECT, INSERT, DELETE, and UPDATE /* authority for the Parts and Inventory tables of the /* PurchDB database. */ */ */ */ CREATE GROUP Warehouse; ADD Kelly@Cota TO GROUP Warehouse; ADD Al@Dal TO GROUP Warehouse; ADD Peter@Kane TO GROUP Warehouse; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.Parts TO Warehouse; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.
PAGE 604
CREASEC Command File GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.OrderItems TO AccountsPayable; /* The following commands create the group called Purch. All DBEUserIDs, */ /* or the groups to which they belong, are made members of this group. */ /* This group has CONNECT authority only to PartsDBE.
PAGE 605
CREASEC Command File /* The following commands create a group called DBEUser for all /* other DBEUserIDs, and GRANTS specific authorities to this /* group. */ */ */ CREATE GROUP DBEUsers; GRANT CONNECT TO DBEUsers; GRANT RESOURCE to DBEUsers; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.Parts TO DBEUsers; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.Inventory TO DBEUsers; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.SupplyPrice TO DBEUsers; GRANT SELECT, INSERT, DELETE, UPDATE ON PurchDB.
PAGE 606
CREASEC Command File INSERT, DELETE, UPDATE ON RecDB.Clubs TO DBEUsers; GRANT SELECT, INSERT, DELETE, UPDATE ON RecDB.
PAGE 607
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.
PAGE 608
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.94 9050 |1984-07-13 09:25:53.183 | 0.
PAGE 609
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.319 |1984-07-29 |14:01:28 |20:16:07 | 1984-06-19 08:45:33.
PAGE 610
PurchDB.Inventory Table select partnumber,binnumber,qtyonhand,lastcountdate from purchdb.
PAGE 611
PurchDB.OrderItems Table PurchDB.OrderItems Table select ordernumber,itemnumber,vendpartnumber from purchdb.
PAGE 612
PurchDB.OrderItems Table select purchaseprice,orderqty,itemduedate,receivedqty from purchdb.orderitems; --------------+--------+-----------+----------PURCHASEPRICE |ORDERQTY|ITEMDUEDATE|RECEIVEDQTY --------------+--------+-----------+----------2000.00| 5|19840621 | 3 565.00| 10|19840621 | 10 450.00| 5|19840701 | 5 180.00| 5|19840701 | 2 210.00| 5|19840701 | 5 70.00| 10|19840616 | 8 435.00| 3|19840705 | 2 70.00| 5|19840701 | 5 345.00| 3|19840701 | 3 195.00| 5|19840701 | 5 180.00| 5|19840715 | 5 195.
PAGE 613
PurchDB.Orders Table PurchDB.Orders Table isql=> select * from purchdb.
PAGE 614
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.00 1243-MU-01 |Extended Memory Unit | 100.
PAGE 615
PurchDB.Reports Table PurchDB.Reports Table select reportname, reportowner, filedata from purchdb.
PAGE 616
PurchDB.SupplyPrice Table isql=> select partnumber, vendornumber,vendpartnumber from purchdb.
PAGE 617
PurchDB.SupplyPrice Table 1923-PA-01 | 9002|8113 1923-PA-01 | 9008|790805 1923-PA-01 | 9012|71755 1923-PA-01 | 9014|15550 1933-FD-01 | 9001|7310 1933-FD-01 | 9003|93715 1933-FD-01 | 9007|35701 1933-FD-01 | 9009|99201 1933-FD-01 | 9014|16530 1943-FD-01 | 9007|37502 -----------------------------------------------------------------------Number of rows selected is 69 U[p], d[own], l[eft], r[ight], t[op], b[ottom], pr[int] , or e[nd]> isql=> select unitprice,deliverydays,discountqty from purchdb.
PAGE 618
PurchDB.SupplyPrice Table 1650.00| 30| 35 260.00| 15| 10 230.00| 15| 13 250.00| 20| 11 240.00| 20| 12 250.00| 15| 18 225.00| 20| 14 255.00| 15| 19 435.00| 20| 0 450.00| 15| 2 450.00| 15| 1 1985.00| 20| 1 1990.00| 15| 1 70.00| 15| 7 80.00| 20| 7 70.00| 20| 8 75.00| 10| 6 565.00| 20| 5 585.00| 15| 5 600.00| 10| 5 590.00| 15| 5 585.00| 20| 3 575.
PAGE 619
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. |Diane Oliver 9006|Pro-Litho Inc.
PAGE 620
PurchDB.Vendors Table select vendorcity,vendorstate,vendorzipcode from purchdb.
PAGE 621
RecDB.Clubs Table RecDB.Clubs Table select * from recdb.
PAGE 622
RecDB.Events Table select * from recdb.
PAGE 623
RecDB.Members Table RecDB.Members Table select * from recdb.
PAGE 624
Sample Program Files The following table contains a list of sample program les located in SAMPLEDB.SYS. All programs except those marked with an asterisk (*) are fully discussed in the ALLBASE/SQL application programming guides. Table C-1. Sample Programs in SAMPLEDB.SYS C FORTRAN Pascal Description cex2 cobex2 forex2 pasex2 Single-row SELECT into host variables from PurchDB.Parts cex5 cobex5 forex5 pasex5 Single-row SELECT into host variables from PurchDB.
PAGE 625
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 ags all features or extensions that do not conform to the SQL language or are processed in a nonconforming manner. FIPS 127.
PAGE 626
Table D-1. ALLBASE/SQL FIPS 127.1 Compliance FIPS 127.1 Compliant Statement? ALLBASE/SQL Statement ADD DBEFILE NO ADD TO GROUP NO ADVANCE NO ALTER DBEFILE NO ALTER TABLE NO BEGIN NO BEGIN ARCHIVE NO Extension to Statement FIPS 127.
PAGE 627
Table 1.1. ALLBASE/SQL FIPS 127.1 Compliance (Cont) FIPS 127.1 Compliant Statement? ALLBASE/SQL Statement Extension to Statement FIPS 127.
PAGE 628
Table 1.1. ALLBASE/SQL FIPS 127.1 Compliance (Cont) FIPS 127.
PAGE 629
Table 1.1. ALLBASE/SQL FIPS 127.1 Compliance (Cont) FIPS 127.
PAGE 630
Table 1.1. ALLBASE/SQL FIPS 127.1 Compliance (Cont) FIPS 127.1 Compliant Statement? ALLBASE/SQL Statement INSERT SingleRowValues YES LOCK TABLE NO LOG COMMENT NO OPEN YES 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 D-6 Standards Flagging Support Extension to Statement FIPS 127.
PAGE 631
Table 1.1. ALLBASE/SQL FIPS 127.1 Compliance (Cont) FIPS 127.
PAGE 632
Table 1.1. ALLBASE/SQL FIPS 127.1 Compliance (Cont) FIPS 127.1 Compliant Statement? ALLBASE/SQL Statement STOP DBE NO STOREINFO NO TERMINATE USER NO TRANSFER OWNERSHIP NO TRUNCATE TABLE NO UPDATE YES UPDATE STATISTICS NO UPDATE WHERE CURRENT YES VALIDATE NO WHENEVER YES D-8 Standards Flagging Support Extension to Statement FIPS 127.
PAGE 633
Non-Standard Data Types The following data types are not FIPS compliant. They are used in CREATE TABLE and ALTER TABLE column de nitions.
PAGE 634
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 agger warning is generated for these exceptions. Table D-2.
PAGE 635
Table 1.2. FIPS Syntax Rules and ALLBASE/SQL Exceptions (Cont) Section # Status Rule # Note FIPS SQL Syntax Rule ALLBASE/SQL Exception 8.7 SR 6b An INSERT into an exact numeric column must be an exact numeric value. Compatible data and truncation are allowed. (No agger warning is generated). 8.10 SR 4b In a SELECT . . . INTO, only an exact numeric column or expression may be selected into an exact numeric host variable. Compatible data and truncation are allowed. (No agger warning is generated).
PAGE 636
PAGE 637
Index A access plan de ned, 10-145 modifying with SETOPT, 10-243 access to databases multiple connections, 2-33 types, 1-2 active connection de ned, 2-40 active set in DECLARE CURSOR, 10-91 in FETCH, 10-140 in OPEN, 10-179 in REFETCH, 10-189 actual parameter using in procedures, 4-13 ADD DBEFILE syntax, 10-11 adding ADD TO GROUP, 10-13 column to table, 10-19 constraint to table, 10-19 DBEFiles, 10-11 members to authorization group, 10-13 rows, 2-20, 10-162 ADD MONTHS in an expression , 8-2 syntax, 8-8 ADD
PAGE 638
enabling logging, 10-128 functionality de nition, 2-29 information wrapperDBE, 2-30 log record, 2-29 partition with ALTER TABLE, 10-19 tool, 2-30 transactions with SQLAudit, 2-31 AUDIT NAME in START DBE NEW, 10-267 in START DBE NEWLOG, 10-276 authorities and program development, 2-32 and program use, 2-32 de ned, 1-4 direct, 10-153 granting, 2-16, 10-152 granting on DBEFileSET, 10-156 how to obtain, 2-15 OWNER, 2-15 REFERENCES, 2-15 revoking, 2-16, 10-201 RUN, 2-15 summary of types, 2-15 table and view, 2-1
PAGE 639
case sensitive comparison predicate, 9-6 comparisons, 7-4 CAST function arguments in, 8-12 syntax, 8-12 CATALOG owner of catalog views, 6-5 catalog views explained, 2-41 chain of grants, 2-16 of rules, 4-22, 4-26 changing connections, 10-241 data, 2-21 DBEFile type, 10-17 table locking , 10-19 CHAR conversions rules, 10-217 de ned, 7-2 native language data, 7-18 storage requirements, 7-4 check constraint authorization in ALTER TABLE, 10-24 authorization in CREATE TABLE, 10-82 de ned, 2-9, 4-3 in ALTER TABLE
PAGE 640
connection and SET CONNECTION, 10-232 changing, 10-241 disconnecting, 10-110 initiating, 10-44 terminating, 2-40 to DBEnvironments, 2-34 use with timeouts, 2-33 connection name in CONNECT, 10-44 in SET CONNECTION, 10-232 in START DBE, 10-264 in START DBE NEWLOG, 10-276 constant de ned, 8-16 in an expression , 8-2 in NULL predicates, 9-15 CONSTANT syntax, 8-16 constraint check constraint, 2-9, 4-1 de ned, 4-1 de ning, 10-73 error checking and SET statement, 10-234 error checking in SET SESSION, 10-248 error
PAGE 641
current connection in CONNECT, 10-44 none after DISCONNECT CURRENT, 2-41 setting, 2-34 CURRENT DATE function syntax, 8-17 CURRENT DATETIME function syntax, 8-17 CURRENT TIME function syntax, 8-17 cursor names in CLOSE, 10-37 in DECLARE CURSOR, 10-90 in DELETE WHERE CURRENT, 10-100 in FETCH, 10-140 in OPEN, 10-179 in UPDATE WHERE CURRENT statement, 10-299 rules for, 6-1 cursors active set, 10-140, 10-189 advancing, 10-15 and INTO clause, 10-219 closed, 10-284 closing, 10-37, 10-191, 10-208 current row, 10-14
PAGE 642
de ned, 1-6 naming, 10-272 DBEFile names group name, 10-46 in ADD DBEFile, 10-11 in ALTER DBEFile, 10-17 in CREATE DBEFILE, 10-46 in DROP DBEFILE, 10-112 in REMOVE DBEFile, 10-192 rules for, 6-1 DBEFiles adding, 10-11 altering type of, 10-17 creating, 10-46 de ned, 1-5 dropping, 10-112, 10-192 for data, 1-5 for indexes, 1-5 incrementing size, 10-46 purging, 10-112 relation to DBEFileset, 1-5 removing from DBEFileSet, 10-112, 10-114, 10-192 size range, 10-46 type, 10-46 using, 10-46 DBEFileSet authorization
PAGE 643
arguments in aggregate functions, 8-11 DECLARE and local variables in a procedure, 4-13 DECLARE CURSOR syntax, 10-90 DECLARE Variable syntax, 10-95 declaring cursors, 10-90 host variables, 10-30, 10-130 local variables in a procedure, 10-95 default columns in tables, 10-19 ownership discussed, 2-18 default DBEFileSet dropping, 10-114 setting, 10-237 default partition in START DBE NEW, 10-267 in START DBE NEWLOG, 10-276 deferred error checking constraint (SET CONSTRAINTS), 10-234 explained, 4-8 referential c
PAGE 644
modules, 10-119 partition, 10-121 procedures, 10-122 rules, 10-123 tables, 10-124 TempSpaces, 10-126 views, 10-124, 10-127 DROP PROCEDURE syntax, 10-122 DROP RULE syntax, 10-123 DROP TABLE syntax, 10-124 DROP TEMPSPACE syntax, 10-126 DROP VIEW syntax, 10-127 dual logging, 10-267 duplicate column DISTINCT, 8-11 column values eliminated, 8-11 dynamic parameters example of usage , 8-6 example with BULK INSERT, 10-173 example with INSERT, 10-173 in DECLARE CURSOR:specifying, 10-92 in EXECUTE PROCEDURE, 10-137 s
PAGE 645
under ow , 8-6 use, 8-1 use of parentheses , 8-6 USER expression value, 9-8 extended characters comparison prediate, 9-6 F FETCH syntax, 10-140 fetching rows, 10-140 le names explained, 6-4 le space management for tables and indexes, 2-4 FILL option setting in BEGIN WORK, 10-31 setting in SET SESSION, 10-248 FIPS agging support, D-1 xed-length strings de ned, 7-2 xed-point values in constants, 8-16 FLOAT conversions rules, 10-217 de ned, 7-2 storage requirements, 7-4 FORCE VALIDATE parameter, 10-304 FOREIG
PAGE 646
H hash in CREATE TABLE, 10-73 specifying with SETOPT, 10-243 HAVING in SELECT, 10-220 in simple queries, 3-2 hexadecimal string in constants, 8-16 host variables declaration of, 10-30, 10-130 di erences from local variables, 4-13 free log space, 10-35 in an expression , 8-2 in CONNECT, 10-44 in EXECUTE IMMEDIATE, 10-136 in FETCH, 10-140 in INSERT, 10-163, 10-173 in LIKE predicates, 9-12 in PREPARE, 10-183 input, 2-32 in ROLLBACK WORK, 10-208 in SAVEPOINT, 10-210 in SELECT, 10-212 in SET CONNECTION, 10-232
PAGE 647
isolation level de ned, 5-17 setting in BEGIN WORK, 10-31 setting in SET SESSION, 10-248 setting in SET TRANSACTION, 10-254 ISQL de ned, 1-2 EXECUTE PROCEDURE in, 4-16 syntax summary, B-1 using to issue statements, 2-26 IX lock explained, 5-11 J join algorithm speci ed by SETOPT, 10-243 asymmetric, 3-16 in complex queries, 3-6 inner and outer, 10-222 natural, 10-226 nested loop, 10-243 not using explicit join syntax, 10-228 outer join, 3-19 sort merge, 10-243 symmetric, 3-16 three or more tables, 10-227 jo
PAGE 648
in START DBE, 10-264 in START DBE NEW, 10-267 in START DBE NEWLOG, 10-276 log bu ers ushing, 10-35 log comment generating, 10-178 LOG COMMENT syntax, 10-178 log le creating new, 10-276 de ned, 1-6 increasing or decreasing space, 10-281 orphaned, in wrapperDBE, 2-30 log le names assigning, 10-272 in START DBE NEWLOG, 10-276 rules for, 6-1 logging audit, 2-29, 10-267, 10-276 dual, 10-267, 10-276 rollback, 10-208 row level DML atomicity, 4-8 logical operators, 9-2 LONG I/O string syntax, 7-14 LONG BINARY de ne
PAGE 649
as index keys, 10-54 behavior in Cartesian product, 10-228 behavior in joins, 10-228 de ned, 7-8 in an expression , 8-6 in Cartesian product, 3-5 in comparison predicates, 9-6 in joins, 3-5 in search conditions, 9-3 in SET MULTITRANSACTION, 10-241 multiuser mode de ned, 1-7 in a DBE session, 2-3 N names basic, 6-1 used in ALLBASE/SQL, 6-1 naming database objects, 6-1 DBECon le, 6-4 DBEnvironment, 6-4 DBEUserID, 6-2 host variables, 6-4 owners, 6-2 system les, 6-4 with native language objects, 6-2 native-30
PAGE 650
origin of, 2-17 rules governing, 6-2 speci cation of, 1-4 use of, 2-17 ownership and dropping authorization groups, 10-116 creating objects, 2-17 how it is assigned, 2-17 of objects, 2-17 transferring, 2-17, 10-289 P page bu ers ushing, 10-35 page level locking, 5-9 pages deadlocks, 5-26 in DBEFiles, 10-46 in TempSpaces, 10-84 PARALLEL FILL option setting in BEGIN WORK, 10-31 setting in SET SESSION, 10-248 parameter in procedures, 4-13 naming rules, 6-4 using in procedures, 4-10 partition creating, 2-30, 1
PAGE 651
specifying in DECLARE CURSOR, 10-92 using DECLARE Variable in, 10-95 using with rules, 4-22 validating, 10-304 with multiple row sets, 10-61 with single format multiple row sets, 10-62 procedure cursor authorization in CREATE PROCEDURE, 10-92 de ned, 4-15, 10-61, 10-91 in ISQL, 4-16 parameters in, 4-13 query types, 4-15 procedure names in GRANT, 10-154 in REVOKE, 10-203 procedures and rules chains of, 4-22 using, 4-9, 4-20 programmatic database access de ned, 1-2 programs e ect of DBEnvironment changes on,
PAGE 652
de ned, 1-4 release DBE session, 10-191 in COMMIT WORK, 10-42 RELEASE syntax, 10-191 remote connections establishment of, 2-33 REMOVE DBEFile syntax, 10-192 REMOVE FROM GROUP syntax, 10-194 removing DBEFiles, 10-192 RENAME COLUMN syntax, 10-196 RENAME TABLE syntax, 10-197 renaming columns, 10-196 tables, 10-197 repeatable read isolation level in BEGIN WORK, 10-31 in SET SESSION, 10-249 in SET TRANSACTION, 10-255 repeatable read (RR) explained, 5-7 RESET syntax, 10-198 resetting ALLBASE/SQL system data, 10-1
PAGE 653
S sample DBEnvironment, C-1 SAVEPOINT in a procedure, 4-11 setting, 10-210 syntax, 10-210 using, 10-208 scale de ned, 7-2 in DECIMAL operations, 7-8 scoping transaction and session attributes, 2-26 search condition compatible predicates, 9-3 de ned, 3-2 de nition, 9-1 in complex queries, 3-6 in DELETE, 10-97 subquery in, 3-10 syntax, 9-2 type conversion in, 9-3 use for, 9-1 value extensions in, 9-3 SearchCondition in CREATE TABLE, 10-79 section authorization in PREPARE, 10-183 de ned, 2-31 invalidation by
PAGE 654
DML atomicity, 10-239 multiple-transaction mode, 2-37 options with BEGIN WORK, 10-31 savepoints, 10-210 the current connection, 10-232 the FILL option, 10-248 timeout values, 2-35 transaction mode, 2-36 setting DML atomicity, 4-8 setting DML ATOMICITY with SET SESSION, 10-248 with SET TRANSACTION, 10-254 setting transaction attributes with SET SESSION, 10-248 with SET TRANSACTION, 10-254 SET TRANSACTION CS isolation level, 10-255 cursor stability isolation level, 10-255 RC isolation level, 10-255 read commi
PAGE 655
BEGIN ARCHIVE, 10-28 BEGIN DECLARE SECTION, 10-30 BEGIN WORK, 10-31 BULK FETCH, 10-140 BULK INSERT, 10-162 BULK SELECT, 10-212 CHECKPOINT, 10-35 CLOSE, 10-37 COMMIT ARCHIVE, 10-40 COMMIT WORK, 10-42 CONNECT, 10-44 CREATE DBEFILE, 10-46 CREATE DBEFILESET, 10-49 CREATE GROUP, 10-51 CREATE INDEX, 10-53 CREATE PARTITION, 10-56 CREATE PROCEDURE, 10-58 CREATE RULE, 10-65 CREATE SCHEMA, 10-70 CREATE TABLE, 10-73 CREATE TEMPSPACE, 10-84 CREATE VIEW, 10-86 DECLARE CURSOR, 10-90 DECLARE Variable , 10-95 DELETE, 10-97
PAGE 656
SQLVer de ned, 1-3 S (SHARE) locks, 10-176 standards agging support, D-1 START DBE syntax, 10-264 START DBE NEW syntax, 10-267 START DBE NEWLOG syntax, 10-276 starting a DBE session using CONNECT, 10-44 using START DBE, 10-264 startup parameters de ned in START DBE NEW, 10-273 in START DBE NEW, 2-2 statement level constraint error checking, 10-234 DML atomicity, 10-239 error enforcement explained, 4-8 STOP DBE syntax, 10-284 stopping ALLBASE/SQL using STOP DBE, 10-284 session using DISCONNECT, 10-110 storag
PAGE 657
de ning default columns in, 10-19, 10-77 deleting all rows from, 10-291 dropping, 10-112, 10-124 dropping constraint from, 10-19 explicit locking, 10-176 granting authorities, 10-152 implicit locking, 10-73 inserting rows into, 10-162 locking, 5-15 locking explained, 5-9 referenced, 4-2 referencing, 4-3 revoking authorities, 10-201 updating statistics, 10-297 TABLE DBEFiles, 10-46 table names in ALTER TABLE, 10-19 in CREATE INDEX, 10-53 in CREATE TABLE, 10-73 in DELETE, 10-97 in DELETE WHERE CURRENT, 10-100
PAGE 658
management, 2-22 maximum in START DBE, 10-264 maximum in START DBE NEWLOG, 10-277 mode setting, 2-36 priority, 5-26 SET USER TIMEOUT, 10-260 simultaneous with BEGIN WORK, 10-32 statements that must be in the same, 10-32 terminating, 10-32, 10-42, 10-208 TRANSFER OWNERSHIP syntax, 10-289 transferring ownership, 10-289 when dropping authorization group, 10-116 TRUNCATE TABLE syntax, 10-291 truncation and native language data, 7-18 in aggregate functions, 8-11 in expressions , 8-6 of data, 7-5 tuple de ned, 1-
PAGE 659
variables BEGIN DECLARE SECTION, 10-30 END DECLARE SECTION, 10-130 indicator, 2-32 input, 2-32 output, 2-32 verb de ned, 1-9 view names in CREATE VIEW, 10-86 in DELETE, 10-97 in DELETE WHERE CURRENT, 10-100 in DROP TABLE, 10-124 in DROP VIEW, 10-127 in INSERT, 10-162 in REVOKE, 10-201 in TRANSFER OWNERSHIP, 10-289 in UPDATE, 10-292 in UPDATE WHERE CURRENT, 10-299 rules for, 6-1 views and check constraints, 4-5 base tables, 2-8 creating, 2-8, 10-86 de ned, 1-3, 2-8 dropping, 10-124, 10-127 granting authoriti
PAGE 660