IBM® Informix® version 12.10.xC1 added support for grid queries. A grid query selects data from multiple servers in a grid and consolidates the results. After you define a grid and designate which tables can be used in grid queries, all you have to do is identify the grid name with the GRID keyword in the FROM clause of a SELECT statement. The query then runs on all the grid servers.
The result of a grid query are qualifying rows from a logical UNION or UNION ALL of each table in the FROM clause across tables with the same names and the same schema in every grid server. This union can include tables across all nodes in the grid, or across a subset of those grid nodes, called a region.
So how useful are grid queries? Check out this example.
The following example is based on the stores_demo database. A grid named grid1 has eight servers, named store1 through store8. The example assumes that you defined the items, orders, and customer tables as grid tables.
Suppose you want the total sales and number of sales for each customer across all stores. You use 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
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 ALL keyword in the GRID ALL 'grid1' clause indicates that duplicate results are allowed.
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 . . .
Look at other grid query examples.
Find out all about grid queries.
Explore the syntax for grid queries.