Users who use Tibero are classified into the following groups according to their purpose and role:

If Tibero is installed normally, the system needs to specify the following environment variables:

If environment variables are not properly specified, Tibero cannot be used. Therefore, it is recommended to check the environment variables in advance.

The syntax to check environment variables via a UNIX shell prompt is shown below:

tbSQL is an interactive utility for SQL command processing, which is provided by Tibero^®. It can process SQL queries, data definition language (DDL), and transaction-related SQL statements.

This section describes how to connect to the database using the tbSQL utility and how to execute some simple SQL statements.

tbSQL Utility

Executing the tbSQL utility is shown below:

$ tbsql

tbSQL 6 

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

SQL>

After the tbSQL utility executes normally, the SQL prompt is shown as above. Database users can execute SQL statements within this prompt. If the database connection fails and the SQL prompt does not appear, refer to “Appendix C. Troubleshooting” to solve the problem.

Database Connection

If the SQL prompt is shown on the screen after the tbSQL utility executes, the database is ready to connect to.

Connecting to the database is shown below:

$ tbsql SYS/tibero

tbSQL 6 

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

Connected to Tibero.

SQL>

In the example above, the user name (SYS) and password (tibero) are entered as part of executing the tbSQL utility in a UNIX shell prompt.

When entering user name and password, the following rules apply:

tbSQL Utility Commands

Commands provided by tbSQL are case insensitive and include those necessary for executing SQL statements and managing the database.

The following are the commands can be used in tbSQL. The commands can be executed without including the letters between square brackets ([ ]).

The LS command can be used to view objects in the schema.

The following example shows the execution of the LS command after connecting to the database as the SYS user.

SQL> LS

NAME                           SUBNAME       OBJECT_TYPE
------------------------------ ------------- --------------------
SYS_CON100                                   INDEX
SYS_CON400                                   INDEX
SYS_CON700                                   INDEX
_DD_CCOL_IDX1                                INDEX
        [Some results omitted for brevity]......
UTL_RAW                                      PACKAGE
DBMS_STATS                                   PACKAGE BODY
TB_HIDDEN2                                   PACKAGE BODY

SQL>

The LS command is provided for user convenience and is only usable within tbSQL. In other words, when the connection is made through JDBC or CLI, not tbSQL, this command cannot be used.

In [Example 2.3], all objects in the SYS user's schema are listed. The SYS schema has many objects that are internally used for Tibero to manage itself.

The following example shows how to view the currently connected users with the LS command:

SQL> LS USER

USERNAME
------------------------------
SYS

The following example shows how to view the tablespaces that are currently in the database using the LS command:

SQL> LS TABLESPACE

TABLESPACE_NAME
------------------------------
SYSTEM
UNDO
TEMP
USER

Execution of SQL Statements

The following example shows the execution of an SQL statement that searches for data of the 'WTHR' type using the VT_SESSION view:

SQL> SELECT SID, STATUS, TYPE, WTHR_ID FROM V$SESSION WHERE TYPE = 'WTHR';

       SID STATUS                           TYPE        WTHR_ID
---------- -------------------------------- -------- ----------
        10 ACTIVE                           WTHR              1
        13 RUNNING                          WTHR              1

2 rows selected.

V$SESSION is a view that lists session IDs.

The following is a brief explanation of the SQL standard.

There are some rules that must be obeyed when executing SQL statements. These rules follow the SQL standard, which is the relational or object-relational database language created by ANSI (American National Standard Institute) and ISO/IEC (International Standard Organization/International Electrotechnical Commission).

The SQL standard reached version 2 1992 and reached version in 1999. The SQL standard released in 1992 is called SQL2 or SQL-92 and is a relational database language.

The SQL standard released in 1999 is called SQL3 or SQL-99 and is an object-relational database language with object-oriented concepts added.

SQL statements defined in the SQL standard are divided into:

The SELECT statement included in DML can be executed as follows:

SQL> select SID, STATUS, TYPE, WTHR_ID from v$session where type = 'WTHR';

......Same result as [Example 2.6]......

SQL> select SID, STATUS, TYPE, WTHR_ID from V$SESSION where type = 'WTHR';

......Same result as [Example 2.6]......

SQL> select SID, STATUS, TYPE, WTHR_ID From v$session Where type = 'WTHR';

......Same result as [Example 2.6]......

