The following example illustrates how to execute the tbSQL utility.

$ tbsql

tbSQL 5 SP1

Copyright (c) 2001-2011 TmaxData Corporation. All rights reserved.

SQL>

When tbSQL is executed normally, a SQL prompt as shown above will appear. Database users can execute SQL statements to this prompt.

The following is the syntax of commands that execute the tbSQL utility.

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

The following options are valid entries for the options parameter.

connect_string contains the user account information for Tibero. It is specified as follows:

username[/password[@connect_identifier]]

The following options are valid entries for the connect_string parameter.

start_script sets the script files that will be executed when tbSQL utility starts. It is specified as follows:

@filename[.ext]

The following options are valid entries for the start_script parameter.

When the SQL prompt appears after executing the tbSQL utility, users can access the database.

If there are any tasks that should be processed before starting the database session, write 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.

See the following for the method of accessing a database using the tbSQL utility.

$ tbsql SYS/syspassword

tbSQL 5 SP1

Copyright (c) 2001-2011 TmaxData Corporation. All rights reserved.

Connected.

SQL>

In the previous example, a username and password are entered at the UNIX shell prompt, when tbSQL starts.

Rules for entering a username and password are as follows:

If connect_identifier is omitted, similar to [Example 1.2], the default database is accessed. To access a specific database, specify connect_identifier in one of the following methods.

The following example shows the execution of a tbSQL utility.

$ tbsql

tbSQL 5 SP1

Copyright (c) 2008, 2009, 2011, 2012 TmaxData Corporation. All rights reserved.

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

SQL>

In the previous example, after executing tbSQL utility, it accesses the database using the username 'dbuser' and the CONNECT command. The tbSQL utility receives the input in text format, and displays the result when the user requests it.

tbSQL utility is executed by an interface that has the following features.

To set the configuration environment of the tbSQL utility, the SET command is used. By using the SET command, users can configure the output format of a SQL query result, the transaction commit option, etc.

The following is the syntax of the SET command.

SET [system_variable] [system_variable_value]

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

SQL> EXIT

Specifies the automatic commit option after processing SQL statements, e.g. INSERT, DELETE, UPDATE, etc.

The following is the syntax of AUTOCOMMIT.

Shows the statistical or plan information for a query being processed. DBA or PLUSTRACE permissions are required to use AUTOTRACE. PLUSTRACE contains the authorities required for AUTOTRACE. A user with the DBA authority can generate the PLUSTRACE authority and can 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 tbPSM statement input.

The following is the syntax for BLOCKTERMINATOR.

Specifies a column separator character to be printed between the selected columns.

The following is the syntax for COLSEP.

Specifies a character that indicates that a character string is appended to a substitution variable.

The following is the syntax for CONCAT.

Specifies whether to show the statistical or plan information of the DDL statement being processed. To use this system variable, AUTOTRACE must also be activated.

The following is the syntax for DDLSTATS.

Specifies the character to be used when defining a substitution variable.

The following is the syntax for DEFINE.

Specifies whether to list each query in a script file as it is being executed using an @ or the START command.

The following is the syntax for ECHO.

Specifies the default value of a filename that is to be used with the EDIT command. When omitting the extension, use the value specified in FILEEXT.

The following is the syntax for EDITFILE.

Specifies the character used as the escape character. After enabling ESCAPE, if the specified escape character is attached in 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 a utility.

The following is the syntax for EXITCOMMIT.

Specifies whether to display the SQL statement result on the screen.

The following is the syntax for FEEDBACK.

Specifies the file extension to be used when the extension is omitted.

The following is the syntax for FILEEXT.

Specifies the file path to be used when the path is omitted.

The following is the syntax for FILEPATH.

Specifies whether to display column headers when displaying query execution results.

The following is the syntax for HEADING.

Specifies the size of the command history.

The following is the syntax for HISTORY.

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

The following is the syntax for LINESIZE.

Specifies the display 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 default column format of number type data. This applies to number type columns, not to columns with FORMAT defined by the COLUMN command.

The following is the syntax for NUMFORMAT.

