· Nov 8, 2022

Howto perform tune table in a readonly database (Message Bank)


searching messages in our Message Bank is quite slow, often runs into timeout.

I wanted to perform a tune table on  Ens_Enterprise_MsgBank.MessageHeader because this apparently has not been done yet - the Tune Table utility shows no entries for selectivity, etc.

I tried

 w $SYSTEM.SQL.Stats.Table.GatherTableStats("""Ens_Enterprise_MsgBank"".MessageHeader")

and got this error message

  Table 'Ens_Enterprise_MsgBank.MessageHeader' is mapped to a readonly datababase. No tuning will be performed.0 ЉpTable 'Ens_Enterprise_MsgBank.MessageHeader' is mapped to a readonly datababase. No tuning will be performed. Q
MESSAGEBANK ?3e^zGatherTableStats+5^%SYSTEM.SQL.Stats.Table.1^1#e^zExecute+15^%Studio.General.1^1#x^zExecute+15^%Studio.General.1^1d^runMtdLow+22^%SYS.BINDSRV^1x^runMtdLow+22^%SYS.BINDSRV^1d^popFrame+218^%SYS.BINDSRV^1 d^ServerLoop+27^%SYS.BINDSRV^2 d^SuperConnect+62^%SYS.DBSRV^1!d^SuperServer+107^%SYS.SERVER^1d^^^0

Can I and if yes how can I perform a tune table on this table in the Message Bank? 

In our other (non-Message Bank) IRIS instances the Tune Table utility (or the command line equivalent) works without an error.



Product version: IRIS 2021.1
$ZV: IRIS for UNIX (SUSE Linux Enterprise Server for x86-64) 2021.1 (Build 215U) Wed Jun 9 2021 09:48:27 EDT
Discussion (6)2
Log in or sign up to continue

Yes, it always behaved like this. Currently, table stats are stored as part of the class definition, based on the idea that they are often kind of static and belong with the application. That holds for certain types of applications, but in many cases it is a little too static and this is especially true for the Message Bank example here, as TuneTable cannot update class definitions for a read-only system class.

We have a project coming up to move the table stats to live with the data rather than keep them in the class definition and hope to bring that to an IRIS release in the course of 2023. In the meantime, the workaround suggested above, marking ENSLIB as writable, running TT and then marking it read-only again is reasonable (though quite a step from an official recommendation). Note that after upgrades, your ENSLIB will be overwritten and your stats will have been lost.

FYI, the last part of this article also touches on this subject