Using aexport and iloader#
This document how to download database objects and data in a text file that can be checked by the user using the aexport and iloader utilities, and how to upload it as a downloadable file.
This document introduces the most basic and simplest form. Since aexport and iloader can be applied in various forms by using various options, please refer to the following manual for necessary information.
- iLoader User's Manual
- Utilities Manual
- Manual download page: http://support.altibase.com/en/manual/
- Github: https://github.com/ALTIBASE/Documents/tree/master/Manuals/
Create database object and upload data#
Database object and data upload procedure#
In order to restore all database objects and data, execute the following .sh files created when aexport is executed in order.
From 2. to 8., execute it in order.
1. run_il_out.sh : [faq: iloader formout, data-out script ] # This is a script for backing up data, so it is excluded from this step.
2. run_is.sh : [faq: isql table-schema script ]
3. run_il_in.sh : [faq: iloader data-in script ]
4. run_is_refresh_mview.sh : [faq: isql materialized view refresh script ]
5. run_is_index.sh : [faq: isql table-index script ]
6. run_is_fk.sh : [faq: isql table-foreign key script ]
7. run_is_repl.sh : [faq: isql replication script ]
8. run_is_job.sh : [faq: isql job script ]
Each file contains isql commands that execute .sql files containing object creation statements.
$ cat run_is.sh
isql -s localhost -u SYS -p MANAGER -f ALL_CRT_TBS.sql
isql -s localhost -u SYS -p MANAGER -f ALL_CRT_USER.sql
isql -s localhost -u SYS -p MANAGER -f ALL_CRT_SYN.sql
isql -s localhost -u SYS -p MANAGER -f ALL_CRT_DIR.sql
isql -s localhost -u SYS -p MANAGER -f ALL_CRT_TBL.sql
isql -s localhost -u SYS -p MANAGER -f ALL_CRT_SEQ.sql
isql -s localhost -u SYS -p MANAGER -f ALL_CRT_LIB.sql
isql -s localhost -u SYS -p MANAGER -f ALL_CRT_VIEW_PROC.sql
isql -s localhost -u SYS -p MANAGER -f ALL_CRT_LINK.sql
Preparation before uploading data#
Before restoring data, be sure to check the following environment variables before proceeding.
ALTIBASE_NLS_USE is required to prevent the breakdown of Korean data, and ILO_DATEFORM should be set to prevent duplication when a date type column has a unique value.
- ALTIBASE_NLS_USE
- ILO_DATEFORM
In the session of executing iloader, it is applied by setting it with the export command as shown below or adding it to the user environment configuration file (.bash_profile or .profile) and logging out and logging in.
How to set environment variables
$ export ALTIBASE_NLS_USE=Database server character set
$ export ILO_DATEFORM='YYYY/MM/DD HH:MI:SS.SSSSSS'
How to set environment variables
$ echo $ALTIBASE_NLS_USE
$ echo $ILO_DATEFORM
The Altibase server character set can be checked with the following statement: NLS_CHARACTERSET is the ALTIBASE server's character set and NLS_USE is the client's character set. Hangul data is not broken only when these two are set identically.
How to check the ALTIBASE server character set-Available from Altibase version 5
iSQL> set linesize 1024;
iSQL> set colsize 20;
iSQL> select NLS_USE, NLS_CHARACTERSET from v$nls_parameters;
NLS_USE NLS_CHARACTERSET
-----------------------------------------------
MS949 MS949
1 row selected.
Creating database object#
When running the .sh file, the user should leave a log file to check for errors.
Ex) Logging when running .sh
$ sh run_is.sh \| tee run_is.log
or
$ nohup sh run_is.sh &
$ mv nohup.out run_is.log
Uploading data#
Data upload is performed using run_il_in.sh. In order to upload only a table owned by a specific user or only a specific table, the user can extract only what the user wants from run_il_in.sh as follows.
Ex) In case of uploading only table owned by ALTITEST user
$ grep "\-f ALTITEST_" run_il_in.sh > altitest_il_in.sh # Extract only the iloader command corresponding to the altitest user from run_il_out.sh and saves it in another file.
$ sh altitest_il_in.sh | tee upload.out # If done in the foreground, closing the terminal window will terminate the execution.
Or,
$ nohup sh altitest_il_in.sh & # If you do it in the background, closing the terminal window does not terminate the execution.
$ mv nohup.out upload.out # You can connect again and check the progress with the download.out file.
Ex) In case of uploading the SYS user's ORDERS table
$ grep 'SYS_ORDERS.fmt' run_il_in.sh
iloader -s localhost -u SYS -p MANAGER in -f SYS_ORDERS.fmt -d SYS_ORDERS.dat -log SYS_ORDERS.log -bad SYS_ORDERS.bad
If there are many tables or a lot of data, monitor upload.out to see if it is terminated.
Data upload performance log monitoring
$ tail -f upload.out
Checking the data upload result#
Checking the .sh running log#
Whenever .sh is running, all logs are left to check whether it is normal or not through the log after running .sh.
Ex) Checking errors
$ grep -i err- run_is.log
Checking log files for each table#
Execute the following commands to check if there is an error when uploading data.
$ grep -i err- upload.out # Check whether an error has occurred in the result of running the run_il_int.sh script.
[ERR-311F4 : Invalid column name # If something starts with ERR- like this, it means that an error has occurred.
$ ls -l *.fmt|wc -l # Check the number of tables
$ ls -l *.log|wc -l # Check the number of log files created by running run_il_in.sh
$ cat *.log | grep 'Error Row Count' | awk -F: '{print $2}' | wc -l # The number of these results should match the number of the two results above. If it is different, it means that the error has occurred as many times as the difference.
$ cat *.log | grep 'Error Row Count' | awk -F: '{print $2}' | sort -u # If this result is 0, nothing has failed when performing the upload.
0
Database object backup using aexport#
Preparation before executing aexport#
Change the values of the following settings in the aexport.properties file.
- ILOADER_FILED_TERM
- ILOADER_ROW_TERM
ILOADER_FIELD_TERM stands for field separator, and ILOADER_ROW_TERM stands for record separator.
If the aexport.properties file has never been changed, these settings are commented out and the defaults are set as follows.
Default value of separator
$ grep TERM aexport.properties
#ILOADER_FIELD_TERM = ^
#ILOADER_ROW_TERM = %n
If it is set as the default as above, it's a bit complicated, but change it as follows.
Change separator property setting value
$ grep TERM aexport.properties
ILOADER_FIELD_TERM = ^Cc__Cc^
ILOADER_ROW_TERM = ^Rr__Rr^%n
The location of the aexport.properties file is $ALTIBASE_HOME/conf. If this file does not exist, copy and use the aexport.properties.sample file.
$ cd $ALTIBASE_HOME/conf
$ ls -l aexport.properties*
-rw-r--r-- 1 user1 user1 1636 Mar 9 2022 aexport.properties.sample
$ cp -p aexport.properties.sample aexport.properties
When aexport is executed, the following scripts are created.
- Database object creation script
- Data download/upload script using iloader
The data download/upload script using iloader contains iloader commands. iloader can use several options, and the value used for the option depends on the above setting.
The above setting means field separator and row separator, respectively, and the default values are simply set. If this setting value is included in the character data type column, data may not be uploaded normally when uploading data.
Therefore, it is recommended to set this setting value rather complicated.
Executing aexport#
Execute aexport after changing aexport.properties.
$ aexport
-----------------------------------------------------------------
Altibase Export Script Utility.
Release Version 6.3.1.2.7
Copyright 2000, ALTIBASE Corporation or its subsidiaries.
All Rights Reserved.
-----------------------------------------------------------------
Write Server Name (default:localhost) : # Press enter when running locally, and enter IP when connecting to a remotely installed Altibase server.
Write UserID : # Enter sys to back up all objects in the database, and enter corresponding USER to back up objects of a specific user.
Write Password : # Enter USER's password
###### TBS #####
###### USER #####
** input user ALTITEST's password(default - same with USER_NAME): # If sys is entered for UserID, passwords of all users created in the database must be entered.
** input user USER1's password(default - same with USER_NAME): USER1 # Even if the user enters the password randomly, a backup script is created, but if the password does not match during the actual iloader backup, iloader will fail. Also, be aware that you are creating the create user ~identified by ~ syntax with the password entered here.
** input user USER2's password(default - same with USER_NAME): USER2
###### SYNONYM #####
###### DIRECTORY #####
###### TABLE #####
** "ALTITEST"."ORDERS"
** "SYS"."CUSTOMERS"
** "SYS"."DATE_T"
** "SYS"."DEMO_EX1"
** "SYS"."DEPARTMENTS"
** "SYS"."DISK_T"
** "SYS"."DISK_T2"
** "SYS"."EMPLOYEES"
** "SYS"."GOODS"
** "SYS"."MEM_T"
** "SYS"."ORDERS"
** "SYS"."PLAN_TEST"
** "SYS"."T"
** "SYS"."TEST_EMP_TBL"
** "SYS"."T_BINARY"
** "SYS"."T_BLOB"
** "SYS"."T_BYTES"
** "SYS"."T_CLOB"
** "SYS"."T_NIBBLE"
** "SYS"."VOL_T"
** "USER1"."T"
###### 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 ]
-------------------------------------------------------
When aexport is executed, .sh (.bat for Windows system) files and .sql files are created as follows.
The .sql files contain the syntax for creating database objects and are scripts that execute .sh files to .sql files at once.
(Note: The number of .sh files may vary depending on the supported objects for each Altibase version.
-------------------------------------------------------
##### 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 ]
-------------------------------------------------------
Data download using iloader#
Preparation for iloader#
Before executing the iloader, be sure to check the following environment variables before proceeding.
ALTIBASE_NLS_USE is required to prevent the breakdown of Korean data, and ILO_DATEFORM should be set to prevent duplication when a date type column has a unique value.
- ALTIBASE_NLS_USE
- ILO_DATEFORM
In the session of executing the iloader, it is applied by setting it with the export command as shown below or adding it to the user environment configuration file (.bash_profile or .profile) and logging out and logging in.
How to set environment variables
$ export ALTIBASE_NLS_USE= Database server character set
$ export ILO_DATEFORM='YYYY/MM/DD HH:MI:SS.SSSSSS'
How to set environment variables
$ echo $ALTIBASE_NLS_USE
$ echo $ILO_DATEFORM
The Altibase server character set can be checked with the following sentence: NLS_CHARACTERSET is the ALTIBASE server's character set and NLS_USE is the client's character set. Hangul data is not broken only when these two are set identically.
Error rendering macro 'code': Invalid value specified for parameter 'firstline'
iSQL> set linesize 1024;
iSQL> set colsize 20;
iSQL> select NLS_USE, NLS_CHARACTERSET from v$nls_parameters;
NLS_USE NLS_CHARACTERSET
-----------------------------------------------
MS949 MS949
1 row selected.
Performing backup#
Use run_il_out.sh to backup table data.
$ ls --l run_il_out.sh
rw-rw-rw 1 eheejung eheejung 633 Nov 1 11:00 run_il_out.sh
Download data for all tables in the database#
run_il_out.sh must be a file created after entering UserID as sys when executing aexport.
Run run_il_out.sh.
Example
$ sh run_il_out.sh | tee download.out # If done in the foreground, closing the terminal window will terminate the execution.
or
$ nohup sh run_il_out.sh & # If you do it in the background, closing the terminal window does not terminate the execution.
$ mv nohup.out download.out # You can connect again and check the progress with the download.out file.
Downloading data of a table belonging to a specific user#
In order to back up only tables owned by a specific user, use run_il_out.sh that aexport-ed to that user.
$ aexport
-----------------------------------------------------------------
Altibase Export Script Utility.
Release Version 6.3.1.2.7
Copyright 2000, ALTIBASE Corporation or its subsidiaries.
All Rights Reserved.
-----------------------------------------------------------------
Write Server Name (default:localhost) :
Write UserID : altitest
Write Password :
...Omitted...
$ cat run_il_out.sh
iloader -s localhost -u ALTITEST -p ALTITEST formout -f ALTITEST_ORDERS.fmt -T ORDERS
iloader -s localhost -u ALTITEST -p ALTITEST out -f ALTITEST_ORDERS.fmt -d ALTITEST_ORDERS.dat -log ALTITEST_ORDERS.log
If aexport is executed by sys, it can extract only the user from run_il_out.sh and execute it.
Ex) When the user name is altitest
$ grep "\-f ALTITEST_" run_il_out.sh > altitest_il_out.sh # Extract only the iloader command corresponding to the altitest user from run_il_out.sh and saves it in another file.
$ sh altitest_il_out.sh | tee download.out # If done in the foreground, closing the terminal window will terminate the execution.
or
$ nohup sh altitest_il_out.sh & # If you do it in the background, closing the terminal window does not terminate the execution.
$ mv nohup.out download.out # It can connect again and check the progress with the download.out file.
Downloading data for a specific table#
In order to download only specific tables, extract only the tables you want from run_il_out.sh and back them up.
Ex) Extracting the SYS user's ORDERS tables
$ grep 'SYS_ORDERS.fmt' run_il_out.sh
iloader -s localhost -u SYS -p MANAGER formout -f SYS_ORDERS.fmt -T ORDERS
iloader -s localhost -u SYS -p MANAGER out -f SYS_ORDERS.fmt -d SYS_ORDERS.dat -log SYS_ORDERS.log
Execute iloader formout and iloader out commands in sequence.
Ex) Backup of the SYS user's ORDERS table
[heejung.lee@63127 /data/heejung.lee/work/631_aexport]$iloader -s localhost -u SYS -p MANAGER formout -f SYS_ORDERS.fmt -T ORDERS # Create iloader form file
-----------------------------------------------------------------
Altibase Data Load/Download utility.
Release Version 6.3.1.2.7
Copyright 2000, ALTIBASE Corporation or its subsidiaries.
All Rights Reserved.
-----------------------------------------------------------------
ISQL_CONNECTION : TCP
[heejung.lee@63127 /data/heejung.lee/work/631_aexport]$
[heejung.lee@63127 /data/heejung.lee/work/631_aexport]$ls -l SYS_ORDERS.fmt # Check whether iloader form file is created
-rw-rw-rw- 1 heejung.lee heejung.lee 210 2014-11-19 15:28 SYS_ORDERS.fmt
[heejung.lee@63127 /data/heejung.lee/work/631_aexport]$
[heejung.lee@63127 /data/heejung.lee/work/631_aexport]$iloader -s localhost -u SYS -p MANAGER out -f SYS_ORDERS.fmt -d SYS_ORDERS.dat -log SYS_ORDERS.log # Perform data download
-----------------------------------------------------------------
Altibase Data Load/Download utility.
Release Version 6.3.1.2.7
Copyright 2000, ALTIBASE Corporation or its subsidiaries.
All Rights Reserved.
-----------------------------------------------------------------
ISQL_CONNECTION : TCP
DATE FORMAT : YYYY/MM/DD HH:MI:SS:SSSSSS
DATA_NLS_USE: MS949
Total 30 record download(ORDERS)
DOWNLOAD : 2.3150 msec
If there are many tables or a lot of data, monitor download.out to see if it is shutting down.
Data download performance log monitoring
$ tail -f download.d.out
Check the data download result#
Check the run_il_out.sh execution log#
The run_il_out.sh run log is the file specified in the tee command.
$ sh run_il_out.sh | tee download.out
Check whether an error has occurred in the specified file as follows.
$ grep –i err- download.out
[ERR-311F4 : Invalid column name # If something starts with ERR- like this, it means that an error has occurred.
$ Actions are required according to the situation, so if it is difficult to take action directly after checking the error, contact us.
Check log by table#
When run_il_out.sh is executed, log files in the form of DBUSER_TABLENAME.log are created. (Eg, ALTITEST_ORDERS.log in the altitest user's orders table)
In these files, check for errors with the command below.
$ cat run_il_out.sh | grep fmt | wc -l # Check the number of tables in the database
106
$ ls -l .fmt | wc -l # Check the number of tables to be backed up by the number of .fmt files.
106
$ cat *.log | grep 'Error Row Count' | awk -F: '{print $2}' | wc -l # If the number of Error Row Count occurrences is different from the number of .fmt files, it means that an error occurred in a specific table by the number of differences.
106 # Open the run_il_out.sh execution log (download.out) and search by table name to check.
$ cat *.log | grep 'Error Row Count' | awk -F: '{print $2}' | sort -u # If the result is only 0, there is no download failure in DBUSER_TABLENAME.log.
0 # If there is a non-zero result, the user need to look for that table and find the cause with the .log and .bad files.
Data download file#
The backup file created by running run_il_out.sh is created in USERNAME_TABLENAME.dat format.
$ ls -l *.dat
-rw-rw-rw- 1 heejung.lee heejung.lee 12457 2014-11-19 15:03 ALTITEST_ORDERS.dat
$ ls -l *.dat | wc -l # The backup result can be checked once again by comparing the number of backup files with the number of tables.
$ ls -l ALTITEST_ORDERS* # .Dat, .fmt, .log files are created for each table.
-rw-rw-rw- 1 heejung.lee heejung.lee 0 2014-11-19 15:03 ALTITEST_ORDERS.dat
-rw-rw-rw- 1 heejung.lee heejung.lee 210 2014-11-19 15:03 ALTITEST_ORDERS.fmt
-rw-rw-rw- 1 heejung.lee heejung.lee 169 2014-11-19 15:03 ALTITEST_ORDERS.log
What is aexport, iloader?#
aexport#
aexport is a utility that creates scripts that can download object creation scripts and data from the database as text files.
The database objects that aexport can extract are as follows:
- Database user
- User privilege
- Tablespace
- Table
- Table constraint
- Index
- View
- Materialized view (Provided starting from Altibase 6.3.1)
- Stored procedure
- Replication object
It is recommended that aexport be performed whenever there is a database object change.
iloader#
iloader is a utility that allows you to download or upload database data in table units. The data is saved in a text file that can be viewed by the user.
This can be used for database migration or table-by-table backup purposes.
To back up table data using iloader, a form file and various options are required. If aexport is executed, the iloader execution command is created as a script.