Table of Contents
The following chapter describes the tb export utility and its usage.
tb export is the export utility provided by Tibero. This utility helps to export all or some part of a schema object saved in a Tibero database, and then saves it into a unique type of file. Therefore, it is useful when processing a database backup and transmitting a database between different machines.
When you export one schema object using the tbExport utility, the related schema objects will also be exported automatically. For example, if you export one table, the constraints and indexes that are created with the table will also be exported. If necessary, it is possible to stop some of the related schema objects from being exported.
tbExport has three modes: full database mode, user mode, and table mode. Only a DBA is allowed to use the full database mode.
The files created through the tbExport utility are OS files. Therefore, unlikeTibero database files, these files can be processed as normal files. For example, they can be transmited using FTP or saved on a CD-ROM and then sent to a remote Tibero database.
Specify the log, which occurs while exporting, by using the LOG parameter.
The following table describes the Complete, Alert, and Error messages created as a result of executing the tbExport utility.
| Item | Description |
|---|---|
| Complete Message | This message is output when the export has been successfully processed. |
| Alert Message | This message is output when the export has finished, but something is wrong. It occurs when users try to export a table that does not exist. In this case, the tbExport utility outputs an Alert message, skips the corresponding table, and then continues with exporting the next object. |
| Error Message | This message is output when an error occurs during export and the export cannot continue. It occurs when the export cannot be processed anymore (when there is not enough system memory the necessary view for the export is not created). After this error message is output, the export session will be terminated. |
The tbExport utility has the following features.
-
Logical Backup
Extracts the data and internal schema of Tibero.
-
Data with Different Point of Time
When exporting many tables at once, data in the tables will not be exported all at the same time. The data will be exported in order.
-
Saves the Table Definition
Saves the table definition (DDL script of a table) regardless of the data existence.
-
Reorganizing the Table
After creating a table, it removes all the fragmentations and migrated rows caused by a lot of DML work.
The tbExport utility will be installed or removed when Tibero is installed or removed. Also, the tbExport utility is implemented in the Java language and it can be run on any platform in which JVM (Java Virtual Machine) is installed.
Check the following requirements are met before executing the tbExport utility.
-
If JRE 1.4.2 or higher is installed.
-
Should be installed on the same platform as Tibero database server or be connected through a network.
-
Necessary class libraries (the default location: $TB_HOME/lib/jar directory) for execution.
-
tbExport class: expimp.jar
-
Common library of the utilities: toolcom.jar
-
Common Logger library of the utilities: msllogger-14.jar
-
JDBC driver: internal-jdbc-14.jar
-
The Class libraries required for execution will be installed automatically when installing Tibero. Therefore, there are no additional tasks required for this.
Export mode has three modes: full database mode, user mode, and table mode. Each mode can be specified by using a parameter.
The following figure shows the relationship of schema objects, which are to be exported, in each mode.
Full Database Mode
Full database mode exports the entire Tibero database. Only a DBA is allowed to use this mode. It exports objects of all users except the SYS user.
To use the full database mode, specify Y for the FULL parameter.
FULL=Y
User Mode
User mode specifies the target users and exports all of their schema objects. In other words, user mode is used to export all the objects that belong to the specified users. A DBA can allow more than one user to use this mode.
To use the user mode, specify the userlist for the USER parameter. (USER=userlist format).
USER=SCOTT, USER1, …
Table Mode
Table mode specifies one or more tables and exports all of their schema objects, such as index.
To use table mode, specify the tablelist for the TABLE parameter (TABLE=tablelist format). Make sure to enter the user who owns the table like SCOTT.EMP as shown below.
TABLE=SCOTT.EMP, USER1.TABLE1, …
To export a partition table, specify the partition after the table name (:<partition name>). Only one partition can be exported.
TABLE=USER1.TABLE1:PART1, …
To execute the tbExport utility, enter tbexport command in the $TB_HOME/client/bin directory.
The following is an example of how to execute tbExportin full database mode.
[Example 3.1] Executing the tbExport utility
$ tbexport username=tibero password=tmax file=export.dat full=y $ tbexport cfgfile=export.cfg
If a user executes tbExport without specifying any values for the parameters the user will see a list of parameters and their usage, which can be specified in the command prompt, as shown below.
tbExport 5.1 87326 Copyright (c) 2014 TmaxData Corporation. All rights reserved. Patch files (none) Usage: tbexport [PARAMETER1=VALUE] [PARAMETER2=VALUE] ... Parameters: CFGFILE Config file name USERNAME Database user name PASSWORD User password IP IP address, default: localhost PORT PORT number, default: 8629 SID Database name FILE Export dump file name, default: default.dat NO_PACK_DIR Export unpacked dump files to specified directory. If this option is specified, FILE parameter will be ignored. OVERWRITE Overwrite datafile if same file name exists: Y/N, default: N LOG Export log file name FULL Full Mode: Y/N, default: N USER User Mode: user name list TABLE Table Mode: table name list. Append :<Partition name> to select a single partition (Optional) QUERY Where predicate: (Optional) to filter data to be exported (must be used with TABLE parameter) INDEX Export Index: Y/N, default: Y GRANT Export Grant: Y/N, default: Y CONSTRAINT Export Constraint: Y/N, default: Y ROWS Export Table Rows: Y/N, default: Y SCRIPT LOG THE DDL SCRIPT: Y/N, default: N THREAD_CNT Thread Count, default: 4
Parameter values do not have to be entered in order. Among the parameter values, CFGFILE can only be specified in the command prompt. Other parameter values can be specified in the configuration file.
The parameters used in the command prompt can be saved in the configuration file, in the following two ways. The second type is used when specifying two or more parameter values.
PARAMETER=value PARAMETER=value1, ...
The following is an example of how to specify a number of parameters.
FULL=Y FILE=EXPORT.DAT GRANT=Y INDEX=Y CONSTRAINT=Y
The following table describes the tbExport utility parameters, that can be specified in the command prompt.
| Item | Description |
|---|---|
| CFGFILE | Name of the configuration file. |
| USERNAME | The account of the user, who performs the Export. |
| PASSWORD | The password of the user, who performs the Export. |
| IP | The IP address of theTibero server, that is to be exported. (The default value: localhost) |
| PORT | The port number of the Tibero server, that is to be exported. (The default value: 8629) |
| SID | Enters the SID of theTibero server, that is to be exported. |
| FILE | The name of the file that is created during the export. (The default value: default.dat) It is created in the operating system as a binary file type. If the name is not specified the default value will be used. |
| NO_PACK_DIR | The directory which stores the unpacked dump file. If this option is specified, the value set in the FILE parameter is ignored. |
| OVERWRITE | Decides whether or not to overwrite a file, which is created by exporting, when a file with the same name already exists.
|
| LOG | Enters the name of a file, which will contain the export log. |
| FULL | Specifies whether or not to export in full database mode.
|
| USER | Specifies the owner of the object to be exported in user mode. The format is USER=userlist. |
| TABLE | Specifies the name of the table to be exported in table mode. The format is TABLE=tablelist. |
| QUERY | Specifies the filter condition for the data to be exported.
|
| INDEX | Decides whether or not to export the index information.
|
| GRANT | Decides whether or not to export the authorities.
|
| CONSTRAINT | Decides whether or not to export the constraints.
|
| ROWS | Decides whether or not to export the table data.
|
| SCRIPT | Decides whether or not to show the DDL script that creates a schema object during export.
|
| THREAD_CNT | Enters the number of threads, which are used to export the table data. (The default value: 4) |
The following is an example of exporting using the tbExport utility.
[Example 3.2] Executing Export by Using the tbExport Utility
tbExport 5.1 87326 Copyright (c) 2014 TmaxData Corporation. All rights reserved Patch files (none) the entire database: Wed May 04 16:16:08 KST 2014 Export character set: MS949 exporting tablespaces exporting roles exporting schema: "TIBERO" exporting tables [0] exporting table BONUS no rows exported. [1] exporting table DEPT 4 rows exported. [2] exporting table EMP 10 rows exported. [3] exporting table SALGRADE 5 rows exported. exporting object privileges exporting indexes exporting sequences exporting views exporting synonyms Packing the file... Export completed successfully: Wed May 04 16:16:20 KST 2014