Oracle bulk load stage

[SonShe]

I have a job that reads data from a DB2 table in mainframe and loads the data into an oracle table table using SQLLOad stage. There is no transformation involved.

DB2STG----------->Transformer------------------->OCILoadstage

The jobs run at a nice speed at about 3000 rows per second and in about 10-12 minutes the links show the total number records in the source table. However, the count in the links keeps increasing. It alsmost takes another 15 - 20 minutes for the job to complete.

I would like to understand what the job is basically waiting on. Is it the load to the table (I believe so) that is the bottleneck or some datastage parameters that need to be set properly. By the way, I am using the 'Automatic' for the batch mode and the max record number is 1000.

I will appreciate any help.

Thanks,

Bhusan

[ray.wurlod]

The clock keeps running after DataStage invokes sqlldr, even though no more rows are processed within DataStage. The clock only stops when sqlldr reports that it has completed its task, and any after-stage or after-job subroutines have been executed and returned. Use your "active stage finishing" log events to get accurate measures of elapsed time spent processing rows.

[chulett]

I don't understand why you would be seeing the row counts climb over the number you are actually loading into the table. However, there is typically a period of time when the counts 'stop' because the records have been loaded, but Oracle is doing its cleanup work. This typically involves updating / rebuilding / revalidating indexes and constraints.