SQL is returning so many rows that the hash overflows

[singhald]

hi all

my problem is : i m using a informix stage and hash file ,
i m using query to extract the data from database and load into informix stage and populate a hash file . i m using this hash file a lookup in my other job. i m using a huge table as a input to informix stage.

my other job is not working for some records,

i m thinking that it may be the problem of hash file overloading

or it may be Informix ODBC Driver][Informix]Could not open or create a temporary file.

plz give me any hint to overcome this problem

singhal

[ray.wurlod]

You are unlikely to have overloaded the hashed file, otherwise you would have received error messages indicating this fact, and you have failed to mention any. By default a hashed file can contain 2GB of data, and can be adjusted to accommodate much more.

I'd be looking at the Informix end. Exactly how are you asking the ODBC stage to load the table? Set up an ODBC trace to find out exactly what's happening. Determine what temporary file is required, where it is written, and the executing user has write permissions to that folder.

[singhald]

here i m not creating a hash file in disk only using check option "Allow Stage Write Cache". so i think it will not allow more than 128 MB.

i got another message "collection aborted
[Informix][Informix ODBC Driver][Informix]Could not position within a table (informix.tablename).
SQLExecute: Failed to execute statement.

[ArndW]

singhald,

as Ray has already pointed out, hash files tend not to show this type of problem. If you could change your job to write to a sequential stage instead of a hash stage for testing purposes and the error goes away, then we would need to look at the hashed file a bit closer. Could you try that and inform the forum on whether the problem remains or went away?