Insertion of a BIGINT value in DB2

[sonalkrishna]

Hi,


I have been trying to insert an 11 digit number having datatype BIGINT(19) in DB2. But instead of the actual value it inserts some random number . Can u suggest a solution for this?

Regards,
Sonal.

[ArndW]

Good morning Sonal,

a bigint (64-bit) has enough "room" for 11-digit integers; so I would ask (a) if you get random results redirecting output to a sequential file and (b) which method and stage are you using to insert into DB/2 and is the bigint(19) the DS definition or the DB/2 definition?

Also, if you have valid values for this column in Db/2 already, can you read it correctly through DataStage?

[Sainath.Srinivasan]

There are some problems noted using BigInt. Try Number / Numeric or Char instead and convert in the insert SQL.

[sonalkrishna]

Hi,

I am trying to insert Bigint in DB2 through UDB DB2 stage. The design is something like this:

Code: Select all

           -----------> Xformer--------->Seq. File
                            |
                            |
                            DB2

The data we are inserting is DB2 stage is further sent to Seq. File. In seq file this 11 digit value comes as actual no. But in DB2 stage inserted value is magic no. which is 2147483647. In the database this field is defined as BigInt.

[Sainath.Srinivasan]

Yep!! As mentioned it is the max size possible in an int value (2 power n - 1). So the resolution is to use number or char.

[sonalkrishna]

What is the value of n here?

Yep!! As mentioned it is the max size possible in an int value (2 power n - 1). So the resolution is to use number or char.

[Sainath.Srinivasan]

31 (max bits used for a 32 bit value)

[sonalkrishna]

Very true Shrinivas.
Size for int is 32 so 2^(32-1) is this magic value i.e. 2147483647.

But BigInt size is 64 Bit. So why this DS is truncating and making it 32 for storing purpose in DB2. In fact in Seq file this value is properly stored and displayed. But in case of Db2 stage it is truncating it and making it to 2^(32-1)

31 (max bits used for a 32 bit value)

[Sainath.Srinivasan]

One thing you may try is to change the 64BIT option in uvconfig file to 1 (true) and regenerate your DS kernel. I am not sure but is worth a try.

Maybe the value is because the result in BigInt becomes -1, which is stored in that way.

[Baldmartyr]

Very true Shrinivas.
Size for int is 32 so 2^(32-1) is this magic value i.e. 2147483647.

Remarkable! That's my old phone number! <i>(214) 748-3647</i>.