Query Sample Data in Db2

The sample data used in this tutorial

Assume an authorized training schema named TRAINING. Its CUSTOMER table has CUSTOMER_ID, CUSTOMER_NAME, CITY, STATUS, and SALES_REP_ID columns. SALES_REP_ID is nullable because a new customer might not yet have a representative. A second table, SALES_ORDER, has ORDER_ID, CUSTOMER_ID, ORDER_DATE, ORDER_STATUS, and ORDER_TOTAL. Your site will probably use another qualifier. Replace TRAINING only after confirming the correct schema with your instructor or DBA.

1
2
3
4
5
6
7
8
9
10
11
12
-- Plausible rows used by the examples
-- TRAINING.CUSTOMER
-- 101 | Acme Tools       | London     | A | 9001
-- 102 | Blue Sky Market  | Birmingham | A | 9002
-- 103 | Cedar Workshop   | NULL       | I | NULL
-- 104 | Delta Parts      | London     | A | 9001
 
-- TRAINING.SALES_ORDER
-- 5001 | 101 | 2026-07-02 | SHIPPED | 1250.00
-- 5002 | 101 | 2026-07-18 | OPEN    |  425.50
-- 5003 | 102 | 2026-07-21 | OPEN    | 2100.00
-- 5004 | 104 | 2026-08-01 | SHIPPED |  780.00

These rows are explanatory, not a request to insert anything. If you completed the previous tutorial, query your own sandbox objects. Otherwise, read the SQL and predict the results before running it against a table where you have SELECT authority.

Start with safe SELECT exploration

SELECT is read-only, but a careless SELECT can still consume substantial CPU, perform large scans and sorts, hold locks depending on isolation, or produce enormous output. Begin narrowly. Fully qualify the table, list the columns you need, filter on a known key, and limit the result. Do not copy a statement from a tutorial and aim it at production merely because it contains no UPDATE or DELETE.

1
2
3
4
5
6
SELECT CUSTOMER_ID,
       CUSTOMER_NAME,
       STATUS
  FROM TRAINING.CUSTOMER
 WHERE CUSTOMER_ID = 101
 FETCH FIRST 10 ROWS ONLY;

Naming columns is preferable to SELECT *. It documents the expected shape, transfers less unnecessary data, and avoids changing your output when a table gains a column. The row limit is defensive during exploration. The key predicate should return at most one row, but the limit protects you if your understanding of the table is wrong.

• Verify the subsystem, schema, and authorization ID before execution.
• Use an approved training table or a personal sandbox.
• Start with equality on a primary key or another selective indexed column.
• Request only useful columns and a small number of rows.
• Read all statements in the input member; SPUFI and DSNTEP2 execute what you submit.

Predicates: choose which rows qualify

A WHERE clause contains predicates. An equality predicate finds one exact value. BETWEEN checks an inclusive range. IN compares against a list. LIKE performs pattern matching, where percent means any sequence of characters and underscore means one character. Combine conditions with AND and OR, but use parentheses so the intended logic is obvious.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
-- Active London customers
SELECT CUSTOMER_ID, CUSTOMER_NAME
  FROM TRAINING.CUSTOMER
 WHERE STATUS = 'A'
   AND CITY = 'London'
 ORDER BY CUSTOMER_NAME;
 
-- Open orders in a date range (both endpoints are included)
SELECT ORDER_ID, CUSTOMER_ID, ORDER_DATE, ORDER_TOTAL
  FROM TRAINING.SALES_ORDER
 WHERE ORDER_STATUS IN ('OPEN', 'HELD')
   AND ORDER_DATE BETWEEN DATE('2026-07-01') AND DATE('2026-07-31')
 ORDER BY ORDER_DATE, ORDER_ID;
 
-- Parentheses make the business rule explicit
SELECT CUSTOMER_ID, CUSTOMER_NAME, CITY
  FROM TRAINING.CUSTOMER
 WHERE STATUS = 'A'
   AND (CITY = 'London' OR CITY = 'Birmingham');

