Db2 for i: beginner to advanced

Understand schemas, tables, rows, columns, keys, nulls, and the difference between a statement and a transaction.

• A table is a set of rows described by columns; a physical file can be used through SQL, but a logical file is not the same thing as a SQL view.
• Use a qualified name such as MYLIB.CUSTOMER so a library-list change cannot silently select another object.
• A primary key expresses identity. A unique constraint enforces a rule. An index supports access; it does not replace a business rule.

SELECT CUSTOMER_ID, NAME
  FROM MYLIB.CUSTOMER
 WHERE STATUS = 'A'
 ORDER BY CUSTOMER_ID;

Write safe data-changing statements and explain the row set or predicate before executing them.

• SELECT should name the columns needed by the caller. Avoid SELECT * in stable interfaces because schema changes can change the result contract.
• INSERT should name its target columns. UPDATE and DELETE need a deliberate WHERE predicate and a row-count check.
• Use parameter markers from the application rather than concatenating user input into SQL text.

-- Preview the exact rows before changing them
SELECT ORDER_ID, STATUS
  FROM MYLIB.ORDERS
 WHERE CUSTOMER_ID = :customerId;

UPDATE MYLIB.ORDERS
   SET STATUS = :newStatus
 WHERE ORDER_ID = :orderId;

Combine related tables while preserving the intended cardinality and handling missing related rows.

• INNER JOIN returns only matching rows. LEFT JOIN keeps the left row and produces nulls when the right side is absent.
• Put filters on the correct side of an outer join; a WHERE predicate on the right table can turn a LEFT JOIN into an inner result.
• GROUP BY creates one row per group. HAVING filters groups after aggregation; WHERE filters rows before aggregation.

SELECT c.CUSTOMER_ID, c.NAME, COUNT(o.ORDER_ID) AS ORDER_COUNT
  FROM MYLIB.CUSTOMER AS c
  LEFT JOIN MYLIB.ORDERS AS o
    ON o.CUSTOMER_ID = c.CUSTOMER_ID
 GROUP BY c.CUSTOMER_ID, c.NAME
HAVING COUNT(o.ORDER_ID) > 0;

Use views to expose a stable, permission-aware shape without copying data.

• A view stores a query definition. It normally does not store a second copy of the table rows.
• Use a view to hide joins, expose only approved columns, or give reporting consumers a stable name.
• Document whether a view is intended for reading only and test how inserts or updates behave before allowing them.

CREATE VIEW MYLIB.ACTIVE_CUSTOMERS AS
  SELECT CUSTOMER_ID, NAME, STATUS
    FROM MYLIB.CUSTOMER
   WHERE STATUS = 'A';

SELECT * FROM MYLIB.ACTIVE_CUSTOMERS;

Move values safely between SQL and RPG, and process multi-row results without stale data.

• A host variable is an RPG variable referenced by a colon in embedded SQL.
• A nullable column needs a null indicator or a nullable host structure; do not confuse NULL with zero or blank.
• A cursor has a lifecycle: DECLARE, OPEN, FETCH, handle end-of-data, then CLOSE. A singleton SELECT is not a substitute for a cursor when multiple rows are expected.

EXEC SQL
  DECLARE orderCursor CURSOR FOR
    SELECT ORDER_ID, TOTAL
      FROM MYLIB.ORDERS
     WHERE CUSTOMER_ID = :customerId;

EXEC SQL OPEN orderCursor;
// FETCH in a loop; leave when SQLSTATE = '02000'
EXEC SQL CLOSE orderCursor;

Make changes atomic, understand locks, and measure access paths before changing indexes or SQL.

• Commitment control groups related changes into a unit that can commit or roll back together.
• Isolation affects what a transaction can see and how it locks data. Choose it from the consistency requirement, not from habit.
• When a statement slows down, capture the statement, row counts, access plan, waits, and timing. An index change without measurement can make another workload worse.

EXEC SQL
  SET TRANSACTION ISOLATION LEVEL READ COMMITTED;

EXEC SQL UPDATE MYLIB.ORDERS
            SET STATUS = 'P'
          WHERE ORDER_ID = :orderId;

// Check SQLSTATE, then COMMIT or ROLLBACK as one unit