The following example illustrates how to execute tbSQL.

$ tbsql

tbSQL 6

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

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

The following is the command syntax for executing tbSQL.

When the SQL prompt appears after executing tbSQL, it is ready to connect to the database.

If there are any tasks that need to be processed before starting the database session, create the tbsql.login file. If this file can't be found in the current directory, it can be found in the directory specified in the environment variable, TB_SQLPATH.

The following shows how to connect to the database in tbSQL.

$ tbsql SYS/syspassword

tbSQL 6

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

Connected.

SQL>

In the previous example, username and password are entered at the UNIX shell prompt that is displayed after starting tbSQL.

Rules for entering the username and password are as follows:

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

The following example shows tbSQL utility execution.

$ tbsql

tbSQL 6

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

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

In the previous example, after starting tbSQL a connection is made to the database using the CONNECT command with the username 'dbuser'. tbSQL is a text-based user interface.

The tbSQL interface that supports the following features:

Use the SET command to configure tbSQL. It can be used to configure various settings including SQL query result output format and transaction commit option.

The following is the syntax of the SET command.

SET [system_variable] [system_variable_value]

To terminate tbSQL, enter the EXIT or QUIT command at the SQL prompt.

SQL> EXIT

Executes commit after an INSERT, UPDATE, DELETE, MERGE, or PSM block is successfully executed.

The following is the syntax of AUTOCOMMIT.

Shows the statistical or plan information for the current query. DBA or PLUSTRACE privileges are required to use AUTOTRACE. PLUSTRACE role includes the permissions required to use AUTOTRACE. A user with the DBA privileges can create the PLUSTRACE role and grant it to other users. The creation script is $TB_HOME/scripts/plustrace.sql.

The following is the syntax for AUTOTRACE.

Specifies a character to indicate the end of a tbPSM statement input.

The following is the syntax for BLOCKTERMINATOR.

Specifies a column separator character to print between columns of the results.

The following is the syntax for COLSEP.

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

The following is the syntax for COLSEP.

Specifies whether to show the statistical or plan information for the running DDL statement. To use this system variable, AUTOTRACE must also be enabled.

The following is the syntax for DDLSTATS.

Specifies the character used to define a substitution variable.

The following is the syntax for DEFINE.

Specifies the depth of the object specification shown by the DESCRIBE command.

The following is the syntax for DESCRIBE.

Specifies whether to display queries as they are being executed when a script is executed by using @ or START command

The following is the syntax for ECHO.

Specifies the default value of a filename used with the EDIT command. When the extension is omitted, the value set in SUFFIX is used.

The following is the syntax for EDITFILE.

Specifies the escape character. If ESCAPE is enabled and the escape character is appended at the front of '&<character string>', it is not recognized as a substitution variable.

The following is the syntax for ESCAPE.

Specifies whether to commit when terminating the utility.

The following is the syntax for EXITCOMMIT.

Specifies whether to display the SQL statement result.

The following is the syntax for FEEDBACK.

Specifies whether to display column headers for query results.

The following is the syntax for HEADING.

Specifies the line-break character used in column headings.

The following is the syntax for HEADSEP.

Specifies the size of the command history.

The following is the syntax for HISTORY.

Specifies the interval for executing each statement in the LOOP command.

The following is the syntax for INTERVAL.

Specifies the length of a line on the screen. Minimum value is 1 and the maximum value depends on the OS.

The following is the syntax for LINESIZE.

Specifies the output size of the CLOB, BLOB, NCLOB, LONG, or XML data type. Maximum length is 2 billion.

The following is the syntax for LONG.

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

The following is the syntax for NEWPAGE.

Specifies the default column format of numeric data. This applies numeric columns except those with FORMAT defined through the COLUMN command.

The following is the syntax for NUMFORMAT.

Specifies the default width for outputting numeric data. This value cannot exceed the size set in LINESIZE.

The following is the syntax for NUMWIDTH.

Specifies the number of lines to output on each page.

The following is the syntax for PAGESIZE.

Specifies whether to wait for user input before displaying the next page.

The following is the syntax for PAUSE.

Specifies where to print the row separator.

The following is the syntax for RECSEP.

Specifies the row separator character to print after each line of the size, LINESIZE.

The following is the syntax for RECSEPCHAR.

Specifies whether to display query statement result.

The following is the syntax for ROWS.

Specifies whether to display the result of the DBMS_OUTPUT package.

The following is the syntax for SERVEROUTPUT.

Specifies the prompt string.

The following is the syntax for SQLPROMPT.

Specifies the character for terminating a SQL statement.

The following is the syntax for SQLTERMINATOR.

Specifies the default file extension.

The following is the syntax for SUFFIX.

