Sun N1 Grid Engine 6.1 User's Guide

Creating and Running Simple Queries

The query defines the data set that you want to retrieve. You can create simple queries for which the system formulates the SQL query string. If you know SQL and you want to write the query yourself, you can create advanced queries.

ProcedureHow to Create a Simple Query

  1. Go to Query List and press the New Simple button.

    The following screen appears with three tabs showing common information, such as the query category and description. This information is optional. To define the query, go to the Simple Query tab. To define the configuration how to display the results of the query, go to the View tab.

    Query List screen showing list of queries. Shows
New Simple Query and New Advanced Query buttons. Shows Run, Edit,
and Delete buttons.

    Click the Simple Query tab to access the Query definition page. The page provides the following features:

    • A Table/View dropdown menu you use to choose a database table or view to predefine your query

    • The Field List where all the fields are listed as a row

    • A Filter List for defining filter conditions for your query

    • The Row Limit field to restrict the number of result entries for your query

    Simple query screen shows table, field, filter,
row

    The single steps how to construct a simple query are outlined as follows.

  2. Select a table from the table list.

  3. Define the fields that you want to see.

    The Field Function describes the functionality used for the field. The following list shows the supported values of Field Function.

    VALUE

    Use the current value of the field

    SUM

    Accumulate the values of the field

    COUNT

    Count the number of values of the field

    MIN

    Get the minimum value of the field

    MAX

    Get the maximum value of the field

    AVG

    Get the average value of the field

    • The Field Name is a field in the selected table.

    • The User Defined Name allows the results to display a more meaningful name.

    • Sort allows to define the sorting order for every field if needed.

  4. (Optional) Define Filters.

    You must specify at least one field before you can define filters.

    • AND/OR is needed for any filter except the first. This setting provides the logical connection to the previous filter condition.

    • The Field Name is the name of the field to be filtered. If a field has a user-defined name, that name is shown in the selection list. Otherwise, a generated name is shown.

    • The Condition field specifies the operators that are used to filter the values from the database. The following table lists the supported operators.

    Filter 

    Symbol 

    Description 

    Number of Requirements 

    Equal 

    The value must equal the Requirement 

    Not Equal 

    <>, != 

    The value must not equal the Requirement 

    Less Than 

    The value must be less than the Requirement 

    Less Than or Equal 

    <=, ≤ 

    The value must be less than or equal to the Requirement 

    Greater Than 

    The value must be greater than the Requirement 

    Greater Than or Equal 

    >=, ≥ 

    The value must be greater than or equal to the Requirement 

    Null 

     

    The value must be null 

    Not Null 

     

    The value must not be null 

    Between 

     

    The value must be in a specified interval 

    In 

     

    The value must be equal to an element of a specified list 

    1 or more 

    Like 

     

    The value must contain the given Requirement 

    The Requirement field contains a value that is used for filtering the values returned by the query. The following list contains some examples of things that might go into the Requirement field.

    1 AND 100

    For a between condition

    d%

    For a like condition

    %d%

    For a like condition

    %d%e%

    For a like condition

    Wert-1', Wert-2', ... , 'Wert-n

    For an in condition

  5. (Optional) Limit the number of data sets.

    To limit the number of data sets, select the Limit Query To First option. Then type the number of data sets you want returned.

  6. Click Save to save the query.

    The following figure displays the Save this Query As screen. Type a name for the query in the Query Name field, and then click Ok.

    After you save your query, you return to a modified version of the Simple Query screen.

ProcedureHow to Create a View Configuration

  1. To change the view configuration for a query, click the View tab.

    To create a view for a saved query:

    • Choose the query from the Query List on the Overview page.

    • Click the Edit button.

    • Click the View tab.

    The queries current view configuration displays.

  2. Declare how you want to view the results of your query.

    You can add three different sections to the view configuration, decide if additional information about the query is shown, and in which order it is shown.

    view 1 page

    Use the links at the top of the page to move to the corresponding section. The possible sections are Database Table, Pivot Table and Graphic. The View Configuration section is always visible and enables you to display the query description that has been entered on the common tab, the filter conditions from the filter list, and the resulting SQL statement of the query definition or the content of the SQL tab for advanced queries.

    Selecting Add Database, Add Pivot, or Add Graphic adds the corresponding section.

    For some queries, only a subset of the possible view selections are meaningful. For example, if you have only two columns to select from, pivot makes no sense.

    For the Database Table add and choose the columns that you need to display under Name and adjust their Type and Format. The order in which the columns are added will be the order in which the columns are presented. The selections that you make for this report do not affect the filters applied to the data.

    view table screen

    For the Pivot Table, add the pivot column, row, and data entries. Then choose the column Name, Type, and Format. To shift an entry to a different pivot type, select it under Pivot Type.

    view pivot table

    For the Graphic section, you can attach the query data to different chart diagram types. The following chart types are available from the Diagram Type menu:

    • Bar Chart

    • Bar Chart (3D)

    • Bar Chart Stacked

    • Bar Chart Stacked (3d)

    • Pie Chart, Pie Chart 3D

    • Line Chart

    • Line Chart Stacked Line

    Three different diagram types are available:

    • Bar

    • Pie

    • Line

    Bar and Pie types can be display with a 3D effect. Bar and Line diagrams can be drawn as stacked diagrams with values on the y-axis summarized.

    view graphic page
  3. Click Save or Save As to save your View configuration to the query.

  4. Click Run to run your query.

Defining Data Series for Diagrams

Two ways to define the data series for a diagram are:


Example 5–1 Accounting per Department Pie Chart

The query “Accounting per Department” results in a table with the columns: time, department, and cpu.

example columns and rows table

To display the result in a pie chart, select the following configuration:

example series config

The result will be multiple pie charts

pie charts

Example 5–2 CPU, Input/Output, and Memory Usage Over All Departments Bar Chart

A query summarizes CPU, IO, and Mem usage over all departments:

Usage example

To display the results in a bar chart, select the following configuration

bar chart config page

The results will be a bar chart with three bars for each department:

shows the results, a bar chart with three bars
for each department.

ProcedureHow to Run a Simple Query

  1. Run the query.

    • To run a query that you just created, click Run on the Simple Query screen.

      run simple query page
    • To run a query that you previously saved, select the query from the Query List screen and click Run.

      running a selected query from the query list
shown

ProcedureHow to Edit a Simple Query

  1. Select a query from the list on the Query List screen

  2. Click Edit.

    The selected Simple Query screen displays.

  3. Make changes to the Simple Query screen by navigating through the tabs and making your changes as you would when creating a simple query .

  4. Save or run your changed query.