IBM DB2: Asking for Data Without Specifying Every Retrieval Step

“Which customers in Flagstaff have at least $1,000 in unpaid invoices?”

The question is perfectly ordinary. The retrieval procedure can be less ordinary: find customer records, find invoices, match identifiers, discard paid items, add amounts, and choose an order that doesn’t keep a mainframe busy until lunch.

IBM DB2 helped make those two problems separable. SQL described the answer; the database chose a way to obtain it. That division sounds comfortable until the chosen route turns out to be expensive. The abstraction delegates work, including some decisions worth inspecting.

DB2’s place in this story is commercial engineering in IBM’s mainframe environment. The relational model, SQL, and the research prototype System R had already supplied much of the intellectual groundwork.

An announcement, a product, and a longer research history

IBM announced Database 2—DB2—on June 7, 1983, for MVS/XA and MVS/370. The announcement presented SQL as the language for defining, retrieving, manipulating, and controlling data. It also described customer testing ahead of general availability.¹

Dates need care here. The announcement planned general availability for the third quarter of 1984. IBM’s 2023 anniversary account instead identifies a 1985 launch of V1.1, while other IBM histories loosely describe a 1983 introduction or shipment. This article uses 1983–1985 to distinguish the documented announcement from subsequent commercial rollout; the planned date is not proof of actual delivery.¹²³

IBM identifies Don Haderle as a central figure in DB2’s development, often calling him its father. The work grew from IBM’s database research and development community in San Jose, California.²⁴

The ideas behind the product were older:

Person or team	Contribution	Why it mattered
Edgar F. Codd	Published the relational-model paper in June 1970	Separated the user’s view of data from physical storage details
Donald Chamberlin and Raymond Boyce	Presented SEQUEL in 1974, in the lineage of SQL	Developed a language for expressing operations on tabular data
Patricia Selinger and System R colleagues	Developed cost-based access-path selection	Made high-level requests practical by choosing execution strategies
Don Haderle and the DB2 development organization	Advanced the commercial DB2 product	Carried relational ideas into IBM’s enterprise product environment

These contributions were complementary. A data model, a language, an optimizer, and a deployable database system are different engineering achievements.⁵⁶⁷²

The essential separation: data meaning and data location

Codd’s 1970 paper argued that users and applications should be protected from changes in the internal representation of data. A database should not force every application to understand precisely how its records are organized in storage.⁵

Consider the difference between two instructions:

• Find the customer file, follow its invoice links, visit each record, and add qualifying balances.
• Return each qualifying customer’s name and total unpaid amount.

The first describes a route. The second describes a result.

That is the appeal of declarative querying. The application still needs to understand the meaning of customer identifiers, amounts, and invoice status. It can delegate much of the physical retrieval work.

This separation creates room for change. A database administrator might add an index or reorganize storage while preserving the query’s meaning. The application does not necessarily need a new traversal routine.

It is also an abstraction with boundaries. SQL does not remove the need to design tables, understand relationships, or investigate poor performance. It relocates responsibilities rather than making them disappear.

Tables are not merely spreadsheets on a server

A relational system presents data through tables and relates records through values. IBM’s contemporary description emphasizes that relationships can be established through the information in those tables rather than being permanently embedded as navigation paths.⁸

Here is an original miniature example.

CUSTOMERS

CUSTOMER_ID	CUSTOMER_NAME	CITY
101	Pine Workshop	Flagstaff
102	Desert Print	Phoenix
103	Aspen Repairs	Flagstaff

INVOICES

INVOICE_ID	CUSTOMER_ID	AMOUNT	STATUS
11	101	900	OPEN
12	101	300	OPEN
13	102	1900	OPEN
14	103	250	OPEN
15	101	5000	PAID

The customer identifier connects an invoice to a customer. A customer can have several invoices, and each invoice row identifies its customer.

The tables are a teaching example, not recovered data from an early DB2 installation. Their structure makes an important idea visible: the join uses matching values, not the visual positions of rows.

A join turns separate facts into an answer

The question from the opening can be expressed as:

SELECT C.CUSTOMER_ID,
       C.CUSTOMER_NAME,
       SUM(I.AMOUNT) AS UNPAID_TOTAL
FROM CUSTOMERS C, INVOICES I
WHERE C.CUSTOMER_ID = I.CUSTOMER_ID
  AND C.CITY = 'Flagstaff'
  AND I.STATUS = 'OPEN'
GROUP BY C.CUSTOMER_ID, C.CUSTOMER_NAME
HAVING SUM(I.AMOUNT) >= 1000;

