tbSQL

This chapter describes the tbSQL utility and its usage.

Overview

tbSQL is an interactive Tibero utility that processes SQL statements. It allows to process SQL queries, Data Definition Language (DDL), and transactional SQL statements and to create and execute PSM programs. It also allows DBAs to execute commands to manage Tibero systems.

In addition, tbSQL provides many other functions, such as auto commit setting, OS command execution, output saving, and script creation. Especially, you can create a script including multiple SQL statements, PSM programs, and tbSQL utility commands.

tbSQL is one of the most frequently used Tibero utilities. It provides the following functions.

• Inputs, edits, saves, and executes general SQL statements and PSM programs

• Sets and ends transactions

• Executes batch processing through a script

• Allows DBAs to manage databases

• Starts and ends databases

• Executes external utilities and programs

• Configures the tbSQL utility.

---

Quick start

tbSQL is automatically installed and uninstalled along with Tibero.

Execution

The following executes tbSQL.

Executing tbSQL

tbsql

tbSQL 7

TmaxData Corporation Copyright (c) 2008-. All rights reserved.

When tbSQL is executed successfully, the SQL prompt is displayed. You can enter and execute SQL statements at the prompt.

The following is the command syntax for executing tbSQL.

Usage

tbsql [[options]|[connect_string]|[start_script]]

• [options]

Option	Description
-h, --help	Displays help information.
-v, --version	Displays the version.
-s, --silent	Does not display the start message and prompt.
-i, --ignore	Does not execute the login script (tbsql.login).

• [connect_string]

Information about a user account that attempts to access Tibero. It is specified as follows:

username[/password[@connect_identifier]]

The following describes each item for connect_string.

Item	Description
username	User name. It is not case-sensitive except when it is enclosed in double quotation marks (" ").
password	User password. It is not case-sensitive except when it is enclosed in single quotation marks (' ').
connect_identifier	Either Data Source Name (DSN) that contains database connection information or a connection description that complies with predefined rules.

• [start_script]

Script file to execute when tbSQL starts. It is specified as follows:

@filename[.ext] [parameter ...]

The following describes each item for start_script.

Item	Description
filename	File name.
ext	File extension. If not set, the extension set in the SUFFIX system variable is used by default.
parameter	Substitution variables used in the file.

Database Connection

The SQL prompt displayed after executing tbSQL means that it is ready to connect to a database.

If there are any tasks to process before starting the database session, create the tbsql.login file. This file is searched in the current directory. If not found, it is searched in the directory set in the TB_SQLPATH environment variable.

The following connects to a database by using tbSQL.

Connecting to Database

$ tbsql SYS/syspassword

tbSQL 7

TmaxData Corporation Copyright (c) 2008-. All rights reserved. 

Connected.

The previous example executes tbSQL and connects to a database with specified username and password in the UNIX shell prompt.

There are the following rules for username and password.

Item	Description
Username	Not case-sensitive like schema object names. However, usernames enclosed in double quotation marks (" ") are case-sensitive.
Password	Not case-sensitive. However, passwords enclosed in single quotation marks (' ') are case-sensitive.

If connect_identifier is omitted as in the previous example, the connection is made to the default database. To connect to a specific database, specify connect_identifier in one of the following ways.

• Data Source Name (DSN) Specify the name defined in the tbdsn.tbr file or ODBC data source in Windows.

The following is an example.

tibero7=(
    (INSTANCE=(HOST=192.168.36.42) 
              (PORT=8629)
              (DB_NAME=tibero7)
          )
)

If tbdsn.tbr includes the previous information, connect to the database as follows:

$ tbsql tibero/tmax@tibero7

• Connection description Specify connection information in one of the following two methods.

Method 1

(INSTANCE=(HOST=host)(PORT=port)(DB_NAME=dbname))

Example

$ tbsql 'tibero/tmax@(INSTANCE=(HOST=192.168.36.42)(PORT=8629)(DB_NAME=tibero7))'

Method 2

host:port/dbname

Example

$ tbsql 'tibero/[email protected]:8629/tibero7'

Interface

The following shows the interface between users and tbSQL.

$ tbsql 

tbSQL 7

TmaxData Corporation Copyright (c) 2008-. All rights reserved.

SQL> CONNECT dbuser
Enter password : dbuserpassword 
Connected to Tibero.

In the previous example, after tbSQL is executed, a connection is made to a database by using the CONNECT command with the username, dbuser.

Like this, tbSQL is a text-based user interface utility.

In this document, all SQL statements, PSM programs, and tbSQL commands, with some exceptions, are written in uppercase letters.

(Command parameters written in lowercase letters are specified by the user.)

The tbSQL interface works as follows:

• When tbSQL is executed successfully, the SQL prompt is displayed. At the SQL prompt, you can enter SQL statements, PSM programs, and tbSQL commands.

• Input can span multiple lines. SQL statements and PSM programs can be not executed when entered, but tbSQL commands are executed when entered.

• Not case-sensitive. Input text is not case-sensitive, with some exceptions such as a string value in an SQL statement.

For example, the following two statements have the same meaning.

SQL> SET AUTOCOMMIT ON
SQL> set autocommit on

Environment Configuration

To configure tbSQL environment, use the SET command. You can set the output format of SQL query results, transaction commit option, and other environment options.

The following is the syntax of the SET command.

SET [system_variable] [system_variable_value]

For detailed information, refer to “System Variables”.

End

To end tbSQL, use the EXIT or QUIT command at the SQL prompt.

SQL> EXIT

For detailed information about tbSQL commands, refer to “tbSQL Commands”.

---

System Variables

This section describes system variables for tbSQL. To set the system variables, use the SET command, and to display the results, use the SHOW command.

The following describes each system variable.

System Variable	Default Value	Description
AUTOCOMMIT	OFF	Executes a commit after processing SQL statements.
AUTOTRACE	OFF	Displays the plan and statistics of a running query.
BLOCKTERMINATOR	"." (0x2E)	Sets a character that indicates the end of a PSM block.
COLSEP	" " (0x20)	Sets a delimiter for columns displayed after processing a SELECT statement.
CONCAT	"." (0x2E)	Sets a character that indicates the end of a substitution variable name.
DDLSTATS	OFF	Displays the plan and statistics of a running DDL statement.
DEFINE	"&" (0x26)	Sets a character used to define a substitution variable.
DESCRIBE	10	Sets the level of object specification to display.
ECHO	OFF	Displays running script queries when a script file is executed with the START or @ command.
EDITFILE	.tbedit.sql	Sets the default file name used for the EDIT command.
ESCAPE	OFF	Sets an escape character used to ignore the substitution variable character defined in DEFINE.
EXITCOMMIT	ON	Executes a commit when a utility is ended.
FEEDBACK	0	Displays the SQL statement results.
HEADING	ON	Displays column headers for query results.
HEADSEP	"|" (0x7C)	Sets a new line character for column headers.
HISTORY	50	Sets the size of command history.
INTERVAL	1	Sets the wait time between executions of the LOOP command.
LINESIZE	80	Sets the length of a line on the screen.
LONG	80	Sets the output length of large object data.
MARKUP	OFF	Sets the output of a utility in html.
NEWPAGE	1	Sets the number of empty lines added to the beginning of each page.
NUMFORMAT	""	Sets the default format of numeric columns.
NUMWIDTH	10	Sets the output length of numeric data.
PAGESIZE	24	Sets the number of lines per page.
PAUSE	OFF	Waits for user input before displaying the next page.
RECSEP	WRAPPED	Displays a row separator.
RECSEPCHAR	" " (0x20)	Sets a character used as a row separator.
ROWS	ON	Displays query statement results.
SERVEROUTPUT	OFF	Displays the result of the DBMS_OUTPUT package.
SQLCODE		Displays the last SQLCODE.
SQLPROMPT	"SQL> "	Sets the prompt string.
SQLTERMINATOR	";" (0x3B)	Sets a character that indicates the end of an SQL statement.
SUFFIX	sql	Sets the default file extension.
TERMOUT	ON	Displays the result of commands in a script.
TIME	OFF	Displays the current time in the prompt.
TIMEOUT	3	Sets the timeout for a server response to the ping command.
TIMING	OFF	Displays the processing time along with SQL and PSM statement results.
TRIMOUT	ON	Trims whitespaces at the end of each line when displaying SQL and PSM results.
TRIMSPOOL	OFF	Trims whitespaces at the end of each line when spooling SQL and PSM results.
UNDERLINE	"-" (0x2D)	Sets a character used to underline headers.
VERIFY	ON	Displays commands including substitution variables after replacing the variables with values.
WRAP	ON	Wraps lines that are longer than LINESIZE.

