DSXchange

Hi All,


This is a production issue and we have a job which generally runs for 4 minutes , but its running for 9-10 hours now . nothing has changed , we did everything we can in the database side but of no use . all the other jobs other than this runs fine.


This job just extrats data from a db2udb table and dumps in another table using API stage.no complex logic e.t.c ,

This job actually joins two tables on the source side one which has 150,000 and the other 1.8 million and does a update on one coluomn in target table.


Can anyone give ur view or help in this situation.

Thanks

Raj

Something must have been changed IMO. Or else, Did you try analyzing the target table? or When was the last time you analyzed this table. What is your target Database( I am guessing its the DB2 since you mention API). What is the Update action set to?If analyzing table(reorgchk command in DB2) doesn't work, post your job design. That might help.

Hi

Thanks for your reply

But we performed a reorg on the database and also performed runstats
on the tables . But of no use.

SELECT
stage.I_DSTG_DIM,
retail.I_RCUS_DIM as I_CUST_DIM

FROM #pUDB_DB2_schema#.#pUDB_DB2_Table01# stage,
#pUDB_DB2_schema#.#pUDB_DB2_Table02# retail

WHERE ( stage.I_CUST_DIM < 0 ) and
stage.I_CUS_PH = retail.I_RCUS_PH and
stage.X_CUS_LINE1 = retail.X_RCUS_LINE1 and
stage.C_CUST_TYPE in ('R','I',' ')

this is what the source stage does and an update on a coloumn is made on the target side , ya its db2udb.

UPDATE #pUDB_DB2_schema#.#pUDB_DB2_Table01# SET I_CUST_DIM=? WHERE I_DSTG_DIM=?;

This is the query and the update action is set to update existiing rows only.

This job is running in our production schedule for th epast 6 months and completes around 5 minutes .

but from yesterday its running like 9 hours.


I higly appreciate any help in this matter.

There may be some uncommited transactions..

I have had such situations in development, which got fixed by doing a commit on the database.

If it does not cause any harm you could try this in your production environment.

Hi Raj..
What is size of the database at the target table.There may be few reasons:
1)If it is huge database table sometimes it may take long time to run the update query.
2)If they are other huge applications(like java...) are running then the server database may go slow down too..

The Target Table ONLY CONTAINS I50,000 RECORDS , IT SHULDNT BE A PROBLEM


Thnaks again for ur replies guys.

Yes true it is not the problem with the table size....

goriparthi wrote:The Target Table ONLY CONTAINS I50,000 RECORDS , IT SHULDNT BE A PROBLEM


Thnaks again for ur replies guys.

Do you have Index for the Where clause field(s)?

Howdy!

I don't remember the syntax but your DBA will know it. Run the DB2 command that will repair a damaged index on the target table. Make sure to hit the primary key/business key/clustered indexes and the like. What is the OS? If Windoze, reboot to reset everything and see if that makes a difference. When you look at the job while it's running, can you tell if it is the extract from source that is slowed down or the update to the target. That should also be checked.

Bestest!

its actuallly extracting nothing almost .

we performed REORG AND RUNSTATS but we will do it again and i will update that.


Thanks

Dump the data into the Sequential file instead of the the db stage and see if it affects the timing (and by what factor).

That way you would be sure of the problematic area to look for, either the source or target.

Is there any chance of any other process having locks on the same table?

You must run to your DBA to check is there any locks happing?

Thanks,
Anupam

check for the server load.

ask your DBA about how many connections have been made to the database.

Ask your admin about how many datastage jobs are running on the server.

these all affect the job performance.

I dont know if Raj could crack down his issue. We faced similar problem when all of a sudden couple of jobs extracting from db2 source starting to hang. Nothing was getting extracted and we had to kill all the processes first to make sure there were no locks and then the jobs started working fine again.

I hope Raj shares his experience with us...

Guys,

Looks like its an issue with the indexes itself on one of the tables i am joining so DBA worked on it. I will let the schedule run once again and confirm the same.

I appreciate all the response from you guys

Thanks

Raj.