Specifies whether to display the result of executing commands in a script.

The following is the syntax for TERMOUT.

Specifies whether to display the current time in the prompt.

The following is the syntax for TIME.

Specifies the timeout for a server response to a PING command.

The following is the syntax for TIMEOUT.

Specifies whether to display the processing time with the SQL and PSM statement results.

The following is the syntax for TIMING.

Specifies whether to truncate whitespaces at the end of each line, every time SQL or PSM results are displayed.

The following is the syntax for TRIMOUT.

Specifies whether to truncate to truncate whitespaces at the end of each line while spooling.

The following is the syntax for TRIMSPOOL.

Specifies the character used to underline headers.

The following is the syntax for UNDERLINE.

Specifies whether to display the command after replacing substitution variables with values.

The following is the syntax for VERIFY.

Specifies whether to wrap the output to the next line when it exceeds the line size.

The following is the syntax for WRAP.

A SQL statement, PSM program, or tbSQL command can be entered at the tbSQL command prompt. Each command can be entered in the same way.

The following describes how to enter each command.

SQL Statement

How to enter a SQL statement:

The following example illustrates how to enter a SQL statement in tbSQL.

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

PSM Programs

PSM program consists of multiple SQL or PSM statements. Each SQL statement is terminated with a semicolon (;). When the user starts to enter PSM program, tbSQL will be automatically switched to PSM program input mode. In the PSM program input mode, SQL statements are not executed until the entire program is entered.

The statements that trigger tbSQL to switch to PSM program input mode are anonymous blocks such as DECLARE and BEGIN, and those that create a procedure, function, and trigger (CREATE (OR REPLACE) PROCEDURE, FUNCTION, and TRIGGER).

PSM programs can be entered like SQL statements.

How to enter a PSM program:

The following example illustrates how to enter 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, there is a comment line and a period (.), which terminates the PSM program, below the END statement. The last line contains only the block terminator character (a period) without any other characters or strings.

tbSQL Utility Commands

tbSQL utility commands are used to execute SQL or manage Tibero database. For more detailed information about tbSQL utility commands, refer to “1.6. Commands”.

There are 3 ways to execute a command from the command prompt in tbSQL.

There is no separate tbSQL utility command for executing commands unlike SQL statement or PSM program. The commands are not saved in the SQL buffer, and each command is automatically executed as it is entered.

The following example illustrates how to execute a SQL statement that is saved in the SQL buffer.

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

At the first SQL prompt, a SQL statement is immediately executed when it is terminated with a semicolon (;). At the second SQL prompt, the '/' command executes the last SQL statement saved in the SQL buffer, which is the SQL entered at the first prompt. Hence, the execution result (1) and (2) will be the same.

The following example illustrates how to execute the previous SQL statement using the slash (/) command. In this case, 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>

This section describes the functions for inserting a comment, auto commit, executing OS command, and saving output, which are the most commonly used functions in tbSQL.

Inserting Comments

Comments can be inserted in the following two ways.

Auto Commit

A SQL execution does not update the database until the transaction is committed. A transaction usually consists of multiple SQL statements.

In tbSQL, the AUTOCOMMIT parameter can be set to ON or OFF. By default, it is set to OFF.

Use the SET AUTOCOMMIT command to set auto commit on or off. To check the current setting, use the SHOW AUTOCOMMIT command.

Executing OS Commands

To execute an OS command in tbSQL, use the HOST command.

The following example illustrates how to list all script files with .sql extension. The '!' command can be used in place of the HOST command.

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

After executing an OS command, the tbSQL prompt is displayed again to accept other tbSQL commands.

If the HOST or '!' command is entered without additional commands, the tbSql interface switches to the OS command line interface. To return to tbSQL, enter EXIT.

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

Saving the Output

To save all inputs and outputs of tbSQL as a text file, use the SPOOL command. The SPOOL command saves all user-entered SQL statements or PSM programs, tbSQL utility commands, query results, program execution results, and tbSQL command prompts.

If you execute the SPOOL command, it will start saving from the next line. To stop the command, enter SPOOL OFF and any following lines are not saved.

The following example illustrates how to use the SPOOL command. If the specified file, save.txt, already exists, the existing file will be overwritten by the new file.

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 is an example of the save.txt file from the previous example.

SQL> SELECT
    *
    FROM DUAL;

DUMMY
-----
X

1 row selected.

SQL> SPOOL OFF

Any user-entered SQL statements, query results, and commands are saved in th file until SPOOL OFF command is executed.

A script consists of SQL statements, PSM programs, and tbSQL utility commands used to perform batch processing. When the script is executed in tbSQL, the commands in the script are executed sequentially.

Creating a Script

