Db2 Buffer Pool Guide for COBOL and SQL Performance



Db2 buffer pools cache pages before disk I/O.

A nightly COBOL job can run slowly even when the SQL text has not changed. One common reason is that Db2 is reading too many table or index pages from disk instead of finding them in memory. A Db2 buffer pool is the memory area that holds those pages while SQL statements read or change data.

This refresh corrects the old page's mixed platform wording and focuses on Db2 for z/OS. Buffer pools are not application code, but application teams still need to understand them because access paths, table-space design, index usage, and batch volume all affect buffer pool pressure.

What Is a Db2 Buffer Pool?

A Db2 buffer pool is a virtual storage area used to cache pages from table spaces and index spaces. When a program executes SQL, Db2 requests pages. If the page is already in the buffer pool, Db2 can use it from memory. If not, Db2 must read the page from disk.

That difference matters. Memory access is much faster than synchronous disk I/O. A bad access path that scans millions of pages can put pressure on the buffer pool and slow other work running in the same subsystem.

Why Buffer Pools Matter to COBOL Programs

COBOL code does not name the buffer pool directly in embedded SQL. The effect shows up through elapsed time, CPU use, wait time, and batch-window pressure. A cursor that fetches 10,000 rows by index might behave well. The same cursor after a predicate change might scan a large table space and drive many GETPAGE requests.

Application symptom	Buffer pool angle	First check
Batch job elapsed time doubles after data growth.	More pages are read or scanned.	EXPLAIN access path and object statistics.
Online transaction waits during peak hours.	Hot pages may be competing with scan-heavy work.	Buffer pool display and high-volume SQL statements.
Query reads only a few rows but uses many pages.	Index or clustering may not match the access pattern.	Predicate indexability and clustering ratio.

GETPAGE, Hit Ratio, and Disk Reads

A GETPAGE is Db2 asking for a page. A high GETPAGE count is not automatically bad; a heavily used table can produce many logical page requests. The expensive case is when many requests require physical I/O or when one query scans far more pages than the business result needs.

Use hit ratio carefully. A high ratio can still hide a wasteful query if the system performs millions of unnecessary GETPAGEs. A low ratio can be normal for a one-time sequential scan. Tie the metric back to SQL volume, object size, and elapsed time.

Buffer Pool Page Sizes

Db2 for z/OS uses buffer pools for different page sizes. A table space or index space must be assigned to a buffer pool with a compatible page size. Common page sizes are 4 KB, 8 KB, 16 KB, and 32 KB.

Page size	Typical use	Developer impact
4 KB	Many standard table spaces and indexes.	Good fit for narrow rows and common OLTP access.
8 KB or 16 KB	Wider rows or larger index pages.	Can reduce overflow pressure but changes page economics.
32 KB	Very wide rows, LOB-related designs, or special cases.	Should be chosen deliberately with DBA review.

Commands DBAs Use

Application developers usually do not alter buffer pools, but they should recognize the commands used during investigation. Site standards vary, and production changes belong to the DBA team.

-- Display buffer pool activity
-DISPLAY BUFFERPOOL(BP0) DETAIL

-- Example of a DBA-controlled change pattern
-ALTER BUFFERPOOL(BP8K0) VPSIZE(120000)

Do not paste tuning commands into production from a tutorial. The right buffer pool size and thresholds depend on subsystem memory, workload mix, page size, object assignment, and service goals.

Object Assignment Matters

Table spaces and index spaces are assigned to buffer pools. A large reporting table, a heavily updated account table, and a hot index might not belong in the same pool if their access patterns fight each other.

• Random index lookups benefit from having hot index pages in memory.
• Large sequential scans can push useful pages out of a shared pool.
• Work files and temporary activity can affect sort-heavy workloads.
• High-update objects need attention to write thresholds and changed-page handling.

The next related post in this sequence, DB2 Table Spaces, should cover how table-space design connects to buffer pool assignment.

What Developers Should Check Before Blaming the Buffer Pool

