DB2 UDB API Stage

sairamkrish · Post by **sairamkrish** » Fri Nov 24, 2006 3:05 am

Hi,

This is my job design

DB2 API--> Parallel Tx--> Seq file.

I have a parallel job to extract records from a db2 db on OS/390. I use DB2 UDB API stage which is the recommended one for non unix databases like mainframes. My db2 table is partitioned and the first partition contains around 2 million records.When i tried execution my job wit DB2 UDB API stage's execution mode set to sequential..the stage executes withing a single node and pumps out my 2 million records. But when i execute the same job with execution mode set to parallel(my configuration file contains 8 nodes) the no of records extracted by the db2 stage was 8 * 2 million records which means that each node in my 8 node config file has pumped out 2 million each.

My requirement is to make use of parallel execution mode of DB2 API stage with the help of 8 nodes to extract only the existing 2 million from my first partition. Please tell me how?

Thanks
Krish

Nageshsunkoji · Post by **Nageshsunkoji** » Fri Nov 24, 2006 3:27 am

Hi,

Some time back, I have done some testing with DB2 API stage. When you will change the execurion mode from sequentail to Parallel. It will take the Entire partition as Auto partition. So, you will get Numberofnodes * Number of records as a result. Each node will take entire data. This is due to API stage is designed for sequentail flow and not for parallel flow. So, for your requirement its better to choose DB2 EE stage nothing but enterprise stage and this stage is exclusively for Parallel flow.

sairamkrish · Post by **sairamkrish** » Fri Nov 24, 2006 3:37 am

Db2 enterprise stage is not recommended to be used for non unix databases. As i already mentioned my db2 database resides on a OS 390 machine.

Thanks
Krish

Nageshsunkoji · Post by **Nageshsunkoji** » Fri Nov 24, 2006 3:42 am

Hi,
Few more points, I have missed from my last post. If you are intended to use DB2 API stage. Use execution mode is Sequential only and set your parallel configuration file for 8 nodes. While extracting the data it will take in sequentail manner from DB2 API stage and while processing the data it will process on 8 nodes as per your config file. While loading also, if your using the same DB2 API database stage, no problem. Processing is important in the Parallel environment.

sairamkrish · Post by **sairamkrish** » Fri Nov 24, 2006 3:47 am

Processing is already happening in parallel in my 8 nodes, am looking forward to do the extraction of records in parallel. Considerable amount of time could be saved if i could do the extraction of records on multiple nodes. Also the fact that the no of records fetched by the db2 stage in parallel execution is no of nodes * no of records in the partition does'nt make any sense to me.

One more question i would like to add in this regard. If i select all the records from my db2 table which has around 30 partitions, is it possible to tell data stage to use different nodes for extracting different partitions in parallel?

Thanks
Krish

Nageshsunkoji · Post by **Nageshsunkoji** » Fri Nov 24, 2006 3:53 am

sairamkrish wrote:Processing is already happening in parallel in my 8 nodes, am looking forward to do the extraction of records in parallel. Considerable amount of time could be saved if i could do the extraction of records on multiple nodes. Also the fact that the no of records fetched by the db2 stage in parallel execution is no of nodes * no of records in the partition does'nt make any sense to me.

One more question i would like to add in this regard. If i select all the records from my db2 table which has around 30 partitions, is it possible to tell data stage to use different nodes for extracting different partitions in parallel?

Thanks
Krish

Hi Krish,

I don't think so, you will acheive the your requirement of extraction of records on multiple nodes by using DB2 API stage. You should use the DB2 EE stage to acieve this.
Answer for your second question, yes it is possible with DB2 EE stage.

sairamkrish · Post by **sairamkrish** » Tue Nov 28, 2006 4:42 am

Hi,

I got to know from the previous postings by Rhys Jones that it is not possible to connect to Db2 databases on OS 390 machine using DB2 EE stage. Since am looking at doing the extraction of records from different partitions in parallel, Does that mean that it is not possible to extract records in parallel from different partitions from db2 table on OS 390?

Thanks
Krish

Nageshsunkoji · Post by **Nageshsunkoji** » Tue Nov 28, 2006 8:46 am

sairamkrish wrote:Hi,

I got to know from the previous postings by Rhys Jones that it is not possible to connect to Db2 databases on OS 390 machine using DB2 EE stage. Since am looking at doing the extraction of records from different partitions in parallel, Does that mean that it is not possible to extract records in parallel from different partitions from db2 table on OS 390?

Thanks
Krish

Yes,

If you must use a DB2 API stage, then there is no chance of getting the data in a parallel manner. You will get the data always in sequential way in the API stage. I am not sure, whether this process is different or same for OS 390.