My Job is running very slow

ravij · Post by **ravij** » Fri Mar 17, 2006 1:05 am

Hi,

I am running a job with stages
Src(Seqfile)-->LKP-->3 XFM stages-->LCC-->DB2

I selected Round Robin method in Link Partitioner and Sort/Merge method in Link Colletor and sort key is the Key column of Input columns. Update action is "Clear the table, then insert rows" and given DELETE Statement in Before SQL.

I am processing 1 lakh records.
why my is running very slow?

any assistance can be apprecieated.
thanks in advance.

balajisr · Post by **balajisr** » Fri Mar 17, 2006 1:12 am

ravij wrote:Hi,

I am running a job with stages
Src(Seqfile)-->LKP-->3 XFM stages-->LCC-->DB2

I selected Round Robin method in Link Partitioner and Sort/Merge method in Link Colletor and sort key is the Key column of Input columns. Update action is "Clear the table, then insert rows" and given DELETE Statement in Before SQL.

I am processing 1 lakh records.
why my is running very slow?

any assistance can be apprecieated.
thanks in advance.

Is it necessary to use sort/merge collection method?. Why don't you try round robin collection method. Performance may improve. Sort merge collection method is used mostly when hash partition is made.
When you had deleted the table why do you need to clear it again. Just insert into the table.

--Balaji S.R

ArndW · Post by **ArndW** » Fri Mar 17, 2006 1:50 am

Either replace your DB/2 write stage with a sequential file stage and measure your speed to determine if the DB/2 is the bottleneck. Also, what %age CPU do your 3 transform stage show? If they are at less than 30% on a long run with a moderately busy system then perhaps you don't need splitting into 3 processes. If the DB/2 is your bottleneck, dispense with the link collector and have all 3 transforms write out to the database in parallel.

kumar_s · Post by **kumar_s** » Fri Mar 17, 2006 2:40 am

Instead of Delete, why cant you just replace the whole content.

ravij · Post by **ravij** » Fri Mar 17, 2006 3:06 am

Hi Kumar,

thanks for reply.

Instead of Delete, why cant you just replace the whole content.

What does it mean?
I am giving the query in Before SQL. like this

DELETE FROM SCHEMA.TABLENAME

thanks in advance.

kumar_s · Post by **kumar_s** » Fri Mar 17, 2006 3:37 am

ravij wrote:Hi Kumar,

thanks for reply.

Instead of Delete, why cant you just replace the whole content.

What does it mean?
I am giving the query in Before SQL. like this

DELETE FROM SCHEMA.TABLENAME

thanks in advance.

As mentioned in other post, Delete uses the transaction log. Where as if you use replace it by pass the log files.
Another suggestion, 'Truncate' is far better that 'Delete' without condition.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 17, 2006 6:07 am

Do you really need the Link Collector? DB2 will be quite happy with three inputs, so long as the sets of keys in each set are disjoint (so that there is no contention for locks). You may see substantial throughput gain as well.

kumar_s · Post by **kumar_s** » Fri Mar 17, 2006 6:28 am

ray.wurlod wrote:Do you really need the Link Collector? DB2 will be quite happy with three inputs, so long as the sets of keys in each set are disjoint (so that there is no contention for locks). You may see substantial throughput gain as well.

If you opt the option suggested by Ray, Replace may not be a wise idea.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 17, 2006 6:34 am

With disjoint sets of keys Replace ought not to be a problem.

kumar_s · Post by **kumar_s** » Fri Mar 17, 2006 6:36 am

ray.wurlod wrote:With disjoint sets of keys Replace ought not to be a problem.

Load - Replace option replace table totaly with the input data, irrespective of the presence/absence of the key in the input.

ray.wurlod · Post by **ray.wurlod** » Fri Mar 17, 2006 6:38 am

Ah, yes, I was thinking of the ODBC replace (delete before insert).

kcbland · Post by **kcbland** » Fri Mar 17, 2006 7:33 am

The first question to be answered is always: what else is running on the DS server?

Second is: what is running on the database server?

Third is: have you looked at your job processes cpu utilization?

Fourth is: are warning messages spewing into your job log?

Fifth is: what does your load SQL look like (pure inserts versus inserts w/updates versus wildcard updates etc)

ray.wurlod · Post by **ray.wurlod** » Fri Mar 17, 2006 9:50 am

Are you running on an MVS (Mine's Very Slow) system?

DSXchange