Db2 Anti-Pattern: SELECT * and Over-Fetching

What SELECT * means in Db2

In a simple query, an asterisk expands to the columns of the named table in that table's defined order. A qualified asterisk such as O.* expands only the columns exposed by correlation name O. In a join, an unqualified asterisk can expose columns from every participating table. IBM also documents an important detail: implicitly hidden columns are not included merely because the query uses an asterisk. If an application needs an implicitly hidden ROWID or generated XML document identifier, it must name that column.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
-- Broad projection: every non-hidden column from both tables
SELECT *
  FROM ORDERS AS O
  JOIN CUSTOMER AS C
    ON C.CUSTOMER_ID = O.CUSTOMER_ID
 WHERE O.ORDER_STATUS = 'OPEN';
 
-- Still broad, but limited to the ORDERS table
SELECT O.*
  FROM ORDERS AS O
 WHERE O.ORDER_STATUS = 'OPEN';
 
-- Intentional projection: the consumer's actual contract
SELECT O.ORDER_ID,
       O.ORDER_DATE,
       C.CUSTOMER_NAME,
       O.TOTAL_AMOUNT
  FROM ORDERS AS O
  JOIN CUSTOMER AS C
    ON C.CUSTOMER_ID = O.CUSTOMER_ID
 WHERE O.ORDER_STATUS = 'OPEN';

The third query is longer, but every line carries useful information. A reviewer can see what the feature returns. Db2 can describe a stable four-column result. A Java, COBOL, REST, report, or export consumer can map those columns deliberately. The query does not begin returning an internal audit field merely because a DBA adds that field to ORDERS.

Why SELECT * becomes a maintenance defect

Column additions change the exposed result

Imagine that an application prepares SELECT * FROM CUSTOMER and maps the result by ordinal position. Today the table has CUSTOMER_ID, CUSTOMER_NAME, and EMAIL_ADDRESS. A later release adds PREFERRED_LANGUAGE. A newly prepared dynamic statement can now describe four columns rather than three. A generic serializer might publish the new field without review. A CSV export gains a fourth value. A program that allocates or binds only three receiving variables can fail or mishandle the result.

IBM's Db2 for z/OS guidance specifically warns against SELECT * in static SQL because an added column can create host-variable compatibility and performance implications. Static packages and dynamic statements have different preparation and invalidation lifecycles, but neither makes a vague result contract desirable. The safe rule is to treat schema evolution and API evolution as separate decisions.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
-- Version 1 table
CREATE TABLE CUSTOMER (
    CUSTOMER_ID    INTEGER       NOT NULL,
    CUSTOMER_NAME  VARCHAR(100)  NOT NULL,
    EMAIL_ADDRESS  VARCHAR(254)
);
 
-- Fragile consumer: result shape follows the table
SELECT *
  FROM CUSTOMER
 WHERE CUSTOMER_ID = ?;
 
-- Later schema change
ALTER TABLE CUSTOMER
  ADD COLUMN PREFERRED_LANGUAGE CHAR(5);
 
-- Stable consumer: result shape remains intentional
SELECT CUSTOMER_ID,
       CUSTOMER_NAME,
       EMAIL_ADDRESS
  FROM CUSTOMER
 WHERE CUSTOMER_ID = ?;

Column order is not an application contract

Many consumers read result columns by position. That is normal for embedded SQL host variables, and some mappers, unloads, and file generators do the same. The problem is letting physical table order define those positions accidentally. Adding columns, replacing a table during a migration, redefining a view, or changing a generated query can expose a different shape. Even when a new column is appended and existing positions remain unchanged, the result count and total width still change.

An explicit SELECT list controls order independently. If a downstream file requires customer number first, name second, and balance third, write that order. When names from joined tables collide, assign aliases. This turns the projection into a visible interface rather than a side effect of DDL.

1
2
3
4
5
6
7
8
9
10
11
12
13
SELECT C.CUSTOMER_ID             AS CUSTOMER_NUMBER,
       C.CUSTOMER_NAME           AS DISPLAY_NAME,
       DECIMAL(A.CURRENT_BALANCE, 13, 2) AS BALANCE
  FROM CUSTOMER AS C
  JOIN ACCOUNT  AS A
    ON A.CUSTOMER_ID = C.CUSTOMER_ID
 WHERE A.ACCOUNT_STATUS = 'A';
 
-- Contract:
-- 1 CUSTOMER_NUMBER
-- 2 DISPLAY_NAME
-- 3 BALANCE
-- Physical table order is irrelevant.

Performance effects of over-fetching

Over-fetching is not one cost; it is repeated work across a pipeline. Db2 identifies qualifying rows, constructs the result, converts values where necessary, places output into buffers or query blocks, and returns it to the requester. A local program copies values into host variables. A distributed request moves query blocks through DDF and TCP/IP. Driver code decodes values, an object mapper creates fields, and an API may serialize them again. An unused column can consume resources at several stages.