The SQL standard is case insensitive, so a mixture of uppercase and lowercase letters can be used, except for characters included between double quotes (" ") or single quotes (' '). All the examples above show the same result as [Example 2.6] and their meaning is identical.

However, the following SQL statement has a different meaning, showing a different result:

SQL> SELECT SID, STATUS, TYPE, WTHR_ID FROM V$SESSION WHERE TYPE = 'wthr';

0 row selected.

This section describes how to create a user and a table for use in the database.

Creating Users

The CREATE USER statement is used to create a user.

The following example creates a user named ADMIN and grants it permission to create sessions and tables (CREATE SESSION and CREATE TABLE).

SQL> CREATE USER ADMIN IDENTIFIED BY 'password123';
      ...A user named 'ADMIN' is created with the password 'password123'.
User 'ADMIN' created.

SQL> GRANT CREATE SESSION TO ADMIN;
      ...The permission to create a session is granted to the ADMIN user.
Granted.

SQL> GRANT CREATE TABLE TO ADMIN;
      ...The permission to create a table is granted to the ADMIN user.
Granted.

SQL> CONN ADMIN/PASSWORD123
      ...Connects to the database with the ADMIN user.
TBR-17001: Login failed: invalid user name or password.

No longer connected to server.
      ...Failed to connect to the database because the password is 
         case sensitive.

SQL> CONN ADMIN/password123
      ...Enter the password correctly and retry the connection.
Connected.

SQL> LS
      ...The new user has no schema to show.

SQL>

After the process of [Example 2.8] is completed, the 'ADMIN' user is added to Tibero.

Creating Tables

The CREATE TABLE statement is used to create a table.

The following example creates a table named 'PRODUCT' and inserts four rows of data via INSERT.

SQL> CREATE TABLE "PRODUCT"
    (
      PROD_ID NUMBER(3) NOT NULL CONSTRAINT PROD_ID_PK PRIMARY KEY,
      PROD_NAME VARCHAR(50) NULL,
      PROD_COST NUMBER(10) NULL,
      PROD_PID NUMBER(3) NULL,
      PROD_DATE DATE NULL
    );
Table 'PRODUCT' created.

SQL> SELECT TABLE_NAME FROM USER_TABLES;
      ...USER_TABLES is a static view that lists all tables of 
         the users currently connected to the database.

TABLE_NAME
------------------------
PRODUCT

1 row selected.


SQL> DESC "PRODUCT"
      ...DESC is a command of the tbSQL utility that displays column
         types for a given table.

COLUMN_NAME              TYPE             CONSTRAINT
------------------------ ---------------- --------------------------
PROD_ID                  NUMBER(3)        PRIMARY KEY
                                          NOT NULL
PROD_NAME                VARCHAR(50)
PROD_COST                NUMBER(10)
PROD_PID                 NUMBER(3)
PROD_DATE                DATE

INDEX_NAME               TYPE    COLUMN_NAME
------------------------ ------- ------------------------------
PROD_ID_PK               NORMAL  PROD_ID


SQL> INSERT INTO "PRODUCT" VALUES(601,'TIBERO',7000,'',
to_date('2004-12-31 09:00:00', 'yyyy-mm-dd hh24:mi:ss'));
           ...If double quotes (" ") are used in PRODUCT, characters,
           other than alphabetical, can be used for a schema or user name 
           according to SQL standards.


           ... According to SQL standards, use single quotes for string 
           data that is inserted to a database directly...
1 row inserted.

SQL> INSERT INTO "PRODUCT" VALUES(602,'TIBERO2',8000,'601',
to_date('2005-06-21 09:00:00', 'yyyy-mm-dd hh24:mi:ss'));
1 row inserted.

SQL> INSERT INTO "PRODUCT" VALUES(603,'TIBERO3',9000,'601',
to_date('2007-01-01 09:00:00', 'yyyy-mm-dd hh24:mi:ss'));
1 row inserted.

SQL> INSERT INTO "PRODUCT" VALUES(604,'TIBERO4',10000,'601',
to_date('2009-04-30 09:00:00', 'yyyy-mm-dd hh24:mi:ss'));
1 row inserted.

SQL> SELECT * FROM "PRODUCT";
      ...This SQL statement displays all data in the PRODUCT table.

   PROD_ID PROD_NAME             PROD_COST    PROD_PID  PROD_DATE
