Search Maximo CLOB fields with DB2 LOCATE

Maximo stores some large pieces of text in CLOB columns. Automation script source is a useful example: when you need to find every script containing a method name, property or diagnostic value, a normal equality comparison is not enough.

On DB2, the LOCATE function can search the CLOB directly:

SELECT
  *
FROM
  MAXIMO.AUTOSCRIPT
WHERE
  LOCATE('TESTVAL', SOURCE) > 0;

This returns every row where SOURCE contains TESTVAL.

How LOCATE works

The basic form is:

LOCATE(search_string, source_string)

In the automation script query:

• 'TESTVAL' is the text to find.
• SOURCE is the CLOB to search.
• The result is the position of the first match, starting at 1.
• The result is 0 when the text is not present.
• The result is NULL when either argument is NULL.

That is why the predicate uses > 0. Any positive position means DB2 found the search text.

You can expose the position while checking a query:

SELECT
  SCRIPTNAME,
  LOCATE('TESTVAL', SOURCE) AS MATCH_POSITION
FROM
  MAXIMO.AUTOSCRIPT
WHERE
  LOCATE('TESTVAL', SOURCE) > 0
ORDER BY
  SCRIPTNAME;

Once the query works, select only the columns you need instead of returning every CLOB with SELECT *:

SELECT
  SCRIPTNAME,
  DESCRIPTION,
  ACTIVE
FROM
  MAXIMO.AUTOSCRIPT
WHERE
  LOCATE('TESTVAL', SOURCE) > 0
ORDER BY
  SCRIPTNAME;

Case-sensitive and case-insensitive searches

LOCATE compares using the database collation. Do not assume that TESTVAL, TestVal and testval are equivalent in every DB2 database.

When you deliberately want to ignore case, normalize both values:

SELECT
  SCRIPTNAME,
  DESCRIPTION
FROM
  MAXIMO.AUTOSCRIPT
WHERE
  LOCATE(UPPER('testval'), UPPER(SOURCE)) > 0
ORDER BY
  SCRIPTNAME;

Calling UPPER on every CLOB adds work, so use the case-sensitive form when you know the exact spelling.

Start searching later in the CLOB

An optional third argument tells DB2 where to begin:

LOCATE(search_string, source_string, start_position)

For example, this ignores the first 500 characters:

SELECT
  SCRIPTNAME
FROM
  MAXIMO.AUTOSCRIPT
WHERE
  LOCATE('TESTVAL', SOURCE, 501) > 0;

The returned position still refers to the original source string. This is useful when a known header or generated prefix cannot contain the value you need.

LOCATE returns only the first occurrence at or after the starting position. If you need every occurrence inside one CLOB, use the returned position as the starting point for another search or process the text in a tool better suited to that analysis.

Search for SQL literals safely

A single quote inside a literal must be doubled. To find service.error('custom', write:

SELECT
  SCRIPTNAME
FROM
  MAXIMO.AUTOSCRIPT
WHERE
  LOCATE('service.error(''custom''', SOURCE) > 0;

Application code should bind the search value as a parameter instead of assembling SQL text:

SELECT
  SCRIPTNAME,
  DESCRIPTION
FROM
  MAXIMO.AUTOSCRIPT
WHERE
  LOCATE(?, SOURCE) > 0;

The parameter contains the text to find. Characters such as quotes remain data and cannot change the query structure.

Narrow the rows before searching the CLOB

A LOCATE predicate normally requires DB2 to inspect the CLOB for every candidate row. Add selective predicates on indexed columns where the business question allows it:

SELECT
  SCRIPTNAME,
  DESCRIPTION
FROM
  MAXIMO.AUTOSCRIPT
WHERE
  ACTIVE = 1
  AND SCRIPTNAME LIKE 'PO%'
  AND LOCATE('TESTVAL', SOURCE) > 0
ORDER BY
  SCRIPTNAME;

This is especially important when searching larger Maximo tables. Run broad CLOB searches away from busy periods, review the access plan for recurring queries and avoid returning the CLOB itself unless you need it.

For frequent linguistic searches across a large collection, DB2 Text Search provides indexed CONTAINS queries and supports CLOB columns. That is a separately administered feature with index maintenance and Maximo support implications; do not add a text index to a Maximo-owned table without reviewing the design, backup, upgrade and support requirements.

Other Maximo CLOB searches

The same pattern is useful anywhere the source column is a character or CLOB value. Examples include finding:

• A deprecated API call in automation script source.
• A hostname in stored integration configuration.
• A property name embedded in a large template.
• A fragment of generated SQL captured in diagnostic data.

Always confirm the table and column through Maximo metadata before adapting the query. Use read-only database access for investigation and make supported configuration changes through Maximo rather than updating CLOB data directly.

References

• IBM DB2: LOCATE scalar function
• IBM DB2: CONTAINS function
• IBM DB2: Creating a text search index