|
xddlconv is a useful utility help you migrate relational database data from mainframe RDBMS to Unix, Linux and Windows RDBMS. It analyzes DDLs objects and generates:
| 1. | DDL written in Oracle, UDB or Microsoft SQL Server dialect |
| 2. | Copybook data structure to generate program conversion through XCONV toolkit |
| 3. | Control File to upload data through sqlldr oracle utility or load to upload with load utility |
| 4. | (optional) XML descriptor of the processed table |
Syntax
usage: xddlconv <[options]> [file]
Parameters
-h, --help
|
show command options
|
-t, --target <id>
|
Sets the target database environment. Possible values for <id>:
Which means, respectively, Oracle database, IBM UDB and Microsoft SQL Server
|
-v, --verbose
|
verbose mode
|
-c, --customize <file>
|
force alternative customization file. Default $HOME/etc/xddlconv.conf
|
-s, --vse
|
generate COPY and CONTROL FILE for data downloaded by ARIDBS utility. ( VSE utility)
|
-d, --db2
|
(deprecated, supported for backward compatibility only) sets the target database to UDB
|
-x, --xml
|
generate XML description of tables
|
-o,--out <path>
|
sets the root of the output directory
|
--udb-tablespace <name>
|
forces the specified tablespace for UDB
|
--preserve-schema
|
forces schema name preservation
|
Output directories
Each xddlconv output is stored in a separate directory. The location of these directories may be set using --out or providing environment variables.
Common root
It is possible to set a common root for the outputs, using the --out option on command line. This option must path to directory containing the following sub-directories:
Environment variables
It is possible to set the location of each output type, with the following environment variables:
XDDLCONTRL
|
Path where xddlconv generates control file or load (DB2) modules.
|
XDDLCOPY
|
Path where xddlconv generates copybook to convert data. Through those copy you can perform data conversion process generating xml and program conversion objects.
|
XDDLDDL
|
Path where xddlconv generates ddls for target RDBMS. It divide table and index into 2 different scripts in order to create table, load data and at the end create indexes.
|
XDDLXML
|
Path where xddlconv generates the XML description of processed tables.
|
I.e.
export XDDLCONTRL=$HOME/db2/ctl # Control file path
export XDDLCOPY=$HOME/db2/import/diction # Copybook path
export XDDLDDL=$HOME/db2/sql # Ddl path
export XDDLXML=$HOME/db2/xml # XML table descriptions
Configuration file
xddlconv has a useful customization file that permit customize some download or upload parameters. Entire help (--docs) shows and resume all this options. xddlconv looking for customization file in $HOME/etc named xddlconv.conf, if you use -c | --customize option followed file name you can force alternative file. All date and time option are valid only for ORACLE RDBMS.
Notes
Rows prefixed by '#' are handled as remarks. All commands in metalanguage are prefixed by '@' and must be in lowercase. When code is used, it is PERL 5 code.
Commands
@set target = <id>
|
Sets the target database environment. Possible values for <id>:
Which means, respectively, Oracle database, IBM UDB and Microsoft SQL Server
|
@set vse = on/off
|
set on if data downloaded with ARIDBS utility (VSE system) (equal to submit xddlconv with -s| --vse option from command line )
|
@set vse_unload_ebcdic = on/off
|
Cut 1 byte from record length (VSE system)
|
@set omit_schema = on/off
|
write table name without schema (ex. PROD.TABLE_NAME TABLE_NAME )
|
@set time2char = on/off
|
time field are managed as char (default is off)
|
@set time_format = HH24:mi:ss
|
set time format to load data ( is set to on if time2char is off )
|
@set data_format = DD.MM.YYYY
|
set date format to load data
|
@set timestamp_format = YYYY-MM-DD-HH24.MI.SS
|
set time format to load data
|
@set db2 = on/off
|
(deprecated, supported for backward compatibility only) sets the target database to UDB
|
Example
conversion from DB2 (MVS) vs ORACLE
# xddlconv.conf
#
# file parametrizzazione della conversione dei JCLs per BCCAP
#
# Omissione del OWNER sul nome tabella
@set omit_schema=on
@set date_format=YYYY-MM-DD
@set timestamp_format=YYYY-MM-DD-HH24.MI.SS;
@set time2char=off
Generate table without schema name (OWNER). Sqlldr utility require for date data types the field format. If format no match with data error occurs. You can specify date format for date, time,and timestamp fields, forcing corresponding set statement. In this case only DATE and TIMESTAMP are force to specify value. In this case time data types as treat as date fields. If you force time2char setting on you create transform time field in CHAR(8) field
conversion from DB2 (VSE) to ORACLE
# xddlconv.conf
#
# file parametrizzazione della conversione dei JCLs per BCCAP
#
@set omit_schema=on
@set vse=on
@set vse_unload_ebcdic=on
@set time_format=HH24:MI:SS
@set date_format=YYYY-MM-DD
@set timestamp_format=YYYY-MM-DD-HH24.MI.SS;
VSE system provide ARIDBS utility to download data. This utility force packed and binary fields to zoned data. So you have to set on vse options to convert and load data without problem.If you have data in ebcdic character set set vse_unload_ebcdic to on.
conversion from DB2(MVS) to UDB
# xddlconv.conf
#
# file parametrizzazione della conversione dei JCLs per BCCAP
#
# Omissione del OWNER sul nome tabella
@set omit_schema=off
@set db2=on
In UDB we have a different utility to load data, and also DDLs have a little bit change from original version.
| 
