Using ISQL Files and Buffers

ISQL has a command router that determines whether a command is an SQL statement or an ISQL command and then routes it to SQLCore for execution. The ISQL commands that entail database access, such as data loading operations, are converted into SQL statements by the ISQL command manager. The command router also looks up synonyms when a synonym file is used.

As Figure 2-1 illustrates, ISQL's command router that accepts commands from the following sources:

The terminal.
Files containing commands.
The command buffer.
The command history buffer.

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 file is a file that contains one or more commands you want to execute using ISQL. Command files are useful when you want to enter lengthy or repetitive commands. Any SQL statement you can enter from a terminal can be put in a command file, and any ISQL command except REDO can be put in a command file. Commands in a command file can contain parameters. To execute commands in a command file, you by issue the START command. Control returns to the terminal when ISQL stops processing commands from the file.

To maintain command files, you can use the following ISQL commands:

EDIT invokes that editor named in the EDITOR option of the SET command.
STORE saves the current contents of the command buffer in a file.
ERASE purges files.
LIST FILE displays files at the terminal.
RENAME changes the names of files.
RECALL FILE places up to 32K bytes of a command file into the command buffer.
START executes the commands in the command file.

Figure 2-2 summarizes the ISQL commands related to command file creation, maintenance, and use. Refer to "Command Buffer" and "Command History Buffer" later in this chapter for an explanation of the buffer areas.

Figure 2-2 ISQL Commands Related to Command Files

You can create command files with the editor on your system. For example:

   isql=> EDIT LoadDB;

   /* Command file to load PartsDBE tables                */

   /* Command to load table PurchDB.Parts                 */

   LOAD FROM EXTERNAL PartData TO PurchDB.Parts

   PartNumber             1      16

   PartName               17     30     ?

   SalesPrice             47     10     ? 

   END  NO;

   COMMIT WORK;

   SELECT * FROM PurchDB.Parts;

   PAUSE;

   .

   .

   .

   :wq

   isql=>

You can format commands however you want to in command files, but you must terminate each ISQL and SQL statement in the file with a semicolon. You can also insert comments into the command file; 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 80 in command files if you want ISQL to process it, because ISQL reads only up to 80 bytes per line when executing commands from a command file.

You execute command files by using the START command as follows:

   isql=> START LoadDB;

You can include additional START commands in a command file. The maximum number of STARTs permitted (including the first START and all levels of nesting) is 10.

During execution of command files, what you see at the terminal depends on the setting of the ECHO option of the SET command and whether you have included the PAUSE option:

When ECHO is ON, ISQL displays lines at the terminal as they are read from the command file. Both comments and commands in the file are displayed. Regardless of the setting of this option, however, normal command output and error messages are displayed at the terminal.
When you specify the PAUSE option in your command file following a SELECT statement, ISQL temporarily suspends command file processing to let you browse through and optionally print the query result at your terminal. The discussion under "SELECTSTATEMENT" in chapter 4 shows how to browse through displayed query results. When you enter END; at the terminal, ISQL resumes reading lines from the command file.
If you do not specify the PAUSE option following a SELECT statement in a command file, ISQL displays the first group of data rows of the query result, then resumes processing commands from the file; in this case, you must include END; in your command file to terminate the query.

If ISQL encounters an error while processing commands from a command file, it terminates command file processing if the CONTINUE option value of the SET command is OFF. If CONTINUE is ON, ISQL continues processing any subsequent commands in the file.

Creating a Profile File

The profile file is a special kind of command file. When you invoke ISQL, the commands in your profile file are executed before the input prompt appears. Your profile file must reside in your current working directory and be named isqlpro.

You can create a profile file with an editor on your system, or with ISQL STORE or EDIT commands as follows:

   isql=> EDIT;



   Editor is invoked.

   . . .

   SET ECHO ON;

   CONNECT TO 'mydbe';

   :wq isqlpro

   isql=>

This profile file 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 file by specifying the file name as part of the ISQL command invocation. Example:

   $ isql < CommandFile

If you specify a command file and if isqlpro also exists, the commands in isqlpro are executed first.

Running ISQL in the Background

You can run ISQL in the background by using a command file as redirected input to ISQL. As an example, imagine a command file that could be used to create the sample DBEnvironment PartsDBE. The command file, which might be called creajob, starts several other ISQL command files (located in the sampledb directory) to define the sample DBEnvironment. Here is the content of creajob:

   /* This file starts the various ISQL command files that create

       and load the PartsDBE DBEnvironment. */



   SET ECHO ON;

   START sampledb/STARTDBE;

   !echo The DBEnvironment PartsDBE is now created!;

   START sampledb/CREATABS;

   !echo The Tables and Views are now created!;

   COMMIT WORK;

   START sampledb/LOADTABS;

   !echo The Tables and Views are now loaded and statistics updated!;

   COMMIT WORK;

   START sampledb/CREAINDX;

   !echo The Indexes are now created!;

   COMMIT WORK;

   START sampledb/CREASEC;

   !echo The Security schema for PartsDBE is now created!;

   COMMIT WORK;

   EXIT;

   !echo Creation and Loading of PartsDBE is now complete!;

The ISQL command file named STARTDBE configures the DBEnvironment. Tables and other objects are defined in the command file named CREATABS. The command file named LOADTABS contains ISQL LOAD commands for loading the tables. The command file named CREAINDX contains SQL statements for creating indexes. The command file named CREASEC contains the SQL statements that create authorization groups and grant users and groups specific authorities.

