900 Series HP 3000 Computer Systems ISQL Reference Manual for ALLBASE/SQL and IMAGE/SQL ABCDE HP Part No. 36216-90096 Printed in U.S.A.
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.
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.
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 ALLBASE/QUERY User's Guide HP PC API User's Guide for ALLBASE/SQL and I
Preface This manual describes ISQL (Interactive Structured Query Language) and how to use it on HP 3000 computers running under the MPE/iX operating system. SQL is the language you use to de ne and maintain data in an ALLBASE/SQL DBEnvironment. ALLBASE/SQL is Hewlett-Packard's proprietary relational database management system. 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.
What's New in this Release The following table highlights the new or changed functionality in this release, 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.
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.
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.
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.
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.
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.
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.
New Features in ALLBASE/SQL Release G.0 (continued) Feature (Category) Description Documented in . . . 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.
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.
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][...
Contents 1. Introduction Purpose of ISQL . . . . ISQL Users . . . . . . Authorization . . . . . Native Language Support Reading More about ISQL . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-1 1-2 1-3 1-3 1-5 2. Getting Started with ISQL Invoking ISQL . . . . . . . . . . . . . . . . . . . . . .
3. Using ISQL for Database Tasks Creating Objects . . . . . . . . . . Controlling Access to Objects . . . . Manipulating Data . . . . . . . . . Using the INSERT Statement . . . Using the INPUT Command . . . . Using the SELECT Statement . . . TID Function . . . . . . . . . Using the DELETE Statement . . . Using the UPDATE Statement . . . Using the INFO Command . . . . . Unloading and Loading Tables . . . . Using the UNLOAD Command . . . External Format . . . . . . . . Internal Format . . . . . . . . .
A. SQL Syntax Summary B.
Figures 1-1. 2-1. 2-2. 2-3. 2-4. 3-1. ISQL and SQLCore Interaction . . . . . . . . . . . . Command Sources . . . . . . . . . . . . . . . . . ISQL Commands Related to Command Files . . . . . . ISQL Commands Related to the Command Bu er . . . ISQL Commands Related to the Command History Bu er Files Used for SELECT Command . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . . 1-1 2-9 2-10 2-13 2-14 3-4 . . . . . . . . . . . . . . . . .
1 Introduction ISQL stands for Interactive Structured Query Language. With ISQL, you can interactively de ne and access data in an ALLBASE/SQL relational database environment. This manual describes the ISQL commands and how to use ISQL to execute SQL and ISQL commands. The following topics are discussed in this chapter: Purpose of ISQL. ISQL Users. Authorization. Native Language Support. Purpose of ISQL You use ISQL to enter SQL commands at a terminal.
ISQL also has its own set of commands which facilitate the interactive use of SQL statements. For example, you can use ISQL commands to quickly change and resubmit previously executed SQL statements. You can also group SQL statements together in a le and execute the le through ISQL. From ISQL, you can use ISQL and SQL commands to do the following: Create and maintain DBEnvironments. Manipulate data. Load and unload data. Install modules.
Authorization Special MPE/iX capabilities are not required to invoke ISQL. To execute database access statements, however, you must have the proper ALLBASE/SQL authorization. The authorization required to execute SQL statements is de ned in the ALLBASE/SQL Reference Manual . The authorization required to execute ISQL commands that access a database is de ned in this manual.
Run the MPE/iX utility program NLUTIL.PUB.SYS to determine which native languages are supported on your system.
Reading More about ISQL Chapter 2 examines how and when the ISQL commands are used. Chapter 3 explains how to use ISQL to change and display data in the DBEnvironment. Chapter 4 describes all the ISQL commands. It de nes their syntax and semantics and provides examples. Appendix A contains a summary of SQL command syntax. Appendix B contains a summary of ISQL command syntax.
2 Getting Started with ISQL This chapter examines how and when ISQL commands are used. The following topics are discussed: Invoking ISQL. Starting a DBE Session. Leaving ISQL. Managing Transactions. Using SQL and ISQL Commands. Using ISQL Files and Bu ers. Getting Help. Setting Command Options. Using Other Programs within ISQL. Handling Errors. Invoking ISQL You invoke ISQL by issuing the following command: : RUN ISQL.PUB.
ISQL interacts with several les each time it is invoked: ISQLPRO|the pro le le. ISQLSYN|the synonym le. SQLCT000|the message le. SQLWL000|the welcome le. The pro le and synonym les are discussed later in this chapter under \Using ISQL Files and Bu ers." The message le contains ISQL and other ALLBASE/SQL messages. The welcome le contains the ISQL banner and prompt to be displayed when ISQL is invoked, as shown above.
Managing Transactions A transaction consists of one or more SQL statements that are grouped together to form a unit of work. For example, if you wanted to transfer money from a savings to a checking account, the withdrawal and the deposit would both occur within the same transaction. A transaction begins with a BEGIN WORK statement and ends with either a COMMIT WORK or a ROLLBACK WORK statement. Either all the statements or none of the statements are executed.
isql=> SAVEPOINT; Savepoint number is 1 Use this number to do ROLLBACK WORK TO 1. isql=> Command;Command . . . ; isql=> ROLLBACK WORK TO 1; isql=> ISQL automatically terminates transactions for you in several instances: ISQL issues a COMMIT WORK statement when the SET AUTOCOMMIT option is ON and you are using the ISQL command INPUT, INSTALL, or LOAD. ISQL issues a COMMIT WORK or ROLLBACK WORK statement when you respond to the exit prompt described earlier in this chapter under \Leaving ISQL.
Prompting Mode ISQL provides a special mode of command entry called prompting mode. Prompting mode is available to you when you submit ISQL commands from a terminal. In prompting mode, you can enter part of an ISQL command, and ISQL prompts you for subsequent options and parameters: isql=> LIST; Option (s[et], f[ile], i[nstall], or h[istory])> File; File name> MyFile; The contents of the named le are displayed.
Special Characters in Commands Table 2-1 lists characters that have special meaning to ISQL. Table 2-1. Special Characters in ISQL Special Character Meaning to ISQL ' Delimiter " Delimiter ; Command terminator & Parameter indicator -- Comment marker /* . . .
In the following example, the ISQL CHANGE command is used to edit a command placed in the command bu er with the HOLD command. The escape character (n) is needed so that ISQL interprets the single quotation marks being inserted as quotation marks, not delimiters. The slashes (/) are delimiters for the CHANGE command. isql=> HOLD SELECT * FROM PurchDB.Parts WHERE PartNumber<1300; isql=> CHANGE /1300/\'1300\'/; SELECT * FROM PurchDB.
Alternatively, you can submit parameter values in the START command itself as shown here: isql=> START MyFile (PurchDB.Parts); UPDATE STATISTICS FOR TABLE &1; Command le processing continues. Within any single command le or command bu er, each parameter number assumes one value. This feature lets you use the same parameter more than once, but you can supply its value only once.
Figure 2-1. Command Sources Entering Interactive Commands from the Terminal You submit one or more commands when you see the input prompt. ISQL processes the commands, then displays the input prompt again. Files Containing Commands A command le is a le that contains one or more commands you want to execute using ISQL. Command les are useful when you want to enter lengthy or repetitive commands.
Figure 2-2 summarizes the ISQL commands related to command le creation, maintenance, and use. Refer to \Command Bu er" and \Command History Bu er" later in this chapter for an explanation of the bu er areas. Figure 2-2. ISQL Commands Related to Command Files You can create command les with the editor on your system. For example: isql=> EDIT LoadDB; TDP/3000 (A.05.03) HP36578 Editor (c) COPYRIGHT Hewlett-Packard Co. 1990 HPSPELL (A.01.
the command le; enter a /* before the comment starts and a */ at the end of the comment. Comments can span lines, and in this case you only need to put the */ at the end of the comment. Do not put information after column 72 in command les if you want ISQL to process it, because ISQL reads only up to 72 bytes per line when executing commands from a command le. You execute command les by using the START command as follows: isql=> START LoadDB; You can include additional START commands in a command le.
/a 1 SET ECHO ON; 2 CONNECT TO 'MYDBE'; 3 // /k ISQLPRO /e isql=> This pro le le contains one ISQL and one SQL statement. The ISQL command sets the SET ECHO option ON. The SQL statement establishes a DBE session. Running ISQL From a Command File You can run ISQL from a command le by specifying the le name as part of the ISQL command invocation. Example: : RUN ISQL.PUB.SYS;STDIN=COMMANDS.SomeGrp.SomeAcct If you specify a command le and if ISQLPRO also exists, the commands in ISQLPRO are executed rst.
The Command Buffer The command bu er is a 32K byte area for storing one or more commands for the duration of an ISQL session. A single command greater than 32K bytes is truncated and is not executed. You put commands into the command bu er by using the ISQL RECALL or HOLD command. You execute commands in the command bu er by using the ISQL START command. Figure 2-3 summarizes the ISQL commands that operate on the command bu er. Figure 2-3.
You can change, store, or execute the contents of the command bu er with the CHANGE, STORE, and START commands, respectively: CHANGE replaces one string with another string, which can be null. You may replace one or all occurrences of the string in the command bu er. STORE keeps the contents of the command bu er in a le. START executes the command(s) in the command bu er. The commands may contain parameters. The Command History Buffer When you invoke ISQL, the command history bu er is empty.
LIST HISTORY displays one or all of the commands in the command history bu er; you can use an @ to indicate that you want to see the entire command history bu er. For example: isql=> LIST HISTORY @; 1 2 3 4 5 6 7 8 9 10 SELECT * FROM PurchDB.Parts; SELECT * FROM PurchDB.Vendors; SELECT * FROM PurchDB.PartInfo; CONNECT TO 'PartsDBE.SomeGrp.SomeAcct'; isql=> RECALL HISTORY puts one command from the command history bu er into the command bu er and displays it.
When you submit an ISQL command in prompting mode, as in the following example, only the part of the command rst submitted is stored in the command history bu er: isql=> LOAD FROM INTERNAL; Input file name> InFile; 'InFile' cannot be found. isql=> RECALL HISTORY 1; (DBERR 17) LOAD FROM INTERNAL; isql=> START; Input file name> IntFile; Table name> PurchDB.
To invoke a synonym that contains embedded blanks, enclose it in double quotation marks. For example, if a CONNECT statement is de ned in your synonym le as \OPEN DB", invoke it as follows: isql=> "OPEN DB"; Getting Help ISQL has a HELP command you can use to obtain information about the function and syntax of any ISQL command or SQL statement.
Setting Command Options Various options are controlled with the SET command. The SET options have initial settings, which you can change for the duration of an ISQL session with the SET command. To determine the current value of a SET option, you use the LIST SET command.
Table 2-2. SET Command Options Category Data loading; application program support Related Command SET Option E ect INPUT LOAD INSTALL AUTOCOMMIT Determines whether ISQL issues COMMIT WORK automatically. LOAD AUTOLOCK Determines whether tables are locked when loaded. INPUT LOAD AUTOSAVE Determines how often automatic COMMIT WORK is performed. LOAD CONVERT Determines whether data will be converted during the load. LOAD UNLOAD LOAD_BUFFER Determines the size of the load/unload bu er.
Table 2-2. SET Command Options (continued) Category Query results Related Command SET Option E ect FRACTION Determines how many decimal digits are displayed. NULL Determines the character displayed when a value is null. OUTPUT Determines the le to which the query result is sent. PAGEWIDTH Determines the maximum row length in the query result. CONTINUE Determines whether processing is to be continued if an error is encountered.
Using Other Programs within ISQL Using the System Editor You can invoke any editor on your system from ISQL. Use the ISQL SET command with the EDITOR option to de ne the editor you want to use. For example: isql=> SET EDITOR TDP.PUB.SYS; isql=> EDIT; For complete information on using the SET command, refer to chapter 4, \ISQL Commands." Using System Commands and Programs You can execute operating system commands by using the SYSTEM command.
Using SQLGEN SQLGEN is a program that allows you to re-create a given DBEnvironment. SQLGEN commands cannot be issued directly from ISQL. You must rst invoke SQLGEN from ISQL by using the SQLGEN command as follows: isql=> SQLGEN; The SQLGEN banner and prompt (>>) are displayed. You can then execute SQLGEN commands until you enter an EXIT or QUIT command. For complete information on using SQLGEN, refer to the \DBA Tasks and Tools" chapter of the ALLBASE/SQL Database Administration Guide .
UPDATE STATISTICS FOR TABLE PurchDB.Parts; isql=> START; isql=> When you are submitting commands from a command le, the CONTINUE option of the SET command determines what ISQL does when it encounters an erroneous command. If CONTINUE is ON, ISQL continues processing any subsequent commands in the command le. If CONTINUE is OFF, ISQL discontinues processing commands from the le and displays the input prompt.
Table 2-3. JCWs Set by ISQL JCW Name 1 Contents Range ISQLERR number of SQL errors encountered 0 - 32767 ISQLWARN number of SQL warnings encountered 0 - 32767 ISQLLASTERR last DBERR number encountered 0 - 32767 ISQLLASTWARN last DBWARN number encountered 0 - 32767 JCW1 FATAL if SQL error encountered 0 if SQL error not encountered FATAL or 0 The system JCW is set only if the EXIT ON DBERR option of the SET command is ON.
3 Using ISQL for Database Tasks This chapter explains how to use ISQL to change and display data in the DBEnvironment. The following topics are discussed: Creating Objects. Controlling Access to Objects. Manipulating Data. Unloading and Loading Tables. Using Modules. Creating Objects You can use ISQL to create database objects such as tables, views, and indexes.
DROP DROP DROP DROP DROP DROP DROP DROP DBEFILE DBEFILESET TABLE VIEW INDEX GROUP MODULE TEMPSPACE Refer to the \SQL Statements" chapter of the ALLBASE/SQL Reference Manual for complete information on using these DROP statements. For complete information on con guring a new DBEnvironment, refer to the chapters on \DBEnvironment Con guration and Security" and \Database Creation and Security" in the ALLBASE/SQL Database Administration Guide .
Manipulating Data You can use ISQL to manipulate and access the data in your database by using the following SQL and ISQL commands: An SQL statement. An ISQL command. An SQL statement. An SQL statement. An SQL statement. An ISQL command. The SQL statements in this list are collectively referred to as data manipulation language (DML) statements. INPUT and INFO are ISQL commands which support the data manipulation function. A brief description of each command is presented in the next sections.
AUTOSAVE option of the SET command has been entered and when you terminate the INPUT command. You can specify the COMMIT WORK option at anytime during INPUT command processing to commit rows entered since the last COMMIT WORK was processed. For example: 4> ('CharValue',NumericValue); 5> COMMIT WORK; 6> You can specify an option called ROLLBACK WORK to back out rows entered since the last COMMIT WORK was processed.
The FRACTION option determines the number of decimal digits displayed in the query result for data of types FLOAT, REAL, and DECIMAL. The value of this option is initially 2. The NULL option determines the character that ISQL displays when a null value occurs. The value of this option is initially a blank. The PAGEWIDTH option determines the maximum length of each row ISQL puts into the output le. The initial setting of this option is its maximum value: 32767 bytes.
LEFT and RIGHT scroll the display left and right, respectively, by 40 screen columns at a time. PRINT n send n copies of the query result to the system printer; the default is one copy (n equals 1). To specify a di erent printer, issue a le equation such as the following: : FILE ISQLLP;DEV=PP The b response, shown in the previous example, displays the last group of rows in the query result as shown below. SELECT PARTNUMBER,UNITPRICE,DELIVERYDAYS FROM PURCHDB.
isql=> EDIT; /t KEEPFILE /l all 1 SELECT PARTNUMBER,UNITPRICE,DELIVERYDAYS FROM PURCHDB.SUPPLYPRICE; 2 ----------------+--------------+-----------3 PARTNUMBER |UNITPRICE |DELIVERYDAYS 4 ----------------+--------------+-----------5 1123-P-01 | 450| 30 | 525| 15 .. 6 1123-P-01 . /e isql=> TID Function Each row (tuple) in an ALLBASE/SQL table is stored at a database address on disk. This unique address is called the tuple identi er or TID.
A TID consists of eight bytes of binary data and has the following format: Table 3-1. SQLTID Data Internal Format Content Byte Range Version Number 1 through 2 File Number 3 through 4 Page Number 5 through 7 Slot Number 8 ISQL parses and displays the TID as three elds separated by colons. The version number is not displayed. In the following example, the le number is 1, the page number is 2, and the slot number is 133.
isql=> INFO PurchDB.Parts; Nulls Case Column Name Data Type (length) Allowed Language Sensitive ----------------------------------------------------------------------PARTNUMBER Char ( 16) NO NATIVE-3000 YES PARTNAME Char ( 30) YES NATIVE-3000 YES SALESPRICE Decimal ( 10, 2) YES isql=> Unloading and Loading Tables ISQL provides the following commands for loading and unloading data: UNLOAD copies data from tables into a le. LOAD provides higher-volume data loading than the INPUT or INSERT.
You can use the UNLOAD command with the EXTERNAL option to unload LONG column values. For LONG columns, the data is placed in the output le or device that was speci ed at the time the data was inserted into the table (or at the time the column was last updated). The output le or device name is placed in the external le. Note You can unload data from a table in the following ways: With the prompting mode of the UNLOAD command. Directly from the isql prompt. By using a command le.
To execute the command le, use the START command. For example, if the le is named MyFile, issue the following command: isql=> START MyFile; The description le is automatically created and contains a record for each column unloaded. The record describes where and in what format the data exists in the external le. You can use your editor to view this le.
isql=> UNLOAD; Output file format (i[nternal] or e[xternal])> INTERNAL; Output file name> IntFile; TableName or "SelectCommand"> PurchDB.Vendors; Command in progress. Number of rows processed is 15 isql=> You cannot use the INTERNAL option to unload LONG column data. Using the LOAD Command You load data stored in a le into a table by using the LOAD command. The le is a data le in external or internal format.
isql=> EDIT; The editor named in the EDITOR option of the SET command is invoked. / a 1 2 3 1145 1167 260097 . . . /k EParts2 Abacus Vacuum tube Analytic engine 15.50 7.95 9000.50 isql=> String data does not need to be enclosed in quotation marks. You do not need to align numeric values within a column, and you can extend beyond column 72.
In the example above, you can only load data from records that contain a speci c character string (Select eld pattern) beginning at a speci c location (Starting location of select eld) in the input records. In this example, only parts having numbers that start with 1 are loaded. You can also specify only a range of records using the PARTIAL option. Refer to the LOAD command in chapter 4, \ISQL Commands," for a description of the PARTIAL option.
isql=> LOAD; File format (i[nternal] or e[xternal])> INTERNAL; Input file name> IntFile; Table name> PurchDB.Vendors; Number of rows read is 15 Number of rows processed is 15 COMMIT WORK to save to DBEnvironment. isql=> Setting the AUTOCOMMIT Option Modi ed or newly loaded data is written to the log bu er. When a COMMIT WORK is processed, the data is moved from the log bu er to the log le and the changes to the DBEnvironment are made permanent.
To store the installable module in the new DBEnvironment, use the ISQL INSTALL command: isql=> INSTALL; File name> SQLMOD.COBOL.HPSQL; Name of module in this file: PGMR1@COBOL.PROGRAM1 Number of sections installed: 6 COMMIT WORK to save to DBEnvironment. isql=> You can use the ISQL LIST INSTALL command to identify the names of the modules contained in an installable module le. For example: isql=> LIST INSTALL; File name> SQLMOD.COBOL.HPSQL; Name of module in this file: PGMR1@COBOL.
4 ISQL Commands This chapter describes all the ISQL commands, giving syntax and examples for each and authorization requirements for commands that result in database access. The commands are presented in alphabetical order; those related to query results are de ned under \SELECTSTATEMENT." The size of commands submitted at a terminal or from an ISQL command le is unlimited. Commands submitted through the ISQL command bu er cannot exceed 32K bytes; the LOAD and UNLOAD size is unlimited.
Table 4-1. ISQL Command Summary (continued) Group Command support Category Query results Command Use UP Display previous rows in a query result. DOWN Display next rows in a query result. TOP Display the group of selected rows of a query result. BOTTOM Display the last group of selected rows of a query result. LEFT Display previous screen columns in a query result. RIGHT Display next screen columns in a query result. PRINT Print a query result.
Table 4-1. ISQL Command Summary (continued) Group Category Command Command Use RECALL CURRENT Display current contents of the command bu er. RECALL FILE Displays a le and stores its contents in the command bu er. RECALL HISTORY Place a command from the command history bu er into the command bu er and display it. START Execute command(s) in the command bu er or a le. STORE Save the contents of the command bu er in a le. DO Execute a command in the command history bu er.
CHANGE The CHANGE command modi es the rst occurrence or all occurrences of a string in the command bu er. Scope ISQL only. ISQL Syntax 2 3 2 C HANGE Delimiter OldString Delimiter NewString Delimiter @ 3 Parameters Delimiter OldString NewString @ is a one-byte nonblank character delimiting OldString and NewString . is the string in the command bu er to be replaced. Its maximum length is 32K bytes. is the string to replace the OldString . Its length does not have to equal that of OldString .
CHANGE Example isql=> RECALL CURRENT; SELECT Name,Owner FROM System.Table WHERE Type = '0'; isql=> CHANGE |\'||@; When nothing is inserted for the new string, the old string is deleted. SELECT Name,Owner FROM System.Table WHERE Type = 0; isql=> CHANGE |Owner|Nrows,Avglen|; SELECT Name,Nrows,Avglen FROM System.Table WHERE Type = 0; isql=> CHANGE |,Avglen||; SELECT Name,Nrows FROM System.
DO The DO command lets you execute any of the commands in the command history bu er. Scope ISQL only. ISQL Syntax CommandNumber DO CommandString Parameters CommandNumber CommandString identi es one of the commands in the command history bu er. The command history bu er holds the fty most recently submitted commands, numbered 1 through 50. The CommandNumber of the most recently submitted command is 1.
DO The "INFO PurchDB.Parts" command is executed again because it is the most recent command in the command history bu er. isql=> DO ED; The "EDIT MyFile" command is executed because it is the most recent command in the command history bu er that begins with the string "ED".
EDIT The EDIT command invokes the editor named in the SET EDITOR option for creating or updating a le. Scope ISQL Only ISQL Syntax 2 ED IT 3 Example isql=> EDIT; HP32201A.07.17 EDIT/3000 WED, JUL 15, 1987, 10:19 AM (C) HEWLETT-PACKARD CO. 1985 /a 1 UPDATE STATISTICS FOR TABLE PurchDB.SupplyPrice; 2 UPDATE STATISTICS FOR TABLE PurchDB.Orders; 3 \\ /k Daily.SQL /e isql=> SET EDITOR TDP.PUB.SYS; isql=> EDIT; TDP/3000 (A.04.00) HP36578 Editor (c) COPYRIGHT Hewlett-Packard Co. 1985 HPSPELL (A.01.
END END The END command terminates an ISQL session and returns you to MPE/iX. Scope ISQL only. ISQL Syntax 2 EN D 3 Description If you have a DBEnvironment transaction in progress when you issue the END command, ISQL prompts you as follows: A transaction is in progress. Commit work (Y/N)> Enter N to roll back any DBEnvironment change(s) you made since signing on or since the latest COMMIT WORK. ISQL automatically processes a ROLLBACK WORK statement, and your changes are undone.
ERASE The ERASE command deletes a le. Scope ISQL only. ISQL Syntax 2 3 ER ASE FileName Parameters FileName identi es the le to be erased. Name quali cation follows MPE/iX conventions: FileName[/Lockword][.Group[.
EXIT EXIT The EXIT command terminates an ISQL session and returns you to MPE/iX. Scope ISQL only. ISQL Syntax 2 EX IT 3 Description If you have a DBEnvironment transaction in progress when you issue the EXIT command, ISQL prompts you as follows: A transaction is in progress. Commit work (Y/N)> Enter N to roll back any DBEnvironment change(s) you made since signing on or since the latest COMMIT WORK. ISQL automatically processes a ROLLBACK WORK statement, and your changes are undone.
EXTRACT The EXTRACT command copies modules from a DBEnvironment into an installable module le. Scope ISQL only. ISQL Syntax 2 3 2 8 , < MODULE 2Owner. ModuleName 3 . .. 3 2 SECTION Owner. ModuleName(SectionNumber) , . . . ALL MODULES 2 3 NO SETOPTINFO INTO FileName EXTRACT : 9 3= ; Parameters [Owner.]ModuleName SectionNumber FileName identi es the module containing sections to be extracted. speci es the section contained in the module. identi es the le containing the installable modules.
HELP HELP The HELP command displays information about ISQL commands and SQL statements. Scope ISQL only. ISQL Syntax 2 HE LP 8 @ 3< : SQLstatement ISQLcommand 33 92 2 = D2 ESCRIPTION 3 4 S YNTAX 5 2 3 ; E AMPLE Parameters @ SQLstatement ISQLcommand DESCRIPTION SYNTAX EXAMPLE is speci ed to obtain a list of the SQL statements and a list of the ISQL commands. is any unabbreviated SQL statement. This consists of the SQL verb and the command keywords that follow. is any unabbreviated ISQL command.
HELP ===== SQL Statements ===== ADD DBEFILE ADD TO GROUP *ADVANCE ALTER DBEFILE ALTER TABLE *BEGIN BEGIN ARCHIVE *BEGIN DECLARE SECTION BEGIN WORK CHECKPOINT *CLOSE COMMIT ARCHIVE COMMIT WORK CONNECT CREATE DBEFILE CREATE DBEFILESET CREATE GROUP CREATE INDEX CREATE PARTITION CREATE PROCEDURE CREATE RULE CREATE SCHEMA CREATE TABLE CREATE TEMPSPACE CREATE VIEW *DECLARE *DECLARE CURSOR DELETE *DELETE WHERE CURRENT *DESCRIBE DISABLE AUDIT LOGGING DISABLE RULES DISCONNECT DROP DBEFILE DROP DBEFILESET DROP GROUP
HELP ===== ISQL Commands ===== CHANGE DO EDIT END ERASE EXIT EXTRACT GENPLAN HELP HOLD INFO INPUT INSTALL LIST FILE LIST HISTORY LIST INSTALL LIST SET LOAD RECALL REDO RENAME SET SQLGEN SQLUTIL START STORE SYSTEM UNLOAD isql=> help help; SCOPE: ISQL Only The HELP command displays information about ISQL or SQL commands.
HELP COMMAND [OPTION [parameter] [...] ] Horizontal ellipses enclosed in brackets indicate that you can repeatedly select the element(s) that appear within the immediately preceding brackets or braces. In the following example, you can select parameter zero or more times. [parameter] [...] |...| Horizontal ellipses enclosed in vertical bars indicate that you can select more than one element within the immediately preceding brackets or braces. However, each particular element can only be selected once.
HELP isql=> help do; SCOPE: ISQL Only The DO command lets you execute any of the commands in the command history buffer. ========== SYNTAX ========== DO [CommandNumber] [CommandString] =========== EXAMPLE =========== DO ; DO 2 ; isql=> help add dbefile example; =========== EXAMPLE =========== ADD DBEFILE ThisDBEFile TO DBEFILESET Miscellaneous ; isql=> help add dbefile description; SCOPE: ISQL or Application Programs The ADD DBEFILE statement associates a DBEFile with a DBEFileSet.
HOLD The HOLD command puts SQL and ISQL commands into the command bu er, overwriting the current contents of the command bu er. Scope ISQL only. ISQL Syntax 2 HO LD 3 SQLStatement ISQLCommand EscapeCharacter; SQLStatement ISQLCommand 2 . .. 3 Parameters SQLStatement ISQLCommand EscapeCharacter is any SQL statement, which may contain one or more parameters. is any ISQL command, which may contain one or more parameters.
HOLD When scanning a HOLD command, ISQL deletes any escape character and treats the character following an escape character as a simple character. Therefore, if an escape character is to appear in the command bu er, you must use three escape characters instead of one (that is, the escape character and the following character must each be escaped). The escape character and semicolon are still required between commands when multiple commands are placed in the command bu er.
INFO The INFO command displays de nitions of the columns of a table or view. Scope ISQL only. ISQL Syntax IN 2 3 2 3 Owner. 3TableName 2 FO Owner. ViewName Parameters [Owner .]TableName [Owner .]ViewName identi es a table in the DBEnvironment you are using. identi es a view in the DBEnvironment you are using. Description ISQL responds with the name of the language of the table and a display of its column de nition.
INFO Authorization You must have authorization to select from the named table or view. Refer to the discussion of the SELECT statement in the ALLBASE/SQL Reference Manual . Example isql=> INFO System.
INPUT The INPUT command inserts rows into an existing table. Scope ISQL only. ISQL Syntax 2 INP UT 3 3 2 32 2 Owner. 3TableName 2 (ColumnName ,ColumnName 2 Owner. ViewName (Value ,Value 32 ... 3 ROLLBACK WORK ) COMMIT WORK 2 . .. 3 2 E ND 3 ... ) 3 Parameters [Owner .]TableName [Owner .]ViewName ColumnName The new rows are inserted into the table on which the view is based. identi es the column(s) for which you will specify values.
INPUT terminates the INPUT command. If AUTOCOMMIT is on, ISQL issues a COMMIT WORK, and the following message appears: END Number of rows processed is n DBEnvironment has been updated. If AUTOCOMMIT is o , you must explicitly save any unsaved input, as indicated by the following message that appears after you enter END: Number of rows processed is n COMMIT WORK to save to DBEnvironment. Description You must establish a DBE session with the CONNECT or START DBE statement before using the INPUT command.
INPUT Character data that contains a single quote must double the single quote. For example: isql=> INPUT PurchDB.Parts (PartNumber,PartName,SalesPrice) > ('2002-DA-01', 'Joe''s Plotter', 500.00) > END; Number of rows processed is 1 COMMIT WORK to save to DBEnvironment. isql=> Authorization You must have authorization to insert a row into the named table or into the table on which the named view is derived. Refer to the discussion of the INSERT statement in the ALLBASE/SQL Reference Manual .
INSTALL INSTALL The INSTALL command stores the modules contained in an installable module le into a DBEnvironment. Scope ISQL only. ISQL Syntax 2 3 2 IN STALL FileName DROP 32 IN DBEFileSetName 32 NO OPTINFO 3 Parameters FileName DROP IN DBEFileSetName NO OPTINFO identi es the le containing the installable module. When rst created by an ALLBASE/SQL preprocessor, the le is named SQLMOD. Name quali cation follows MPE/iX conventions: FileName[/Lockword][.Group[.
INSTALL Before installing a module, ALLBASE/SQL determines the validity of each section in the module based on current DBEnvironment objects and authorization. A section is marked invalid, but stored, if it is for a statement that accesses an object that does not exist or that the module owner is not authorized to execute. You can use the VALIDATE statement to revalidate invalid sections or you can let ALLBASE/SQL revalidate sections when you run the program. If a module by the same OwnerName.
LIST FILE LIST FILE The LIST FILE command displays the contents of a currently accessible le. Scope ISQL only. ISQL Syntax 2 3 2 3 LI ST F ILE FileName Parameters FileName identi es the le to be displayed, most likely a command le. Name quali cation follows MPE/iX conventions: FileName[/Lockword][.Group[.
LIST HISTORY The LIST HISTORY command displays one or all commands in the command history bu er. Scope ISQL only. ISQL Syntax 2 3 2 LI ST H ISTORY 3 CommandNumber @ Parameters CommandNumber @ is a number in the range 1 through 50, where 1 identi es the most recently submitted ISQL or SQL command in the ISQL session. refers to the entire command history bu er.
LIST INSTALL LIST INSTALL The LIST INSTALL command displays the names of modules in an installable module le. Scope ISQL only. ISQL Syntax 2 3 2 3 LI ST I NSTALL FileName Parameters FileName identi es the le containing the installable module. Name quali cation follows MPE/iX conventions: FileName[/Lockword][.Group[.Account]] Description In prompting mode, ISQL prompts you for a le name as shown here: isql=> LIST; Option (s[et], f[ile], i[nstall], or h[istory])> INSTALL; File name> SQLMOD.COBOL.
LIST SET The LIST SET command displays any or all of the current SET option values. Scope ISQL only. ISQL Syntax 2 3 2 LI ST S ET 3 Option @ Parameters Option names one of the following SET command options: AUTOC[OMMIT] AUTOL[OCK] AUTOS[AVE] C[ONTINUE] CONV[ERT] EC[HO] ECHO_[ALL] ED[ITOR] ES[CAPE] EXIT[_ON_DBERR] EXIT_ON_DBWARN FL[AGGER] F[RACTION] N[ULL] LOAD_B[UFFER] LOAD_E[CHO] OU[TPUT] OW[NER] PA[GEWIDTH] PR[OMPT] Refer to the SET command for a complete explanation of each option.
LIST SET Description In prompting mode, ISQL prompts you for an option: isql=> LIST; Option (s[et], f[ile], i[nstall], Valid set options are: autoc[ommit] autol[ock] conv[ert] ec[ho] es[cape] exit[_on_dberr] f[raction] load_b[uffer] ou[tput] ow[ner] Set option or @> @; or h[istory])> SET; autos[ave] echo_[all] exit_on_dbwarn load_e[cho] pa[gewidth] cont[inue] ed[itor] fl[agger] n[ull] pr[ompt] Example isql=> LIST SET ECHO; ec[ho] - OFF isql=> LIST SET @; autoc[ommit] autol[ock] autos[ave] c[ontinue]
LOAD The LOAD command inserts rows into a table from a le. Scope ISQL only. ISQL Syntax 2 3 3 2 E XTERNAL 2 3 InputFileName AT StartingRow LO AD P ARTIAL FROM I NTERNAL 3 2 3 2 Owner. TableName ExternalInputSpec 3 FOR NumberOfRows TO 2 Owner. ViewName USING DescriptionFileName 3 2 Y2 ES3 PatternLocation Pattern N O 2 32 2 33 Parameters PARTIAL EXTERNAL INTERNAL InputFileName StartingRow NumberOfRows [Owner .]TableName [Owner .
LOAD ExternalInputSpec describes how ISQL should read an EXTERNAL le: 2 3 ColumnName StartingLocation Length NullIndicator 3 2 FormatType 2 3 2 3 . . . E ND ColumnName identi es the column into which the data at StartingLocation is to be inserted. You must provide data for any column that is not null . StartingLoca- is the column (byte position) in each external le line at which the data for ColumnName starts. tion The rst column in a line is 1.
LOAD DescriptionFileName identi es a description le. Name quali cation follows the same conventions used for the output le. The description le contains one line for each column in the table. The rst line describes the rst column in the table, the second line describes the second column in the table, and so on. Each line contains seven elds. Data in all the elds except the column name eld are right-justi ed; the column name eld is left justi ed. Column(s) Contents 1-20 Column name.
LOAD indicates that you want to load only rows that meet speci c criteria. identi es the input line column at which a Pattern starts. is a character string that constitutes the criteria for loading. Only rows that have the Pattern starting at PatternLocation are loaded. indicates that you want to load all the rows in the external le. YES PatternLocation Pattern NO Description You must establish a DBE session with the CONNECT or the START DBE command before using the LOAD command.
LOAD To improve performance, issue the following SET commands prior to the load operation: SET LOAD_BUFFER to enlarge the load bu er beyond the default size of 16,384 bytes. SET AUTOLOCK ON to lock the table in exclusive mode when the load is performed. SET AUTOCOMMIT ON and SET AUTOSAVE to automatically commit the number of rows speci ed by autosave if the load bu er is full. Should the load operation subsequently fail, you can insert the remaining rows with the LOAD PARTIAL command.
LOAD When loading from an external le created by using the UNLOAD command, specify a StartingLocation after the 10-byte pre x if the le contains VARCHAR data. The 10-byte pre x, written by the UNLOAD command using the EXTERNAL option, contains the actual length of the VARCHAR data item.
LOAD Valid IBM Mainframe Format Types ExternalInputSpec FormatType Target ALLBASE/SQL Column IBM Mainframe Format Type 1 INTEGER or SMALLINT INTEGER PACKED DECIMAL ZONED DECIMAL1 ASCII EBCDIC INTEGER PACKED Scale ZONED Scale CHAR CHAR DECIMAL PACKED DECIMAL ZONED DECIMAL1 ASCII EBCDIC PACKED Scale ZONED Scale CHAR CHAR CHAR or VARCHAR ASCII EBCDIC none none BINARY or VARBINARY BINARY none A ZONED DECIMAL number may have either a leading or trailing sign.
LOAD isql=> SET AUTOCOMMIT ON; isql=> SET AUTOSAVE 40; isql=> LOAD FROM EXTERNAL Price TO PurchDB.SupplyPrice; Enter information as requested for each column to be loaded. Enter END for the column name when finished.
LOAD isql=> SET AUTOSAVE 50 isql=> LOAD FROM EXTERNAL EParts > TO PurchDB.Parts > PartNumber 1 16 > PartName 17 30 ? > SalesPrice 47 7 ? > END > N; > Command in progress Number of rows read is 25 Number of rows processed is 25 Number of rows read is 50 Number of rows processed is 50 DBEnvironment has been updated. Number of rows read is 75 Number of rows processed is 75 Number of rows read is 78 Number of rows processed is 78 DBEnvironment has been updated.
LOAD Load from the external le Ecustomer, which was created on an IBM mainframe, into the Customer table. The character data in the external le is in EBCDIC format.
RECALL The RECALL command displays the current contents of the command bu er (up to 32K bytes). Optionally, it places a command from the command history bu er or the contents of a le into the command bu er and displays it. Scope ISQL only. ISQL Syntax 2 REC ALL 3 8 2 URRENT C < 3 2 3 9 = F ILE FileName 3 ; H ISTORY CommandNumber : 2 Parameters CURRENT displays the current contents of the command bu er (including comments).
RECALL If RECALL HISTORY 1 is invoked right after a multiple-command command le is executed, the command displayed is the last SQL or ISQL command processed, not all the commands in the command le.
REDO The REDO command lets you edit any of the commands in the command history bu er and optionally execute the edited command. The original command in the command history bu er is left unchanged. Scope ISQL only. ISQL Syntax 2 RED O 3 CommandNumber CommandString Parameters CommandNumber CommandString identi es one of the commands in the command history bu er. The command history bu er holds the fty most recently submitted commands, numbered 1 through 50.
REDO inserts one or more characters starting at the location of the I. You can delete, then insert, characters by using one or more Ds followed by an I. L lists the complete command as it is currently edited, then redisplays the current edit line. R replaces one or more characters in the line being edited. Characters are replaced beginning with the character above the R one for one with any characters you enter following the R.
REDO Example isql=> SELECT PartName, SalesPrice > FROM PurchDB.Prts > WHERE PartName IS NUL; SELECT PartName, SalesPrice FROM PurchDB.Prts WHERE PartName IS NUL; | Syntax error. (DBERR 1001) isql=> REDO; SELECT PartName, SalesPrice FROM PurchDB.Prts WHERE PartName IS NUL; B SalesPrice FROM PurchDB.Prts WHERE PartName IS NUL; -1 SELECT PartName, IPartNumber, SELECT PartName, PartNumber, + SalesPrice FROM PurchDB.Prts WHERE PartName IS NUL; a SalesPrice FROM PurchDB.
REDO PartName IS NULL; 4 Return 5 The command is executed.
RENAME The RENAME command changes the name of a le. Scope ISQL only. ISQL Syntax 2 3 REN AME OldFileName NewFileName Parameters OldFileName NewFileName identi es the le to be renamed. Name quali cation follows MPE/iX conventions: FileName[/Lockword][.Group[.Account]] is the new name. File name quali cation follows the conventions given for OldFileName .
SELECTSTATEMENT SELECTSTATEMENT When you enter the SQL SELECT statement through ISQL, the query result is displayed. You can scroll through the query result at the terminal and optionally print it. Scope ISQL only. ISQL Syntax 2 2 3 32 SelectStatement; PA USE ; BrowseOption; 32 3 2 . . . E ND 3 Parameters SelectStatement PAUSE is an SQL SELECT statement. Refer to the ALLBASE/SQL Reference Manual for additional information on the SELECT statement.
SELECTSTATEMENT scrolls forward by half the number of displayed rows, along with the previous rows, and preserves the column headings. (Pressing 4Return5 has the same e ect as pressing D.) L[EFT] scrolls the display left 40 screen columns. R[IGHT] scrolls the display right 40 screen columns. T[OP] displays the rst group of rows and the column headings. B[OTTOM] displays the last group of rows and the column headings. N[EXT] displays the next group of rows and the column headings.
SELECTSTATEMENT Column headings are truncated. CHAR or VARCHAR data is truncated after the PAGEWIDTH column. Numeric data is not displayed or printed unless all the data ts. The following SET command options also a ect the query result: FRACTION determines the number of decimal digits displayed for FLOAT and DECIMAL data. NULL determines the character ISQL displays to signify a null value. OUTPUT identi es the le to which the query result is sent.
SELECTSTATEMENT Example isql=> SELECT * FROM System.Table; SELECT * from System.
SET SET The SET command de nes a number of ISQL environment options. Scope ISQL only. ISQL Syntax 2 3 SE T Option OptionValue Parameters Option OptionValue AUTOC[OMMIT] ON AUTOC[OMMIT] OFF AUTOL[OCK] ON AUTOL[OCK] OFF AUTOS[AVE] NumberOfRows identi es the option and a value for it. The available options and values are listed here: Initial setting is OFF.
SET C[ONTINUE] ON C[ONTINUE] OFF CONV[ERT] ASCII CONV[ERT] EBCDIC CONV[ERT] OFF EC[HO] ON EC[HO] OFF Initial setting is ON. If ON, ISQL continues processing commands from a command le after encountering an error. If OFF, ISQL terminates command le processing after encountering an error. If ASCII, the LOAD command converts the data in an external le from IBM mainframe format to an ALLBASE/SQL data type. Character data in the external le is in ASCII format.
SET [StandardName ] Initial setting is blank, indicating that SQL statements are not checked for standards compliance. F[RACTION] Length ISQL displays a warning for each SQL statement that does not conform to the StandardName speci ed. The StandardName option is not case sensitive. To turn o the agger, issue this command without the StandardName option. See the \Standards Flagging Support" appendix in the ALLBASE/SQL Reference Manual for more information on ALLBASE/SQL standards compliance.
SET OW[NER] OwnerName Initial setting is user's DBEUserID name. PA[GEWIDTH] PageWidth This command temporarily resets the default owner name. It does not change the ownership of existing objects in the DBEnvironment nor does it change any authorities in the DBEnvironment. The new owner name must be 20 bytes or fewer in length. Setting the owner name eliminates the need to fully qualify table and view names in SQL statements. To reset the owner name to the login name, type SET OWNER with no owner name.
SET Example isql=> LIST SET @; autoc[ommit] autol[ock] autos[ave] c[ontinue] conv[ert] ec[ho] echo_[all] ed[itor] es[cape] exit[_on_dberr] exit_on_dbwarn fl[agger] f[raction] n[ull] ou[tput] ow[ner] load_b[uffer] pa[gewidth] pr[ompt] - OFF OFF 5 ON OFF OFF OFF EDITOR.PUB.SYS \ OFF OFF 2 ISQLOUT LINDA@HPSQL 16384 32767 isql=> isql=> SET EDITOR TDP.PUB.
SQLGEN The SQLGEN command invokes SQLGEN. Scope ISQL only. ISQL Syntax 2 SQLG EN 3 Description Once you submit the SQLGEN command, you can submit only SQLGEN commands until you enter EXIT. SQLGEN uses the editor speci ed by the SET EDITOR setting. Refer to the ALLBASE/SQL Database Administration Guide for information on SQLGEN commands. Example isql=> SQLGEN; MON, JAN 20, 1992 2:12 PM HP36216-02A.F0.00 SQL Command Generator HP SQL/XL (C)COPYRIGHT HEWLETT-PACKARD CO.
SQLUTIL SQLUTIL The SQLUTIL command invokes SQLUtil. Scope ISQL only. ISQL Syntax 2 SQLU TIL 3 Description Once you submit the SQLUTIL command, you can submit only SQLUtil commands until you enter EXIT or QUIT. Refer to the ALLBASE/SQL Database Administration Guide for information on SQLUtil commands. Example isql=> SQLUTIL; MON, JAN 20, 1991 3:19 PM HP36216-02A.F0.00 DBE Utility/3000 HP SQL/XL (C)COPYRIGHT HEWLETT-PACKARD CO. 1982,1983,1984,1985,1986,1987,1988, 1989,1990,1991,1992.
START The START command executes one or more SQL or ISQL commands from the command bu er or a command le. The command(s) may contain parameters to which you assign values at execution time. Scope ISQL only. ISQL Syntax 2 STA RT 32 32 2 CommandFileName (Value ,Value 32 3 3 ... ) Parameters CommandFileName identi es a command le. Name quali cation follows MPE XL conventions: FileName[/Lockword][.Group[.
START The following summarizes how parameter values you pass in the START command are interpreted by ISQL when a parameter is de ned within single quotation marks (INFO '&1') or without them (INFO &1): HOW VALUE IS PASSED START START START START START ( purchdb.parts ) ( 'purchdb.parts' ) ("\'purchdb.parts\'") ('\'purchdb.parts\'') ('\"purchdb.parts\"') HOW VALUE IS INTERPRETED &1 '&1' purchdb.parts 'purchdb.parts' purchdb.parts 'purchdb.parts' 'purchdb.parts' ''purchdb.parts'' 'purchdb.parts' ''purchdb.
START Authorization You must have the corresponding authority for each SQL statement and ISQL command contained in the bu er or command le. Example isql=> EDIT; /a 1 /*This file updates statistics of table &1 2 and does several SELECT operations.*/ 3 UPDATE STATISTICS FOR TABLE &1; 4 SELECT &2 FROM &1; 5 PRINT; 6 END; 7 SELECT &2 FROM &1 WHERE &3; 8 PRINT; 9 END; 10 COMMIT WORK; 11 // ...
START PRINT; Number of rows selected is 20. END; SELECT &2 FROM &1 WHERE &3; First group of data rows of query result are displayed. PRINT; Number of rows selected is 20. END; COMMIT WORK; isql=> SET ECHO OFF; SET CONTINUE OFF; These SET options a ect command le processing as follows: Only error messages are displayed. Processing terminates if an error is encountered. isql=> START MyFile (PurchDB.Parts,*,SalesPrice> 1000); UPDATE STATSTICS FOR TABLE PurchDB.Parts; | Unexpected keyword.
STORE The STORE command saves the contents of the command bu er in a command le. Scope ISQL only. ISQL Syntax 2 3 2 2 STO RE FileName R EPLACE 33 Parameters FileName REPLACE identi es the le that will contain the contents of the command bu er. Name quali cation follows MPE/iX naming conventions. FileName[/Lockword][.Group[.Account]] overwrites any existing le with the same name as FileName .
STORE Example isql=> RECALL FILE Monthly; UPDATE STATISTICS FOR TABLE PurchDB.OrderItems; ...; UP DATE STATISTICS FOR TABLE PurchDB.Inventory; isql=> CHANGE /Inventory/Orders/; UPDATE STATISTICS FOR TABLE PurchDB.OrderItems; ...; UP DATE STATISTICS FOR TABLE PurchDB.
SYSTEM The SYSTEM command lets you execute MPE/iX commands from within ISQL. Scope ISQL Only ISQL Syntax 2 SY STEM : 3 2 MPE/iXCommand 3 Parameters MPE/iXCommand is any MPE/iX command that can be executed in break mode. If entered, it is passed to MPE/iX to be executed, and you return to ISQL automatically. If omitted, you must enter RESUME to return to ISQL. The length of the MPE/iX command can be as long as 255 bytes. Example isql=> SYSTEM :LISTF @.
UNLOAD UNLOAD The UNLOAD command copies data from one or more tables into an output le. Scope ISQL only. ISQL Syntax 2 3 E XTERNAL 2 3 OutputFileName U NLOAD TO NTERNAL I 3 9 82 < 2 Owner. 3TableName = Owner. ViewName ExternalOutputSpec FROM ; : "SelectStatement" 2 3 Parameters EXTERNAL INTERNAL OutputFileName [Owner .]TableName [Owner .]ViewName SelectStatement indicates that the output le is a text le; les in this format are intended for use by user programs.
UNLOAD ExternalOutputSpec describes an EXTERNAL le. The syntax for this option is: DescriptionFileName {OutputLength [FractionLength] [NullIndicator]} [...] Description- identi es a description le. The le name follows FileName the same conventions as the name of the output le. The description le contains at least one line for each column unloaded. The rst line describes the rst column unloaded, the second line describes the second column unloaded, and so on. Each line contains seven elds.
UNLOAD OutputLength is the number of columns (bytes) to allocate in the output line for data from each column unloaded. Specify OutputLength (and, optionally, FractionLength and NullIndicator ) for each column to be unloaded in the order in which they are to be unloaded. For numeric values, allow one additional space for the sign. For oat and decimal numbers, allow one extra space for the decimal point.
UNLOAD Description You must establish a DBE session with a CONNECT or a START DBE command before using the UNLOAD command. You can unload an external le directly to tape. For example: isql=>UNLOAD; Output file format (i[nternal] or e[xternal])> external; Output file name> TAPE; TableName or "SelectStatement"> ManufDB.TestData; Command in progress. Number of rows processed is 12 The description le needs to be a separate le, and cannot be unloaded directly to tape.
UNLOAD UNLOAD INTERNAL may not be used with LONG columns. When you are unloading to an external le in prompting mode, ISQL also prompts you for a description le name and for information on each column to be unloaded. Description file name> FileName; Column COLUMNNAME1, Type (Size): Output length> OutputLength; Fraction length> FractionLength; Null indicator> NullIndicator; Column COLUMNNAME2, Type (Size): .. . You are prompted for a fraction length only if the column contains DECIMAL or FLOAT data.
UNLOAD Example isql=> UNLOAD; Output file format (i[nternal] or e[xternal])> e> Output file name> EXTD; TableName or "SelectStatement"> ManufDB.
UNLOAD isql=> LIST FILE EXTD; -------------| EXTD | -------------1984-06-19 08:45:33.123 1984-06-14 11:13:15.437 1984-07-02 14:54:07.984 1984-07-22 09:06:23.319 1984-06-19 08:45:33.123 1984-07-09 16:07:17.394 1984-07-13 09:25:53.183 1984-07-15 13:22:13.782 1984-07-09 16:07:17.394 1984-07-15 15:32:03.529 1984-07-25 10:15:58.159 1984-07-25 10:15:58.159 1984-08-19 08:45:33.
UNLOAD isql=> UNLOAD TO EXTERNAL EXTD FROM ManufDB.TestData > DTD > 24 > 11 ? > 9 ? > 9 ? > 20 ? > 3 ? > 3 ?; Command in progress. Number of rows processed is 13 isql=> The same command in a script le would look like this: UNLOAD TO EXTERNAL EXTD FROM ManufDB.
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.
AddConstraintSpecification 3 2 8 8 < < UniqueConstraint CONSTRAINT 2 9 9 ConstraintID1 3 =2 3 = , . . . CONSTRAINT ConstraintID1 ) ADD CONSTRAINT ( ReferentialConstraint 3 2 ; ; : : CONSTRAINT ConstraintID1 CheckConstraint 33 2 2 CLUSTERING ON CONSTRAINT ConstraintID2 DropConstraintSpecification DROP CONSTRAINT 2 (ConstraintID , . . .
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 , ...
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 . . .
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 , . . .
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.
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.
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.
EXECUTE IMMEDIATE EXECUTE IMMEDIATE 'String' :HostVariable EXECUTE PROCEDURE 32 2 3 . ProcedureName ReturnStatusVariable =3 32 Owner EXECUTE PROCEDURE :32 3 3 2 2 2 ...
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.
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 , . . .
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.
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 2 3 GroupName , . . . FROM GROUP Owner. TargetGroupName ; : ClassName RESET RESET 8 < : SYSTEM.ACCOUNT SYSTEM.
REVOKE Revoke Authority on a Table or View 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.
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 . .. 3 Query Block Level 3 2 ALL SELECT SelectList INTO HostVariableSpeci cation DISTINCT 3 2 , . ..
FromSpec 8 > > TableSpec > > (FromSpec) > > > > > > > < 2 9 > > > > > > > > > > > = 3 INNER2 3 TableSpec 4 5 FromSpec NATURAL LEFT 2OUTER 3 JOIN (FromSpec) RIGHT OUTER > > 2 3 > > > INNER2 > 3 > ON SearchCondition3 TableSpec > 4 5 > OUTER JOIN FromSpec LEFT 2 > > 3 USING (ColumnList) ( FromSpec ) : RIGHT OUTER > > > > > > > > > > > ; TableSpec 2 2 3 Owner.
Scan Access 2 NO 3 8 9 SERIALSCAN > > > > < = INDEXSCAN HASHSCAN > > : SORTINDEX > > ; Join Algorithm 2 NO 3 8 9 NESTEDLOOP > > > > < = NLJ > > SORTMERGE : SMJ > > ; SET PRINTRULES SET PRINTRULES ON OFF SET SESSION SET SESSION 9 8 9 8 > RR > > > > > > > > > > > > > > > > > CS > > > > > > > > > > > > > > > > > > RC > > > > > > > > > > > > > > > > > > RU > > > > > > > > > > = < > > > > REPEATABLE READ > > > > ISOLATION LEVEL > > > > > > SERIALIZABLE > > > > > > > > > > > > > > > > >
SET TRANSACTION SET TRANSACTION 8 > > > > > > > > > > > > > > > > > > > > > > ISOLATION > > > > > > > > > > > < LEVEL 8 > RR > > > > CS > > > > > RC > > > > RU > > < REPEATABLE READ 9 > > > > > > > > > > > > > > > > = 9 > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > > =2 > > SERIALIZABLE > > > > > > CURSOR STABILITY > > > > > > > > > > READ COMMITTED > > > > > > > > > > READ UNCOMMITTED > > > > : ; :HostVariable1 > > > > > > > > > > > Priority > > > > > PRIORITY > > > > :HostVa
START DBE NEW 2 ' START DBE 'DBEnvironmentName' AS 'ConnectionName 3 2 DUAL | . . .
START DBE NEWLOG 2 START DBE 'DBEnvironmentName' AS 'ConnectionName' 9 3 28 < ARCHIVE = 7 6 DUAL | . . .
TRANSFER OWNERSHIP TRANSFER OWNERSHIP OF 3 32 82 TABLE Owner. TableName > 3 2 32 > < 9 > > = Owner . ViewName 2 3 TO NewOwnerName Owner. ProcedureName > > ; GROUP GroupName VIEW > PROCEDURE > : TRUNCATE TABLE 3 2 TRUNCATE TABLE Owner. TableName UPDATE 2 3 Owner. 3TableName UPDATE 2 Owner. ViewName 99 8 8 = =2 < Expression < , ... SET ColumnName = 'LongColumnIOString' : : 2 WHERE SearchCondition 3 ;; NULL 3 LongColumnIOString 33 2 2 9 8 FileName .Group .
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 > > > > ; : . .. | >%$ VALIDATE 2 3 SETOPTINFO VALIDATE 8DROP 3 2 8 Owner. ModuleName > MODULE > > > > > < PROCEDURE 82 92 , . .. 3 Owner. ProcedureName > > > > > MODULES > : ALL 92 3 , . ..
B ISQL Syntax Summary CHANGE 2 3 2 C HANGE Delimiter OldString Delimiter NewString Delimiter @ 3 DO DO CommandNumber 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) , . . .
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 . ..
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 . . .
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
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 ...
Index Special characters @, 2-18 A aborting commands, 2-5 application program using SQL statements, 3-15 application programmers, 1-2 authorization, 1-3 AUTOCOMMIT option, 4-53 and INPUT, 2-4, 3-4, 4-22, 4-23 and INSTALL, 2-4, 4-25 and LOAD, 2-4, 3-15 AUTOLOCK option, 4-53 automatic DBE session termination, 2-3 automatic transaction management, 2-3 AUTOSAVE option, 4-53 and INPUT, 3-4 and LOAD, 3-15 B banner, ISQL, 2-1 beginning a DBE session, 2-2 BEGIN WORK automatically by ISQL, 2-3 use of, 2-3 BINARY
from command le, 2-9{11 from pro le le, 2-11{12 from the command bu er, 2-13{14 from the command history bu er, 2-14{16 comment marker, 2-6 comments, 2-6, 2-11 COMMIT WORK and DBEnvironment termination, 2-23 and END, 4-9 and EXIT, 4-11 and INPUT, 3-4, 4-22 and ISQL termination, 2-23 and LOAD, 3-15 by ISQL, 2-2, 2-4 use, 2-3 concurrency, 2-4 CONNECT, 2-2 CONTINUE option, 4-54 and command les, 2-11 converting data and LOAD, 4-37 IBM mainframe format, 4-37, 4-54 CONVERT option, 4-54 creating les, 2-14, 4-8, 4-
ESCAPE option escape characters, 2-7 of SET, 4-54 use of, 2-7 EXECUTE statement, 3-16 executing command bu er contents, 2-14 executing a module, 3-16 executing ISQL in job mode, 2-12 executing MPE/iX commands from ISQL, 4-66 EXIT, 4-11 and RELEASE, 2-2 syntax, 4-11 EXIT ON DBERR option of SET, 4-54 EXIT ON DBWARN option of SET, 4-54 external data les use of, 3-9 value loading criteria, 4-35 external data les: creation, 4-67 length of data values in, 4-34 location of data in, 4-34 null indicators, 4-34, 4-36
EDIT, 4-8 END, 4-9 ERASE, 4-10 EXIT, 4-11 EXTRACT, 4-12 HELP, 4-13{17 HOLD, 4-18{19 INFO, 4-20{21 INPUT, 4-22{24 INSTALL, 4-25{26 LIST FILE, 4-27 LIST HISTORY, 4-28 LIST INSTALL, 4-29 LIST SET, 4-30{31 LOAD, 4-32{41 purpose, 1-2 RECALL, 4-42{43 REDO, 4-44{47 RENAME, 4-48 SELECT, 4-49{52 SELECTSTATEMENT, 4-49{52 SET, 4-53{57 size, 4-1 SQLGEN, 4-58 SQLUtil, 4-59 START, 4-60{63 STORE, 4-64{65 structure, 1-3 summary of, 4-1{3 SYSTEM, 4-66 UNLOAD, 4-67{74 ISQLLP and PRINT option, 3-6, 4-50 isqlout and query resu
M managing transactions, 2-3 modifying commands, 4-4 les, 4-8 module dropping, 4-25{26 executing, 3-15, 3-16 extracting, 4-12 installing, 4-25{26 MPE/iX commands, 4-66 and leaving ISQL, 2-21 multiple-line commands, 2-4 N NATIVE-3000 de ned, 1-3 native language and special characters, 2-6 setting and resetting, 1-3{4 native language support overview, 1-3{4 NLUSERLANG job control word setting and resetting, 1-3{4 null indicator, 4-33 NULL option and SELECT, 3-5, 3-6, 4-51 setting, 4-55 null values and INFO,
and maintaining command les, 2-9 syntax, 4-48 renaming les and RENAME, 2-10, 4-48 RESUME after using SYSTEM, 2-21 revalidate sections, 4-26 RIGHT, 3-5 ROLLBACK WORK and END, 4-9 and EXIT, 4-11 and INPUT, 3-4, 4-22 and savepoints, 2-3 by ISQL, 2-2 use, 2-3 row count, 3-5 insertion, 3-3 length, 3-5 S savepoint use of, 2-3 script le example of, 4-74 for UNLOAD command, 4-74 sections extracting, 4-12 revalidating, 4-26 SELECT, 4-49 ISQL support of, 3-4 SELECTSTATEMENT, 4-49 semantic errors, 2-22 semicolon and
U UNLOAD, 4-67{74 direct to tape, 4-70 EXTERNAL option, 3-9, 3-11 INTERNAL option, 3-11 syntax, 4-67 use of, 3-9 using a script le, 4-74 unloading data and UNLOAD, 4-67 from the command line, 3-10 prompting mode, 3-10 ways of, 3-10, 3-12 with a command le, 3-10 unloading from tables, 4-67 UP, 3-5 UPDATE ISQL support of, 3-3 updating les, 4-8 uses for ISQL, 1-2 V VALIDATE, 4-26 values passing, 4-61 view de nition, 3-8, 4-20 W wildcard, 2-18 Z ZONED DECIMAL converting, 4-38 Index-7