---------- -------------------  ----------  ----------  ----------
       601 TIBERO                    7000               2004/12/31
       602 TIBERO2                   8000          601  2005/06/21
       603 TIBERO3                   9000          601  2007/01/01
       604 TIBERO4                  10000          601  2009/04/30

4 rows selected.

In addition to USER_TABLES, various static views are provided by Tibero. Using these views, current database users can see information about accessible schema objects.

After the table has been created and data has been inserted, leave this console window open and open another console window.

The following example uses the tbSQL utility to connect to Tibero with the ADMIN user created in [Example 2.8].

$ tbsql ADMIN/password123

tbSQL 6 

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

Connected to Tibero.

SQL> SELECT * FROM "PRODUCT";

0 row selected.

In the example above, the four rows of data are not present as in [Example 2.9]. Because Tibero supports transactions, session data is invisible to other sessions until the data is committed. Therefore, move to the previous console window ([Example 2.9]) and execute the COMMIT command to see the four rows of data.

Executing the COMMIT command is shown below:

SQL> COMMIT;
Commit succeeded.

After the COMMIT is finished, execute a SELECT command in the second console window to see that the four rows of data are displayed.

Usage

The following describes the execution of some SQL statements in an example scenario:

Based on this scenario, the following SQL statements are executed:

SQL> UPDATE "PRODUCT" SET PROD_COST = PROD_COST * 1.1
    WHERE PROD_COST < 8500;
2 rows updated.

SQL> SELECT PROD_NAME, PROD_COST FROM "PRODUCT";

PROD_NAME                                           PROD_COST
-------------------------------------------------- ----------
TIBERO                                                   7700
TIBERO2                                                  8800
TIBERO3                                                  9000
TIBERO4                                                 10000

4 rows selected.

      ...The UPDATE statement raised product prices which are less than 8,500 Won
         by 10%.

SQL> ROLLBACK;
      ...Rolls back to the previous state.

Rollback succeeded.


SQL> DELETE FROM "PRODUCT" WHERE PROD_NAME = 'TIBERO2';
      ...The TIBERO2 product is no longer for sale.
         Thus, this product is deleted from the PRODUCT table.

1 row deleted.

SQL> SELECT PROD_NAME, PROD_COST FROM "PRODUCT";
      ...Shows the PRODUCT table. The TIBERO2 product has been deleted, and
         the TIBERO products which had their prices raised by 10%
         have been rolled back. 

PROD_NAME                                           PROD_COST
-------------------------------------------------- ----------
TIBERO                                                   7000
TIBERO3                                                  9000
TIBERO4                                                 10000

3 rows selected.

SQL> quit
      ...The quit command commits the current transactions and ends the database
         connection. After the transaction is finished, the TIBERO2 product has
         been completely removed from the PRODUCT table.
Disconnected.

$

When executing SQL statements, permissions given to users are very important. If a user is granted a DBA role, it is easy to execute any SQL statement while managing the database. Otherwise, users need to reconnect to the database with the SYS user or a user with the DBA role, and grant those permissions necessary to execute certain commands. To solve this problem, a method exists to create a role by combining various permissions.

The following example connects to the database with the SYS user and grants the DBA role to the ADMIN user created in [Example 2.8].

$ tbsql SYS/tibero

tbSQL 6 

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

Connected to Tibero.

SQL> GRANT DBA TO ADMIN;
Granted.

If the ADMIN user is no longer needed, execute the DROP command below:

SQL> DROP USER ADMIN;
TBR-7139: cascade is required to remove this user from the system

SQL> DROP USER ADMIN CASCADE;
User 'ADMIN' dropped.

As shown in the example, the CASCADE option must be used to completely delete the objects created in the ADMIN user's schema. Otherwise, the TBR-7139 error will occur. After being dropped, the ADMIN user cannot connect to the database.

This section describes the commands used to start up and shut down Tibero and how to use them.

$ cd $TB_HOME/bin
$ chmod 700 tbsvr tblistener tbboot tbdown tbctl
$ ls -alF
total 56
drwxr-xr-x  4 tiberotibero 4096 Dec 28 18:12 ./
drwxr-xr-x 13 tiberotibero 4096 Dec 20 11:59 ../

        ...... Some results omitted for brevity......

-rwx------  1 ... tbboot*
-rwx------  1 ... tbctl*
-rwx------  1 ... tbsvr*
lrwxrwxrwx    1 ... tblistener*
lrwxrwxrwx    1 ... tbdown -> $TB_HOME/bin/tbsvr*