Match values to column data types. Use DATE for a date value rather than relying on an ambiguous character format. Avoid wrapping an indexed column in a function merely to make a predicate look convenient; for example, UPPER(CUSTOMER_NAME) can prevent simple index matching unless the design supports that expression. Also avoid a leading wildcard such as LIKE '%Tools' when a prefix search can meet the requirement.

ORDER BY and repeatable row limiting

Db2 does not promise row order unless you specify ORDER BY. A result that appears sorted today may change after RUNSTATS, REORG, index changes, or a rebind. ORDER BY can name columns and choose ASC or DESC. Add a unique tie-breaker when two rows can share the main sort value.

1
2
3
4
5
6
7
8
9
-- Five largest orders, with a stable tie-breaker
SELECT ORDER_ID,
       CUSTOMER_ID,
       ORDER_DATE,
       ORDER_TOTAL
  FROM TRAINING.SALES_ORDER
 ORDER BY ORDER_TOTAL DESC,
          ORDER_ID
 FETCH FIRST 5 ROWS ONLY;

FETCH FIRST controls quantity, not identity. Without ORDER BY, “first five” means any five rows that satisfy the query. With ORDER_TOTAL DESC and ORDER_ID, the query has a clear top-five meaning. A sort may still be required, so row limiting is not a guarantee of a cheap query. The available indexes and predicates determine how much work Db2 must do before it can identify the requested rows.

GROUP BY, aggregate functions, and HAVING

Aggregates summarize several detail rows. COUNT counts, SUM totals, AVG calculates a mean, and MIN or MAX finds an extreme. GROUP BY creates one result row per distinct grouping value. Every selected expression that is not aggregated must normally participate in the grouping.

1
2
3
4
5
6
7
8
9
SELECT ORDER_STATUS,
       COUNT(*)         AS ORDER_COUNT,
       DECIMAL(SUM(ORDER_TOTAL), 12, 2) AS TOTAL_VALUE,
       DECIMAL(AVG(ORDER_TOTAL), 12, 2) AS AVERAGE_VALUE
  FROM TRAINING.SALES_ORDER
 WHERE ORDER_DATE >= DATE('2026-07-01')
 GROUP BY ORDER_STATUS
HAVING COUNT(*) >= 2
 ORDER BY TOTAL_VALUE DESC;

WHERE filters orders before grouping. HAVING filters the completed groups, so it is the correct place for COUNT(*) >= 2. COUNT(*) includes rows regardless of NULL columns, while COUNT(SALES_REP_ID) counts only rows where that particular expression is not NULL. SUM and AVG also ignore NULL inputs and can return NULL when no non-NULL input exists.

Join related sample tables

A join combines rows by a relationship. CUSTOMER_ID is the link between the customer and order tables. Use aliases to keep column references short, and qualify shared column names so readers know their source. An INNER JOIN returns only matching pairs. A LEFT JOIN keeps every row from the left table and supplies NULL for right-side columns when no match exists.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Orders that have a matching customer
SELECT O.ORDER_ID,
       O.ORDER_DATE,
       C.CUSTOMER_NAME,
       O.ORDER_TOTAL
  FROM TRAINING.SALES_ORDER AS O
  INNER JOIN TRAINING.CUSTOMER AS C
    ON C.CUSTOMER_ID = O.CUSTOMER_ID
 WHERE O.ORDER_STATUS = 'OPEN'
 ORDER BY O.ORDER_DATE, O.ORDER_ID;
 
-- Include customers that have no orders
SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME,
       COUNT(O.ORDER_ID) AS ORDER_COUNT
  FROM TRAINING.CUSTOMER AS C
  LEFT JOIN TRAINING.SALES_ORDER AS O
    ON O.CUSTOMER_ID = C.CUSTOMER_ID
 GROUP BY C.CUSTOMER_ID, C.CUSTOMER_NAME
 ORDER BY C.CUSTOMER_ID;

Notice COUNT(O.ORDER_ID) in the LEFT JOIN. For a customer without an order, the join still produces one preserved customer row with NULL order columns. COUNT(*) would count that preserved row as one, but COUNT(O.ORDER_ID) correctly returns zero because the order ID is NULL. A missing or incomplete join predicate can multiply rows, so compare expected row counts as you build the query.

