1. Using iSQL#

iSQL Overview#

iSQL is an user's 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: • ALTIBASE

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 startup and shutdown	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.
Database connection and disconnection	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.
	Display 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.
	Saving results to a file	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 declaration	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.
	Assign values to variables	EXEC :p2 := 'abc';	Assigns the text ‘abc’ to variable p2.
	Variable display	PRINT VAR[IABLE];	Shows the currently declared variables.
	Variable display	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>
	Repeat execution	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, Unix domain socket, SSL/TLS protocol, and Infiniband. 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.

ALTIBASE_UT_FILE_PERMISSION#

This common environment variable sets the permission for files created by aexport, iLoader, and iSQL.

If users do not specifiy this value, it is automatically set to 666 ( user:rw, group:rw, other: rw).

Example)

Desired Permission Setting: user:rw, group:--, other:--

export ALTIBASE_UT_FILE_PERMISSION=600

If ISQL_FILE_PERMISSION, AEXPORT_FILE_PERMISSION, or ILO_FILE_PERMISSION is set in advance, these properties take precedence over ALTIBASE_UT_FILE_PERMISSION.

Example)

export ALTIBASE_UT_FILE_PERMISSION=660;
export ISQL_FILE_PERMISSION=600;

In the example, the permission setting for files generated in iSQL adheres to the ISQL_FILE_PERMISSION=600, which grants only the user read and write permissions (user:rw, group:--, other:--). Note that the permission settings for files generated in aexport and iLoader still follow the ALTIBASE_UT_FILE_PERMISSION.

ISQL_FILE_PERMISSION#

This environment variable sets the permission for files created by iSQL. If users do not specify this value, it is automatically set to 666(user:rw, group:rw, other: rw).

Example)

Desired Permission Setting: user:rw, group:--, other:--

export ISQL_FILE_PERMISSION=600

ISQL_SECURE_LOGIN_MSG#

To reinforce security, this environment variable sets whether a detailed reason for login failure is displayed or not when users try logging in with the wrong user ID or password on iSQL. If users do not specify this value, it is automatically set to 0.

1: Error message "Invalid UserID or Password" is displayed.
0 or do not set this value: The specific reason for login failure is displayed.
Example

export ISQL_SECURE_LOGIN_MSG=1

export ISQL_SECURE_LOGIN_MSG=0

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.

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

Admin

General Reference

Developer

Tools

External Tools

1. Using iSQL#

iSQL Overview#

iSQL Main Functionality#

Setting up iSQL#

iSQL Command-Line Options#

iSQL Commands#

iSQL Environment Variables#

ALTIBASE_HOME#

ALTIBASE_PORT_NO#

ALTIBASE_SSL_PORT_NO#

ALTIBASE_NLS_USE#

ALTIBASE_NLS_NCHAR_LITERAL_REPLACE#

ISQL_CONNECTION#

ISQL_BUFFER_SIZE#

ALTIBASE_DATE_FORMAT#

ISQL_EDITOR#

ALTIBASE_IPC_FILEPATH#

IPCDA_FILEPATH#

ALTIBASE_TIME_ZONE#

ALTIBASE_UT_FILE_PERMISSION#

ISQL_FILE_PERMISSION#

ISQL_SECURE_LOGIN_MSG#

Personalizing iSQL#

glogin.sql#

login.sql#

Notes#

1. Using iSQL#

iSQL Overview#

iSQL Main Functionality#

Setting up iSQL#

iSQL Command-Line Options#

iSQL Commands#

iSQL Environment Variables#

ALTIBASE_HOME#

ALTIBASE_PORT_NO#

ALTIBASE_SSL_PORT_NO#

ALTIBASE_NLS_USE#

ALTIBASE_NLS_NCHAR_LITERAL_REPLACE#

ISQL_CONNECTION#

ISQL_BUFFER_SIZE#

ALTIBASE_DATE_FORMAT#

ISQL_EDITOR#

ALTIBASE_IPC_FILEPATH#

IPCDA_FILEPATH#

ALTIBASE_TIME_ZONE#

ALTIBASE_UT_FILE_PERMISSION#

ISQL_FILE_PERMISSION#

ISQL_SECURE_LOGIN_MSG#

Personalizing iSQL#

glogin.sql#

login.sql#

Editing the LOGIN file#

Notes#