Loader

Key Elements

 

Prerequisites

Prior to using the external loader for any data transfer you first have to create an export or import script in order to define query values for the table data to be transferred. The name of the export/import script is free choice, but you have to make sure the file ending is "*.exp" or "*.imp".

In the following the export and import scripts are described in detail.

 

Export Script

The parameters (query values) which might appear in an export script are as follows:

export.bin Name of the export file (default: export.bin)
. File width (default: 80) ignored
Y Query values Yes/No (if No: the values until the
tables are ignored!)
USER1 Query user1
. Query user2 or "." = end of user list
GROUP1 Query group1
. Query group2 or "." = end of group list
.

Query since creation date or "." = no value (Boolean: >=)

Example for the date format: 31.11.2007@12:37:57

. Query since update date or "." = no value (Boolean: >=)
Table_1
Table_2
Table_n
.

Table names

Table name or "." = end of table list

Please note that for reasons of compatibility either a point or a number has to be entered as parameter for the file width although a number indicated won't have an impact on the file width!
  As a general rule as how to handle the query values in an export script, it is necessary to enter "Y" to point out that query values are following. Possible query values are user name(s), group assignment(s) or creation and update date. This is an effective way to limit the amount of the exported data. No matter how many query values you will define,
it is required to indicate those tables in the export script where you know changes were conducted. If the tablesare not known exactly, you may use the SQL-trace function to find them out.
Please note that several usernames as well as groups are linked as an "OR"-combination whereas the different query options (User, Group, Cre-Date, Upd-Date) are linked as "AND"-combination! Usually the number of records can be significantly reduced using the "AND"-combination!
 

In the example below, "N" is entered for the query values. This implies that no further query values are defined. The export script will look more concise and the loader file will contain all entries from the table T_MESSAGE (except the system messages) independent from user, group assignment or creation and update date.

export.bin
.
N
T_MESSAGE
.

 

Import Script

The parameters (query values) which might appear in an import script are as follows:

export.bin Name of the import file (default: export.bin)
Y Import all tables? Yes/No
I Import mode
Please note that if "N" is entered for the query value as depicted in the example below, the table data will be automatically
imported in "X" import mode (dump loader mode) which implies a direct import without checking/changing the C_ID values. Therefore no id conflict handling will be provided!
 

The following import modes are available:

  • I = insert
  • U = update
  • O = overwrite (insert and update)
  • R = replace (means delete everything existing before loading)

In case you want to import directly, but don't want to import all tables, the import script may look as follows:

export.bin Name of the import file (default: export.bin)
N Import all tables? Yes/No
Y Load data directly? Yes/No
Table_1
Table_2
Table_n
.

Table names

Table name or "." = end of table list

In this case only the following import mode is available:

  • X = direct import without checking/changing the C_ID value
Please note that in the X-modus (dump loader modus) C-ID's won't be replaced. Therefore no id conflict handling will happen!
 

Loader file

A loader file is automatically created when table records are exported from a database. It is the file the exported table records are written to. In case of a data export the loader file is also referred to as "export file" (import = import file). You can see from the first export script depicted above that the default name of the loader file (export.bin) is listed in the first line. You may change the default name to a name of your choice, but
you need to make sure the file ending is correct according to the kind of loader you want to transfer table data with. As an example, if you want to use the binary loader for a data transfer the loader file ending must be "*.bin". The external loader also offers the additional suffix "*.xml". Different loader file endings than "*.bin" and "*.xml" are declined (internal loader) or always corrected to "*.bin" (external loader). The loader file is stored in the dmp directory of your application.

   
 

Structure of the loader file

Depending on the file format (*.bin, *.xml) the export file has a different structure.

 

Binary loader file

The binary loader file consists of the following five parts:

1. Encoding part

The encoding part contains information about the encoding, the version and the type of loader file.

Example:

ENC/***
ENC->UTF-8
VER->1.0
TYP->BIN CTRL-V

2. Query

The query part contains all the query definitions.

Example:

QUE/***
31|C_UPD_DAT:>=20.11.2003@00:00:00
31|C_CRE_DAT:>=20.12.2003@00:00:00
9|C_GIC:201
9|C_UIC:201

3. Text

The text part contains column names and a corresponding value. The loader looks for the column name in every table and replaces the existing value with the value indicated behind the column name.

Example:

TXT/***
10|C_UIC:1006

4. Description

The description part simply lists all the column names of the tables.

Example:

DSC/T_MASTER_DAT
C_ID
C_VERSION
C_LOCK
C_UIC
C_GIC
C_CRE_DAT
C_UPD_DAT
C_ACC_OGW
C_NAME*
...


5. Data

In the data part only the values for the column names are listed, follwing the order in the description part. The gaps in between indicate an empty field.

Example:

DAT/T_MASTER_DAT
1976674548
4
0
1009
200
2008-01-23 07:57:38
2008-01-23 10:07:28
3|dwr
17|EDB-CHKNOTALLOWED
...

Please note that the characters indicated after a pipe are read as one string including line breaks!
 

xml loader file

The xml loader file consists of the following six parts:

1. Information

This section contains information about the version and the file type of the loader file. Because the encoding information is part of the xml file header, it is not mentionned here.

Example:

<information>
<version VER="1.4"/>
<type TYP="XML"/>
</information>

2. Query

This section contains the query condition names and their values.

Example:

<query>
<condition fieldName="C_UIC" queryValue="1007 | 202 | 1008"/>
</query>

3. Replacements

This section contains the replacements.

Example:

<defaults>
<default fieldName="C_CRE_DAT" defaultValue="2010-03-27 11:11:11"/>
</defaults>

4. Definition

The definition section contains the table names with relations and the type of relation. In case the file is converted from a binary loader file, this part remains empty.

Example:

<definition>
<table name="T_MASTER_DAT">
<relation type="REF" table="T_MASTER_HIS"/>

5. Description

The description section contains the table name and the column names included as well as the type and length and optionally the primary key flag.

Example:

<description>
<table name="T_MASTER_DAT">
<column name="C_ID" type="I" length="10"/>
<column name="C_VERSION" type="I" length="10"/>
<column name="C_LOCK" type="I" length="10"/>
<column name="C_ACC_OGW" type="S" length="9"/>
<column name="C_NAME" type="S" length="60" primaryKey="true"/>
...
</description>

6. Data Part

The data part contains the entries for the table names listed in the description part.

Example:

<data>
<table name="T_MASTER_DAT">
<record C_ID="1976674548" C_VERSION="2" C_LOCK="0" C_ACC_OGW="dwr" C_NAME="EDB-CHKNOTALLOWED"...
...
</table>

Please note that in the data part long text fields and blobs are Base64 encoded!
Since all record data is written in one line, those lines can be very long!