Since ages HP ServiceManager developers were forced to build business logic in a quite inefficient way due to the fact that queries involving multiple tables were not possible.
To achieve the same result we had to code what normally the database engine would automatically do for us. This means a lot of subselects and looping through record sets which takes time and
consumes also resources on the database and application server.
Such business logic is causing actually a lot of performance bottlenecks within HP ServiceManager.
We would like to select all CIs in the CMDB not having any downstream or upstream relations.
This query involves the device and cirelationship table. The CMDB device table normally contains quite a high number of records - therefore looping through all device records and checking for
each if there is any related record within the cirelationship table is definitely not an option.
In the past there were only 2 options to achieve this:
- Add a new field to the device table e.g. ciHavingRelations (true/false). Configure a schedule job looping through the CI records and evaluate the flag once per night. Sounds not very
efficient but would theoretically work out
- Create a View on the database directly and offer the view to HPSM by creating a dbdict pointing to the corresponding view. Works out but is quite a lot of work and not very flexible if for
instance a new field is required in the view later on. Furthermore the data within the view can only be used read-only within HPSM - updates against the view are not possible.
var file = new SCFile("device");
var sql = "SELECT ta1.logical.name from device ta1
LEFT OUTER JOIN cirelationship ta2
ON ( ta1.logical.name=ta2.logical.name )
LEFT OUTER JOIN cirelationship ta3 ON ( ta1.logical.name=ta3.related.cis )
WHERE (ta2.logical.name=NULL and ta3.related.cis=NULL)";
var count = file.doCount(sql);
There we go - This query returns all CIs having no downstream nor upstream relationship in one step.
The same query can also be used for instance within a link (used by so called "fill" fields on forms).
Since the system checks the syntax on the query field I suggest to pass such cross table queries whenever possible in a variable.
Even when this functionality has been introduced quite late by HPE it's a big pleasure to have it now.
This opens a lot of doors and makes the live of a HPSM developer definitely easier. As you can imagine there are also certain limitations e.g.
- Only the following RAD functions can be used efficiently within cross table queries:
- null, str, nullsub, substr, lng, toupper, tolower, operator, tod
- Queries against array fields mapped as a CLOB won't perform
- Tables which are joined or merged in HPSM should not be used (but can be joined in the cross table query)
- Sorting or Grouping by array fields won't perform
See also the following demo video by HPE.