A script file can be created and edited externally and executed in tbSQL, or an external editor can be executed from tbSQL by specifying the editor to use.

The following example illustrates how to execute vi, an external editor, in tbSQL.

$ export TB_EDITOR=vi

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

The following example illustrates how to call an external editor to edit the script file, run.sql.

SQL> EDIT run

A SQL statement, PSM program, or tbSQL command can be added to a script file as follows:

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

The following example shows a script file that performs multiple operations on the table EMP. 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 -- Execute PSM program
/* Reflect the final updates. */
COMMIT;

Executing a Script

To execute a script file, use the START or @ command with the file name. The file extension can be omitted if the extension is the same as the SUFFIX system variable (sql).

The following example illustrates how to execute a script file, run.sql, using the two commands that produce the same results.

SQL> START run
SQL> @run

One or more script files can be executed from within a script file by calling the START or @ command. Make sure not to create an infinite loop when executing a script file recursively.

The @ command can be executed when starting tbSQL to execute a script at startup. This is useful when executing a batch program from the OS.

The following example illustrates how to run a script file, run.sql, at tbSql startup.

$ tbsql dbuser/dbuserpassword @run

Or the redirect shell command can be used to execute the script as follows:

$ tbsql dbuser/dbuserpassword < run.sql

DBA functions can be executed in tbSQL by loggin into Tibero as a user with DBA privileges.

The following illustrates how to log in as a SYS user with DBA privileges.

$ tbsql sys/syspassword

The user can also connect to the database as a DBA after starting tbSQL by using the CONNECT command.

The following illustrates how to connect to the database as a DBA using the CONNECT command.

SQL> CONNECT sys/syspassword

A DBA can perform the following task in tbSQL:

tbSQL can restrict users from executing certain commands.

For this, tbSQL references the CLIENT_ACCESS_POLICY table created by the SYS user. DBA can define the access policy to grant privileges to execute commands to specific users.

tbSQL loads user information when the user connects to the database, and checks the privileges each time the user executes a command to allow/prohit its execution. The user information is released when the user disconnects from the database.

Creating Access Control Table

The access control table can only be created by a SYS user by executing the $TB_HOME/scripts/client_policy.sql file.

A detailed description of the CLIENT_ACCESS_POLICY is as follows:

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

Configuring Command Access Control

A record must be added to the CLIENT_ACCESS_POLICY to restrict access to tbSQL, SQL, or PSM commands. To allow access to these commands again, the record must be deleted.

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

The following are the command lists:

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 user .....
SQL> CONNECT TIBERO
SQL> SELECT * FROM DUAL;
TBS-70082: The 'SELECT' command has been disabled.

tbSQL saves database access information (connect_string) in an encrypted file (wallet).

The database connection information used in tbSQL

Creating an Encrypted File

After accessing a database in tbSQL, the user can create an encrypted file using the SAVE CREDENTIAL command.

The following illustrates how to encrypt the database connection information by setting ISQL_WALLET_PATH to the wallet.dat file in the current directory.

$ export ISQL_WALLET_PATH=./wallet.dat
$ tbsql

tbSQL 6

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 this example,

To resolve the error, reconnect to the database and create the './wallet.dat' file using the SAVE CREDENTIAL command.

The following illustrates how to encrypt the database connection information in the wallet.dat file of the current directory, without setting the environment variable of ISQL_WALLET_PATH.

$ tbsql

tbSQL 6

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.

Execute SAVE CREDENTIAL with the './wallet.dat' file to create the wallet by encrypting the database connection information.

Using an Encrypted File

Set the ./wallet.dat file created in the previous example to the ISQL_WALLET_PATH before starting tbSQL to be able to reuse the database connection information that was used before configuring the encrypted file.

The following illustrates how to access the database by using the file specified in ISQL_WALLET_PATH:

$ tbsql

tbSQL 6

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

Connected to Tibero.

Executes an OS command in tbSQL. The HOST command can be used instead of this command.

The following is the syntax for the '!' command.

Re-executes the command stored in the tbSQL history buffer without re-entering it.

The following is the syntax for the '%' command.

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

The system variable set using the SET command before executing the script remains in effect while executing the script. Use the EXIT or QUIT command in a script file to terminate tbSQL.

The START command can be used instead of the '@' command.

The following is the syntax for the '@' command.

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

RUN command can be used instead of the '/' command.

The following is the syntax for the '/' command.

Receives user input and saves it in a specified substitution variable. The specified value will automatically replace any string in a SQL statement or PSM program specified as &variable.

The following is the syntax for the ACCEPT command.

Displays redo log file information.

The following is the syntax for the ARCHIVE LOG command.

