Lesson 2.4: Analyzing products and market trends with Db2 Big SQL joins and predicates

In this lesson, you will create and run Db2® Big SQL queries that analyze the market and financial data in the GOSALESDW schema.

1. From the Linux® command line in the cluster that contains the Db2 Big SQL server, log in as the bigsql user, and provide the password if prompted.

   su - bigsql

2. Use any available editor (for example, vi) to create a new file named companyInfo.sql in your local directory.

   vi companyInfo.sql

3. Add the following code in the companyInfo.sql file:

   
   --Fetch the product name, the quantity, and 
   --  the order method.
   --Product name has a key that is part of other 
   --  tables that we can use as a join predicate.
   --The order method has a key that we can use 
   --  as another join predicate.
   
   SELECT pnumb.product_name, sales.quantity, meth.order_method_en
     FROM
       gosalesdw.sls_sales_fact sales,
       gosalesdw.sls_product_dim prod,
       gosalesdw.sls_product_lookup pnumb,
       gosalesdw.sls_order_method_dim meth
     WHERE
       pnumb.product_language = 'EN'
       AND sales.product_key = prod.product_key
       AND prod.product_number = pnumb.product_number
       AND meth.order_method_key = sales.order_method_key
     FETCH FIRST 20 ROWS ONLY;

   Your immediate objectives are to learn what products were ordered from the Sample Outdoor Company, and how they were ordered. Because more than one table is referenced in the FROM clause, the query can join rows from those tables. A join predicate specifies a relationship between one or more columns from each table in the join.

   • The predicate prod.product_number = pnumb.product_number helps to narrow the result to product numbers that match in two tables.
   • The predicate pnumb.product_language = 'EN' helps to further narrow the result to English output only.
   • This query also defines aliases in the FROM clause. For example, pnumb is an alias for the GOSALESDW.SLS_PRODUCT_LOOKUP table, and that alias can be used in the WHERE clause as a convenient way to avoid ambiguous references.
   • The FETCH FIRST clause retrieves a subset of the results in case the full result set is very large.
4. Save the companyInfo.sql file in the /home/bigsql directory.

   
   [bigsql@<node1> ~]$ ls
   aFirstFile.sql  companyInfo.sql  GOSALESDW_viewddl.sql  hosts  logs  sqllib
   

5. Open the JSqsh client, specifying the bigsql configuration name, and provide the password if prompted:

   /usr/ibmpacks/common-utils/current/jsqsh/bin/jsqsh bigsql

6. Run the SQL script from the JSqsh client:

   \eval /home/bigsql/companyInfo.sql

7. Review the results.
   You can see some of the products that are being sold, and how they are being ordered by customers.

   
   +--------------------------------+----------+-----------------+
   | PRODUCT_NAME                   | QUANTITY | ORDER_METHOD_EN |
   +--------------------------------+----------+-----------------+
   | TrailChef Deluxe Cook Set      |      423 | Fax             |
   | Canyon Mule Weekender Backpack |      170 | E-mail          |
   | Canyon Mule Journey Backpack   |      167 | E-mail          |
   | Firefly Lite                   |      340 | E-mail          |
   | Firefly 2                      |      340 | E-mail          |
   | Firefly Multi-light            |      162 | E-mail          |
   | EverGlow Butane                |       82 | E-mail          |
   | Mountain Man Analog            |       98 | E-mail          |
   | Mountain Man Digital           |       81 | E-mail          |
   | Glacier Basic                  |      717 | E-mail          |
   | Glacier GPS                    |      246 | E-mail          |
   | BugShield Lotion Lite          |      498 | E-mail          |
   | BugShield Lotion               |     1091 | E-mail          |
   | Sun Blocker                    |      915 | E-mail          |
   | Deluxe Family Relief Kit       |      287 | E-mail          |
   | Calamine Relief                |      210 | E-mail          |
   | Aloe Relief                    |      258 | E-mail          |
   | Lady Hailstorm Steel Irons     |       46 | E-mail          |
   | Lady Hailstorm Titanium Irons  |       37 | E-mail          |
   | Lady Hailstorm Steel Woods Set |       24 | E-mail          |
   +--------------------------------+----------+-----------------+
   20 rows in results(first row: 1.704s; total: 1.727s)

8. Use the COUNT function to determine how many rows the full query returns:

   
   --SELECT pnumb.product_name, sales.quantity, meth.order_method_en
   SELECT COUNT(*)
     FROM
       gosalesdw.sls_sales_fact sales,
       gosalesdw.sls_product_dim prod,
       gosalesdw.sls_product_lookup pnumb,
       gosalesdw.sls_order_method_dim meth
     WHERE
       pnumb.product_language = 'EN'
       AND sales.product_key = prod.product_key
       AND prod.product_number = pnumb.product_number
       AND meth.order_method_key = sales.order_method_key
   --FETCH FIRST 20 ROWS ONLY;

   This query returns 446023 rows:

   
   +--------+
   |      1 |
   +--------+
   | 446023 |
   +--------+
   1 row in results(first row: 4.023s; total: 4.050s)
   

