2. Examples of iSQL in Use#
This chapter describes several examples of the use of iSQL to manipulate databases.
Logging In to iSQL#
To use iSQL, users must first be logged in. Connection information may be input directly via a command line, or via the iSQL input prompt.
isql -U userID -P password [-SYSDBA]
or
isql [-SYSDBA]
Additional information necessary for connection with the server is the server name (-S), user ID (-U), and password (-P). The user ID and password are not case-sensitive.
In order for the SYS user to use iSQL as an administrator, the SYSDBA option is used. The SYSDBA option can be used for remote access.
-SYSDAB option should be used in order for the SYS user to use iSQL as an administrator. The SYSDBA option can be also used for remote access. Use double quotation marks if the user ID contains special characters or spaces.
$ isql -U \"user name\"
Login Restrictions#
- Only one user is permitted to connect in SYSDBA mode at one time. Two or more users cannot connect in SYSDBA mode at the same time.
- The user can access the database remotely in SYSDBA mode, but can't start up the database.
For detailed information on system privileges, please refer to the Altibase SQL Reference.
For detailed information on errors that may arise during iSQL execution, please refer to the Altibase Error Message Reference.
$ isql -U sys -P manager [-SYSDBA]
$ isql [-sysdba]
-------------------------------------------------------
Altibase Client Query utility.
Release Version 7.1.0.1
Copyright 2000, Altibase Corporation or its subsidiaries.
All Rights Reserved.
-------------------------------------------------------
Write Server Name (default:127.0.0.1) :
Write UserID : sys
Write Password : manager -> The password on the screen is not displayed.
ISQL_CONNECTION = TCP, SERVER = 127.0.0.1, PORT_NO = 20300
iSQL(sysdba)> -> iSQL is connected to the server, and SQL, iSQL, and PSM commands
can be input and executed here.
Starting Up and Shutting Down Altibase#
iSQL can be used to start up and shut down Altibase.
Starting Up Altibase#
To start up Altibase, iSQL must first be launched with the -sysdba option, in the same way as when a database is created.
Altibase startup commands can be executed only with the UNIX account with which Altibase (including iSQL) was installed.
The following is an example of the use of iSQL to start up Altibase. For more information on starting up Altibase, please refer to the Altibase Administrators’ Manual Chapter 4: Startup and Shutdown.
$ isql –s 127.0.0.1 –u sys –p manager –sysdba
-------------------------------------------------------
Altibase Client Query utility.
Release Version 7.1.0.1
Copyright 2000, Altibase Corporation or its subsidiaries.
All Rights Reserved.
-------------------------------------------------------
ISQL_CONNECTION = TCP, SERVER = 127.0.0.1, PORT_NO = 20300
[Connected to idle instance]
iSQL(sysdba)> startup service
Connecting to the DB server... Connected.
TRANSITION TO PHASE : PROCESS
TRANSITION TO PHASE : CONTROL
TRANSITION TO PHASE : META
[SM] Recovery Phase - 1 : Preparing Database
: Dynamic Memory Version => Parallel Loading
[SM] Recovery Phase - 2 : Loading Database
[SM] Recovery Phase - 3 : Skipping Recovery & Starting Threads...
Refining Disk Table
[SM] Refine Memory Table : ........................................................ [SUCCESS]
[SM] Rebuilding Indices [Total Count:100] .................................. [SUCCESS]
TRANSITION TO PHASE : SERVICE
[CM] Listener started : TCP on port 20300
[CM] Listener started : UNIX
[RP] Initialization : [PASS]
--- STARTUP Process SUCCESS ---
Command execute success.
Shutting Down Altibase#
Use the SHUTDOWN command to shut down a running Altibase server.
The following is an example of the use of iSQL to shut down Altibase. For more information on shutting down Altibase, please refer to the Altibase Administrators’ Manual Chapter 4: Startup and Shutdown.
iSQL(sysdba)> shutdown normal
Ok..Shutdown Proceeding....
TRANSITION TO PHASE : Shutdown Altibase
[RP] Finalization : PASS
shutdown normal success.
Connecting and Disconnecting#
Connecting to a Database#
The CONNECT command is used to connect to Altibase with a specified user ID. If the first connection attempt fails, the CONNECT command does not prompt again for the user ID or password.
CONNECT [logon][nls] [AS SYSDBA];
logon: userID[/password]
nls: NLS=character_set
- userID/password The user ID and password with which to establish a connection to Altibase.
- NLS=character_set The NLS option specifies the character set.
iSQL> CONNECT sys/manager NLS=US7ASCII
Connect success.
- AS SYSDBA The AS clause permits the SYS user to access the server in sysdba manager mode.
If CONNECT is successful, the current session is terminated, and a connection is established to the server using the specified user ID and password and the information in altibase.properties. Accordingly, the session information is cleared before connecting.
For instance, if AUTOCOMMIT mode is set to TRUE in altibase.properties and AUTOCOMMIT mode is changed to FALSE in iSQL, when the CONNECT statement is executed, AUTOCOMMIT mode will be changed to TRUE, because of the value in altibase.properties.
If CONNECT fails, the previous session is terminated and the connection with the server is closed. In other words, the result of all SQL statements executed thereafter will be a “Not connected” message. Execute “CONNECT userID/password [AS SYSDBA]” to attempt to re-establish a connection with the server.
$ isql
-------------------------------------------------------
Altibase Client Query utility.
Release Version 7.1.0.1
Copyright 2000, Altibase Corporation or its subsidiaries.
All Rights Reserved.
-------------------------------------------------------
Write Server Name (default:127.0.0.1) :
Write UserID : SYS
Write Password :
ISQL_CONNECTION = TCP, SERVER = 127.0.0.1, PORT_NO = 20300
iSQL> SHOW USER;
User : SYS
iSQL> CREATE USER altiadmin IDENTIFIED BY altiadmin1234;
Create success.
iSQL> CONNECT altiadmin/altiadmin1234;
Connect success.
iSQL> SHOW USER;
User : ALTIADMIN
iSQL> CREATE TABLE altitbl(i1 INTEGER, i2 CHAR(5));
Create success.
iSQL> SELECT * FROM tab;
TABLE NAME TYPE
---------------------------------------------
ALTITBL TABLE
.
.
.
33 row selected.
iSQL> CONNECT sys/manager;
Connect success.
iSQL> SHOW USER;
User : SYS
iSQL> CREATE TABLE systbl(i1 INTEGER, i2 CHAR(5));
Create success.
iSQL> SELECT * FROM tab;
USER NAME TABLE NAME TYPE
-----------------------------------------------
SYSTEM_ SYS_COLUMNS_ SYSTEM TABLE
SYSTEM_ SYS_CONSTRAINTS_ SYSTEM TABLE
.
.
.
ALTIADMIN ALTITBL TABLE.
SYS SYSTBL TABLE
.
.
.
93 rows selected.
Note#
Double quotation marks should be used if the name contains special characters or spaces.
iSQL\> CONNECT "user name";
Connecting on SSL#
Server-Exclusive Mode#
When using a private certificate in server-exclusive mode (when the SSL_CLIENT_AUTHENTICATION property is set to 0), the location of the client certificate and private key file need not be specified, as the server does not authenticate the client.
Enable the -ssl_verify option and specify the location of the CA certificate file in which the server public key is incorporated, to verify the certificate received from the server.
$ export ISQL_CONNECTION=SSL
$ isql -s localhost -u sys -p MANAGER
or
$ isql -s localhost -u sys -p MANAGER -ssl_verify -ssl_ca ~/cert/ca-cert.pem
Mutual Authentication Mode#
When using a private certificate in mutual authentication mode (when the SSL_CLIENT_AUTHENTICATION property is set to 1), the location of the client certificate and private key file need to be specified, as the server performs client authentication.
Enable the -ssl_verify option and specify the location of the CA certificate file in which the server public key is incorporated, to verify the certificate received from the server.
$ export ISQL_CONNECTION=SSL
$ isql -s localhost -u sys -p MANAGER \
-ssl_cert ~/cert/client-cert.pem \
-ssl_key ~/cert/client-key.pem
or
$ isql -s localhost -u sys -p MANAGER \
-ssl_verify -ssl_ca ~/cert/ca-cert.pem \
-ssl_cert ~/cert/client-cert.pem \
-ssl_key ~/cert/client-key.pem
Disconnecting from a Database#
DISCONNECT is used to terminate the current session and disconnect from the server. The result of all subsequently executed SQL statements will be a “Not connected” message, and “CONNECT userID/password” must be executed in order to connect to the server again.
DISCONNECT;
iSQL> INSERT INTO systbl VALUES(1, 'A1');
1 row inserted.
iSQL> INSERT INTO systbl VALUES(2, 'A2');
1 row inserted.
iSQL> SELECT * FROM systbl;
SYSTBL.I1 SYSTBL.I2
--------------------------
1 A1
2 A2
2 rows selected.
iSQL> DISCONNECT;
Disconnect success.
iSQL> INSERT INTO systbl VALUES(3, 'A3');
[ERR-91020 : No Connection State]
iSQL> SELECT * FROM systbl;
[ERR-91020 : No Connection State]
iSQL> CONNECT sys/manager;
Connect success.
Executing iSQL with the NOLOG Option#
The /NOLOG option allows the user to execute iSQL without connecting to the database. The server IP address and port number must be specified to use this option.
isql -s localhost -port 20300 /NOLOG
Once iSQL is running, enter the database user ID and password with the CONNECT command to connect to the database, and then execute a SQL statement.
Retrieving Information Related to the Database and Database Objects#
Performance Views#
A performance view is a type of data dictionary table capable of inquiring about the server status and database information. The following SELECT statement can be used to view the list of performance views provided by Altibase:
iSQL> SELECT * FROM V$TAB;
TABLE NAME TYPE
---------------------------------------------
V$ALLCOLUMN PERFORMANCE VIEW
V$ARCHIVE PERFORMANCE VIEW
V$BUFFPOOL_STAT PERFORMANCE VIEW
V$DATABASE PERFORMANCE VIEW
V$DATAFILES PERFORMANCE VIEW
V$DISKGC PERFORMANCE VIEW
V$DISKTBL_INFO PERFORMANCE VIEW
V$FLUSHINFO PERFORMANCE VIEW
or the complete list of the performance views provided with Altibase and the meanings of the columns, please refer to the Altibase General Reference Chapter 3: Data Dictionary.
Data in a particular performance view can be queried in the same way as an ordinary table using a SELECT statement, and using JOIN, etc., results can be output in various forms.
Viewing the List of Tables#
Information on all of the tables that exist in the database can be retrieved using the following SELECT statement. The SYS_TABLES_ meta table is an internal system table that contains information about the database catalog provided by Altibase.
iSQL> SELECT * FROM system_.sys_tables_;
.
.
iSQL> SELECT * FROM tab; -> This command is available in iSQL only.
USER NAME TABLE NAME TYPE
-----------------------------------------------
.
..
Viewing a Table Structure#
The following command is used to retrieve information on user-created tables:
DESC table_name;
CREATE TABLE department (
DNO SMALLINT PRIMARY KEY,
DNAME CHAR(30) NOT NULL,
DEP_LOCATION CHAR(9),
MGR_NO INTEGER );
iSQL> DESC department; -> The name of a table whose information (table structure) you want to know.
[ TABLESPACE : SYS_TBS_MEM_DATA ]
[ ATTRIBUTE ]
-----------------------------------------------------------
NAME TYPE IS NULL
-----------------------------------------------------------
DNO SMALLINT FIXED NOT NULL
DNAME CHAR(30) FIXED NOT NULL
DEP_LOCATION CHAR(9) FIXED
MGR_NO INTEGER FIXED
[ INDEX ]
-----------------------------------------------------------
NAME TYPE IS UNIQUE COLUMN
-----------------------------------------------------------
__SYS_IDX_ID_122 BTREE UNIQUE DNO ASC
[ PRIMARY KEY ]
-----------------------------------------------------------
DNO
Use double quotation marks if the table name contains special characters or spaces.
iSQL> DESC "table name";
iSQL> DESC "user name"."table name";
Viewing Sequence Information#
The following commands are used to obtain information on all sequences that exist in the database:
SELECT * FROM seq;
iSQL> CONNECT sys/manager;
Connect success.
iSQL> CREATE USER user1 IDENTIFIED BY user1;
Create success.
iSQL> CONNECT user1/user1;
Connect success.
iSQL> CREATE SEQUENCE seq1 MAXVALUE 100 CYCLE;
Create success.
iSQL> CREATE SEQUENCE seq2;
Create success.
iSQL> CONNECT sys/manager;
Connect success.
iSQL> CREATE SEQUENCE seq2 START WITH 20 INCREMENT BY 30;
Create success.
iSQL> CREATE SEQUENCE seq3 CACHE 40;
Create success.
iSQL> SELECT * FROM seq;
->When accessing the database using the SYS account, information of all sequences will be displayed.
USER_NAME
--------------------------------------------
SEQUENCE_NAME CURRENT_VALUE INCREMENT_BY
------------------------------------------------
MIN_VALUE MAX_VALUE CYCLE CACHE_SIZE
------------------------------------------------
SYS
SEQ2 20 30
1 9223372036854775806 NO 20
SYS
SEQ3 1 1
1 9223372036854775806 NO 40
USER1
SEQ1 1 1
1 100 YES 20
USER1
SEQ2 1 1
1 9223372036854775806 NO 20
4 rows selected.
iSQL> CONNECT user1/user1;
Connect success.
iSQL> SELECT * FROM seq;
-> Information of all sequences created by User 1 will be displayed.
SEQUENCE_NAME CURRENT_VALUE INCREMENT_BY
------------------------------------------------
MIN_VALUE MAX_VALUE CYCLE CACHE_SIZE
------------------------------------------------
SEQ1 1 1
1 100 YES 20
SEQ2 1 1
1 9223372036854775806 NO 20
2 rows selected.
Controlling Transactions#
Defining Transaction Modes#
AUTOCOMMIT determines whether to automatically commit the results of a command at the time of execution.
iSQL> AUTOCOMMIT OFF; -> Commands are not automatically committed before being manually committed by the user.
Set autocommit off success.
iSQL> AUTOCOMMIT ON; -> Commands are automatically committed at the time of execution.
Set autocommit on success.
PLANCOMMIT#
SET PLANCOMMIT ON/OFF;
When EXPLAIN PLAN has been set to ON or ONLY, there is the possibility that the iSQL commands DESC; SELECT * FROM TAB; or SELECT * FROM SEQ; will be committed, even if AUTOCOMMIT has been set to OFF. This setting determines whether to commit them automatically.
Note: This setting has been provided to overcome the misunderstanding where the user believes that such a command has not been prepared, but the system prepares the command in order to generate the execution plan. The command would then be committed, without the user knowing it, when a COMMIT command is executed later. When this value is OFF (which is the default) in a session for which EXPLAIN PLAN is ON (or ONLY) and AUTOCOMMIT is OFF, Altibase does not autocommit the above commands (DESC, SELECT * FROM tab; or SELECT * FROM seq;). When this value is ON, iSQL issues a special commit command to commit these commands.
File Management#
Saving Results#
iSQL enables results returned through iSQL to be saved in a designated file. In the following example, results are stored in the designated file, book.txt, using the SPOOL command.
To cancel this command, use the SPOOL OFF command.
iSQL> SPOOL book.txt
Spool start. [book.txt] -> All subsequently executed commands and their results will be written to
book.txt. The file is created in the current directory.
iSQL> SPOOL OFF
Spool Stop -> From this point on, no more commands or results will be saved in the file.
Running Scripts#
@ Command#
@file_name[.sql]
or
START file_name[.sql]
file_name[.sql]: The script file to be executed. If the filename extension is omitted, iSQL assumes the default command file extension (.sql).
When this command is executed, , iSQL executes all of the commands in the specified script file in sequence.
@command performs the same function as START.
- An EXIT or QUIT command in the script file terminates iSQL.
- The script file may include general SQL statements, iSQL commands, references to stored procedures, etc.
The following is an example in which the schema.sql script, which can be found in the $ALTIBASE_HOME/sample/APRE/schema directory, which is the current directory, is executed.
iSQL> START schema.sql <- The SQL statements in the file are executed.
or
iSQL> @schema.sql
When specifying a script file, you can use a question mark (“?”) to indicate the Altibase home directory (\$ALTIBASE_HOME) of the user account. The following is an example in which the schema.sql script, which can be found in the \$ALTIBASE_HOME/sample/APRE/schema directory, is executed regardless of which directory is the current directory.
iSQL> @?/sample/schema.sql
The question mark (“?”) can also be used with the following iSQL commands:
edit, save, load, spool, start
The -- or / / characters can be used to insert comments in script files. -- means that everything that follows until the end of the line will be handled as a comment, whereas comments that span several lines are placed between / and /.
@@ Command#
@@file_name[.sql]
file_name[.sql]: This indicates the embedded script to be executed. If the extension is omitted, iSQL assumes the default command file extension(.sql).
Executes the specified script. The functionality of the @@ command is similar to that of the @ command.
This command searches for script files in the same path as the script currently being executed, and is thus useful for executing embedded scripts.
The @@ command can be used for the following purposes:
-
If a script file that contains the text @@file_name.sql is executed, iSQL looks for the file specified by file_name.sql, and executes its contents in sequence. file_name.sql must be located in the same directory as the script file that called it. If no such file exists, iSQL raises an error.
-
If a user inputs @@file_name.sql at the iSQL prompt, the result will be the same as when using iSQL to execute @file_name.sql.
-
The script typically may include SQL statements, iSQL commands, or stored procedures.
-
An EXIT or QUIT command in the script terminates iSQL.
The following is an example of the execution of a.sql, in which schema.sql is referenced, from the \$ALTIBASE_HOME directory. In order for this example to be executed without error, a.sql must exist in the $ALTIBASE_HOME/sample/APRE/schema directory alongside schema.sql.
iSQL> @sample/APRE/schema/a.sql
$ cat a.sql
@@schema.sql
Note: The following chapter provides examples of editing the results of a query in an iSQL environment based on the tables created by execution of the above script (see appendix Schema).
Passing parameters through SART Command#
START file_name[.sql] [param1 [param2] ...]
@file_name[.sql] [param1 [param2] ...]
@@file_name[.sql] [param1 [param2] ...]
[param1 [param2] ...] : The value to be transferred as a parameter to the script file.
The substitution variables are used if a user wants to specify every time execution is made and not fixating certain values of a SQL statement within the script file. The values to be replaced the substitution variable can be passed as a parameter if the script file is executed with START, @ or @@ command.
The substitution variable within the script file is used with '&' and numbers, and the number signifies the sequence. However, this feature is performed only if the SET DEFINE ON option is specified. Refer to the SET DEFINE(hyperlink) for further information.
For instance, if substitution variables are used in emp.sql file as in the following :
SELECT ENO, E_LASTNAME FROM EMPLOYEES
WHERE EMP_JOB = '&1'
AND SALARY > &2;
If 'programmer' and '2000' are inserted as parameters when executing the START command, 'programmer' and '2000' are replaced into &1 and &2, respectively. Thus, employees whose job is 'programmer' and salary is '2000' are viewed.
iSQL> SET DEFINE ON; -- Substitution values are replaced as parameters if it is set to ON.
iSQL> START emp.sql programmer 2000
old 2: WHERE EMP_JOB = '&1'
new 2: WHERE EMP_JOB = 'programmer'
old 3: AND SALARY > &2;
new 3: AND SALARY > 2000;
ENO E_LASTNAME
-------------------------------------
10 Bae
iSQL outputs SQL commands before and after parameter values are replaced for the command-lines containing substitution variables. SQL commands after replacing values are not output if the SET VERIFY OFF option is specified. The substitution variable can be used for multiple times within a single script and it is not necessary to be used in sequence.
The substitution value can also be replaced with parameters in the following manner.
START emp.sql
...
Enter value for 1: programmer
old 2: WHERE EMP_JOB = '&1'
new 2: WHERE EMP_JOB = 'programmer'
Enter value for 2: 2000
old 3: AND SALARY > &2;
new 3: AND SALARY > 2000;
In addition, in order to use specific characters by connecting immediately after the substitution value, a period(.) should be used for distinguishing the substitution value and characters.
SELECT E_LASTNAME FROM EMPLOYEES WHERE ENO='&1.0';
Enter value for 1: 2
old 1: SELECT E_LASTNAME FROM EMPLOYEES WHERE ENO='&1.0';
new 1: SELECT E_LASTNAME FROM EMPLOYEES WHERE ENO='20';
SET DEFINE#
This specifies whether or not to replace substitution variables with the parameter values inserted by a user when executing a script file containing the substitution variable through the START, @ or @@ command.
The default value is set to OFF, and substitution variables are not replaced with parameter values. That is, this option should be set to ON when executing a script file containing substitution variables.
SET VERIFY#
This specifies whether or not to display SQL statements before and after replacing with the parameter value when executing a script file containing the substitution variable through the START, @ or @@ command.
The default value is set to ON and before and after SQL statements are output.
$cat Param1.sql
SELECT * FROM T1 WHERE I1 = &1;
iSQL> SET DEFINE ON;
iSQL> SHOW VERIFY;
Verify : On
iSQL> START Param1.sql 5;
iSQL> SELECT * FROM T1
WHERE I1 = &1;
old 2: WHERE I1 = &1;
new 2: WHERE I1 = 5;
T1.I1 T1.I2
---------------------------
5 Hyacinth
1 row selected.
iSQL> SET VERIFY OFF;
iSQL> SHOW VERIFY;
Verify : Off
iSQL> START Param1.sql 5;
iSQL> SELECT * FROM T1
WHERE I1 = &1;
T1.I1 T1.I2
---------------------------
5 Hyacinth
1 row selected.
Saving SQL Statements#
Of the commands currently in the iSQL buffer, the SAVE command saves the most recently executed one in a file.
This file will be created in the current directory.
iSQL> SELECT * FROM book;
iSQL> SAVE book.sql; -> ‘SELECT * FROM book;’ is saved in the file book.sql.
Save completed.
Loading SQL Statements#
This function loads the first command in the specified file to the last position in the iSQL buffer.
iSQL> LOAD book.sql
iSQL> SELECT * FROM book;
Load completed.
iSQL> / -> The results of execution of SELECT * FROM book; can be seen.
Saving DML Statements#
Executed DML statements such as INSERT, UPDATE, DELETE and MOVE are saved in $ALTIBASE_HOME/trc/isql_query.log.
Specify SET QUERYLOGGING ON to use this functionality and OFF to disable it.
iSQL> SET QUERYLOGGING ON; -> From this point on, all executed DML statements will be
saved in $ALTIBASE_HOME/trc/isql_query.log.
iSQL> CREATE TABLE T1 ( I1 INTEGER );
Create success.
iSQL> INSERT INTO T1 VALUES ( 1 );
1 row inserted.
iSQL> UPDATE T1 SET I1 = 2;
1 row updated.
iSQL> SELECT * FROM T1;
I1
--------------
2
1 row selected.
iSQL> DELETE FROM T1;
1 row deleted.
iSQL> DROP TABLE T1;
Drop success.
iSQL> EXIT
% cat $ALTIBASE_HOME/trc/isql_query.log -> All queries executed since SET QUERYLOGGING ON
was executed can be observed.
[2009/09/16 10:36:14] [127.0.0.1:25310 SYS] INSERT INTO T1 VALUES ( 1 )
[2009/09/16 10:36:31] [127.0.0.1:25310 SYS] UPDATE T1 SET I1 = 2
[2009/09/16 10:36:37] [127.0.0.1:25310 SYS] DELETE FROM T1
Editing Query Statements#
Editing the Most Recent Query Statement#
The command edit is provided for creating and editing files in iSQL.
If you execute ed without parameters, a temporary file named iSQL.buf containing the most recently executed query statements will be created, and the following screen will be visible. (To save space, only a few of the blank lines that would be displayed on the screen are shown here.)
iSQL> SELECT sysdate FROM dual;
SYSDATE
---------------
01-JAN-2000
1 row selected.
iSQL> ed
SELECT sysdate FROM dual;
~
~
~
"iSQL.buf" 1L, 26C
Editing Existing Files
If the user wants to edit an existing file, type the file name in iSQL as a parameter when launching the text editor using the “ed” command. When the screen is initialized, blank lines will be displayed as ~ (tilde) characters.
iSQL> ed myquery.sql
"myquery.sql"
INSERT INTO employee(ENO, E_FIRSTNAME, E_LASTNAME, SEX) VALUES(21, 'MSJUNG', 'F');
INSERT INTO employee(ENO, E_FIRSTNAME, E_LASTNAME, SEX, JOIN_DATE)
VALUES(22, 'Joshua', 'Baldwin', 'M', TO_DATE('2001-11-19 00:00:00', 'YYYY-MM-DD HH:MI:SS'));
~
~"myquery.sql"
Editing Query Statements in History Lists#
The user can use the number in the history list to edit previously executed commands. In detail, the query statements are stored in the temporary file iSQL.buf in association with numbers, and can be edited with reference to them. The edited query will be stored again as the most recent record in the history list, and can be executed by entering the ‘/’ (re-execute) character.
iSQL> h
1 : SELECT * FROM customers;
2 : SELECT * FROM employees;
iSQL> 2ed
or
iSQL> 2 ed
SELECT * FROM employees;
~
~
"iSQL.buf"
The command-line parameter 2, which is the name of the file to be edited (iSQL> ed 2), must be distinguished from the number indicating the line in the file to edit.
After editing (employees was replaced with orders)
iSQL> h <- The history list currently in the iSQL buffer
1 : SELECT * FROM customers;
2 : SELECT * FROM employees;
: SELECT * FROM orders;
<- The query statement edited using the 2 ed command will be saved as the last command in the history list.
iSQL> / <- The most recently executed command will be executed.
ORDERS.ONO ORDERS.ORDER_DATE ORDERS.ENO ORDERS.CNO
-----------------------------------------------
ORDERS.GNO ORDERS.QTY ORDERS.ARRIVAL_DATE ORDERS.PROCESSING
-----------------------------------------------
0011290007 2000/11/29 00:00:00 12 7111111431202
A111100002 70 2000/12/02 00:00:00 C
0011290011 2000/11/29 00:00:00 12 7610011000001
E111100001 1000 2000/12/05 00:00:00 D
…
0012310012 2000/12/31 00:00:00 19 7308281201145
C111100001 250 2001/01/03 00:00:00 O
30 rows selected.
Note#
Use double quotation marks if the file name contains special characters or spaces.
iSQL> SPOOL "file name.txt";
iSQL> START "file name.sql";
iSQL> EDIT "file name.sql";
Formatting SELECT Query Results#
The results of a SELECT query can be formatted as desired by the user.
SET LINESIZE#
Sets the size (number of characters) of one line to be displayed when the results of a SELECT statement are output. It must be between 10 and 32767.
iSQL> SET LINESIZE 100; --> Set the display size of one line to 100.
SET LOBSIZE#
This specifies the number of characters to display when a CLOB column is queried using a SELECT statement.
In order to query CLOB column data using a SELECT statement, the transaction mode must first be set to AUTOCOMMIT OFF.
CREATE TABLE C1(I1 INTEGER, I2 CLOB);
INSERT INTO C1 VALUES(1, 'A123456789');
INSERT INTO C1 VALUES(2, 'A1234');
INSERT INTO C1 VALUES(3, 'A12345');
INSERT INTO C1 VALUES(4, 'A1234567890123');
iSQL> autocommit off; -> This sets the transaction mode to OFF so that a CLOB column can be queried.
Set autocommit off success.
iSQL> select * from c1;
C1.I1 C1.I2
---------------------------
1 A123456789
2 A1234
3 A12345
4 A1234567890123
4 rows selected.
iSQL> set lobsize 10; -> This specifies the number of characters to display on the screen when querying a CLOB column using a SELECT statement
iSQL> select * from c1;
C1.I1 C1.I2
--------------------------
1 A123456789
2 A1234
3 A12345
4 A123456789
4 rows selected.
SET LOBOFFSET#
This specifies the starting location from which to display CLOB data when a CLOB column is queried using a SELECT statement.
In order to query CLOB column data using a SELECT statement, the transaction mode must first be set to AUTOCOMMIT OFF.
CREATE TABLE C1(I1 INTEGER, I2 CLOB);
INSERT INTO C1 VALUES(1, 'A123456789');
INSERT INTO C1 VALUES(2, 'A1234');
INSERT INTO C1 VALUES(3, 'A12345');
INSERT INTO C1 VALUES(4, 'A1234567890123');
iSQL> autocommit off;
Set autocommit off success.
iSQL> set loboffset 4; -> This specifies the starting location of data to be shown on the
screen number of characters to skip) when querying a CLOB column using a SELECT statement.
iSQL> select * from c1;
C1.I1 C1.I2
--------------------------
1 456789
2 4
3 45
4 4567890123
4 rows selected.
SET FEEDBACK#
Outputs the number of records found when the results of a SELECT statement are output.
SET FEEDBACK ON\|OFF\|n
- ON: Output the number of resultant records after execution of a SELECT statement.
- OFF: Do not output the number of resultant records after execution of a SELECT statement.
- n: Output the number of resultant records when the number is n or greater.
iSQL> SET FEEDBACK ON;
iSQL> SELECT * FROM employees WHERE ENO < 3;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
2 Davenport Susan designer
0113654540 1500 F 721219 18-NOV-2009 H
2 rows selected.
SET PAGESIZE#
Specifies the number of resultant rows to display at one time.
iSQL> SET PAGESIZE 2; -> Show results in groups comprising two rows each
iSQL> SELECT * FROM employees;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
2 Davenport Susan designer
0113654540 1500 F 721219 18-NOV-2009 H
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
3 Kobain Ken engineer
0162581369 1001 2000 M 650226 11-JAN-2010 H
4 Foster Aaron PL
0182563984 3001 1800 M 820730 H
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
5 Ghorbani Farhad PL
01145582310 3002 2500 M 20-DEC-2009 H
6 Momoi Ryu programmer
0197853222 1002 1700 M 790822 09-SEP-2010 H
.
.
.
20 rows selected.
iSQL> SET PAGESIZE 0; -> Show all of the results on one page.
iSQL> SELECT * FROM employees;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
2 Davenport Susan designer
0113654540 1500 F 721219 18-NOV-2009 H
3 Kobain Ken engineer
0162581369 1001 2000 M 650226 11-JAN-2010 H
.
.
.
20 rows selected.
SET HEADING#
Sets whether to output the header with a SELECT result.
iSQL> SET HEADING OFF; -> Header is not displayed with the result.
iSQL> SELECT * FROM employees;
1 Moon Chan-seung CEO
01195662365 3002 M R
2 Davenport Susan designer
0113654540 1500 F 721219 18-NOV-2009 H
3 Kobain Ken engineer
0162581369 1001 2000 M 650226 11-JAN-2010 H
.
.
.
20 rows selected.
iSQL> SET HEADING ON; -> Outputs header in result.
iSQL> SELECT * FROM employee;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
2 Davenport Susan designer
0113654540 1500 F 721219 18-NOV-2009 H
3 Kobain Ken engineer
0162581369 1001 2000 M 650226 11-JAN-2010 H
.
.
.
20 rows selected.
SET COLSIZE#
When the results of a SELECT statement are output, sets the number of characters from a column of type CHAR or VARCHAR to display so that columns containing long lines of text can be easily viewed.
iSQL> CREATE TABLE LOCATION(
ID INTEGER,
NAME CHAR(20),
ADDRESS VARCHAR(500),
PHONE CHAR(20));
Create success.
iSQL> INSERT INTO LOCATION VALUES(1, 'ALTIBASE', 'Inyoung Bldg, 5fl 44-11 Youido-dong Youngdungpo-qu seoul, 150-890. Korea', '82-2-769-7500');
1 row inserted.
In the following example, the number of characters of a column of type CHAR or VARCHAR is set to 7:
iSQL> SET COLSIZE 7;
iSQL> SELECT ID,NAME,ADDRESS,PHONE FROM LOCATION;
ID NAME ADDRESS PHONE
--------------------------------------------
1 ALTIBAS 10Fl., 82-2-20
E Daerung 82-1000
post-to
wer II,
Guro-d
ong, Gu
ro-qu,
Seoul 1
52-790.
Korea
1 row selected.
SET NUM[WIDTH]#
This command sets the number of characters to display for data of NUMERIC, DECIMAL, NUMBER and FLOAT columns in SELECT result sets. Data with many significant digits can be made more legible by setting this value high.
The following example sets NUMWIDTH to 30, and then queries NUMERIC, DECIMAL, NUMBER and FLOAT columns.
iSQL> CREATE TABLE t1
(
c_numeric NUMERIC(38, 0),
c_decimal DECIMAL(38, 0),
c_number NUMBER(38, 0),
c_float FLOAT(38)
);
Create success.
iSQL> INSERT INTO t1 VALUES(12345678901234567890, 12345678901234567890, 12345678901234567890, 12345678901234567890);
1 row inserted.
iSQL> SET NUMWIDTH 30
iSQL> SELECT c_numeric, c_decimal, c_number, c_float FROM t1;
C_NUMERIC C_DECIMAL
-----------------------------------------------------------
C_NUMBER C_FLOAT
-----------------------------------------------------------
12345678901234567890 12345678901234567890
12345678901234567890 12345678901234567890
1 row selected.
SET NUMF[ORMAT]#
Syntax#
SET NUMF[ORMAT] format;
This command sets a format of NUMERIC, DECIMAL, NUMBER, and FLOAT type to display their SELECT results. It will take precedence over SET NUMWIDTH settings.
Refer to the "General Reference> Data Types > Numeric Data Types > Numeric" in order to grasp on the formatting on format.
The following is an example of viewing through an exponential form.
iSQL> create table t1(i1 float(30));
Create success.
iSQL> insert into t1 values (123456789012);
1 row inserted.
iSQL> SET NUMFORMAT 9.99EEEE
iSQL> select * from t1;
T1.I1
-------------
1.23E+11
1 rows selected.
CL[EAR] COL[UMNMS]#
This command releases the display format of all of the columns which have been specified by COLUMN commands.
Syntax#
CL[EAR] COL[UMNS]
COLUMN#
This command verifies or sets the display format for a target column of SELECT. The setting is applied to the following cases only.
- The length of character data type.
- The display format of numeric data type.
Syntax#
COL[UMN] [{column | expr} [option]]
column or expr should be indicating a target column or an expression, and it should be identical with the one used in the SELECT statement. Every specified column or the specified format can be confirmed by the COL[UMN] [{column | expr}] command.
The followings can be used in option.
| Option | Description |
|---|---|
| CLE[AR] | This option releases a specified column. |
| FOR[MAT] format | This option sets the display format for the specified column The character data type column: This type can set the display length of the CHAR and VARCHAR type. It will take precedence over SET COLSIZE settings. The numeric data type column: The display format of the NUMBER, DECIMAL, FLOAT, and NUMERIC type can be specified by this option. Refer to "General Reference > Data Types > Numeric Data Types > Numeric " for the available format which can be applied into. It will take precedence over SET NUMFORMAT settings. |
| ON|OFF | This option confirms whether or not to apply the specified display. OFF: OFF leaves the column setting as it is, however; it is not applied to output ON : The specified setting is applied. |
Description#
The display format of a target column in the SELECT statement can be specified. If multiple display formats are selected, the last format will be applied.
In order to release the display format, the user can use the CLEAR or OFF option. The differences between the CLEAR and OFF option is that the CLEAR option can completely remove the specified display setting whereas the OFF option executes the same except it is not applied to output
Example#
The following example demonstrates displaying the length of an address column in VARCHAR(60) with 20.
iSQL> @schema.sql
iSQL> COLUMN address FORMAT A20
iSQL> select cno, address from customers;
CNO ADDRESS
----------------------------------------------
1 2100 Exposition Boul
evard Los Angeles US
A
...
The following commands should be taken in order to delete the given setting.
iSQL> COLUMN address CLE
Setting Output Options#
Getting the Elapsed Time#
This function displays the time it took to execute the SQL statement.
iSQL> SET TIMING ON; -> Output the execution time in the last line after the command is executed.
iSQL> SELECT * FROM employees;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
2 Davenport Susan designer
0113654540 1500 F 721219 18-NOV-2009 H
.
.
.
20 rows selected.
elapsed time : 0.01
iSQL> SET TIMING OFF; -> Execution time is not displayed.
Setting Execution Time Units for Output#
This function sets the units with which to output SQL statement execution time. Can be set to the following units:
- Seconds
- Milliseconds
- Microseconds
- Nanoseconds
iSQL> SET TIMING ON
iSQL> SET TIMESCALE SEC;
iSQL> SELECT * FROM employees;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
...
20 rows selected.
elapsed time : 0.00
iSQL> SET TIMESCALE MILSEC;
iSQL> SELECT * FROM employee;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
...
...
20 rows selected.
elapsed time : 0.72
iSQL> SET TIMESCALE MICSEC;
iSQL> SELECT * FROM employee;
ENO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
...
20 rows selected.
elapsed time : 966.00
iSQL> SET TIMESCALE NANSEC;
iSQL> SELECT * FROM employee;
NO E_LASTNAME E_FIRSTNAME EMP_JOB
------------------------------------------------------------------------------
EMP_TEL DNO SALARY SEX BIRTH JOIN_DATE STATUS
-----------------------------------------------------------------------------------
1 Moon Chan-seung CEO
01195662365 3002 M R
...
20 rows selected.
elapsed time : 681000.00
Describing Foreign Key Information#
This function displays information on foreign keys when the DESC command is used to view the table structure.
iSQL> SET FOREIGNKEYS ON; -> The foreign key information will be output.
iSQL> DESC employees;
[ TABLESPACE : SYS_TBS_MEM_DATA ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME TYPE IS NULL
------------------------------------------------------------------------------
ENO INTEGER FIXED NOT NULL
E_LASTNAME CHAR(20) FIXED NOT NULL
E_FIRSTNAME CHAR(20) FIXED NOT NULL
EMP_JOB VARCHAR(15) FIXED
EMP_TEL CHAR(15) FIXED
DNO SMALLINT FIXED
SALARY NUMERIC(10, 2) FIXED
SEX CHAR(1) FIXED
BIRTH CHAR(6) FIXED
JOIN_DATE DATE FIXED
STATUS CHAR(1) FIXED
[ INDEX ]
------------------------------------------------------------------------------
NAME TYPE IS UNIQUE COLUMN
------------------------------------------------------------------------------
__SYS_IDX_ID_238 BTREE UNIQUE ENO ASC
EMP_IDX1 BTREE DNO ASC
[ PRIMARY KEY ]
------------------------------------------------------------------------------
ENO
[ FOREIGN KEYS ]
----------------------------------------------------------------------
iSQL> SET FOREIGNKEYS OFF; -> The foreign key information will not be output.
iSQL> DESC employees;
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME TYPE IS NULL
------------------------------------------------------------------------------
ENO INTEGER FIXED NOT NULL
E_LASTNAME CHAR(20) FIXED NOT NULL
E_FIRSTNAME CHAR(20) FIXED NOT NULL
EMP_JOB VARCHAR(15) FIXED
EMP_TEL CHAR(15) FIXED
DNO SMALLINT FIXED
SALARY NUMERIC(10, 2) FIXED
SEX CHAR(1) FIXED
BIRTH CHAR(6) FIXED
JOIN_DATE DATE FIXED
STATUS CHAR(1) FIXED
[ INDEX ]
------------------------------------------------------------------------------
NAME TYPE IS UNIQUE COLUMN
------------------------------------------------------------------------------
__SYS_IDX_ID_238 BTREE UNIQUE ENO ASC
EMP_IDX1 BTREE DNO ASC
[ PRIMARY KEY ]
----------------------------------------------------------------------
ENO
Describing CHECK constraints Information#
This function outputs information on CHECK constraints when the DESC command is used to view the table structure.
iSQL> SET CHKCONSTRAINTS ON; -> Check Constraint information is output.
iSQL> DESC employees;
[ TABLESPACE : SYS_TBS_MEM_DATA ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME TYPE IS NULL
------------------------------------------------------------------------------
ENO INTEGER FIXED NOT NULL
E_LASTNAME CHAR(20) FIXED NOT NULL
E_FIRSTNAME CHAR(20) FIXED NOT NULL
EMP_JOB VARCHAR(15) FIXED
EMP_TEL CHAR(15) FIXED
DNO SMALLINT FIXED
SALARY NUMERIC(10, 2) FIXED
SEX CHAR(1) FIXED
BIRTH CHAR(6) FIXED
JOIN_DATE DATE FIXED
STATUS CHAR(1) FIXED
[ INDEX ]
------------------------------------------------------------------------------
NAME TYPE IS UNIQUE COLUMN
------------------------------------------------------------------------------
__SYS_IDX_ID_238 BTREE UNIQUE ENO ASC
EMP_IDX1 BTREE DNO ASC
[ PRIMARY KEY ]
------------------------------------------------------------------------------
ENO
[ CHECK CONSTRAINTS ]
------------------------------------------------------------------------------
NAME : EMP_CHECK_SEX1
CONDITION : SEX in ('M', 'F')
iSQL> SET CHKCONSTRAINTS OFF; -> Check Constraint information is not output.
iSQL> DESC employees;
[ TABLESPACE : SYS_TBS_MEM_DATA ]
[ ATTRIBUTE ]
------------------------------------------------------------------------------
NAME TYPE IS NULL
------------------------------------------------------------------------------
ENO INTEGER FIXED NOT NULL
E_LASTNAME CHAR(20) FIXED NOT NULL
E_FIRSTNAME CHAR(20) FIXED NOT NULL
EMP_JOB VARCHAR(15) FIXED
EMP_TEL CHAR(15) FIXED
DNO SMALLINT FIXED
SALARY NUMERIC(10, 2) FIXED
SEX CHAR(1) FIXED
BIRTH CHAR(6) FIXED
JOIN_DATE DATE FIXED
STATUS CHAR(1) FIXED
[ INDEX ]
------------------------------------------------------------------------------
NAME TYPE IS UNIQUE COLUMN
------------------------------------------------------------------------------
__SYS_IDX_ID_238 BTREE UNIQUE ENO ASC
EMP_IDX1 BTREE DNO ASC
[ PRIMARY KEY ]
------------------------------------------------------------------------------
ENO
Outputting the partition information#
This function allows to view partition information when viewing the table structure with the DESC command.
iSQL> create table t1_range(
c1 integer,
c2 integer,
c3 varchar(4))
PARTITION BY RANGE(c3)
(
PARTITION P_2000 VALUES LESS THAN ('2001') TABLESPACE sys_tbs_disk_data,
PARTITION P_2001 VALUES LESS THAN ('2002') TABLESPACE sys_tbs_mem_data,
PARTITION P_DEFAULT VALUES DEFAULT
) tablespace SYS_TBS_DISK_DATA;
iSQL> SET PARTITIONS ON; -> This command outputs the partition information.
iSQL> DESC t1_range
[ TABLESPACE : SYS_TBS_DISK_DATA ]
[ ATTRIBUTE ]
----------------------------------------------------
NAME TYPE IS NULL ----------------------------------------------------
C1 INTEGER
C2 INTEGER
C3 VARCHAR(4)
T1_RANGE has no index
T1_RANGE has no primary key
[ PARTITIONS ]
----------------------------------------------------
Method: Range
Key column(s)
----------------------------------------
NAME
----------------------------------------
C3
Values
----------------------------------------------------
PARTITION NAME MIN VALUE MAX VALUE
----------------------------------------------------
P_2000 '2001'
P_2001 '2001' '2002'
P_DEFAULT '2002'
Tablespace
---------------------------------------------------
PARTITION NAME TABLESPACE NAME
---------------------------------------------------
P_2000 SYS_TBS_DISK_DATA
P_2001 SYS_TBS_MEM_DATA
P_DEFAULT SYS_TBS_DISK_DATA
iSQL> SET PARTITIONS OFF; -> This command does not output the partition information.
iSQL> DESC t1_range
[ TABLESPACE : SYS_TBS_DISK_DATA ]
[ ATTRIBUTE ]
----------------------------------------------------
NAME TYPE IS NULL
----------------------------------------------------
C1 INTEGER
C2 INTEGER
C3 VARCHAR(4)
T1_RANGE has no index
T1_RANGE has no primary key
Outputting the Execution Results and Commands of Script Files#
The SET TERM and SET ECHO commands determine whether or not to output the execution results and commands of script files to the screen.
Script execution results are output(TERM ON) by default. If the TERM option is set to OFF, the commands which are executed and the results that are generated when the script file is executed in iSQL are not output to the screen. Even if the TERM option is set to OFF, however, query results are output to the screen if queries are manually input(e.g., iSQL> select * from t1;). Only when script commands are used(e.g., iSQL> @t.sql ), are the results not output to the screen.
Even if the TERM option is set to OFF, the commands executed in the script can be output by setting the ECHO command to ON.
The following example outputs the execution results of a script file.
iSQL> SET TERM ON; -> Outputs the script execution result.
iSQL> @schema.sql
iSQL> ALTER SESSION SET AUTOCOMMIT = TRUE; -> Beginning of the result.
Alter success.
iSQL> DROP TABLE ORDERS;
Drop success.
elapsed time : 0.00
iSQL> DROP TABLE EMPLOYEES;
Drop success.
elapsed time : 0.00
.
.
.
iSQL> CREATE INDEX ODR_IDX3 ON ORDERS (GNO ASC);
Create success.
elapsed time : 0.00 -> End of the result.
The following example demonstrates how the commands in the script that is executed with @ can be output, although the TERM option is set to OFF, by setting the ECHO option to ON.
iSQL> SET TERM OFF; -> The script execution results are not output.
iSQL> @schema.sql
iSQL> SELECT eno, e_firstname, e_lastname FROM employees;
-> The result is output when the query is manually input.
ENO E_FIRSTNAME E_LASTNAME
------------------------------------------------------------
1 Chan-seung Moon
2 Susan Davenport
3 Ken Kobain
4 Aaron Foster
5 Farhad Ghorbani
.
.
.
iSQL> SET ECHO ON; -> Only the commands in the script that is executed with @ are output.
iSQL> @schema.sql
ALTER SESSION SET AUTOCOMMIT = TRUE;
DROP TABLE ORDERS;
DROP TABLE EMPLOYEES;
.
.
.
iSQL> CREATE INDEX ODR_IDX3 ON ORDERS (GNO ASC);
Create success.
elapsed time : 0.00 -> End of the result
Outputting an Execution Plan#
In iSQL, an execution plan can be output to fine-tune SQL statements. Using an execution plan, DML statements such as SELECT, INSERT, UPDATE and DELETE can be checked.
In order to accomplish this, the following command must be executed before a statement such as a SELECT statement is executed.
ALTER SESSION SET EXPLAIN PLAN = option;
This option can be set to ON, OFF, or ONLY. The default is OFF.
-
ON: After the SELECT statement is executed, the execution plan information is displayed along with the resultant records.
-
ONLY: The SELECT statement is prepared but not executed, and only the execution plan information is output.This can be used to check the execution plan for a SELECT statement that involves host variable binding, or to quickly check the execution plan for queries that take a long time to execute.
-
OFF: After the SELECT statement is executed, only the resultant records are displayed.
The following command is used to obtain detailed information about how conditions included in WHERE clauses written by the user will be execute:
ALTER SYSTEM SET TRCLOG_DETAIL_PREDICATE = 1;
If this property is set to 1, signifying “ON”, as in the above statement, the execution plan’s WHERE clause conditions, including FIXED KEY RANGE, VARIBALE KEY RANGE and FILTER, are classified and displayed in detail. Therefore, if the WHERE clause is complicated, you can check which predicates will be executed by scanning the sorted indexes. However, this information may not be output if queries are changed to optimize them in some way.
The following example shows the output when the given SQL statement is executed:
- When TRCLOG_DETAIL_PREDICATE has been set to 1 (=on), and EXPLAIN PLAN = ON, the following is output in addition to the results.
iSQL> alter system set trclog_detail_predicate = 1;
Alter success.
iSQL> alter session set explain plan = on;
Alter success.
iSQL> SELECT eno, e_lastname, e_firstname FROM employees WHERE eno = 1;
ENO E_LASTNAME E_FIRSTNAME
------------------------------------------------------------
1 Moon Chan-seung
1 row selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 48 )
SCAN ( TABLE: EMPLOYEES, INDEX: __SYS_IDX_ID_238, ACCESS: 1, SELF_ID: 2 )
[ FIXED KEY ]
AND
OR
ENO = 1
------------------------------------------------------------
- When TRCLOG_DETAIL_PREDICATE is not set to 1, and EXPLAIN PLAN = ON, the following is output in addition to the results.
iSQL> ALTER SYSTEM SET TRCLOG_DETAIL_PREDICATE = 0;
Alter success.
iSQL> ALTER SESSION SET EXPLAIN PLAN = ON;
Alter success.
iSQL> SELECT eno, e_lastname, e_firstname FROM employees WHERE eno = 1;
ENO E_LASTNAME E_FIRSTNAME
------------------------------------------------------------
1 Moon Chan-seung
1 row selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 48 )
SCAN ( TABLE: EMPLOYEES, INDEX: __SYS_IDX_ID_238, ACCESS: 1, SELF_ID: 2 )
------------------------------------------------------------
- When TRCLOG_DETAIL_PREDICATE is not set to 1, and EXPLAIN PLAN = ONLY, only the following is output.
iSQL> ALTER SYSTEM SET TRCLOG_DETAIL_PREDICATE = 0;
Alter success.
iSQL> ALTER SESSION SET EXPLAIN PLAN = ONLY;
Alter success.
iSQL> SELECT eno, e_lastname, e_firstname FROM employees WHERE eno = 1;
ENO E_LASTNAME E_FIRSTNAME
------------------------------------------------------------
No rows selected.
------------------------------------------------------------
PROJECT ( COLUMN_COUNT: 3, TUPLE_SIZE: 48 )
SCAN ( TABLE: EMPLOYEES, INDEX: __SYS_IDX_ID_238, ACCESS: ??, SELF_ID: 2 )
------------------------------------------------------------
If EXPLAIN PLAN = ONLY, because only an execution plan is created and the query is not executed, values that would be determined after actual execution are indicated using question marks (“??”), like an ACCESS clause.
Setting Result Output Orientation#
When querying data using a SELECT statement in iSQL, the results can be displayed either horizontally or vertically.
This function is suitable for outputting results that comprise a small number of rows and many columns. If such a result set is output horizontally, as is usually the case, it is difficult to compare columns and check the values. However, it is easy to see when output vertically.
iSQL>SET VERTICAL ON; -> This sets the print direction vertically.
iSQL> SELECT * FROM employees WHERE eno = 2;
ENO : 2
E_LASTNAME : Davenport
E_FIRSTNAME : Susan
EMP_JOB : designer
EMP_TEL : 0113654540
DNO :
SALARY : 1500
SEX : F
BIRTH : 721219
JOIN_DATE : 18-NOV-2009
STATUS : H
1 row selected.
Viewing iSQL Display Settings#
The following is an example of viewing the values of the iSQL environment variables for the current session:
iSQL> SHOW USER -> This is the current user.
User : SYS
iSQL> SHOW COLSIZE
ColSize : 0
iSQL> SHOW LOBOFFSET
LobOffset: 0
iSQL> SHOW LINESIZE
Linesize : 100
iSQL> SHOW LOBSIZE
LobSize : 80
iSQL> SHOW NUMWIDTH
NumWidth : 11
iSQL> SHOW PAGESIZE
Pagesize : 0
iSQL> SHOW TIMESCALE
TimeScale : Second
iSQL> SHOW HEADING
Heading : On
iSQL> SHOW TIMING
Timing : Off
iSQL> SHOW VERTICAL
Vertical : Off
iSQL> SHOW CHKCONSTRAINTS
ChkConstraints : Off
iSQL> SHOW FOREIGNKEYS
ForeignKeys : Off
iSQL> SHOW PLANCOMMIT
PlanCommit : Off
iSQL> SHOW QUERYLOGGING
QueryLogging : Off
iSQL> SHOW TERM
Term : On
iSQL> SHOW ECHO
Echo : OFF
iSQL> SHOW FEEDBACK
Feedback : 1
iSQL> SHOW ALL
User : SYS
ColSize : 0
LobOffset : 0
LineSize : 80
LobSize : 80
NumWidth : 11
PageSize : 0
TimeScale : Second
Heading : On
Timing : Off
Vertical : Off
ChkConstraints : Off
ForeignKeys : Off
Partitions : Off
PlanCommit : Off
QueryLogging : Off
Term : On
Echo : Off
Feedback : 1
Fullname : Off
Sqlprompt : "iSQL> "
Define : Off
Host Variables#
Host variables are first declared and then used. Host variables are useful when executing procedures or functions.
Declaring a Host Variable#
Syntax#
VAR[IABLE] var_name[INPUT|OUTPUT|INOUTPUT] var_type
The default value is automatically given unless INPUT, OUTPUT or INOUTPUT is specified.
Type#
The following types can be used when declaring variables:
INTEGER, BYTE(n), NIBBLE(n),
NUMBER, NUMBER(n), NUMBER(n,m),
NUMERIC, NUMERIC(n), NUMERIC(n,m),
CHAR(n), VARCHAR(n), NCHAR(n), NVARCHAR(n), DATE
DECIMAL, DECIMAL(n), DECIMAL(n,m),
FLOAT, FLOAT(n), DOUBLE, REAL
BIGINT, SMALLINT
Example#
The following examples demonstrate how to declare variables:
iSQL> VAR p1 INTEGER
iSQL> VAR p2 CHAR(10)
iSQL> VAR v_double DOUBLE
iSQL> VAR v_real REAL
Assigning a Value to a Host Variable#
Syntax#
EXEC[UTE] :var_name := value;
Example#
The following example shows how to assign a value to a variable:
iSQL> EXECUTE :p1 := 100;
Execute success
iSQL> EXEC :p2 := ‘abc’;
Execute success
Viewing Host Variables#
Syntax#
PRINT VAR[IABLE]
Shows all declared variables.
PRINT var_name
Shows the type and value of the variable var_name.
Example#
The following shows the values of all declared variable:
iSQL> PRINT VAR
[ HOST VARIABLE ]
-------------------------------------------------------
NAME TYPE VALUE
-------------------------------------------------------
P1 INTEGER 100
P2 CHAR(10) abc
V_REAL REAL
V_DOUBLE DOUBLE
iSQL> PRINT p2 -> Outputs only variable p2 information.
NAME TYPE VALUE
-------------------------------------------
P2 CHAR ( 10 ) abc
Executing Prepared SQL Statements#
Prepared SQL versus Dynamic SQL Statements#
SQL statements executed in iSQL are usually executed according to the so-called “Direct Execution” method.
In Direct Execution, syntax analysis, validity testing, optimization and execution of a query are all performed at once. However, in Prepared Execution, only the syntax analysis, validity testing, and optimization of the query are performed to set up an execution plan for the query, which is then executed when requested by the client. When creating an application that uses ODBC, the Prepared Execution method is typically used, and is more advantageous in terms of speed when a SQL statement is to be repeatedly executed using host variable binding.
In iSQL, the difference between these two methods lies only in whether variables are used or not; there is no advantage in terms of speed. However, when it is executed in Prepared Execution, the printed graph and the execution plan may contain different information. The graph shows the plan up until the optimization phase, whereas execution plan shows the plan once the actual value is applied to the variable.
Prepared SQL Statements#
Syntax#
PREPARE SQL_statement;
Example#
The following is an example of the use of the PREPARE command to execute a SQL statement:
iSQL> VAR t1 INTEGER;
iSQL> EXEC :t1 := 1;
Execute success.
iSQL> PREPARE SELECT eno, e_firstname, e_lastname, sex
FROM employees WHERE eno=:t1;
ENO E_FIRSTNAME E_LASTNAME SEX
------------------------------------------------------------------
1 Chan-seung Moon M
1 row selected.
Creating, Executing, and Dropping Stored Procedures#
Creating Procedures#
Support is provided for the creation and execution of stored procedures. A stored procedure must end with the following:
END;
/
Successful creation of the procedures can be confirmed by checking the sys_procedures_ meta table.
Executing Procedures#
Procedures are executed in order to execute multiple queries at one time. If the procedure to be executed has parameters, as many variables as there are parameters must be declared before the procedure is executed.
Example 1#
In the following example, a procedure named emp_proc, which executes an INSERT statement using IN parameters, is created:
iSQL> CREATE OR REPLACE PROCEDURE emp_proc(p1 IN INTEGER, p2 IN CHAR(20), p3 IN CHAR(20), p4 IN CHAR(1))
AS
BEGIN
INSERT INTO employees(eno, e_firstname, e_lastname, sex)
VALUES(p1, p2, p3, p4);
END;
/
Create success.
iSQL> SELECT * FROM system_.sys_procedures_ order by created desc limit 1;
USER_ID PROC_OID
------------------------------------
PROC_NAME OBJECT_TYPE STATUS
----------------------------------------------------------------------
PARA_NUM RETURN_DATA_TYPE RETURN_LANG_ID RETURN_SIZE
-------------------------------------------------------------
RETURN_PRECISION RETURN_SCALE PARSE_NO PARSE_LEN CREATED
-------------------------------------------------------------------------
LAST_DDL_TIME
----------------
2 3208680
EMP_PROC 0 0
4
2 192 29-FEB-2012
29-FEB-2012
1 row selected.
emp_proc, which was created above, is executed:
iSQL> VAR eno INTEGER
iSQL> VAR first_name CHAR(20)
iSQL> VAR last_name CHAR(20)
iSQL> VAR sex CHAR(1)
iSQL> EXECUTE :eno := 21;
Execute success.
iSQL> EXECUTE :first_name := 'Joel';
Execute success.
iSQL> EXECUTE :last_name := 'Johnson';
Execute success.
iSQL> EXECUTE :sex := 'M';
Execute success.
iSQL> EXECUTE emp_proc(:eno, :first_name, :last_name, :sex);
Execute success.
iSQL> SELECT eno, e_firstname, e_lastname, sex FROM employees WHERE eno = 21;
ENO E_FIRSTNAME E_LASTNAME SEX
-----------------------------------------------------------------
21 Joel Johnson M
1 row selected.
Example 2#
In the following example, a procedure called outProc, which executes a SELECT statement, is created:
iSQL> CREATE TABLE outTbl(i1 INTEGER, i2 INTEGER);
Create success.
iSQL> INSERT INTO outTbl VALUES(1,1);
1 row inserted.
iSQL> /
1 row inserted.
iSQL> /
1 row inserted.
iSQL> /
1 row inserted.
iSQL> /
1 row inserted.
iSQL> SELECT * FROM outTbl;
OUTTBL.I1 OUTTBL.I2
---------------------------
1 1
1 1
1 1
1 1
1 1
5 rows selected.
iSQL> CREATE OR REPLACE PROCEDURE outProc(a1 OUT INTEGER, a2 IN OUT INTEGER)
AS
BEGIN
SELECT COUNT(*) INTO a1 FROM outTbl WHERE i2 = a2;
END;
/
Create success.
In the following example, outProc is executed:
iSQL> VAR t3 INTEGER
iSQL> VAR t4 INTEGER
iSQL> EXEC :t4 := 1;
Execute success.
iSQL> EXEC outProc (:t3, :t4);
Execute success.
iSQL> PRINT t3;
NAME TYPE VALUE
-----------------------------------------------
T3 INTEGER 5
Example 3#
In the following example, the procedure outProc1 is created:
iSQL> CREATE OR REPLACE PROCEDURE outProc1( p1 INTEGER, p2 IN OUT INTEGER, p3 OUT INTEGER)
AS
BEGIN
p2 := p1;
p3 := p1 + 100;
END;
/
Create success.
iSQL> VAR v1 INTEGER
iSQL> VAR v2 INTEGER
iSQL> VAR v3 INTEGER
iSQL> EXEC :v1 := 3;
Execute success.
iSQL> EXEC outProc1(:v1, :v2, :v3);
Execute success.
iSQL> PRINT VAR;
[ HOST VARIABLE ]
-----------------------------------------------
NAME TYPE VALUE
-----------------------------------------------
..
V1 INTEGER 3
V2 INTEGER 3
V3 INTEGER 103
..
Example 4#
In the following example, a procedure called inoutProc1, which executes a SELECT statement, is created:
iSQL> CREATE TABLE inoutTbl(i1 INTEGER);
Create success.
iSQL> INSERT INTO inoutTbl VALUES(1);
1 row inserted.
iSQL> /
1 row inserted.
iSQL> /
1 row inserted.
iSQL> SELECT * FROM inoutTbl;
INOUTTBL.I1
--------------
1
1
1
3 rows selected.
iSQL> CREATE OR REPLACE PROCEDURE inoutProc (a1 IN OUT INTEGER)
AS
BEGIN
SELECT COUNT(*) INTO a1 FROM inoutTbl WHERE i1 = a1;
END;
/
Create success.
iSQL> VAR t3 INTEGER
iSQL> EXEC :t3 := 1;
Execute success.
iSQL> EXEC inoutProc(:t3);
Execute success.
iSQL> PRINT t3;
NAME TYPE VALUE
-----------------------------------------------
T3 INTEGER 3
Example 5#
In the following example, the procedure inoutProc1 is created:
iSQL> CREATE OR REPLACE PROCEDURE inoutProc1( p1 INTEGER, p2 IN OUT INTEGER, p3 OUT INTEGER)
AS
BEGIN
p2 := p1 + p2;
p3 := p1 + 100;
END;
/
Create success.
In the following example, the procedure inoutProc1 is executed:
iSQL> VAR v1 INTEGER
iSQL> VAR v2 INTEGER
iSQL> VAR v3 INTEGER
iSQL> EXEC :v1 := 3;
Execute success.
iSQL> EXEC :v2 := 5;
Execute success.
iSQL> EXEC inoutProc1(:v1, :v2, :v3);
Execute success.
iSQL> PRINT VAR;
[ HOST VARIABLE ]
-----------------------------------------------
NAME TYPE VALUE
-----------------------------------------------
..
V1 INTEGER 3
V2 INTEGER 8
V3 INTEGER 103
..
Dropping Procedures#
The DROP command is used to drop (delete) procedures.
In the following example, the procedure emp_proc is deleted:
iSQL> DROP PROCEDURE emp_proc;
Drop success
Creating, Executing ,and Dropping Functions#
Creating Functions#
A function is provided to create functions. When creating a function, you must end with the following syntax, and the return type must be defined.
END;
/
Successful creation of the function can be confirmed by checking the sys_procedures_ meta table.
In the following example, the function emp_func, which executes an UPDATE statement and a SELECT statement, is created:
iSQL> CREATE OR REPLACE FUNCTION emp_func(f1 IN INTEGER)
RETURN NUMBER
AS
f2 NUMBER;
BEGIN
UPDATE employees SET salary = 1000000 WHERE eno = f1;
SELECT salary INTO f2 FROM employees WHERE eno = f1;
RETURN f2;
END;
/
Create success.
iSQL> SELECT * FROM system_.sys_procedures_;
USER_ID PROC_OID PROC_NAME
-------------------------------------------------------------------------------
OBJECT_TYPE STATUS PARA_NUM RETURN_DATA_TYPE RETURN_LANG_ID
--------------------------------------------------------------------------
RETURN_SIZE RETURN_PRECISION RETURN_SCALE PARSE_NO PARSE_LEN
------------------------------------------------------------------------
CREATED LAST_DDL_TIME
------------------------------
.
.
.
2 3300024 INOUTPROC1
0 0 3
2 132
15-SEP-2010 15-SEP-2010
2 3302344 EMP_FUNC
1 0 1 6 30000
23 38 0 3 209
15-SEP-2010 15-SEP-2010
36 rows selected.
Executing Functions#
Functions can be executed to simultaneously execute multiple queries. If the function to be executed has parameters, as many variables as there are functions must be declared before the function is executed. Additionally, a variable for saving the result of the function must also be defined.
The following is an example of executing the function emp_func:
iSQL> VAR eno INTEGER
iSQL> VAR ret NUMBER
iSQL> EXEC :eno := 11;
Execute success.
iSQL> EXEC :ret := emp_func(:eno);
Execute success.
iSQL> SELECT eno, salary FROM employees WHERE eno = 11;
ENO SALARY
---------------------------
11 1000000
1 row selected.
Dropping Functions#
The DROP FUNCTION statement is used to drop functions.
In the following example, the function emp_func is deleted:
iSQL> DROP FUNCTION emp_func;
Drop success
Convenient User Functions#
History#
A list of all previously executed commands can be displayed using the HISTORY command. The number corresponding to a previously executed command can be used to easily execute that command again.
iSQL> HISTORY; -> View history list
or
iSQL> H;
1 : SELECT * FROM tab;
2 : SELECT * FROM book;
3 : HISTORY;
iSQL> / -> Re-execute the most recent command(HISTORY;))
iSQL> 2/ -> Execute Command number 2 in history list(SELECT * FROM book;)
History Logging#
It saves the commands executed in iSQL to a file when you exit iSQL. Enabling this function loads previous commands stored in the file when iSQL is restarted. Therefore, previous commands are accessible and executable by using the arrow keys on the keyboard.
To use the history logging function, ISQL_HIST_FILE environment variable should be set and iSQL has to be restarted.
$ export ISQL_HIST_FILE=~/.isql_history
To turn off the history logging function, delete the ISQL_HIST_FILE environment variable.
$ unset ISQL_HIST_FILE
Default Value#
Not used
Constraints#
- This function can only be used when previous commands are accessible by using the arrow keys on command prompt or shell prompt.
- Maximum 100 commands can be stored.
File access control should be well taken care of when this function is used since every command the user entered is stored in the file, including sensitive information such as user passwords.
Shell Commands#
The exclamation point (“!”) is a convenient function that allows direct execution of most shell commands from within iSQL.
iSQL> !ls -al
total 3417
-rw-r----- 1 wlgml337 section 1198 Nov 1 13:30 .aliases
-rw------- 1 wlgml337 section 5353 Oct 18 21:17 .bash_history
-rw-r----- 1 wlgml337 section 1436 Nov 2 15:42 .bashrc
-rw-r----- 1 wlgml337 section 1549 Dec 13 17:36 .profile
drwxr-x--- 2 wlgml337 section 512 Nov 2 02:00 TEMP
drwxr-xr-x 2 root root 512 Oct 16 11:29 TT_DB
-rw------- 1 wlgml337 section 3446548 Dec 18 13:19 core
drwxr-x--- 2 wlgml337 section 512 Nov 11 16:33 cron
drwxr-x--- 2 wlgml337 section 512 Nov 15 10:52 test
drwxr-xr-x 6 wlgml337 section 512 Nov 11 11:45 work
Command Prompt#
The prompt can be modified by configuting other values instead of the fundamental command prompt 'iSQL>'. The SET SQLPROMPT dynamically replaces variables when including runtime variables, such as current accessed user, and current time.
SET SQLP[ROMPT] {text}
The followings are the substitution variables available for use.
| Variable | Description |
|---|---|
| _CONNECT_IDENTIFIER | The connected server. It is expressed with " host:port_no ". |
| _DATE | The current time. It is expressed through a specified format in the DATE_FORMAT. |
| _PRIVILEGE | This variable displays the iSQL access privilege. If it is connected with sysdba, '(sysdba)' is replaced. |
| _USER | The user name currently being connected. |
Example#
iSQL>SET SQLPROMPT "_CONNECT_IDENTIFIER> "
iSQL>SET SQLP "_USER> "
iSQL>SET SQLPROMPT "_USER'@'_CONNECT_IDENTIFIER > "
iSQL>SET SQLPROMPT "_USER on _DATE from _CONNECT_IDENTIFIER> "
Getting Help#
Help is available for the commands provided with iSQL. The HELP command without parameters outputs information on how to use help. For help on specific commands, enter HELP followed by the name of the command for which help is desired.
iSQL> HELP;
Use 'help [command]'
Enter 'help index' for a list of command
iSQL> HELP INDEX;
/ EXIT PARTITIONS
@ EXPLAINPLAN QUERYLOGGING
ALTER FEEDBACK QUIT
AUTOCOMMIT FOREIGNKEYS ROLLBACK
CHKCONSTRAINTS FULLNAME SAVE
CL[EAR] H[ISTORY] SELECT
COL[UMN] HEADING SPOOL
COLSIZE INSERT SQLP[ROMPT]
COMMIT LINESIZE START
CREATE LOAD TERM
DEFINE LOBOFFSET TIMESCALE
DELETE LOBSIZE TIMING
DESC MERGE UPDATE
DROP MOVE USER
ECHO NUM[WIDTH] VAR[IABLE]
EDIT NUMF[ORMAT] VERTICAL
EXECUTE PAGESIZE
iSQL> HELP EXIT;
exit;
or
quit; - exit iSQL
Using National Character Sets#
When using NCHAR and NVARCHAR type character sets, if the following environment variables settings are made, there will be no concerns over possible data loss.
The ALTIBASE_NLS_NCHAR_LITERAL_REPLACE environment variable must be set to1.
$ export ALTIBASE_NLS_NCHAR_LITERAL_REPLACE =1
In order to use NCHAR type data that are encoded differently from the database character set, enter the character “N” in front of the string.
iSQL> create table t1 (c1 nvarchar(10));
Create success.
iSQL> insert into t1 values (N'AB가나');
1 row inserted.
iSQL> select * from t1;
C1
------------------------
AB가나
1 row selected.