You run creajob in the background as follows:

   $ isql < creajob > isqlout &lt;\user>

Refer to the "Sample DBEnvironment" appendix in the ALLBASE/SQL Reference Manual for a complete description of the ISQL command files used to create PartsDBE.

When executing ISQL in the background, the output file created does not contain an echo of the commands executed in ISQL. It does contain visible escape characters in query results.

The Command Buffer

The command buffer 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 buffer by using the ISQL RECALL or HOLD command. You execute commands in the command buffer by using the ISQL START command. Figure 2-3 summarizes the ISQL commands that operate on the command buffer.

Figure 2-3 ISQL Commands Related to the Command Buffer

When you first invoke ISQL, the command buffer is empty. You can use one of the following commands to put information into the command buffer:

HOLD puts one or more commands you key in at the terminal into the command buffer. To put multiple commands into the command buffer with the HOLD command, type the current escape character before the semicolon separating commands as shown below:
isql=> HOLD UPDATE STATISTICS > FOR TABLE PurchDB.Parts\;INFO PurchDB.Vendors;
RECALL FILE puts the contents of a file into the command buffer and displays it.
RECALL HISTORY puts a command from the current command history buffer into the command buffer and displays it. The command history buffer is discussed in the following section.

You can use the RECALL CURRENT command to identify the commands currently in the buffer. For example:

   isql=> RECALL CURRENT;



   UPDATE STATISTICS FOR TABLE PurchDB.Parts;INFO PurchDB.Vendors;

You can change, store, or execute the contents of the command buffer 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 buffer.
STORE keeps the contents of the command buffer in a file.
START executes the command(s) in the command buffer. The commands may contain parameters.

The Command History Buffer

When you invoke ISQL, the command history buffer is empty. As you submit commands, ISQL puts the first 32K bytes of each command into this buffer. As illustrated in Figure 2-4, ISQL and SQL commands submitted from any source are put into the command history buffer. The RECALL HISTORY and LIST HISTORY commands are the only commands not placed in this buffer.

Figure 2-4 ISQL Commands Related to the Command History Buffer

The command history buffer holds the 10 most recently submitted commands. Commands in the command history buffer are numbered from 1 through 10, with 1 being the most recently submitted command. When you submit multiple commands at the same time, each command occupies a separate slot in the history buffer.

The following three commands operate on the commands currently in the command history buffer:

LIST HISTORY displays one or all of the commands in the command history buffer; you can use an @ to indicate that you want to see the entire command history buffer. For example:

   isql=> LIST HISTORY @;

   

   1     SELECT * FROM PurchDB.Parts;

   2     SELECT * FROM PurchDB.Vendors;

   3     SELECT * FROM PurchDB.PartInfo;

   4     CONNECT TO 'sampledb/PartsDBE';

   5

   6

   7

   8

   9

   10

   

   isql=>

RECALL HISTORY puts one command from the command history buffer into the command buffer and displays it. For example:
isql=> RECALL HISTORY 2; SELECT * FROM PurchDB.Vendors; isql=>
REDO lets you edit any of the commands in the command history buffer, then execute the edited command if you desire. As the following example illustrates, you use the REDO I subcommand to insert data and the X subcommand to execute the edited command:
isql=> REDO 2; SELECT * FROM PurchDB.Vendors; Itatistics SELECT * FROM PurchDB.Vendorstatistics; X SELECT statement is executed.

Refer to the REDO command in chapter 4, "ISQL Commands," for a complete explanation of this command's editing features.

When you submit an ISQL command in prompting mode, as in the following example, only the part of the command first submitted is stored in the command history buffer:

   isql=> LOAD FROM INTERNAL;

   Input file name> InFile;

   'InFile' cannot be found.  (DBERR 17)

   isql=> RECALL HISTORY 1;

   

   LOAD FROM INTERNAL;

   isql=> START;

   

   Input file name> IntFile;

   Table name> PurchDB.Vendors;

   Number of rows processed is 15

   COMMIT WORK to save to DBEnvironment

   

   isql=>

Using a Synonym File

You can equate ISQL and SQL commands to your own command verbs by using a synonym file. For example, you can define UNDO to be the synonym for ROLLBACK WORK. When you enter UNDO, ISQL issues the SQL ROLLBACK WORK statement. A synonym file must be named isqlsyn and must exist in your current working directory when you invoke ISQL.

Each line in a synonym file should contain an ISQL or SQL command verb, followed by at least one blank, then a synonym. There can be as many as 100 synonyms per file. The ISQL or SQL command can contain as many as 60 bytes, including embedded ASCII blanks. Synonyms can be as long as 16 bytes, including embedded ASCII blanks. Multiple-word verbs and synonyms should be delimited by ASCII double quotation marks as shown below:

   isql=> EDIT;



   Editor is invoked.

   . . .

   "COMMIT WORK"  CW

   REDO           FIX

   "CONNECT TO '../sampledb/PartsDBE'"   OPEN

   :wq isqlsyn

   isql=>

To invoke a synonym that contains embedded blanks, enclose it in double quotation marks. For example, if a CONNECT statement is defined in your synonym file as "OPEN DB", invoke it as follows:

   isql=> "OPEN DB";