Limit on multiple uses of same lookup fileset in a job?

[ShaneMuir]

Hi All

Does anybody know if there is any limit on the number of times a single lookup fileset can be used in the one job, be it an numerical limit (eg 10 times) or a limit enforced by system constraints (eg memory).

I ask as we have a couple of jobs which fail periodically, each time the failure is due to the use of a particular lookup fileset. There is nothing particularly special about this lookup fileset, other than each lookup is performed using a range hence the need for separate lookup stages. The file itself is about 161MB.

The warning we are receiving is as follows:

Code: Select all

Exchange_Rate_Orig_Acct,0: Node node1 could not map table file "H:/Datasets/lookuptable.54628.20160907161854685000.e4p50va" of length 160075336: (12) Not enough space, retrying mmap
Exchange_Rate_Orig_Acct,0: Node node1 could not map table file "H:/Datasets/lookuptable.54628.20160907161854685000.e4p50va" of length 160075336: (12) Not enough space

Searches suggest that this is caused by the temp lookuptable (or in this case the fileset) going beyond the 2GB limit, or running out of space on the drive. In the current case neither of these are true.

The more infuriating thing is that sometimes the job will run, sometimes it will fail once or more in a row and then run successfully. It doesn't seem to care if there are other jobs running or not. (I have monitored it several times to watch disk usage, memory usage etc).

As a result the only thing that I can think is that it is the multiple usage of the lookup fileset. This is reinforced due to a change made in one of the jobs whereby we removed 4 usages of the fileset and the job has not failed since.

Does anybody know of any limits surrounding the usage of the lookup fileset stage - or encountered a similar problem?

[ArndW]

I haven't encountered issues like this. If this is a memory issue, you might see if turning off operator combination makes a difference.

[chulett]

Searches suggest that this is caused by the temp lookuptable (or in this case the fileset) going beyond the 2GB limit, or running out of space on the drive. In the current case neither of these are true.

It is true. The former. Learned this lesson the hard way back in the day when (in our case) we mixed otherwise identical 32 and 64 bit environments and processes failed in the former. The discussions you found are not so much a suggestion as they are an assertion and it's all about 32bit memory addressing and nothing whatsoever to do with disk space in spite of the rather confusing error message. It builds the file just fine but cannot 'map it' to memory. That's where it runs out of 'space'. They are hard as hell to see while monitoring and they are confined to the current job's processes. Meaning, from what I've seen, not affected by other jobs running.

I can't explain why in your case it sometimes works and sometimes doesn't (ours always failed for a certain data set / volume) but perhaps 'auto' partitioning is at play here? Something that would dynamically change how much any given node in your job is processing? I'd also give Arnd's suggestion a try, see if that helps. Sorry, wish I could be of more help but wanted to try to pull you away from chasing any lookup fileset limit issue.