Table name is not supported as bind variable in EXECUTE IMMEDIATE in PL/SQL

In DB2 PL/SQL , it does not support table name as bind variable in EXECUTE IMMEDIATE.

Like following :

create table A (c1 varchar2(100))

DB20000I  The SQL command completed successfully.

create or replace procedure proc1 (p1 varchar2(200),p2 varchar2(200)) IS t_sql varchar2(200); BEGIN t_sql := 'insert into :x values (:y)'; execute immediate t_sql using p1,p2;end

DB20000I  The SQL command completed successfully.

call proc1 ('a','b')
SQL0104N  An unexpected token ":x" was found following "insert into ".
Expected tokens may include:  "<insert_target>".  SQLSTATE=42601

Solution :

Solution is to replace the bind variable with variable name :

create or replace procedure proc1 (p1 varchar2(200),p2 varchar2(200)) IS t_sql varchar2(200); BEGIN t_sql := 'insert into ' || p1 || ' values (:y)'; execute immediate t_sql using p2;end

DB20000I  The SQL command completed successfully.

call proc1 ('a','b')
  Return Status = 0

select * from a
C1
----------------------------------------------------------------------------------------------------
b

  1 record(s) selected.

DEFAULT CLAUSE DOES NOT SUPPORT USER DEFINED FUNCTION in DB2

In DB2 , DEFAULT Clause does not support any user defined function.

Solution would be to define a Before/After trigger (depending upon requirement).
A)    If the requirement is to call the function whenever there is no value provided for that particular column - Then we should go for After trigger.
B)    If the requirement is to call the function every time an insertion happens :- We should go for Before trigger.


SOURCE CODE - EXAMPLE:   

db2 "alter table tabs alter column c2 set with default SYS_GUID()"
DB21034E The command was processed as an SQL statement because it was not a
valid Command Line Processor command. During SQL processing it returned:
SQL0574N DEFAULT value or IDENTITY attribute value is not valid for column
"C2" in table "ADMINISTRATOR.TABS". Reason code: "7". SQLSTATE=42894

SOLUTION :


A) After trigger :
db2 "create or replace trigger t1 after insert on tabs REFERENCING NEW AS n
FOR EACH ROW BEGIN ATOMIC IF n.C2 is null then update tabs SET C2 = SYS_GUID(); END IF ; END"

B)Before trigger :
db2 "create or replace trigger t1 before insert on tabs REFERENCING NEW AS n FOR EACH ROW SET n.ID = SYS_GUID()"

Steps to Upgrade a DB2 9.7 Instance to 10.1 on windows platform

There are two ways to upgrade a v9.7 instance to V10.1 on Windows platform.

1) Before Installing V10.1

Utility db2ckupgrade needs to be run first.
The db2ckupgrade script can be found at   <Image_path>\ESE\image\db2\Windows\utilities in the V10.1 image path.
The command  to be run -

db2ckupgrade [ -e | <dbname> ] -l <location of log file>

Example :

db2ckupgrade -e -l C:\1.log
DBT5508I  The db2ckupgrade utility completed successfully. The database or databases can be upgraded.

Note : If db2ckupgrade fails in above scenario , please refer the log file and do the needful operations on the databases to make db2ckupgrade successful.

Once db2ckupgrade runs clean , stop the instance (db2stop)  and start the DB2 V10.1 installation.

At the time of installation , SELECT the  "Work with Existing" option under ESE section.
After selection, the present instances will be listed. Select the instance that needs to be upgraded.

Above step will upgrade the instance. Then verify it by using db2level command .
Now upgrade the individual databases.

Database Upgrade Step :

$ upgrade db <dbname>


Method 2) If V10.1 is already installed and we need to upgrade the instance.

If we have already installed V10.1 , then to upgrade a v9.7 instance , we just have to go the DB2COPY of V10.1(Generally DB2COPY2) through CLP and run the following db2iupgrade command :

db2iupgrade <instance_needs_to_be_upgraded> /u:<userid_provided during installation>,<password>

Example :
db2iupgrade DB2 /u:db2admin,db2admin

