Db2 Request to SQL: Translate Business Rules into SELECT Statements



Translate the request before writing the SELECT.

A request like "show active accounts with balances over 10,000" is not SQL yet. Before a COBOL program can use it in embedded SQL, the developer must identify the result columns, source tables, join rules, predicates, ordering, and host variables. Most bad SELECT statements start with a vague request that was never translated into database terms.

This refresh keeps the original beginner intent but rewrites it for Db2 and mainframe developers. The goal is not SQL tuning; the goal is turning a business question into a precise, testable SQL statement.

Start With the Business Request

Write the request in one sentence and remove soft wording. Replace words like "current," "recent," "large," or "active" with the actual rule used by the application.

Vague wording	Database rule
current customers	CUST_STATUS = 'A'
recent payments	PAYMENT_DATE >= :WS-FROM-DATE
large balances	BALANCE > 10000
open accounts	CLOSE_DATE IS NULL

Identify the Columns to Return

The SELECT list should contain the columns the program actually needs. If the report needs account number, customer name, and balance, name those columns. Do not use SELECT * in production COBOL cursors unless there is a rare, documented reason.

SELECT A.ACCT_NO,
       C.CUST_NAME,
       A.BALANCE

Choosing only the needed columns reduces data movement and keeps the COBOL host variable list clear.

Find the Source Tables

Next, map each result column to a table. If columns come from more than one table, write the join rule before writing the final query.

Needed value	Column	Table
Account number	ACCT_NO	ACCT_BAL
Balance	BALANCE	ACCT_BAL
Customer name	CUST_NAME	CUSTOMER
Customer status	CUST_STATUS	CUSTOMER

Add Join Rules

A join rule explains how rows from two tables match. Do not rely on column names that merely look similar. Confirm the relationship from the data model, DCLGEN copybooks, or existing tested SQL.

FROM ACCT_BAL A
JOIN CUSTOMER C
  ON C.CUST_NO = A.CUST_NO

If the relationship is optional, decide whether the query needs an inner join or a left join. A wrong join type can silently drop rows.

Convert Conditions into Predicates

Conditions become the WHERE clause. Each condition should have a column, operator, and value or host variable.

WHERE C.CUST_STATUS = 'A'
  AND A.BALANCE > :WS-MIN-BALANCE
  AND A.BRANCH_ID = :WS-BRANCH-ID

Use host variables for values that change at runtime. Match host variable definitions to Db2 column types. The related DB2 Host Variables and Structures article covers that copybook work.

Write the Final SELECT

After the request is translated into columns, tables, joins, and predicates, the final SQL is much easier to review.

SELECT A.ACCT_NO,
       C.CUST_NAME,
       A.BALANCE
  FROM ACCT_BAL A
  JOIN CUSTOMER C
    ON C.CUST_NO = A.CUST_NO
 WHERE C.CUST_STATUS = 'A'
   AND A.BALANCE > :WS-MIN-BALANCE
   AND A.BRANCH_ID = :WS-BRANCH-ID
 ORDER BY A.BALANCE DESC
 FETCH FIRST 100 ROWS ONLY

The SQL now says exactly what data is needed, where it comes from, how rows match, and which rows qualify.

COBOL Cursor Pattern

In COBOL, the translated SQL often becomes a cursor. Keep the cursor declaration close to the host variable definitions during review.

EXEC SQL
    DECLARE C-ACCT CURSOR FOR
    SELECT A.ACCT_NO,
           C.CUST_NAME,
           A.BALANCE
      FROM ACCT_BAL A
      JOIN CUSTOMER C
        ON C.CUST_NO = A.CUST_NO
     WHERE C.CUST_STATUS = 'A'
       AND A.BALANCE > :WS-MIN-BALANCE
       AND A.BRANCH_ID = :WS-BRANCH-ID
END-EXEC.

Check the SQLCODE after every OPEN, FETCH, and CLOSE. A correct SELECT still needs correct cursor handling.

Review Before Tuning

Before tuning a query, confirm it answers the right question. Then check access path and indexes. The companion Db2 SQL Optimization Tips for COBOL Programs guide covers EXPLAIN, RUNSTATS, and predicate checks.

• Are all selected columns required by the program?
• Are joins based on real relationships?
• Are date and status rules stated precisely?
• Are host variables typed correctly?
• Does the query need ordering or row limiting?

FAQ

What is the first step in translating a request into SQL?

Rewrite the business request as exact data rules: result columns, source tables, join relationships, and predicates.

Should I use SELECT * while translating a request?

No for production COBOL SQL. Name only the columns the program needs so host variables, performance, and review remain clear.

When should I tune the SQL?

Tune after the query answers the right business question. Then use EXPLAIN, current statistics, and index checks to review the access path.

A good SELECT starts as a precise requirement. Once the columns, tables, joins, and predicates are clear, the SQL is easier to test, bind, and maintain.