Many buffer pool complaints are really SQL access-path problems. Before asking for a buffer pool change, collect evidence from the statement and object design.

• Run EXPLAIN for the statement and confirm index access versus table-space scan.
• Check whether RUNSTATS is current for the table, index, and key columns.
• Review predicates for functions, arithmetic, mismatched host variable types, and non-indexable patterns.
• Check whether the program fetches more rows than it uses.
• Compare test data volume with production data volume before trusting elapsed time.

The refreshed Db2 SQL Optimization Tips for COBOL Programs guide covers these access-path checks in more detail.

Buffer Pool Tuning Is a DBA Task

Developers can provide the failing SQL, package name, plan name, object names, row counts, and timing. DBAs can then review buffer pool statistics, object placement, thresholds, and memory tradeoffs.

Developer provides	DBA reviews
SQL text, package, collection, and job or transaction name.	Buffer pool activity and object assignment.
Before/after elapsed time and row counts.	Synchronous reads, writes, thresholds, and page residency.
EXPLAIN output and RUNSTATS date.	Whether memory tuning or SQL tuning is the better fix.

FAQ

Does a larger Db2 buffer pool always improve performance?

No. More memory can help when the workload is I/O-bound and pages can be reused, but it will not fix a bad access path that scans too many pages.

Can a COBOL program choose a buffer pool?

No. The program issues SQL. Buffer pool use follows the table space or index space that Db2 accesses for that SQL statement.

What should I collect before reporting a buffer pool issue?

Collect the SQL statement, package or job name, EXPLAIN output, row counts, elapsed time, object names, and whether the issue started after data growth, bind, RUNSTATS, or a code change.

A buffer pool problem is easiest to solve when the SQL evidence and Db2 subsystem evidence are reviewed together. Start with the statement, then decide whether the fix belongs in SQL, statistics, object design, or buffer pool tuning.

Db2 User-Defined Functions: SQL and External UDF Guide



Db2 UDFs keep reusable logic close to SQL.

A payroll query that repeats the same allowance calculation in ten COBOL programs is hard to test and easy to change in one place only. A Db2 user-defined function can move that calculation behind one SQL routine, so the application calls PAYROLL.NET_PAY(...) instead of carrying copy-pasted expression logic through every cursor.

This refresh keeps the original topic, but narrows it to what a Db2 for z/OS developer needs: when a UDF belongs in SQL, how scalar and table functions differ, when an external COBOL or C routine is justified, and which options can affect performance or security.

What Is a Db2 User-Defined Function?

A user-defined function, or UDF, is a Db2 routine that you create with CREATE FUNCTION and then call from SQL. IBM's Db2 for z/OS documentation describes CREATE FUNCTION as the statement that registers a user-defined function with the database server. The function can return a single scalar value or a table, depending on how it is defined.

From an application point of view, the most common use is a scalar call in a SELECT, WHERE, or VALUES statement.

SELECT EMPNO,
       PAYROLL.NET_PAY(SALARY, BONUS, TAX_CODE) AS NET_AMOUNT
  FROM PAYROLL.EMP_PAY
 WHERE PAYROLL.ACTIVE_EMP(STATUS, TERM_DATE) = 'Y';

That example hides the rule behind a named function. It also creates a contract. If the rule changes, the DBA and development team can review the function definition instead of searching every batch program for a similar expression.

Use a UDF for Reusable SQL Logic

A UDF is a good fit when the result belongs inside a SQL expression. Date normalization, account-number formatting, small code translations, financial rounding rules, and common eligibility checks are typical examples.

Use case	Good UDF fit?	Reason
Return a normalized branch code for each account row.	Yes	The result is a scalar value used directly in SQL.
Calculate net pay from salary, allowance, and tax code.	Usually	The same expression is reused by reports and batch programs.
Post an accounting transaction across several tables.	No	That is transaction logic. Use a stored procedure or application service.
Return a filtered set of rows from a table-like routine.	Maybe	An SQL table function can work, but compare it with a view or direct query.

