1. aexport#

Introducing aexport#

Overview#

aexport supports automated data migration between Altibases. This utility stores logical structures and data in text format, and automatically creates a script to load the stored text data into a new database.

The objects and components that aexport can extract from a database to which it is connected are database users, user privileges, tables, tablespaces, table constraints, indexes, views, materialized views, stored procedures, sequences, and replication objects.

Because aexport creates SQL scripts corresponding to logical structures in the database and downloads all data in text form, it can migrate data between databases of different versions or platforms. This utility should be used when the database is running, but not actively providing service (when clients are not connected).

aexport Features#

aexport can extract the following database objects and structural elements:

Database users
User privileges
Roles
Tablespaces
Tables
Table constraints
Indexes
Views
Materialized views
Stored procedures
Replication objects

In order to execute, aexport generates the SQL scripts to create the database components listed above and the shell scripts to run them.

aexport Modes and Script Files#

aexport can be executed in different modes to extract different portions of the database. The desired mode can be specified on the command-line.

The aexport execution mode and the SQL script files generated for each mode are described in the section below.

Full DB Mode#

Full DB mode extracts the entire database and is only available for the SYS user.

The following SQL script files are generated for this mode:

SYS_CRT_DIR.sql: Creates all directory objects.
SYS_CRT_USER.sql: Creates all users and roles.
SYS_CRT_SYNONYM.sql: Create all synonym objects.
SYS_CRT_REP.sql: Create all replicaiton objects.
ALL_CRT_VIEW_PROC.sql: Creates all views and stored procedures.
ALL_CRT_TBS.sql: Creates all tablespaces.
ALL_CRT_TBL.sql: Creates all user tables.
ALL_CRT_INDEX.sql: Creates all user indexes.
ALL_CRT_FK.sql: Creates all user-defined foreign keys.
ALL_CRT_TRIG.sql: Creates all user-defined triggers.
ALL_CRT_SEQ.sql: Creates all user-defined sequences.
ALL_CRT_LINK.sql: Creates all user-defined database link objects.
ALL_EXE_STATS.sql: Creates statistics for all user-defined tables, columns, and indexes.
ALL_REFRESH_MVIEW.sql: Refreshes all user materialized views.
ALL_ALT_TBL.sql : Switches the data access mode for tables and partitions of all users.

Note:

A role can only be extracted in full DB mode, because it is a non-schema object.

User Mode#

This mode exports all objects owned by a specified user and is available only for the SYS user or the user whose objects are to be exported. Set the -u command-line option to the desired user for this mode.

The following SQL script files are generated for this mode:

{User name}_CRT_TBL.sql: Creates all tables of the specified user.
{User name}_CRT_INDEX.sql: Creates all indexes of the specified user.
{User name}_CRT_FK.sql: Creates all foreign keys of the specified user.
{User name}_CRT_TRIG.sql: Creates all triggers of the specified user.
{User name}_CRT_SEQ.sql: Creates all sequences of the specified user.
{User name}_CRT_LINK.sql: Creates all database link objects of the specified user
{User name}_EXE_STATS.sql: Creates statistics for all tables, columns and indexes of specified user
{User name}_REFRESH_MVIEW.sql: Refreshes all materialized views of the specified user.
{User name}_ALT_TBL.sql : Switches the data access mode for tables and partitions of the specified user.

Object Mode#

Object mode exports a specified set of objects (user.object) and is available only for the SYS user or the user whose objects are to be exported. Use the -object command-line option for this mode.

All specified objects must belong to the same user; however, the SYS user can export any user object.

The following SQL script files are generated for this mode

{User name}_{Object name}_CRT.sql: Creates the specified user object.
{User name}_{Object name}_STATS.sql: Creates the specified user statistics.

Shell Script Files#

In addition to the above SQL scripts, the following shell script files are also created when aexport is executed:

run_il_in.sh: Loads data.
run_il_out.sh: Downloads data.
run_is.sh: Creates schema.
run_is_con.sh: Creates constraints. This script includes SQL scripts for creating indexes, foreign keys, triggers, and replication objects. This script is created if the TWO_PHASE_SCRIPT property is set to ON.
run_is_fk.sh: Creates foreign keys and triggers. This script is not created if the TWO_PHASE_SCRIPT property is set to ON.
run_is_index.sh: Creates indexes. This script is not created if the TWO_PHASE_SCRIPT property is set to ON.
run_is_repl.sh: Creates replication objects. This script is not created if the TWO_PHASE_SCRIPT property is set to ON.
run_is_refresh_mview.sh: Refreshes materialized views. This script is not created if the TWO_PHASE_SCRIPT property is set to ON.
run_is_alt_tbl.sh : Switches the data access mode of tables and partitions. This script is not created if the TWO_PHASE_SCRIPT property is set to ON.

