While you can write and execute MOCA commands/scripts using MOCA client or run as jobs using the job_definition table (schagt in 2004–2009). However, today I am going to use a batch script to stop an instance using a batch script. If you’re running JDA WMS on Linux, we can use a shell script instead.
In this blog, I am going to explore an aspect of the msql utility. To read about my previous blog on MSQL (Click Here) and integrate it with a programming language.
So I have about 10 or more instances having multiple versions of JDA WMS running on a single VM. I will use a simple technique to see if I haven’t logged into an instance for more than 24 hours, then turn the instance off. Sounds simple, right? Well, you have to be creative about it when you code all if it.
Below is a simple piece of msql statement that I have saved in a file called active.msql under E:\_scripts
publish data where mydiff = 1
|
[select count(*) from sys_audit where exec_cmd like ‘Login succeeded%’ and auddte >=sysdate-@mydiff order by 1 desc]
/
exit
/
All it does is, gives me a count of users who have successfully logged into the MOCA Server in the last 24 hours.
You can execute any MOCA script by using the msql utility in the following format.
This is a neat little trick to use as a cronjob on Linux or Windows Task scheduler to run a job at a specific interval and show the output into a file.
msql -S @E:\_scripts\active.msql > ali_test.txt 2>&1
If you want to append to the text/log file instead of overwriting it, use >> instead of >
So far so good. So now we will write a batch script to take this output and do some action on it.
I’ll use the following batch script (you are welcome to try it in Powershell if you’re more comfortable in it).
@echo off
REM set myvar=211REM echo %myvar% | findstr "[2-9]"
REM msql -S @E:\scripts\active.msqlSETLOCAL ENABLEDELAYEDEXPANSION
SET count=1
FOR /F "tokens=* USEBACKQ" %%F IN (`msql -S @E:\scripts\active.msql`) DO (
SET var!count!=%%F
SET /a count=!count!+1
)
ECHO "var1 is" %var1%
ECHO "var2 is" %var2%
ECHO "var3 is" %var3%
ECHO "var4 is" %var4%
ECHO "var5 is" %var5%
ECHO "var6 is" %var6%
ECHO "var7 is" %var7%
ECHO "var8 is" %var8%
ECHO "var9 is" %var9%if %var7% gtr 0 goto DONOTHING
if %var7% equ 0 goto DOSOMETHING:DOSOMETHING
echo "Doing something"
net stop moca.instancename
pause
exit:DONOTHING
echo "Exiting"
pause
exitENDLOCAL
This may not be the best example, or may not apply to everyone, but still provides a valuable lesson and opens up ideas on how batch/shell scripts can be combined with MOCA command/SQL statements to get the results you want, perhaps if your production MOCA sessions spikes randomly and you want to get to the root cause and would like to run a shell script that runs every 5 minutes and takes a count of users, sessions, status, blocking status (from the v$session view in Oracle) for example can give you a good insight on what is happening on a database level.
I would love to hear if you’re already using something like this or would like to explore the possibility of doing it.
