× News Cego SysMT Croom Web Statistics Impressum
SysMT Logo
Special July 2016 - Tableset export and import modes
Category : Admin
Level : Easy
Back to Overview

Welcome to the july web special.

This month, we will explore the tableset export and import functionality. This might be useful to create backups which can later be restored to any other cego database system. Since there are limitations regarding the conistency of the data and the overall performance of this method, tableset export and import are appropriate just for some cases. To overcome these limitations, we will discuss in another web special how to save and restore data via backup and restore procedure. For now, lets start and see, how export and import basically works.

Any export and import operations can be done either with a running or stopped database system. In this workshop, we just will discuss the online functionality.
Dedicated command line options are provides with the cego database program, if you would like to perform these operations without having the database started.

To use the database online, we first have to connect to a running database using the admin console cgadm To follow this, you should have already set up a cego database system with any online tableset. If you are still not ready for this, please refer to the cego documentation Getting Started

Now we can list the available tablesets for the database.

CGADM > list tableset;
+----------------+----------------+----------------+
| TSL            | TSL            | TSL            |
| NAME           | RUNSTATE       | SYNCSTATE      |
+----------------+----------------+----------------+
| croom          | DEFINED        | SYNCHED        |
| sysmt          | ONLINE         | SYNCHED        |
| lit            | OFFLINE        | SYNCHED        |
+----------------+----------------+----------------+
Tableset list
ok ( 0.002 s )

A tableset has to be online to be exported or imported. From the above list, we select tableset lit for our first shot. We use XML as export mode, which creates a readable file.

CGADM > export xml tableset lit to '/tmp/lit.exp';
Exporting table lit_user ...
Exporting table lit_ticket ...
Exporting table lit_credits ...
Exporting table lit_task ...
Exporting table lit_clog ...
Exporting table lit_ticketlog ...
Exporting table lit_log ...
Exporting table lit_tasklog ...
Exporting table lit_sw ...
Exporting btree lit_user_pbtree...
Exporting btree lit_user_b1...
Exporting btree lit_ticket_pbtree...
Exporting btree lit_task_b1...
Exporting btree lit_task_pbtree...
Exporting btree lit_log_b1...
Exporting view lit_vwebstat2...
Exporting view lit_vwebstat...
Exporting procedure lowCal...
Exporting procedure newTicket...
Exporting table data for lit_user ...
10 rows exported
Exporting table data for lit_ticket ...
29 rows exported
Exporting table data for lit_credits ...
10 rows exported
Exporting table data for lit_task ...
60 rows exported
Exporting table data for lit_clog ...
1531 rows exported
Exporting table data for lit_ticketlog ...
26 rows exported
Exporting table data for lit_log ...
366741 rows exported
Exporting table data for lit_tasklog ...
185 rows exported
Exporting table data for lit_sw ...
14 rows exported
Tableset lit exported
ok ( 9.504 s )

As you can see from the export protocol, all contained database objects for the corresponding tableset are stored to the export file. Since the tableset is online running and might be modified by another user, there might be subsequent transactions which are not contained in the export. There is no way to roll forward these transactions using the online redo logs.
It is one of the biggest disadvantages and restricts the use of tableset exports for backup purposes. If you have concerns about this, don't worry. In a later special, we will discuss more the sophisticated database backup and restore functionaliy. This enables transaction save recovery of a database either for point in time or recover up to crash recovery. For now, we continue with further capabilities of the export und import feature.

To perform exports automatically on a regular base, the following shell script can be configured as a daily cronjob

#!/usr/local/bin/bash
DAYOFWEEK=`date +%w`
LITBUFILE=/serv/db/backup/exp-lit.$DAYOFWEEK
SYSMTBUFILE=/serv/db/backup/exp-sysmt.$DAYOFWEEK
BUCMDFILE=/tmp/bulit.cmd
echo "export xml tableset lit to '$LITBUFILE';" > $BUCMDFILE
echo "export xml tableset sysmt to '$SYSMTBUFILE';" >> $BUCMDFILE
/usr/local/bin/cgadm --port=2050 --batchfile=$BUCMDFILE --user=cgadm/secret

To restore a tableset from a succesful created export file, the tableset has to be prepared for import. First it has to be cleaned from all existing database objects. The most easy way to do this, is to drop the tableset and recreate it. The following subsequent admin commands illustrate this procedure.

