Model Query Language overview

The find() command from the TADDM API accepts a query string, specified using the Model Query Language (MQL). The MQL acts as a filter to limit the selected objects.

MQL uses a SQL-like syntax to specify the model object class or other data sources, their attributes, along with a filter expression.

The syntax of an MQL query is as follows:

SELECT attribute-list FROM data-sources [ WHERE expression ]

Table 1 describes the elements of an MQL query. MQL is not case-sensitive.

Table 1. MQL query elements
Element Description

attribute-list

The value *, or a comma-separated list of attributes of the source ModelObject class. Embedded attributes can also be specified.

An attribute name always starts with a lowercase letter, unless the first and the second letter are both uppercase. Subsequent letters in the attribute name can be uppercase or lowercase. Examples of attribute names include the following:
  • displayName
  • fqdn
  • OSRunning

data-sources

A comma-separated list of model object class names. These classes must be persistent, since you cannot query non-persistent objects.

expression

The filter expression, expressed using the following format:

member-name OP expression [ ... ]

where:

  • Member-name is an attribute of the selected data source, and can include dot separated members (the member classes specified in the query expression must be persistent. You can query the member attributes to get the values that match the query expression).
  • OP is an operator.
  • Expression is a statement that returns a value.

For example:

SELECT * 
   FROM   ComputerSystem 
   WHERE  OSRunning.OSName == 'Linux'

In this case, OSRunning is an OperatingSystem object referenced by a ComputerSystem (which is a persistent object), and OSName is a primitive member.

See Table 2 for a description of operators and associated precedence.

Table 2 describes the MQL operator precedence, with higher values representing greater precedence.

Table 2. MQL Operator Precedence
Token Operator Precedence
or logical OR 1
and logical AND 2
instanceof is instance of 3
== equals 3
!= not equal to 3
> greater than 3
>= greater than or equal 3
< less than 3
<= less than or equal 3
starts-with starts-with 4
ends-with ends-with 4
equals equals 4
not-equals not equals 4
is-null is null 4
is-not-null is not null 4
in in 5
() parentheses 5
exists array contains 5
upper() function 5
lower() function 5
! unary not 5
. dot selection 6
contains contains 3
eval eval 3
MQL does not support the following SQL SELECT operators or features:
  • GROUP BY
  • HAVING
  • DISTINCT
  • nested SELECTs
  • BETWEEN
  • Aggregates
You can specify the logical operator AND as and, AND, or &&, and the logical operator OR as or, OR, or ||. In addition, you must enclose all strings using single quotation marks, for example, 'IBM'.

Joins

MQL supports left inner joins against model objects, as illustrated by the following example:

SELECT Db2Server.* FROM Db2Server, OracleInstance WHERE Db2Server.port == OracleInstance.port

This join returns all Db2Server model objects in cases when the port number of Db2Server and the OracleInstance are equal. MQL does not support combinations of right outer, left outer, full, or cross joins

Limitations

On DB2 version 9.5, the equals and not-equals operators fail when they are run on attributes of the CLOB data type in the database. The following exception is thrown:
com.ibm.db2.jcc.am.SqlSyntaxErrorException: DB2 SQL Error: SQLCODE=-418, SQLSTATE=42610

SELECT statement grammar

The following example shows the grammar of the SELECT statement. See the Javadoc for more details and the latest updates. 

statement := SELECT attribute-list [EXCLUDING attribute-list]
   FROM [ONLY] class_list { WHERE [expression | 
   exists_expr] }