Finds and replaces a specified pattern with a new pattern in the current line of the SQL buffer. Generally, the current line of the last executed SQL statement is the last line. Refer to the example below to change the current line.

The following is the syntax for the CHANGE command.

Initializes or deletes the specified option.

The following is the syntax for the CLEAR command.

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

The following is the syntax for the COLUMN command:

Connects to the database with a specified user ID. If the user name or password is not specified, tbSQL prompts for the name or password.

The CONNECT command commits the last transaction, disconnects from the database, and then tries to reconnect. If the new connection attempt fails, the previous connection cannot be recovered.

The following is the syntax for the CONNECT command.

Defines or displays substitution variables.

The following is the syntax for the DEFINE command.

Deletes the line that is saved in the SQL buffer. If the line number is omitted, all lines will be deleted.

The following is the syntax for the DEL command.

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

The following is the syntax for the DESCRIBE command.

Closes the connection to the current database. This command terminates the currently running transaction but does not terminate tbSQL.

When a transaction is terminated without being disconnected from the database, the transaction will continue to access to the database. For security, if a CONNECT command is included in a script file, it is recommended to execute the DISCONNECT command to terminate a transaction.

The following is the syntax for the DISCONNECT command.

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

If $TB_EDITOR is not set, this command references $EDITOR. If neither variables are set, 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 default value. The default value of the SUFFIX system variable is .sql, and it can be changed using the SET command. tbSQL searches for the specified file in the current directory.

The following is the syntax for the EDIT command.

Processes a PSM statement, which includes only CALL statements and anonymous blocks. There must be a semicolon (;) at the end of the statement.

The following is the syntax for the EXECUTE command.

Terminates tbSQL. Commits all transactions being processed and terminates all connections to the database.

The following is the syntax for the EXIT command.

Exports SELECT statement results or table data to a file with a format that tbLoader can recognize. Must use either a fixed or variable format that uses column and row separators.

The following is the syntax for the EXPORT command.

Outputs help for items that include a specified word.

The following is the syntax for the HELP command.

Displays command execution history that is saved in the history buffer.

The following is the syntax for HISTORY command.

This is same as the '!' command.

The following is the syntax for the HOST command.

Adds a new line after the last line in the SQL buffer.

The following is the syntax for INPUT command.

Displays specified lines from the SQL buffer.

The following is the syntax for the LIST command.

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

The following is the syntax for LOADFILE command.

Infinitely repeats a statement. Enter <Ctrl>+C to terminate the loop.

The following is the syntax for the LOOP command.

Displays the information about the database object, which is created by the current user, in a certain type or name.

The following is the syntax for the LS command.

Changes the user password.

The following is the syntax for the PASSWORD command.

Holds the execution until the user presses <Enter> key. If a message is entered, it will be shown on the screen.

The following is the syntax for PAUSE command.

Displays whether a specified database allows connections.

The following is the syntax for PING command.

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

The following is the syntax for the PRINT command.

Displays a certain message or empty line.

The following is the syntax for the PROMPT command.

It is the same as the EXIT command.

The following is the syntax for the QUIT command.

Restores user selected data from a file.

The following is the syntax for the RESTORE command.

It is the same as the / command. However, this command displays the SQL statement saved in the SQL buffer.

The following is the syntax for the RUN command.

Encrypts the tbSQL utility's database access information and saves it in a file. For detailed information on this command, refer to “1.5.4. Encrypting Connection String”.

The following is the syntax for the SAVE CREDENTIAL command.

Specifies the system variables of the tbSQL utility. The system variables specified by the SET command can be displayed by using the SHOW command. However, the changed system variable is valid only in the current session.

For a detailed description of each system variable, refer to “1.3. System Variables”.

The following is the syntax for SET command.

Displays the system variables of the tbSQL utility. Parameters can be used to select the information to be displayed. It is possible to display all the system variables.

The following is the syntax for SHOW command.

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

The following is the syntax for the SPOOL command

It is the same as the @ command.

The following is the syntax for the START command.

Terminates Tibero. Users have four options to choose depending on the urgency. Some options require recovery process when rebooting the database.

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

The following is the syntax for TBDOWN command.

Deletes a substitution variable specified by the ACCEPT command.

The following is the syntax for UNDEFINE command.

Defines user-defined bind variables that can be used in a PSM program or a SQL statement.

The following is the syntax for the VARIABLE command.

Defines the actions to be taken by tbSQL when an error occurs.

The following is the syntax for the WHENEVER command.

CHAR, NCHAR, VARCHAR, NVARCHAR types have the default value for the length of a database column. When the value of data is greater than the length of a column, the data can be cut. However, the character type column format can easily handle this problem.

The following is the syntax for specifying a character-type column format.

The following is the syntax for specifying a numeric-type column format.