tbExport
This chapter describes the tbExport utility and its usage.
Overview
tbExport is Tibero export utility that extracts a part or full of schema objects saved in Tibero database and saves them to a file in a specific format. It is useful for database backup and data transfer between heterogeneous databases.
When exporting a schema object by using tbExport, all relevant schema objects are also exported automatically. For example, if you export a table, constraints and indexes related to the table are also exported. If necessary, some of schema objects related to an exported object can be specified not to export.
tbExport has three modes: full database mode, user mode, and table mode. Only DBA can use these modes. If a user who wants to perform export has no DBA privilege, it is recommended to grant the SELECT ANY DICTIONARY privilege to the user.
tbExport result files are OS files. Therefore, the files can be processed like general files. For example, they can be transferred by using FTP, saved in CD-ROM, and stored in remote Tibero database.
To set log that occurs during export, use the LOG parameter.
The following describes each tbExport result message.
Item
Description
Complete
Message
Displayed when export successfully completed.
Warning Message
Displayed when export completed with one or more warnings. When tbExport recognizes that a table to export does not exist, this message is displayed, the table is skipped, and exporting the next object is tried.
Error Message
Displayed when export cannot continue due to an error. In cases such as that system memory is insufficient and views required for export are not created, this message is displayed. After this message is displayed, the tbExport session ends.
tbExport has the following features.
Logical backup Extracts Tibero internal schema and data.
Data at different points in time When exporting multiple tables, data in each table is exported sequentially. Therefore, exported data is not from the same point in time.
Saving table definitions Saves table definition (tables' DDL scripts) regardless of data existence.
Reorganizing tables Eliminates migrated rows and fragmentations caused by many DML tasks that are executed after the table creation.
Quick Start
tbExport is automatically installed and uninstalled along with Tibero. It is implemented in Java and can be run on any platform where Java Virtual Machine (JVM) is installed.
Preparations
To use tbExport, check the following requirements.
JRE 1.4.2 or later must be installed.
tbExport must be installed in a platform where Tibero database server is installed. Otherwise, it must be connected to the platform through a network.
The following class libraries are required. (Default location: $TB_HOME/client/lib/jar)
tbExport class : expimp.jar
Common utility library : toolcom.jar
Common utility logger library : msllogger-14.jar
JDBC driver : internal-jdbc-14.jar
The class libraries are automatically installed along with Tibero. No separate tasks are required.
Export Modes
There are three export modes: full database mode, user mode, and table mode. Each mode can be set with a parameter.
The following shows the relationship between schema objects in each mode.
[Figure 1] Export Modes
Full Database Mode
Exports entire Tibero database. Objects of all users except for the SYS user are exported. To use this mode, set the FULL parameter to Y as follows:
User Mode
Exports all schema objects of specified users except for the SYS user. DBA can specify multiple users. To use this mode, set the USER parameter to the list of users as follows:
Table Mode
Exports schema objects related to one or more specified tables. To use this mode, set the TABLE parameter to the list of tables along with their owner as follows:
Executing tbExport
To execute tbExport, run the tbexport command in the $TB_HOME/client/bin directory.
The following executes tbExport in full database mode.
Executing tbExport
tbExport Parameters
This section describes tbExport parameters that can be set at the command prompt.
Executing tbExport without any parameters displays the list and usage of the parameters as follows:
The following describes each parameter.
Item
Description
CFGFILE
Configuration file name
COMPRESS
Option to compress data during export.
Y: Compresses data.
N: Does not compress data. (Default value)
If set to Y, a single thread is used.
CONSISTENT
Option to export data in Consistent mode, which exports data based on the time when the exporting starts.
Y: Uses Consistent mode.
N: Does not use Consistent mode. (Default value)
This is not available for targets that are not supported in flashback queries.
CONSTRAINT
Option to export constraints.
Y: Exports constraints. (Default value)
N: Does not export constraints.
ENCRYPTION
Option to encrypt data in the dump file during export. The encryption algorithm is AES-128.
Select one of the following options:
ALL: Encrypts both data and metadata.
DATA_ONLY: Encrypts only data.
ENCRYPTED_COLUMNS_ONLY: Encrypts only encrypted columns.
METADATA_ONLY: Encrypts only metadata.
NONE: Encrypts no data. (Default value)
ENCRYPTION_PASSWORD
Password for the dump file with data encrypted by using the ENCRYPTION parameter.
EXCLUDE
Option to exclude specific users or tables from export.
Examples)
Excluding schemas and schema.table exclude=schema:"='TIBERO'/table:LIKE 'T%'" exclude=schema:"IN'USER1'"
Excluding multiple schemas exclude=schema:"='TIBERO'" exclude=schema:"='USER1'"
Excluding multiple tables exclude=table:"LIKE'E%'" exclude=table:"IN('T2','T3')"
FILE
Name of a binary dump file created by OS and used during export. (Default value: default.dat)
FULL
Option to export data in full database mode.
Y: Uses full database mode.
N: Uses either user or table mode. Either one must be specified. (Default value)
GEOM_ASBYTES
Option to export geometry column data as bytes.
Y: Exports the data as bytes.
N: Exports the data as WKB. (Default value)
In a server of Tibero 6 or later version, geometry column data is stored as WKB. Therefore, if this option is set to Y, export and import performance improves because LOB data can be processed without using a function such as st_asbinary.
In a server of Tibero 5 SP1 or previous version, do not set this option to Y to export data as WKB. This option must be set to N, and use the st_asbinary function to export data as WKB. A performance issue may occur when temp LOB data needs to be created and processed for export, and an issue that huge geometry data cannot be processed may occur when DPL is used for import.
GRANT
Option to export permissions.
Y: Exports permissions. (Default value)
N: Does not export permissions.
INDEX
Option to export indexes.
Y: Exports indexes. (Default value)
N: Does not export indexes.
INLINE_CONSTRAINT
Option to display scripts with inline constraints.
Y: Displays scripts with inline constraints. Only non-null scripts are supported.
N: Displays scripts with out-of-line constraints. (Default value)
IP
IP address of the Tibero server to export. (Default value: localhost)
LOG
Name of the export log file.
LOGDIR
Directory that includes the export log file.
NO_PACK_DIR
Directory that includes a decompressed dump file. If this option is specified, the value set in the FILE parameter is ignored.
Since files in the specified directory can be deleted, it is recommended to specify a new or an empty directory.
OVERWRITE
Option to overwrite an existing file with the same name.
Y: Overwrites the file.
N: Does not overwrite the file. (Default value)
Note that you may lose an existing file.
PACK_TYPE
Option to select a packing algorithm for files to export.
TAR: Compresses files in TAR format. (Default value)
ZIP: Compresses files in ZIP format.
PASSWORD
Password for the user who performs export.
PACK_TYPE (hidden)
Packing algorithm.
TAR: TAR format. (Default value)
ZIP: ZIP format.
PARALLEL_DEGREE
(hidden)
Parallel hint to use in queries used to export table data. (Default value: 0 (NOT PARALLEL))
PORT
Port number of the Tibero server to export. (Default value: 8629)
QUERY
Filter conditions for data to export.
The conditions are used regardless of the mode, but can be applied to undesired tables.
Enclose a WHERE clause with "\ and enclose a string in the clause with "\".
If an SQL statement has a syntax issue due to the condition, the statement is retried to be executed without the condition.
REMAP_TABLESPACE
Changes the tablespace name.
Examples)
Changing the tablespace name from USR1 to USR3 REMAP_TABLESPACE=usr1:usr3
Changing multiple tablespace names REMAP_TABLESPACE=usr1:usr3,usr2:usr4
Changing to a case-sensitive tablespace name REMAP_TABLESPACE="Usr1":usr3
REMAP_TABLE
Changes the table name.
Examples)
Changing the table name from TEST1 to TEST2 REMAP_TABLE=test1:test2
Changing multiple table names REMAP_TABLE=test1:test2,test3:test4
Changing a partition table name REMAP_TABLE=test1.partition_test1:test2.partition_test2
ROWS
Option to export table data.
Y: Exports table data. (Default value)
N: Does not export table data.
SAVE_CREDENTIAL
Specified when using encrypted username and password.
Use this item to create EXPIMP_WALLET encryption file.
Usage SAVE_CREDENTIAL=[EXPIMP_WALLET_FILE_NAME]
Examples)
SAVE_CREDENTIAL=/tmp/.expimp
USERNAME=username
PASSWORD=password
The following sets environment variables of the EXPIMP_WALLET file.
export EXPIMP_WALLET=/tmp/.expimp
The following are priorities for the previous setting. If all of them are not specified, an error occurs.
1. username and password parameters entered at the command line.
2. USERNAME and PASSWORD files in cfgfile.
3. USERNAME and PASSWORD in the EXPIMP_WALLET file.
SCRIPT
Option to show the DDL script that creates schema objects during export.
Y: Shows the DDL script.
N: Does not show the DDL script. (Default value)
SERVER_VER (hidden)
Tibero version to export. Create a script appropriate for the version. Export version constants are:
8: Tibero 6, Tibero 7 (Default value)
7: Tibero 5 SP1
6: Tibero 5
SID
SID of the Tibero server to export.
STATISTICS
Option to export statistics about the file to export. Recalculating the statistics such as SAFE and RECALCULATE is not supported.
Y: Exports the statistics about the target file.
N: Does not export the statistics about the target file. (Default value)
TABLE
● Usage TABLE=tablelist
TEMP_DIR
Directory that includes temporary dump files generated during export.
THREAD_CNT
Number of threads used to export table data. (Default value: 4)
USER
USER=userlist
USERNAME
User account that performs export.
NOVALIDATE
Option to check whether the existing rows satisfy constraints to export.
Y: Does not check whether the existing rows satisfy the constraints.
N: Checks whether the existing rows satisfy the constraints. (Default value)
This item is effective only when it is set for export. This setting also applies to import regardless of the setting for import.
INDEX_PARALLEL_DEGREE
Parallel degree for indexes to export. (Default value: 0 (NOPARALLEL))
The order of entered parameters does not matter. CFGFILE can be set only at the command prompt, but the other parameters can be set in a configuration file as well as at the command prompt. Note that parameter names set in the file must be in uppercase.
The following shows how to set parameters in a configuration file. Some parameters can have multiple values as shown in the second line.
The following is an example of setting parameters.
Usage Example
The following is an example of exporting data by using tbExport.
tbExport Usage Example
Last updated
