For queries involving sorting and/or joins, FairCom DB SQL may create temporary tables which exist in memory and/or on disk, depending on the amount of data in the temporary table. FairCom DB SQL provides options to optimize these queries with regard to physical memory space available on the server. Tuning these options can greatly enhance performance when dealing with large result sets generating extremely large temporary tables.
An internal memory storage system provides a mechanism for FairCom DB SQL to store data in memory instead of on disk. By using this internal storage system for volatile data such as temporary tables and dynamic indexes, the FairCom DB SQL improves the performance of many queries, such as joins. Depending on the amount of memory available on a system, certain queries may create temporary tables too large to be stored in memory. In these cases, the internal storage system swaps blocks of data to a disk file as necessary. The following variables allow implementations to control the characteristics of how this internal storage system uses memory to create temporary tables.
The TPE_MM_CACHESIZE setting determines how much memory the SQL engine uses for its temporary tables for each SQL client. Increasing this setting increases the amount of temporary table data the FairCom DB SQL stores in memory instead of writing this data to the disk-based swap file. However, note that this memory is allocated for each SQL client, so you should consider how many clients will connect to FairCom DB SQL at any given time and make sure total cache memory doesn't exceed available physical memory on the system.
Up to 4 TB total memory is available in this subsystem.
(-16001):MM No data block
To resolve this error, the TPE_MM_SWAPSIZE limit needs to be increased.
Note: These swap files can be found during an active connection with a filename beginning with ‘M’ followed by a string of random alphanumerics. These files can sometimes be left around after an abnormal server shutdown. They can be safely deleted in such a case.
This keyword can be used to resolve error (-16025):MM sorting error.
Increasing the FairCom DB cache configurations DAT_MEMORY (data cache size) and IDX_MEMORY (index cache size) settings can significantly reduce the number of bytes read from disk while executing a query.
Note: The MM subsystem was replaced with a new architecture beginning with V10.3. See SQL_OPTION USE_MM.