How Load/Import would work in case of
RAW data
Case 1 ) Export and
Load/Import between DB2 itself :
If the export and import/load is taking place in DB2 tables
itself then while exporting DB2 exports the data of Varchar(255) for bit data
column as varchar only. Therefore while importing, it takes the input as
varchar and insert it as HEX
For better understanding , please refer the example below :
Example :
A) db2 Create table tabv2 (c1 varchar(3) for bit data, c2
int)
B) db2 “insert into tabv2 values ('ab',5)"
C) db2 “insert into tabv2 values ('abc',9)"
D) db2 select * from tabv2
C1 C2
--------- -----------
x'6162' 5
x'616263' 9
2 record(s)
selected.
E) db2 "export
to data.del of del
select * from tabv2"
SQL3104N The Export
utility is beginning to export data to file "data.del".
SQL3105N The Export
utility has finished exporting "2" rows.
Number of rows exported: 2
F) Now when we look
at the content of the exported file , the data is stored as :
"ab",5
"abc",9
G) When we import
this data back into the table , DB2 will take it as Varchar and will stores it
back in the HEX form.
db2 delete from tabv2
DB20000I The SQL command completed successfully.
db2 "import from data.del of del insert into tabv2"
SQL3109N The utility
is beginning to load data from file "data.del".
SQL3110N The utility
has completed processing. "2"
rows were read from the
input file.
SQL3221W ...Begin
COMMIT WORK. Input Record Count = "2".
SQL3222W ...COMMIT of
any database changes was successful.
SQL3149N
"2" rows were processed from the input file. "2" rows were
successfully inserted into the table. "0" rows were rejected.
Number of rows read
= 2
Number of rows skipped
= 0
Number of rows inserted
= 2
Number of rows updated
= 0
Number of rows rejected
= 0
Number of rows committed
= 2
I) db2 select * from tabv2
C1 C2
--------- -----------
x'6162' 5
x'616263' 9
2 record(s)
selected.
Case 2) When Load/Import
is performed through a Input file of DEL/IXF and we want the Varchar(255) for
bit data column to treat the value as HEX :
This case would require a trigger , which will call HEXTORAW
function to make DB2 treat the data as HEX , before every insert during the
load operation.
Note : The only
reason to define trigger is that , DB2 does not allow to use function during
LOAD operation (Like it does in Insert statement).
To explain , We will refer the same data.del file , which
has content as :
"ab",5
"abc",9
To understand the difference , first we
will again have a look at the scenario without trigger :
A) db2 "import from
data.del of del
insert into tabv2"
SQL3109N The utility is beginning to load data from
file "data.del".
SQL3110N The utility has completed processing. "2" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count =
"2".
SQL3222W ...COMMIT of any database changes was
successful.
SQL3149N "2" rows were processed from the
input file. "2" rows were
successfully inserted into
the table. "0" rows were
rejected.
Number of rows read = 2
Number of rows skipped = 0
Number of rows inserted = 2
Number of rows updated = 0
Number of rows rejected = 0
Number of rows
committed = 2
B) db2 "select * from
tabv2"
C1 C2
--------- -----------
x'616263' 9
x'6162' 5
2 record(s) selected.
Now we will define a trigger and see how
the load behaves:
A) db2 delete from tabv2
DB20000I The SQL command completed successfully.
B) db2 "CREATE OR
REPLACE TRIGGER mytrig1 before insert on tabv2 referencing new as n for each
row set n.c1=HEXTORAW(n.c1)"
DB20000I The SQL command completed successfully.
C) db2 "select * from
tabv2"
C1 C2
--------- -----------
0 record(s) selected.
D) db2 "import from data.del of del insert into tabv2"
SQL3109N The utility is beginning to load data from
file "data.del".
SQL3148W A row from the
input file was not inserted into the table.
SQLCODE
"-723" was returned.
SQL0723N An error occurred in a triggered SQL
statement in trigger
"ADMINISTRATOR.MY". Information returned for the error includes
SQLCODE
"-171", SQLSTATE "42815" and message tokens
"1|SYSIBM.VARCHAR_BIT_FORMAT|character-expr". SQLSTATE=09000
SQL3185W The previous error occurred while processing
data from row "2" of
the input file.
SQL3110N The utility has completed processing. "2" rows were read from the
input file.
SQL3221W ...Begin COMMIT WORK. Input Record Count =
"2".
SQL3222W ...COMMIT of any database changes was
successful.
SQL3149N "2" rows were processed from the
input file. "1" rows were
successfully inserted into
the table. "1" rows were
rejected.
Number of rows read = 2
Number of rows skipped = 0
Number of rows inserted = 1
Number of rows updated = 0
Number of rows rejected = 1
Number of rows
committed = 2
db2 "select * from
tabv2"
C1 C2
--------- -----------
x'AB' 5
1 record(s) selected.
Explanation: Here ,
the data is being treated as HEX now . That’s why the insertion of value ‘ABC’
failed as DB2 does not accept odd number of hex values .
The other row with
‘AB’ got inserted as HEX only.
Caution : if we just want to deal
with Load/Import once , and for individual Insert statement we want to use
HEXTORAW function explicitly (and not trigger) , then we have to be sure that
we drop the trigger once we load the data. ( If we forget to drop the trigger
after load , the insert statement coming next would already have used HEXTORAW
function , and this trigger would again convert that data to HEX)
Therefore, to avoid that situation, I would suggest to
declare a trigger once for this table and then simply make insert statement
without using HEXTORAW ( We can discuss it in detail , if required).
How IDMT
transferred the data from Oracle table to DB2 :
As we know , that IDMT stores the extracted data under
IDMT/migr/data/<schema>_<table_name>
So , I will take the example of user_group table here .
From that load file ecm_user_group.txt , I am picking one
row :
"f77a5ff5e9c64483bbda0b3922828d52"~"Standard
User 21"~~2006-01-09
22:08:53~~"afb5fcdfad09462dba9d70be3c7a9185"~2006-01-09
22:08:53~"c391fe7a022c4ce08cdf809c29a275c7"~2007-03-13
12:50:47~"0"~"0"~"
@ "
"dbd698ed54164863b40a38351da76bdc"~"Standard
User 22"~~2006-01-09
22:09:15~~"afb5fcdfad09462dba9d70be3c7a9185"~2006-01-09
22:09:15~"c391fe7a022c4ce08cdf809c29a275c7"~2007-03-13
12:50:31~"0"~"0"~"
@
"
Here the data for column "USER_ORGANIZATION_MASK"
is extracted as varchar only. Therefore in the export file , it has stored
values for this column as NULLs and @.
In short , this is Case 1 . So we simply import/load the
data into DB2 tables (without defining trigger).
LOAD FROM
"C:\Mydata\sumit\Integration\Emptoris\IBMDataMovementTool\migr\data\ecm_user_group.txt"
OF DEL
MODIFIED BY CODEPAGE=1208 COLDEL~ ANYORDER USEDEFAULTS CHARDEL""
DELPRIORITYCHAR DUMPFILE="C:\Mydata\sumit\Integration\Emptoris\apr\IBMDataMovementTool\migr\dump\ecm_user_group.txt"
METHOD P (1,2,3,4,5,6,7,8,9,10,11,12)
REPLACE INTO
"DVK_SOURCING_USER"."USER_GROUP"
(
"USER_GROUP_ID","USER_GROUP_NAME","USER_GROUP_DESCRIPTION","USER_GROUP_START_DATE","USER_GROUP_END_DATE","CREATED_BY","CREATED_ON","MODIFIED_BY","MODIFIED_ON","IS_USER_GROUP","IS_SYSTEM","USER_ORGANIZATION_MASK")
NONRECOVERABLE INDEXING MODE AUTOSELECT;
Here , after load ,
if I access a row , it shows me the value in HEX :
db2 "select USER_ORGANIZATION_MASK from
dvk_sourcing_user.user_group where
USER_GROUP_ID='f77a5ff5e9c64483bbda0b3922828d52'"
USER_ORGANIZATION_MASK
------------------------------------------------------------------------------------------------------------
x'0000004000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000
00000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'
1 record(s)
selected.
No comments:
Post a Comment