Scalar, Table, SQL, External, and Sourced Functions

The old post listed UDF categories but did not explain when each matters. Db2 supports several function forms, and the choice affects how the function is written, invoked, secured, and tuned.

Function type	What it returns	Typical use
SQL scalar function	One value	Reusable expression written in SQL PL or a single return expression.
SQL table function	A row set	Table-like result that can be referenced in a query.
External scalar function	One value	Logic implemented in COBOL, C, Java, PL/I, or Assembler.
External table function	A row set	External routine returns rows to the invoking SQL statement.
Sourced function	Depends on source function	Reuse an existing built-in or user-defined function with a distinct type or different signature.

For a COBOL application team, start with a SQL scalar function when the rule can be stated in SQL. Move to an external function only when the logic already exists in a tested language routine or needs facilities that SQL cannot express cleanly.

Create a Simple SQL Scalar UDF

The simplest UDF is a scalar SQL function. The function below standardizes a status value before the calling program compares it. A rule like this is small enough to keep in SQL and easy to test with VALUES.

CREATE FUNCTION APP.CLEAN_STATUS
       (P_STATUS CHAR(1))
  RETURNS CHAR(1)
  LANGUAGE SQL
  DETERMINISTIC
  NO EXTERNAL ACTION
  RETURN
    CASE
      WHEN P_STATUS IN ('A', 'I', 'S') THEN P_STATUS
      WHEN P_STATUS IS NULL            THEN 'U'
      ELSE 'X'
    END;

Test it before wiring it into a batch cursor.

VALUES APP.CLEAN_STATUS('A');
VALUES APP.CLEAN_STATUS(' ');
VALUES APP.CLEAN_STATUS(CAST(NULL AS CHAR(1)));

If the function is deterministic, tell Db2. If it reads tables, calls an external service, or depends on the current time, do not mark it deterministic just because that looks faster. The declaration should match the real behavior.

Call a UDF from COBOL SQL

A COBOL program calls a Db2 UDF inside embedded SQL the same way it calls many built-in functions: qualify the function when needed, pass host variables, and fetch the result into a compatible host variable.

EXEC SQL
    SELECT APP.CLEAN_STATUS(STATUS)
      INTO :WS-CLEAN-STATUS
      FROM CUSTOMER_STATUS
     WHERE CUSTOMER_ID = :WS-CUSTOMER-ID
END-EXEC.

Keep the host variable data type close to the UDF return type. A CHAR(1) result belongs in a one-byte character field, not a loosely sized display field that later gets compared with padded values. The related DB2 Host Variables and Structures guide is the right companion when copybook definitions are the problem.

External UDFs Need More Operational Control

An external UDF registers code that lives outside the SQL function body. IBM's external scalar function reference notes that the statement registers an external scalar function and that a scalar function returns one value each time it is invoked. External definitions can specify options such as language, parameter style, WLM environment, security behavior, null handling, and SQL data access.

CREATE FUNCTION APP.RISK_SCORE
       (P_ACCT_NO CHAR(12), P_BALANCE DECIMAL(13,2))
  RETURNS INTEGER
  EXTERNAL NAME 'RSKSCORE'
  LANGUAGE COBOL
  PARAMETER STYLE SQL
  FENCED
  DETERMINISTIC
  NO SQL
  RETURNS NULL ON NULL INPUT
  NO EXTERNAL ACTION
  WLM ENVIRONMENT RISKUDF;

That definition is not just syntax. It tells the operations team where the routine runs, whether it can read SQL data, whether null input should call the routine, and whether the function has side effects outside Db2.

UDF or Stored Procedure?

Do not use a UDF as a hidden transaction processor. If the logic updates several tables, writes audit rows, sends messages, or owns commit boundaries, a stored procedure is usually the cleaner object. A UDF should behave like a function: input values in, result value or rows out.

