Partitoned Table in server jobs

andru · Post by **andru** » Mon Dec 27, 2004 12:13 am

Hi,

From the search I did on this forum on partition table, I understand that PX jobs can talk to partitioned table concurrently. But what will be the case with the server job. I guess server jobs cannot talk to different partiotions concurrently from a server job. If so, what is the use of having a partitioned table for a server job? If my understanding is wrong, please correct me.

Thanks

ray.wurlod · Post by **ray.wurlod** » Mon Dec 27, 2004 1:31 am

Not even PX jobs can necessarily use partitioned tables properly. They have to be configured correctly (the correct partitioning specified, for example). While IBM shared its partitioning algorithm with Ascential, so that working with DB2 partitioned tables in PX is fairly seamless (if the DB2 partitioning method is chosen!), the same is not true for Oracle.

With server jobs, everything is single threaded, so there's not a great deal of difference in the way that server jobs work with partitioned or non-partitioned tables.

The question, however, is not what is the use of having a partitioned table for a server job? Rather, take on board that, even though the table may be partitioned, this is not an obstacle to working with it using server jobs.

Should you happen to know the partitioning, you could design multi-instance server jobs that each process a subset of the partitions. Typically the table would be one that is partitioned on some attribute of its data. The WHERE clause of the SELECT statement would normally use job parameters as part of its constraint expression in this design.

gotosarath · Post by **gotosarath** » Mon Dec 27, 2004 6:08 am

Hi,

We have used partitioned tables. Our partitions are based on time (yearly partitions to be more precise). We have used multiple instance jobs for loading each of the partition in parallel.

One advantage we got is we have to code only one job (in a generic manner) to load all partions by coding the partition table name as parameter. We are able to load multiple partitions in parallel. How ever this is limited to number of multiple instances you can run at a particular time (usually constarined by numebr of jobs which occupy 100% CPU).

Thanks,
Sarath

Amit Jaiswal · Post by **Amit Jaiswal** » Mon Apr 25, 2005 9:40 pm

Hi Ray,
You are saying that Server Job can not create separate threads to use partitioned table. Then my question is, what is the use of Link Partioner. And whether server jobs are multi-process or multi-threaded. I guess when we use link partioner, we have option to select algorithm for partioning and on that basis it is creating multiple threads out of single process. Please explain this ETL process in DS with respect to processes and threads.
Thanks in advance.
-Amit

ray.wurlod · Post by **ray.wurlod** » Mon Apr 25, 2005 11:09 pm

Welcome aboard! :D

You have misread my post. I did not claim that server jobs can not address partitioned tables. Indeed, I suggested that a multi-instance server job could have instances addressing each partition, if that is what is needed.

On UNIX systems DataStage server jobs are always multi-process; threads are not used (except inside the sorting algorithms). On Windows systems the mixture can be a bit more eclectic, but primarily the mode of operation is still multi-process.

DSXchange