9.5.3 Attribute Filters in LIST and ALTER Commands

You can use the attribute_filters clause to specify the objects to display in LIST commands. Some ALTER commands also support the attribute_filters clause.

This syntax of the attribute_filters clause is: 

WHERE attribute_filter1 [ AND attribute_filter2 ... ]

Each attribute_filterN has the following syntax: 

attribute [ NOT | ! ] operator comparison_value

The attribute placeholder represents the name of the attribute to use for filtering. The supported types of operator are listed in the following table. These operators can be combined with NOT or !.

Table 9-1 Supported Operators in Attribute Filters

Operator Description

=

Tests for equality between string, status, or numeric attributes. For example:

status NOT = normal

>

Tests for attribute values greater than the specified comparison value. For example:

size > 139920M

<

Tests for attribute values less than the specified comparison value. For example:

freeSpace < 100M

LIKE

Tests for a similarity match.

For string comparisons, the LIKE operator uses case-sensitive regular expression matching that uses different wildcard characters from those used in SQL.

For example, the SQL wildcard '%' translates to '.*'. So, LIKE 'GD_IO_RQ.*' matches strings prefixed with GD_IO_RQ.

With numeric comparisons, the LIKE operator is functionally equivalent to the equals (=) operator. Likewise, NOT LIKE behaves the same as NOT =. Wildcards are not available for numeric comparisons with the LIKE operator.

When used with the supported operators, comparison_value is one of the following data types:

  • Numeric: Large values can be expressed using suffices M (1024^2), G (1024^3), or T (1024^4)
  • Literal: Value such as active or normal
  • Datetime: Value containing a valid ISO 8601 timestamp. For example, "2024-01-02T00:01:00-07:00" represents one minute after midnight on the second day of January 2024 in the time zone corresponding to UTC−07:00.
  • String: Value delimited by single quotation marks (' ') or double quotation marks (" ")
  • NULL: Unassigned strings or empty lists

Note the following when using the attribute_filters clause:

  • If either the attribute or comparison_value are a string, then a string comparison is always used. If the other value is not a string, it is implicitly converted to a string for the comparison.

    For example, many objects have a numeric freespace attribute that contains the amount of free space (in bytes) for instances of that object. In that case, where freespace = 0M is a numeric comparison that matches objects with no free space. However, where freespace = "0M" is a string comparison that never matches any objects because the numeric value of freespace can never contain the character M.

  • String comparisons using the equals (=), the greater-than (>), or the less-than (<) operators are not case-sensitive.

    Strings are considered equal if they contain the same character sequence after conversion to lowercase. For example, "Apple" = "apple".

    For greater-than (>) or less-than (<) operators, the strings are compared lexicographically based on the Unicode value of each character after converting the string to lowercase. The string with the lowest Unicode character value in the first position of difference is considered the lesser. For example, "appendix" < "apple" because both strings start with "app" and the 'e' character in "appendix" has a lower Unicode value than the 'l' in "apple". If there are no positional character differences but the string lengths are different, the shorter string lexicographically precedes the longer string. For example, "apple" < "apples".

Parent topic: DBMCLI Object Attributes