Question	Choose UDF	Choose stored procedure
Can it be used inside a SELECT expression?	Yes	Usually no
Does it return one calculated value?	Yes	Not the main reason
Does it perform a business transaction?	No	Yes
Does COBOL call it with CALL?	No, it appears in SQL	Yes, SQL CALL is normal

For the next topic in this cleanup list, the DB2 Stored Procedure page should cover transaction-style routines, result sets, parameters, and deployment. This UDF page should stay focused on function behavior inside SQL.

Performance Checks Before You Add a UDF

A UDF can make SQL easier to read, but it can also hide work from the person reading the query. Before using a UDF in a high-volume cursor, check how often it runs and whether it blocks index-friendly predicates.

• Use EXPLAIN on the SQL that calls the function.
• Avoid wrapping indexed columns in a function inside a WHERE clause unless you have tested the access path.
• Mark DETERMINISTIC, NO SQL, and NO EXTERNAL ACTION only when they are true.
• For external UDFs, confirm WLM environment, RACF permissions, load module availability, and Language Environment setup.
• Test null handling with RETURNS NULL ON NULL INPUT or CALLED ON NULL INPUT.
• Do not give a UDF the same name as a built-in function unless the function signature and SQL path behavior are reviewed.

The companion Db2 SQL Optimization Tips for COBOL Programs article covers access-path checks in more detail.

Deployment Checklist

• Choose a schema that matches site naming rules and does not collide with system schemas.
• Use a specific name for functions that may be overloaded.
• Grant EXECUTE only to the packages, roles, or IDs that need the function.
• For static COBOL SQL, confirm bind or rebind steps after new SQL references are introduced.
• Keep source, DDL, grants, WLM definition, and load module promotion in the same change record.
• Prepare rollback DDL before changing a function used by nightly batch jobs.

FAQ

Can a Db2 UDF return more than one row?

Yes. A table function can return a set of rows. A scalar function returns one value each time it is invoked.

Can COBOL code be used in a Db2 UDF?

Yes, an external UDF can reference a routine written in COBOL when the site supports the required Language Environment, WLM, security, and deployment setup.

Should a UDF replace a stored procedure?

No. Use a UDF for reusable logic inside SQL expressions. Use a stored procedure for transaction-style work, multiple statements, result sets, or business operations called with SQL CALL.

A UDF is worth creating when it gives SQL a clear name for a repeated rule and behaves predictably under production volume. If it hides expensive work inside every fetched row, fix the design before it reaches the batch window.

Db2 SQL Optimization Tips for COBOL Programs



Db2 SQL tuning starts with access-path evidence.

A batch job named ACCTPOST can look healthy in test and still burn CPU in production when a predicate stops matching an index or catalog statistics are old. In Db2 for z/OS, SQL tuning is not guesswork. Start with the SQL text, host-variable definitions, catalog statistics, and the access path that Db2 selected.

This guide keeps the original post's intent - practical SQL performance tips for Db2 developers - but updates it for a COBOL + Db2 audience. It focuses on what application developers can change safely before asking a DBA to add an index or change database design.

Start With EXPLAIN, Not Opinions

Before rewriting a query, capture the access path. IBM describes EXPLAIN as the Db2 statement that records access-path information for explainable SQL statements in EXPLAIN tables. Use it to check whether Db2 chose an index access, tablespace scan, sort, nested-loop join, merge scan join, or other path.

For dynamic SQL, also look at statement-cache information when it is available. For static COBOL SQL, compare access paths before and after a package rebind, especially after RUNSTATS, index changes, or predicate rewrites.

EXPLAIN PLAN SET QUERYNO = 101
FOR
SELECT CUST_NO,
       BALANCE
  FROM ACCT_BAL
 WHERE BRANCH_ID = 'D01'
   AND STATUS    = 'A'
   AND BALANCE   > 10000;

Do not tune from elapsed time alone. Elapsed time can change because of locking, buffer pool residency, batch window load, or another job holding resources. EXPLAIN tells you what Db2 planned to do.

Keep RUNSTATS Current