Understand Db2 NULL semantics

NULL is not zero, an empty string, or a special character. It means the value is unknown or absent. SQL therefore uses three-valued logic: TRUE, FALSE, and UNKNOWN. A WHERE clause retains only TRUE rows. CITY = NULL and CITY <> NULL both evaluate to UNKNOWN, so use IS NULL or IS NOT NULL.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- Customers without an assigned sales representative
SELECT CUSTOMER_ID, CUSTOMER_NAME
  FROM TRAINING.CUSTOMER
 WHERE SALES_REP_ID IS NULL;
 
-- Display a readable substitute without changing stored data
SELECT CUSTOMER_ID,
       CUSTOMER_NAME,
       COALESCE(CITY, 'Not supplied') AS DISPLAY_CITY
  FROM TRAINING.CUSTOMER
 ORDER BY CUSTOMER_ID;
 
-- Correctly include active rows and rows whose status is unknown
SELECT CUSTOMER_ID, CUSTOMER_NAME, STATUS
  FROM TRAINING.CUSTOMER
 WHERE STATUS = 'A'
    OR STATUS IS NULL;

COALESCE returns the first non-NULL argument. It is useful for presentation, but it does not repair missing data and can affect predicate matching if placed around a column in WHERE. In an application program, a nullable selected value also needs a null indicator or another supported representation. Without an indicator, embedded SQL can report SQLCODE -305 when Db2 tries to assign NULL to an ordinary host variable.

Be aware of EXPLAIN and access paths

Correct SQL can still be expensive. The Db2 optimizer chooses an access path using table and index definitions, catalog statistics, predicates, join choices, sort requirements, and bind options. EXPLAIN writes access-path information to properly defined explain tables. It does not execute the query merely to show its result rows.

1
2
3
4
5
6
7
8
9
10
EXPLAIN PLAN SET QUERYNO = 96010 FOR
SELECT C.CUSTOMER_NAME,
       SUM(O.ORDER_TOTAL) AS TOTAL_VALUE
  FROM TRAINING.CUSTOMER AS C
  INNER JOIN TRAINING.SALES_ORDER AS O
    ON O.CUSTOMER_ID = C.CUSTOMER_ID
 WHERE O.ORDER_DATE >= DATE('2026-01-01')
 GROUP BY C.CUSTOMER_NAME
 ORDER BY TOTAL_VALUE DESC
 FETCH FIRST 10 ROWS ONLY;

Your explain tables and process are site-specific. Some teams use PLAN_TABLE queries, Visual Explain, Data Studio, or performance tooling rather than hand-reading every column. As a beginner, look for broad table-space scans, non-matching index access, large estimated row counts, sorts, and surprising join order. Those observations are questions, not automatic proof that the optimizer is wrong. Check current RUNSTATS and discuss the plan with a DBA before adding hints or indexes.

Check SQLCODE and SQLSTATE

An interactive tool prints diagnostics, while an embedded program must check them. SQLCODE 0 normally means success. +100 means no row was found; in a cursor loop it means no more rows remain. Negative SQLCODE values indicate errors. Common examples include -204 for an undefined object, -206 for an invalid column reference, -305 for NULL without an indicator, and -551 for missing authorization. Always use the full message text and SQLSTATE because one number alone is not a complete diagnosis.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
           EXEC SQL
                FETCH C-ORDERS
                 INTO :HV-ORDER-ID,
                      :HV-ORDER-TOTAL:HV-TOTAL-IND
           END-EXEC.
           EVALUATE SQLCODE
             WHEN 0
               PERFORM PROCESS-ORDER
             WHEN +100
               SET END-OF-ROWS TO TRUE
             WHEN OTHER
               DISPLAY 'QUERY FAILED SQLCODE=' SQLCODE
               DISPLAY 'SQLSTATE=' SQLSTATE
               PERFORM SQL-ERROR
           END-EVALUATE.

