Session Commands

In addition to SQL statements, CSQL Interpreter provides special commands allowing you to control the Interpreter. These commands are called session commands. All the session commands must start with a semicolon (;).

Session Commands

Enter the ;help command to display a list of the session commands available in the CSQL Interpreter. Note that only the uppercase letters of each session command are required to make the CSQL Interpreter to recognize it. Session commands are not case-sensitive.

CUBRID SQL Interpreter

Type `;help' for help messages.

csql> ;help

=== <Help: Session Command Summary> ===

   All session commands should be prefixed by `;' and only blanks/tabs

   can precede the prefix. Capitalized characters represent the minimum

   abbreviation that you need to enter to execute the specified command.

   ;REAd   [<file-name>]       - read a file into command buffer.

   ;Write  [<file-name>]       - (over)write command buffer into a file.

   ;APpend [<file-name>]       - append command buffer into a file.

   ;PRINT                      - print command buffer.

   ;SHELL                      - invoke shell.

   ;CD                         - change current working directory.

   ;EXit                       - exit program.

   ;CLear                      - clear command buffer.

   ;EDIT                       - invoke system editor with command buffer.

   ;List                       - display the content of command buffer.

   ;RUn                        - execute sql in command buffer.

   ;Xrun                       - execute sql in command buffer, and clear the command buffer.

   ;COmmit                     - commit the current transaction.

   ;ROllback                   - roll back the current transaction.

   ;AUtocommit [ON|OFF]        - enable/disable auto commit mode.    

   ;CHeckpoint                 - issue checkpoint.

   ;Killtran                   - kill transaction.

   ;REStart                    - restart database.

   ;SHELL_Cmd  [shell-cmd]     - set default shell, editor, print and pager

   ;EDITOR_Cmd [editor-cmd]      command to new one, or display the current

   ;PRINT_Cmd  [print-cmd]       one, respectively.

   ;PAger_cmd  [pager-cmd]

   ;DATE                       - display the local time, date.

   ;DATAbase                   - display the name of database being accessed.

   ;SChema class-name          - display schema information of a class.

   ;SYntax [sql-cmd-name]      - display syntax of a command.

   ;TRigger [`*'|trigger-name] - display trigger definition.

   ;Get system_parameter       - get the value of a system parameter.

   ;SEt system_parameter=value - set the value of a system parameter.

   ;PLan [simple/detail/off]   - show query execution plan.  

   ;Info <command>             - display internal information.

   ;TIme [ON/OFF]              - enable/disable to display the query execution time.

   ;HISTORYList                - display list of the executed queries.

   ;HISTORYRead <history_num>  - read entry on the history number into command buffer.

   ;HElp                       - display this help message.

csql>

Options

Reading SQL statements from a file (;REAd)

The ;REAd command reads the contents of a file into the buffer. This command is used to execute SQL commands stored in the specified file. To view the contents of the file loaded into the buffer, use the ;List command.

csql> ;rea nation.sql

The file has been read into the command buffer.

csql> ;list

insert into "sport_event" ("event_code", "event_name", "gender_type", "num_player") values

(20001, 'Archery Individual', 'M', 1);

insert into "sport_event" ("event_code", "event_name", "gender_type", "num_player") values

20002, 'Archery Individual', 'W', 1);

....

Saving SQL statements into a file (;Write)

The ;Write command stores the contents of the command buffer into a file. This command is used to store SQL commands that you entered or modified in the CSQL Interpreter.

csql> ;w outfile

Command buffer has been stored.

Appending to a file (;APpend)

This command appends the contents of the current command buffer to an outfile file.

csql> ;ap outfile

Command buffer has been stored.

Executing a shell command (;SHELL)

The ;SHELL session command calls an external shell. Starts a new shell in the environment where the CSQL Interpreter is running. It returns to the CSQL Interpreter when the shell terminates. If the shell command to execute with the ;SHELL_Cmd command has been specified, it starts the shell, executes the specified command, and returns to the CSQL Interpreter.

csql> ;shell

% Is -al

total 2088

drwxr-xr-x 16 DBA cubrid   4096 Jul 29 16:51 .

drwxr-xr-x  6 DBA cubrid   4096 Jul 29 16:17 ..

drwxr-xr-x  2 DBA cubrid   4096 Jul 29 02:49 audit

drwxr-xr-x  2 DBA cubrid   4096 Jul 29 16:17 bin

drwxr-xr-x  2 DBA cubrid   4096 Jul 29 16:17 conf

drwxr-xr-x  4 DBA cubrid   4096 Jul 29 16:14 cubridmanager

% exit

csql>

Registering a shell command (;SHELL_Cmd)

The ;SHELL_Cmd command registers a shell command to execute with the SHELL session command. As shown in the example below, enter the ;shell command to execute the registered command.

csql> ;shell_c ls -la

csql> ;shell

total 2088

drwxr-xr-x 16 DBA cubrid   4096 Jul 29 16:51 .

drwxr-xr-x  6 DBA cubrid   4096 Jul 29 16:17 ..

drwxr-xr-x  2 DBA cubrid   4096 Jul 29 02:49 audit

drwxr-xr-x  2 DBA cubrid   4096 Jul 29 16:17 bin

drwxr-xr-x  2 DBA cubrid   4096 Jul 29 16:17 conf

drwxr-xr-x  4 DBA cubrid   4096 Jul 29 16:14 cubridmanager

csql>

Changing the current working directory (;CD)

This command changes the current working directory where the CSQL Interpreter is running to the specified directory. If you don't specify the path, the directory will be changed to the home directory.

csql> ;cd /home1/DBA/CUBRID

Current directory changed to  /home1/DBA/CUBRID.

Exiting the CSQL Interpreter (;EXit)

This command exits the CSQL Interpreter.

csql> ;ex

Clearing the command buffer (;CLear)

The ;CLear session command clears the contents of the command buffer. 

csql> ;cl

csql> ;list

Displaying the contents of the command buffer (;List)

The ;List session command lists the contents of the command buffer that have been entered or modified. The command buffer can be modified by ;READ or ;Edit command.

csql> ;l

Executing SQL statements (;RUn)

This command executes SQL statements in the command buffer. Unlike the ;Xrun session command described below, the buffer will not be cleared even after the query execution. 

csql> ;ru

Clearing the command buffer after executing the SQL statement (;Xrun)

This command executes SQL statements in the command buffer. The buffer will be cleared after the query execution. 

csql> ;x

Committing transaction (;COmmit)

This command commits the current transaction. You must enter a commit command explicitly if it is not in auto-commit mode. In auto-commit mode, transactions are automatically committed whenever SQL is executed.

csql> ;co

Current transaction has been committed.

Rolling back transaction (;ROllback)

This command rolls back the current transaction. Like a commit command (;COmmit), it must enter a rollback command explicitly if it is not in auto-commit mode (OFF).

csql> ;ro

Current transaction has been rolled back.

Setting the auto-commit mode (;AUtocommit)

This command sets auto-commit mode to ON or OFF. If any value is not specified, current configured value is applied by default. The default value is ON.

csql> ;au off

AUTOCOMMIT IS OFF

CHeckpoint Execution (;CHeckpoint)

This command executes the checkpoint within the CSQL session. This command can only be executed when a DBA group member, who is specified for the custom option (-u user_name), connects to the CSQL Interpreter in system administrator mode (--sysadm).

Checkpoint is an operation of flushing all dirty pages within the current data buffer to disks. You can also change the checkpoint interval using a command (;set parameter_name value) to set the parameter values in the CSQL session. You can see the examples of the parameter related to the checkpoint execution interval (checkpoint_interval_in_mins and checkpoint_every_npages). For more information, see Logging-related Parameters.

csql> ;ch

Checkpoint has been issued.

Transaction Monitoring Or Termination (;Killtran)

This command checks the transaction status information or terminates a specific transaction in the CSQL session. This command prints out the status information of all transactions on the screen if a parameter is omitted it terminates the transaction if a specific transaction ID is specified for the parameter. It can only be executed when a DBA group member, who is specified for the custom option (-u user_name), connects to the CSQL Interpreter in system administrator mode (--sysadm).

csql> ;k

Tran index      User name      Host name      Process id      Program name

-------------------------------------------------------------------------------

      1(+)            dba      myhost             664           cub_cas

      2(+)            dba      myhost            6700              csql

      3(+)            dba      myhost            2188           cub_cas

      4(+)            dba      myhost             696              csql

      5(+)         public      myhost            6944              csql

 

csql> ;k 3

The specified transaction has been killed.

Restarting database (;REStart)

A command that tries to reconnect to the target database in a CSQL session. Note that when you execute the CSQL Interpreter in CS (client/server) mode, it will be disconnected from the server. When the connection to the server is lost due to a HA failure and failover to another server occurs, this command is particularly useful in connecting to the switched server while maintaining the current session.

csql> ;res

The database has been restarted.

Displaying the current date (;DATE)

The ;DATE command displays the current date and time in the CSQL Interpreter. 

csql> ;date

     Tue July 29 18:58:12 KST 2008

Displaying the database informatio (;DATAbase)

This command displays the database name and host name where the CSQL Interpreter is working. If the database is running, the HA mode (one of those followings: active, standby, or maintenance) will be displayed as well. 

csql> ;data

     demodb@localhost (active)

Displaying schema information of a class (;SChema)

The ;SChema session command displays schema information of the specified table. The information includes the table name, its column name and constraints. 

csql> ;sc event

=== <Help: Schema of a Class> ===

 <Class Name>

     event

 <Attributes>

     code           INTEGER NOT NULL

     sports         CHARACTER VARYING(50)

     name           CHARACTER VARYING(50)

     gender         CHARACTER(1)

     players        INTEGER

 <Constraints>

     PRIMARY KEY pk_event_event_code ON event (code)

Displaying syntax (;SYntax)

This command displays the syntax of the SQL statement specified. If there is no specific syntax specified, all the syntaxes defined and their rules will be displayed. 

csql> ;sy alter

=== <Help: Command Syntax> ===

 <Name>

   ALTER

 <Description>

Change the definition of a class or virtual class.

 <Syntax>

<alter> ::= ALTER [ <class_type> ] <class_name> <alter_clause> ;

<class_type> ::= CLASS | TABLE | VCLASS | VIEW

<alter_clause> ::= ADD <alter_add> [ INHERIT <resolution_comma_list> ] |

                   DROP <alter_drop> [ INHERIT <resolution_comma_list> ] |

                   RENAME <alter_rename> [ INHERIT <resolution_comma_list> ] |

>                   CHANGE <alter_change> |

                   INHERIT <resolution_comma_list>

<alter_add> ::= [ ATTRIBUTE | COLUMN ] <class_element_comma_list> |

                CLASS ATTRIBUTE <attribute_definition_comma_list> |

                FILE <file_name_comma_list> |

                METHOD <method_definition_comma_list> |

                QUERY <select_statement> |

                SUPERCLASS <class_name_comma_list>

......

Displaying the trigger (;TRriger)

This command searches and displays the trigger specified. If there is no trigger name specified, all the triggers defined will be displayed. 

csql> ;tr

=== <Help: All Triggers> ===

     trig_delete_contents

Checking the parameter value(;Get)

You can check the parameter value currently set in the CSQL Interpreter using the ;Get session command. An error occurs if the parameter name specified is incorrect. 

csql> ;g isolation_level

=== Get Param Input ===

isolation_level=4

Setting the parameter value (;SEt)

You can use the ;Set session command to set a specific parameter value. Note that changeable parameter values are only can be changed. To change the server parameter values, you must have DBA authorization. For information on list of changeable parameters, see cubrid_broker.conf Configuration File and Default Parameters.

csql> ;se block_ddl_statement=1

=== Set Param Input ===

block_ddl_statement=1

-- Dynamically change the log_max_archives value in the csql accessed by dba account

csql>;se log_max_archives=5

Setting the view level of executing query plan (;PLan)

You can use the ;PLan session command to set the view level of executing query plan the level is composed of simple, detail, and off. Each command refers to the following:

Displaying information (;Info)

The ;Info session command allows you to check information such as schema, triggers, the working environment, locks and statistics. 

csql> ;i lock

*** Lock Table Dump ***

 Lock Escalation at = 100000, Run Deadlock interval = 1

Transaction (index  0, unknown, unknown@unknown|-1)

Isolation REPEATABLE CLASSES AND READ UNCOMMITTED INSTANCES

State TRAN_ACTIVE

Timeout_period -1

......

Outputting statistics information of server processing (;.Hist)

This command shows the statistics information of server processing. The information is collected after this command is entered. Therefore, the execution commands such as ;.dump_hist or ;.x must be entered to output the statistics information

This command is executable while the communication_histogram parameter in the cubrid.conf file is set to yes. You can also view this information by using the cubrid statdump utility. Following options are provided for this session command.

This example shows the server statistics information for current connection. For information on specific items, see Outputting Statistics Information of Server.

csql> ;.hist on

csql> ;.x

Histogram of client requests:

Name                            Rcount   Sent size  Recv size , Server time

 No server requests made

 

 *** CLIENT EXECUTION STATISTICS ***

System CPU (sec)              =          0

User CPU (sec)                =          0

Elapsed (sec)                 =         20

 

 *** SERVER EXECUTION STATISTICS ***

Num_file_creates              =          0

Num_file_removes              =          0

Num_file_ioreads              =          0

Num_file_iowrites             =          0

Num_file_iosynches            =          0

Num_data_page_fetches         =         56

Num_data_page_dirties         =         14

Num_data_page_ioreads         =          0

Num_data_page_iowrites        =          0

Num_data_page_victims         =          0

Num_data_page_iowrites_for_replacement =          0

Num_log_page_ioreads          =          0

Num_log_page_iowrites         =          0

Num_log_append_records        =          0

Num_log_archives              =          0

Num_log_checkpoints           =          0

Num_log_wals                  =          0

Num_page_locks_acquired       =          2

Num_object_locks_acquired     =          2

Num_page_locks_converted      =          0

Num_object_locks_converted    =          0

Num_page_locks_re-requested   =          0

Num_object_locks_re-requested =          1

Num_page_locks_waits          =          0

Num_object_locks_waits        =          0

Num_tran_commits              =          1

Num_tran_rollbacks            =          0

Num_tran_savepoints           =          0

Num_tran_start_topops         =          3

Num_tran_end_topops           =          3

Num_tran_interrupts           =          0

Num_btree_inserts             =          0

Num_btree_deletes             =          0

Num_btree_updates             =          0

Num_btree_covered             =          0

Num_btree_noncovered          =          0

Num_btree_resumes             =          0

Num_query_selects             =          1

Num_query_inserts             =          0

Num_query_deletes             =          0

Num_query_updates             =          0

Num_query_sscans              =          1

Num_query_iscans              =          0

Num_query_lscans              =          0

Num_query_setscans            =          0

Num_query_methscans           =          0

Num_query_nljoins             =          0

Num_query_mjoins              =          0

Num_query_objfetches          =          0

Num_network_requests          =          8

Num_adaptive_flush_pages      =          0

Num_adaptive_flush_log_pages  =          0

Num_adaptive_flush_max_pages  =          0

 

 *** OTHER STATISTICS ***

Data_page_buffer_hit_ratio    =     100.00

csql> ;.h off

Displaying query execution time (;TIme)

The ;TIme session command can be set to display the elapsed time to execute the query. It can be set to ON or OFF. The current setting is displayed if there is no value specified.

csql> ;ti ON

csql> ;ti

TIME IS ON

Displaying query history (;HISTORYList)

This command displays the list that contains previously executed commands (input) and their history numbers. 

csql> ;historyl

----< 1 >----

select * from nation;

----< 2 >----

select * from athlete;

Reading input with the specified history number into the buffer (;HISTORYRead)

You can use ;HISTORYRead session command to read input with history number in the ;HISTORYList list into the command buffer. You can enter ;ru or ;x directly because it has the same effect as when you enter SQL statements directly. 

csql> ;historyr 1

Calling the default editor (;EDIT)

This command calls the specified editor. The default editor is vi on Linux Notepad on Windows environment. Use ;EDITOR_Cmd command to specify a different editor. 

csql> ;edit

Specifying the editor (;EDITOR_Cmd)

This command specifies the editor to be used with ;EDIT session command. As shown in the example below, you can specify other editor (ex: emacs) which is installed in the system.

csql> ;editor_c emacs

csql> ;edit