Click here to view and discuss this page in DocCommentXchange. In the future, you will be sent there automatically.

SAP SQL Anywhere, on-demand edition 1.0 SP 6 » Search queries

Search syntax

Search queries are used to specify cloud objects for task parameters and are used within the Cloud Console to search for cloud objects that meet a set of criteria. They are also used to define rules.

Syntax
search-query :
object-list
| complex-search
object-list :
full-name [ ; ... ]
| object-id [ ; ... ]
complex-search :
top-level search 
| full-search
top-level-search : 
top-level-element [ { or | and } top-level-element ... ]
| not top-level-element
top-level-element :
full-name 
| name-pattern 
| filter
full-name :
identifier
| quoted-identifier
quoted-identifier :
"identifier"
| [ identifier ]
name-pattern : See Remarks.
full-search : expression
expression :  
not expression
| expression { 
   and | or | add | sub | mul | div | mod | comparison-operator }
   expression
| ( expression)
| full-name
| function
| filter
| literal-value
| cloud-object-attribute
function : 
startswith ( cloud-object-attribute, string-literal)
| endswith ( cloud-object-attribute, string-literal)
| substringof ( string-literal, cloud-object-attribute)
| isnull ( cloud-object-attribute)
| regexp ( cloud-object-attribute, regexp-pattern)
filter :
all: 
| type: type-value
| name: name-value
| status: status-value
| search: search-query
| link-filter
type-value :
type-name
| (type-name [, type-name] ... )
name-value :
name-pattern
| "name-pattern"
| [ name-pattern ]
| identifier
| quoted-identifier
status-value : running-status
link-filter :
link-name:link-filter-value
link-filter-value : search
comparison-operator : 
eq | = 
| ge | >= 
| gt | >
| le | <=
| lt | <
| ne | <>
Parameters