The following sets system variables.

SET AUTOCOMMIT ON 
SET PAGESIZE 32 
SET TRIMSPOOL ON

AUTOCOMMIT

Executes a commit after processing SQL statements such as INSERT, UPDATE, DELETE, MERGE, and PSM blocks.

Syntax

SET AUTO[COMMIT] {ON|OFF|n}

Item	Description
ON	Executes an auto commit.
OFF	Does not execute a commit. (Default value) If set to OFF, a commit must be executed manually.
n	Executes a commit after processing n SQL statements. Setting to 0 is the same as setting to OFF, and setting to 1 is the same as setting to ON.

AUTOTRACE

Displays the plan and statistics of a running query. To use this variable, DBA or PLUSTRACE privilege is required. The PLUSTRACE privilege includes permissions required to use AUTOTRACE. A user who has DBA privilege can create the PLUSTRACE privilege and grant it to another user.

To create the privilege, the $TB_HOME/scripts/plustrace.sql script can be used.

Syntax

SET AUTOT[RACE] {ON|OFF|TRACE[ONLY]} [EXP[LAIN]] [STAT[ISTICS]] [PLANS[TAT]]

• Input

Item	Description
ON	Displays the plan and statistics depending on the query result and options.
OFF	Does not display the query plan and statistics. (Default value)
TRACE[ONLY]	Does not display the query result, but displays the plan and statistics depending on options.

• Options

The following describes each option.

Item	Description
None	Both the plan and statistics are displayed.
EXP[LAIN]	Displays the plan.
STAT[ISTICS]	Displays the statistics.
PLANS[TAT]	Displays query execution information such as processing time, the number of processed rows, and execution count for each node.

BLOCKTERMINATOR

Sets a character that indicates the end of a PSM block.

Syntax

SET BLO[CKTERMINATOR] {c|ON|OFF}

Item	Description
c	Character that indicates the end of a PSM block. (Default value: ".")
ON	Enables this variable. (Default value)
OFF	Disables this variable.

COLSEP

Sets a delimiter for columns displayed after processing a SELECT statement.

Syntax

SET COLSEP {text}

Item	Description
text	Column delimiter. (Default value: " ")

CONCAT

Sets a character that indicates the end of a substitution variable name.

Syntax

SET CON[CAT] {c|ON|OFF}

Item	Description
c	Character that indicates the end of a substitution variable name. (Default value: ".")
ON	Enables this variable. (Default value)
OFF	Disables this variable.

DDLSTATS

Displays the plan and statistics of a running DDL statement. To use this variable, AUTOTRACE must be enabled.

Syntax

SET DDLSTAT[S] {ON|OFF}

Item	Description
ON	Enables this variable.
OFF	Disables this variable. (Default value)

DEFINE

Sets a character used to define a substitution variable.

Syntax

SET DEF[INE] {c|ON|OFF} 

Item	Description
c	Character that indicates a substitution variable. (Default value: "&")
ON	Enables this variable. (Default value)
OFF	Disables this variable.

DESCRIBE

Sets the level of object specification to display.

Syntax

SET DESCRIBE DEPTH {n} 

Item	Description
n	Level of object specification to display recursively. (Default value: 10)

ECHO

Displays running script queries when a script file is executed with the START or @ command.

Syntax

SET ECHO {ON|OFF}

Item	Description
ON	Enables this variable.
OFF	Disables this variable. (Default value)

EDITFILE

Sets the default file name used for the EDIT command. If the extension is omitted, the value set in SUFFIX is used.

Syntax

SET EDITF[ILE] filename[.ext]

Item	Description
filename[.ext]	File name used for the EDIT command. (Default value: .tbedit.sql)

ESCAPE

Sets an escape character used to ignore the substitution variable character defined in DEFINE. If the escape character is followed by a substitution variable, the variable is not recognized as a substitution variable.

Syntax

SET ESC[APE] {c|ON|OFF}

