Inconsistency in data loaded to DB2



IBM's flagship relational database management system

Inconsistency in data loaded to DB2

Postby tsharsri » Tue Oct 05, 2010 4:49 am

Hi,
I am loading a file to a DB2 table using load utility. I have a new column included to the table. While loading the table, this column is loaded incorrectly. All 0's in the input file is loaded as 15 and all 2's as 47 and so on. Field in file is Pic 9(3) Comp-3 and that in table is SMALLINT not null with default. Rest of the columns all loaded correctly. Can someone explain why is there a inconsistency in this load?

Field in Input file:

2/P
(778-779)
355------------
************-CAPS
0
0
0
2
0
57
38
8

Field in DB2:

SMALLINT
--------
*********
15
15
15
47
15
1407
911
143

Thanks,
Sharmila
tsharsri
 
Posts: 3
Joined: Mon Oct 04, 2010 9:21 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Inconsistency in data loaded to DB2

 

Re: Inconsistency in data loaded to DB2

Postby dick scherrer » Tue Oct 05, 2010 6:50 am

Hello and welcome to the forum,

Suggest you look at the input data using HEX ON.

There is no inconsistency that i can see. . .

x'0F' = decimal 15

x'8F' = decimal 143. . .

The load needs to use consistent data types.
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Inconsistency in data loaded to DB2

Postby tsharsri » Tue Oct 05, 2010 10:46 pm

Thanks D.Sch for the clarification. As of now i have unpacked the data in the input file and loaded the table. Will get back if there are any issues in the load.

Regards,
Sharmila
tsharsri
 
Posts: 3
Joined: Mon Oct 04, 2010 9:21 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Inconsistency in data loaded to DB2

Postby dick scherrer » Tue Oct 05, 2010 11:01 pm

Good luck :)

d
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times

Re: Inconsistency in data loaded to DB2

Postby tsharsri » Wed Oct 06, 2010 10:14 pm

Hi,

Changing the input field to Pic 9(3) did not work either. It gave a data conversion error during the load. Then changed the pic to S9(3) COMP and it worked. The data is now loaded correctly.

Thanks,
Sharmila
tsharsri
 
Posts: 3
Joined: Mon Oct 04, 2010 9:21 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Inconsistency in data loaded to DB2

Postby dick scherrer » Thu Oct 07, 2010 1:05 am

Hello,

Then changed the pic to S9(3) COMP and it worked. The data is now loaded correctly.

Yup:
The load needs to use consistent data types.


Good to hear it is now resolved - thank you for the update :)

d
User avatar
dick scherrer
Global moderator
 
Posts: 6304
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 91 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post