Hi
I have a fact table which is going to have around 2.5 billion records in the first load and after that around 20 million records in the subsequent loads. Can anyone give an estimated time it will take to load the 2.5 million records. The Source and target are both in DB2. The mappings are not so complex. I don't want the accurate time but an estimate.
The fact table is located on the AIX/P5 IBM Server. and source and target both are connected through network that means the source and target table are located on two different machines.
Loading 2.5 Billion Records in a Fact Table
Moderators: chulett, rschirm, roy
Re: Loading 2.5 Billion Records in a Fact Table
Histiwari wrote:Hi
I have a fact table which is going to have around 2.5 billion records in the first load and after that around 20 million records in the subsequent loads. Can anyone give an estimated time it will take to load the 2.5 million records. The Source and target are both in DB2. The mappings are not so complex. I don't want the accurate time but an estimate.
The fact table is located on the AIX/P5 IBM Server. and source and target both are connected through network that means the source and target table are located on two different machines.
it is not easy to estimate the time of your load.
We had some years ago the same problem. Our Jobs had to load 3.5 Billion of records every month and the only method to do that quickly was to partion tha target table, split the input data stream to load them separately but in parallel.
Sorry for mu english, I know that it is orrible.
Umberto
To put your question differently, you have just asked "I am driving from New York to Los Angeles. How long will it take me to get there?".
Well, are you using a truck or a Porsche or walking? Are you going cross-country or using roads? The list of questions goes on and on. The same applies to your actual question. Is the DS server heavily loaded, is the DB/2 instance heavily used? Is it partitioned? What extract and what load method are you using? What are the data volumes (i.e. how many bytes per row - one CHAR(1) field or 500 columns of char(1024)?
With a similar configuration I've seen server jobs to ~ 60,000 rows per second using the normal load stage and a bit of tweaking. It would most likely go faster using the bulk loader. This is about the initial speeds you would get with PX for this HW configuration and table layout as well, but that can also be tuned upwards.
The answer that stated you would get 1000 rows/second is (imho) not only wrong but also very slow - even for a heavily loaded system. At that rate it would take over 29 days to load.
Well, are you using a truck or a Porsche or walking? Are you going cross-country or using roads? The list of questions goes on and on. The same applies to your actual question. Is the DS server heavily loaded, is the DB/2 instance heavily used? Is it partitioned? What extract and what load method are you using? What are the data volumes (i.e. how many bytes per row - one CHAR(1) field or 500 columns of char(1024)?
With a similar configuration I've seen server jobs to ~ 60,000 rows per second using the normal load stage and a bit of tweaking. It would most likely go faster using the bulk loader. This is about the initial speeds you would get with PX for this HW configuration and table layout as well, but that can also be tuned upwards.
The answer that stated you would get 1000 rows/second is (imho) not only wrong but also very slow - even for a heavily loaded system. At that rate it would take over 29 days to load.
</a>[quote="stiwari"]Hi Umbix62,
Thanx for the reply. Just one quick question
How much time it took to load 3.5 billion records and i would be more than thank ful if you can tell me what kind of partition you created and how many jobs you ran in parallel to load the data.
Thanks
Sumit[/quote
I have read the ArndW answer and he said exatly what I meant. It's no easy to estimate the time of a load and compare it with other job, devoleped on an other environment and with other tools. It isn't correct for me. Anyway we worked on a IBM Mainframe S/390. The Job was developed in COBOL language and not with datastage. The RDBMS was a IBM/DB2 V5.1 (max degree was 10). The fact table, one table for every month, had 32 partition for each table and we run 10 parallel at time. Every job run in about four/six hour.
I only give you a suggestion, an idea, but my situation was different from your for the reasons explained by ArndW. So the situations aren't comparabile.
Take in consideration it but try to understand if it is the best way to load data more quickly for the reasons exposed by ArndW
Umberto
Thanx for the reply. Just one quick question
How much time it took to load 3.5 billion records and i would be more than thank ful if you can tell me what kind of partition you created and how many jobs you ran in parallel to load the data.
Thanks
Sumit[/quote
I have read the ArndW answer and he said exatly what I meant. It's no easy to estimate the time of a load and compare it with other job, devoleped on an other environment and with other tools. It isn't correct for me. Anyway we worked on a IBM Mainframe S/390. The Job was developed in COBOL language and not with datastage. The RDBMS was a IBM/DB2 V5.1 (max degree was 10). The fact table, one table for every month, had 32 partition for each table and we run 10 parallel at time. Every job run in about four/six hour.
I only give you a suggestion, an idea, but my situation was different from your for the reasons explained by ArndW. So the situations aren't comparabile.
Take in consideration it but try to understand if it is the best way to load data more quickly for the reasons exposed by ArndW
Umberto