Above step will upgrade the instance. Then verify it by using db2level command .
Now upgrade the individual databases.

Database Upgrade Step :

$ upgrade db <dbname>

The Basic Concept and Usage of DB2_COMPATIBILITY_VECTOR in DB2

DB2_COMPATIBILITY_VECTOR registry variable is set at Instance level , so it will affect all the databases present in the instance including old (which are created without setting ORA) and new ones (created with ORA).
The only difference will be that old databases will not be able to use any functionality which contain VARCHAR2 (And will not have number and date compatibility as well).

Summary : Creation of any PL/SQL object depends on the value of DB2_COMPATIBILITY_VECTOR at that particular moment . It does not matter whether the database is created in ORA or Non ORA.
Usage of varchar2 , number and date compatibility depends on the value of DB2_COMPATIBILITY_VECTOR at the time of database creation . If it was set at the time of db creation , than only database can use these features.

For detailed explanation Please refer the following examples :

Example 1) Please have a close look at step c .

a) I have not set DB2_COMPATIBILITY_VECTOR to ORA . I created a db NONORA . Now If i try to create a table with varchar2 , it fails as expected.

 db2 "create table tt1 (c1 varchar2(100))"
SQL0104N  An unexpected token "varchar2" was found following "reate table TT1
(C1 ".  Expected tokens may include:  "VARCHAR".  SQLSTATE=42601

b) If I try to create a PL/SQL function , it fails.

db2 "create or replace function func1(in1 in int) return integer is begin return 5;end"
SQL0104N  An unexpected token "return integer is" was found following "on
func1(in1 in int)".  Expected tokens may include:  "<func_attribute_list>".
LINE NUMBER=1.  SQLSTATE=42601

c) Now If I set DB2_COMPATIBILITY_VECTOR to ORA , I can create PL/SQL objects in this database as well

db2set DB2_COMPATIBILITY_VECTOR=ORA

db2start

db2 "create or replace function func1(in1 in int) return integer is begin return 5;end"
DB20000I  The SQL command completed successfully.

d) Even after setting DB2_COMPATIBILITY_VECTOR to ORA , if I try to create a table with varchar2 , it fails because database is not created in ORA mode.

db2 "create table tt1 (c1 varchar2(100))"
SQL0104N  An unexpected token "varchar2" was found following "reate table TT1
(C1 ".  Expected tokens may include:  "VARCHAR".  SQLSTATE=42601



Example 2 ) If I have created my database after setting DB2_COMPATIBILITY_VECTOR to ORA. I can create tables with varchar2 at any point of time irrespective of current value of DB2_COMPATIBILITY_VECTOR  .
But If I reset the registry variable  DB2_COMPATIBILITY_VECTOR  , I will not be able to create PL/SQL object in this database as well , even though it is created in ORA mode.

a) I have ORA mode set . I can create PL/SQl object

db2 "create or replace function func1(in1 in int) return integer is begin return 5;end"
DB20000I  The SQL command completed successfully.

b) Now I am resetting  DB2_COMPATIBILITY_VECTOR . Creation of PL/SQL objects in this database will fail as well.

db2 "create or replace function func1(in1 in int) return integer is begin return 5;end"
SQL0104N  An unexpected token "return integer is" was found following "on
func1(in1 in int)".  Expected tokens may include:  "<func_attribute_list>".
LINE NUMBER=1.  SQLSTATE=42601


c) But even after resetting DB2_COMPATIBILITY_VECTOR , I can still able to use varchar2 for this database as these cfg parameters are set at the time of db creation

db2 "create table tt1 (c1 varchar2(100))"
DB20000I  The SQL command completed successfully.

Issue with DATE and TIMESTAMP data type in DB2 Oracle Compatibility Mode

While working with a client , I came across this strange behavior of DB2 in Oracle compatibility mode.
This issue only comes into picture when we are using the regular DB2 date and timestamp semantics in the Oracle compatibility mode.
I'll explain it through examples :
db2 "values current timestamp"

1
--------------------------
2012-06-12-11.26.42.445000

  1 record(s) selected.

