IDS - Running temp dbspaces on RAMDISK
Phil Torkington asked about using RAMDISK for IDS temp dbspaces. Superboer said it was not supported and to keep real temp dbspaces since they are lost on reboot. eriegger suggested raising DS_NONPDQ_QUERY_MEM. david said the temp dbspaces need recreating at startup and suggested using temp tables with an index and update statistics. No definitive answer.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Hi All, Does anyone have any recommendations / bad experiences / general opinion on using RAMDISK for temp dbspaces ? Over 90% of our read & write disk activity is on 20 temp dbspaces (approx. 30-35GB) but we are in the fortunate position of having that much spare RAM on the box. So we kind of figure this could maybe help ;-) Phil Torkington
ahum not supported... if you do decide to use it; make sure that you have a couple of real temdbspaces configured. if the machine reboots your ramdisk tempspaces are gone and fast recovery may need tempspace in order to do things. Superboer topdeck schreef: > Hi All, > > Does anyone have any recommendations / bad experiences / general > opinion on using RAMDISK for temp dbspaces ? > > Over 90% of our read & write disk activity is on 20 temp dbspaces > (approx. 30-35GB) but we are in the fortunate position of having that > much spare RAM on the box. So we kind of figure this could maybe help > ;-) > > Phil Torkington
Another try could be to increase DS_NONPDQ_QUERY_MEM significatntly:
Must be >= 128 and <= 0.25 * DS_TOTAL_MEMORY
DS_NONPDQ_QUERY_MEM 65536 # Non PDQ query memory (Kbytes)$ onmode -wm DS_NONPDQ_QUERY_MEM=65536 (IDS 10)Could decrease IO into temp spaces.
and you will need to recreate the temp dbspaces on startup... you might not be able to drop them if they are "corrupt" since the underlying file has gone.. Check the application usage of temp tables no rows /columns you do not need. It may be faster to - get the first n tables in the query into temp table 1 - build an index on temp table 1 - update statistics high on temp table 1 - run a second query joining temp table 1 to the rest of the tables. This allow IDS to elimiate rows earlier in the query and partially forces it to use a given query plan... and make sure it is using the fright indexes..