tbboot
tbboot -v
tbboot -h
tbboot -C
tbboot -c
tbboot [-t] [ normal | mount | nomount | resetlogs |
              NORMAL | MOUNT | NOMOUNT | RESETLOGS ]

$ tbboot NORMAL
listener port = 8629

Tibero 6 

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

Tibero instance started suspended at NORMAL mode.

$ tbboot NOMOUNT
listener port = 8629

Tibero 6 

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

Tibero instance started suspended at NOMOUNT mode.

$ tbboot MOUNT
listener port = 8629

Tibero 6 

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

Tibero instance started suspended at MOUNT mode.

$ tbboot RESETLOGS
listener port = 8629

Tibero 6 

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

Tibero instance started suspended at NORMAL mode.

tbdown
tbdown -h
tbdown [-t] [ normal | post_tx | immediate | abort | switchover | abnormal |
              NORMAL | POST_TX | IMMEDIATE | ABORT | SWITCHOVER | ABNORMAL ]
tbdown clean

$ tbdown

Tibero instance terminated (NORMAL mode).

$ tbsql admin/password123

tbSQL 6 

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

Connected to Tibero.

SQL> CREATE TABLE T1 (COL1 NUMBER);
Table 'T1' created.

SQL> INSERT INTO T1 VALUES(10);
1 row inserted.

SQL> SELECT * FROM T1;
      COL1
----------
        10

1 row selected.

        ...At this point, tbdown POST_TX is executed.
        ...the tbdown command waits for transactions to end.

SQL> COMMIT;
Commit succeeded.
        ...At this point, the server is actually terminated, 
           completing the execution of tbdown.

SQL> SELECT * FROM T1;
TBR-2131: Generic I/O error.
        ...The TBR-2313 error occurred at the prompt of the tbSQL utility.

$ tbdown immediate

Tibero instance terminated (IMMEDIATE mode).

$ tbdown abort

Tibero instance terminated (ABORT mode).

$ tbdown abnormal

Tibero instance terminated (ABNORMAL mode).

Binary TIP (hereafter BTIP) is a function that saves Tibero initialization parameter values to a binary file. Unlike a TIP file, parameters that are modified and saved during operation in a BTIP are applied at the next startup.

BTIP is created using the following DDL statements.

SQL> CREATE BTIP FROM TIP;
      ...Creates BTIP,$TB_HOME/config/$TB_SID.btip, 
      ...using the TIP file located in $TB_HOME/config. 


SQL> CREATE BTIP='PATH' FROM TIP; 
      ...To save the BTIP in a directory other than $TB_HOME/config,
      ...set the name of the BTIP file with an absolute path. 
      ...The file extension name must be .btip.

If $TB_SID.btip is located in $TB_HOME/config by default at startup, parameter information is read from the BTIP file. The user can also set a desired btip file path. If BTIP does not exist, parameter information is read from the $TB_SID.tip file.

The information of the TIP file in use is retrieved as follows.

SQL> SELECT TIP_FILE FROM V$INSTANCE;
TIP_FILE
--------------------------------------------------------------------------------
/home/tibero/work/6/config/t6.btip

...BTIP file under $TB_HOME/config is currently in use.

To make and apply dynamic changes to a parameter in BTIP, specify the scope for applying the changes through the following DDL statements.

SQL> ALTER SYSTEM SET PARAMETER1 = NEW_VALUE SCOPE BTIP ;
...By applying NEW_VALUE, the changed value of PARAMETER1, only to BTIP, 
...NEW_VALUE is applied to PARAMETER1 at the next startup. 
...The current PARAMETER 1 value is OLD_VALUE after executing DDL.

SQL> ALTER SYSTEM SET PARAMETER1 = NEW_VALUE SCOPE MEMORY ;
...NEW_VALUE, the changed value of PARAMETER1, is only applied during the operation.
...OLD_VALUE is applied to PARAMETER1 at the next startup. 

SQL> ALTER SYSTEM SET PARAMETER1 = NEW_VALUE SCOPE BOTH ;
...NEW_VALUE, the changed value of PARAMETER1, is applied to BTIP during the 
   operation. 
...NEW_VALUE is applied to PARAMETER1 at the next startup.
...The PARAMETER1 value applied after the DDL statement is also NEW_VALUE.

Creating a TIP file from a BTIP is only possible when the operating server uses BTIP.

SQL> CREATE TIP FROM BTIP;