Set tables in memory

Hi, I work with Informix Server and I'm looking for a way to put some tables of much use in memory and I found this syntax in an old document: set table <table> memory_resident When I executed i get: Memory resident status changed But in manuals of 11.70 and 12.10 I didn't found this sintaxis. Is it correct ? Is there any other way to put some tables in memory ? Please provide me any clues according your experience. Thanks in advance.

That feature was depreciated around version 11. Informix handles all the buffer cache management. If you are having issue with a table not staying the cache you probably need to increase your buffer cache.

Hi Kernoal, Thank you for your response. Is there a way of validating the use of buffer cache for a specific table ? I know the onstat -p (bufreads %cached 99.92), but I think this ratio is not real. Thanks. PD. Profile dskreads pagreads bufreads %cached dskwrits pagwrits bufwrits %cached 105267594 414961854 137955903594 99.92 51699105 143049239 2202078132 97.66 isamtot open start read write rewrite delete commit rollbk 212335854822 1253049037 1463012882 113215684976 2130049044 824865784 13520019 25789090 4517 gp_read gp_write gp_rewrt gp_del gp_alloc gp_free gp_curs 0 0 0 0 0 0 0 ovlock ovuserthread ovbuff usercpu syscpu numckpts flushes 0 0 0 636884.68 79948.29 5906 5928 bufwaits lokwaits lockreqs deadlks dltouts ckpwaits compress seqscans 9344495 34315 44187750635 0 0 5568 7058806 30185521 ixda-RA idx-RA da-RA logrec-RA RA-pgsused lchwaits 0 536660374 57825353 2 50332319 23058211

All of the onstat's will show the results from the last time the server was started or last time onstat -z was run. If the server has been up for a long time it may be an average that is skewed. If you want to see those stats at the table/index level use onstat -g ppf and/or there are some sysmaster tables. onstat -g ppf is faster. It sill show you at the partnumber level the activity for each one including the rhitratio: Lester and Art have sysmaster scripts for the same information. onstat -g ppf output truncated:Partition profiles partnum lkrqs lkwts dlks touts isrd iswrt isrwt isdel bfrd bfwrt seqsc rhitratio 0x6 0 0 0 0 1 0 0 0 0 0 0 0 0xa 0 0 0 0 6235 0 0 0 0 0 0 0 0xb 0 0 0 0 0 0 0 0 0 0 0 0 0xc 0 0 0 0 0 0 0 0 0 0 0 0 0xe 0 0 0 0 0 0 0 0 0 0 0 0 I use something like this to see which table is hogging the buffer cache. You can also do this with sysmaster tables, but the onstat -P is faster to load into a temp table. I also truncated the names so the output would fit on the putty screen, very lazy way to do it. ksh script: #!/usr/bin/ksh onstat -P | egrep -v "Buffer pool|partnum|IBM Informix|Percent|Data |Btree |Other |Totals: " | grep . | awk '{print $1"|"$2"|"$3"|"$4"|"$5"|"$6}' >onstatp.unl dbaccess sysmaster <<EOF 2>/dev/null | grep . set isolation to dirty read; create temp table onstatp(partnum integer, total integer, btree integer, data integer, other integer, dirty integer) with no log; load from onstatp.unl insert into onstatp; create unique index idx_onstatp on onstatp(partnum);output to pipe cat select {+INDEX(p,idx_onstatp),AVOID_FULL(systabnames)} first t.dbsname[1,6] dbname, t.tabname[1,12] tabname, left(dbinfo("DBSPACE", t.partnum),9) dbspace, round(i.ti_pagesize/1024,0)::decimal(2,0) psize, -- p.total totalpages, -- p.data datapages, -- p.btree indexpages round(p.total*i.ti_pagesize/1024/1024,2)::decimal(5,0) Ttlmb, round(p.data*i.ti_pagesize/1024/1024,2)::decimal(5,0) Tabmb, round(p.btree*i.ti_pagesize/1024/1024,2)::decimal(5,0) Idxmb from onstatp p, systabnames t, systabinfo i where t.partnum = p.partnum and t.partnum = i.ti_partnum and t.tabname not in ("_temptable","TBLSpace") and round(p.total*i.ti_pagesize/1024/1024,2)::decimal(5,0) > 0 order by i.ti_pagesize desc, Ttlmb desc; EOF rm onstatp.unl -------- To find out how old the stats are or when onstat -z was last ran you can query sysmaster database database sysmaster;select dbinfo('utc_to_datetime', sh_pfclrtime) from sysshmvals; See how the table looks now. Run onstat -z and wait a couple of hours and check them again. That will give you a good idea of the tables that are using a lot of diskio rather than staying cached. If your overall onstat -p show 99.9 % cached that is good.

Roger the command to make a table resident was introduced in v7.30 and removed in 7.31 because it turned out to be a VERY BAD IDEA. It caused more performance problems than it solved so the command was made no-op in 7.31/9.30 and later and finally dropped altogether in 11.50.