Specifies the size of result to display. This value must not exceed the size specified in LINESIZE.

The following is the syntax for NUMWIDTH.

Specifies the number of lines to print on each output page from the tbSQL utility.

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 whether to display the result of the DBMS_OUTPUT package.

The following is the syntax for SERVEROUTPUT.

Specifies the prompt character string on the screen.

The following is the syntax for SQLPROMPT.

Specifies the character for terminating a SQL statement.

The following is the syntax for SQLTERMINATOR.

Specifies whether to display the result of command, executed using a script, on the screen.

The following is the syntax for TERMOUT.

Specifies whether to display the current time on the prompt.

The following is the syntax for TIME.

Specifies the period of time to wait for a server response to a PING command.

The following is the syntax for TIMEOUT.

Specifies whether to display the processing time when the SQL and PSM statements are displayed.

The following is the syntax for TIMING.

Specifies whether to truncate spaces at the end of each line, every time execution results of SQL, PSM statements are displayed.

The following is the syntax for TRIMOUT.

Specifies whether to truncate spaces at the end of each line, every time execution results of SQL, PSM statements are spooled.

The following is the syntax for TRIMSPOOL.

Specifies whether to display the details of a SQL command before and after replacing substitution variables with values.

The following is the syntax for VERIFY.

When the result is longer than the specified LINESIZE, it specifies whether to display the rest of the result on the following line, or only display up to the LINESIZE.

The following is the syntax for WRAP.

SQL statement, PSM program, and tbSQL utility command are the three commands that can be entered in a command prompt of the tbSQL utility. To enter each command, users can use similar methods.

The following item explains about how to enter each command.

Entering SQL Statement

How to enter SQL statement.

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

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

SQL>

Entering PSM Programs

PSM program is consisted of multiple SQL statements or PSM statements. Each SQL statement is finished with a semicolon (;). When users start entering the PSM program, tbSQL utility will be automatically converted to PSM program input mode. In the PSM program input mode, SQL statements will not be executed after SQL statements are all entered.

The following statements are the ones that convert tbSQL utility to PSM program input mode. One is anonymous block such as DECLARE, BEGIN, etc., and the other is CREATE (OR REPLACE) PROCEDURE, FUNCTION, and TRIGGER that create procedure, function, and trigger.

Method of entering PSM programs is similar to the way of entering SQL statements.

How to enter PSM programs.

The following example illustrates how to enter an anonymous block in tbSQL utility.

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 example above, you will be able to see an inserted comment line and also a period (.) under the END statement which terminates the PSM program entry. The last line contains only the block termination character (in this example, it is a period) without any other character or string.

Entering tbSQL Utility Commands

tbSQL utility command contains the commands to perform SQL or manage Tibero database. For more detailed information about tbSQL utility command, refer to “1.6. Command ”.

There are 3 ways to execute a command that is entered in command prompt of the tbSQL utility.

tbSQL command does not provide any special command to execute a SQL statement or a PSM program. It is not saved in the SQL buffer. tbSQL utility command is executed at the same time it is entered.

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

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

In the first SQL prompt, SQL statement is entered and then the entry is finished with a semicolon (;) to be executed right away. In the second SQL prompt, the / command is entered to execute the SQL statement saved in SQL buffer. SQL buffer has the latest SQL statement thus the SQL statement entered in the first SQL prompt will be executed. Consequently, execution result ① and ② will have the same output.

The following example illustrates how to execute the above SQL statement using slash (/) command. Users should not add a semicolon (;) 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 the output. These are the most commonly used functions in the tbSQL utility.

Inserting a Comment

Comments can be inserted in the following two ways.

Auto Commit

The content submitted in a SQL statement does not update the database until the transaction is committed. One transaction usually consists of several SQL statements.

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

The SET AUTOCOMMIT command is used to specify whether to perform the commit automatically or not. To check the current setting, use SHOW AUTOCOMMIT command.

Executing OS command

To execute the OS command when tbSQL utility has started, enter HOST command.

The following example illustrates how to list all script files with .sql extension. Users can use the ! command instead of the HOST command.

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

