Parallel Extender usage on non-partitioned database

[sathyanveshi]

Hi,

My source database from where I extract the data has no partitions. If I use Parallel Extender for loading tables from source to target, and my source doesn't have partitions on it, then whether the use of Parallel Extender will add any value? I heard some contradictory statements which say that: when we use Parallel Extender on non-paritioned database and allow DataStage to parition, then that would add significant overhead on the DataStage processing.

Anyways, please give me your inputs.

Cheers,
Mohan

[T42]

There are some databases where you can partition on the source (Oracle, DB2...), but the advantage of PX lies in the transformation stage. PX is capable of breaking up the data into good set of partitions (as defined by your $APT_CONFIG_FILE), and handle each set individually.

This is why we strongly advise against using massive SQL statements in your source database. Basic SQL statements (where a = b) that minimize data transfer over networks are good, but nesting SQL statements usually do not perform as well as EE on equally-configurated computer systems.

[jyc168]

There are some databases where you can partition on the source (Oracle, DB2...), but the advantage of PX lies in the transformation stage. PX is capable of breaking up the data into good set of partitions (as defined by your $APT_CONFIG_FILE), and handle each set individually.

This is why we strongly advise against using massive SQL statements in your source database. Basic SQL statements (where a = b) that minimize data transfer over networks are good, but nesting SQL statements usually do not perform as well as EE on equally-configurated computer systems.

This is a question to T42, would you please expand a little as to the definition of "massive SQL statement" and "nesting SQL statement" in your reply. I'm not strong on SQL, some example will help me a lot. Thanks in advance.

[ray.wurlod]

Had T42 used the adjectives "simple" and "complex" to describe SQL perhaps his meaning would have been clearer.

He suggests that you use simple, straightfoward SQL statements to extract data, and let DataStage EE worry about the partitioning of those data.