1. Using iSQL#
iSQL Overview#
iSQL is a user tool for accessing an Altibase and retrieving and modifying stored data using SQL statements and a number of additional commands.
iSQL Main Functionality#
-
Altibase Startup and Shutdown
iSQL allows users to perform database management tasks, such as starting up and shutting down the server, and execute SQL statements using the same command prompt. -
Database Connection & Disconnection
After Altibase starts up, users can use various user names to connect to and disconnect from the database. -
Database Object Information Inquiry
iSQL allows users to use SQL statements to query all database object information, and supports convenient commands for inquiring about main objects. -
Database Management via SQL Statements
Because iSQL can be used to execute any kind of SQL statement, users can control transactions and alter databases quickly and conveniently. -
Functions to Improve User Convenience
The above tasks can be easily and conveniently accomplished using the file management and editing functions, the ability to execute shell commands over iSQL, and the HISTORY function.
Setting up iSQL#
In order for iSQL to access a server, the following information is necessary.
-
ALTIBASE_HOME
A path to a server or client installation -
server_name
The name (or IP address) of a computer on which Altibase Server is running -
port_no
The port number used when connecting via TCP, IPC or IPCDA -
user_id
A User ID registered in the database -
password
The password corresponding to the User ID -
NLS_USE
The character set with which to display retrieved data to the user
ALTIBASE_HOME can only be set using an environment variable, while the other settings may be made using command-line options. (For more information, please refer to iSQL Command-Line Options.)
ALTIBASE_HOME environment variable must be set in order to use iSQL. Although it is automatically configured when the server is installed in general, but the user should directly configure since there might be a chance of conflict with the environment variables in the server.
port_no and NLS_USE can be set using the environment variables or the server settings file (altibase.properties). If these settings are made via all three methods, they will take priority as follows, in descending order:
- command-line options
- environment variables (ALTIBASE_PORT_NO, ALTIBASE_NLS_USE)
- server settings file (altibase.properties)
Therefore, when it is desired to connect using options other than those that have been previously set, the command-line options can be used, so that it is not necessary to change the settings in the server setting file or the environment variables.
If any options have not been set, when iSQL is executed for the first time, the user will be prompted to enter the corresponding variables. At this time, it is essential to enter values that are valid and follow the proper format, otherwise iSQL may not run properly.
However, if the NLS_USE option in particular has not been set, no command prompt will appear at the time of execution. Instead, US7ASCII will be used, and a connection attempt will be made. In this case, if the character set of the database is not US7ASCII, the application will not execute properly, or some of the user’s data may become corrupted. Thus it is paramount that NLS_USE be set to a suitable value for the usage environment.
In order to ensure stable iSQL operation, we recommend that the following environment variables be set: • ALTIBAS
- ALTIBASE_HOME : the path to a server or client installation
- ALTIBASE_PORT_NO : the port number to use to connect to the server
- ALTIBASE_NLS_USE : the character set to use to display retrieved data to the user
- PATH : the path containing the executable file, which must equal $ALTIBASE_HOME/bin
iSQL Command-Line Options#
The Altibase server must be started before iSQL is executed. The following options are case-insensitive.
isql
[-H]
[-S server_name]
[-PORT port_no]
[-U user_id] [-P password] [/NOLOG]
[-SYSDBA]
[-UNIXDOMAIN-FILEPATH filepath]
[-IPC-FILEPATH filepath]
[-SILENT]
[-F infile_name [param1 [param2]...]] [-O outfile_name] [-NLS_USE nls_name]
[-NLS_NCHAR_LITERAL_REPLACE 0|1]
[-prefer_ipv6] [-TIME_ZONE timezone]
[-ssl_ca CA_file_path | -ssl_capath CA_dir_path]
[-ssl_cert certificate_file_path]
[-ssl_key key_file_path]
[-ssl_verify]
[-ssl_cipher cipher_list]
- -S server_name This option specifies the name (or IP address) of a computer on which Altibase Server is running.
If connection is attempted while the ISQL CONNECTION environment variable is set to IPC or UNIX, and the remote server is specified for this option, iSQL ignores the ISQL CONNECTION specification and connects to the remote server via TCP, and outputs a warning message that the ISQL CONNECTION specification has been ignored. It can be a host name, an IPv4 address, or an IPv6 address. An IPv6 address must be enclosed by a left square bracket([) and a right square bracket(]). For example, in the case of localhost (meaning this computer), localhost can be specified as the host name, 127.0.0.1 as the IPv4 address, or [::1] as the IPv6 address.
For more information about the IPv6 address notation, please refer to the Altibase Administrator's Manual.
- -PORT port_no This option specifies the port number for connecting via TCP, IPC or IPCDA. However, when connecting in a Unix environment via IPC, this option must not be specified.
After a warning message is output, connection to the server is made. To connect via TCP, first set 'ISQL_CONNECTION=TCP' on the client and then enter the PORT_NO. If the environment variable ISQL_CONNECTION is not set to IPC and the -PORT option is omitted, ALTIBASE_PORT_NO and PORT_NO property is referred in sequence. However, the prompt for port number input is output if all is not specified.
-
-U user_id This option specifies a user ID registered in the database.
-
-P password This option specifies the password corresponding to the user ID.
-
/NOLOG This executes iSQL without connecting to the database.
-
-SYSDBA This allows the SYS user to execute iSQL in SYSDBA mode. If the server has not yet started, iSQL connects as an idle instance and allows the user to start the server.
-
-UNIXDOMAIN-FILEPATH filepath
When a server and client connect using a Unix domain socket in a Unix environment (ISQL_CONNECTION=UNIX), the connection will fail if the server and client have different values for ALTIBASE_HOME and also have different Unix domain socket paths. In this case, if the server and client use corresponding files (e.g. ALTIBASE_HOME/trc/cm-unix), Unix domain communication is possible. -
-IPC-FILEPATH filepath
When the client and the server connect via IPC(ISQL_CONNECTION=IPC) in a Unix environment, if ALTIBASE_HOME is set differently on them, they will not be able to connect because they have different socket paths. In this case, Unix domain communication can be achived using the ALTIBASE_HOME/trc/cm-ipc file, and then information about shared memory can be retrieved. However, this option can be omitted if ALTIBASE_IPC_FILEPATH is set. -
-IPCDA-FILEPATH filepath
If ALTIBASE_HOME is different from each other when attempting to connect the client and server via IPCDA (ISQL_CONNECTION=IPCDA), the connection cannot be made due to different socket paths Howeve, if ALTIBASE_HOME/trc/cm-ipcda file is used, the Unix domain communication is enabled to bring the information of shared memory. However, this option can be omitted if IPCDA_FILEPATH of environment variables is specified. -
-F infile_name [param1 [param2]...]
This command option specifies a script file to be executed immediately after iSQL is launched. Use double quotation marks if the file name contains special characters or spaces.
Ex) -F \" file name\"
This command also can specify a paramater value which will be substituted for a substitution variable in the script file. Refer to the 'Passing parameters through START command' for more information regarding the substitution variables. -
-O outfile_name This command option specifies a file in which to store the results of the excuted iSQL commands. This file will be created in the current directory. If the file already exists, it will be overwritten.
Use double quotation marks if the file name contains special characters or spaces.
Ex) -O \" file name\"
-
-H This option outputs help information for iSQL execution.
-
-SILENT s This option turns on silent mode. If silent mode is on, noncritical messages, such as the copyright notice, etc. will not be displayed.
-
-NLS_USE Character set to display to user when searching data. This specifies the encoding of the terminal running iSQL. If omitted, the environment variable ALTIBASE_NLS_USE will be referred to, followed by altibase.properties. If not set, the default charset (US7ASCII) is used.
-
US7ASCII
- KO16KSC5601
- MS949
- BIG5
- GB231280
- MS936
- UTF8
- SHIFTJIS
-
MS932
-
EUCJP-NLS_NCHAR_LITERAL_REPLACE
0 : convert all strings to the database character set without checking for the "N" character.
1 : do not convert strings that are preceded by the "N" character to the database character set -
-prefer_ipv6 This option determines the IP address to be connected first when a host name is given for the -s option.
If this option is specified and a host name is given for the -s option, this means that resolving the host name to the IPv6 address is prefered. If this option is omitted, iSQL connects to the IPv4 address by default. If it fails to connect to the prefered IP version address, an attempt is made to connect using the other IP version address.
For example, when localhost is given for the -s option and this option is specified, iSQL first tries to connect to the [::1] IPv6 address. If this attempt fails, iSQL proceeds to connect to the 127.0.0.1 IPv4 address. -
-TIME_ZONE timezone
This option sets the time zone of the client. If DB_TZ is specified for this option, the time zone is defaulted to that of the database server. Time zone names like Asia/Seoul, abbreviations such as KST and UTC offset values as +09:00 are valid for specification.
If this option is omitted, the time zone set for the ALTIBASE_TIME_ZONE environment variable is defaulted to the time zone of the client; on omission of the environment variable, the time zone is defaulted to that of the database server. -
-ssl_ca CA_file_path
This specifies the location of the certification authority (CA) certificate in which the public key of the Altibase server to be connected to is incorporated. -
-ssl_capath CA_dir_path
This specifies the directory under which the certification authority (CA) certificate in which the public key of the Altibase server to be connected is incorporated. -
-ssl_cert certificate_file_path
This specifies the location of the client authentication file. -
-ssl_key key_file_path
This specifies the location of the client private key file. -
-ssl_verify This verifies the certificate the client receives from the server.
-
-ssl_cipher cipher_list
This specifies a cipher list for SSL encryption. Please refer to the SSL_CIPHER_LIST property in the General Reference.
If any of the -S, -U, or -P options are missing from the above command, the user will be prompted to input the option values.
iSQL Commands#
When iSQL is started, an iSQL command prompt will appear, and when iSQL commands are entered, the results of execution will be displayed. The iSQL commands are described individually in the following table.
| Category | Type | Command | Description |
|---|---|---|---|
| iSQL startup and shutdown | Startup | $ isql [option] | If you execute this command in a shell, iSQL will start up. For information on the available options, please refer to the iSQL Command-Line Options section. |
| Prompt | iSQL> | Type a command at the iSQL prompt and press the ENTER key. | |
| Shutdown | EXIT; QUIT; | Used to shut down iSQL. | |
| Altibase startup and shutdown | Altibase Startup | STARTUP | Use the PRE-PROCESS, PROCESS, CONTROL, META, or SERVICE option to start Altibase up to the corresponding stage. |
| Altibase Shutdown | SHUTDOWN | Use one of the NORMAL, IMMEDIATE, or ABORT options to shut down Altibase. | |
| Database connection and disconnection | Access the server as another user | CONNECT [logon] [nls] [AS sysdba]; logon:user1/pass1 nls: NLS=character_set | This command allows access to the database as user1 with password pass1 after having already accessed the database as another user in iSQL. If CONNECT is successful, the information related to the previous session is cleared.The AS clause allows the SYS user to access the server in sysdba manager mode. Only one user is allowed to connect as sysdba at a time. The nls option specifies the character set. For detailed information on character sets, please refer to the iSQL Command-Line Options: -NLS_USE option. |
| Terminate a connection | DISCONNECT; | Ends the current session and terminates the connection with the server. | |
| Database object information inquiry | Display performance view list | ELECT * FROM V$TAB; | Displays the list of all of the performance views provided by the system. This command is available only in iSQL. |
| View table list | SELECT * FROM TAB; | Displays the list of currently created tables. This command is only available in iSQL. | |
| Display table list | DESC samp; | Lists the column definitions for the table samp | |
| Display sequence Information | SELECT * FROM SEQ; | If you accessed the server with the SYS account, information on all sequences is displayed.If you accessed the server as another user, only the information on the sequences generated by that user will be displayed. This command is available only in iSQL. | |
| File management | Saving results to a file | SPOOL filename; | Starts writing the results of executed command in iSQL to the file file_name. |
| SPOOL OFF; | Stops spooling. | ||
| SQL script execution | START file_name; | Reads a script file and executes the SQL statements in sequence. | |
| @ file_name; | Performs a function similar to that of startup when executed via an iSQL prompt. | ||
| @@ file_name; | When used in a script, this command executes the file file_name in the same directory as the calling script. | ||
| Save SQL statement to file | SAVE abc.sql; | Saves the last of the commands currently in the iSQL buffer to a file. | |
| Load SQL statement | LOAD abc.sql; | Loads the first of the commands saved in a file at the end of the command buffer. | |
| Save DML statements to file | SET QUERYLOGGING ON; SET QUERYLOGGING OFF; | This writes executed DML statements, such as INSERT, UPDATE, DELETE and MOVE, in $ALTIBASE_HOME/trc/isql_query.log. | |
| Edit query statements | ED[IT] | This command edits the most recently executed query. | |
| ED[IT] filename[.sql] | This command edits existing files or new files. | ||
| 2ED[IT] or 2 ED[IT] | This edits the query statements with the number 2 in the history list. | ||
| Control output option | Format SELECT result column | SET LINESIZE 100; | Sets the length of a display line for outputting the result of a SELECT query.
Must be between 10 and 32767 inclusive. Default: 80 |
| SET LOBSIZE 10; | Sets the number of characters to display
when a CLOB column is output. Default: 80 |
||
| SET LOBOFFSET 3; | Sets the number of characters by which
to offset the display when a CLOB column is output. Default: 0 |
||
| SET FEED[BACK] ON; SET FEED[BACK] OFF; SET FEED[BACK] n; | Determines whether to output the number of rows in a query result. | ||
| SET PAGESIZE 10; | Sets how many records of a SELECT
query result are output at one time.
When set to 0, all resultant records are
output. Default: 0 |
||
| SET HEADING ON; SET HEADING OFF; | Sets whether to output the header of a
SELECT result Default: ON |
||
| SET COLSIZE N; | Sets the number of characters to output when CHAR or VARCHAR type columns are output as a SELECT query result. | ||
| SET NUM[WIDTH] N; | Sets the number of characters to output
when data of NUMERIC, DECIMAL,
NUMBER, FLOAT type columns are
output as a SELECT query result. Default: 11 |
||
| CL[EAR] COL[UMNS] | This command releases the column format which has been specified with the COLUMN. | ||
| COL[UMN] [{column | expr} [option]] | This command verifies and configures the display format for a SELECT target column. | ||
| SET NUMF[ORMAT] format; | This command sets the display format of SELECT results of NUMERIC, DECIMAL, NUMBER, and FLOAT type. | ||
| Show SQL statement execution time | SET TIMING ON; SET TIMING OFF; | Sets whether to output the amount of
time taken to execute a SQL command. Default: OFF |
|
| Set the SQL statement execution time units for output | SET TIMESCALE SEC; SET TIMESCALE MILSEC; SET TIMESCALE MICSEC; SET TIMESCALE NANSEC; | Sets the unit of time for executing SQL statements as seconds, milliseconds, microseconds or nanoseconds. | |
| Show/hide CHECK constraint information | SET CHKCONSTRAINTS ON; Sets whether to output CHECK constraint output including information when displaying the table structure(using DESC). Default: OFF |
||
| Show/hide foreign key information | SET FOREIGNKEYS ON; SET FOREIGNKEYS OFF; | Determines whether to include foreign
key information in the output when displaying the table structure (using DESC). Default: OFF |
|
| Show/hide partition information | SET PARTITIONS ON; SET PARTITIONS OFF; | Determines whether to include partition
information in the output when displaying the table structure (using DESC). Default: OFF |
|
| Show/hide script execution result | SET TERM ON; SET TERM OFF; | Determines whether to display the results
of execution of a script file on the screen. Default: ON |
|
| Show/hide script commands | SET ECHO ON; SET ECHO OFF; | Option to output the commands in the script file executed by @. Default : ON |
|
| Replace Substitution Variable | SET DEFINE ON; SET DEFINE OFF; | This command specifies whether or not
to replace substitution variables with
parameter values inserted by a user when
executing a script file containing substitution variables. Default: OFF |
|
| Display contents before/after replacing substitution variable | SET VERIFY ON; SET VERIFY OFF; | This command specifies whether or not
to display SQL statements before and
after the substitution variables are replaced with the parameter values
when executing a script file containing
substitution variables. Default: ON |
|
| Output executionplan tree | ALTER SESSION SET EXPLAIN PLAN = ON; ALTER SESSION SET EXPLAIN PLAN = ONLY; ALTER SESSION SET EXPLAIN PLAN = OFF; | Determines whether to output an execution plan for a SELECT statement. Default: OFF |
|
| SELECT result output direction | SET VERTICAL ON; SET VERTICAL OFF; | Displays SELECT results vertically
when set to ON. Default: OFF |
|
| Show value of iSQL display settings | SHOW LINESIZE | Displays the current LINESIZE value. | |
| SHOW COLSIZE | Displays the current COLSIZE value. | ||
| SHOW LOBOFFSET | Displays the current LOBOFFSET value. | ||
| SHOW LOBSIZE | Displays the current LOBSIZE value. | ||
| SHOW PAGESIZE | Displays the current PAGESIZE value. | ||
| SHOW PLANCOMMIT | Shows whether PLANCOMMIT is ON or OFF. | ||
| SHOW QUERYLOGGING | DML Shows whether DML statements wil be written to ALTIBASE_HOME/trc/isql_query.log when executed. | ||
| SHOW FEEDBACK | Shows the current FEEDBACK value. | ||
| SHOW HEADING | Shows the current HEADING setting. | ||
| SHOW TERM | Shows the current TERM setting. | ||
| SHOW ECHO | Shows the current ECHO setting. | ||
| SHOW TIMING | Shows the current TIMING setting. | ||
| SHOW TIMESCLAE | This shows the current time units for the execution of SQL statements. | ||
| SHOW USER | Shows the current user. | ||
| SHOW CHKCONSTRAINTS | Shows whether the current CHECK constraint is set or not. | ||
| SHOW FOREIGNKEYS | Shows the current foreign key display setting. | ||
| SHOW PARTITIONS | Shows whether the current partition display is set or not. | ||
| SHOW VERTICAL | Shows whether the results of a SELECT query will be output vertically. | ||
| SHOW ALL | Shows the set values of the display settings for the current session. | ||
| Variable and Prepared SQL statements | Variable declaration | VAR p1 INTEGER; | Declares the variable p1 as integer type. |
| VARIABLE p2 CHAR(10); | Declares the variable p2 as CHAR type. | ||
| Assign values to variables | EXECUTE :p1 := 100; | Assigns the value 100 to variable p1. | |
| EXEC :p2 := 'abc'; | Assigns the text ‘abc’ to variable p2. | ||
| Variable display | PRINT VAR[IABLE]; | Shows the currently declared variables. | |
| PRINT p1; | Shows the type and value of variable p1. | ||
| Prepared SQL statement execution | PREPARE SQL statement ; | Separates the processes of query optimization and execution, and executes the query as a prepared SQL statement. In iSQL, the default execution method for executing SQL statements is the Direct Execution method, in which optimization and execution are performed at once.There is no difference between the two execution methods in iSQL in terms of the results obtained, however, prepared SQL statements can be used to bind variables to values and execute SQL statements based thereon. | |
| Functions for user convenience | Historylist display | HISTORY; H; | Shows a list of the commands currently saved in the iSQL buffer. |
| Repeat execution | / | Repeats execution of the command currently in the iSQL buffer. The most recently executed command will be executed again./TD> | |
| 2/ | Executes the second command in a list output using the HISTORY command. | ||
| Shell command execution | ! shell command | A shell command that follows an exclamation point will be immediately executed from within iSQL. | |
| Command prompt change | SET SQLP[ROMPT] {text} | This configures the iSQL command prompt. | |
| Comment | /* comment */ -- comment | Indicate a multiple-line comment and a single-line comment, respectively. | |
| Help | HELP; HELP INDEX; HELP EXIT; | This provides information on how to use help, outputs a list of commands, and describes (e.g.) the EXIT command, respectively. |
iSQL Environment Variables#
ALTIBASE_HOME#
ALTIBASE_HOME is the environment variable which must be configured in order to used iSQL.
Although it is automatically configured when the server is installed in general, but the user should directly configure since there might be a chance of conflict with the environment variables in the server.
ALTIBASE_PORT_NO#
This is the port number of the server to connect to. This can be specified either by using the -PORT option or in altibase.properties.
If no designated port number can be found (in descending order of precedence) in the -PORT option, in the environment variable ALTIBASE_PORT_NO, or in altibase.properties, a prompt to enter the port number will appear.
ALTIBASE_SSL_PORT_NO#
The port number of the server iSQL is to connect to on SSL/TLS.
The -PORT option, environment variables, ALTIBASE_SSL_PORT_NO, the properties in the altibase.properties file take priority in this order as the port number in SSL. On omission, the command prompt asks the user to enter the port number.
ALTIBASE_NLS_USE#
This is the character set used to display retrieved results to the user.
-
US7ASCII
-
KO16KSC5601
-
MS949
-
BIG5
-
GB231280
-
MS936
-
UTF8
-
SHIFTJIS
-
MS932
-
EUCJP
This can be set either using the -NLS_USE option or in altibase.properties.
If NLS_USE is not specified using the -NLS_USE option, the environment variable ALTIBASE_NLS_USE, or altibase.properties (in descending order of precedence), US7ASCII is used as the default character set.
ALTIBASE_NLS_NCHAR_LITERAL_REPLACE#
By default, iSQL converts an entire query string to the database character set before sending the data to the database. This behavior can be prevented for a given string literal by setting this property to 1 and placing the "N" character in front of the string literal.
A property setting of 1 instructs iSQL to search for the "N" character in front of every string literal. If the "N" character is found, iSQL sends the string to the database without converting it to the database character set. This is useful when it is desired to use NCHAR type data that are encoded differently from the database character set.
-
0: convert all strings to the database character set without checking for the "N" character
-
1: do not convert strings that are preceded by the "N" character to the database character set
Note: Setting this variable to 1 can be expensive in terms of usage of client resources.
ISQL_CONNECTION#
When Altibase is operated with a client-server arrangement, the user can select the client-server protocol that is suitable for the operating environment by setting environment variables. Altibase supports the TCP/IP, IPC, IPCDA, and Unix domain SSL socket protocols. The default protocol for communication with Altibase servers is TCP/IP.
- TCP
- UNIX
- IPC
- IPCDA
- SSL
- IB
Note that when using the IPC or IPCDA protocol the value of Altibase properties related to the IPC channel (IPC_CHANNEL_COUNT or IPCDA_CHANNEL_COUNT) must be considered.
The following example shows how to set the environment variable when using the IPC protocol:
CSH: setenv ISQL_CONNECTION IPC
SH: ISQL_CONNECTION=IPC; export ISQL_CONNECTION
Note: If the value set for the ISQL_CONNECTION environment variable is UNIX or IPC, and the remote server is specified for the -s option, a warning message that the setting for ISQL_CONNECTION has been ignored is output and iSQL connects to the remote server using TCP.
ISQL_BUFFER_SIZE#
The size of the buffer in which to store queries can be set using this environment variable.
CSH: setenv ISQL_BUFFER_SIZE 128000
SH: ISQL_BUFFER_SIZE = 128000; export ISQL_BUFFER_SIZE
ALTIBASE_DATE_FORMAT#
When retrieving Date type data using a SELECT statement, the environment variable ALTIBASE_DATE_FORMAT can be used to change the default date format, which is YYYY/MM/DD HH:MI:SS, to some other date format.
Ex) For Born, Korn, or Bash Shell
export ALTIBASE_DATE_FORMAT=’DD-MON-YYYY’
ISQL_EDITOR#
This environment variable can be used to change the default editor (Ex: /bin/vi ).
CSH: setenv ISQL_EDITOR /usr/bin/ed
SH: ISQL_EDITOR=/usr/bin/ed; export ISQL_EDITOR
ALTIBASE_IPC_FILEPATH#
In a Unix environment, if a client and the server have different values for ALTIBASE_HOME, they will not be able to connect via IPC since they have different Unix domain socket paths. In this case, in order to be able to connect via IPC, it is necessary to set the ALTIBASE_IPC_FILEPATH environment variable or the -IPC-FILEPATH iSQL option to the $ALTIBASE_HOME/trc/cm-ipc file used by the server.
IPCDA_FILEPATH#
In a Unix environment, if a client and the server have different values for ALTIBASE_HOME, they will not be able to connect via IPCDA since they have different Unix domain socket paths. In this case, if IPCDA_FILEPATH environment variables or –IPCDA -FILEPATH is specified as a file of $ALTIBASE_HOME/trc/cm-ipcda in the server connection via IPCDA is possible because the server and client can use the identical socket file.
ALTIBASE_TIME_ZONE#
This environment variable sets the time zone of the client. If DB_TZ is specified for this option, the time zone is defaulted to that of the database server.
This environment variable can be set with time zone names like Asia/Seoul, abbreviations such as KST and UTC offset values as +09:00 are valid for specification.
Personalizing iSQL#
iSQL users can customize their iSQL environment and use the same settings for each session. For example, using the OS file, the user can specify a desired output format so that each query result displays the current time whenever query results are output. These files can be categorized into the following two types.
glogin.sql#
For initialization tasks that must be conducted when iSQL is started, iSQL supports the creation of a global script file, glogin.sql, by the DB administrator. iSQL executes this script whenever any user executes iSQL or attempts to connect to Altibase for the first time. The global file allows the DB administrator to make site-specific iSQL environment settings for all users. The global script file is located in $ALTIBASE_HOME/conf.
login.sql#
iSQL also supports the login.sql file, which is executed after glogin.sql. If both the glogin.sql file and the login.sql file exist, login.sql is executed after glogin.sql during iSQL startup, so the commands in login.sql will take precedence.
If several people share one Unix account, it will be impossible for them to personalize the glogin.sql file. In this case, individual users may add SQL commands, stored procedures, or iSQL commands to their respective login.sql files in their personal work directories. When a user starts up iSQL, iSQL automatically searches the current directory for the login.sql file and executes the commands in it.
The login.sql file cannot modify initial iSQL settings or individual session actions.
Editing the LOGIN file#
The user may change the LOGIN file, like any other script. The following is an example of user1 creating a LOGIN file that turns off autocommit mode and executes SQL statements:
$ vi glogin.sql
AUTOCOMMIT ON
SET HEADING OFF
SELECT sysdate FROM dual;
$ vi login.sql
AUTOCOMMIT OFF;
SET HEADING ON
DROP TABLE savept;
CREATE TABLE savept(num INTEGER);
INSERT INTO savept VALUES(1);
SAVEPOINT sp1;
INSERT INTO savept VALUES(2);
SELECT * FROM savept;
ROLLBACK TO SAVEPOINT sp1;
SELECT * FROM savept;
COMMIT;
$ 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 : user1
Write Password :
ISQL_CONNECTION = TCP, SERVER = 127.0.0.1, PORT_NO = 20300
Set autocommit on success. -> Executing glogin.sql first
28-DEC-2004 -> heading off
1 row selected.
Set autocommit off success. -> Execute login.sql in the current work directory of the user after
glogin.sql is executed.
Drop success.
Create success.
1 row inserted.
Savepoint success. -> It is executable only when Autocommit mode is off
1 row inserted.
NUM -> heading on
--------------
1
2
2 rows selected.
Rollback success.
SAVEPT.NUM
--------------
1
1 row selected.
Commit success.
Notes#
For security reasons, the CONNECT command which inputs both the user name and password cannot be used with the LOGIN file. If the CONNECT command is included in the LOGIN file, the following warning message is output and the command is not executed.
WARNING: CONNECT command in glogin.sql file ignored