When one of the above shell script files is executed on the destination database, the logical structure of the source database is created on the destination database. Additionally, all data that exists on the source database is loaded into the destination database. These shell script files use iLoader to download and upload data; the iLoader process is automated within the shell script.

All files generated by aexport are text files, so the user can modify them as desired.

aexport Properties and Script Files#

This section discusses script files generated by aexport properties.

Please refer to aexport Properties for more information.

INVALID_SCRIPT = ON,INVALID.sql is generated. This script file contains SQL scripts for all invalid views and stored procedures. A shell script file for executing INVALID.sql is not generated.
TWO_PHASE_SCRIPT = OON, ALL_OBJECT.sql is generated for all objects and ALL_OBJECT_CONSTRAINTS.sql for all indexes, foreign keys, triggers, and replication objects. The run_is_con.sh shell script file for executing ALL_OBJECT_CONSTRAINTS.sql is also generated.

Prerequisite#

Install DBMS_METADATA Package#

The DBMS_METADATA package provides functionality to extract object creation DDL statements or GRANT statements from the database dictionary.

To use aexport, it is necessary to have the DBMS_METADATA package installed in Altibase, as aexport has a dependency on this package. If not, the following error occurs:

$ aexport -s localhost -u sys -p manager
-----------------------------------------------------------------
     Altibase Export Script Utility.
     Release Version 7.3.0.0.0
     Copyright 2000, ALTIBASE Corporation or its subsidiaries.
     All Rights Reserved.
-----------------------------------------------------------------
[ERR-91144 : DBMS_METADATA package does not exist.]

For more detailed information about DBMS_METADATA package, please refer to Stored Procedures Manual - DBMS_METADATA.

Setting aexport#

aexport requires the following information to connect to a server:

ALTIBASE_HOME
The path where the server or client is installed.
server_name
The name or IP address of the computer hosting the database from which data is to be downloaded.
port_no
The port number to be used to connect over TCP or IPC.
user_id
The database user identifier used by aexport to connect to the database.
Password
The password for the database user identifier.
NLS_USE
The character set in which to display data.

The path where the server or client is installed can only be set with the ALTIBASE_HOME environment variable. The rest can be set with command-line options. For more detailed information about command-line options, please refer to "How to Use aexport".

The ALTIBASE_HOME environment variable must be correctly set, and the aexport property settings file (aexport.properties) must exist and be properly configured for aexport to run successfully. For more detailed information about the aexport.properties file, please refer to "aexport Properties".

ALTIBASE_HOME is usually set automatically when the server is installed. For the client, the user must set it manually. If it is not set, it may not work properly, so it is recommended to check whether it is set correctly before executing.

port_no and NLS_USE can be set with environment variables or the altibase.properties file. If set in more than one way, the following methods take precedence in descending order.

Command-line options
Environment variables (ALTIBASE_PORT_NO, ALTIBASE_NLS_USE)
The altibase.properties file

On omission, the user is prompted to enter a value immediately after aexporthas been executed. aexport may not work normally if the value is invalid.

If the option is not set, the first time aexport is run, it prompts for an option and prompts the user for a value.If incorrect format or an invalid value is entered, aexport may not work properly.

One exception is the NLS_USE option. On omission, the user is not prompted to enter a value, but the database character set is used by default. If the user omits the NLS_USE option in an environment that does not use the US7ASCII character set, aexport will run abnormally with the risk of data loss. Therefore, the user should set the NLS_USE option to a value that is compatible with his or her operating environment.

The user is recommended to set the following environment variables for aexport to run normally:

ALTIBASE_HOME: The path where the server or client is installed.
ALTIBASE_PORT_NO: The port number used to connect to the server.
ALTIBASE_NLS_USE: The character set used to export and import data.
PATH: The path to the aexport executable file. It is normally $ALTIBASE_HOME/bin.

Environment Variables#

ALTIBASE_HOME#

Sets the directory in which the package was installed. This must be set to use aexport.

ALTIBASE_PORT_NO#

Sets the port number used to connect to the server. This can also be set with the -port command-line option, or set in advance in the altibase.properties file.

If different values are set for the ALTIBASE_PORT_NO environment variable and the altibase.properties file, the environment variable takes precedence. However, the value set with the -port command-line option overrides both. On omission, the user will be prompted to enter a value after aexport has started.

