.
Eloquence B.07.10 contact contact

Documentation / TurboIMAGE database migration utilities

TurboIMAGE database migration utilities

 
.
  The Eloquence MPE database migration utilities provide an easy and straight forward approach to move your TurboIMAGE databases to Eloquence.

  • The Eloquence schema processor understands TurboIMAGE schema files so any schema file could simply be transferred to the target system and used with the Eloquence schema processor.

  • Eloquence provides a utility to extract the content of any database to a set of flat files. These export files are transferred to the target system and used to reload the database.

Moving your database to Eloquence involves the following steps:

  1. Install the DBEXPORT utility on HP3000
  2. Export the database
  3. Transfer the schema file and the export files to the target system
  4. Create and import the database on Eloquence

Limitations:

  • No support for Business Basic decimals
  • Export files on MPE are limited in size to 2GB (workaround available)
  • Binary export mode includes the IMAGE record number and chain pointers along with a binary representation of the record. Broken chains or corrupted data will show up in Eloquence.



Contents


Install the DBEXPORT utility on the HP3000

Eloquence comes with utilities to load and unload the database. Eloquence supports both using text files to hold the database content or using binary files.

An equivalent utility is also available for the HP3000 and may be used to unload your database. It can be downloaded from the Eloquence ftp server at the URL below:

The following files are available:

  • LICENSE - license for the migration utilities (BSD like)
  • RELNOTES - release notes documenting recent changes
  • hp3ksrc.tar.Z - source code
  • hp3kbin.tar.Z - binary version, tar format (MPE 6.0 or newer)

Note: When using ftp please make sure you download the archive files (.tar.Z) in binary format.

When using ftp to download an archive file to an MPE system you need to provide an alternative file name which is MPE compliant or prepend the file name on MPE with a ./ (dot slash) to make sure it is accepted as a POSIX file name, such as shown below:

ftp> bin
ftp> cd /eloq/B0710/mpe/migration
ftp> get hp3kbin.tar.Z ./hp3kbin.tar.Z

To install the tar archive on your system, please enter the POSIX shell and follow the procedure below:

$ tar -xvzopf hp3kbin.tar.Z
This restores the archive content in the current directory.

As an alternative, you may extract the archive file from the MPE prompt with a command like below:

:/bin/tar "-xvzopf hp3kbin.tar.Z"
To use binary export (recommended), the DBEXPORT utility needs to run in privileged mode and must be installed in a privileged group. Also, the program capabilities must be changed with linkedit as below:
:linkedit "altprog DBEXPORT; cap=ia,ba,pm"
This adds the PM capability to DBEXPORT.

Compiling from source code

The source code to the Eloquence data migration utilities is available as an option. To build your own binary from source code you need the HP C compiler.

Download the source archive to MPE, unpack it with tar and run make -f Makefile.MPE (in a POSIX shell). As a result, the programs DBEXPORT and DBINFO are built.

Compiling was only tested with the HP C compiler on MPE 6.5 or newer. As the MPE 6.0 HP C compiler does not support the "long long" type, you need to modify the Makefile to set the HAS_LONG_LONG=0 to get it build on MPE 6.0.


The DBEXPORT utility

DBEXPORT is used to export the database contents to one or multiple text or binary files. To export to binary files, DBEXPORT must be run in PRIV mode.

When started without command line arguments (or with the -help option) the command line options are printed.

usage: DBEXPORT [options] database [set[.item] ...]
options:
 -help    - show usage (this list)
 -version - show version information
 -u user  - set user name
 -p pswd  - set password
 -o path  - set output directory (not single file)
 -v       - verbose output
 -c       - chained export
 -r       - create restructure information
 -s file  - output into single file, '-' = stdout
 -x       - exclude specified data sets
 -B       - binary export mode (needs PM)
 -S       - chained export by sort item
