Row out of sequence when it's in order ?

[kaps]

Hi

I am aggregating about 30 million records and get the "row out of sequence" error even though it's in sorted order. Input is seq file.Input is already sorted and the order is mentioned in AGG.

Job desgin is...Input seq file(with filter command to sort the record passing to Aggregator and output seq file). I even removed the filter command and sorted it seprately. but facing the same problem.

When it encounter the second record in the following example it throws the error. Data Type is defined as BIGINT in aggregator.

89843731,x,x,x,x,x,x
241146165,x,x,x,x,x,

When I changed the data type to INTEGER it worked fine.
Does anyone know the reason ?

Thanks

[ray.wurlod]

No idea at all. BigInt and Int are treated as the same in DataStage BASIC. Is any of the values larger than 2^31-1 (which might cause an arithmetic overflow in an Int data type)?

[kaps]

Ray

You are correct. 2^31 -1 is 2147483649 and the value I have is 241146165.

So I believe Bigint is 2^31 and integer is may be more than that.
Does not make sense to me that Integer is holding more value than Big Integer

Thanks

[ray.wurlod]

BigInt and Int are treated as the same in DataStage BASIC.

Treat the column as VarChar.

[jdmiceli]

Quick question, Ray:

If he treats it as a VARCHAR, aren't the numbers going to be ordered lexically as opposed to numerically. To me, it seems that is what the original example looks like.

Bestest!

[chulett]

Yes, they will. When I've played these games, I make sure the string values are all the same length and zero padded to avoid the 'row out of sequence' error.

[jdmiceli]

Great! Thanks for the clarification Craig. That makes perfect sense because as long as the target field is numeric, then the leading zeroes would just drop off during datatype conversion.

Bestest!