I/O: be precise about what a narrower list can save

Selecting fewer columns does not guarantee that Db2 reads fewer table-space pages. If the access path must scan the same pages to test the WHERE clause, the page-read count might be unchanged. Narrow projection still means fewer result bytes to copy and return. More importantly, it can change the possible access paths. If an index contains every column needed for predicates and output, Db2 might use index-only access and avoid fetching data pages. SELECT * almost always defeats that possibility because ordinary indexes do not contain every table column.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
-- Assume IX_ORDER_STATUS contains:
-- (ORDER_STATUS, ORDER_DATE, ORDER_ID)
 
-- Narrow projection can be satisfied from the index,
-- subject to Db2 optimizer choice and current statistics.
SELECT ORDER_ID,
       ORDER_DATE
  FROM ORDERS
 WHERE ORDER_STATUS = 'OPEN';
 
-- SELECT * requires columns not present in that index,
-- so Db2 generally needs table data as well.
SELECT *
  FROM ORDERS
 WHERE ORDER_STATUS = 'OPEN';

Do not promise index-only access from SQL text alone. Confirm the selected access path with EXPLAIN, keep catalog statistics current, and measure getpages, synchronous reads, prefetch, and elapsed time under representative conditions.

Network and memory: row width multiplies by row count

For distributed Db2 work, unnecessary bytes cross the network. IBM recommends keeping select lists short and using WHERE, GROUP BY, and HAVING to eliminate unwanted data at the server. A 500-byte unused payload may look small, but across 20,000 rows it adds roughly 10 MB before considering protocol framing, character conversion, application objects, or JSON expansion. Wider rows fit less densely in communication and application buffers, can require more query blocks, and create more allocation and garbage-collection pressure in managed runtimes.

The remedy has two dimensions. Projection controls columns; predicates and limits control rows. A narrow SELECT that returns five million unnecessary rows still over-fetches. Likewise, FETCH FIRST 20 ROWS ONLY does not excuse returning a multi-megabyte LOB in each row.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
-- Anti-pattern: list screen retrieves all columns and all history
SELECT *
  FROM ORDER_HISTORY
 ORDER BY EVENT_TIMESTAMP DESC;
 
-- Bounded list contract
SELECT ORDER_ID,
       EVENT_TYPE,
       EVENT_TIMESTAMP
  FROM ORDER_HISTORY
 WHERE CUSTOMER_ID = ?
   AND EVENT_TIMESTAMP >= ?
 ORDER BY EVENT_TIMESTAMP DESC
 FETCH FIRST 50 ROWS ONLY;
 
-- OPTIMIZE FOR expresses an access-path expectation;
-- FETCH FIRST enforces the result limit.
SELECT ORDER_ID,
       EVENT_TIMESTAMP
  FROM ORDER_HISTORY
 WHERE CUSTOMER_ID = ?
 ORDER BY EVENT_TIMESTAMP DESC
 FETCH FIRST 50 ROWS ONLY
 OPTIMIZE FOR 50 ROWS;

CPU: conversion, copying, and parsing are work

Db2 CPU can increase when additional values must be materialized, converted, copied, or processed for transport. Client CPU can increase while a driver converts encodings and numeric representations, an object mapper populates properties, and a serializer escapes strings. Some costs depend on the driver, data type, access path, compression, and whether values are actually materialized, so avoid claiming a fixed percentage. The engineering principle is simpler: data that is never used should normally not cross the SQL boundary.

LOB columns make SELECT * much more dangerous

BLOB, CLOB, and DBCLOB columns can hold values far larger than ordinary names, dates, and codes. A document table might contain a 2 KB title and a 20 MB binary body. A list screen needs the identifier, title, type, and modified timestamp—not the body. SELECT * makes the expensive field part of the requested result even when the user never opens a document.

Separate summary retrieval from detail retrieval. Fetch the LOB only when the user asks for it. IBM documents LOB locators for supported Db2 application interfaces: a locator is a four-byte reference to the server-side value, allowing an application to obtain a portion or the complete value later. JDBC and other drivers can also provide streaming behavior. Exact lifetime and cursor rules depend on the interface, so follow the documentation for the installed driver rather than assuming every LOB API behaves alike.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
-- Summary page: no document body
SELECT DOCUMENT_ID,
       DOCUMENT_TITLE,
       MIME_TYPE,
       MODIFIED_TIMESTAMP,
       OCTET_LENGTH(DOCUMENT_BODY) AS BODY_BYTES
  FROM DOCUMENT_STORE
 WHERE OWNER_ID = ?
 ORDER BY MODIFIED_TIMESTAMP DESC
 FETCH FIRST 25 ROWS ONLY;
 