The optimizer depends on statistics. IBM's Db2 documentation says RUNSTATS gathers information about table spaces, indexes, and partitions, records that information in the Db2 catalog, and uses it during access-path selection at bind time.

Old statistics can make a good SQL statement look bad. A table that had 10,000 rows last year may have 40 million rows now. A column that used to have five status values may now be skewed, with 90 percent of rows in one status. Without current statistics, Db2 can choose an access path that looks cheap on paper and expensive in production.

Symptom	Check	Likely action
Query changed from index access to scan after data growth.	Table, index, and column statistics date.	Run RUNSTATS and rebind static packages when your site process requires it.
Predicate on a skewed column picks the wrong access path.	Frequency and cardinality statistics.	Collect distribution statistics for the right columns or column groups.
Join order looks wrong.	Cardinality of join columns and indexes.	Refresh statistics and review index design.

Make Predicates Indexable

The fastest SQL is often the statement that lets Db2 reject rows early. Avoid wrapping indexed columns in scalar functions inside the WHERE clause when a range predicate can express the same rule.

-- Weak predicate for an index on HIREDATE
WHERE YEAR(HIREDATE) = 2026

-- Better range predicate
WHERE HIREDATE >= DATE('2026-01-01')
  AND HIREDATE <  DATE('2027-01-01')

The second form leaves HIREDATE by itself on the left side of the comparison. If a useful index exists and statistics support it, Db2 has a better chance of using matching index access.

Move Arithmetic Away From Indexed Columns

Arithmetic on an indexed column can stop Db2 from using the index in the way you expect. Move the calculation to the constant or host-variable side when the business rule is the same.

-- Weak
WHERE SALARY * 1.10 > :WS-LIMIT

-- Better
WHERE SALARY > :WS-LIMIT / 1.10

Do this only when the rewrite keeps the same rounding and data type behavior. For packed-decimal or decimal columns, confirm the scale of the host variables and test boundary values such as exactly 50000.00.

Select Only the Columns the Program Uses

SELECT * makes the program read columns that it may never move to an output record. Extra columns can increase I/O, enlarge sort work, prevent index-only access, and make FETCH handling slower in COBOL.

-- Weak
SELECT *
  FROM CUSTOMER
 WHERE CUST_NO = :WS-CUST-NO

-- Better
SELECT CUST_NO,
       CUST_NAME,
       STATUS
  FROM CUSTOMER
 WHERE CUST_NO = :WS-CUST-NO

If the selected columns are all in an index, Db2 may be able to avoid reading the data page. For index design basics, see DB2 Indexing.

Use DISTINCT Only When Duplicates Are Real

DISTINCT can require sort or duplicate elimination work. Do not add it as a defensive habit. If duplicates appear because a one-to-many table is joined only to test existence, an EXISTS predicate can express the intent more clearly.

-- Often expensive if many project rows exist per employee
SELECT DISTINCT E.EMPNO,
       E.LASTNAME
  FROM EMP E,
       EMPPROJACT P
 WHERE P.EMPNO = E.EMPNO

-- Clear existence check
SELECT E.EMPNO,
       E.LASTNAME
  FROM EMP E
 WHERE EXISTS
       (SELECT 1
          FROM EMPPROJACT P
         WHERE P.EMPNO = E.EMPNO)

Do not assume the rewrite is faster every time. Explain both statements. Data distribution, indexes, and query transformation can change the result.

Test IN and EXISTS Both Ways

IN and EXISTS can return the same rows while giving Db2 different rewrite choices. For a small lookup list, IN may be clear. For a correlated existence check, EXISTS often states the access rule better.

SELECT E.EMPNO,
       E.LASTNAME
  FROM EMP E
 WHERE EXISTS
       (SELECT 1
          FROM DEPARTMENT D
         WHERE D.MGRNO = E.EMPNO
           AND D.DEPTNO LIKE 'D%')

For production tuning, compare EXPLAIN output and real test data. A rewrite that wins on a 10-row test table may lose when the production table has millions of rows and skewed department codes.