This is an illustrative SQL query using a traditional comma-separated table list and a join condition in the WHERE clause. It is not a transcription from a 1983 DB2 manual or a claim that every part of the formatting reproduces a particular early release.

Read it in terms of meaning:

Part	Job in this example
FROM	Identifies the two tables
Matching identifiers in WHERE	Relates each invoice to its customer
City and status conditions	Keeps Flagstaff customers’ open invoices
GROUP BY	Groups qualifying rows by customer
SUM	Adds the invoice amounts
HAVING	Keeps groups whose total reaches the threshold
SELECT	Specifies the returned columns

The result is:

CUSTOMER_ID	CUSTOMER_NAME	UNPAID_TOTAL
101	Pine Workshop	1200

Desert Print is outside the selected city. Aspen Repairs has too little outstanding. The paid invoice contributes nothing.

Notice what the query leaves unspecified. It does not tell the database which table to read first, which index to use, or which physical join procedure to execute.

Those omissions are the point.

The optimizer supplies the missing plan

System R, IBM’s research prototype, demonstrated how this division could work. The 1979 access-path paper by Selinger, Morton Astrahan, Chamberlin, Raymond Lorie, and Thomas Price describes choosing access paths and join order for SQL requests.⁷

Patricia Selinger in April 2014. Her System R work helped make cost-based query optimization practical. Photo credited to Jacek Ambroziak and Sam Madden, via Wikimedia Commons, CC BY-SA 4.0. This is a later portrait; the supplied image is already a crop.

For the example query, possible strategies might include reading invoices broadly or first identifying customers in the selected city and looking up their invoices.

The optimizer compares estimated costs among candidate plans. The goal is an economical way to obtain the same logical answer, not a different answer.⁷

A general teaching model is:

$$\widehat C(P) = w_{\mathrm{IO}}\widehat B(P) + w_{\mathrm{CPU}}\widehat T(P).$$

Here, $P$ is a candidate plan, $\widehat B$ estimates page accesses, $\widehat T$ estimates processing work, and the weights express their relative cost.

This formula illustrates cost-based planning. It is not the exact cost formula of a particular DB2 release. Estimates and the set of plans considered limit the decision: an optimizer does not guarantee the fastest imaginable plan.

IBM credits Selinger’s work with helping make the relational approach perform well enough to be useful.⁹

Why an index is not always faster

An index offers an organized route to selected records. It also has a cost: navigating it and fetching the referenced data.

Suppose a hypothetical table occupies 10,000 data pages. Compare these deliberately simplified estimates:

Scenario	Full scan	Index route
A narrow request	10,000 page accesses	200 index-page accesses + 500 data-page accesses = 700
A broad request	10,000 page accesses	1,200 index-page accesses + 9,000 data-page accesses = 10,200

For the narrow request:

$$\frac{10{,}000}{700}\approx14.29.$$

That is about fourteen times as many estimated page accesses for the scan—not a claim of fourteen times the elapsed time.

For the broad request, the index route performs more estimated accesses than the scan. Visiting almost all the data through an additional structure can erase its advantage.

These numbers are invented to explain the tradeoff. Real costs depend on caching, physical organization, repeated accesses, processing work, and the difference between sequential and scattered reads.

The lesson is that “use an index” is not a complete performance strategy. The useful question is: how much work will each available plan perform for this request?

Estimates can be wrong

To compare plans, the system needs an idea of how much data each step will produce. The number of rows is called cardinality; the fraction retained by a condition is often discussed as selectivity.

The optimizer therefore relies on estimates. This general explanation extends the System R access-path idea without assigning modern statistical features to the first DB2.⁷

Imagine a dataset in which almost every Flagstaff customer has open invoices, while customers elsewhere rarely do. Treating city and invoice status as unrelated characteristics can misrepresent how many rows their combined conditions will return.

A query can therefore be logically correct and still receive a poor execution plan.

That distinction matters when diagnosing a slow report. Changing its meaning to make it run faster is not equivalent to improving its execution.

Research software had to become enterprise software

The original announcement positioned DB2 alongside existing IBM environments. It described access from transaction-processing systems, interactive users, and batch jobs. Query Management Facility offered an accompanying route for business queries and reports.¹

An IBM 3081 system, illustrating the mainframe setting relevant to early DB2. This photograph does not establish that the pictured installation ran DB2. Photo: Norsk Teknisk Museum; crop supplied by ThisIsNotABetter, via Wikimedia Commons, CC BY-SA 3.0.

A research lineage also addressed repeated execution. A 1981 System R paper describes precompiling SQL embedded in PL/I or COBOL so that repeated operations did not have to redo all the work of parsing and choosing access paths.¹⁰

