Examples of grid queries

Example 1: Return chunk information about grid servers

Suppose you want to know about the chunks on all your grid servers. You want to know the number of chunks, which dbspaces each chunk is in, the total size of each chunk, and the amount of free space in each chunk.

You run the following grid query to return chunk information for each grid server. The tables in the sysmaster database are grid tables by default.

database sysmaster;

SELECT ifx_node_name()::char(12) AS node, chknum, dbsnum, nfree, chksize
FROM syschunks GRID ALL 'grid1';

The grid query returns the following results:

node         chknum dbsnum       nfree     chksize

store1            1      1     1777275     2000000
store1            2      2        5025      100000
store1            3      3       24974      100000
store2            1      1     1775579     2000000
store2            2      2        5025      100000
store2            3      3       24974      100000
store3            1      1     1769260     2000000
store3            2      2        5025      100000
store3            3      3       24974      100000

. . . 

Example 2: Aggregate results by server and find skipped servers

Suppose you want a list of the orders by customer for each store in the grid named grid1. A store is represented by its grid server name. You want to return all results, including duplicate rows. You do not want the query to fail if any of the grid servers are unavailable, but you want to know which servers were skipped.

Before you run the grid query, you run the following statement to run the query on available grid servers and skip any unavailable grid servers:

SET ENVIRONMENT GRID_NODE_SKIP ON;

You run the following grid query to return the outstanding orders by customer for each store:

SELECT c.fname, c.lname, ifx_node_name() AS node
  SUM(i.total_price) AS tot_amt, SUM(i.quantity) AS tot_cnt
  FROM items i, orders o, customer c GRID ALL 'grid1'
  WHERE i.order_num = o.order_num
  AND o.customer_num = c.customer_num
  GROUP BY 1,2
  ORDER BY 2,1,3;

The grid query returns the following results:

fname    Alfred
lname    Grant
node     store1
tot_amt  $84.00
tot_cnt  2

fname    Alfred
lname    Grant
node     store2
tot_amt  $84.00
tot_cnt  4

. . .

You run the following statement to find how many grid servers were skipped:

EXECUTE FUNCTION ifx_gridquery_skipped_node_count();

2

Two servers were skipped. You run the ifx_gridquery_skipped_nodes() statement for each of the skipped servers:

EXECUTE FUNCTION ifx_gridquery_skipped_nodes();

store5

EXECUTE FUNCTION ifx_gridquery_skipped_nodes();

store8

Example 3: Query a region of the grid

Suppose you want to know the total sales and number of sales per person for each store in Kansas. Kansas has two stores whose grid servers are named store3 and store4. You want all queries during your database session to be run as grid queries for the Kansas stores.

You run the following command to define a grid region named region1 that contains the servers store3 and store4:

cdr define region --grid=grid1 region1 store3 store4 

You run the following statement to set all SELECT statements during the session as grid queries for the region region1:

SET ENVIRONMENT SELECT_GRID_ALL region1

You run the following statement to return the total sales and number of sales per person for each store. The GRID clause is not necessary because you set the SELECT_GRID_ALL option.

SELECT fname[1,10], lname[1,10], ifx_node_id() AS storenum, 	
  SUM(quantity) AS tot_cnt, SUM(total_price) AS tot_amt
  FROM items i, orders o, customer c 
  WHERE i.order_num = o.order_num
  AND o.customer_num = c.customer_num
  GROUP BY 2,1
  ORDER BY 2,1,3;

The query returns the following results:

fname      lname         storenum          tot_cnt          tot_amt

Alfred     Grant                3                8           $84.00
Alfred     Grant                4                6           $84.00
Marvin     Hanlon               3               12          $438.00
Marvin     Hanlon               4               10          $438.00
Anthony    Higgins              3               45         $1451.80
Anthony    Higgins              4               36         $1451.80
Roy        Jaeger               3               16         $1390.00
Roy        Jaeger               4               13         $1390.00
Fred       Jewell               3               16          $584.00
Fred       Jewell               4               13          $584.00
Frances    Keyes                3                4          $450.00
Frances    Keyes                4                3          $450.00

. . .

Example 4: Use a grid query as a subquery

Suppose you want the total sales and number of sales for each customer across all stores. You use the same query that you use in example 2 as the subquery to return information by grid server. The main query aggregates the results of the subquery.

You run the following statement to return the total sales and number of sales per person:

SELECT fname, lname,
   SUM(tot_amt) AS amt_by_person, SUM(tot_cnt) AS tot_by_person
   FROM
       (
         SELECT c.fname, c.lname, ifx_node_name() AS node,
            SUM(i.total_price) AS tot_amt, SUM(i.quantity) AS tot_cnt
            FROM items i, orders o, customer c GRID ALL 'grid1'
            WHERE i.order_num = o.order_num
            AND o.customer_num = c.customer_num
            GROUP BY 1,2
        )
   GROUP BY fname, lname
   ORDER BY 2, 1;

The query returns the following results:

fname           lname              amt_by_person    tot_by_person

Alfred          Grant                    $336.00               20
Marvin          Hanlon                  $1752.00               40
Anthony         Higgins                 $5807.20              135
Roy             Jaeger                  $5560.00               50
Fred            Jewell                  $2336.00               50
Frances         Keyes                   $1800.00               10
Margaret        Lawson                  $1792.00              110

. . .