ALTIBASE_SSL_PORT_NO#

Sets the server port number that aexport is to connect to over SSL/TLS.

For the port number in SSL, the -PORT option, environment variables, ALTIBASE_SSL_PORT_NO, and the properties in the altibase.properties file take precedence over each other (in consecutive order). On omission, the user is prompted to enter the port number.

ALTIBASE_NLS_USE#

Sets the character set use when connecting to the server. This can also be set with the - nls_use command-line option, or be set in advance in the altibase.properties file.

If different values are set for the ALTIBASE_NLS_USE environment variable and the altibase.properties file, the environment variable takes precedence. However, the value set with the -nls_use command-line option overrides both.

Note: If the server character set and the value set in ALTIBASE_NLS_USE are different, it may not work properly. It is recommended to set the appropriate value.

How to Use aexport#

Syntax#

Parameters#

Parameter	Description
-h	Displays the Help menu
-s	Sets the host name or IP of the server from which to download data. On omission, the user is prompted for the host name. This can be a host name, an IPv4 address, or an IPv6 address. If it is an IPv6 address, it must be enclosed in square brackets ("[ ]"). The localhost (the same computer on which aexport is executed) can be set as the computer's host name, the localhost's IPv4 address (usually 127.0.0.1), or the localhost IPv6 address (usually [::1]). For more information about Altibase and IPv6 address notation, please refer to the Administrator's Manual.
-u	This sents the name of the Altibase user to access the server from whichd data is to be downloaded. On omission, the user is prompted for the user name. This option must be set to the SYS user to perform a full DB mode export. Use double quotation marks if there are lower case letters, special characters or spaces in the username. -u \"user name\"
-p	Sets the password for the user. On omission, the user is prompted for a password.
-port	Sets the server port number to access for data download. On omission, the ALTIBASE_PORT_NO environment variable and the altibase.properties setting are checked in turn to determine the port number. If neither is set, the user is prompted for a port number.
-object	This option specifies an object which will be extracted along with the owner name. Use double quotation marks if there are lower case letters, special characters or space in the object name. -object \"user name\".\"table name\"
-tserver	Sets the destination server (the server to which the exported data is to be uploaded). This information is written to the script files that are created when aexport is executed, and used when those scripts are subsequently executed. As with the -s option, this can be a host name, an IPv4 address or an IPv6 address.
-tport	Sets the destination server port number. This information is written to the script files that are created when aexport is executed, and used when those scripts are subsequently executed.
-nls_use	Sets the character set to export (download) data from the source database and import (upload) data to the destination database. At present, the supported character sets are US7ASCII, KO16KSC5601, MS949, BIG5, GB231280, MS936, UTF8, SHIFTJIS, MS932 and EUCJP.
-prefer_ipv6	This option determines whether to first attempt to resolve a host name to an IPv4 address or an IPv6 address. If a host name is specified for the -s option and this option is used, aexport will first attempt to resolve the host name to an IPv6 address. In contrast, if a host name is specified for the -s option and this option is omitted, aexport will first attempt to resolve the host name to an IPv4 address. That is, the default behavior is to attempt to resolve the host name to an IPv4 address. If aexport fails to connect using the preferred IP address type, it attempts to connect using the other IP address type. For example, when localhost is specified for the -s option and this option is used, aexport first tries to connect to the [::1] IPv6 address. If this attempt fails, aexport then attempts to connect to the 127.0.0.1 IPv4 address.
-ssl_ca CA_file_path	Specifies the location of the certification authority (CA) certificate in which the public key of the Altibase server to be connected to is incorporated.
-ssl_capath CA_dir_path	Specifies the directory under which the certification authority (CA) certificate in which the public key of the Altibase server to be connected is incorporated.
-ssl_cert certificate_file_path	Specifies the location of the client authentication file.
-ssl_key key_file_path	Specifies the location of the client private key file.
-ssl_verify	Verifies the certificate the client receives from the server.
-ssl_cipher cipher_list	Specifies a cipher list for SSL encryption. Please refer to the SSL_CIPHER_LIST property in the General Reference.

For more detailed information about SSL connection, please refer to Chapter 2. Connecting and Disconnecting in the iSQL User's Manual.

The Data Migration Process#

The process of using aexport to migrate data can be roughly divided into the following steps:

Generate SQL script files for creating the structure of the objects to be exported from the source database and shell script files for executing the SQL script files
Export (download) the data from the source database
Create the required database structures in the destination database
Import (upload) the data to the destination database
Refresh the materialized view in the destination database, and create indexes and foreign keys in the destination database, and then switch the data access mode