After executing the OS command, another tbSQL utility prompt will appear. Users can continue to enter other tbSQL utility commands.

If you omit the statement that comes after HOST or ! command, the OS command prompt will appear. To return to tbSQL utility, enter EXIT.

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

Saving the Output

To save all the content that is entered in and generated from the tbSQL utility, as a text file, use the SPOOL command. The SPOOL command is used to save the user-entered SQL statement or PSM program, tbSQL utility command, query result, program execution result, and tbSQL prompt.

If you execute the SPOOL command, it will start saving from the next line. To stop this function, enter SPOOL OFF. The lines following the SPOOL OFF command will not be saved.

The following example illustrates how to use the SPOOL command. If the save.txt file, which is used in SPOOL command, already exists, the new file will replace the old file and the old file will disappear.

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 shows the contents saved in save.txt file from the example above.

SQL> SELECT
    *
    FROM DUAL;

DUMMY
-----
X

1 row selected.

SQL> SPOOL OFF

The user-entered SQL statement, query result, and commands up to the SPOOL OFF command, are saved in the file.

The Script is a group of SQL statements, PSM programs, and tbSQL utility commands used to perform batch processing. When the script is executed in the tbSQL utility, all the commands in the script will be executed in order.

Creating a Script

A Script file can be created and edited externally, and then executed in the tbSQL utility. Or users can execute tbSQL utility first and then create and edit the script by calling an external editor. When users call an external editor, they can specify which editor to use.

The following example illustrates how to use vi using an external editor.

$ export TB_EDITOR=vi

To edit a certain script file using an external editor, use the EDIT command. Users should specify the file name by using the EDIT command. The file extension can be omitted if the extension is the same as the FILEEXT system variable.

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

SQL> EDIT run

Users can enter a SQL statement,PSM program, and tbSQL command into a script file using the following methods:

When a script is executed, SQL statements will be executed immediately. A PSM program will be executed after entering the RUN or / command.

The following example shows a script file which performs several tasks in the table EMP. Blank space is 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. When using these commands, you should specify a file name. The file extension can be omitted if the extension is the same as FILEEXT system variable (sql).

The following example illustrates how to execute a script file, run.sql. The results will be the same.

SQL> START run
SQL> @run

One or more script files can be executed in a single script file. Script file can contain START or @ command. Make sure not to have an infinite loop when executing a script file recursively.

When @ command is used to start the tbSQL utility, a script file is created and started at the same time. This is useful when executing a batch program in the OS.

The following example illustrates how to run a script file, run.sql, at the same time as the tbSQL utility is started.

$ tbsql dbuser/dbuserpassword @run

Or a redirection shell command can be used to run a script:

$ tbsql dbuser/dbuserpassword < run.sql

Users can process DBA functions with tbSQL utility. To do so, login to Tibero as a user who has the DBA authority.

The following example illustrates how to log in as a SYS user who has DBA authority.

$ tbsql sys/syspassword

You can connect to a database as a DBA even after starting the tbSQL utility. Users should use the CONNECT command. Connect to the database as a user who has DBA authority.

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

SQL> CONNECT sys/syspassword

What the DBA function can do with the tbSQL utility.

The tbSQL utility saves database access information(connect_string) with the wallet.dat file

The information in the database, which the tbSQL

Creating an Encrypted File

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

The following example illustrates how to encrypt the database information by specifying ISQL_WALLET_PATH to have a wallet.dat file in the current directory.

$ export ISQL_WALLET_PATH=./wallet.dat
$ tbsql

tbSQL 5 SP1

Copyright (c) 2008, 2009, 2011, 2012 TmaxData Corporation. 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.

ISQL_WALLET_PATH is specified before executing tbSQL

Users should connect to the database again and create a ./wallet.dat file using SAVE CREDENTIAL command.

The following example illustrates how to encrypt information from databases accessed by the user in the wallet.dat file of the current directory, without setting the environment variable of ISQL_WALLET_PATH.

$ tbsql

tbSQL 5 SP1

Copyright (c) 2008, 2009, 2011, 2012 TmaxData Corporation. All rights reserved.

