|
dbunload
Description
The dbunload command line utility unloads data from one relational table to one flat file. With dbunload, you can unload all rows from an entire table or select columns and records by using PL/SQL language. The output records written by the dbunload utility are compatible as input to the SQLLDR oracle utility by using control file generated from xddlconv XFRAME utility, and to LOAD udb utility.
Synopsis
dbunload <table> [<file>]
Parameters
table is the name of the table to download
file is the flat file name, if omitted data are written to STDOUT
Configuration file
Configuration file is necessary to run dbunload utility. It must to be placed in $HOME/etc, be named dbunload.conf and has to contain oracle connection parameters, as following.
For Oracle
user=user
password=password
database_url=jdbc:oracle:thin:@linux01:1521:htoradb
inquiry_structure=[yes|no]
For UDB (DB2):
user=user
password=password
database_url=jdbc:db2://linux02:50000/htdb2
rdbms=db2
where:
user
|
user for RDBMS
|
password
|
password for RDBMS
|
rdbms
|
default is oracle, for to db2 for UDB connection. Provide to setting correct jdbc driver for remote connection.
|
database_url
|
URL of database connection
|
structure_inquiry
|
(optional) enables override of columns data type. New datatype is read from UNLOADER_TABLE.
|
Environment variables
UNLOADER_SELECT
It specifes the text file containing the SQL SELECT instruction to perform in place of the entire table unloading.
I.e.
export UNLOADER_SELECT="$HOME/etc/unload_table01.sql"
where the file contains:
SELECT FIELD1, FIELD2, FIELD3
FROM MYTABLE
WHERE FIELD4 = 'AAAA'
OR FIELD3 = 01
The UNLOADER_TABLE definition
The UNLOADER_TABLE may be created to contain columns redefinition datatype. User have to generate this table in the RDBMS, if inquiry_structure is set to "yes" in the dbunload.conf file.
Table creation
In order to create UNLOADER_TABLE, the following DDL must be used
CREATE TABLE UNLOADER_TABLE (
TABLE_NAME CHAR(60) NOT NULL,
FIELD_NAME CHAR(60) NOT NULL,
FIELD_TYPE CHAR(20),
FIELD_BYTES NUMBER DEFAULT 0 NOT NULL
PRIMARY KEY(TABLE_NAME, FIELD_NAME)
)
Populating the table
In order to redefines some of the fields of one or more table, rows must be inserted in to the table. For each field to be redefined a row must be created specifing:
I.e.
INSERT INTO UNLOADER_TABLE VALUES ('TB1824', 'RICZ_NU_EDIZ', 'SMALLINT', 2);
or DATE field managed as TIME in ORACLE database
INSERT INTO UNLOADER_TABLE VALUES ('TB1824', 'START_TIME', 'TIME', 8);
DBUNLOAD data type
The dbunload utility follows the general rules and conventions of DB2 on the data type attributes and the compatibility among the data types.
Data type managed
CHAR
|
The data field contains character data. The length is the specified field's length.
|
VARCHAR
|
A varchar field is a length-value datatype. It consists of a binary length subfield followed by a character string of the specified length
|
INTEGER
|
The data is a full-word binary integer. The length, in bytes, is based on the size of a LONG INT (4 bytes).
|
SMALLINT
|
The data is a full-word binary integer. The length, in bytes, is based on the size of a INT. N.B. Oracle change SMALLINT in INTEGER datatype so it becames 4 Bytes. SMALLINT columns have to be inserted into UNLOADER_TABLE table with 2 bytes as FIELD_BYTES to unload as 2 bytes.
|
DECIMAL
|
data is in packed decimal format: two digits per byte, except for the last byte, which contains a digit and sign. It's length is calculated by field lenght / 2 + 1
|
DATE
|
field contains character data that should be converted using the specified date mask: DD/MM/YYYY
|
TIME
|
field contains character data that should be converted using the specified time mask: hh:mm:ss. TIME datatype is valid only for DB2 rdbms. If you have an Oracle database that contains DATE field used as TIME, you can manage this field as exception using UNLOADER_TABLE and settings columns as TIME length 8.
|
TIMESTAMP
|
datatype is an extension of the DATE datatype. It stores the year, month, and day of the DATE datatype, plus the hour, minute, and second values of the TIME datatype. Format: YYYY-MM-DD-HH24.MI.SS.SSXFF
|
NULL
|
Column with NULL value clause has 1 byte with X'00' value if field has a valid contents instead has X'3F' (?) if field contents null value.
|
An example
Supposing the following table description:
Name Null? Type
----------------------------------------- -------- ----------------------------
CODE NOT NULL CHAR(4)
DATE_MOD NOT NULL DATE
SUB_CODE NUMBER(7,2)
REF NUMBER(5)
When the dbunload utility is run, user gets the following output:
# unloader TABLE01 $HOME/some/where/flat_file.dat
inquiry_structure=yes
Open Connection to :jdbc:oracle:thin:@localhost:1521:dbora
Record length 23
Download 122948 records
Close connection
Record length nnn is the length of each row in bytes.
Download nnnn records is the number of records unloaded.
If you multiply record length for number of records unloaded obtain file size. The record produced will look as follows:
pos 1234|5678901234|5678|9|012|3|
------------------------------------
rec 0516|24/06/2004| G,| | v| |
hex: 3333|3323323333|0042|0|001|0|
hex: 0516|24F06F2004|007C|0|00C|0|
| 