Exporting the Source Database Structure#

aexport is first used to generate SQL script files that contain information about the structure of the source database and shell script files for executing the SQL script files.

Execute aexport

$ aexport -s 127.0.0.1 -u sys -p manager

Enter the passwords of the Altibase users at the prompts.
This sets the password for each user that is created in the destination database.
When using aexport to back up the data on a remote server, indicate the address of the remote server and the port through which to connect to the remote server.
```
$ aexport -s 222.112.84.200 -port 20300 -u sys -p manager
```

Exporting Data from the Source Database#

Export (download) the data from the source database by executing the shell script that was created by aexport in the previous step.

Check the disk to which the data are to be downloaded to ensure that it has enough free space to hold the data.
Because data in text form can occupy more space than the data in internally used data files, it is recommended that the amount of available free space be twice the size of the original data files.
Execute the "run_il_out.sh" script.
```
$ sh run_il_out.sh
```

Creating the Destination Database Structure#

Copy all SQL scripts and shell scripts and all 'fmt', 'log', and 'dat' format files created by the "run_il_out.sh" shell script to the system on which the destination database is located. Skip this step if the destination database is on the same system as the source database.

Start up the destination database.
Execute the "run_is.sh" script.
```
$ sh run_is.sh
```
Use iSQL to access the database and check whether all of the required database objects were properly created. If the required database structure was not properly created, inspect the output that was displayed on the screen while run_is.sh was executing to determine the cause of the problem.

Importing Data into the Destination Database#

Execute the "run_il_in.sh" script.
```
$ sh run_il_in.sh
```
Check the directory containing the "run_il_in.sh" shell script file to see whether it contains any files that have the ".bad" filename extension and are greater than 0 bytes in size. If such a file exists, inspect the contents of the ".bad" file and the log files related to the table having the same name as the ".bad" file and take suitable steps to resolve the problem. For more information on how to resolve such problems, please refer to the iLoader User's Manual.

Refresh the materialized view in the destination database, and create indexes and foreign keys in the destination database, and then switch the data access mode#

When the TWO_PHASE_SCRIPT property is set to "Off":

Execute the "run_is_refresh_mview.sh" script.
```
$ sh run_is_refresh_mview.sh
```
Execute the "run_is_index.sh" script.
```
$ sh run_is_index.sh
```
Execute the "run_is_fk.sh" script.
```
$ sh run_is_fk.sh
```
Execute the "run_is_alt_tbl.sh" script.
```
$ sh run_is_alt_tbl.sh
```

When TWO_PHASE_SCRIPT = ON:

Execute the "run_is_con.sh" script.
```
$ sh run_is_con.sh
```

Notes#

If a normal user who is not the SYS user executes aexport, scripts are created only for the user's schema.
If a normal user who is not the SYS user executes aexport, scripts are created for replication objects.
If a normal user who is not the SYS user executes aexport, CREATE TABLE privileges are required. This is because aexport creates temporary tables to analyze object interdependencies.
Do not run two or more aexport processes at the same time. Because aexport uses a temporary table to store created SQL scripts, running two or more aexport processes at the same time will yield unpredictable results.
If the EXECUTE and TWO_PHASE_SCRIPT aexport properties are both set to ON and the OPERATION property is set to IN when uploading data, this uploading operation will not be affected by the value of the INDEX property, because no SQL script file dedicated to creating the index is generated.
Therefore, when it is desired to perform the uploading operation (EXECUTE = ON and OPERATION = IN) with ON for the INDEX property, the TWO_PHASE_SCRIPT property must be set to OFF.
When the "run_is.sh" script is executed, all existing users and objects will be deleted from the database. Therefore, care must be taken to avoid executing this script on the source database.
If the -tserver and -tport options are not specified, then the -s and -p parameters are used not only to identify the source server from which data are downloaded, but are also used in all created scripts to identify the destination server to which data will be uploaded. To specify a destination server and port that are different from the source server and port, use the -tserver and -tport options. In this case, the -s and -p options will only identify the source server from which data are downloaded, whereas the values specified for the -tserver and -tport options will be written into the created scripts.
```
$ aexport -s 127.0.0.1 -u sys -p manager -tserver 192.168.1.10 -tport 21300

$ cat run_il_in.sh

iloader -s 192.168.1.10 -port 21300 -u SYS -p MANAGER in -f SYS_T1.fmt -d
SYS_T1.dat -log SYS_T1.log -bad SYS_T1.bad
```
If CALLBACK functions are specified using PASSWORD_VERIFY_FUNCTION at user creation, the user's password must be set to correspond to validity functions before user importation. Validity functions must also be imported before importing the user to the database.
Double quotation marks should be used if there are lower case letters, special characters or spaces in the user name or the name of objects which will be extracted.

