DSXchange

Hi ,

I have a simple job that extracts data from db2 database and loads in to sql server. The job design is source drs stage ---> copy stage ---> target drs stage (SQLserver) and from copy stage ---> aggregator --> audit table.(In aggregator iam taking count of source records).

The job is processing with around 6 million records. The source stage is DRS stage and the array size we have put is 5000 and the transaction isolation level is Read Uncommitted.The target DRS stage has properties array size 1000 and transaction size of 10000. Isolation level is Read committed. The job is aborting after processing around 4 million records and the fatal error is
sql_F4108,0: Caught exception from runLocally(): APT_BadAlloc: Heap allocation failed..

So i changed the target DRS stage properties as array size 1 and transaction size 10 .. the job finished with out any warning .. but my concern here is the throughput is just 350 rows/sec. so the job is taking longtime to finish..

I have searched the forum by key word heap allocation failed and many posts says that might be due to virtual memory.. Why the job is aborting when the array size and transaction size are 1000 and 10000 and why not with arraysize 1 and transaction size 10000. what are the recommended settings for the array size and transaction size to put in the target DRS stage not to abort and also to improve the performance of the job.

Please help me to solve this issue .. i have three more jobs failing with the same issue.

Thanks in Advance

The transaction size / commit frequency is not contributing to your heap allocation issue (that is purely array size) so I would either leave transaction size back at 10000 or set it to 0. Commits complicate restarts and are an expensive operation which slow things down when done 'too often' , so I prefer the 'all or nothing' approach that using zero brings.

There's no magic answer for the Array Size as there are too many factors that contribute to any 'optimal' setting - average record width, packet size, network speed, etc and that doesn't include any database settings. You are going to need to experiment with various settings until you find the 'sweet spot' or until it seems to be 'fast enough'... I'd suggest values not quite so high, perhaps 128 or 256. Don't ask me why, but I tend to use a power of two. [shrug]

Thanks Chulett for you reply,

As per my understanding, you dont want me to change the transaction size and isolation level . So transaction size of 10000 is fine right?

Can you suggest what values need to play with the array size..( Not greater than 128 or 256)

That transaction size is "fine" as long as you don't mind how difficult it can make an abort/restart scenario and you understand that they can slow down your processing. For example, at 4M records processed you've commited 400 times in the database. Me, I prefer doing that only once at the end.

As to the array size, all I suggested was 1000 was obviously too large, so try something smaller - 128 or 256 perhaps.

Hi Chulett,

I tried by tweaking the array size and transaction size .

Array size as 128 , Transaction size as 0 --- Job aborted with Heap allocation message.
Array size as 1 and transaction size 50000 -- job aborted with same error.
Array size as 1 and transaction size 10000-- job ran successfully..,

job has 6 million records processing with speed 1042 rows/sec and it is taking 1.30hrs to complete. And also the job has more than 120 columns .. So in this case the packet size is also big.
What would be the optimum array and transaction sizes to eliminate the the error ( Bad heap allocation) and to improve the performance..

Any other suggestions please

HOW big is the row (in bytes, not in columns)? You will find the size of each data type documented in the manual.

Once you know the row size, THEN you can calculate the optimal array size.

pradkumar wrote:Array size as 1 and transaction size 50000 -- job aborted with same error.

This one surprised me, the implication being that transaction size is somehow playing a role in the heap issue... and I really don't see how that could be the case.

Hi,

Ray -- For one job the size of each row is 1100 bytes and for the other the size is 500 bytes.

Chulett-- Sorry i have rechecked again the job by running with the Source DRS stage array size as 1 and target drs stage ( SQL server) with array size as 1 and transaction size as 10000 and 50000... In both cases job finished successfully with the thruput 1070 rows /sec. As you said the array size is playing heap allocation error.

So what would be the optimum array size and transaction size?

I checked the ulimit with user id and below are the details:

Servername> ulimit -a
time(seconds) unlimited
file(blocks) unlimited
data(kbytes) unlimited
stack(kbytes) 8192
coredump(blocks) unlimited
nofiles(descriptors) 1024
vmemory(kbytes) unlimited

Can you please suggest how to avoid this error and to improve the performance.

Thanks in Advance

Hi,

Is this is something i need to do by playing with array size and transaction size or need to ask admin ?
Any suggestions Please..

It's something you are going to need to experiment with to find the 'sweet spot' for your data in your job on your hardware and your configuration. Suggest you do this in a methodical manner and document as you go so you know what works and what doesn't.