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

Question

Question

Stefan Schick · Nov 8, 2022

#Databases #Enterprise Message Bank #Message Search #SQL #InterSystems IRIS for Health

Hello,

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.

Regards
Stefan

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

Benjamin De Boe · Nov 10, 2022

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

2 0

score 1 · Answer 1 · 2022-11-08T16:46:46-05:00

I never had this kind of problem, but a quick and dirty method would be: 1) remove the 'Mount Read-only' flag, 2) run the tune table utility, 3) reenable the 'Mount Read-only flag'. I hope, you do not have some mean application, waiting for the chance of his life, to get a writable database...

score 0 · Answer 2 · 2022-11-09T08:46:10-05:00

Hi Julius,

thanks for your answer!

I looked it up and the database MESSAGEBANK is already mounted R/W (what imho totally makes sense). I really don't understand where the problem lies.

Regards
Stefan

score 0 · Answer 3 · 2022-11-09T10:48:16-05:00

I do not work with Ensemble nor do I have Ensemble installed... But you could take a close look on that Ens_Enterprise_MsgBank.MessageHeader class, and check, if there is a mapping into an other, possibly readonly, database. Maybe somebody with ENS experience has a solution for you

score 0 · Answer 4 · 2022-11-09T12:26:33-05:00

this applies to Ens.MessageHeader as well. The code is mapped to EnsLib which is normally mounted read only.

I think Tunetable is trying to update the class definition.

I can't remember if it has always behaved like this

score 0 · Answer 5 · 2022-11-10T07:43:28-05:00

Thanks Benjamin for the detailed explanation and the article!

Now I get it: the class is mapped to a read-only database not the data and tune table tries to update the class definition.

I will try using the workaround suggested by Julius.