September 19, 2005 at 7:38 am
Hi Friends,
I have a query that links two huge tables (44+millions vs 65+million records) and two small tables. I want to process the data for the query dividing into subunits like 1st 1 million records and the 2nd million like that. The query will run fine if we specify the limit like top 500000. But cannot perform the whole result since it exceeds the size of tempDB (10 GB). Is there any other solutions other than increasing the size of tempDB?? Is there any way to specify the buffer size for the resultset. I just want to run it once only (migrating to DB2).
FYI : Query is well tuned
Your help is appreciated.
Thanks,
Ragesh Chavarattil
September 19, 2005 at 7:45 am
What transformations do you need to do to that data?
September 19, 2005 at 11:11 pm
I just want to take it out and insert into DB2 table.
September 20, 2005 at 6:41 am
I think you can run that into batch when using bulk insert but I can't help you much more than that, sorry.
September 20, 2005 at 8:26 am
try adding an identity column to each table and use the unique values generated in your "where" clause for each of the large tables.
September 20, 2005 at 10:22 am
For this volume of data I'd suggest good old fashioned bcp to extract the data using the -F (first row) and -L (last row) execution flags. Also since you are going to probably use the DB2 load utility you may have to add a bit of formatting so you'll also need to use the -f (formatfile) option as well. However in order to accomplish this you'll have to create a view from your join. Then you may still have the tempdb size issue. So, since this is a 'one time' conversion it just might be easier to increase the size of tempdb and go with the original plan.
RegardsRudy KomacsarSenior Database Administrator"Ave Caesar! - Morituri te salutamus."
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply