No-engine jobs

No-engine jobs are monitoring jobs that does not run in the database engine. No-engine jobs are jobs that run on the dbWatch Server, and typically run a SQL statement and process the result of this statement in javascript.

Example code for different platforms are available here: Example no-engine jobs

The contents are in xml format, and can be uploaded to the dbWatch Server as a file with .xml extension.

We go through the code section by section.
The entire code for the monitoring job is within the properties tags

<properties>

Under this level there are tree tags, comp-query, asproperties and property.

The tag “comp-query” is an optional tag to make sure this file is not read for database instances where its not compatible with. Its is there purely for performance reasons as it helps dbWatch Control Center to not read files that are not relevant for database instances that they are not needed for. As a developer that makes code for internal use this is not required.
The tag:

 <comp-query><![CDATA[.[type='instance' & databasetype='oracle']]]></comp-query>

This will let dbWatch Control Center know that this file is only relevant for objects that are of the type “instance”, and also of the databasetype “oracle”.

The tag “asproperties” sets metadata for this no-engine job. These metadata tags are used for resolving versioning and how this job will appear. Name is the name of the job, but not the name it will show up as in the “Configure jobs” view, this is “display-name” in the main property definition. Version will be used to determine which job will be used, so if you update with a new job, this needs to have a higher version number. Company should match the domain name for your license. Group should match your domain name in reverse notation + .job, as this is used in grouping files internally. If your domain is “example.com”, company should be example.com and group should be “com.example.job”. The tag “artifactid” must be unique to this job, and typically follow the naming convention “databaseplatform_noshema_name_of_job”.

Example:

<asproperties>
		<name>Oracle Simple Query</name>
		<version>1.0</version>
		<company>company.com</company>
		<group>com.company.job</group>
		<artifactid>oracle_noschema_simple_query</artifactid>
</asproperties>

The tag “property” consists of the bulk of the no-schema job, and the property tag itself sets an internal name for the job. This should not conflict with any other FDL property used, so its naming convention is “_job_noschema_name_of_job”, but must be unique to this job. At the end there is a closing xml tag.

<property name="_job_noschema_simple_query">

There are many tags defined under the property tag. The first tags defines mandatory keys for status (alarm, warning, ok) and details (short information about status) that is used in the monitoring gui. They can be reused as-is.
bc.
details

The “display-name” tag is the name this monitoring job will have in the “Configure jobs” and Monitoring gui. It is what the end user will see as the name of the monitoring job.

<display-name>Simple Query</display-name>

This will name this job “Simple Query”.

The “description” tag is the description about this job that will be visible in the “Configure jobs” interface when you choose that monitoring jobs to install. It is a short description on what this monitoring job does, that can say a bit more than its name.

<description>Doing a simple query</description>

The “category” tag will group this job in a category. Typical categories are Availability, Capacity, Maintenance and Performance, but this can be something new, like an application name or the name of a company, to make it easier to spot. The “Maintenance” category is given special treatment, however, as the jobs defined in this category will have no timeout, so its intended for jobs that will not run quickly, and have their own internal control of when they should stop to run. Most maintenance jobs have a configured time-window where they will stop themselves if they run for too long.

 <category>Availability</category>

The “parameters” tags are the configurable parameters that you get when you right-click and choose “Configure” on a job. Their “name” will be the name of the automatically declared variable in the javascript routine (so no spaces). The “default” is the default configured setting. The “type” is the javascript datatype, such as int, bigint, string etc, and “description” is the description of what this parameter adjusts that will be shown to the end user when they choose to “Configure” a job.

<parameters>
			<parameter name="warning_threshold" default="20" type="int" description="Number threshold for warning"/>
			<parameter name="alarm_threshold" default="5" type="int" description="Number threshold for alarm"/>
</parameters>

The “installable” tags are used to determine if this job can be installed on a specific instance. The key must be set to the “property name”. The “compatibility” is a FDL tag to what kind of databasetype or other FDL tags this job is compatible with. The “instance” tag here is only true on Oracle RAC type systems where we should have one installed per instance in a cluster.

<installable> 
			<key>_job_noschema_simple_query</key>
			<compatability><![CDATA[.[databasetype='oracle'] ]]></compatability>
			<instance>false</instance>
</installable>

The next “compatibility” tag is for when this should run, which is only when its installed, so only when this job is installed on an instance.

<compatability>.[source like "_job_noschema_simple_query"]/instance</compatability>

The “default-schedule” tag is what its default schedule should be. Here we can have both interval format in s (seconds), m (minutes) or h (houres), as well as the crontab-like format is allowed. To short intervals, so less than 30s should be avoided as it could lead to performance issues.

<default-schedule>1h</default-schedule>

The “entityType” tag will tell the dbWatch Control Center server what type of property this is and can be left as-is.

<entityType>scheduledtask</entityType>

The “value” tags are a water-fall type set of tags, where tags furter down in the file can inherit data from the previous tag or tags. They are executed as sorted in the file. First value tag is executed first.
The value tag has a engine variable, which tells us what type of engine will evaluate the value contents. Engines are sql (run a SQL statement), fdl (run a FDL query), javascript (run a javascript routine), dbwql (same as fdl) and regex (run a regular expression).
Example SQL:

<value engine="sql"> select 10 from dual
			<instance-resolver>at/instance</instance-resolver>
</value>	

Example javascript:

<value engine="javascript" foreach="row">
			<![CDATA[
            	try {
            		var value=input.get(0).asLong();
					var status=0;
					var msg;			
					if (value > warning_threshold) { 
						status = 1;
						msg="Value more than warning threshold" + value +" ";
					}
					if (value > alarm_threshold) { 
						status = 2;
						msg="Value more than alarm threshold" + value +" ";
					}
					else {
						msg="Value " + value +" ";
					}
					result.setStatus(status, msg);   
                } 
                catch (err) {
                    result.setStatus(2, err.message);                                                            
                }
        	]]>
		</value>

This javascript routine, gets the output from the SQL as “input” and parameters are automatically declared variables. The routine result.setStatus(int,string) will set status value (0=ok,1=warning and 2=alarm) and a string details message.

The “dbwatch-report-template” is the report template for what gets generated when you click on “Details” for a job. Its not mandatory, but follows the same format as engine-jobs and general reports.

<dbwatch-report-template>
			<version>2</version>
			<title>Simple Query</title>
			<description>Simple Query</description>
			<default-schedule>0 * * *</default-schedule>
			<text>Simple Query</text>
			<chapter>
				<type>
				</type>
				<title>Details</title>
				<text>
				</text>
				<presentations>
					<presentation>
						<resultset>
							<column>
								<name>Details</name>
								<sql-type>0</sql-type>
							</column>
						</resultset>
						<table>
							<to-plot>Details</to-plot>
						</table>
						<select-call><![CDATA[ select sysdate from dual ]]></select-call>
						<text>
							This table shows just the sysdate
						</text>
						<title>
							Details
						</title>
					</presentation>
				</presentations>
			</chapter>
</dbwatch-report-template>

Full example code for Oracle:

<?xml version='1.0' encoding='utf-8'?>
<properties>
	<comp-query><![CDATA[.[type='instance' & databasetype='oracle']]]></comp-query>
	<asproperties>
		<name>Oracle Simple Query</name>
		<version>1.0</version>
		<company>company.com</company>
		<group>com.company.job</group>
		<artifactid>oracle_noschema_simple_query</artifactid>
	</asproperties>	 
	<property name="_job_noschema_simple_query">
		<key name="status" entityType="status"/>
		<key>details</key>	
		<display-name>Simple Query</display-name>
		<description>Doing a simple query</description>
		<category>Availability</category>
		<parameters>
			<parameter name="warning_threshold" default="20" type="int" description="Number threshold for warning"/>
			<parameter name="alarm_threshold" default="5" type="int" description="Number threshold for alarm"/>
		</parameters>
		<installable> 
			<key>_job_noschema_simple_query</key>
			<compatability><![CDATA[.[databasetype='oracle'] ]]></compatability>
			<instance>false</instance>
		</installable>
		<compatability>.[source like "_job_noschema_simple_query"]/instance</compatability>
		<default-schedule>1h</default-schedule>
		<entityType>scheduledtask</entityType>
		<value engine="sql"> select 10 from dual
			<instance-resolver>at/instance</instance-resolver>
		</value>		
		<value engine="javascript" foreach="row">
			<![CDATA[
            	try {
            		var value=input.get(0).asLong();
					var status=0;
					var msg;			
					if (value > warning_threshold) { 
						status = 1;
						msg="Value more than warning threshold" + value +" ";
					}
					if (value > alarm_threshold) { 
						status = 2;
						msg="Value more than alarm threshold" + value +" ";
					}
					else {
						msg="Value " + value +" ";
					}
					result.setStatus(status, msg);   
                } 
                catch (err) {
                    result.setStatus(2, err.message);                                                            
                }
        	]]>
		</value>
		<dbwatch-report-template>
			<version>2</version>
			<title>Simple Query</title>
			<description>Simple Query</description>
			<default-schedule>0 * * *</default-schedule>
			<text>Simple Query</text>
			<chapter>
				<type>
				</type>
				<title>Details</title>
				<text>
				</text>
				<presentations>
					<presentation>
						<resultset>
							<column>
								<name>Details</name>
								<sql-type>0</sql-type>
							</column>
						</resultset>
						<table>
							<to-plot>Details</to-plot>
						</table>
						<select-call><![CDATA[ select sysdate from dual ]]></select-call>
						<text>
							This table shows just the sysdate
						</text>
						<title>
							Details
						</title>
					</presentation>
				</presentations>
			</chapter>
		</dbwatch-report-template>
	</property>
</properties>

Feedback

Was this helpful?

Yes No
You indicated this topic was not helpful to you ...
Could you please leave a comment telling us why? Thank you!
Thanks for your feedback.

Post your comment on this topic.

Post Comment