As I am in GMT + 5:30 , so the timezone that DB2 shows is :
db2 "values current timezone"

1
--------
  53000.

  1 record(s) selected.
Now , If we want to use these details to get the value of GMT , we would obviously subtract the timezone value from current timestamp.
When we try it :

db2 "values current timestamp - current timezone"

1
--------------------------
1867-05-04-11.26.43.753000

  1 record(s) selected.

Here it gives year as 1867 ? Strange .
Reason : When Oracle Compatibility mode is set , the DATE data type is changed to support applications that use the Oracle DATE data type expecting that the values include time information.
So when the DATE data type is explicitly encountered in SQL statements, it is implicitly mapped to TIMESTAMP(0). As a result of this implicit mapping, messages refer to the TIMESTAMP data type instead of DATE, and any operations that describe data types for columns or routines return TIMESTAMP instead of DATE.
Adding a numeric value to a TIMESTAMP value or subtracting a numeric value from a TIMESTAMP value assumes that the numeric value represents a number of days. The numeric value can have any numeric data type, and any fractional value is considered to be a fractional portion of a day. For example,TIMESTAMP '2008-03-28 12:00:00' + 1.3adds 1 day, 7 hours, and 12 minutes to the TIMESTAMP value, resulting in '2008-03-29 19:12:00'.
Solution : The same thing happened in our case where we subtracted CURRENT TIMEZONE from CURRENT TIMESTAMP.
CURRENT TIMEZONE is giving us result in hours and minutes , but due to ORA mode implicit mapping , DB2 is treating as 53000 days . That's why we are getting year as 1867 in the result set.
So the solution would be to cast CURRENT TIME in hours/minutes :
1) Let's try to cast CURRENT TIMEZONE to hours :

db2 "values current timestamp - (current timezone/10000)hours"

1
--------------------------
2012-06-12-06.26.46.417000

  1 record(s) selected.

Here the result would be improper , as 53000/10000 would give 5.3 , which would be rounded up in 5 hours . So the result would not be proper.

2) Now , let's cast CURRENT TIMEZONE to minutes :
db2 "values current timestamp - ((current timezone/9630)*60)minutes

1
--------------------------
2012-06-12-05.56.49.405000

  1 record(s) selected.

Here we get the correct output.

How Oracle and DB2 differ in RAW data handling

Comparison between RAW and VARCHAR FOR BIT DATA

In Oracle , RAW (255) means , 255 Bytes have been allocated . Which internally means that 510 hex values can be stored ( As one hex value takes 4 bits).

Similarly in DB2 , VARCHAR(255) FOR BIT DATA , means max 255 Bytes will be allocated .

However, there are some difference in the behavior of how BIT Data is handled in DB2 when an insert statement is executed.

While insertion , Oracle treats the input as HEX, the input can be in the range of [0-9],[A-F]). Therefore, a string of length up to 510 can be inserted in Oracle.

On the contrary, DB2 treats the input string as a character string and due to which a string of size more than 255 is not accepted.

DB2 takes the string as character and stores it in hexadecimal format.

Resolution : To make DB2 understand that the input string is a hex string , we need to use HEXTORAW function which will make the string to be stored as HEX.

Please refer following examples that compare Oracle and DB2 for the same inputs:

Example 1 :

This example will show the difference in how DB2 and Orcale deals with it.

Oracle	DB2	Conclusion
create table tab1 (c1 raw(255)); Table created. SQL> insert into tab1 values ('abc'); 1 row created. SQL> select * from tab1   2  ; C1 -------------------------------------------------------------------------------- 0ABC SQL> insert into tab1 values ('abxv'); insert into tab1 values ('abxv')                            * ERROR at line 1: ORA-01465: invalid hex number	db2 create table tabv (c1 varchar(255) for bit data) DB20000I  The SQL command completed successfully. db2 insert into tabv values ('abc') DB20000I  The SQL command completed successfully. db2 select * from tabv C1 --------------------------------- x'616263'   1 record(s) selected. db2 insert into tabv values ('abxv') DB20000I  The SQL command completed successfully. db2 select * from tabv C1 --------------------------------- x'616263' x'61627876'   2 record(s) selected.	DB2 takes the input as char(If it is supplied without any HEXTORAW function or x’) and converts it into HEX values and store it in the column. So varchar(255) will allow upto 255 characters . Oracle treats the values as hex only . So there RAW(255) means , it has been allotted 255 bytes to store hex values . And each hex value contains 4 bits only . So we can store upto 510 hex values.

