Using Orchestration Query Language from command line
You can use the essential Orchestration Query Language keywords and syntax to effectively write queries from command line.
You can monitor the HCL Workload Automation production plan environment by using the OQL, which applies to REST API V2. With the OQL, you can write queries for items in your database. Also with the OQL, you can retrieve required results quickly and easily.
An OQL query begins with an expression, which is composed of different conditions, known as queryTerms. A queryTerm is the minimum condition of a OQL construction, for example job_name = test1.
QueryTerms include three main elements: a field, a comparison_operator, and a value. The fields query are case-insensitive. The values are case-sensitive.
The field must begin with a letter.
After the first letter, the field can contain the following characters:
- 0 - 9
- a - z
- A - Z
- _
- -
- .
- Integers, both positive and negative.
- Strings in single quotation marks (').
The following table lists OQL keywords with descriptions.
Keywords | Description |
---|---|
AND | Returns the results that satisfy all the conditions separated by AND. It is case insensitive. |
OR | Returns the results that satisfy at least one of the conditions separated by OR. It is case insensitive. |
( | Opens a priority expression. |
) | Closes a priority expression. |
[ | Opens a list. |
] | Closes a list. |
= | Returns all the elements equal to the specified element. Numbers, strings, or one of these keywords can follow the keyword: true, false, or null. |
!= | Returns all the elements different from the given one. Numbers, strings, or one of these keywords can follow the keyword: true, false, or null. |
<= | Returns all the elements that are less than or equal to the specified keyword. Only numbers or strings can follow. |
>= | Returns all the elements that are greater than or equal to the specified keyword. Only numbers or strings can follow. |
< | Returns all the elements that are less than the specified keyword. Only numbers or strings can follow. |
> | Returns all the elements that are greater than the specified keyword. Only numbers or strings can follow. |
IN | Returns items that the specified list includes. |
NOT IN | Returns items that the specified list does not include. |
LIKE | Returns elements that match the specified pattern. The accepted
characters are as follows:
|
NOT LIKE | Returns elements that do not match the specified pattern. The
accepted characters are as follows:
|
ORDER BY | Orders the query results according to the specified fields. You
can use the keyword at the end of the construction. By default, the
field is sorted in ascending order. The accepted values are as follows:
|
, | Separates values inside a list. |
. | Specifies a subfield of an item. |
true | Represents the Boolean value true . |
false | Represents the Boolean value false . |
null | Represents the null value. |
- AND or OR
- The following example explains how to find a job stream that does not have
the name "accounting" and that contains from 5 to 10 jobs. If this condition
is not met, the query searches for a job stream that contains a job
specified in the following list: ['job1', 'job\'3\'',
'job4'].
(jobStreamName != 'accounting' AND (totalJobs > 5 AND totalJobs <= 10)) OR jobName IN ['job1', 'job\'3\'', 'job4']
- LIKE
- The following example shows how to find all the job streams with names that
start with "test1"::
jobStreamName LIKE 'test1@
- IN or NOT IN
- The following example shows how to find a job stream named "accounting". If
this condition is not met, the query searches for a job stream that contains
a job in the specified list and that is not on the workstations specified in
the previous
list:
JobStreamName = 'accounting' OR jobName IN ['italy','united_kingdom'] AND workstation NOT IN ['wks2', 'wks5']
- ORDER BY
- To put field1 data in ascending order,
field2 data in descending order, and
field3 data in ascending order, you can write a
query as
follows:
field1 = 'x' or field2 = 'y' and field3 = 'z' ORDER BY field1, field2 DESC, field3
- Specify subfields (.)
- The following example uses the period character (.) to specify the
type
subfield of thedependencies
item, and theID
of the predecessor job or job stream:(dependencies.type = EXTERNAL_JOB AND dependencies.jobId = <job_id>) OR (dependencies.type = EXTERNAL_JOBSTREAM AND dependencies.jobstreamId = <js_id>)