data stage job running more time.

[suresh_dsx]

Hi,

We have job delete data in 5 tables.
Db2 stage type is DB2UDBAPI.

Code: Select all

                             |------------------------>targer table1
                             |------------------------>targer table2
                             |
Source table ----->copystage------>targer table3
                             |
                             |------------------------>targer table4
                             |------------------------>targer table5
                             

The source data count 600000 records. the job is running 15 hours to complete in sit environment.

the same job completed in the development environment is 3 hours.

As we are taking the help of datasage admin and DBA to moniter the process why it is taking lot of time. they said it is not occupying not that many resources. they are unable to figure out the root cause.

i have searched the fourms realted but dod not find the similar issue.

Any help greatly appriciated.
thanks -suresh

[ray.wurlod]

What's different between the two environments?

Don't answer "nothing" because that's clearly not the case.

For example, are the tables indexed on the key in dev and not in sit? Or is the database local to dev and remote from sit? There are several other possibilities.

[suresh_dsx]

Thanks ray for the swift responce.

First check we have done indexes on the tables. both are in sync.
second data stage sctrach space. we have enough space.
third check compared two jobs any change in the jobs(different environments). jobs are same.


Any other check which we dont have access to the admin side to monitor the job.

thanks Suresh

[suresh_dsx]

Now the jobs is running avarage with 10records per sec.

DBA happy with the indexes.
datastage admin-enough space in the temp area.

source data which is having 400000 records completed with in one hour.
when we have 500000 records running 15 hrs.

any checks need to request DBA /admin on this issue.

any help greatly appriciated.
Thanks -Suresh

[PaulVL]

Are all of the 5 tables on the same server / database?

[suresh_dsx]

yes all the tables are in same database schema and same server

[mail2krishna79]

Try recreating the same job because some times internal functions may not work properly while deleting and placing new stages. this might help

[mail2krishna79]

Try recreating the same job because some times internal functions may not work properly while deleting and placing new stages. this might help

[jwiles]

What other differences may there be between the two systems? Hardware? Physical location? Are the network connections different on SIT and DEV? Perform a traceroute and ping from each DS server to each database server (sources and targets).

Also look at the DB2 client configuration on each DS server.

What about how you're running the job? Degree of parallelism on SIT vs DEV?

What kind of throughput do you get from the source table on dev and sit? Create a job which just reads the table and dumps to a peek or copy and see what the difference in runtime is on the two environments. If you can do so with the targets, run a job that is a row generator going into the DB2 stage and write to a temporary table on each of the target database servers.

This above is just a list of things to check in the process of elimination.

Regards,

[greggknight]

I am assuming you have watched this job run from director and are not receiving a warning message for each row processed.

Have you confirmed that the records are actually going in the tables.
600000 rows to a DB2 should be less then 5 min. It is on my system anyway

[DSguru2B]

A lot of factors can play into database deletes. The tables primary key may be foreign keys in several different tables. It has to go check for the existence of child before actually deleting the record. Then depending upon the constraint definition, it may be delete cascade or do nothing.
If this is zos DB2 then it also depends upon how much load is on the cpu, if zip processors are involved etc.
So depending upon everything that goes around under the covers the normal time may be 5 mins or may be 5 hours.

[mantums]

Do some RND

Take single source and load to the Single target table, check the same in both the enviroment (sit and dev)

Source table ---------> Target table1

Rgds
Mahantesh

[Bicchu]

You can provided values based on your environment in Array Size and Transaction Size in the DB2 API target stage in order to make your job faster.

I once faced such situation and by providing correct value as per my environment, my jobs ran faster.

[SURA]

Write it in Dataset and from there to table.