The MPE DBEXPORT utility is similar to, but different from the Eloquence dbexport utility. While the options (if available) are the same, not all options are available for both versions.

  • The -help option prints a short overview on the command line options.
  • The -version option prints version information.
  • The -u user option specifies the optional USERIDENT to open the database (this is rarely used on TurboIMAGE).
  • The -p pswd option specifies the password to open the database. Using creator access is recommended.
  • The -o option specifies the directory where export files are created. By default, export files are created in the current directory.
  • The -v option prints progress information during runtime
  • The -c option, which is only relevant for text mode export, causes dbexport to read detail datasets in chained mode. By default, detail records are exported in serial order. With chained export, you may optionally select the path by specifying the search item name or number for detail sets. If not specified, the primary path is used.
  • The -r option causes dbexport to output additional information in the export file on the data set structure (only relevant for text mode export). This may be used by dbimport if the database structure of the source and target database is different.
  • The -s file option allows to specify a single file for text mode export. If a - (dash) is used, dbexport will write to stdout. If not specified, the output is written to multiple files (one per dataset) in the current directory or the directory specified with the -o option.
  • The -x option may be used to indicate that any specified data set should be excluded from export.
  • The -B (upper case) option specifies to create binary export files. This requires to run dbexport in PRIV mode.
  • The -S (upper case) option is similar to the -c option. It is only relevant for text mode export and causes dbexport to read detail datasets in chained mode instead of serial mode. Similar to the -c option, you may optionally select the path by specifying the search item name or number for detail sets. Unlike the -c option, it favors the first sorted chain over the primary path, if a dataset has sorted chains and the search item name or number was not specified explicitly.
    Using the -S option instead of the -c option may improve dbimport performance on detail sets with sorted chains where the sort item is different from the primary path.

In addition, the database to be exported must be specified and optionally a list of data sets may be present (either data set name or number). If a list of data sets is provided, only the specified data sets are processed (or excluded, if the -x option is used). For a detail data set, a search item may also be specified to define the detail set export order (in case of chained export).

The most commonly used options are:

  • The -p passwd option specifies the database password used to open the database. Using creator access is recommended to avoid unexpected partial exports, if set or item level security is used in the database.
  • the -B option specifies to use binary export mode.
  • The -s file option specifies that a single file should be used, which contains all data sets (text mode). This option of text mode export is mainly useful for small databases or when output needs to be directed to a custom filename or pipe.
  • The -v (verbose) option displays progress messages
Any data set name or number specified after the database name indicates that only the specified data sets should be exported. When the -x option is present, any specified data sets are not exported.

For example:

$ DBEXPORT -p secret -v sample
Export the database sample in text mode. The database is opened with the password "secret".
$ DBEXPORT -p secret -B -v sample
Export the database sample in binary mode.
$ DBEXPORT -p secret -v -s sample.exp sample
Export the database sample in text mode, the results are written to a single text file.
$ DBEXPORT -p secret sample customers parts
$ DBEXPORT -p secret sample 1 2
Export the data sets customers and parts in text mode. The second example uses dataset numbers instead of names.
$ DBEXPORT -p secret -x sample 1 2
Export all but the first two data sets in text mode.

All the examples above used the POSIX shell. To use dbexport from the MPE prompt, you need to enclose the arguments in quotes

: DBEXPORT "-p secret -v sample"

See also:
http://eloquence.marxmeier.com/doc/html/manuals/dbms/dbms-57.html
http://eloquence.marxmeier.com/support/misc/export.html

How to use the text mode export

The text mode dbexport converts the database content to text. Unless the -s option is specified, a separate text file is created for each data set (using the naming convention DBNAME.999.exp, where DBNAME is the database name and 999 is the dataset number).

Any empty data sets or automatic masters are skipped and no export file is created.

If the -c or -S options are present, detail sets are exported in chained mode instead of serial mode. The path to be used for chained mode export may be explicitly specified with each detail set. If the path for a detail set is not specified, the -c and -S options choose slightly different defaults: the -c option always uses the primary path, whereas the -S option favors the first sorted chain for detail sets that have sort items.

Please note that DBEXPORT replaces any invalid numeric values by a zero value during export (eg. corrupted packed/zoned numbers).

See below for a workaround to overcome the 2 GB file size limitation on MPE for bytestream files.

How to use the binary mode export

The binary export mode is used when the -B (upper case B) command line option is specified.

The binary export mode creates a separate file for each dataset (unless it has zero entries). DBEXPORT uses the naming convention DBNAME.999.exb for each binary export file, where DBNAME is the database name and 999 is the dataset number. If the size of the binary export file for a data set would exceed 1 GB, however, multiple partial files are created instead, each named DBNAME.999.b## where ## is the sequential number of this partial file.

The binary export file includes a header to identify the data in the file and for each record the original record number and the chain pointers (except for the synonym chain information for a master set). This information may then be used by the Eloquence bimport to efficiently import the database into Eloquence, retaining the chain order and record number for any detail set.

See below for a workaround to overcome the 2 GB file size limitation on MPE for bytestream files.


The DBINFO utility

DBINFO lists the data sets for the specified database including data set name, type, number of entries and capacity. When started without command line arguments (or with the -help option) the command line options are printed.
usage: DBINFO [options] database [set ...]
options:
 -help    - show usage (this list)
 -version - show version information
 -u user  - set user name
 -p pswd  - set password
  • The -help option prints a short overview on the command line options.
  • The -version option prints version information.
  • The -p pswd option specifies the password to open the database.