The COBOL starts in conventional fixed-format areas and illustrates the control flow, not a complete compilable program. Production error handling should also capture useful context and formatted diagnostic text without exposing sensitive values.

Run queries with SPUFI or DSNTEP2

Interactive exploration with SPUFI

SPUFI is an ISPF facility for submitting SQL to a Db2 subsystem. Place the statement in an input data set or member, select an output destination, verify execution defaults, and submit. Browse both the result and the messages. Keep the original SQL beside the output so another person can reproduce what you ran. Use a small maximum row setting when your installation exposes one.

Repeatable batch queries with DSNTEP2

DSNTEP2 is a Db2-supplied dynamic SQL sample often run in batch. SQL is supplied through its input DD and output appears in job output. It is useful when the same validation needs to be retained or repeated, but its prepared plan, JCL procedure, terminator rules, and control parameters vary by installation. Copy an approved site example rather than inventing production JCL from an internet fragment.

1
2
3
4
5
6
7
8
9
10
-- Suitable read-only SQL input for SPUFI or an approved DSNTEP2 job
SELECT CUSTOMER_ID,
       CUSTOMER_NAME,
       CITY
  FROM TRAINING.CUSTOMER
 WHERE STATUS = 'A'
 ORDER BY CUSTOMER_ID
 FETCH FIRST 20 ROWS ONLY;
 
-- Review the tool output for SQLCODE, SQLSTATE, messages, and row count.

SPUFI and DSNTEP2 are execution tools, not safety barriers. If your input contains INSERT, UPDATE, DELETE, MERGE, DDL, or COMMIT, the tool can execute it when your authorization permits. Keep exploratory members read-only, separate changes from inquiries, and follow your site's review process.

A practical query-building workflow

• Confirm the schema and inspect the table definition or an approved data dictionary.
• Select a known key and two or three named columns.
• Add a small FETCH FIRST limit while learning the data shape.
• Add predicates one at a time and verify the row count after each change.
• Add ORDER BY whenever row sequence or top-N meaning matters.
• Introduce joins only after each table query works independently.
• Test NULL cases explicitly and use indicators in embedded SQL.
• Use EXPLAIN before repeatedly running a broad or multi-table statement.
• Save SQL, SQLCODE, SQLSTATE, messages, row count, and relevant access-path evidence.

Explain it like I'm 5

Imagine two boxes of cards. One box has customer cards, and the other has order cards. SELECT says which words you want to read. WHERE is a rule that chooses cards, such as “only blue cards.” ORDER BY lines them up, and FETCH FIRST says to bring only a small handful. GROUP BY makes piles and counts each pile. A JOIN matches customer cards to order cards with the same number. NULL is a blank space where nobody knows the answer, so you must ask “is it blank?” instead of asking whether the blank equals a word. EXPLAIN is looking at the librarian's route before asking them to search the whole building.

Exercises

1. Write a query for active customers that returns CUSTOMER_ID, CUSTOMER_NAME, and CITY, sorted by name and limited to ten rows.
2. Find July 2026 orders whose total is at least 500.00. Sort from largest to smallest and use ORDER_ID as a tie-breaker.
3. Group SALES_ORDER by ORDER_STATUS and display a row count and total value. Explain why a status condition belongs in WHERE but a minimum count belongs in HAVING.
4. Join CUSTOMER to SALES_ORDER and show each open order with the customer name. Predict what an INNER JOIN does with a customer who has no orders.
5. Rewrite CITY = NULL correctly, then use COALESCE to display “Not supplied.” Explain why those two expressions solve different problems.
6. Write a LEFT JOIN that counts orders for every customer. Use COUNT(O.ORDER_ID), and explain why COUNT(*) gives the wrong impression for customers without orders.
7. Prepare an EXPLAIN statement for your grouped order query. Record the questions you would ask about scans, sorts, estimated rows, and indexes.
8. Run one approved SELECT in SPUFI or DSNTEP2. Save the exact input, result row count, SQLCODE, SQLSTATE, and messages without submitting any data-changing statement.