Match COBOL Host Variables to Db2 Columns

A COBOL host variable should match the Db2 column type as closely as possible. Mismatched types can force conversion work and can affect predicate matching. Use DCLGEN output instead of hand-written copybook fields when your site allows it.

Db2 column	COBOL host variable pattern	Risk when mismatched
INTEGER	PIC S9(9) COMP or site-standard binary equivalent	Conversion or range issues.
DECIMAL(9,2)	PIC S9(7)V99 COMP-3	Scale or rounding errors at predicate boundaries.
CHAR(10)	PIC X(10)	Padding and comparison surprises.
DATE	PIC X(10) in ISO form, or the site standard date host variable	Invalid date strings or nonmatching formats.

When tuning a COBOL program, review the copybook before blaming Db2. The related DB2 Host Variables and Structures post is a useful companion.

Be Careful With OR Logic

OR can make predicate evaluation harder, especially when one branch is indexable and another is not. Sometimes the same logic can be expressed with common predicates factored out.

-- Harder to read and tune
WHERE (ADMRDEPT = 'E01' AND DEPTNAME LIKE 'BRANCH%')
   OR (DEPTNO   = 'D01' AND DEPTNAME LIKE 'BRANCH%')

-- Same rule with common predicate moved once
WHERE (ADMRDEPT = 'E01' OR DEPTNO = 'D01')
  AND DEPTNAME LIKE 'BRANCH%'

Another option is UNION ALL when each branch can use a different strong index and duplicates are not possible or can be handled deliberately. Always test the rewrite with production-like data.

Limit Rows Early

A COBOL program that needs 50 rows should not fetch 50,000 rows and stop in application code. Put the row limit and ordering rule in SQL when the business result allows it.

SELECT CUST_NO,
       BALANCE
  FROM ACCT_BAL
 WHERE STATUS = 'A'
 ORDER BY BALANCE DESC
 FETCH FIRST 50 ROWS ONLY

For repeated batch processing, also consider multi-row fetch when the SQL returns many rows and the program processes them sequentially. See COBOL DB2 Multi-Row Fetch for the rowset pattern.

Use SQL Diagnostics When Tuning Batch Jobs

Tuning work often needs simple instrumentation. Record row counts after update, delete, insert, or fetch statements. GET DIAGNOSTICS ROW_COUNT gives the program a direct count for many statement types.

EXEC SQL
    GET DIAGNOSTICS :WS-ROW-COUNT = ROW_COUNT
END-EXEC.

That count helps separate an access-path problem from a data-volume problem. If a nightly job suddenly updates 4 million rows instead of 40,000, the first question is why the qualifying row count changed. For details, see Db2 GET DIAGNOSTICS Statement Information Items.

Db2 SQL Tuning Checklist

• Capture EXPLAIN before and after the rewrite.
• Check RUNSTATS age for tables, indexes, and key columns.
• Keep indexed columns bare in predicates where possible.
• Move scalar functions and arithmetic away from predicate columns.
• Select only columns the COBOL program uses.
• Remove defensive DISTINCT when duplicate rows are not possible.
• Compare IN, EXISTS, and join rewrites with real data.
• Match host variable data types to Db2 columns.
• Review OR predicates for indexability and possible rewrites.
• Limit rows in SQL when the program needs only a small ordered set.

FAQ

What is the first step in Db2 SQL tuning?

Capture the access path with EXPLAIN. Without access-path evidence, it is too easy to tune the wrong part of the statement.

Does RUNSTATS improve SQL performance by itself?

RUNSTATS updates catalog statistics. Db2 uses those statistics for access-path selection. Static SQL may also need the normal site rebind process before a package uses a new path.

Should every slow query get a new index?

No. First check predicates, selected columns, host variables, statistics, and access path. Add or change an index only when the access pattern justifies the extra storage and insert/update cost.

The best tuning change is the one you can explain from the access path, row counts, and data distribution. Make one change, capture the new EXPLAIN output, and keep the result tied to the production problem you are solving.