Mimer SQL System Management Handbook TOC PREV NEXT INDEX

Mimer Developer Site

www.mimer.com/developer


Load and Unload Functions


The load and unload functions allow data to be moved between Mimer SQL tables and sequential files.

The functionality here is equivalent to that provided by the LOAD and UNLOAD commands in BSQL, this interface involving prompted responses while the other is command based.

The term "load" refers to moving data from a file to a table.

The term "unload" refers to moving data from a table to a file.

Choosing the load/unload function from the main menu displays another menu offering the following alternatives:

                               -- Load / Unload -- 
 
                1. Load from file INTO table, default format       
                2. Load from file INTO table, user specified format
                3. Load from file INTO table, delimited data       
                4. Unload to file FROM table, default format       
                5. Unload to file FROM table, user specified format
                6. Unload to file FROM table, delimited data       
 
                                   0. Exit      

Data File Formats

The user may choose between a default format, a user-specified format or a delimited format for the data file. In any case, one row of table data (one tuple) corresponds to one record in the sequential file.

The default format allocates space for each column according to the definition of the column in the table, with no extra space between fields in the record.

If a user specified format is chosen, the user must give the start and end positions in the record for each column of the table. Fields in the record do not need to be contiguous. If fields overlap in an unload operation, data from the earlier columns in the table definition will be overwritten in the overlapping positions by data from the later columns.

If a delimited format is chosen, the user is prompted for the delimiter character (which is entered via the keyboard). Fields in the record are separated by the delimiter character.

Both character and numerical data is written to the sequential file in string format. Files created by unloading table data can be edited with any text editor before being reloaded.

When the default or a user specified format is used, numerical data is right justified and blank padded to the size of the numeric data. For example, the number 143.6 is unloaded as the string " 143.6", occupying 6 bytes (the leading blank is the sign position).

NULL Values

Before the load or unload operation is performed, the user is asked if a preceding NULL byte is to be used.

If data is unloaded from a table with a preceding NULL byte, an extra byte is added before each field in the sequential record. This byte is assigned an ampersand (&) if the column from which the field is derived contains NULL. Otherwise the byte is blank. At unload, if the NULL byte contains an ampersand, the rest of the field is filled with periods (...).

If data is loaded into a table using a preceding NULL byte, the first byte of each field is read as a NULL flag (an ampersand in this byte indicates NULL; any other value indicates non-NULL). If the NULL byte contains an ampersand, the actual data content of the field is irrelevant.

Note: If the preceding NULL byte is used for the load operation, each field must be one byte longer than the corresponding column.

When the preceding NULL byte is used with a user-specified file format, the starting position given for each field should be the position of the NULL byte. In this case the column data starts at position+1.

If the load operation attempts to insert the NULL-value into a NOT NULL column or if some data type conversion error occurs, an error will be raised and the INSERT operation will fail.

Load Operation

The load operation transfers data from a sequential file to a table in the order of the records in the sequential file.

Default File Format

If the default file format is used, data from the file record is mapped into the table columns using the definition and order of the columns in the table.

User-specified Format

If the user-specified file format is used, the user has full control over where in the record the data for each column in the table is to be taken. The same positions in the record may be used for data inserted into as many columns in one table row as is required. Values for one row in the table may not, however, be taken from more than one record. Any columns in the table omitted from the user-specified file format will be assigned the NULL indicator or a default value as appropriate.

Delimited File Format

If the delimited file format is used, data from the file record is mapped into the table columns in the order of the columns in the table using the delimiter character to indicate the end of the data for a column.

If a string is specified which is longer than the character column width, the following error message appears:

 Input character string too long

Performing Loading

The load operation is performed, conceptually, as a series of INSERT statements, one for each row in the table.

If rows with duplicate primary key values exist in the loaded data (or if a loaded row duplicates an already existing row), only one of the duplicates is retained in the table.

The number of rows loaded, the number of duplicates found and the number of rows not loaded because of conversion errors is reported when the operation is complete.

.
Tip: If there is a large amount of data to be loaded it can be performed faster by setting the databank option to NULL before the load operation takes place. This is because transaction handling affects the performance of the load operation.
Note that the databank option may not be NULL if foreign or unique constraints are involved (an error message will be generated).
Do not forget to set the databank back to the desired option after the load is finished.

Unload Operation

When data is unloaded from Mimer SQL tables, records are written to the sequential file in the ascending primary key order of the table.

All rows are unloaded from the table. If a subset of rows is required, a view can be defined containing the required rows and the data is then unloaded from the view. (Alternatively, the whole table can be unloaded and excluded records are then deleted from the sequential file). The source table remains intact after the unload operation.

Selected columns may be unloaded by choosing the user-specified format and listing the required columns. Only the columns listed in the file format will be unloaded.

The sequential file is created at the beginning of the unloading process. It is not possible to append unloaded data directly to an existing file.

When the operation is complete the number of rows unloaded is reported.

Authorization

The ident performing a load operation must have INSERT privilege on the table into which the data is being loaded.

The ident performing an unload operation must have SELECT privilege on the table or view being unloaded.



Upright Database Technology AB
Voice: +46 18 780 92 00
Fax: +46 18 780 92 40
dbtechnology@upright.se
Mimer SQL System Management Handbook TOC PREV NEXT INDEX