# Loader

## Key Features

| Since any data transfer has an impact on the data stored at the database, the export or import of table records can only be performed by users with manager privileges!
| It is recommended to save the dump always before loading new data into it.
| ## Data Export
| Exporting table data from a database basically means that the data is written into a loader file from the database. For the export of database table data the following loaders with their specific method of data transfer can be used: The binary loader as "internal loader" The CTRL+V loader The XML loader as "external loader"
| ### Exporting table data with the "internal" binary loader
| The binary table loader is an efficient tool for transferring any table data (also tables with BLOB fields) from a DataView source system into a DataView target system. The data export creates a binary loader file. It is used as an exchange medium. In addition to the exported records, the loader file additionally contains all information that specify the export process in detail (description of source tables, selection criteria, field pre-assignments).
| Complete data stock or selected table data can be exported. For the data selection you can specify tables and several selection criteria. Additionally table fields can be pre-assigned during the export. You can summarize export activities in loader objects and store and manage them in the database.
| To export individual records you can open alternatively to the load data form a record export form via [Ctrl+B] at every DataView system mask (NoSelect function). It is particularly suitable for the export of individual meta data records (e.g. an individual menu with all contained selections).
| #### Exporting complex data stock with the internal binary loader
| The internal binary loader is started via the table loader utility button () from the Agile e6 Customizing toolbar (manager priviliges required!).
| The Load Data mask for the table export as depicted above contains the following fields and submasks:
| Name of load object: Identifier for stored load objects Load file: name of the loader file Log file: name of the log file Comment: comment on the loader object Transaction clause: Combines loader activities on several tables to one transaction if "on" Export function: Export Tables: Exports the records of the selected tables of the source system into the loader file. The specified selection criteria and fields pre-assignments are considered. In addition to the exported records, DataView stores all export information (tables, selection criteria, field pre-assignments) in the loader file. Tables to load from Table name: list of the tables the data is loaded from Query field values: filter conditions in order to further limit the table data to be exported. Data of tables that are listed in the Tablename column and that match the conditions entered in the Query field is exported. Field name: table fields to limit the data export Query expression: search terms to limit the data export Replacement field values: values in order to replace existing values in tables. In every table listed in the Tablename column, the field entry will be replaced with the entry specified in the Replacement field. Field name: table field which will be automatically pre-assigned Text entry: new field value which overwrites the existing field value
| More detailed information about the fields and submasks of the Load Data mask can be found in the Manager Information in the topic "Binary loader load data mask explanation".
| Please note the following "weak point" when exporting table data with the internal binary loader: existing entity records won't be exported if they do not fulfill the query conditions.
| Searching for existing loader files in the Insert mode is not possible with WebStart deployed JavaClient because Client and Server are running on different machines with different file systems. The server has no access to the local file system of the client-side! Therefore the search button which appears in the Insert mode above the Load File field is disabled (greyed out) in case of WebStart deployed JavaClient!
| #### Exporting individual records with the binary loader [Ctrl+B]
| You can open the record export form in NoSelect state at every DataView system mask via [Ctrl+B]. The form is a minimized loader form and is used to export locally stored meta records, e.g. for exporting an individual menu object. DataView automatically determines all depending elements and exports them, e.g. all assigned selections for the individual menu.
| ### Exporting table data with the CTRL+V loader
| The CTRL+V loader is used to collect single selected data from any list and write them to a loader file. Only selected (marked) datasets can be pre-booked for the data export. When using the CTRL+V loader the following restriction applies: The CTRL+V command doesn't work for foreign tables.
| Please note that prior to using the CTRL+V loader certain prerequisites are required. As a default, the userexit xief_loa_exp_rec whose function is to pre-book the selected data for the export, is reserved for the short key CTRL+V. In order to write the selected data to a loader file, the userexit xief_loa_exp_fin has to be placed. For further information on this topic, please refer to the Manager Information!
| ### Exporting table data with the external XML loader
| The XML loader is an efficient tool to transfer any table data from a DataView source system into a DataView target system. Data export creates a loader file (binary and xml formats are supported). It is used as exchange medium. It contains a description of all source tables and the records in the source tables themselves.
| #### Using the Loader
| You can use the XML loader to transfer complete data stock as well as selected table data. You will especially use the XML loader for the following kinds of transfers: Exchanging and upgrading user data between systems with identical DataView applications Porting complete DataView applications from one data base system to another data base system (Oracle -> MSSql) Exchanging and upgrading between development and user system Exchanging and upgrading data between several systems in case of distributed application development
| #### Methods for Export
| Table data can be exported only on operating system level (shell) using an export script. (For a detailed description of the export script see the topic "Key Elements").
| #### Starting the XML loader
| The XML loader is started via the command line with the following parameter settings: loader <application> <operation> <argument(s)> The parameters indicated in the command line have the following meaning: loader : call to start the loader <application> : name of a valid application, e.g: yin_app <operation> : --export <argument(s)> for this specific operation is: export script According to the definitions above, the command line to start the loader for an export operation may look as follows: loader yin_app --export export.exp With this command the loader will start the data export from the defined tables and according to the query values defined in the export script and store the exported table data in the loader file that is also indicated in the export script.
| Please note that already existing export files will be overwritten without warning or questioning!
| #### Loader file location
| The export of table data creates a loader file *.bin or *.xml in the <dmp> directory. The loader allows you to select the tables to be exported and the records from within these tables.
| Whenever possible you should export all tables which are linked in the entity relationship model of your application. Thus you make sure that all internal references in the data model are also contained in the loader file!
| ## Data Import
| Please note that prior to any user data import, the repository meta data (entity, table, field ...) must exist. Also it must be checked if the tables and fields have been created on the database. This is an assential prerequesite for any data import as otherwise the loader won't be able to identify the type of data to be imported! Please be aware of the fact, that user data inside the loader file which belongs to not existing meta data will be ignored by the loader!
| Importing table data to a database basically means that the data is written into the database from a loader file. Since there are different import modes available that all have a different impact on the table data already existing in the database, make sure you fully understand the impact of the selected import mode before executing any import function!
| For the import of table data to the database the following loaders can be used: The binary loader as "internal loader" The "external" part of the binary loader The XML loader as "external loader"
| ### Importing table data with the internal binary loader
| The binary table loader is an efficient tool for transferring any table data (also tables with BLOB fields) from a DataView source system into a DataView target system. The data import inserts data from a previously exported loader file into the database. It is used as an exchange medium. In addition to the records which should be imported, the loader file additionally contains all information that specify the import process in detail (description of source tables, selection criteria, field pre-assignments).
| Complete data stock or selected table data can be imported. For the data selection you can specify tables and several selection criteria. Additionally, table fields which have been pre-assigned during the previous export are displayed. You can summarize import activities in loader objects and store and manage them in the database.
| Table data is imported using exactly the same Load Data mask as for the data export.
| The internal binary loader is started via the table loader utility button () from the Agile e6 Customizing toolbar (manager priviliges required!).
| The Load Data mask for the table import as depicted above contains the following fields and submasks:
| Name of load object: Identifier for stored load objects Load file: name of the loader file Log file: name of the log file Comment: comment on the loader object Transaction clause: Combines loader activities on several tables to one transaction if "on" Import function: Import Tables: The import depends on the selected mode. There are four different import modes supported (see below for a detailed description): insert update overwrite replace import full tables The loader imports the records of the loader file into the database. The specified selection criteria and fields pre-assignments are considered. DataView stores all import information (tables, selection criteria, field pre-assignments) in the loader file. DataView update: Pressing this button will replace the basic system data (contained in dtv.bin) in the database. All changes done in system tables will be replaced! Tables to load from: Table name: list of the tables the data is loaded from Query field values: filter conditions to further limit the table data to be deleted before the import which takes place only in replace mode. Data of tables that are listed in the Tablename column and that match the conditions entered in the Query field is deleted prior to the replace operation. Field name: table fields to limit the data import Query expression: search terms to limit the data import Replacement field values: values which have been replaced during the export (creation of the loader file) are displayed here. During the import nothing will be changed. In every table listed in the Tablename column, the field has the entry which is specified in the Replacement field. Field name: table field which has been automatically pre-assigned during the export Text entry: new field value which has been written during the export
| Survey window If you start the import function, a survey window will be opened in the loader form. During import you can monitor for each target table how many records are deleted, updated or newly inserted. After finishing the import you can explicitly close the survey window via the button.
| Cancelled functions and transaction clause You can interrupt running import procedures using a cancel button. In order to allow the complete rollback of actions already performed while importing, you can switch on the transaction clause. It combines single statements to one transaction. Please note that a complete rollback of an import of a large loader file with several thousand records is only possible if the rollback segment of the database is sufficiently large.
| Log file All activities executed by DataView in an import procedure can be additionally recorded in a log file. The log file is in readable ASCII format and can be opened with any editor. It lists the C_IDs of all imported records as well as the corresponding table names. For each imported record: <Tablename> <C_ID> For each imported record: <Tablename> <C_ID in the Loaderfile> <C_ID in the target system> <Import mode (I or U)> <Return code of EPSQL (0 = OK)>
| Delete processes are only logged in the log file, but not in the control window!
| The log file name including (server!)-path is entered in the field Log file (selection via file browser only possible if DataView server and client run on the same host computer). Default path and file name for the log file are ... application_dir\tmp\export.log.
| ## Import modes
| ### Import/Insert Inserts new records into the target system. "New" means, all key field entries are different to already existing ones. If unique key field entries in the loader file and the target system are identical, then these records and their related records are not imported. The unique table identifier (e.g. C_ID) will be re-assigned by DataView if necessary. The import is done in logical mode, i.e. DataView re-assigns the ident numbers of the imported records in the target system. For import/insert definitions to the selection criteria are without any effect. The automatic pre-assignments of table fields are already done during the previous export. If you start the function, a survey window will be opened. During import you can monitor for each target table how many records are deleted, updated or newly inserted. If necessary, you can interrupt the running import procedures using a cancel button. In order to allow the complete rollback of actions already performed while importing, you can switch on the transaction clause.
| ### Import/Update Updates already existing records into the target tables. "Already existing" means that the key field entries correspond. If the key field entries in the loader file and target system are identical, these and their related records are imported. Not existing records are ignored. The unique table identifier (e.g. C_ID) will not be changed. Replaces all records in master tables whose unique field entries correspond. Records in master tables and type tables are only replaced by DataView if the corresponding parent and child elements exist in the master tables. The import is done in logical mode, i.e. DataView re-assigns the ident numbers of the imported records in the target system. For import/update definitions to the selection criteria are without any effect. The automatic pre-assignments of table fields are already done during the previous export. If you start the function, a survey window will be opened. During import you can monitor for each target table how many records are deleted, updated or newly inserted. If necessary, you can interrupt the running import procedures using a cancel button. In order to allow the complete rollback of actions already performed while importing, you can switch on the transaction clause.
| ### Import/Overload This modus is a combination of the insert and update mode. Inserts new records into the target tables and also updates already existing records. The unique table identifier (e.g. C_ID) will be re-assigned by DataView if necessary. The import is done in logical mode, i.e. DataView re-assigns the ident numbers of the imported records in the target system. For import/overload definitions to the selection criteria are without any effect. The automatic pre-assignments of table fields are already done during the previous export. If you start the function, a survey window will be opened. During import you can monitor for each target table how many records are deleted, updated or newly inserted. If necessary, you can interrupt the running import procedures using a cancel button. In order to allow the complete rollback of actions already performed while importing, you can switch on the transaction clause.
| ### Import (Replace) Replaces all records in the target tables. "Replace" means that existing records which fulfill the query conditions will be removed before new records will be inserted into the target tables! Prior to writing table data from a loader file into the database, first all records in the tables of the target system identified by the selection criteria are deleted. Then DataView loads all records of the loader file adding them to the records still existing in the target tables by re-assigning the ident number of the imported records in the target system. If unique field entries in the loader file and the target system are identical, these records and their related records are not imported. The import is done in logical mode, i.e. DataView re-assigns the ident numbers of the imported records in the target system. The selection criteria responsible for deleting the records in the target system are read from the loader file and displayed in the corresponding fields. Immediately before starting the import process you can change or extend the criteria for import/replace. The automatic pre-assignments of table fields are already done during the previous export. If you start the function, a survey window will be opened. During import you can monitor for each target table how many records are deleted, updated or newly inserted. If necessary, you can interrupt the running import procedures using a cancel button. In order to allow the complete rollback of actions already performed while importing, you can switch on the transaction clause.
| ### Import Tables
| The import function first removes all records in the target tables and then imports (writes) the records from the loader file into the tables of the target system (database) without checking existing C_ID field entries.
| Please note that when using the "import tables" button (dump loader) already existing data in the database will be completely deleted before the new table data is written to the database!
| The import is done directly, i.e. the ident numbers are taken from the loader file and not re-assigned in the target system. They come from the source system. For direct import definitions to the selection criteria are without any effect. The automatic pre-assignment of table fields is possible. If you start the function, a survey window will be opened. During import you can monitor for each target table how many records are deleted, updated or newly inserted. If necessary, you can interrupt the running import procedures using a cancel button. In order to allow the complete rollback of actions already performed while importing, you can switch on the transaction clause.
| Example (export and import of customized fields) You have added or customized a number of fields in a development environment (in existing tables) and want to port these changes to a test system or a productive system. To do so, you have to export the field objects themselves, but also any required modified mask-field relations (you may have changed positions and dimensions of fields).
| How to export fields:
| Create a new loader object MED_FIELDS with the corresponding loader file name med_fields.bin.
| Select all DataView system tables relevant for the definition of the field objects. These are T_FIELD, T_MASK, T_MAS_FLD.
| First enter one selection criterion filtering the desired fields. We assume that the field name MED_TAB.MED_% identifies the changed fields, thus: T_FIELD.C_NAME | MED_TAB.MED_%.
| Using joins for the system table T_MASK you make sure now that only the masks are exported which are connected with the customized field objects. You do this with the entries T_MASK-<JOIN | T_MAS_FLD in the list of the selection criteria.
| Now create the loader file via tables/export. The survey window appears and displays the number of field objects and related masks objects that have been exported.
| How to import fields:
| Create a new loader object and select the loader file med_fields.bin. DataView will immediately show the system tables selected in the export process as well as the selection criteria.
| Switch on the transaction clause in order not to destroy the old data in the target system during the import process.
| Import in logical mode via import/overwrite in order to update changed field objects and related objects as well as to add newly-created field objects and related objects. The survey window appears and shows the number of old objects deleted in the tables concerned and the number of newly-inserted ones.
| Refresh changed masks.
| Individual objects are imported into a target system using logical import with the regular loader form.
| Example (export and import of an individual menu) Assumption: You have changed an individual menu in a development environment and want to port it to a test- or productive environment.
| How to export the menu:
| Load the desired menu into the menu data system form and open the record export form via [Ctrl+B].
| Select the loader file name t_menu.bin and start exporting via the Start button. The survey window appears and displays a menu and - automatically - several related item objects have been exported.
| Explicitly close the form via the End button.
| How to import the menu:
| Create a new loader object and select the transferred loader file t_menu.bin. DataView displays the tables and selection criteria automatically and internally selected during record export.
| Import logically via import/overload to update the changed menu and its related items. The survey window appears and shows that the old menu object and several related items have been updated or newly inserted.
| Refresh the updated menu.
| ### Importing table data with the external binary loader
| In order to import table data with the external binary loader the following syntax has to be used: dtv_ora <application_name> <dtv_user> {-c} LOAD "DTVLoad <loader-file> {I|O\U} [<log-file>]"
| Please read the output of the binary loader as it may contain hints on how to solve upcoming issues!
| ### Importing table data with the XML loader
| The XML loader is able to read any file format (*.dat, *.bld, *.bin, *.xml), but "old" loader files as well as xml loader files must first be converted to the new binary file format. (*.bin) before they can be imported to the database. This is because the XML loader is only capable of reading data from the database. In order to convert "old" and/or xml loader files to the new binary format the XML loader can be used either implicitly or explicitly. implicit conversion The implicit use of the XML loader means that "old" and/or xml loader files are converted automatically to the new binary file format without using the "convert" command. The old or xml loader file is the first parameter in the import script. The XML loader is started via the command line with the following parameter settings: loader <application> <operation> <argument(s)> The parameters indicated in the command line have the following meaning: loader : call to start the loader <application> : name of a valid application, e.g: yin_app <operation> : --import <argument(s)> for the specific operations are: import script According to the definitions above, the command line to start the loader for an implicit import operation may look as follows: loader yin_app --import import.imp explicit conversion Using the XML loader explicitly means that you first have to use the "convert" command in order to convert "old" and/or xml loader files to the new binary file format. You then have to use the "import" command to write the converted loader files into the database. The binary loader file is then the first parameter in the import script. Example to convert (more details see below): loader yin_app --convert test.bld test.bin In this example the loader file of the old binary file format (test.bld) is converted to the new binary loader file format (test.bin). Once you have converted the old loader file to the new file format, you can also use the internal binary loader to import the loader file to the database.
| It is recommended to use the XML loader in explicit mode in order to convert "old" and/or xml loader files. When using the XML loader implicitly, always the same temporary loader file name will be used to store the converted data, thus overwriting the existing file with the same file name when importing this loader file to the database. Using the XML loader in explicit mode has the advantage that old and new loader file both exist, thus providing the possibility to compare the content! The loader file has to be converted only once!
| Example (export and import of customized fields)
| You have added or customized a number of fields in a development environment (in existing tables) and want to port these changes to a test system or a productive system. To do so, you have to export the field objects themselves, but also any required modified mask-field relations (you may have changed positions and dimensions of fields).
| How to export fields:
| Create a new export script: med_fields.exp.
| Add the loader file name med_fields.bin, your user and group name and the date since when you started with your changes. Additionally, add all DataView system tables relevant for the definition of the field objects. These are T_FIELD, T_MASK, T_MAS_FLD.
| Open the command shell and enter the follwing command to run the export operation: loader yin_app --export med_fields.exp
| How to import fields:
| Create a new import script: med_fields.imp.
| Add the loader file name med_fields.bin, enter "Y" to import all tables, enter "O" to import/overwrite in order to update changed field objects and related objects as well as to add newly-created field objects and related objects.
| Open the command shell and enter the follwing command to run the import operation: loader yin_app --import med_fields.imp
| ## File Conversion
| Please note that prior to any user data conversion, the repository data (entity, table, filed...) must exist. Also it must be checked if the tables and fields have been created on the database. This is an essential prerequesite for any data conversion as otherwise the loader won't be able to identify the type of data to be imported/converted!
| The character endcoding of the database is UTF-8 (multi-byte character encoding) which allows customers all over the world to include any character of their multi-language data. Old loader files (ASCII loader files *.dat, binary loader files *.bld) which are based on a single-byte character encoding are rejected and can't be written directly into the database. The fact that one might want to load the data stored in old loader files into the database implies the need of a conversion into new UTF-8 loader files. Therefore, a converter is provided to convert "old" loader files into "new" UTF-8 loader files. The new loader is able to convert any file format into any other file format. The read file is transfered into UCS2 (Unicode) meta data and can be transferred to any other character set and file format. The new loader is able to distinguish between old (*.bld, *.dat) and new loader files (*.xml, *.bin) as well as between the different loader file formats (ASCII, binary, XML). The command line to start the loader for the conversion of a loader file may look as follows: loader yin_app --convert test_item.bld test_item.bin With this command the loader will convert the "old" loader file "test_item.bld" to the new binary file format (test_item.bin).
| Please note that the script to start the loader (loader.bat/loader.sh) is located in the following directory:
| Windows: <ep_root>/axalant/cmd/loader.bat Unix: <ep_root>/axalant/scripts/loader.sh
| The following file conversions are possible:
old binary loader file (*.bld) | new binary loader file (*.bin)
ASCII loader file (*.dat) | new new binary loader file (*.bin)
xml loader file (*.xml) | binary loader file (*.bin)
binary loader file (*.bin) | xml loader file (*.xml)
| Please note that a direct conversion from ASCII file format (*.dat) to XML file format is not possible. Instead, the ASCII loader file is automatically conversed into a binary file format and can then finally be converted to the XML file format!
| #### Configuration File In order to convert "old" loader files into loader files of the new formats, a configuration file (*.cfg) has to be set up for the converter once. This configuration file contains the default and other possible language settings that apply for the Windows Client. You can simply use an editor to create and modify the configuration file. The configuration file has to be placed in the directory <ep_root>/axalant/ini/LoaOldEncoding.cfg!
| The entry DEFAULT=cp1252 is the encoding for all single language string fields. The other language specific entries belong to multi-lingual fields.
| Please note that if the configuration file is missing, the loader will implicitly assume that all language specific fields of the source loader file are encoded with Windows code page 1252 (latin1)!
| If you know, that the data of the source loader file has been encoded differently than with Windows code page 1252 and you do not create the configuration file with the correct encoding entry , the result data will be corrupt!
| In case you encoded e.g. LN5=UTF8, and you also use GER, ENG and FRA with the default encoding in the source loader file, you still have to create entries for all other language settings in the configuration file. If you don't use the other language settings, you are basically allowed to enter any encoding, but we recommend you enter the default encoding entry cp1252!

##