CGADM > stop tableset lit;
Tableset lit stopped
ok ( 0.005 s )
CGADM > drop tableset lit;
Tableset lit dropped
ok ( 0.004 s )
CGADM > create tableset lit;
Tableset lit created
ok ( 0.445 s )
CGADM > start tableset lit;
Tableset lit started
ok ( 0.002 s )
CGADM > import xml tableset lit from '/tmp/lit.exp';
Importing table data for lit_user ...
Importing table data for lit_ticket ...
Importing table data for lit_credits ...
Importing table data for lit_task ...
Importing table data for lit_clog ...
Importing table data for lit_ticketlog ...
Importing table data for lit_log ...
Importing table data for lit_tasklog ...
Importing table data for lit_sw ...
Importing btree lit_user_pbtree...
Importing btree lit_user_b1...
Importing btree lit_ticket_pbtree...
Importing btree lit_task_b1...
Importing btree lit_task_pbtree...
Importing btree lit_log_b1...
Importing view lit_vwebstat2...
Importing view lit_vwebstat...
Tableset lit imported
ok ( 17.498 s )
CGADM >

The tableset is now available with the data imported from the export file and can be used in a normal way. Please note, that during the import the tableset is already online and can already be used by other database users. Since the charset of the XML export file format is UTF-8 and does not contain any binary data ( any blobs are uuencoded ), the file can be copied to an other machine ( with another OS or processor architectire ) and can be imported there. The name of the target tableset must match the name of the source tableset, otherwise the import fails. If required, the file can be edited with a normal text editor.

In the sample above, we discussed how to perform an xml export xml to a file. This method produces a machine independent UTF-8 file but in terms of performance, it is not very fast. With the restriction of binary compatibility, we can also can use a binary export format.

CGADM > export binary tableset lit to '/tmp/litexp.bin';
Exporting table lit_user...
Exporting table data for lit_user ...
10 rows exported
Exporting table lit_ticket...
Exporting table data for lit_ticket ...
29 rows exported
Exporting table lit_credits...
Exporting table data for lit_credits ...
10 rows exported
Exporting table lit_task...
Exporting table data for lit_task ...
60 rows exported
Exporting table lit_clog...
Exporting table data for lit_clog ...
1531 rows exported
Exporting table lit_ticketlog...
Exporting table data for lit_ticketlog ...
26 rows exported
Exporting table lit_log...
Exporting table data for lit_log ...
366741 rows exported
Exporting table lit_tasklog...
Exporting table data for lit_tasklog ...
185 rows exported
Exporting table lit_sw...
Exporting table data for lit_sw ...
14 rows exported
Exporting btree lit_user_pbtree...
Exporting btree lit_user_b1...
Exporting btree lit_ticket_pbtree...
Exporting btree lit_task_b1...
Exporting btree lit_task_pbtree...
Exporting btree lit_log_b1...
Exporting view lit_vwebstat2...
Exporting view lit_vwebstat...
Exporting procedure lowCal...
Exporting procedure newTicket...
Tableset lit exported
ok ( 4.433 s )
CGADM > 

As you can see from the used time for the binary export, the export speed improves from 9.5 seconds used for XML export to 4.4 seconds used for binary export. The created file os not human readable and just can be imported on a database with the same processor architecture.

If the table data does not contain any clob or blob objects, plain mode can be used. In plain mode, tuples are written as complete chunks down to the file, which improves export performance significantly.

CGADM > export plain tableset lit to '/tmp/litexp.plain';
Exporting table lit_user...
Exporting table data for lit_user ...
10 rows exported
Exporting table lit_ticket...
Exporting table data for lit_ticket ...
29 rows exported
Exporting table lit_credits...
Exporting table data for lit_credits ...
10 rows exported
Exporting table lit_task...
Exporting table data for lit_task ...
60 rows exported
Exporting table lit_clog...
Exporting table data for lit_clog ...
1531 rows exported
Exporting table lit_ticketlog...
Exporting table data for lit_ticketlog ...
26 rows exported
Exporting table lit_log...
Exporting table data for lit_log ...
366741 rows exported
Exporting table lit_tasklog...
Exporting table data for lit_tasklog ...
185 rows exported
Exporting table lit_sw...
Exporting table data for lit_sw ...
14 rows exported
Exporting btree lit_user_pbtree...
Exporting btree lit_user_b1...
Exporting btree lit_ticket_pbtree...
Exporting btree lit_task_b1...
Exporting btree lit_task_pbtree...
Exporting btree lit_log_b1...
Exporting view lit_vwebstat2...
Exporting view lit_vwebstat...
Exporting procedure lowCal...
Exporting procedure newTicket...
Tableset lit exported
ok ( 1.663 s )

Using the plain method, export time could be reduced to 1.6 seconds. As mentioned, this method is restricted to clob and blob free table data.

The restore procedure for binary and plain exports is the same as discussed for xml exports.

Hopefully, this was a useful lesson and stay tuned for the upcoming august special !