All keywords in the search query syntax are case sensitive, including the logical conditions (such as and) and the function names (such as startswith). Link names, filter names, and cloud object names are case insensitive. Cloud object IDs must start with two upper-case letters. For example, DC-j2f5 is acceptable, but dc-j2f5 is not.

  • startswith (cloud-object-attribute, string-literal) function

    This function has two parameters: the cloud-object-attribute to search for, and a string-literal in single quotes. The function searches for the string-literal to appear at the beginning of the specified cloud-object-attribute. The string comparison is case-insensitive.

    For example, to list all the informational events for 17 May 2012 that are related to all primary database copies, run the following command:

    dbcloudcmd describe "type:event and event_severity eq 'Info' and related_object:
    ( type:databasecopy and db_mode eq 'Primary' ) and startswith ( creation_time, '2012-05-17' )"

    This search uses the related_object filter whose argument is a search-query. The related_object filter allows additional conditions that are evaluated against the cloud object that the event pertains to. In this example, the type:databasecopy filter and the db_mode attribute restrict the set of returned objects to events that pertain to primary database copies. The reference to creation_time refers to the creation time of the event, not the creation time of the database copy, since the startswith function is outside of the related_object filter.

    You can alter the above search to restrict the events to those relating to databases CompanyA and demo, by running the following command:

    dbcloudcmd describe "type:event and event_severity eq 'Info' and related_object:
    ( type:database and search:(CompanyA;demo))"
  • endswith (cloud-object-attribute, string-literal) function

    This function has two parameters: the cloud-object-attribute to search for, and a string-literal in single quotes. The function searches for the string-literal to appear at the end of the specified cloud-object-attribute. The string comparison is case-insensitive.

    For example, search for cloud users whose email addresses end with sap.com by running the following command:

    dbcloudcmd describe type:user and endswith ( email_address, 'sap.com' )
  • substringof (string-literal, cloud-object-attribute) function

    For example, to list all events containing the phrase adding database in the event summary, run the following command:

    dbcloudcmd describe "type:event and substringof ( 'adding database',summary )"

    This function searches for the string-literal (in single quotes) to appear anywhere in the specified cloud-object-attribute. The string comparison is case-insensitive.

  • isnull (cloud-object-attribute) function

    This function searches for the specified cloud-object-attribute to be NULL. A cloud object attribute can be NULL if its value has not been set, or if the attribute is for an object that is inapplicable to the search query.

    For example, search for the running databases that are not yet backed up by running the following command:

    dbcloudcmd describe "type:database and status:running and isnull ( last_backup_time )"
  • regexp (cloud-object-attribute, regexp-pattern) function

    This function searches for the specified cloud-object-attribute to match the regular expression pattern. The regular expression pattern must be a string-literal in single quotes. Character comparisons are case-sensitive.

    For example, you can search for all events stating when all database copies were added to the cloud by searching for the text "added" in the summary text of the event:

    dbcloudcmd describe "type:event and event_severity eq 'Info' and related_object:
    ( type:databasecopy ) and regexp ( summary, '.*added.*' )"
  • name-pattern

    An identifier, possibly quoted using double quotes or square brackets, containing at least one character and one or more asterisks, each of which represent zero or any number of characters. Adjacent asterisks are not permitted.

  • cloud-object-name

    Cloud object names:

    • Must contain one or more characters.

    • Can contain spaces, as long as the name is enclosed in either quotation marks or square brackets.

    • Can contain one or more asterisks as wildcards.

    The cloud-object names and cloud-object properties are not differentiated by a search query. A search query could interpret a cloud-object attribute as a cloud-object name if the search query is malformed.

    For example, the following search query returns database copies that are mirrored: database_copy(dbmode eq 'Mirror'). While the following search query returns database copies named dbmode: database_copy(dbmode)

  • cloud-object-id

    An object ID identifies every object in the cloud. The following object ID is an example of a database object ID: DB-h6w5.

    The cloud-object-id has the following form where XX is the two-character abbreviation of the cloud-object-type and a9a9 is the generated ID number: XX-a9a9.

    The cloud-object-id can be viewed in the object's Overview panel in the Cloud Console, and is returned when you run tasks in dbcloudcmd. To view a list of objects, including their cloud-object-id values for a particular cloud-object-type, run the following command: dbcloudcmd describe type:cloud-object-type.

  • filter-name:filter-value

    Filters the result to a specific cloud-object type.

    A filter consists of a filter-name:filter-value pair separated by a colon. Whitespace is not permitted between the filter-name, the colon, and the beginning of the filter-value. All filters are system defined.

    The following values are valid filters:

    • all:

      Matches all cloud objects. This filter takes no values, and the colon is required.

    • name:name-value

      Matches the title of an object in the cloud. The metadata for cloud objects includes both a name and a title, and for many objects these two properties are identical. However, some transient cloud objects, such as tasks, do not have names, only titles. The name filter supports asterisk (*) wildcard search queries.

      For example, the following search query matches an object with the name abc:

      name:abc

      The following search query matches objects either named MyHost or Myhost2:

      MyHost;Myhost2
    • status:status-value

      Matches all objects with the specified current status. status-value must be one of:

      disconnected dropped starting stopping
      dropping running stopped  
    • type:type-value

      Matches all objects with the specified type. object-type is case insensitive and must be one of the following values:

      AgentAction Filter Schedule
      Backup Host Server
      Cloud InstalledVersion StagingDirectory
      Database InstallSoftware Tag
      DatabaseCopy MaintenancePlan Task
      DataType Metric TaskDefinition
      Event Object User
      EventType Rule  
    • search:search-query

      The search filter takes a search query as an argument. The search filter allows you to combine top-level search, object-list search, and full-search elements in the same search query.

      For example, you can specify (type:server and search:myhost*) or search:(myhost;yourhost) to return cloud server objects that match the top-level search myhost* or the specific cloud servers myhost and yourhost.

    • link-name:link-filter-value

      A link filter (link-name:link-filter-value) is used to qualify a search query by testing for the existence of a relationship between cloud objects, and if the related objects also satisfy the other search query criteria. A link filter permits you to create a search query that queries associations between related objects in the cloud.

      link-name is the name of a cloud object type and can be one of the following values. These values are case insensitive.

      apply_after incremental_schedule server
      arbiter_for_database instance subevent
      arbiter maintenance_plan subtask
      backup notification tag
      database_copy notified_user tag_rule
      database object_rule target_tag
      event_type owner task
      event parent_event task_definition
      full_maintenance_plan parent_task task_event
      full_schedule rule watching_user
      host schedule related_object
      incremental_maintenance_plan schedule_task watched_object
      target_object    
  • literal-value

    Search supports both numeric and string literals. A string literal is an arbitrary sequence of characters delimited with single quotes. Double any quotes that appear within the string.

Remarks

To search for object names that contain white space, enclose the name within double quotes or square brackets.

