iSQL Commands

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.
	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.
		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; SET CHKCONSTRAINTS OFF;	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.