That illustrates another part of the engineering challenge. A database must support both a newly asked question and an operation that runs repeatedly.

The product continued to mature after its initial rollout. IBM’s anniversary account identifies DB2 version 2 in 1988 as an important milestone for online transaction processing.² The original announcement should not be treated as proof that the system already had every capability associated with later DB2.

The query can be right while the answer is wrong

There is a separate risk from a poor execution plan: an incorrectly specified relationship. Remove the identifier comparison from our example, and every qualifying customer can combine with every qualifying invoice. A machine that runs that query perfectly has faithfully answered a foolish question.

Even a proper identifier match needs a sound data model. If the customer table contains two rows with the same identifier, matching an invoice against both rows can multiply the amount before aggregation. The duplicate may look harmless when browsing a table. In a sum it acquires financial ambitions.

This is why understanding declarative software means understanding the meaning of the request. Handing the retrieval route to the database does not hand it the meaning of “customer,” “paid,” or “outstanding.” Those are facts and policies the designer has to represent accurately.

Our example also uses whole-number amounts for readability. A working billing system must decide how monetary values are stored, what a reversal means, and whether an invoice’s status can change while a report runs. Those are independent requirements. Adding another index will not answer them.

A mainframe product is more than a clever query

The original announcement’s pairing of DB2 with reporting and extraction tools is revealing. Organizations already had data and working systems. A new database had to offer a route from those systems to new queries while allowing existing work to continue.¹

That makes the commercial achievement less theatrical and more interesting. Database research supplied a new way to represent and retrieve information. Product engineering had to make it fit institutional routines, existing programs, and the economics of repeated work. A bank does not pause its ledger merely because somebody has developed a more elegant abstraction.

DB2’s early history is consequently a story about making flexibility operational. The application can preserve the question while storage and retrieval strategies evolve. The bargain holds when the question is precise, the estimates are useful, and people can investigate the plan when the machinery becomes conspicuously busy.

SQL leaves the database room to choose. It leaves the author responsibility for what was asked.

A short timeline

Date	Milestone
June 1970	Codd publishes the relational-model paper
May 1974	Chamberlin and Boyce publish the SEQUEL paper
May 1979	Selinger and colleagues publish System R’s access-path work
June 7, 1983	IBM announces DB2 for MVS
1984, planned	The announcement’s target for general availability
1985, retrospective account	IBM’s 2023 anniversary article dates the V1.1 launch
1988	IBM identifies version 2 as an important transaction-processing milestone

Sources and further reading

Footnotes

1. IBM, announcement letter 283-159, June 7, 1983, preserved overview of DB2, QMF, and Data Extract. A later reproduction of a contemporary announcement; explicitly distinguishes customer testing from planned general availability. ↩ ↩² ↩³ ↩⁴

2. IBM, “Celebrating 40 years of Db2”, 2023. Supports the 1983 announcement, 1985 V1.1 launch account, Haderle’s role, and 1988 milestone. Its dating of Codd’s famous published paper is not followed here; the publication record below establishes June 1970. ↩ ↩² ↩³ ↩⁴

3. IBM, “The relational database”. Included to make the chronology discrepancy visible: this account describes a 1983 shipment, whereas the anniversary account distinguishes announcement and launch. ↩

4. IBM, “Db2 and 50 Years of Relational Database Design”, 2020. Describes early DB2 development in San Jose. Promotional superlatives are not adopted in this article. ↩

5. E. F. Codd, “A Relational Model of Data for Large Shared Data Banks”, Communications of the ACM, June 1970, pp. 377–387. Publisher record. ↩ ↩²

6. Donald D. Chamberlin and Raymond F. Boyce, “SEQUEL: A Structured English Query Language”, SIGFIDET, May 1974. The URL reproduces a typo in IBM’s catalog title. ↩

7. P. Griffiths Selinger, M. M. Astrahan, D. D. Chamberlin, R. A. Lorie, and T. G. Price, “Access Path Selection in a Relational Database Management System”, SIGMOD, May 1979. Publisher record. The article distinguishes System R research from a claim about exact early DB2 internals. ↩ ↩² ↩³ ↩⁴

8. IBM Research, “Overview of Relational Databases”, cataloged December 1985. Describes tables and relationships through values in SQL/DS and DB2. ↩

9. IBM, Patricia Selinger historical profile. ↩

10. D. D. Chamberlin and colleagues, “Support for Repetitive Transactions and Ad Hoc Queries in System R”, ACM Transactions on Database Systems, March 1981. ↩