The database argument specifies the name of the database.

Optionally, one or more data set names or numbers can be specified after the database name (separated by spaces). In this case, only information on the specified data sets is returned. By default, all accessible data sets are displayed.

:dbinfo testdb
Processing database : testdb

SET NAME              RECLEN CAPACITY ENTRIES
---------------- -- - ------ -------- --------
TEST             01 D    111      100        1

To use DBINFO from the MPE prompt, you need to enclose the arguments in quotes if there is more than one argument.


Exporting the database

It is typically preferable to export the database in binary mode as this usually results in better import performance and also retains the order for all paths.

Exporting the database in text mode is easier to use, the export files are typically smaller and source and target databases may differ to some extent.

Exporting the database in text mode:

When running the DBEXPORT utility from the POSIX shell, the arguments are separated by spaces.

$ DBEXPORT -p SECRET -v SAMPLE
When running from the MPE prompt, you need to enclose the arguments in quotes.
: DBEXPORT "-p SECRET -v SAMPLE"
This creates output files SAMPLE.001.exp ... (one for each non-empty data set, except for automatic masters).

Exporting the database in binary mode:

When running the DBEXPORT utility from the POSIX shell, the arguments are separated by spaces.

$ DBEXPORT -B -p SECRET -v SAMPLE
When running from the MPE prompt, you need to enclose the arguments in quotes.
: DBEXPORT "-B -p SECRET -v SAMPLE"
This creates output files SAMPLE.001.exb ... (one for each non-empty data set). Please note that automatic master sets are exported as well.


Transferring the database to the target system

ftp may be used to transfer the schema file and the export files to the target system.

To transfer the schema file, it must either be converted to bytestream format (/bin/tobyte -a [-t]) and then transferred in binary mode or you may transfer it in text mode. The ftp utility will then convert it to bytestream mode during the transfer.

Any export files (either text or binary export files) must be transferred in binary mode.


Creating the database on Eloquence

On Eloquence, first run the schema utility and the dbcreate utility.
$ dbschema schemafile
$ dbcreate database
Where schemafile is the file name of the schema file and database is the name of the database.

Note: dbschema is equivalent to schema -T. The -T option to schema indicates that the schema file follows the TurboIMAGE conventions. Eloquence schema files are similar to TurboIMAGE schema files but use different options and item sizes are specified in bytes instead of item type specific units.

Importing the database from text mode export (dbimport)

When using text mode export, the Eloquence dbimport utility is used to populate the database from the export files.
$ dbimport [options] database [set [...]]
Typical options:
  • The -help option prints a short overview on the command line options.
  • The -v (verbose) option displays progress messages
  • The -s file option specifies that all data sets are contained in a single file.
The database argument specifies the name of the database and may include the server system (host name or IP address) and database instance (service name or port number).

A list of data set names or numbers can be can be specified after the database name. In this case only the specified data sets are processed. By default, all accessible data sets are imported.

For example:

$ dbimport -v sampledb
$ dbimport -v -s sampledb.exp localhost:8208/sampledb

On the Windows and Linux platform you should use the -z roman8 command line option of dbimport to make sure any national characters ("Umlaute") are converted properly.

Importing the database from binary mode export (bimport)

The bimport function, that has been added with Eloquence B.07.10, allows to efficiently import data from TurboIMAGE while retaining the chain information. For detail sets, the record number is also retained.

While undocumented, the bimport function is included for testing purposes in the most recent Eloquence B.07.00 eloqdb6 patches for the HP-UX platform.

Currently, bimport is not a conventional utility program but integrated into the database server process and invoked with the dbctl utility.

dbctl bimport [options] database set bexp_file
This first erases the specified data set and then processes the specified import file. As the binary import files are directly accessed by the eloqdb6 server process, the files must be readable by the user that is configured for the eloqdb6 server and must reside on the same system.

The following options are supported with bimport:

  • /v - Display additional information.
    Specifying the option /v twice (/v /v) causes the import progress to be output to stderr for every 10,000 records.

  • /a - In case more than one bexp file is needed to import a data set, this flag is specified with any subsequent bexp file. This does not apply to binary export files created on MPE; see note below.

  • /n - This flag must be specified if additional bexp files exist for this data set. This does not apply to binary export files created on MPE; see note below.
Note: The current MPE dbexport creates a single binary export file per dataset or splits its output into multiple partial files, if the size would exceed the 2 GB limit for MPE bytestream files. These partial files, however, are not compatible with the /a and /n options of bimport.
See below for a workaround to overcome the 2 GB file size limitation on MPE for bytestream files.