-- Detail action: retrieve the LOB for one chosen document
SELECT DOCUMENT_BODY
  FROM DOCUMENT_STORE
 WHERE DOCUMENT_ID = ?
   AND OWNER_ID = ?;

The second predicate also shows why performance and security meet here: fetch only the chosen object and keep ownership authorization in the data-access path. Do not expose LOBs, audit notes, tokens, or internal flags simply because they share a table with safe display columns.

Result sets are contracts

A result set has a count, order, labels, data types, lengths, nullability, and meaning. Consumers may compile host structures, bind columns, generate JSON properties, or use metadata to discover that shape. SELECT * delegates much of the contract to table DDL. That is backward: a table exists to support many workloads, while each query should expose the smallest stable shape required by one workload.

• Embedded SQL: align each selected expression with a compatible host variable and indicator variable where null is possible.
• JDBC or ODBC: map by intentional labels or positions and test the described metadata; do not serialize every discovered column by default.
• APIs: define public response fields separately from internal table fields so adding a database column does not publish it.
• Reports and files: specify column order and aliases, and version a layout when the contract truly changes.

Views can provide an abstraction layer, but their definitions should also be intentional. An asterisk in ad hoc dynamic exploration is different from an asterisk embedded in production code. Even if a view definition captures a known set of columns at creation, an explicit list is easier to review and protects the design from ambiguity when the view is replaced.

A practical refactoring method

1. Find SELECT * and qualified asterisks in application SQL, stored procedures, views, report definitions, and generated-query configuration.
2. Trace what the consumer actually reads, including fields used for mapping, authorization, ordering, links, and conditional display.
3. Replace the asterisk with those columns in a deliberate order and add aliases where the external name should differ from the physical name.
4. Remove unnecessary rows with correct predicates. Add a true row limit for bounded user interfaces, but do not use a limit to hide missing business filters.
5. Move LOB retrieval to an on-demand detail query and choose locator or streaming techniques appropriate to the application interface.
6. Rebind or reprepare as required, run contract tests, compare EXPLAIN output, and measure rows, bytes, getpages, CPU, elapsed time, and network behavior.

Avoid a mechanical replacement that lists every existing column. That freezes the same over-fetching under a longer spelling. The goal is not merely “remove the star”; it is “define the smallest correct result.”

When SELECT * can be reasonable

Rules need scope. An analyst inspecting a small table interactively may use SELECT * to learn its contents. A database administration tool may intentionally discover unknown dynamic result metadata. A diagnostic query may need a complete row for one key. IBM documentation also identifies dynamic SQL and view definitions as common contexts for the asterisk. These cases have a consumer that expects discovery or has tightly bounded data.

That exception does not justify SELECT * in a high-volume transaction, shared API, embedded static statement, reusable report, or list screen. Ask two questions: “Does the consumer intentionally support a changing result shape?” and “Does it genuinely use all returned data?” If either answer is no, use an explicit list.

Authoritative IBM references

IBM's Retrieving data by using the SELECT statement documentation explains asterisk expansion, implicitly hidden columns, explicit column ordering, and the static SQL compatibility warning. IBM's distributed application performance guidance recommends short select lists and server-side filtering and documents LOB locator techniques. Always consult the documentation for your installed Db2 release and application driver.

Explain It Like I'm Five

Imagine ordering lunch by saying, “Bring me everything in the kitchen.” You only wanted a sandwich, but the waiter carries soup, boxes, a huge cake, and cleaning supplies. The trip is slower, your table is crowded, and tomorrow the chef might add another item that you did not expect. SELECT * says “bring every column.” An explicit SELECT list says, “Please bring ORDER_ID, ORDER_DATE, and TOTAL_AMOUNT.” Db2 and your application now know exactly what belongs on the tray.

Exercises

1. Rewrite SELECT * FROM EMPLOYEE WHERE DEPARTMENT_ID = ? for a directory screen that displays employee number, last name, first name, and work email.
2. A query returns 10,000 rows with an unused 2,000-byte notes column. Estimate the raw unused bytes before protocol and object overhead, then explain where CPU or memory can be consumed after Db2 returns them.
3. Design separate list and detail queries for a table containing DOCUMENT_ID, TITLE, MIME_TYPE, CREATED_AT, OWNER_ID, and a BLOB named CONTENT.
4. Add a hypothetical column to a table used by a positional CSV export. Describe how SELECT * changes the file and how an explicit list protects the contract.
5. Use EXPLAIN to compare SELECT * with a narrow projection whose columns are covered by an index. Record whether index-only access is selected instead of assuming it.
6. Review one production query for both dimensions of over-fetching: unused columns and unnecessary rows. Propose predicates, projection, ordering, and a justified row limit.