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:

FULL=Y

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:

USER=SCOTT, USER1, …

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:

TABLE=SCOTT.EMP, USER1.TABLE1, …

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 username=tibero password=tmax sid=tibero file=export.dat full=y
$ tbexport cfgfile=export.cfg

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:

tbExport 7.0 102665 TmaxData Corporation Copyright (c) 2008-. All rights reserved.
Usage: tbexport [options] [parameter=value parameter=value ...]

Options:        
  -h|--help            Display the more detailed information. 
  -v|--version         Display the version information.
  -p|--patch           Display the binary's patch information.

Parameters:
  CFGFILE	             Config file name
  COMPRESS	     Compress Mode: Y/N, default: N 
  CONSTRAINT	     Export Constraint: Y/N, default: Y 
  CONSISTENT	     Consistent Mode: Y/N, default: N 
  ENCRYPTION	     Specifies a Encryption Mode
  ENCRYPTION_PASSWORD  Specifies a password for encrypting data
                       in the export Dump File 
  EXCLUDE	             Limit the export to specific objects
  FILE	             Export dump file name, default: default.dat 
  FULL	             Full Mode: Y/N, default: N
  GEOM_ASBYTES         Export the geometry columns as bytes, default: N 
  GRANT	             Export Grant: Y/N, default: Y
  INDEX	             Export Index: Y/N, default: Y
  INLINE_CONSTRAINT Use the Inline Constraint: Y/N, default: N 
                     (this option is only supported for the not null)
  IP	             IP address, default: localhost
  LOG	             Export script log file name
  LOGDIR	     Export log directory
  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 
  PACK_TYPE Packing algorithm: TAR/ZIP, default: TAR
  PASSWORD           User password
  PORT	             PORT number, default: 8629
  QUERY	             Where predicate: (Optional) to filter data to be exported 
                     (must be used with TABLE parameter.)
  REMAP_TABLESPACE Remaps the objects from the source tablespace to the target 
                     tablespace.
  REMAP_TABLE Remaps the objects from the source table to the target table. 
  ROWS	             Export Table Rows: Y/N, default: Y
  SAVE_CREDENTIAL    Save your username and password to specified file 
  SCRIPT	     LOG THE DDL SCRIPT: Y/N, default: N
  SID	             Database name
  STATISTICS	     Export Statistics: Y/N, default: N 
  TABLE	             Table Mode: table name list
                     Append :<Partition Name> to select a single partition (Optional)
  TARGETDB	     Target Server, default: TIBERO
  TEMP_DIR	     Directory for the temporary raw dump files. 
  THREAD_CNT	     Thread Count, default: 4
  USER	             User Mode: user name list 
  USERNAME	     Database user name 
  NOVALIDATE	     NOVALIDATE: Y/N , default: N
  INDEX_PARALLEL_DEGREE	Option of index parallel degree, default: 0 (NOPARALLEL)

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

Table names to export in table mode. For more information, refer to "Table Mode".

● 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

Users of objects to export in user mode. For more information, refer to "User Mode".

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.

PARAMETER=value 
PARAMETER=value1, ...

The following is an example of setting parameters.

FULL=Y 
FILE=EXPORT.DAT 
GRANT=Y
INDEX=Y 
CONSTRAINT=Y

Usage Example

The following is an example of exporting data by using tbExport.

tbExport Usage Example

tbExport 7.0 97668 TmaxData Corporation Copyright (c) 2008-. All rights reserved. 
the entire database: Fri Feb 06 10:45:16 KST 2015
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: Fri Feb 06 10:46:17 KST 2015

PrevioustbSQL NexttbImport

Last updated 7 months ago