For example:

$ dbctl bimport /v /v sample 1 $PWD/SAMPLE.001.exb
This imports the data set 1 of the database sample from the file SAMPLE.001.exb (an absolute path to the export file is specified).

An example script (bimport.sh) is shown subsequently in this document that may be used to automatically import all sets of a single database.

Completing the database migration

In addition to moving the database, it might be necessary to update the database settings (eg. CIUPDATE flag) according to the original setting on MPE. This is done with dbutil.

In case a TPI package was used on MPE, any indexes must be re-created. Eloquence includes limited support for TPI indexes and may be used to handle indexing. In this case the index is either created with dbutil after migration or added to the schema file before creating the database.

You may also want to consider changing the database security settings as by default the Eloquence schema creates databases that are accessible with the default "public" user.

Import performance considerations

Unless you import a trivial volume of data, you may want to consider to (temporarily) set SyncMode = 0 in your eloqdb6.cfg configuration file and disable any forward logging during the import to improve performance and conserve forward log disk space.

dbctl may also be used to change both settings dynamically:

dbctl -u dba syncmode off
dbctl -u dba forwardlog disable

Important: the above suggestion of disabling syncmode and forward logging assumes that the database server instance is used exclusively by the import activities (i.e. no concurrent users on other databases in the same server instance) and that you have a proper backup of the database server as a fallback option in case of problems.

To further improve import performance, you may also want to consider increasing the BufferCache setting in your eloqdb6.cfg file and, if on HP-UX, increasing the dynamic file system buffer cache size with the dbc_max_pct kernel parameter.


Potential Issues

A number of subtle differences between Eloquence and TurboIMAGE may affect the database migration. The potential issues below were encountered in migration projects:

Item type U (upper case strings)

The item type U (upper case strings) is enforced with Eloquence. With TurboIMAGE the item type U is only used as an application hint. Eloquence will upshift the item content when the record is modified.

This immediately affects the text mode import (dbimport). While any item type U items are unmodified when using binary import the item contents will be changed subsequently when modified by the application.

It is recommended to change the item type U to X if lower case data are required in a string.

Binary values in text items

Binary values placed in a string item (type X or U) may result a subsequent problems. When the client and server use different character sets or byte orders Eloquence internally converts any item values on the fly as required. As Eloquence does not know about them, any binary values placed in a string item will also be subject to a conversion, likely resulting in unexpected values.

Binary values in string items work as long as the new target system uses the same character set and byte order as MPE (eg. HP-UX on PA-RISC or Itanium). It will break when converting to a system using a different byte order or character set (eg. Windows on x86).

The recommended solution is to consider correcting the application to use separate items for binary values. As an option, changing the item to item type B (binary) indicates to Eloquence that no conversion is allowed on that item.

Value aliases (packed and zoned items used as search item)

Packed and zoned items could use a different encoded value to represent the same numerical value. For example, there could be a positive zero, a negative zero and an unsigned zero encoded value that all represent the same numeric value (zero) but use different binary representation.

TurboIMAGE considers different binary values to be different, even if they represent the same numeric value. In a manual master, multiple records could be present that have the same numeric value but a different binary encoded value as the search item.

Eloquence uses the numerical value for any comparison (not the binary encoded value) and thus considers records with the same numeric value for a search item a duplicate in a manual master. This might result in a problem when moving existing databases from TurboIMAGE as the database import fails with a status 43 due to a duplicate entry for a manual master while this was no problem on TurboIMAGE.

The suggested solution is to correct your data on MPE before moving it to Eloquence.

When using binary export it is recommended to check the database consistency (for example, using ADAGER or DBGENERL) before migration. With binary export mode any database inconsistencies will also affect Eloquence any may result in a problem during bimport or broken chains after the migration.


DBEXPORT B.07.10 enhancements

New -S option
The new option -S (upper case S) may be used to specify chained export mode and that a sorted chain, if present, has preference over the primary path. The option -S implies option -c (chained export mode). Please note that the -S option only affects the default path to be used for (text mode) exporting a detail set. A separate path may still be specified explicitly.

Specifying the search item in chained export mode
When using chained export mode (option -c or -S present) the search item for a detail set may be specified to override the default of using the primary path. The (detail) search item name or number may be specified with the dataset, separated by a dot.

The examples below all specify to export the data set PO-RECEIPTS (set number 39) of the data base IOSDB, using chained export and the search item PART-NUMBER (item number 159).