Limitations#

If a stored procedure needs to refer to when creating a stored procedure is not created in advance, the operation will fail. aexport cannot guarantee the order in which stored procedures are created because they cannot access information about dependencies between stored procedures. In this case, the stored procedure creation can fail, so the stored procedure must be created in the destination database.
aexport only has limited access to the meta information of the sequence. Because of this restriction, only the sequence characteristics specified by the INCINMENT BY of a sequence created in an account other than the SYS user are reflected, and the remaining attributes are set to the default values. If these constraints are a problem, the sequence must be created in the destination database.
In creating an object in the destination database, the base table must be created before creating the Material View. Because aexport does not guarantee the table creation order for creating materialized views, it may fail to create a materialized view. In this case, the user must manually create a materialized view
When aexport extracts the materialized view creation statement from the source database, it imports the statement that originally created the materialized view. That is, even if the DDL statement that changes the refresh method or refresh change time is executed for the materialized view in the source database, the change is not reflected in the statement extracted by aexport.

SSL Connection and Script Files#

If aexport is executed on SSL, the SSL option that was specified to execute aexport is used with the source database connection script (run_il_out.sh).
If you want to connect to the target database using SSL, you must configure the SSL-related properties in the property file. For detailed information, refer to the SSL_ENABLE property in the aexport properties section.

aexport Properties#

Setting the aexport Properties#

Some of the settings that govern the use of aexport are made in the aexport.properties file. The aexport.properties file must be located in the $ALTIBASE_HOME/conf directory. (This file is not to be confused with the altibase.properties file, which by default is located in the same directory.)

When Altibase is installed, the $ALTIBASE_HOME/conf directory does not actually contain a file called aexport.properties, but it does contain a sample properties file called aexport.properties.sample. It is thus necessary to copy the aexport.properties.sample, paste it into the same directory, and rename it as aexport.properties before executing aexport. If aexport cannot find the aexport.properties file in $ALTIBASE_HOME/conf, it will raise an error and terminate.

List of aexport Properties#

OPERATION#

OPERATION = [IN|OUT]

If this property is set to OUT, scripts for exporting all schemas and data will be created. When the data export script,which consists of iLoader commands, is executed, form files (.fmt) and data files (.dat) will be created.

If this property is set to IN, the schema creation script and the data loading script, which were created by previouslyexecuting aexport with this property set to OUT, will be executed, the schema will be created in the destination database,and the data will be loaded into the destination database. The schema creation script and the data loading script can beexecuted manually at a shell prompt without executing aexport.

EXECUTE#

EXECUTE = [ON|OFF]

This property determines whether to automatically execute the scripts that were created.

If it is set to ON, the scripts that are appropriate for the current operation (set using the OPERATION property) will beexecuted automatically. The file names of these scripts are set using the ILOADER_OUT, ILOADER_IN, ISQL, ISQL_CON,ISQL_INDEX, ISQL_FOREIGN_KEY, ISQL_ALT_TBL, and ISQL_REPL properties.

If it is set to OFF, the scripts will be created, but not executed.

INVALID_SCRIPT#

INVALID_SCRIPT = [ON|OFF]

This property determines whether to group all of the object creation scripts for invalid objects in a single script file.

If this property is set to OFF, a SQL script file will be generated for each of the invalid objects in the database; that is,they will be treated just like the valid database objects.

TWO_PHASE_SCRIPT#

TWO_PHASE_SCRIPT = [ON|OFF]

This property determines whether to group all of the object creation scripts in two script files.

If this property is set to ON, aexport will create only two SQL script files and two shell script files:

ALL_OBJECT.sql, ALL_OBJECT_CONSTRAINS.sql
run_is.sh, run_is_con.sh

If this property is set to OFF, aexport will generate different SQL script files for each of the objects in a database.

CRT_TBS_USER_MODE#

CRT_TBS_USER_MODE = [ON|OFF] (Default: OFF)

This property determines whether or not to extract a statement for creating tablespace in user mode.
If this property is set to ON, the SQL statement creating tablespace related to the user in the user mode is extracted. Theuser related tablespaces are default tablespace, default temporary tablespace, and the tablespace specified whether or not toavailable to be accessed.

