Specifies an upper bound (in MB) on the amount of heap memory subsequent loads can use.
0 – 2000
0 (zero)
DBA permissions are not required to set this option. Can be set temporary for an individual connection or for the PUBLIC group. Takes effect immediately.
This option specifies an upper bound (in MB) on the amount of heap memory subsequent loads can use. The default setting of 0 means that there is no upper bound, and Sybase IQ can use as much heap memory as necessary to perform the load. A nonzero value means that the user has set an upper bound. The maximum upper bound is 2000MB (2GB). Use the SET OPTION command to adjust the amount of heap memory used by load operations.
The Sybase IQ 15.0 load process has significantly reduced heap memory usage; the LOAD_MEMORY_MB option is primarily for fixed-width loads.
If your system runs out of virtual memory, specify a value less than 2000 and decrease the value until the load works. For insertions into wide tables, you might need to set LOAD_MEMORY_MB to a low value (100-200 MB). If you set the value too low, it may be physically impossible to load the data.
The amount of virtual memory used can become quite large if many columns, such as in a very wide table, are loaded at once. The wider the table, the more the load memory. The more users doing loads, the more heap/load memory is allocated outside IQ.
There are several courses of action you can take if you encounter the following error:
"All available virtual memory has been used ..."
You can set an upper limit on the amount of virtual memory a LOAD command can use by setting LOAD_MEMORY_MB to a non-zero value, with 2000MB the maximum allowed value.
You can also adjust BLOCK FACTOR or BLOCK SIZE LOAD command options. These command options default to 10000 and 500000, respectively, but you can set them to any number. Setting them lower forces the load to use less virtual memory.
You can also resort to loading a subset of the columns at a time, which is referred to as a partial-width load.