DBEXPORT -c IOSDB.QEDDB 39
DBEXPORT -c IOSDB.QEDDB 39.PART-NUMBER
DBEXPORT -c IOSDB.QEDDB 39.129
DBEXPORT -c IOSDB.QEDDB PO-RECEIPTS.159
DBEXPORT -c IOSDB.QEDDB PO-RECEIPTS.PART-NUMBER

Binary export mode was enhanced to split bexp files
When using binary export mode (-B option present), a new export file is created for each GB of data. In this case no .exb file but multiple partial files are created that need to be combined to run bimport.

This allows binary exporting data sets up to a size of 100 GB with little extra effort by concatenating the files. See below for an example.

Please note that this is different from the bimport option to use multiple input files (as specified with the options /v and /a). For bimport to accept multiple files for a data set each file must provide a valid BEXP header which is not supported by DBEXPORT at this time.


DBEXPORT workarounds

The following workarounds may be useful to overcome some commonly encountered limitations with the dbexport database migration tool.

Overcome the 2 GB filesize limitation with text mode export

To overcome the (MPE) 2 GB filesize limitation with text mode export, files you may pipe the output to split.

DBEXPORT -s - database | split -b 1000m - exp.
This will split the export file into 1000 MB chunks named exp.aa, exp.ab ... On the target system you may combine the files with a command like below:
cat exp.* > database.exp
You may also pass the result to dbimport though stdin:
cat exp.* | dbimport -s - database

Performance problem during import with sorted chains (text mode export)

Sorted chains may cause a performance problem in text mode. It might help to export the database in sorted order. The -S options specifies to use chained export mode giving preference to any sort items (instead of primary path).

dbexport -S database
This attempts to retain the chain order for the first sort item which to reduce the overhead of the chain rebuild on the target platform.

Using the -x option (exclude) you may also export the database in two steps. In the first step, all "normal" data sets are processed, excluding any sets that would benefit from different options. In the second step any data set is exported specifying specific options.

dbexport -c -x database 5
This exports the entire database except set #5
dbexport -c database 5.CUSTNO
This uses the chain associated with the item CUSTNO to export set #5.

Overcome the 2 GB filesize limitation with binary mode export

To overcome the (MPE) 2 GB filesize limitation with binary mode export files, the dbexport utility automatically creates multiple .b## files if the export file for a data set would exceed 2 GB.

Binary export files are split into multiple segments for each GB of data. In this case no .exb file but multiple partial files are created that need to be combined on the target platform to run bimport.

For example, exporting set #5 could create the following partial binary export files: SAMPLE.005.b00 SAMPLE.005.b01 SAMPLE.005.b02 ...

The cat command may be used like below to combine these partial binary export files into a complete export file.

   cat SAMPLE.005.b* > SAMPLE.005.exb
While bimport has the option to process partial files (each with a complete bexp header) this approach is currently not supported by DBEXPORT.

Example shell script to bimport a database.

This example script also combines any partial MPE binary dbexport files before importing.

# bimport.sh - Eloquence example script to bimport a database
#
# This erases the target database and then attempts to bimport 
# any files that follow the pattern DBNAME.999.exb
#
# As the bexp files are directly accessed by the eloqdb6 server
# process the files must be readable by the user that is 
# configured for the eloqdb6 and must reside on the same system.
#
# usage:
# bimport.sh database
#
# Note: the database name may not specify an Eloquence 
# instance. Please use EQ_DBSERVER as a workaround.
#
# Note: bimport needs write access to the database. Don't 
# specify the dba user with the dbctl bimport.
#
# Note: Specifying the /v (or /v /v) option with bimport 
# provides a progress report that is printed to stderr.

if [ $# -lt 1 ]; then
   echo "usage: $0 database"
   exit
fi

db=$(echo $1|tr [a-z] [A-Z])
pwd=$(pwd)
#opt="/v /v"

# erase database
dberase $db

# concatenate any partial files
for b00_file in $db.*.b00
do
 set=$(echo $b00_file|cut -d. -f2)
 if [ ! -f "$db.$set.exb" ]; then
  echo "creating $db.$set.exb from fragments ..."
  cat $db.$set.b[0-9][0-9] > $db.$set.exb
 fi
done

for bexp_file in $db.*.exb
do
 set=$(echo $bexp_file|cut -d. -f2)
 if [ -z "$opt" ]; then
  echo "$db.$set ... \c"
  dbctl bimport $db $set $pwd/$bexp_file
 else
  echo "$db.$set ..."
  dbctl bimport $opt $db $set $pwd/$bexp_file
 fi
done


 
 
.
 
 
  Privacy | Webmaster | Terms of use | Impressum Revision:  2009-11-11  
  Copyright © 2006-2008 Marxmeier Software AG