[ FETCH FIRST n { ROW | ROWS } [ ONLY ]] [ ORDER BY order_list ]
attribute_list := attrib {, attrib}* | * 
class_list := domain_class {, domain_class }*  
class := <a model object class> 
exists_expr := exists( array_attrib op value {logical_op array_attrib op value}* ) 
expression := [ attrib op value | attrib post-op | pre-op ( attrib )
   |[ NOT ] IN ( expression [, ...] ){logical_op expression}* 
value := <data value> 
in_expr := [ NOT ] IN ( expression [, expression ... ])
array_attrib := <series of attributes where at least the second to last 
   attribute is an array > 
op := != | == | > | < | >= | <= | contains | starts-with | ends-with | 
   equals | not-equals | instanceof | eval
logical_op := AND | OR | && | || 
post_op := is-null | is-not-null 
pre_op := lower | upper 
attrib := {class .} [<an attribute of a class>{.embedded_attribute} | * ] 
embedded_attribute := [<embedded attribute>{.embedded_attribute} | *] 
domain_class := {domain_list} class
domain_list := domain {, domain}* : 
domain := <the server from which to pull data from, default: local database> 
order_list := attrib [ ASC | ASCENDING | DESC | DESCENDING ] [ , order_list ]

Attributes can contain wildcard characters. Also, all keywords, such as SELECT, FROM, and WHERE are not case-sensitive.

Note: The value can be of the attrib type, when the basic operator op (!=|==|>|<|>=|<=) is used.

Examples

The following example shows an MQL query that filters for computer systems running the Linux® operating system:

SELECT *
   FROM ComputerSystem
   WHERE OSRunning.OSName == 'Linux'

The following query uses the EXISTS operator to query array membership, matching all computers systems that have an interface listening on ibm.com or their netmask set to 255.255.255.0:

SELECT * 
	FROM ComputerSystem 
	WHERE EXISTS (ipInterfaces.ipNetwork.name ends-with '.ibm.com' 
		OR ipInterfaces.ipNetwork.netmask == '255.255.255.0')
The following query selects all computer systems that have the attribute virtual set to true: 
SELECT * 
   FROM ComputerSystem
   WHERE virtual

The following query selects all computer systems that have the attribute virtual set to false:

SELECT * 
   FROM ComputerSystem
   WHERE not virtual
The following query selects all operating systems that have the installed service attribute with the name that contains "Wireless". Since the installed service attribute is available only on the Windows operating system, you must use join.
SELECT OSInstalled 
	FROM ComputerSystem,WindowsOperatingSystem 
	WHERE ComputerSystem.guid==WindowsOperatingSystem.parent.guid 
	AND 
	EXISTS(WindowsOperatingSystem.installedServices.displayName contains 'Wireless')
The following query selects all AppServers with the primarySAP attribute that has a port specified as its value:
SELECT primarySAP.portNumber,displayName 
    FROM AppServer 
    WHERE primarySAP.portNumber==9084
The following query selects all RuntimeProcesses that among their ports have port 1415. The query must use the EXISTS operator because the ports attribute for RuntimeProcess is an array attribute.
SELECT ports.portNumber,displayName 
   FROM RuntimeProcess 
   WHERE EXISTS (ports.portNumber==1415)

MQL queries with NOT EQUAL TO operator (!=)

The queries with NOT EQUAL TO operator do not return results that contain an attribute that is set to NULL because such a result is evaluated to "unknown".
Example
It is often assumed that the number of results that are returned from the following API find command:
./api.sh -u <admin> -p <pass> find --count "select * from ComputerSystem"
equals the sum of the results that are returned from the following two API find commands:
./api.sh -u <admin> -p <pass> find --count "select * from ComputerSystem where manufacturer == 'IBM'"
./api.sh -u <admin> -p <pass> find --count "select * from ComputerSystem where manufacturer != 'IBM'"
However, the manufacturer attribute is set to NULL, therefore it is excluded from the results that are returned from the query that contains NOT EQUAL TO operator.
The queries that contain the NOT EQUAL TO operator can have the following forms:
select * from ComputerSystem where manufacturer != 'IBM'
select * from ComputerSystem where not(manufacturer == 'IBM')
If you want to select all ComputerSystems with manufacturer other than IBM, use the following query:
select * from ComputerSystem where manufacturer != 'IBM' or manufacturer is-null

MQL queries with EVAL operator (XA and XD attributes only)

Many CDM attributes are moved into XML content of the XA or XD attributes. Therefore, MQL syntax supports a new operator eval, which can be added in the where clause. The eval operator enables querying CIs by the values of extended attributes or extended instances.

Note: All MQL queries examples contain escaped quotation marks (\"value\") because it is assumed that the queries are run in the following manner:
./api.sh -u username -p password find "MQL query"
For example, the following query was run to find a computer system with the productID attribute set to 'prod1':
SELECT * FROM ComputerSystem WHERE productID == 'prod1'
The following equivalent query uses the eval operator:
SELECT * FROM ComputerSystem WHERE XA eval '/xml[attribute[@category=\"taddm_global\" and @name=\"productID\"]=\"prod1\"]'
The eval operator can be followed by any valid XPath expression that returns Boolean true or false value to enable the performance of correct SQL filtering by the persistence layer.
More examples
  • Find all ComputerSystems, which have any extended attribute with the val value:
    • MQL:
      SELECT * FROM ComputerSystem WHERE XA eval '/xml[attribute=\"val\"]'
    • SQL:
      SELECT * FROM compsys WHERE xmlexists('$c/xml[attribute="val1"]' passing compsys.xa_x as "c")
  • Find a ComputerSystem with the attr2 extended attribute, which has the category set to Other and value set to two:
    • MQL:
      SELECT * FROM ComputerSystem WHERE XA eval '/xml/attribute[@name=\"attr2\" and @category=\"Other\" and text()=\"two\"]'
    • SQL:
      SELECT * FROM compsys WHERE xmlexists('$c/xml/attribute[@name="attr2" and and @category="Other" and text()="two"]' passing compsys.xa_x as "c")