Note 1 :  In oracle the insertion of value ‘x/v’ failed with the message : Invalid hex number. But DB2 accepts it , because for DB2 , it is a character , whose hex value will be stored in column.

Note 2 : To make DB2 treat it as hex , we’ll have to specify it with HEXTORAW or x’ like following :

db2 insert into tabv values (HEXTORAW('abxv'))

SQL0171N  The data type, length or value of the argument for the parameter in

position "1" of routine "SYSIBM.VARCHAR_BIT_FORMAT" is incorrect. Parameter

name: "character-expression".  SQLSTATE=42815

In the above example, the insertion of ‘xv’ failed because DB2 is treating it as HEX value and reporting invalid hex value.

db2 "insert into tabv values (x'abxv')"

SQL0105N  The string constant beginning with "x'abxv'" is not valid.

SQLSTATE=42604

db2 "insert into tabv values (x'abcc')"

DB20000I  The SQL command completed successfully.

db2 insert into tabv values (HEXTORAW('abcd'))

DB20000I  The SQL command completed successfully.

db2 select * from tabv

C1

---------------------------------

x'ABCC'

x'ABCD'

  2 record(s) selected.

Note 3 : In Oracle , if the specified HEX string is of odd length , it automatically puts 0 at the start to make it even to match the standards .

But in DB2 , the user has to be careful while specifying the string . The string should be of even length as one hexadecimal digit represents 4 bits .

db2 insert into tabv values (HEXTORAW('abc'))

SQL0171N  The data type, length or value of the argument for the parameter in

position "1" of routine "SYSIBM.VARCHAR_BIT_FORMAT" is incorrect. Parameter

name: "character-expression".  SQLSTATE=42815

Now , please refer below examples for better understanding :

Example 2 :

Oracle	DB2	Conclusion
SQL>  create table tab2 (c1 raw(7)); Table created. SQL> insert into tab2 values ('abcabcabc'); 1 row created. SQL> select * from tab2   2  ; C1 -------------- 0ABCABCABC SQL>  insert into tab2 values ('abcdabcdabcdabcd');  insert into tab2 values ('abcdabcdabcdabcd')                           * ERROR at line 1: ORA-12899: value too large for column "CAMP"."TAB2"."C1" (actual: 8, maximum: 7) SQL> insert into tab2 values ('abcdabcdabcdaba'); insert into tab2 values ('abcdabcdabcdaba')                          * ERROR at line 1: ORA-12899: value too large for column "CAMP"."TAB2"."C1" (actual: 8, maximum: 7) SQL> insert into tab2 values ('abcdabcdabcdab'); 1 row created.	db2 create table tabv1 (c1 varchar(7) for bit data) DB20000I  The SQL command completed successfully. db2 insert into tabv1 values ('abxv') DB20000I  The SQL command completed successfully. db2 select * from tabv1 C1 ----------------- x'61627876'   1 record(s) selected. db2 insert into tabv1 values ('abxvaaaa') SQL0433N  Value "abxvaaaa" is too long.  SQLSTATE=22001 db2 insert  into tabv1 values ('abxvaaa') DB20000I  The SQL command completed successfully.	In Oracle , whatever value you enter in a RAW column , it just takes as even (If it is odd string , it adds 0 at the start). In example , we had declared RAW(7) , and we had entered a string of 16 values , then it fails reporting that “Actual length specified is 8 and the limit is 7) It accepts a string upto 14 hex values for RAW(7). In DB2 , if it is declared as varchar(7) , it will take only 7 characters , if specified without HEXTORAW or x’.

Example 3:

Oracle	DB2	Conclusion
SQL> insert into tab1 values (RAWTOHEX('abxv')); 1 row created. SQL> select * from tab1 C1 ---------------------------------- 61627876 SQL> create table tab2 (c1 raw(7)); Table created. SQL> insert into tab2 values (RAWTOHEX('abxvabxv')); insert into tab2 values (RAWTOHEX('abxvabxv'))                          * ERROR at line 1: ORA-12899: value too large for column "CAMP"."TAB2"."C1" (actual: 8, maximum: 7)	db2 insert into tabv values ('abxv') DB20000I  The SQL command completed successfully. db2 select * from tabv C1 --------------------------------- x'61627876'   1 record(s) selected. db2 create table tabv1 (c1 varchar(7) for bit data) DB20000I  The SQL command completed successfully. db2 insert into tabv1 values ('abxvaaaa') SQL0433N  Value "abxvaaaa" is too long.  SQLSTATE=22001	If we want to transfer a varchar data to RAW table , whose characters are actually RAW , so we can insert string of upto RAW(<bytes>) only. This is the actual length. DB2 automatically does a RAWTOHEX conversion while inserting input varchar string to column. In oracle , either you give a hex input or give a varchar through RAWTOHEX.

Example 4 :

Created a table with varchar(3) for bit data . Try inserting with HEXTORAW(6 digits) value . It should succeed . Then enter with 7 digits , it should fail

Oracle	DB2
SQL> create table tab1 (c1 raw(3),c2 int); Table created. SQL> insert into tab1(c1) values (HEXTORAW('000FFF')); 1 row created. SQL> select * from tab1; C1                      C2 ---------------     ------- 000FFF            -	db2 create table tabv2 (c1 varchar(3) for bit data) DB20000I  The SQL command completed successfully. db2 "insert into tabv2 values ('6162')" SQL0433N  Value "6162" is too long.  SQLSTATE=22001 db2 "insert into tabv2 values (HEXTORAW('6162'))" DB20000I  The SQL command completed successfully. db2 select * from tabv2 C1 --------- x'6162'   1 record(s) selected. db2 "insert into tabv2 values (HEXTORAW('61626'))" SQL0171N  The data type, length or value of the argument for the parameter in position "1" of routine "SYSIBM.VARCHAR_BIT_FORMAT" is incorrect. arameter name: "character-expression".  SQLSTATE=42815 db2 "insert into tabv2 values (HEXTORAW('61626264'))" SQL0433N  Value "abbd" is too long.  SQLSTATE=22001

The issue Barbara faced while SEED DATA :

db2 "insert into dvk_sourcing_user.PERMISSION_GROUP values ('00000000000000000000000000000000','Contract > Read','Access this Contract in any manner and view header and party properties.','Contract','2005-06-24 16:48:27.0','2005-06-24 16:48:27.0','d'

,'800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000','System','2005-06-24 16:48:27.0','a7137a035579

4e029f379d2894db85f5','2005-06-24 16:49:24.0','1','Read')

DB21034E  The command was processed as an SQL statement because it was not a

valid Command Line Processor command.  During SQL processing it returned:

SQL0433N  Value "800000000000000000000000000000000000000000000000000000000000"

is too long.  SQLSTATE=22001

Because this column is taking the hex value string as Character String of size 510 , while its own size is 255.

So , if we specify HEXTORAW before insertion , it would work fine .

Like below :

db2 "insert into dvk_sourcing_user.PERMISSION_GROUP values ('00000000000000000000000000000

000','Contract > Read','Access this Contract in any manner and view header and p

arty properties.','Contract','2005-06-24 16:48:27.0','2005-06-24 16:48:27.0','d'

,HEXTORAW('800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'),'System','2005-06-24 16:48:27.0','a7

137a0355794e029f379d2894db85f5','2005-06-24 16:49:24.0','1','Read')

DB20000I  The SQL command completed successfully.

db2 "select PERMISSION_MASK from dvk_sourcing_user.PERMISSION_GROUP"

PERMISSION_MASK

---------------------------------

x'800000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000000'

  1 record(s) selected.

How Load/Import would work in case of RAW data in DB2

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.