INDEX#

INDEX = [ON|OFF]

This property determines whether or not to create the indexes when creating the rest of the schema in the destinationdatabase. If it is desired to create the indexes after the data have been located into the destination database, set thisproperty to ON. It is used when the TWO_PHASE_SCRIPT property is set to OFF.

USER_PASSWORD#

USER_PASSWORD = password

This property is used to set the password when the users exported from the source database are created in the destinationdatabase. (Because aexport does not know the passwords of users exported from the source database, the passwords must bemanually set.) If this property is not set, a prompt for setting each user's password will appear.

VIEW_FORCE#

VIEW_FORCE = [ON|OFF]

If this property is set to ON, views will be forcibly created, even if the underlying tables or other objects don't exist.

DROP#

DROP = [ON|OFF]

This property determines whether to include DROP statements in created scripts.

If this property is set to ON, and if the destination database already contains objects corresponding to those that are to becreated, the existing objects will be dropped. Because this option specifies that existing objects are to be dropped, itshould be used with caution.

Note: If aexport is executed in Object Mode, DROP statements are not generated, regardless of the setting of this property.

ILOADER_OUT#

ILOADER_OUT = run_il_out.sh

This property determines the name of the shell script file that is created to export (download) the data from the sourcedatabase. It is used when the OPERATION property is set to OUT.

ILOADER_IN#

ILOADER_IN = run_il_in.sh

This property determines the name of the shell script file that will be used to import (upload) the data into the destinationdatabase.

ISQL#

ISQL = run_is.sh

This property determines the name of the script file that will be used to create the database schema in the destinationdatabase.

ISQL_CON#

ISQL_CON = run_is_con.sh

This property determines the name of the shell script file that is used to execute the SQL script files for creating indexes,foreign keys, triggers and replication objects. It is used when the TWO_PHASE_SCRIPT property is set to ON.

ISQL_INDEX#

ISQL_INDEX = run_is_index.sh

This property determines the name of the shell script that will be used to create indexes in the destination database. If novalue is specified for this property in the aexport.properties file, this shell script file will not be generated.

ISQL_FOREIGN_KEY#

ISQL_ FOREIGN_KEY = run_is_fk.sh

This property determines the name of the shell script file that is used to execute the SQL script files for creating foreignkeys. If no value is specified for this property in the aexport.properties file, this shell script file will not be generated.

ISQL_REPL#

ISQL_REPL = run_is_repl.sh

This property determines the name of the shell script that will be used to create replication objects in the destinationdatabase. If no value is specified for this property in the aexport.properties file, this shell script file will not begenerated.

COLLECT_DBMS_STATS#

COLLECT_DBMS_STATS = [ON|OFF]

This property determines whether to display the statistics for tables, columns and indexes of specified user.

The default value is OFF and no statistical information is exported. When this property's value is ON, statistics informationis exported.

ISQL_REFERSH_MVIEW#

ISQL_REFERSH_MVIEW = run_is_refresh_mview.sh

This property determines the name of the shell script that will be used to execute the SQL script files for refreshing thematerialized view in the destination database. If no value is specified for this property in the aexport.properties file,this shell script file will not be generated.

ISQL_ALT_TBL#

ISQL_ALT_TBL = run_is_alt_tbl.sh

This property sets the name of the shell script file executing the SQL script which switches the data access mode for thetables and partitions of the target database. On omission, aexport does not generate this shell script file.

ILOADER_FIELD_TERM#

ILOADER_FIELD_TERM = field_term

