problem with big integer values

dswizard · Post by **dswizard** » Thu Feb 24, 2005 3:10 pm

Hello all

I have a column A in an Oracle table with datatype Number(10). When I import the table definition into one of my datastage jobs, this column A is getting imported as BigInt type and is not accomodating values greater than 2147483647. But I have values greater than this value in database which are seen as 2147483647 when I try to view data in the designer. I tried changing datatypes to Char,varchar,decimal,numeric etc etc but no luck there.Same thing when done in server canvas, column A is getting imported as Decimal and there is no problem there. Any input is greatly appreciated

Thanks
KP

donlank · Post by **donlank** » Thu Feb 24, 2005 3:48 pm

Are you using an Oracle Stage? Or Are you looking at the data after you landed it to Disk and viewing it with a DataSet/Sequential File, etc.?

Thanks,
Kevin

dswizard · Post by **dswizard** » Thu Feb 24, 2005 4:07 pm

Kevin

Actually both !! I tried to view data from Oracle stage and I ran a sample job and dumped the data into a sequential file.In both the cases I see the same magic number.

donlank · Post by **donlank** » Thu Feb 24, 2005 4:15 pm

When preforming the view data and landing from the database, you have tried setting the SQL Type as a Decimal Everywhere and it still views it as a BigInt and lands as BigInt?

I assume, when you run a SQL statement outside of Datastage, you dont get the magic number for those rows?

Thanks,
Kevin

dswizard · Post by **dswizard** » Thu Feb 24, 2005 4:27 pm

Kevin

Yes..outside the datastage I dont see the magic number but it is only in the designer when I try to view data that I get to see this and yes I tried all SQL types in datastage but no luck

Thanks
KP

ray.wurlod · Post by **ray.wurlod** » Thu Feb 24, 2005 8:03 pm

Do a search for 2147483647 - you might learn about handling of integers in DataStage, at least in the server environment (where BigInt is treated as Integer). I can not test whether the same occurs in PX, but it sounds like it is.

dswizard · Post by **dswizard** » Fri Feb 25, 2005 1:47 pm

Ray

Thanks for the reply. I did the search but that did not help much. In server job I dont see any problem but only when I open in parallel canvas , I see this number. I changed the datatypes to decimal 10,0 and even varchar but no luck .

Thanks
KP

ray.wurlod · Post by **ray.wurlod** » Fri Feb 25, 2005 3:09 pm

Final suggestion - go for a bigger data type, for example Decimal(38) or VarChar(255). If that doesn't work, log a support call. I'm not using PX at the moment, so I can't test my theories.