Example

  • all:

    The following task stops all databases.

    dbcloudcmd run StopDatabase all:
  • name:name-value filter examples
    Filter example Description
    name:abc Matches an object with the name abc
    name:"abc def" Matches an object with the name abc def (contains an embedded blank)
    name:*abc* Matches an object with a name that contains the letters abc anywhere in the name
    name:"abc def*" Matches any object with a name that begins with "abc def" (contains an embedded blank)
    name:ab**d Invalid search query
  • status:status-value filter examples
    Filter example Description
    status:running Matches an object that is running
    status:stopped Matches an object that is stopped

    The following task stops all databases with the status running.

    dbcloudcmd run StopDatabase "status:running"
  • type:type-value filter examples
    filter example Description
    type:Database Matches databases
    type:TaskDefinition Matches task definitions
    type:Object Matches all objects in the cloud
    type:Cloud Matches the cloud object

    The following task applies the tag mytag to all databases.

    CALL DbCloud.ApplyTag( 'mytag', 'type:database' )
  • link-name:link-filter-value
    Filter example Results
    dbcloudcmd run StopDatabase host:MyHost

    Stops all databases on host MyHost.

    The link-name is host. Because the StopDatabase task applies only to databases, the command only impacts databases running on the MyHost host.

    dbcloudcmd run StopServer host:MyHost

    Stops all cloud servers on host MyHost.

    The link-name is host. Because the StopServer task applies only to servers, the command only impacts cloud servers running on the host MyHost.

    dbcloudcmd run StopServer host:MyHost or MyOtherHost

    Stops all cloud servers on hosts MyHost and MyOtherHost.

    In this case, the StopServer task expects only cloud servers. The same search query (host:MyHost) identifies all cloud objects that have a related host named MyHost. In most cases, the context of the search query limits the matched objects to a single type (for example, a database or a cloud server). In these cases, there is an implicit type filter such as type:database.

    dbcloudcmd run ApplyTag MyTag host:MyHost
    The link-name is host. Because the ApplyTag task can apply to any cloud object, this search query returns all cloud objects that have a relationship with the host MyHost.
    dbcloudcmd run ApplyTag MyTag 
    "(type:Host or type:Server) and database:MyDatabase"
    The ApplyTag task can apply a tag to any type of cloud object. To restrict the type of objects matched, you can use a type: filter. In this example, the tag MyTag is applied to all hosts and cloud servers that are running the database MyDatabase.
     dbcloudcmd run StopDatabase "server:(tag:MyTag)"  

    The value to a link filter is a string that is a search query itself.

    This task stops all databases on a cloud server that has the tag MyTag.

    dbcloudcmd run StopDatabase server:myserver#3

    Stops databases on the cloud server myserver#3.

    dbcloudcmd run StartServer tag:mytag

    Starts all cloud servers that have the Mytag tag.

    • The following task applies the CloudyTag to cloud servers that are running the database MyDatabase and to hosts that are running the database MyDatabase. The MyDatabase database is not tagged.

      dbcloudcmd run ApplyTag CloudyTag "(type:Host or type:Server) and database:MyDatabase"
    • The following task stops databases on all cloud servers that have the specified tag-name.

      dbcloudcmd run StopDatabase server:tag:tag-name
    • The following task starts all cloud servers on all hosts that have the specified tag-name.

      dbcloudcmd run StartServer host:tag:tag-name
    • The following task stops all cloud servers running on hosts with the tag mytag.

      dbcloudcmd run StopServer host:tag:mytag
    • The following task stops all cloud servers running on the host with the name myhost.

      dbcloudcmd run StopServer host:myhost
    • The following task moves the database MyDemo from the cloud server MyServer#3 to a cloud server that has the tag MyTag and has a page size of 4 KB.

      dbcloudcmd run LoadBalanceDatabases MyDemo "tag:MyTag and page_size eq 4096 and not name:MyServer#3" 
    • The following task moves the MyDemo tenant database to the cloud server that has the tag MyTag.

      dbcloudcmd run MoveDatabase MyDemo tag:MyTag
      Note You can only specify one cloud server for the second parameter in the MoveDatabase task. Therefore, the search query tag:MyTag can only return one cloud server; otherwise, an error appears.
    • The following task moves the MyDemo tenant database to the cloud server that is running the database CompanyA.

      dbcloudcmd run MoveDatabase MyDemo database:CompanyA
  • expression searches

    The following task uses load balancing to move all databases off the MYCLOUD-VM00#2 server.

    CALL DBCloud.LoadBalanceDatabases( 'all:','not name:"MYCLOUD-VM00#2"' ); 
  • literal-value

    A literal value can be either a number or a single quote-delimited string.

    dbcloudcmd describe "type:database and run_mode eq 'run'"