This property is used to set the field delimiters that are used when the data in tables are saved as text. If this propertyis not set, the default delimiter between values is the comma (","), no block delimiters are used for numeric values, anddouble quotation marks (" " ") are used as block delimiters around strings.

Note: The pound (i.e. hash or number sign) character "#" cannot be specified as a delimiter, because it is used to denotecomments in the properties file (The remainder of the line after the "#" will be ignored).

ILOADER_ROW_TERM#

ILOADER_ ROW _TERM = row_term

This property sets the record delimiter to use when texting table data. If not set, the default is <LF>.

Note: The pound (i.e. hash or number sign) character "#" cannot be specified as the record delimiter, because it is used todenote comments in the properties file (The remainder of the line after the "#" will be ignored).

ILOADER_PARTITION#

ILOADER_ PARTITION = [ON|OFF]

This property determines whether the iLoader scripts are generated for each partition when there is a partitioned table inthe source database.

When this property is set to ON, scripts for extracting form files and data for each partition are generated in run_il_outsh. Similarly, scripts for loading data for each partition are generated in run_il_in.sh. When this property is set to OFF, scripts for handling partitioned tables are generated to use a single data file forextraction and loading, similar to non-partitioned tables. For more detailed information, please refer to the iLoader User's Manual.

ILOADER_ERRORS#

ILOADER_ERRORS = count (Default: 50)

This property specifies the number of allowable maximum errors when uploading data with iLoader. The default value of thisproperty is set to 50, and the upload is continuously executed regardless of the number of incurring errors if the defaultvalue is set to 0.

ILOADER_ARRAY#

ILOADER_ARRAY = count (Default: 1)

This property specifies the number of rows to be processed at once when downloading or uploading data with iLoader.

ILOADER_COMMIT#

ILOADER_COMMIT = count (Default: 1000)

This property specifies the unit(number) to commit when uploading the data with iLoader. The value of -commit option can bespecified as well.

ILOADER_PARALLEL#

ILOADER_PARALLEL = count (Default: 1)

This property specifies the number of threads which will be executed with parallel processing when uploading or downloadingwith iLoader.

ILOADER_ASYNC_PREFETCH#

ILOADER_ASYNC_PREFETCH = OFF|ON|AUTO (Default: OFF)

This property sets asynchronous prefect behavior when downloading data to iLoader. For more information, please refer to the'-async_prefetch' option in the iLoader User's Manual.

SSL_ENABLE#

SSL_ENABLE = [ON|OFF]

This property specifies whether to connect to the target database using the SSL protocol.

If this property is set to ON, SSL-related options are enabled for iSQL and iLoader commands in the shell scripts (run_is.shand run_il_in.sh) to be executed on the target database.
SSL-related options can be enabled with the SSL_CA, SSL_CAPATH, SSL_CERT, SSL_KEY, SSL_CIPHER, SSL_VERIFY properties. Forfurther information about these properties, please refer to Parameters.

Example#

Execution in Full DB Mode#

$ aexport -s 127.0.0.1 -u sys -p manager
-----------------------------------------------------------------
    Altibase Export Script Utility.
    Release Version 7.1.0.0.0
    Copyright 2000, ALTIBASE Corporation or its subsidiaries.
    All Rights Reserved.
-----------------------------------------------------------------
#### TBS #####
#### USER  #####
#### SYNONYM #####
#### DIRECTORY #####
#### TABLE #####
#### QUEUE #####
#### SEQUENCE #####
#### DATABASE LINK #####
#### VIEW #####
#### MATERIALIZED VIEW #####
#### STORED PROCEDURE #####
#### STORED PACKAGE #####
#### TRIGGER #####
#### LIBRARY #####
#### REPLICATION #####
#### JOB #####
-------------------------------------------------------
#### The following script files were generated. #####
1. run_il_out.sh            : [ iloader formout, data-out script ]
2. run_is.sh                : [ isql table-schema script ]
3. run_il_in.sh             : [ iloader data-in script ]
4. run_is_refresh_mview.sh  : [ isql materialized view refresh script ]
5. run_is_index.sh          : [ isql table-index script ]
6. run_is_fk.sh             : [ isql table-foreign key script ]
7. run_is_repl.sh           : [ isql replication script ]
8. run_is_job.sh            : [ isql job script ]
9. run_is_alt_tbl.sh        : [ isql table-alter script ]
-------------------------------------------------------

$ ls -l
ALL_ALT_TBL.sql 
ALL_CRT_DIR.sql
ALL_CRT_FK.sql
ALL_CRT_INDEX.sql
ALL_CRT_JOB.sql
ALL_CRT_LIB.sql
ALL_CRT_LINK.sql
ALL_CRT_REP.sql
ALL_CRT_SEQ.sql
ALL_CRT_SYN.sql
ALL_CRT_TBL.sql
ALL_CRT_TBS.sql
ALL_CRT_TRIG.sql
ALL_CRT_USER.sql
ALL_CRT_VIEW_PROC.sql
ALL_REFRESH_MVIEW.sql
run_il_in.sh
run_il_out.sh
run_is.sh
run_is_alt_tbl.sh
run_is_fk.sh
run_is_index.sh
run_is_job.sh
run_is_refresh_mview.sh
run_is_repl.sh

Execution in User Mode#

iSQL> CREATE USER user1 IDENTIFIED BY user1;
Create success.
$ aexport -s 127.0.0.1 -u user1 -p user1
-----------------------------------------------------------------
    Altibase Export Script Utility.
    Release Version 7.1.0.0.0
    Copyright 2000, ALTIBASE Corporation or its subsidiaries.
    All Rights Reserved.
-----------------------------------------------------------------
#### USER #####
#### SYNONYM #####
#### TABLE #####
#### QUEUE #####
#### SEQUENCE #####
#### DATABASE LINK #####
#### VIEW #####
#### MATERIALIZED VIEW #####
#### STORED PROCEDURE #####
#### STORED PACKAGE #####
#### TRIGGER #####
#### LIBRARY #####
-------------------------------------------------------
#### The following script files were generated. #####
1. run_il_out.sh            : [ iloader formout, data-out script ]
2. run_is.sh                : [ isql table-schema script ]
3. run_il_in.sh             : [ iloader data-in script ]
4. run_is_refresh_mview.sh  : [ isql materialized view refresh script ]
5. run_is_index.sh          : [ isql table-index script ]
6. run_is_fk.sh             : [ isql table-foreign key script ]
7. run_is_repl.sh           : [ isql replication script ]
8. run_is_job.sh            : [ isql job script ]
9. run_is_alt_tbl.sh        : [ isql table-alter script ]
-------------------------------------------------------

$ ls -l
USER1_ALT_TBL.sql
USER1_CRT_DIR.sql
USER1_CRT_FK.sql
USER1_CRT_INDEX.sql
USER1_CRT_LIB.sql
USER1_CRT_LINK.sql
USER1_CRT_SEQ.sql
USER1_CRT_SYN.sql
USER1_CRT_TBL.sql
USER1_CRT_TRIG.sql
USER1_CRT_USER.sql
USER1_CRT_VIEW_PROC.sql
USER1_REFRESH_MVIEW.sql
run_il_in.sh
run_il_out.sh
run_is.sh
run_is_alt_tbl.sh
run_is_fk.sh
run_is_index.sh
run_is_job.sh
run_is_refresh_mview.sh
run_is_repl.sh

Execution in Object Mode#

iSQL> CREATE USER user1 IDENTIFIED BY user1;
Create success.
iSQL> CONNECT user1/user1;
iSQL> CREATE TABLE t1(i1 INTEGER);
Create success.
iSQL> CREATE VIEW v1 AS SELECT i1 FROM t1;
Create success.
iSQL> CREATE MATERIALIZED VIEW m1 AS SELECT * FROM t1;
Create success.
iSQL> CREATE OR REPLACE PROCEDURE proc1(p1 IN INTEGER)
AS a INTEGER;
BEGIN
SELECT * INTO a FROM t1 WHERE i1 = 1;
END;
/
Create success.

$ aexport -s 127.0.0.1 -u user1 -p user1 -object user1.t1
-----------------------------------------------------------------
    Altibase Export Script Utility.
    Release Version 7.1.0.0.0
    Copyright 2000, ALTIBASE Corporation or its subsidiaries.
    All Rights Reserved.
-----------------------------------------------------------------
#### TABLE #####
$ ls
user1_t1_CRT.sql

$ aexport -s 127.0.0.1 -u user1 -p user1 -object user1.m1
-----------------------------------------------------------------
    Altibase Export Script Utility.
    Release Version 7.1.0.0.0
    Copyright 2000, ALTIBASE Corporation or its subsidiaries.
    All Rights Reserved.
-----------------------------------------------------------------
#### MATERIALIZED VIEW #####
$ ls
user1_m1_CRT.sql

$ aexport -s 127.0.0.1 -u user1 -p user1 -object user1.t1,user1.v1,user1.proc1
-----------------------------------------------------------------
    Altibase Export Script Utility.
    Release Version 7.1.0.0.0
    Copyright 2000, ALTIBASE Corporation or its subsidiaries.
    All Rights Reserved.
-----------------------------------------------------------------
#### TABLE #####
#### VIEW #####
#### STORED PROCEDURE #####
$ ls
user1_proc1_CRT.sql
user1_t1_CRT.sql
user1_v1_CRT.sql

SSL Property Setting#

SSL_ENABLE = ON  # OFF
SSL_CA     = ${ALTIBASE_HOME}/cert/ca-cert.pem
#SSL_CAPATH = ${ALTIBASE_HOME}/cert
SSL_CERT   = ${ALTIBASE_HOME}/cert/client-cert.pem
SSL_KEY    = ${ALTIBASE_HOME}/cert/client-key.pem
SSL_CIPHER = RC4-SHA:RC4-MD5
SSL_VERIFY = ON  # OFF