Item	Description
c	Escape character. (Default value: "\")
ON	Enables this variable.
OFF	Disables this variable. (Default value)

EXITCOMMIT

Executes a commit when tbSQL ends.

Syntax

SET EXITC[OMMIT] {ON|OFF}

Item	Description
ON	Enables this variable. (Default value)
OFF	Disables this variable.

FEEDBACK

Displays the SQL statement results.

Syntax

SET FEED[BACK] {n|ON|OFF}

Item	Description
n	Minimum number of rows to display the results. (Default value: 0)
ON	Enables this variable. (Default value)
OFF	Disables this variable.

HEADING

Displays column headers for query results.

Syntax

SET HEA[DING] {ON|OFF}

Item	Description
ON	Enables this variable. (Default value)
OFF	Disables this variable.

HEADSEP

Sets a new line character for column headers.

Syntax

SET HEADS[EP] {c|ON|OFF}

Item	Description
c	New line character. (Default value: "|")
ON	Enables this variable. (Default value)
OFF	Disables this variable.

HISTORY

Sets the size of command history.

Syntax

SET HIS[TORY] {n}

Item	Description
n	Size of command history. (Default value: 50)

INTERVAL

Sets the wait time between executions of the LOOP command.

Syntax

SET INTER[VAL] {n}

Item	Description
n	Wait time in seconds. (Default value: 1)

LINESIZE

Sets the length of a line on the screen.

Syntax

SET LIN[ESIZE] {n}

Item	Description
n	Length of a line on the screen. (Default value: 80)

LONG

Sets the output length of large object data, such as CLOB, BLOB, NCLOB, LONG, and XML data.

Syntax

SET LONG {n}

Item	Description
n	Output length of large object data. (Default value: 80, maximum value: 2,000,000,000)

MARKUP

Displays the output of a utility in HTML.

Syntax

SET MARKUP HTML {ON|OFF} [HEAD text] [BODY text] [TABLE text] [ENTMAP {ON|OFF}] 
[SPOOL {ON|OFF}] [PRE[FORMAT] {ON|OFF}]

• Input parameters

Item	Description
ON	Enables HTML MARKUP.
OFF	Disables HTML MARKUP. (Default value)

• Option The following are options for detail settings of HTML markup.

  Item	Description
  HEAD text	Specifies text to be included in the <head> tag. (Default value: default setting of the utility)
  BODY text	Specifies attributes for the <body> tag. (Default value: none)
  TABLE text	Specifies attributes for the <table> tag. (Default value: default setting of the utility)
  ENTMAP ON	Enables conversion of characters <, >, ", and & into HTML entities. (Default value)
  ENTMAP OFF	Disables conversion of characters <, >, ", and & into HTML entities.
  SPOOL ON	Enables formatting with the <html> and <body> tags to the beginning and end of a SPOOL file.
  SPOOL OFF	Disables formatting with the <html> and <body> tags to the beginning and end of a SPOOL file. (Default value)
  PREFORMAT ON	Formats query results with the <pre> tag.
  PREFORMAT OFF	Formats query results in HTML tables. (Default value)

The MARKUP capability is supported starting with Tibero 7 FS02 release.

NEWPAGE

Sets the number of empty lines added to the beginning of each page.

Syntax

SET NEWP[AGE] {1|n|NONE} 

Item	Description
n	Number of empty lines. (Default value: 1)

NUMFORMAT

Sets the default format of NUMBER columns. The format applies only to numeric columns whose format is not set with the COLUMN command.

Syntax

SET NUMF[ORMAT] {fmt_str}

Item	Description
fmt_str	Default format of numeric columns. (Default value: "") For more information about numeric type formats, refer to “Column Formats”.

NUMWIDTH

Sets the output length of NUMBER data. The length cannot exceed the value set in LINESIZE.

Syntax

SET NUM[WIDTH] {n}

Item	Description
n	Output length of numeric data. (Default value: 10)

PAGESIZE

Sets the number of lines per page.

Syntax

SET PAGES[IZE] {n}

Item	Description
n	Number of lines per page. (Default value: 24)

PAUSE

Waits for user input before displaying the next page.

Syntax

SET PAU[SE] {ON|OFF}

Item	Description
ON	Enables this variable.
OFF	Disables this variable. (Default value)

RECSEP

Displays a row separator.

Syntax

SET RECSEP {WR[APPED]|EA[CH]|OFF}

Item	Description
WRAPPED	Displays a row separator after wrapped rows. (Default value)
EACH	Displays a row separator after each row.
OFF	Disables this variable.

RECSEPCHAR

Sets a character used as a row separator. This character is repeatedly displayed as many times as LINESIZE.

Syntax

SET RECSEPCHAR {c}

Item	Description
c	Row separator. (Default value: " ")

ROWS

Displays query statement results.

Syntax

SET ROWS {ON|OFF}

Item	Description
ON	Enables this variable. (Default value)
OFF	Disables this variable.

SERVEROUTPUT

Displays the result of the DBMS_OUTPUT package.

Syntax

SET SERVEROUT[PUT] {ON|OFF} [SIZE n]

Item	Description
ON	Enables this variable.
OFF	Disables this variable. (Default value)
n	SERVEROUTPUT buffer size. (Default value: 1000000)

SQLCODE

Displays the last SQLCODE. This value cannot be set with the SET command.

Syntax

SHOW SQLCODE

SQLPROMPT

Sets the prompt string.

Syntax

SET SQLP[ROMPT] {prompt_string}

Item

Description

prompt_string

Prompt string. (Default value: "SQL> ") Recognizes environment variables and the _user identifier in this string. For example, if '$ISQL_PROMPT' is specified, the value of the environment variable $ISQL_PROMPT is used as the prompt string. The name of the environment variable is case-sensitive. If '_user' is specified, the value of _user is dynamically replaced with the currently connected user's name and used as the prompt string. Both an environment variable and the _user identifier can be included in a string at the same time. The maximum length of the string is 128 characters.

SQLTERMINATOR

Sets a character that indicates the end of an SQL statement.

Syntax

SET SQLT[MINATOR} {c|ON|OFF}

Item	Description
c	Character that indicates the end of an SQL statement. (Default value: ";")
ON	Enables this variable.
OFF	Disables this variable.

SUFFIX

Sets the default file extension.

Syntax

SET SUF[FIX] {extension}

Item	Description
extension	Default file extension. (Default value: sql)

TERMOUT

Displays the result of commands in a script.

Syntax

SET TERM[OUT] {ON|OFF}

Item	Description
ON	Enables this variable. (Default value)
OFF	Disables this variable.

TIME

Displays the current time in the prompt.

Syntax

SET TI[ME] {ON|OFF}

Item	Description
ON	Enables this variable.
OFF	Disables this variable. (Default value)

TIMEOUT

Sets the timeout for a server response to the ping command.

Syntax

SET TIMEOUT {n}

Item	Description
n	Timeout for a server response in seconds. (Default value: 3)

TIMING

Displays the processing time along with SQL and PSM statement results.

Syntax

SET TIMI[NG] {ON|OFF}

Item	Description
ON	Enables this variable.
OFF	Disables this variable. (Default value)

TRIMOUT

Trims whitespaces at the end of each line when displaying SQL and PSM statement results.

Syntax

SET TRIM[OUT] {ON|OFF}

Item	Description
ON	Enables this variable. (Default value)
OFF	Disables this variable.

TRIMSPOOL

Trims whitespaces at the end of each line when spooling SQL and PSM statement results.

Syntax

SET TRIMS[POOL] {ON|OFF}

Item	Description
ON	Enables this variable.
OFF	Disables this variable. (Default value)

UNDERLINE

Sets a character used to underline headers.

Syntax

SET UND[ERLINE] {c|ON|OFF}

Item	Description
c	Underline character. (Default value: "-")
ON	Enables this variable. (Default value)
OFF	Disables this variable.

VERIFY

Displays commands including substitution variables after replacing the variables with values.

Syntax

SET VER[IFY] {ON|OFF}

Item	Description
ON	Enables this variable. (Default value)
OFF	Disables this variable.

WRAP

Wraps lines that are longer than LINESIZE.

Syntax

SET WRA[P] {ON|OFF}

Item	Description
ON	Enables this variable. (Default value)
OFF	Truncates lines that are longer than LINESIZE to match the LINESIZE.

---

Basic Functions

tbSQL is mainly used to execute user-entered queries such as SQL statements and PSM programs. This section describes how to enter and execute the queries and explains additional functions.

Entering Queries

At the tbSQL prompt, you can enter SQL statements, PSM programs, and tbSQL commands. The following describes how to enter them.

SQL Statements

Enter SQL statements as follows:

• Entering SQL statements Enter general SQL statements at the prompt. An SQL statement can span multiple lines. To cancel entering a statement, enter an empty line.

• Line break Break a line when entering an SQL statement in multiple lines. You can break a line anywhere except in the middle of a string. It is recommended to break a line by clause for readability.

• Inserting comments Insert comments into SQL statements. Comments start with two dashes (--) and end with the newline character. They can start at the beginning of a line or after another string.

• Using stored statements Entered SQL statements are stored in tbSQL's SQL buffer and can be reused. If they are modified, the modified statements are also stored in the buffer.

  The SQL buffer stores a single SQL statement or PSM program. Depending on the OS, press the up arrow key (­) or down arrow key (¯) to select a line of a stored statement. Since one line of a stored statement is displayed whenever you press the key, you can reuse a part or full of SQL statement.

The following enters an SQL statement in tbSQL.

SQL> SELECT ENAME, SALARY, ADDR 
    FROM EMP
    -- this is a comment. 
    WHERE DEPTNO = 5;
SQL>

PSM Programs

A PSM program consists of multiple SQL and PSM statements. Each SQL statement ends with a semicolon (;). When you start entering a PSM program, tbSQL will be automatically switched to the PSM program input mode. In the mode, SQL statements are not executed until an entire program is entered.

Entering the following statements switch tbSQL to the PSM program input mode: DECLARE and BEGIN for anonymous blocks, CREATE (OR REPLACE) PROCEDURE that creates a procedure, CREATE (OR REPLACE) FUNCTION that creates a function, and CREATE (OR REPLACE) TRIGGER that creates a trigger.

Enter PSM programs as follows:

• Entering PSM Programs A PSM program can span multiple lines. To cancel entering a PSM program, enter the block terminator character (set in BLOCKTERMINATOR). The default character is a period (.). Enter the character in a separate line without any other characters.

• Inserting comments Same as in SQL statements.

• Using stored Statements Entered programs are stored in SQL buffer and can be reused.

The following enters an anonymous block in tbSQL.

SQL> DECLARE
    deptno NUMBER(2);
    BEGIN
        deptno := 5;
        UPDATE EMP SET SALARY = SALARY * 1.05
        WHERE DEPTNO = deptno;
        -- this is a comment.
    END;
    . 
SQL>

In the previous example, a line is entered as a comment, and the PSM program is ended by entering the block terminator character (.) below the END statement. You can check that the character is entered in a separate line without any other characters.

For more information about tbPSM, refer to "Tibero tbPSM Guide".

tbSQL Commands

Enter tbSQL commands at the prompt to execute SQL statements and manage Tibero database.

tFor information about the commands, refer to “tbSQL Commands”.

Executing Queries

There are the following three ways to execute queries entered at the tbSQL prompt.

• Executing SQL statements and PSM programs stored in the SQL buffer The SQL buffer stores an SQL statement or a PSM program entered lastly. To execute the SQL statement or PSM program, enter the RUN or / command.

• Executing SQL statements Entering a full SQL statement followed by a semicolon (;) executes the statement.

• Executing an SQL statement and storing it in the SQL buffer To execute an SQL statement or a PSM program and store it in the butter, enter the / command. Enter the command in a separate line without any other characters like the block terminator character.

There is no separate command for executing tbSQL utility commands. The commands are not stored in the SQL buffer. They are executed when entered.

The following shows how to execute an SQL statement stored in the SQL buffer.

SQL> SELECT ENAME, SALARY, ADDR 
    FROM EMP
    -- this is a comment. 
    WHERE DEPTNO = 5;
......Execution result  ①......
SQL> /
......Execution result  ②......
SQL>

In the previous example, at the first prompt, an SQL statement is executed by entering the statement and a semicolon (;). At the second prompt, the / command is entered, which executes the SQL statement stored in the SQL buffer. Since the SQL buffer stores an SQL statement entered lastly, the SQL statement entered at the first prompt is executed. Therefore, the execution results (1) and (2) are the same.

The following shows how to execute an SQL statement and store it in the SQL buffer. A semicolon (;) is not entered at the end of the SQL statement.

SQL> SELECT ENAME, SALARY, ADDR 
    FROM EMP
    -- this is a comment. 
    WHERE DEPTNO = 5
    /
......Execution result ......
SQL>

Additional Functions

The following describes additional basic functions.

Inserting Comments

There are the following two ways to insert comments.

• Using /* and */ Same as in the C and C++ programing languages. Enclose multi-line comments with /* and */. A comment cannot nest another comment.

• Using two dashes (--) Single-line comments start with two dashes (--) and end with the newline character. They can start anywhere except for in a line with a period (.) that indicates the end of a PSM program.

The following causes an error.

(PSM program)
.-- incorrect comment
RUN

Autocommit

Data modified with an SQL statement is not applied to a database until the transaction including the statement is committed. A transaction usually includes multiple SQL statements.

In tbSQL, you can enable or disable autocommit by using the AUTOCOMMIT variable (default value: OFF). To set the variable, use the SET AUTOCOMMIT command. To check the current setting, use the SHOW AUTOCOMMIT command.

Executing OS Commands

To execute OS commands in tbSQL, use the ! or HOST command.

The following lists all script files with the .sql extension.

SQL> HOST dir *.sql
..... OS command execution result .....
SQL>

After executing an OS command, the tbSQL prompt is displayed.

If the ! or HOST command is used without an OS command, the OS command prompt is displayed. To return to the tbSQL prompt, enter EXIT as follows:

SQL> !
$ dir *.sql
..... OS command execution result .....
$ EXIT 
SQL>

Saving Input and Output

To save all data entered and displayed in tbSQL to a text file, use the SPOOL command. This command saves all user-entered SQL statements and PSM programs, tbSQL commands, query and program execution results, and the tbSQL prompt to a text file. If you execute the command, spooling starts from the next line. To stop this function, use the SPOOL OFF command.

The following uses the SPOOL command with a file name (save.txt). Note that if the file name already exists, the existing file is overwritten.

SQL> SPOOL save.txt 
Spooling is started. 
SQL> SELECT
    *
    FROM DUAL; 
    
DUMMY
------
X

1 row selected.

SQL> SPOOL OFF
Spooling is stopped: save.txt

The following are the contents of the save.txt file in the previous example.

SQL> SELECT
    *
    FROM DUAL; 
    
DUMMY
------
X

1 row selected.

SQL> SPOOL OFF

The file contains user-entered SQL statements, the query result, and the SPOOL OFF command.

---

Advanced Functions

This section describes the advanced tbSQL functions including batch processing using a script and DBA functions for managing Tibero.

Script Functions

A script is a series of SQL statements, PSM programs, and tbSQL commands. When executing a script in tbSQL, all the queries in the script are executed sequentially.

Creating a Script

You can create and edit a script file externally, or call an external editor in tbSQL to create and edit a script file.

The following uses vi as an external editor in tbSQL.

$ export TB_EDITOR=vi

To edit a script file by using an external editor, use the EDIT command with specifying the file name. The file extension can be omitted if the extension is the same as the value set in the SUFFIX system variable.

The following calls an external editor to edit the run.sql script file.

SQL> EDIT run

Add SQL statements, PSM programs, and tbSQL commands to a script file as follows:

• Adding queries Same as entering queries at the tbSQL prompt. A query can span multiple lines.

• Ending SQL statements and PSM programs An SQL statement must be ended with a semicolon (;). A PSM program must be ended with a period (.) in the last line.

• Inserting comments You can insert comments into a script file.

When a script is executed, SQL statements in the script are executed immediately. PSM programs in the script are executed with the RUN or / command.

The following is a sample script file that executes some operations on the EMP table. Blank lines are allowed between lines.

-- SQL statement
SELECT ENAME, SALARY, ADDR FROM EMP 
    WHERE DEPTNO = 5;
UPDATE EMP SET SALARY = SALARY * 1.05 
    WHERE DEPTNO = 5;
    
-- PSM program
DECLARE
    deptno NUMBER(2);
BEGIN
    deptno := 20;
    UPDATE EMP SET SALARY = SALARY * 1.05
    WHERE DEPTNO = deptno; 
END;

RUN -- Executing the PSM program
/* Applying the changes  */ 
COMMIT;

Executing a Script

To execute a script file, use the START or @ command with specifying the file name. The file extension can be omitted if the extension is the same as the value set in the SUFFIX system variable (default value: sql). The script file is searched in the current directory. If not found, it is searched in the directory set in the TB_SQLPATH environment variable.

The following each line executes the run.sql script file and has the same result.

SQL> START run
SQL> @run

A script file can include one or more script files by using the START or @ command. Make sure not to get into an infinite loop when using a script file recursively.

You can execute a script file when starting tbSQL by using the @ command. This is useful when executing a batch program in the OS.

The following executes the run.sql script file when starting tbSQL.

$ tbsql dbuser/dbuserpassword @run

The following also executes the run.sql script file when starting tbSQL by using the shell redirection command.

$ tbsql dbuser/dbuserpassword < run.sql

DBA Functions

A user with the DBA privilege can execute DBA functions in tbSQL.

The following logs in as the SYS user with the DBA privilege.

$ tbsql sys/syspassword

After starting tbSQL, you can connect to a database as a user with the DBA privilege by using the CONNECT command.

The following connects to a database as the SYS user with the DBA privilege.

SQL> CONNECT sys/syspassword

A DBA can perform the following task in tbSQL.

● Ending Tibero by using the TBDOWN command

User Access Control

tbSQL allows to restrict users from executing certain commands. For this, the CLIENT_ACCESS_POLICY table is referenced. The SYS user creates this table, and a DBA defines the access policy in the table to grant command execution permission to specific users.

tbSQL loads the table data when a user connects to a database and it checks the permission whenever the user executes a command. The loaded data is released when the user disconnects from the database.

Privilege is not checked for SYS users.

Creating Access Control Table

The SYS user can create the access control table by executing the $TB_HOME/scripts/client_policy.sql file.

Privilege is not checked if the table does not exist or has an issue.

The contents of the CLIENT_ACCESS_POLICY table are as follows:

CLIENT VARCHAR2(32) NOT NULL 
USERID VARCHAR2(128)
ACTION VARCHAR2(256) 
POLICY VARCHAR2(64)

Item	Description
CLIENT	Case-sensitive client program name. Set to tbSQL.
USERID	User ID(s). Specify as follows. A wildcard (%) is allowed. TIBERO T% (All users that start with T) % (All users)
ACTION	Command to control.
POLICY	Access policy. Set to DISABLED.

Configuring Command Access Control

To restrict executing tbSQL, SQL, or PSM commands, add rows to the CLIENT_ACCESS_POLICY table like the following. To allow executing the commands, delete the rows

CLIENT		USERID		ACTION		POLICY
----------      -----------     -----------     --------------
tbSQL           TIBERO          HOST            DISABLED 
tbSQL           %               INSERT          DISABLED 
tbSQL           PUBLIC          UPDATE          DISABLED 

The following are the lists of commands that can be controlled.

• tbSQL Commands

ACCEPT	        APPEND	        ARCHIVE	        CHANGE
CLEAR	        COLUMN	        CONNECT	        DEFINE
DEL	        DESCRIBE	DISCONNECT	EDIT
EXECUTE	        EXIT	        EXPORT	        HELP (?)
HISTORY	        HOST (!)	INPUT	        LIST
LOADFILE	LOOP	        LS	        PASSWORD
PAUSE		PING	        PRINT	        PROMPT
QUIT		REMARK	        RESTORE	        RUN
SAVE		SET	        SHOW	        SPOOL
START	(@, @@)	TBDOWN	        UNDEFINE	VARIABLE
WHENEVER

• SQL Commands

ALTER	        ANALYZE	        AUDIT	        CALL
COMMENT	        COMMIT	        CREATE	        DELETE
DROP	        EXPLAIN	        FLASHBACK	GRANT
INSERT	        LOCK	        MERGE	        NOAUDIT
PURGE	        RENAME	        REVOKE	        ROLLBACK
SAVEPOINT	SELECT	        SET CONSTRAINTS	SET ROLE
SET TRANSACTION	TRUNCATE	UPDATE	

• PSM Commands

BEGIN         DECLARE

The following is an example of using the user access control function.

..... Connect as SYS user .....
SQL> CONNECT SYS
..... Configure Access Control  .....
SQL> INSERT INTO CLIENT_ACCESS_POLICY VALUES ('tbSQL', 'TIBERO', 'SELECT', 'DISABLED');

..... Connect as TIBERO use .....
SQL> CONNECT TIBERO
SQL> SELECT * FROM DUAL;
TBS-70082: The 'SELECT' command has been disabled.

Encrypting Connection Information

Database connection information (connect_string) can be saved in an encryption file (wallet.dat) and reused in tbSQL. The file is located in the path set in the ISQL_WALLET_PATH environment variable and can be used from the next connection.

Creating an Encryption File

Connect to a database in tbSQL and then create an encryption file by using the SAVE CREDENTIAL command.

The following sets the ISQL_WALLET_PATH environment variable to wallet.dat in the current directory and then encrypts database connection information.

$ export ISQL_WALLET_PATH=./wallet.dat
$ tbsql 

tbSQL 7

TmaxData Corporation Copyright (c) 2008-. All rights reserved.

Can't login with the wallet file.
Login the database and SAVE CREDENTIAL again.

Enter Username: dbuser
Enter Password: dbuserpassword 
Connected to Tibero.

SQL> SAVE CREDENTIAL
Complete to generate the wallet file.

In the previous example, since ISQL_WALLET_PATH is set before starting tbSQL, it is tried to decrypt the set file, but an error occurs because the set file does not exist. To resolve this issue, reconnect to the database and create the file by using the SAVE CREDENTIAL command.

The following creates wallet.dat in the current directory by encrypting database connection information without setting ISQL_WALLET_PATH.

$ tbsql 

tbSQL 7

TmaxData Corporation Copyright (c) 2008-. All rights reserved.

SQL> CONN dbuser/dbuserpassword 
Connected to Tibero.

SQL> SAVE CREDENTIAL "./wallet.dat" 
Complete to generate the wallet file.

You can specify the path of wallet.dat when executing SAVE CREDENTIAL.

Using an Encryption File

Set the encryption file (wallet.dat) created in the previous example in ISQL_WALLET_PATH before executing tbSQL to reuse database connection information used before creating the file.

The following connects to a database by using the file set in ISQL_WALLET_PATH.

$ tbsql 

tbSQL 7

TmaxData Corporation Copyright (c) 2008-. All rights reserved.

Connected to Tibero.

To use the encryption file, it must be set in ISQL_WALLET_PATH. The encryption file is only available in tbSQL instance where the file is created. To use the same file in another tbSQL instance, the encryption file must be reconfigured using the aforementioned steps.

This function is not available in Windows.

---

tbSQL Commands

This section describes commands available in tbSQL.

The following is sample syntax.

COM[MAND] param {choice1|choice2} [option] [arg]*

The following describes each item in the syntax.

Item	Description
Brackets ([ ])	Optional. In the sample syntax, the [MAND], [option], and [arg] can be omitted.
Curly braces ({ })	Mutually exclusive parameters. One of them must be entered. In the sample syntax, choice1 and choice2 are enclosed with curly braces ({, }) and separated by a vertical bar (|). One of them must be entered.
Vertical bar (|)	OR operator used for mutually exclusive parameters.
Asterisk (*)	None or multiple arguments can be entered. In the sample syntax, [arg] followed by an asterisk (*) can be excluded or entered multiple times.
Italic letters	Must be replaced by another string depending on the command.
Case sensitivity	Commands are not case-sensitive.

The following are examples of valid commands.

COMMAND param choice1 
COM param choice1 option
COM param choice2 arg1 arg2 arg3

With tbSQL commands, you can execute SQL statements and manage databases. The following describes the commands in alphabetical order.

The following are the tbSQL commands.

Command	Description
!	Executes an OS command. Same as the HOST command.
%	Executes a command stored in the tbSQL history buffer.
@, @@	Executes a script file. Same as the START command.
/	Executes PSM programs or SQL statements that are saved in the SQL buffer. Same as the RUN command.
ACCEPT	Receives user input and saves it in a specified substitution variable.
APPEND	Appends user-entered text or statements to the SQL buffer.
ARCHIVE LOG	Displays redo log file information.
CHANGE	Finds an old pattern in the current line of the SQL buffer and changes it to a new pattern.
CLEAR	Initializes or deletes the specified option.
COLUMN	Specifies the display properties of a specified column.
CONNECT	Connects to the database as a specified user.
DEFINE	Defines or displays substitution variables.
DEL	Deletes the line that is stored in the SQL buffer.
DESCRIBE	Displays the column information of the specified object.
DISCONNECT	Ends the connection to the current database.
EDIT	Edits the contents of an SQL buffer or a certain file by using an external editor.
EXECUTE	Processes PSM statements that are CALL statements or anonymous blocks.
EXIT	Ends tbSQL. Same as the QUIT command.
EXPORT	Exports SELECT statement results or table data to a file.
HELP	Displays help information.
HISTORY	Displays commands stored in the history buffer.
HOST	Executes an OS command. Same as the ! command.
INPUT	Adds a user-input SQL statement after the last line in the SQL buffer.
LIST	Displays specified lines from the SQL buffer.
LOADFILE	Saves a Tibero table in the format that can be recognized by Oracle SQL*Loader.
LOOP	Repeatedly executes a statement.
LS	Displays information about a user-created database object with a specific type or name.
PASSWORD	Changes the user password.
PAUSE	Pauses the execution until the user presses the <Enter> key.
PING	Displays whether a specified database is accessible.
PRINT	Displays the value and name of user-defined bind variables.
PROMPT	Displays a specified message or an empty line.
QUIT	Ends tbSQL. Same as the EXIT command.
RESTORE	Restores user-selected data from a file.
RUN	Executes PSM programs or SQL statements that are stored in the SQL buffer. Same as the / command.
SAVE	Saves user-selected information to a file.
SET	Sets tbSQL system variables.
SHOW	Shows tbSQL system variables.
SPOOL	Saves all screen outputs to a file in the current directory.
START	Executes a script file. Same as the @ command.
TBDOWN	Ends Tibero database.
UNDEFINE	Deletes substitution variables.
VARIABLE	Declares user bind variables.
WHENEVER	Defines tbSQL action to take when an error occurs.

!

Executes an OS command. Same as the HOST command.

Syntax

! [command]

Item	Description
None	Goes to the Windows prompt where multiple OS commands can be entered. To return to the tbSQL prompt, enter the EXIT command.
command	OS command.

Example

SQL> ! dir *.sql 
SQL> !

%

Executes a command stored in the tbSQL history buffer.

Syntax

% number

Item	Description
number	Command number in the history buffer.

Example

SQL> history
    1: set serveroutput on 
    2: set pagesize 40
    3: select 1 from dual;
SQL> %3

        1
---------
        1
        
1 row selected.

@, @@

Executes a script file. If the script file has the same extension as the one set in the SUFFIX system variable, the extension can be omitted. tbSQL finds the specified script file in the current directory.

System variables set with the SET command before executing the script remain in effect while executing the script. Use the EXIT or QUIT command in a script file to end tbSQL.

Same as the START command.

Syntax

@ {filename}

Item	Description
filename	Script file name.

Example

SQL> @ run 
SQL> @ run.sql

/

Executes PSM programs or SQL statements that are stored in the SQL buffer.

Syntax

/

Example

SQL> SELECT * FROM DUAL;
..... SQL statement execution result  .....
SQL> /
..... The same result as above  .....

ACCEPT

Receives user input and saves it in a specified substitution variable. The value automatically replaces a value that matches &variable in an SQL statement or a PSM program.

Syntax

ACC[EPT] variable [FOR[MAT] format] [DEF[AULT] default] 
[PROMPT statement|NOPR[OMPT]] [HIDE]

Item	Description
variable	Substitution variable name. A new variable will be created if it does not exist.
FOR[MAT] format	Format of the substitution variable. If the value does not match the format, an error occurs.
DEF[AULT] default	Default value of the substitution variable.
PROMPT statement	Displays a prompt before receiving a user input
NOPR[OMPT]	Waits for a user input without displaying a prompt.
HIDE	Prevents to display a user input value.

Example

SQL> ACCEPT name PROMPT 'Enter name : ' 
Enter name : 'John'
SQL> SELECT &name FROM DUAL;
At line 1, column 8
old value : SELECT &name FROM DUAL 
new value : SELECT 'John' FROM DUAL

'JOHN'
------
John

1 row selected.

SQL>

APPEND

Appends user-entered text or statements to the SQL buffer.

Syntax

A[PPEND] statement

Item	Description
statement	Text or statement to append to the SQL buffer.

Example

SQL> LIST 2
..... Select the second line .....
SQL> APPEND text
..... Add text to the second line .....

ARCHIVE LOG

Displays Archive log file information.

Syntax

ARCHIVE LOG LIST

Example

SQL> ARCHIVE LOG LIST

NAME                             VALUE
--------------------------       -----------------------------------------
Database log mode                Archive Mode
Archive destination              /home/tibero/database/tibero/archive
Oldest online log sequence	 300
Next log sequence to archive	 302
Current log sequence	         302

SQL>

CHANGE

Finds an old pattern in the current line of the SQL buffer and changes it to a new pattern. Typically, the current line of the last executed SQL statement is the last line. For information about how to change the current line, refer to the following example.

Syntax

C[HANGE] delim old [delim [new [delim [option]]]]

• Input

Item	Description
delim	Delimiter. Digits and characters used for old or new patterns are not allowed.
old	Pattern to change. Not case-sensitive. General words (for example, dual, ksc911) and '...' which means an arbitrary pattern can be used.

• option

Item	Description
delim	Delimiter. Digits and characters used for old or new patterns are not allowed.
new	New pattern.
option	g: Changes all old patterns in the current line. c: Changes user-selected old patterns in the current line. a: Changes all old patterns in the entire statement.

Example

Since the current line is always the last line by default, the DUAL in the second line is changed to T.

SQL> SELECT *
    FROM DUAL;
..... SQL execution results  .....
SQL> C/DUAL/T
    FROM T 
SQL>

To change the current line, enter the line number.

SQL> 5
    5 WHERE ROWNUM < 5 AND

An arbitrary pattern can be specified with '...'. It can be placed in the front, back, or middle of a word.

SQL> CHANGE /RE...AND/RE ROWNUM >= 5 AND/
    5 WHERE ROWNUM >= 5 AND
SQL> CHANGE /...AND/WHERE ROWNUM < 3/
    5 WEHRE ROWNUM < 3
SQL> CHANGE /WHE.../WHERE ROWNUM < 5 AND/
    5 WHERE ROWNUM < 5 AND

To change all old patterns in the entire statement, use the "a" option. The following changes * to the specified new pattern.

SQL> SELECT *
    FROM DUAL;
..... SQL execution results  .....
SQL> C/*/'replaced'/a 
    SELECT 'replaced' 
    FROM DUAL;
SQL>

CLEAR

Initializes or deletes specified data.

Syntax

CL[EAR] [option]

• option

Item	Description
BUFF[ER]	Clears the SQL buffer.
SCR[EEN]	Clears the screen.
COL[UMNS]	Initializes the display properties of all registered columns.

Example

SQL> CLEAR BUFFER
SQL buffer is cleared 
SQL> CLEAR SCREEN
SQL> CLEAR COLUMNS

COLUMN

Specifies the display properties of a specified column. When the column name is specified, only the display properties of the column are displayed. Otherwise, those of all columns are displayed.

Syntax

COL[UMN] [name [option]]

• input

Item	Description
name	Column name.

• option

Item	Description
CLE[AR]	Initializes the column's display properties.
FOR[MAT] text	Sets the column format. For more information, refer to “Column Formats”.
HEA[DING] text	Sets the column heading.
NEW_V[ALUE] variable	Sets the variable to save the column value.
WRA[PPED]	Wraps the text if the column data exceeds the limit.
TRU[NCATED]	Truncates the data if the column data exceeds the limit.
ON	Enables the display properties of the column.
OFF	Disables the display properties of the column.

Example

SQL> COLUMN
SQL> COLUMN empno
SQL> COLUMN empno CLEAR
SQL> COLUMN empno FORMAT 999,999 
SQL> COLUMN ename FORMAT A10
SQL> COLUMN sal HEADING the salary of this month 
SQL> COLUMN sal OFF
SQL> COLUMN job WRAPPED
SQL> COLUMN job TRUNCATED

CONNECT

Connects to the database as a specified user. If no user name or password is specified, tbSQL displays its prompt and requests the name or password.

Executing the CONNECT command commits the previously executed transaction, disconnects the existing connection, and then attempts to establish a new connection. Although it fails to establish the new connection, the previous connection is not recovered.

The following is the syntax.

Syntax

CONN[ECT] {username[/password[@connect_identifier]]}

Item	Description
username	User name.
password	Password for the user.
connect_identifier	Database connection information. The information includes an IP address, a port number, and DB_NAME. It is set in the tbdsn.tbr file under the $TB_HOME/client/config directory. In Windows, it can be set in ODBC data source, and the source is searched first.

Example

SQL> CONNECT dbuser/dbuserpassword@db_id

DEFINE

Defines or displays substitution variables.

Syntax

DEF[INE] [variable]|[variable = value]

Item	Description
None	Displays all substitution variables.
variable	Substitution variable name.
variable = value	Name and default value of the substitution variable.

Example

SQL> DEFINE NAME
..... Displays the substitution variable named NAME. .....
SQL> DEFINE NAME = 'SMITH'
..... Defines SMITH as the default value of the substitution variable. .....
SQL> DEFINE
..... Displays all substitution variables. .....

DEL

Deletes the line that is stored in the SQL buffer. If no line number is specified, all lines are deleted.

Syntax

DEL [number|number number|number LAST|LAST]

Item	Description
number	Deletes the line with the specified number.
number number	Deletes all lines in the specified range.
number LAST	Deletes all lines from the line with the specified number to the last line.
LAST	Deletes the last line.

Example

SQL> DEL 1
..... Deletes the first line. .....
SQL> DEL 1 3
..... Deletes all lines from the first line to the third line. .....
SQL> DEL 1 LAST
..... Deletes all lines from the first line to the last line. .....
SQL> DEL LAST
..... Deletes the last line. .....

DESCRIBE

Displays the column information of the specified object. An object can be a table, view, synonym, function, procedure, or package.

• For tables and views, the column name, data type, constraint, index information, maximum length, precision, scale, and other items are displayed.

• For functions and procedures, parameter information (name, data type, IN/OUT) are displayed. For packages, all details about the functions and procedures of the package are displayed.

• The column information about an object owned by a specified user can also be displayed. If no username is specified, the current username is used by default.

• Only the information belonging to the owner of a specified object is displayed. For tables, only the indexes of the table owner are displayed.

Syntax

DESC[RIBE] [schema.]object_name[@dblink]

Item	Description
schema	Schema (or owner) that contains the target object.
object_name	Object to display column information for.
dblink	Database link that contains the target object.

Example

SQL> DESCRIBE emp
SQL> DESC scott.emp
SQL> DESC scott.emp@gateway

DISCONNECT

Ends the connection to the current database. Executing this command commits currently running transaction but does not end tbSQL.

If a script file includes CONNECT but does not include DISCONNECT, a connection to a database remains open. Therefore, it is recommended to add DISCONNECT to a script file that includes CONNECT.

Syntax

DISCONN[ECT]

EDIT

Edits the contents of an SQL buffer or a certain file by using an external editor. An external editor can be specified in the $TB_EDITOR environment variable.

If $TB_EDITOR is not set, this command references $EDITOR. If neither variable is set, the vi editor is used. If the SQL buffer is empty, an error will be returned.

A file name can be specified without an extension if its extension is the same as the value set in the SUFFIX system variable. The default value of SUFFIX is .sql, and it can be changed with the SET command. tbSQL searches for the specified file in the current directory.

Syntax

ED[IT] [filename]

Item	Description
None	Opens the contents stored in the current SQL buffer. The default file (.tbedit.sql) is used. This file will be deleted automatically when tbSQL ends.
filename	Name of the file to edit (usually a script file).

Example

SQL> EDIT run.sql 
SQL> EDIT run 
SQL> ED

EXECUTE

Processes PSM statements that are CALL statements or anonymous blocks. There must be a semicolon (;) at the end of a statement.

Syntax

EXEC[UTE] {statement}

Item	Description
statement	PSM statement.

Example

SQL> EXECUTE begin dbms_output.put_line('success'); end; 
success

PSM completed

SQL> EXECUTE call proc1();

This command is also useful when defining a value to a user-defined bind variable.

SQL> VAR x NUMBER;
SQL> EXEC :x := 5;

PSM completed

SQL>

EXIT

Ends tbSQL. (Same as the QUIT command.) Commits all running transactions and ends all database connections.

Syntax

EXIT [SUCCESS|FAILURE|WARNING|n|variable|:variable] [COMMIT|ROLLBACK]

Item	Description
SUCCESS	Returns 0 to indicate success.
FAILURE	Returns 1 to indicate failure.
WARNING	Returns 2 to indicate that tbSQL ends with a warning.
n	Integer exit code. The available code range depends on OS.
variable	Returns the value of a user variable defined with the DEFINE command or a system variable like SQL.SQLCODE. The variable must be a numeric type.
:variable	Specifies an exit code by using a bind variable defined with the VARIABLE command. The variable must be a numeric type.
COMMIT	Executes COMMIT before ending tbSQL.
ROLLBACK	Executes ROLLBACK before ending tbSQL.

EXPORT

Exports SELECT statement results or table data to a file with a format that tbLoader can recognize. The data can be exported in fixed format or variable format that uses column and row separators.

Syntax

EXP[ORT] {QUERY filename|TABLE [schema.]table} [variable_fmt|FIXED]

variable_fmt:
    [FIELDS {TERM[INATED] BY {,|text}|ENCL[OSED] BY {"|text}}] 
    [LINES TERM[INATED] BY {\n|text}]

Item	Description
filename	Name of the file to export. This name is used as a target table name in the control file.
[schema.]table	Name of the table to export.
variable_fmt	FIELDS ...: Column separator for variable format. LINES ...: Row separator for variable format. FIXED: Fixed format indicator.

Example

The following creates t.csv (data file) and t.ctl (control file).

SQL> EXPORT QUERY 't.csv'
Ready to export to 't.csv' 

SQL> SELECT * FROM t;

10 rows exported.

The following exports data in variable format.

SQL> EXPORT QUERY 't.csv' FIELDS TERMINATED BY ',' LINES TERMINATED BY '\n'
SQL> EXPORT QUERY 't.csv' FIELDS ENCLOSED BY '*'

HELP

Displays help information about commands including a specified word.

Syntax

H[ELP] [topic]

Item	Description
None	Displays help information about all tbSQL commands.
topic	Search word.

Example

SQL> HELP SET

HISTORY

Displays commands stored in the history buffer.

Syntax

HIS[TORY]

Example

SQL> HISTORY
..... All commands stored in the history buffer .....

HOST

Executes an OS command. Same as the ! command.

Syntax

HO[ST] [command]

Item	Description
None	Entering the ! command without an OS command goes to the Windows prompt where multiple OS commands can be entered. To return to the tbSQL prompt, enter the EXIT command.
command	OS command.

INPUT

Adds a user-input SQL statement after the last line in the SQL buffer.

Syntax

I[NPUT] [statement]

Item	Description
None	A multi-line SQL statement can be added.
statement	SQL statement to add.

Example

SQL> select * from dual;
..... Outputs result .....
SQL> LIST
select * from dual SQL> INPUT where rownum < 2
select * from dual where rownum < 2
SQL>

The following specifies no option. Unlike the previous example, the SQL statement will be executed as soon as it is entered.

SQL> select * from dual;
..... Outputs result .....
SQL> INPUT
select * from dual
... Enter here. ...

LIST

Displays specified lines stored in the SQL buffer.

Syntax

L[IST] [number|number number|number LAST|LAST]

Item	Description
None	Displays all lines.
number	Displays the line with the specified number.
number number	Displays all lines in the specified range.
number LAST	Displays all lines from the line with the specified number to the last line.
LAST	Displays the last line.

Example

SQL> LIST 1
..... Displays the first line. .....
SQL> LIST 2 3
..... Displays all lines from the second line to the third line. .....
SQL> LIST 2 LAST
.....Displays all lines from the second line to the last line. ......
SQL> LIST LAST
.....Displays the last line. .....

LOADFILE

Saves a Tibero table in the format that can be recognized by Oracle SQL*Loader.

Syntax

LOAD[FILE] {filename}

Item	Description
filename	File name without a file extension.

Example

The following saves the emp table in the format that can be recognized by Oracle SQL*Loader. This creates two files, emp.ctl and emp.dat.

SQL> LOADFILE emp
SQL> select * from emp;

LOOP

Repeatedly executes a statement. Enter <Ctrl>+C to end the loop.

Syntax

LOOP stmt

Item	Description
stmt	Statement to repeatedly execute.

Example

SQL> LOOP select count(*) from v$session
..... The previous statement is executed every 1 second. .....
SQL> SET INTERVAL 10 SQL> LOOP ls
..... ls is executed every 10 seconds. .....

LS

Displays information about a user-created database object with a specific type or name.

Syntax

LS [object_type|object_name]

Item	Description
None	Displays all objects owned by the user.
object_type	One of: FUNCTION INDEX PACKAGE PROCEDURE SEQUENCE SYNONYM TABLE TABLESPACE TRIGGER USER VIEW
object_name	Name of the object to display. The percent (%) sign can be used to indicate a temporary pattern.

Example

SQL> LS 
NAME		   SUBNAME		OBJECT_TYPE
---------------    -------------        ---------------
SYS_CON100	                        INDEX			 
SYS_CON400				INDEX
SYS_CON700				INDEX
_DD_CCOL_IDX1                           INDEX
......Omitted......				
UTL_RAW                                 PACKAGE 
DBMS_STATS                              PACKGE BODY
TB_HIDDEN2                              PACKGE BODY

SQL>
..... Displays all objects. .....

SQL> LS TABLESPACE 
TABLESPACE_NAME
-----------------------------------
SYSTEM 
UNDO 
TEMP 
USR
.....  Displays all objects with the TABLESPACE type. .....

SQL> LS USER 
USERNAME
------------------------------------
SYS
..... Retrieves users with a connection to the system. .....

PASSWORD

Changes the user password.

Syntax

PASSW[ORD] [username]

Item	Description
username	Username to change the password for. If not specified, the currently connected user is specified.

Example

SQL> PASSWORD
Changing password for 'TIBERO'

Enter old password: ...Enter the current password...
Enter new password: ...Enter a new password...
Confirm new password: ...Re-enter the new password...

Password changed successfully. 
SQL>

PAUSE

Pauses the execution until the user presses the <Enter> key.

Syntax

PAU[SE] [message]

Item	Description
message	Message to display when the user presses the <Enter> key.

Example

SQL> PAUSE please enter... 
please enter...
..... Press the <Enter> key. .....
SQL>

PING

Displays whether a specified database is accessible.

Syntax

PING connect_identifier

Item	Description
connect_identifier	Name of the database to access.

Example

SQL> PING tibero7 
Server is alive.

SQL>

PRINT

Displays the name and value of user-defined bind variables.

Syntax

PRI[NT] [variable...]

Item	Description
	Displays all bind variables.
variable	Bind variable names.

Example

SQL> VARIABLE x NUMBER 
SQL> EXECUTE :x := 5;
SQL> PRINT x

        x
---------
        5
SQL>

PROMPT

Displays a specified message or an empty line.

Syntax

PRO[MPT] [message]

Item	Description
None	Displays an empty line.
message	Message to display.

Example

The following is an example of the externally written SQL file named PromptUsage.sql.

PROMPT >>> Test is started. 
CREATE TABLE T (c1 NUMBER); 
INSERT INTO T VALUES (1);
PROMPT Value 1 is inserted. 
COMMIT;
PROMPT <<< Test is ended.

The following shows the execution result of PromptUsage.sql.

SQL> @PromptUsage
>>> Test is started.
Table 'T' created.
1 row inserted.
Value 1 is inserted.
Commit succeeded.
<<< Test is ended.
File finished.

SQL>

QUIT

Ends tbSQL. Commits all running transactions and ends all database connections. (Same as the EXIT command.)

Syntax

Q[UIT] [SUCCESS|FAILURE|WARNING|n|variable|:variable] [COMMIT|ROLLBACK]

RESTORE

Restores user-selected data from a specified file.

Syntax

REST[ORE] HIST[ORY] filename[.ext]

Item	Description
filename[.ext]	Name of the file to read. If the file extension is omitted, the SUFFIX value is used.

Example

SQL> RESTORE HISTORY history.sql

RUN

Executes PSM programs or SQL statements that are stored in the SQL buffer. Same as the / command, but displays statements stored in the buffer.

Syntax

R[UN]

Example

SQL> select 1 from dual;

        1
---------
        1

1 row selected.

SQL> RUN
     1 select 1 from dual 1
     
        1
---------
        1 

row selected.

SAVE

Saves user-selected information to a specified file.

For more information about SAVE CREDENTIAL, refer to “Encrypting Connection Information”.

Syntax

SAVE CRED[ENTIAL] [filename]
SAVE HIST[ORY] filename[.ext] [CRE[ATE]|REP[LACE]|APP[END]]

Item	Description
CREDENTIAL	Encrypts and saves the database connection information. It no filename is specified, it is saved to the file set in ISQL_WALLET_PATH.
HISTORY	Saves command history. If the file extension is omitted, the value set in SUFFIX is used. CREATE: Creates a file if the specified file does not exist, or causes an error if the file exists. (Default value) REPLACE: Creates a file if the specified file does not exist, or overwrites the file if it exists. APPEND: Creates a file if the specified file does not exist, or appends new data to the file if it exists.
filename	File to save to.

Example

SQL> SAVE CREDENTIAL
SQL> SAVE CREDENTIAL "./wallet.dat" 
SQL> SAVE HISTORY history.sql
SQL> SAVE HISTORY history.sql APPEND

SET

Sets tbSQL system variables. The variable values can be checked with the SHOW command. However, the changed system variable is valid only in the current session.

For detailed information about system variables, refer to “System Variables”.

Syntax

SET {parameter} {value}  

Item	Description
parameter	Variable name
value	Variable value

Example

SQL> SET AUTOCOMMIT ON

SHOW

Shows tbSQL system variables.

Syntax

SHO[W] {option}

• option

  Item	Description
  system_parameter	Displays the tbSQL system variable with the specified name.
  ALL	Displays all tbSQL system variables.
  ERROR	Displays PSM program errors that occurred previously.
  PARAM[ETERS] [name]	Displays the database system variable with the specified name. If name is omitted, all the system variables are displayed.
  RELEASE	Displays the release information of tbSQL.
  SQLCODE	Displays the SQLCODE for the most recently executed SQL.

Example

SQL> SHOW autocommit 
SQL> SHOW all
SQL> SHOW error
SQL> SHOW param db_name 
SQL> SHOW release
SQL> SHOW SQLCODE

SPOOL

Saves all screen outputs to a file in the current directory. The results of the HOST and ! commands are excluded.

Syntax

SP[OOL] [filename [APP[END]]|OFF]

Item	Description
None	Displays the current execution state of this command.
filename	Name of the file to save the output.
APP[END]	Option to add the output to the end of the output file.
OFF	Stops spooling.

Example

SQL> SPOOL report.txt 
SQL> SPOOL OFF

START

Executes a script file. (Same as the @ command.)

Syntax

STA[RT] {filename}

Item	Description
filename	Script file name.

TBDOWN

Ends Tibero database. Select one of four options depending on the urgency. Some options require recovery process when rebooting the database.

To execute this command, connect to the database as SYSDBA or SYSOPER.

Syntax

TBDOWN [NORMAL|POST_TX|IMMEDIATE|ABORT]

Item	Description
NORMAL	Waits until all users end their connections. (Default value)
POST_TX	Waits until currently running transactions are ended.
IMMEDIATE	Rolls back currently running transactions and then forcibly ends the database.
ABORT	Ends the database immediately without rolling back currently running transactions.

Example

SQL> TBDOWN
SQL> TBDOWN ABORT

UNDEFINE

Deletes substitution variables defined with the ACCEPT or another command.

Syntax

UNDEF[INE] [variable...]

Item	Description
None	Deletes all substitution variables.
variable...	Substitution variable names.

Example

SQL> UNDEFINE x
SQL> UNDEFINE x y z  

VARIABLE

Declares user bind variables that are available in PSM programs or SQL statements.

Syntax

VAR[IABLE] [variable [datatype]]

Item	Description
None	Displays all bind variables.
variable	Bind variable name
datatype	Data type. Options are: NUMBER CHAR(n) VARCHAR(n) VARCHAR2(n) NCHAR(n) NVARCHAR2(n) RAW(n) BLOB CLOB NCLOB DATE TIMESTAMP REFCURSOR

Example

SQL> VARIABLE x NUMBER
SQL> EXEC :x := 1;

PSM completed.

SQL> SELECT :x FROM DUAL;
        :x
----------
         1
1 row selected.
SQL>

WHENEVER

Defines tbSQL action to take when an error occurs.

Syntax

WHENEVER {OSERROR|SQLERROR}
    {EXIT [SUCCESS|FAILURE|WARNING|n|variable|:variable] | 
    CONTINUE [NONE|COMMIT|ROLLBACK]}

• clause1

Item	Description
OSERROR	Performs the specified action for OS errors from the system on which tbSQL is running.
SQLERROR	Performs the specified action for errors that occur while executing SQL statements. tbSQL errors are ignored.

• clause2 (Default value: CONTINUE)

Item	Description
EXIT	Ends the program when an error occurs. For information about return codes, refer to the EXIT command.
CONTINUE	Continues on to the next command when an error occurs. NONE: Does not process the transaction. (Default value) COMMIT: Commits the transaction. ROLLBACK: Rolls back the transaction.

Example

SQL> whenever sqlerror exit failure rollback 
SQL> select 1 from no_such_table;
TBR-8033: Specified schema object was not found. 
at line 1, column 16:
select 1 from no_such_table
$ echo exit code: $? 
exit code: 1
$

---

Culumn Formats

This section describes data types for tbSQL columns.

The column format can be specified and displayed using the COLUMN command.

Character Type

The length of CHAR, NCHAR, VARCHAR, and NVARCHAR types is the same as that of a database column by default. If data length is greater than column length, the data is written in the next line or truncated. This issue can be easily handled with a character type format.

Syntax

COL[UMN] {name} FOR[MAT] A{n}

Item	Description
name	Column name.
A{n}	A can be replaced with a. n indicates the length of character data.

Example

SQL> SELECT 'Tibero is the best choice' test FROM DUAL; 

TEST
--------------------------
Tibero is the best choice

1 row selected.

SQL> COL test FORMAT a10
SQL> SELECT 'Tibero is the best choice' test FROM DUAL; 

TEST
----------
Tibero is
the best c 
hoice

1 row selected.

SQL>

Numeric Type

The following describes how to set a numeric column format.

Syntax

col_name} FOR[MAT] {fmt_str}

• input

Item	Description
col_name	Column name.
fmt_str	Column formats described in the following table.

• fmt_str format The following shows formats that can be specified in fmt_str.

Format	Example	Description
Comma (,)	9,999	Displays a comma (,) at the specified location.
Period (.)	9.999	Displays a period (.) to separate integer and decimal parts.
$	$9999	Displays $ at the beginning.
0	0999, 9990	Displays 0 at the end.
9	9999	Displays as many digits as the specified number of digits.
B	B9999	If the integer part is 0, it is replaced with a blank space.
C	C9999	Displays ISO currency symbol at the specified location.
D	9D999	Displays a decimal character to separate the integer and decimal parts of a real number.
EEEE	9.99EEEE	Displays scientific notation.
G	9G999	Displays a group separator at the specified location of the integer part.
L	L9999	Displays the local currency symbol at the specified location.
MI	9999MI	Displays a minus sign after a negative number or a blank space after a positive number.
PR	9999PR	Displays a negative number enclosed in '<' and '>' (angle brackets) or a positive number enclosed in blank spaces.
RN	RN	Displays as uppercase roman numerals.
rn	rn	Displays as lowercase roman numerals.
S	S9999, 9999S	Displays the positive or negative sign at the beginning or end.
TM	TM	Displays the smallest number.
U	U9999	Displays the dual currency symbol at the specified location.
V	99V999	Displays the value multiplied by 10n. n is the number of 9 that comes after V.
X	XXXX, xxxx	Displays in hexadecimal format.

Example

SQL> COLUMN x FORMAT 999,999 
SQL> SELECT 123456 x FROM DUAL;

        X
----------
  123,456

1 row selected.