Infrastructure software like RDBMS often tends to become feature-rich in many directions.
MaxDB is no exception to this, so by reading the documentation there’s a pretty good chance to dig out some features that are rarely seen or used.
One example for this is the MaxDB database event dispatcher.
It has been around for quite a while now but hadn’t been used in the NetWeaver scenario.
It has got no frontend and the documentation for it is – let’s say it is a bit “skinny” …
Anyhow, it’s still a piece of MaxDB software that is available on all installations starting with 7.6.
Let’s see how it works in a few easy steps!
Events – what are they?
The first thing to learn is obvious: what is meant by “database event”?
For MaxDB these are certain, predefined (a.k.a. you cannot change them yourself, they are hard-wired!) runtime situations of a database instance.
For example, the startup of a database instance would be such an event.
Or the completion of a log segment. Or the successful creation of a
backup.
There’s a bunch of those events defined in the MaxDB kernel.
Once the situation occurs, the MaxDB kernel basically puts a message about this event to a message queue.
To get a list of what events are available, simply run ‘event_list’ in DBMCLI:
dbmcli on db760>event_list
OK
Name Priority Value Description
DBFILLINGABOVELIMIT LOW 70 Filling level of the data area exceeds the given percentage
DBFILLINGABOVELIMIT MEDIUM 80 Filling level of the data area exceeds the given percentage
DBFILLINGABOVELIMIT MEDIUM 85 Filling level of the data area exceeds the given percentage
DBFILLINGABOVELIMIT HIGH 90 Filling level of the data area exceeds the given percentage
DBFILLINGABOVELIMIT HIGH 95 Filling level of the data area exceeds the given percentage
DBFILLINGABOVELIMIT HIGH 96 Filling level of the data area exceeds the given percentage
DBFILLINGABOVELIMIT HIGH 97 Filling level of the data area exceeds the given percentage
DBFILLINGABOVELIMIT HIGH 98 Filling level of the data area exceeds the given percentage
DBFILLINGABOVELIMIT HIGH 99 Filling level of the data area exceeds the given percentage
DBFILLINGBELOWLIMIT LOW 70 Filling level of the data area has fallen short of the given percentage
DBFILLINGBELOWLIMIT LOW 80 Filling level of the data area has fallen short of the given percentage
DBFILLINGBELOWLIMIT LOW 85 Filling level of the data area has fallen short of the given percentage
DBFILLINGBELOWLIMIT LOW 90 Filling level of the data area has fallen short of the given percentage
DBFILLINGBELOWLIMIT LOW 95 Filling level of the data area has fallen short of the given percentage
LOGABOVELIMIT LOW 50 Filling of the log area exceeds the given percentage
LOGABOVELIMIT HIGH 66 Filling of the log area exceeds the given percentage
LOGABOVELIMIT LOW 75 Filling of the log area exceeds the given percentage
LOGABOVELIMIT MEDIUM 90 Filling of the log area exceeds the given percentage
LOGABOVELIMIT HIGH 94 Filling of the log area exceeds the given percentage
LOGABOVELIMIT MEDIUM 95 Filling of the log area exceeds the given percentage
LOGABOVELIMIT HIGH 96 Filling of the log area exceeds the given percentage
LOGABOVELIMIT HIGH 97 Filling of the log area exceeds the given percentage
LOGABOVELIMIT HIGH 98 Filling of the log area exceeds the given percentage
LOGABOVELIMIT HIGH 99 Filling of the log area exceeds the given percentage
AUTOSAVE LOW The state of the automatic log backup process has changed.
BACKUPRESULT LOW THIS FEATURE IS NOT YET IMPLEMENTED.
CHECKDATA LOW The event CHECKDATA is always transmitted when the database check using CHECK DATA or CHECK DATA WITH UPDATE is completed.
EVENT LOW An event was switched on or off
ADMIN LOW Operational state was changed to ADMIN
ONLINE LOW Operational state was changed to ONLINE
UPDSTATWANTED LOW At least one table needs new optimizer statistics
OUTOFSESSIONS HIGH Maximum number of parallel sessions is running
ERROR HIGH A error occurred which has been written to database diagnostic message file.
SYSTEMERROR HIGH A severe system error occured, see knldiag.err
DATABASEFULL LOW The event DATABASEFULL is transmitted at regular intervals when the data area is filled to 100 percent.
LOGFULL LOW The log area is full and has to be saved.
LOGSEGMENTFULL LOW One log segment is full and can be saved
STANDBY LOW Operational state was changed to STANDBYWith the command ‘event_list_categories’ a description of the events can be displayed, e.g.:
[...] AUTOSAVE AUTOSAVE events give information about the state of the automatic log backup and are triggered by changes of this state. The events of category AUTOSAVE are active by default. An actual event of category AUTOSAVE contains usable information within the following data fields: PRIORITY: This data field contains the priority of the event. The following value can occur: LOW VALUE1: This data field contains the reason that triggered the event. The following values can occur: 0, The automatic log backup task has been started. 1, The automatic log backup task has been stopped. 2, Automatic log backup has been enabled. 3, Automatic log backup has been disabled. 4, A log backup was successfully finished. TEXT: If data field VALUE1 has the value 1 or 4, data field TEXT contains the file name of the log backup medium that is used by the automatic log backup. Otherwise data field TEXT contains no information. [...]
ATTENTION: the names and parameters of events changed between version 7.6 and 7.7 – so be sure to check the current event names for the MaxDB release you are using!
Now there needs to be somebody taking the event-messages (you can also call them notifications) out of the queue and react to them.
That’s what the event dispatcher is for.
The event dispatcher
With MaxDB 7.6 the event dispatcher is a separate executable that needs to be started via command line. In versions >= 7.7 this event dispatcher has been built-in to the DBMServer.
To allow the event dispatcher to react to events, the reaction has to be defined by the user.
This configuration is also done via the event dispatcher executable (7.6) or the DBMServer-client program DBMCLI (=>7.7).
The executable can be found in the version dependent path:
/sapdb//db/bin/dbmevtdisp.exe
Just calling this executable produces a short usage list:
add <cfgFile> Name == <value> [Priority == (LOW|MEDIUM|HIGH)] [Value1 (==|>=|<=|>|<) <value>] [Value2 (==|>=|<=|>|<) <value>] Command == <command> delete <entryID> <cfgFile> list <cfgFile> start [-remoteaccess] <cfgFile> -l <logFile> -d <dbName> (-u <user,pwd>|-U <userkey>) [-n <node> [-e SSL]] state -d <dbName> (-u <user,pwd>|-U <userkey>) [-n <node> [-e SSL]] stop <instanceID> -d <dbName> (-u <user,pwd>|-U <userkey>) [-n <node> [-e SSL]] version
With MaxDB >=7.7 the same set of commands is available via DBMCLI:
dbmcli on db770>help event OK event_available event_create_testevent event_delete <event_category> [<value>] event_dispatcher ADD NAME == <event_name> [PRIORITY == <priority>] [VALUE1 (==|>=|<=|>|<) <value1>] [VALUE2 (==|>=|<=|>|<) <value2>] COMMAND == <command> | DELETE <entry_ID> | SHOW | ON | OFF event_list event_list_categories [<event_category>] event_receive event_release event_set <event_category> LOW|MEDIUM|HIGH [<value>] event_wait --- dbmcli on db770>
Defining a reaction to an event
Now let’s create a event reaction that simply writes out a message to a log file when the event occurs.
This information is stored in a configuration file that will be created with the first use of ‘dbmevtdisp.exe’.
To keep things easy, it’s best to store it in the RUNDIRECTORY of the database instance, where all the other configuration and log files are stored anyhow.
In this example this would be “C:\sapdb\data\wrk\DB760” and we’ll call the file just ‘evtdisp.cfg’.
Let’s say there should be an entry to the log file whenever an AUTOSAVE backup was successfully taken.
This is covered by the event “AUTOSAVE” with VALUE1 =”4″ (these VALUEx information are simple additional information about the event).
dbmevtdisp add C:\sapdb\data\wrk\DB760\evtdisp.cfg Name == "AUTOSAVE" Value1 == 4 Command == "C:\\Windows\\System32\\cmd.exe \/q \/c C:\\sapdb\\data\\wrk\\DB760\\myeventscript.cmd $EVTTEXT$"
The whole command must be entered in one line (I inserted the line breaks for readability here) and it’s important to have spaces around the double equal signs (==)!
For the COMMAND part, it’s also necessary to escape slash characters (/ and ) with a backslash ().
That’s the reason for the double backslashes in the example!
Also, make sure that the ‘add’ command is written in lower case.
The command used here should be just a shell script (Windows). To run this, we need to call the shell (CMD.EXE) first and provide the necessary flags /q (= quiet shell action) and /c (=> run the command and exist the shell afterwards).
As a parameter to the script certain event dispatcher runtime variables can be used.
$EVTTEXT$, for example, contains the full path and filename of the log backup that had been created with AUTOSAVE.
A complete list of these variables can be found in the documentation (http://maxdb.sap.com/doc/7_6/9d/0d754252404559e10000000a114b1d/content.htm)
So basically we add an event reaction into the configuration file of our choice for the successful completion of the AUTOSAVE log backup and call a script ‘myeventscript.cmd’ and hand over the logbackup filename as a parameter.
To state that this command syntax is a bit awkward would be fully acknowledged by the author.
What’s missing now is, of course, the script file.
Let’s make it a simple one like this
echo %1 >> C:\sapdb\data\wrk\DB760\myeventscript.log
Start the event dispatcher
Having this in place all we need to do now is to start the event dispatcher:
c:\sapdb\db760\db\bin>dbmevtdisp start C:\sapdb\data\wrk\DB760\evtdisp.cfg -l C:\sapdb\data\wrk\DB760\evtdisp.log -d db760 -U db760ED Event Dispatcher instance 0 running using configuration file C:\sapdb\data\wrk\DB760\evtdisp.cfg event with name DISPINFO:DISPSTART not dispatched (count 0)
Note that I’ve used pre-configured XUSER data (key db760ED) for this, so that I don’t have to specify the logon credentials here.
Anyhow, the connect user can either be the CONTROL or the SUPERDBA user.
Also, with the -l parameter I specified a log file for the event dispatcher where it will keep track of its actions.
… and stop it again
The event dispatcher will now keep the shell open and print out status messages.
Stopping it is NOT possible via CTRL+C, but instead the same executable must be used to send a stop command:
c:\sapdb\db760\db\bin>dbmevtdisp stop 0 -d db760 -U db760ED OK
Note that it’s necessary to provide the correct event dispatcher instance number (0 in this case) to stop the event dispatcher.
It’s possible to have multiple event dispatchers attached to one MaxDB instance – but let’s keep things simple for now!
Test the dispatcher
So, restart the dispatcher and create some events!
c:\sapdb\db760\db\bin>dbmevtdisp start C:\sapdb\data\wrk\DB760\evtdisp.cfg -l C:\sapdb\data\wrk\DB760\evtdisp.log -d db760 -U db760ED Event Dispatcher instance 0 running using configuration file C:\sapdb\data\wrk\DB760\evtdisp.cfg
To trigger some AUTOSAVE events I’m simply using the ‘load_tutorial’ command.
Pretty soon there will be messages like the following in the event dispatcher shell:
[...] event with name AUTOSAVE not dispatched (count 3) Event with name AUTOSAVE dispatched (count 4) event with name AUTOSAVE not dispatched (count 5) event with name LOGSEGMENTFULL not dispatched (count 6) event with name AUTOSAVE not dispatched (count 7) Event with name AUTOSAVE dispatched (count 8) event with name AUTOSAVE not dispatched (count 9) Event with name AUTOSAVE dispatched (count 10) event with name AUTOSAVE not dispatched (count 11) Event with name AUTOSAVE dispatched (count 12) event with name AUTOSAVE not dispatched (count 13) [...]
We see that there are some AUTOSAVE events that are dispatched (these are the ones we created our event reaction for) and some are not dispatched.
The latter are the events that are triggered when the AUTOSAVE action is started (Value1 == 1).
So this is completely OK.
Let’s check the content of the script logfile myeventscript.log:
C:\sapdb\backup\db760log.822 C:\sapdb\backup\db760log.823 C:\sapdb\backup\db760log.824 C:\sapdb\backup\db760log.825 C:\sapdb\backup\db760log.826 C:\sapdb\backup\db760log.827 C:\sapdb\backup\db760log.828 C:\sapdb\backup\db760log.829 C:\sapdb\backup\db760log.830 C:\sapdb\backup\db760log.831 C:\sapdb\backup\db760log.833 C:\sapdb\backup\db760log.834 C:\sapdb\backup\db760log.835 C:\sapdb\backup\db760log.836 C:\sapdb\backup\db760log.837 C:\sapdb\backup\db760log.838 [...]
Well done … !?
As we this this worked pretty well.
You can, of course, makeup more complicated scripts.
E.g. the documentation for MaxDB 7.7 has an example where log files are copied to a different location.
However, it’s NOT advisable to use the event dispatcher for critical database maintenance tasks (like backups).
There is no automatic monitoring for the dispatcher functionality and it’s rather seldom used until now.
For lightweight monitoring or notification tasks, it may nevertheless be a nice feature.
Since this example for MaxDB 7.6 already was quite complex (with many odd details) I leave out the 7.7 implementations for the next blog.