9. Update the query to return only sales visit data.

   
   SELECT pnumb.product_name, sales.quantity, meth.order_method_en
   --SELECT COUNT(*)
     FROM
       gosalesdw.sls_sales_fact sales,
       gosalesdw.sls_product_dim prod,
       gosalesdw.sls_product_lookup pnumb,
       gosalesdw.sls_order_method_dim meth
     WHERE
       pnumb.product_language = 'EN'
       AND sales.product_key = prod.product_key
       AND prod.product_number = pnumb.product_number
       AND meth.order_method_key = sales.order_method_key
   --FETCH FIRST 20 ROWS ONLY
       AND order_method_en = 'Sales visit';

   This query returns 15842 rows.
10. Create a new query to determine which purchasing method is associated with the largest orders.

    
    SELECT pll.product_line_en AS Product,
        md.order_method_en AS Order_method,
        SUM(sf.QUANTITY) AS total
      FROM
        gosalesdw.sls_order_method_dim AS md,
        gosalesdw.sls_product_dim AS pd,
        gosalesdw.sls_product_line_lookup AS pll,
        gosalesdw.sls_product_brand_lookup AS pbl,
        gosalesdw.sls_sales_fact AS sf
      WHERE
        pd.product_key = sf.product_key
        AND md.order_method_key = sf.order_method_key
        AND pll.product_line_code = pd.product_line_code
        AND pbl.product_brand_code = pd.product_brand_code 
      GROUP BY pll.product_line_en, md.order_method_en;

    The SUM aggregate function totals the orders by product and method.
11. Run this query, directing the output to a file:

    
    \eval /home/bigsql/companyInfo.sql >/home/bigsql/companyOutput.txt
    
    ...
    
    +--------------------------+--------------+----------+
    | PRODUCT                  | ORDER_METHOD |    TOTAL |
    +--------------------------+--------------+----------+
    | Camping Equipment        | E-mail       |  1413084 |
    | Camping Equipment        | Fax          |   413958 |
    | Camping Equipment        | Mail         |   348058 |
    | Camping Equipment        | Sales visit  |  2899754 |
    | Camping Equipment        | Special      |   203528 |
    | Camping Equipment        | Telephone    |  2792588 |
    | Camping Equipment        | Web          | 19230179 |
    | Golf Equipment           | E-mail       |   333300 |
    | Golf Equipment           | Fax          |   102651 |
    | Golf Equipment           | Mail         |    80432 |
    | Golf Equipment           | Sales visit  |   263788 |
    | Golf Equipment           | Special      |    38585 |
    | Golf Equipment           | Telephone    |   601506 |
    | Golf Equipment           | Web          |  3693439 |
    | Mountaineering Equipment | E-mail       |   199214 |
    | Mountaineering Equipment | Fax          |   292408 |
    | Mountaineering Equipment | Mail         |    81259 |
    | Mountaineering Equipment | Sales visit  |  1041237 |
    | Mountaineering Equipment | Special      |    93856 |
    | Mountaineering Equipment | Telephone    |   549811 |
    | Mountaineering Equipment | Web          |  7642306 |
    | Outdoor Protection       | E-mail       |   905156 |
    | Outdoor Protection       | Fax          |   311583 |
    | Outdoor Protection       | Mail         |   328098 |
    | Outdoor Protection       | Sales visit  |  1601526 |
    | Outdoor Protection       | Special      |   183075 |
    | Outdoor Protection       | Telephone    |  1836347 |
    | Outdoor Protection       | Web          |  6848660 |
    | Personal Accessories     | E-mail       |   791905 |
    | Personal Accessories     | Fax          |   359414 |
    | Personal Accessories     | Mail         |   115208 |
    | Personal Accessories     | Sales visit  |  1007107 |
    | Personal Accessories     | Special      |   117758 |
    | Personal Accessories     | Telephone    |  1472592 |
    | Personal Accessories     | Web          | 31043721 |
    +--------------------------+--------------+----------+
    35 rows in results(first row: 3.523s; total: 3.565s)
    

    Redirecting the output to a file makes it easy to capture query results for later analysis.

    
    [bigsql@<node1> ~]$ ls
    aFirstFile.sql  companyInfo.sql  companyOutput.txt  GOSALESDW_viewddl.sql  hosts  logs  sqllib