SQL> CONN dbuser/dbuserpassword
Connected to Tibero.

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

When a ./wallet.dat file is specified by using SAVE CREDENTIAL, the information from the database will be encrypted and a ./wallet.dat will be created.

Using an Encrypted File

By specifying the ./wallet.dat file, which is created in the ISQL_WALLET_PATH before the tbSQL utility is executed, the user can use the information in the database, before the encrypted file is created.

The following example illustrates how to access a database by using the file specified in ISQL_WALLET_PATH.

$ tbsql

tbSQL 5 SP1

Copyright (c) 2008, 2009, 2011, 2012 TmaxData Corporation. All rights reserved.

SQL> CONN dbuser/dbuserpassword
Connected to Tibero.

Reruns the command stored in the tbSQL utility history buffer. The commands do not need to be re-entered.

The following is the syntax for % command.

Executes the OS commands in tbSQL utility. The HOST command can be used instead of ! command.

The following is the syntax for ! command.

Executes the script file. If the script file has the same file extension that is registered in FILEEXT system variables, the name of the script file can be specified without an extension. The tbSQL utility finds the specified script file from a directory that is specified by the FILEPATH system variable.

The System variable specified by the SET command before the script file is executed, is still valid while executing the script file. Enter the EXIT or QUIT command in a script file to terminate the tbSQL utility.

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

The following is the syntax for @ 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 the user input and specifies the attribute of a substitution variable. The specified substitution variable will be substituted automatically if there is any SQL statement which user entered, or a word which matches the &variable in a PSM program.

The following is the syntax for the ACCEPT command.

Searches for the first old pattern in the current line of a statement in a SQL buffer, and then converts it to a new pattern. 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 CHANGE command.

Initializes or deletes the specified option.

The following is the syntax for CLEAR command.

Specifies the output property of the column. When the column name is specified, property of the relevant column will be displayed. When the column name is not specified, all the registered columns will be displayed.

The following is the syntax for COLUMN command.

Accesses a Tibero using another user's ID. If a user name or password is not entered, tbSQL utility will display a prompt and ask the information (name or password).

When the CONNECT command is used, it will commit the previous transaction, cancel the previous access, and then try to access again. Even if the new access fails, the previous access will not be recovered.

The following is the syntax for the CONNECT command.

Defines or displays bind 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. Objects can be table, view, synonym, function, procedure, or package.

The following is the syntax for the DESCRIBE command.

Closes the connection to the current database. The transaction in progress will be committed, but tbSQL utility will not be terminated.

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.

Edits the contents of a SQL buffer or a certain file by using an external editor. Users can choose an external editor to use by specifying the environment variable $TB_EDITOR.

If $TB_EDITOR is not registered, refer to the environment variable $EDITOR. And if $EDITOR is not registered either, use vi editor to edit. If a SQL buffer is empty, an error will be returned.

A file name can be specified without any extension if it is set to the default value. The default value of FILEEXT system variable is .sql, and it can be changed using the SET command. The tbSQL utility searches for the specified file in the path specified in FILEPATH system variable.

The following is the syntax for EDIT command.

Processes a single PSM statement. Only CALL statements and anonymous blocks are applicable. There must be a semicolon (;) at the end of the user-entered statement.

The following is the syntax for EXECUTE command.

Terminates tbSQL utility. Commits all transactions being processed and terminates all the accesses with database.

Outputs the Help of any items that include the specified word.

The following is the syntax for HELP command.

Displays the history of commands that are saved in history buffer.

The following is the syntax for HISTORY command.

It is the same as ! command.

The following is the syntax for HOST command.

Adds a new line, which is entered by a user, under the last line in SQL buffer.

The following is the syntax for INPUT command.

Displays certain contents saved in SQL buffer onto the screen.

The following is the syntax for the LIST command.

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

The following is the syntax for LOADFILE 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.

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 on the screen.

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.

It is the same as the / command. However, this command shows the SQL statement, currently being processed, on the screen.

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.3. 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.

Starts or terminates the process which saves all the contents shown in the screen in an external file. The output file will be created in the current directory.

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.