Db2 Schemas Guide: Qualifiers, CURRENT SCHEMA, and COBOL SQL

A COBOL program can pass bind, compile, and link-edit, then still fail at runtime with SQLCODE -204 because Db2 looked for the table under the wrong qualifier. The SQL text says SELECT * FROM EMPLOYEE, but Db2 might resolve that unqualified table name as PAYROLL.EMPLOYEE, DEV01.EMPLOYEE, or another schema depending on the execution context.

A Db2 schema is a logical owner or namespace for database objects. Tables, views, indexes, aliases, triggers, routines, sequences, and other objects live under a schema name. In production COBOL SQL, the schema is not just a catalog detail. It controls name resolution, migration safety, package promotion, and the difference between testing against the intended table and testing against a same-named object in another environment.

A schema qualifies Db2 object names before SQL is executed.

Last updated: July 2026.

What a Db2 Schema Does

A schema groups objects under a qualifier. The fully qualified form is:

schema_name.object_name

For example, PAYROLL.EMPLOYEE and CLAIMS.EMPLOYEE can both exist because the schema names are different. The object name alone is the same, but the fully qualified names point to different objects.

Object reference	Meaning	Production risk
EMPLOYEE	Unqualified table name. Db2 must infer the schema.	Can resolve differently across test, QA, and production.
PAYROLL.EMPLOYEE	Qualified table name. The schema is explicit.	Safer for shared subsystems and promotion paths.
SET SCHEMA = 'PAYROLL'	Sets the schema used for unqualified dynamic SQL names.	Must be controlled in every code path that prepares SQL.

Schema Versus Database, Table Space, and Owner

Mainframe teams sometimes mix schema, database, table space, and owner because all four can appear near the same object. They are not the same thing.

• Schema: the qualifier used in SQL object names, such as PAYROLL in PAYROLL.EMPLOYEE.
• Database: a Db2 container for objects such as table spaces and indexes.
• Table space: the storage structure that holds table data. See the related Db2 table spaces guide.
• Authorization ID or owner: the ID or role associated with object creation and privileges.

A schema can look like an owner because many sites create objects under an authorization ID, but production rules should still name the concept clearly. When an abend ticket says "wrong database," but the failing SQL references an unqualified table, the first check is usually schema resolution.

How Db2 Resolves Unqualified Names

When SQL names an object without a qualifier, Db2 applies name-resolution rules. Static SQL and dynamic SQL are not always handled the same way, so a COBOL program can behave differently depending on whether a statement is embedded and bound in a package or prepared at runtime.

For dynamic SQL, IBM documents the CURRENT SCHEMA special register as the schema used to qualify unqualified object references in dynamically prepared statements. That makes CURRENT SCHEMA a real production setting, not a theory question.

SET SCHEMA = 'PAYROLL';

SELECT EMPNO, LASTNAME
  FROM EMPLOYEE
 WHERE DEPTNO = 'A00';

After the SET SCHEMA, the unqualified EMPLOYEE reference is resolved under PAYROLL for dynamic SQL. Many shops still prefer explicit qualifiers in production SQL because the qualifier is visible in the statement, the bind review, and the incident ticket.

CREATE SCHEMA Example

The basic DDL is short:

CREATE SCHEMA PAYROLL;

CREATE TABLE PAYROLL.EMPLOYEE
 (EMPNO     CHAR(6)     NOT NULL,
  LASTNAME  VARCHAR(30) NOT NULL,
  DEPTNO    CHAR(3)     NOT NULL,
  PRIMARY KEY (EMPNO));

The important part for COBOL developers is not the first line alone. It is the later discipline of using the same qualifier in DCLGEN, embedded SQL, bind jobs, catalog checks, and operations runbooks.

COBOL and Static SQL Example

A static SQL cursor in a COBOL program should make the target object clear. In regulated shops, this helps code review because the package points at the expected application schema.

EXEC SQL
    DECLARE C1 CURSOR FOR
        SELECT EMPNO, LASTNAME, DEPTNO
          FROM PAYROLL.EMPLOYEE
         WHERE DEPTNO = :WS-DEPTNO
END-EXEC.

If the program uses DCLGEN copybooks, keep the schema naming policy consistent with the copybook generation process. A DCLGEN made from PAYROLL.EMPLOYEE but used with SQL that references only EMPLOYEE can hide a promotion error until bind or execution. For the full compile and bind path, see the Db2 application environment guide.

Common SQLCODE -204 Scenario

SQLCODE -204 is often the first visible symptom of a schema problem. A batch job might fail in QA with a message that the object is undefined. The table exists, the DBA can query it, and the developer can see it in the catalog. The mismatch is usually that Db2 looked for QAUSER.EMPLOYEE while the real table is PAYROLL.EMPLOYEE.

-- Failing dynamic SQL path
SELECT EMPNO FROM EMPLOYEE;

-- Confirm intended object in the catalog
SELECT CREATOR, NAME, TYPE
  FROM SYSIBM.SYSTABLES
 WHERE NAME = 'EMPLOYEE';

Use the Db2 catalog guide to verify the object owner and type. Then check whether the SQL should use an explicit qualifier, whether SET SCHEMA is missing, or whether the package was bound under the wrong qualifier rules.

Schema Checks Before Promotion

Before moving a COBOL Db2 change from test to production, add schema checks to the same review as package, plan, and collection checks.

• Search embedded SQL for unqualified tables, views, aliases, sequences, and routines.
• Compare DCLGEN source against the schema used by the target package.
• Confirm bind jobs use the expected collection and qualifier settings.
• Run catalog queries for same-named objects under different schemas.
• For dynamic SQL, verify any SET SCHEMA path runs before PREPARE.
• Check SQLCODE handling so -204, -551, and related errors are logged with the resolved object name when available.

Those checks belong near the bind and deployment controls covered in the Db2 packages guide and the Db2 DSN command reference.

When to Use Explicit Qualifiers

Use explicit qualifiers when the SQL targets shared production objects, when the subsystem contains multiple application schemas, or when the statement is reviewed by operations during an incident. A qualified name gives the reviewer one less variable to infer.

Unqualified names can still be acceptable in controlled dynamic SQL frameworks where the application sets CURRENT SCHEMA deliberately and logs that setting. The rule should be written down. If developers have to guess, the next promotion will repeat the same -204 investigation.

FAQ

Is a Db2 schema the same as a database?

No. A schema is a SQL namespace or qualifier for objects. A database is a Db2 container used with storage structures such as table spaces.

Why does the same COBOL SQL work in test but fail in production?

One common reason is unqualified object names. Test might resolve EMPLOYEE under one schema while production expects PAYROLL.EMPLOYEE.

Should COBOL programs qualify every table name?

For production static SQL, explicit qualifiers are usually easier to review and troubleshoot. Some sites rely on bind or dynamic SQL settings, but the rule must be consistent.

Does SET SCHEMA affect static SQL?

Treat SET SCHEMA mainly as a dynamic SQL control. Static SQL is resolved through precompile and bind rules, so check the package and bind settings instead of assuming a runtime SET SCHEMA will fix it.

Practical Rule

When a Db2 object name can exist in more than one place, qualify it or prove which schema Db2 will use. That one check prevents many late-night SQLCODE -204 calls.

Db2 Rowset Data Modification: Positioned UPDATE, DELETE, and Multi-Row INSERT

Validate row counts after rowset changes.

A rowset positioning cursor can fetch several rows into COBOL host-variable arrays with one FETCH. After that fetch, Db2 can modify the current rowset with positioned UPDATE or DELETE statements, or target one row inside the rowset. That power is useful, but it also makes row-count validation non-negotiable.

This guide explains how to modify data with rowset positioning in Db2 for z/OS, including positioned update, positioned delete, single-row targeting inside a rowset, multi-row insert, ATOMIC versus NOT ATOMIC, and SQLCA checks for COBOL programs.

What rowset modification means

A rowset cursor fetches a block of rows instead of one row at a time. The program can then process arrays in working storage. For updateable rowset cursors, Db2 can apply positioned updates or deletes to the current rowset, or to a single row within that rowset.

The important distinction is scope. WHERE CURRENT OF cursor-name can affect the current rowset. FOR ROW n OF ROWSET targets one row in that rowset. A production program should make that choice explicit and then verify the affected-row count.

Rowset modification options

Operation	Scope	What to check
Positioned UPDATE ... WHERE CURRENT OF	Can update all rows in the current rowset.	Expected rowset size, row count, and whether every fetched row should change.
Positioned DELETE ... WHERE CURRENT OF	Can delete all rows in the current rowset.	Business rule, audit requirement, and commit/rollback scope.
FOR ROW n OF ROWSET	Targets one row in the current rowset.	Host variable row number is within the fetched row count.
INSERT ... FOR n ROWS	Inserts multiple rows from host-variable arrays.	ATOMIC or NOT ATOMIC, diagnostics, and failed-row handling.

Positioned update for the current rowset

Use this form only when every row in the current rowset should receive the change. If the cursor fetched 50 rows, design and test the program as a 50-row update path, not as a single-row path.

EXEC SQL
   UPDATE EMP
      SET SALARY = :WS-NEW-SALARY
    WHERE CURRENT OF C1
END-EXEC

IF SQLCODE = 0
   PERFORM CHECK-ROWSET-UPDATE-COUNT
ELSE
   PERFORM WRITE-DB2-ERROR
END-IF

Do not assume that SQLCODE = 0 is enough. Check the row count available for your statement and Db2 level, and compare it with the number of rows the program intended to update.

Update one row inside a rowset

When only one row in the fetched rowset should change, use rowset row targeting. The row number can be a host variable, which lets the program select the matching array element after validation.

EXEC SQL
   UPDATE EMP
      SET SALARY = :WS-NEW-SALARY
    FOR ROW :WS-ROW-NO OF ROWSET
    FOR CURSOR C1
END-EXEC

Validate WS-ROW-NO before the SQL statement. It should be greater than zero and less than or equal to the number of rows fetched into the current rowset.

Delete with rowset positioning

A positioned delete can remove every row in the current rowset or one selected row, depending on the syntax used. Treat this as a high-risk path in batch programs because the wrong rowset size can delete more rows than expected.

EXEC SQL
   DELETE FROM EMP
    WHERE CURRENT OF C1
END-EXEC

EXEC SQL
   DELETE FROM EMP
    FOR ROW 3 OF ROWSET
    FOR CURSOR C1
END-EXEC

For production deletes, write audit data before commit. Include the key values from the host-variable arrays, not just the rowset number.

Multi-row insert with host-variable arrays

Db2 can insert multiple rows from host-variable arrays with FOR n ROWS. This is useful for bulk insert paths where the COBOL program has already filled arrays with validated data.

EXEC SQL
   INSERT INTO EMP_TBL
          (EMP_NO, EMP_NAME, SALARY)
   FOR :WS-INSERT-COUNT ROWS
   VALUES (:HV-EMP-NO,
           :HV-EMP-NAME,
           :HV-SALARY)
   ATOMIC
END-EXEC

With ATOMIC, Db2 treats the insert set as one unit for success or failure. With NOT ATOMIC, individual rows can succeed or fail, so the program must inspect diagnostics and handle partial success.

ATOMIC versus NOT ATOMIC

Choice	Behavior	When it fits
ATOMIC	The multi-row statement succeeds or fails as a unit.	Use when partial inserts would break the business rule.
NOT ATOMIC	Rows can succeed or fail independently.	Use only when the program has diagnostics handling for failed rows.

SQLCA and diagnostics checks

Rowset processing needs stronger checks than a one-row cursor loop. At minimum, validate SQLCODE, SQLSTATE, affected-row count, fetched-row count, and diagnostics for partial success cases.

• Check SQLCODE after every rowset update, delete, or insert.
• Compare affected rows with the intended number of rows.
• Use diagnostics when NOT ATOMIC can produce per-row results.
• Log key values from arrays when a row fails, not only the array index.
• Commit only after the full rowset operation passes business validation.

Common mistakes

Using WHERE CURRENT OF when only one row should change

In rowset processing, that can affect the whole current rowset. Use row targeting when the business rule is one row.

Not validating the row number

A host variable used in FOR ROW n OF ROWSET must be checked against the number of rows actually fetched.

Using NOT ATOMIC without diagnostics handling

Partial success is only useful when the program can identify failed rows and decide whether to continue, retry, or roll back.

Related Db2 topics

Use this guide with Db2 Rowset Positioning Cursor, Db2 SQL Execution Validation, Db2 SQLCODE and SQLSTATE, Db2 GET DIAGNOSTICS, and Db2 Application Environment.

FAQ

Can WHERE CURRENT OF affect more than one row with a rowset cursor?

Yes. With rowset positioning, a positioned update or delete can affect all rows in the current rowset. Use row targeting when only one row should be changed.

How do I update one row in a Db2 rowset?

Use FOR ROW n OF ROWSET with the cursor name and validate that n is within the number of rows fetched into the current rowset.

When should I use NOT ATOMIC for multi-row insert?

Use NOT ATOMIC only when partial success is acceptable and the program checks diagnostics for each failed row.

Db2 Scrollable Cursor in COBOL: FETCH FIRST, PRIOR, ABSOLUTE



Db2 scrollable cursor movement in a COBOL + Db2 application.

Last updated: July 5, 2026

Db2 Scrollable Cursor in COBOL: FETCH FIRST, PRIOR, ABSOLUTE, and RELATIVE

A normal Db2 cursor moves forward through a result set. A scrollable cursor is different: after the cursor is opened, the program can move to the first row, last row, previous row, next row, an absolute row number, or a row relative to the current position.

That sounds small until you see a real batch support case. A production support program reads account exceptions from ACCOUNT_EXCP. The operator checks row 200, moves back to row 195, then jumps to the last row to confirm the final exception. A forward-only cursor forces extra host-language logic or a new query. A scrollable cursor lets Db2 manage the row movement.

What Is a Db2 Scrollable Cursor?

A Db2 scrollable cursor is a cursor declared with the SCROLL option. It lets an embedded SQL program fetch rows in more than one direction. In Db2 for z/OS, NO SCROLL is the default, so a cursor is not scrollable unless the program asks for it.

The main difference is cursor positioning. With a forward-only cursor, the common pattern is OPEN, repeated FETCH NEXT, then CLOSE. With a scrollable cursor, the FETCH statement can reposition the cursor before returning data.

When a Scrollable Cursor Helps

Use a scrollable cursor when the program genuinely needs to move around inside the same result set.

• A browse screen where PF7 and PF8 move backward and forward.
• A review program that jumps to the first, last, or nth row in a result set.
• A support utility that compares the current row with the previous row.
• A controlled reporting program where the same row might be inspected more than once.

Do not make every cursor scrollable. If the program only reads rows once from top to bottom, a normal cursor is usually simpler and cheaper.

Basic Syntax

The cursor declaration must include SCROLL. A simple COBOL + Db2 example looks like this:

EXEC SQL
    DECLARE C1 INSENSITIVE SCROLL CURSOR FOR
      SELECT EMPNO,
             LASTNAME,
             WORKDEPT
        FROM DSN8C10.EMP
       WHERE WORKDEPT = :WS-DEPT
       ORDER BY EMPNO
END-EXEC.

INSENSITIVE means the result table does not reflect inserts, updates, or deletes made to the underlying rows after the cursor is opened. It is also read-only. That is often acceptable for browse and report programs.

Common FETCH Options

After opening the cursor, the program can use different fetch orientations.

FETCH option	What it does	Typical use
FETCH FIRST	Moves to the first row	Start browse from the top
FETCH LAST	Moves to the last row	Show the most recent or final row after sorting
FETCH NEXT	Moves one row forward	PF8 or normal forward browsing
FETCH PRIOR	Moves one row backward	PF7 or previous-record logic
FETCH ABSOLUTE n	Moves to row number n	Jump to row 50, 100, or another fixed position
FETCH RELATIVE n	Moves n rows from the current position	Move forward or backward by a page size

COBOL Example

This example opens a scrollable cursor and fetches the first, next, prior, and absolute rows. The host variables are simplified so the cursor movement is easy to see.

01  WS-DEPT        PIC X(03).
01  HV-EMPNO       PIC X(06).
01  HV-LASTNAME    PIC X(15).
01  HV-WORKDEPT    PIC X(03).
01  WS-ABS-ROW     PIC S9(9) COMP VALUE 10.

    MOVE 'A00' TO WS-DEPT.

    EXEC SQL
      OPEN C1
    END-EXEC.

    EXEC SQL
      FETCH FIRST FROM C1
       INTO :HV-EMPNO,
            :HV-LASTNAME,
            :HV-WORKDEPT
    END-EXEC.

    IF SQLCODE = 0
       PERFORM DISPLAY-EMPLOYEE
    END-IF.

    EXEC SQL
      FETCH NEXT FROM C1
       INTO :HV-EMPNO,
            :HV-LASTNAME,
            :HV-WORKDEPT
    END-EXEC.

    EXEC SQL
      FETCH PRIOR FROM C1
       INTO :HV-EMPNO,
            :HV-LASTNAME,
            :HV-WORKDEPT
    END-EXEC.

    EXEC SQL
      FETCH ABSOLUTE :WS-ABS-ROW FROM C1
       INTO :HV-EMPNO,
            :HV-LASTNAME,
            :HV-WORKDEPT
    END-EXEC.

    EXEC SQL
      CLOSE C1
    END-EXEC.

Always check SQLCODE or SQLSTATE after each FETCH. When the requested row is outside the result table, Db2 positions the cursor before the first row or after the last row and no host variables are assigned.

Scrollable Cursor Sensitivity

The sensitivity option controls whether changes to the underlying table can be visible through the cursor.

INSENSITIVE SCROLL

INSENSITIVE gives the program a stable result table after OPEN. Inserts, updates, and deletes made to the base rows are not reflected in the cursor result. The cursor is read-only, so it cannot be used for positioned update or delete.

SENSITIVE STATIC SCROLL

SENSITIVE STATIC keeps the size and order of the result table stable after open. It can see some changes through positioned operations or sensitive fetch behavior. This can introduce update holes or delete holes when the base row no longer matches the result table.

SENSITIVE DYNAMIC SCROLL

SENSITIVE DYNAMIC is useful when the application needs to see committed inserts, updates, or deletes while the cursor is open. It can be useful in interactive applications, but it needs careful isolation-level and concurrency testing.

Performance Rules

• Use a forward-only cursor when the program only reads the result set once.
• Use ORDER BY when the screen or report needs predictable row movement.
• Keep the result set small. A browse cursor over millions of rows is usually a design problem.
• For read-only browse logic, say so clearly with FOR READ ONLY where appropriate.
• Close the cursor when the program is done with it.
• Test behavior at boundaries: before first row, after last row, empty result set, and single-row result set.

Common Mistakes

Declaring a cursor without SCROLL

If the cursor is declared with the default NO SCROLL, FETCH PRIOR, FETCH FIRST, FETCH LAST, FETCH ABSOLUTE, and FETCH RELATIVE are not valid for that cursor.

Using a scrollable cursor instead of a better WHERE clause

If the program already knows the key value, fetch the target row directly with a predicate. Do not open a large scrollable cursor just to jump to a row that can be found by key.

Ignoring SQLCODE +100

+100 is not just an end-of-file signal. With scrollable cursors, it can also tell you that the requested movement went outside the result table.

Internal Links

• DB2 Cursor Types in COBOL + Db2 Applications
• DB2 Scrollable Cursor Guidelines
• SQLCA, SQLCODE, and SQLSTATE in Db2

External References

• IBM Db2 for z/OS: DECLARE CURSOR statement
• IBM Db2 for z/OS: FETCH statement

FAQ

What is the default cursor type in Db2?

For embedded SQL in Db2 for z/OS, a cursor is not scrollable by default. Use SCROLL when the program needs backward, absolute, or relative movement.

Can a scrollable cursor update rows?

It depends on the cursor declaration and the result table. An INSENSITIVE scrollable cursor is read-only. For positioned update or delete, the cursor and SELECT must be updatable.

Should every COBOL + Db2 cursor be scrollable?

No. Use scrollable cursors only when the program needs random or backward movement through the same result set. A forward-only cursor is better for normal sequential processing.

Db2 Scrollable Cursor Guidelines for COBOL Programs

Db2 scrollable cursor movement in COBOL.

A COBOL + Db2 program should not declare SCROLL just because a user may press Page Up on a screen. Scrollable cursors can make a program easier to code, but they can also add work for Db2, make locking behavior harder to reason about, and return rows differently depending on cursor sensitivity.

This checklist is for batch and online developers who already understand the basic DECLARE CURSOR, OPEN, FETCH, and CLOSE flow. For syntax and fetch movement examples, read Db2 Scrollable Cursor in COBOL: FETCH FIRST, PRIOR, ABSOLUTE.

Use a Scrollable Cursor Only When the Program Needs Backward Movement

A normal cursor reads forward. That is enough for most report jobs, extract jobs, validation programs, and one-pass update routines. Use a scrollable cursor when the program has a real need for one of these movements:

• FETCH PRIOR to move back one row.
• FETCH FIRST or FETCH LAST to jump to the edge of the result set.
• FETCH ABSOLUTE n to position on a known row number.
• FETCH RELATIVE n to move forward or backward from the current row.
• Repeated reads of the same result set while keeping cursor position in the program.

If the program reads every qualifying row once and writes a report or output file, a forward-only cursor is usually the cleaner choice. The DB2 cursor life cycle is simpler and the program has fewer cursor states to test.

Pick the Cursor Sensitivity Deliberately

Db2 supports ASENSITIVE, INSENSITIVE, SENSITIVE STATIC, and SENSITIVE DYNAMIC cursor behavior. Do not leave this decision to habit. The right choice depends on whether the program must see changes after the cursor opens.

Need in the program	Cursor choice	Developer note
The result set should not change while the cursor is open.	INSENSITIVE SCROLL	Good for review screens, browse lists, and reports where repeatable movement matters more than seeing later changes.
The program may need to see committed updates or deletes after the cursor opens.	SENSITIVE STATIC SCROLL	Rows inserted after the cursor opens are not added to the result set. Updates and deletes can appear through sensitive fetch behavior.
The result table itself may need to reflect committed inserts, deletes, and order changes.	SENSITIVE DYNAMIC SCROLL	Use only when the application really needs this behavior. Db2 can reject the cursor when the query requires materialization or becomes read-only.
The program has no special sensitivity rule.	ASENSITIVE SCROLL	Db2 chooses whether the cursor is insensitive or sensitive dynamic. For production COBOL, an explicit choice is easier to review.

IBM's Db2 for z/OS documentation for the DECLARE CURSOR statement explains these options and the limits around read-only result tables. The FETCH statement documentation covers the scroll fetch forms.

A Safe Starting Pattern

For a browse-style COBOL program that shows customer rows and lets the operator move forward and backward, start with an insensitive scrollable cursor unless the business rule says changed rows must be visible immediately.

EXEC SQL
    DECLARE C1 INSENSITIVE SCROLL CURSOR FOR
        SELECT CUST_NO,
               CUST_NAME,
               STATUS
          FROM CUSTOMER
         WHERE REGION = :WS-REGION
         ORDER BY CUST_NO
END-EXEC.

EXEC SQL
    OPEN C1
END-EXEC.

EXEC SQL
    FETCH FIRST FROM C1
      INTO :WS-CUST-NO,
           :WS-CUST-NAME,
           :WS-STATUS
END-EXEC.

EXEC SQL
    FETCH PRIOR FROM C1
      INTO :WS-CUST-NO,
           :WS-CUST-NAME,
           :WS-STATUS
END-EXEC.

This pattern keeps the result set stable for the screen. The operator can move through rows without seeing a row disappear because another task committed an update after the cursor opened.

Do Not Use Scrollable Cursors in Every CICS Screen

A pseudo-conversational CICS program ends the task between user interactions. A cursor is not a good place to keep conversational state across that boundary. Store the key values needed to restart the browse, then reopen the cursor on the next task and fetch from a known key.

For example, instead of keeping a cursor open while the user thinks, save LAST-CUST-NO in the commarea or channel container, then reopen with a predicate such as WHERE CUST_NO > :LAST-CUST-NO for the next page. This avoids holding Db2 resources while the terminal is idle.

Watch for Queries That Force Materialization

A scrollable cursor often needs Db2 to preserve cursor position and result table behavior. If a query contains joins, grouping, ordering, expressions, or other clauses that make the result table read-only or materialized, a sensitive dynamic cursor may not be valid.

When the program fails at OPEN, do not patch the COBOL loop first. Check the cursor declaration and the SELECT. A dynamic scrollable cursor with a query that Db2 cannot keep sensitive is a design issue, not a fetch-loop issue.

Test These SQLCODE Paths

Scrollable cursor code has more positioning cases than a forward-only cursor. Add test cases for these return codes and states:

• SQLCODE +100 on FETCH NEXT after the last row.
• SQLCODE +100 on FETCH PRIOR before the first row.
• FETCH ABSOLUTE 0 or invalid relative movement, if the program can build those values.
• Deleted or changed rows when using a sensitive static cursor.
• OPEN failure when the SELECT is not valid for the requested sensitivity.

Use the program's existing SQLCA handling and keep the diagnostic output specific. The related SQLCA and SQLCODE guide is a useful refresher when adding these checks.

Scrollable Cursor Review Checklist

• Can the program work with a forward-only cursor? If yes, use the simpler cursor.
• Does the user or batch logic need PRIOR, FIRST, LAST, ABSOLUTE, or RELATIVE movement?
• Has the developer selected INSENSITIVE, SENSITIVE STATIC, or SENSITIVE DYNAMIC on purpose?
• Does the SELECT make a sensitive dynamic cursor invalid?
• Does a CICS program close the cursor before returning control to the terminal?
• Are +100, open errors, update holes, and delete holes tested?
• Would multi-row fetch or rowset positioning solve the real performance problem better?

FAQ

Is a scrollable cursor faster than a normal Db2 cursor?

No. A scrollable cursor is for movement through a result set, not a speed feature. If the program only reads forward, a normal cursor is usually a better starting point.

Can a scrollable cursor see rows inserted by another program?

A sensitive dynamic cursor can reflect committed inserts when Db2 can support that cursor type. A sensitive static cursor does not add inserted rows to the result set after the cursor opens.

Should CICS programs keep scrollable cursors open between screens?

No. In pseudo-conversational CICS, close the cursor before returning control and save enough key data to restart the browse on the next task.

What is the safest scrollable cursor type for a browse screen?

INSENSITIVE SCROLL is often the safest first choice when the screen needs stable forward and backward movement through the same result set.

The best scrollable cursor is the one whose movement and sensitivity match a real program requirement. If the code only reads the next row until SQLCODE +100, keep the cursor forward-only.

Db2 DSN Command Reference: BIND, RUN, SPUFI, DCLGEN, and REBIND



DSN runs Db2 commands from TSO.

A bind job can fail before a COBOL program ever reaches its first OPEN or FETCH. In many Db2 for z/OS shops, the first place to check is the TSO DSN command stream in SYSTSIN, because that is where BIND, REBIND, RUN, SPUFI, and related subcommands are issued.

DSN is the Db2 command processor that runs as a TSO command. It can be used in the foreground under TSO/ISPF or in batch through programs such as IKJEFT01. Once a DSN session starts, the subcommands tell Db2 what action to perform.

Where DSN fits

Db2 administration and application work uses several command paths. Some commands are Db2 system commands such as -DISPLAY DATABASE. Others are DSN subcommands used inside a DSN session, such as BIND PACKAGE or RUN PROGRAM. The old article mixed those categories together, so this refresh separates the common DSN work from general Db2 command usage.

• DSN command: starts a Db2 command processor session from TSO.
• DSN subcommands: run inside DSN, including BIND, REBIND, FREE, RUN, DCLGEN, SPUFI, END, and comments beginning with *.
• Db2 system commands: can be issued through DSN or from operator/admin paths, usually beginning with a hyphen, such as -DISPLAY or -START.

Common DSN subcommands

Subcommand	Used for	Production note
DSN	Starts the Db2 command processor session for a subsystem.	Check SYSTEM(DB2P) or the subsystem name before running bind or run jobs.
BIND	Creates or replaces an application package or plan from a DBRM.	Review collection, qualifier, owner, validation timing, and isolation before release.
REBIND	Refreshes an existing package or plan without using a new DBRM.	Use with care after RUNSTATS or access-path changes; keep fallback steps clear.
FREE	Deletes a package or plan.	Confirm no active application depends on the object before removing it.
RUN	Runs an application program under DSN.	Check plan name, program load library, and runtime SQL errors together.
DCLGEN	Generates host-language declarations for tables or views.	Regenerate declarations when column definitions used by COBOL change.
SPUFI	Runs SQL from an input file in an ISPF foreground session.	Good for controlled testing, not for unattended production jobs.
END	Ends the DSN session.	Always close the command stream cleanly in batch SYSTSIN.
*	Marks a DSN command-stream comment.	Use comments to identify release number, package, and change ticket.

Batch DSN example with BIND PACKAGE

In batch, a DSN command stream is normally placed in SYSTSIN. The example below starts DSN for subsystem DB2P, binds package member ACCTUPD, and ends the session.

//BINDPKG  EXEC PGM=IKJEFT01
//STEPLIB  DD  DISP=SHR,DSN=DB2P.SDSNLOAD
//SYSTSPRT DD  SYSOUT=*
//SYSPRINT DD  SYSOUT=*
//SYSTSIN  DD  *
  DSN SYSTEM(DB2P)
  BIND PACKAGE(APP1COLL) -
       MEMBER(ACCTUPD) -
       ACTION(REPLACE) -
       ISOLATION(CS) -
       VALIDATE(BIND)
  END
/*

When this job fails, read SYSTSPRT and SYSPRINT before changing the bind cards. The messages usually show whether the problem is an authorization issue, missing DBRM, invalid option, unavailable subsystem, or SQL object problem.

RUN PROGRAM example

RUN can execute an application program through DSN. Many production sites use normal batch JCL or schedulers for application execution, but RUN is still useful in examples and controlled test jobs.

//RUNPGM   EXEC PGM=IKJEFT01
//STEPLIB  DD  DISP=SHR,DSN=DB2P.SDSNLOAD
//         DD  DISP=SHR,DSN=APP1.LOADLIB
//SYSTSPRT DD  SYSOUT=*
//SYSTSIN  DD  *
  DSN SYSTEM(DB2P)
  RUN PROGRAM(ACCTUPD) PLAN(ACCTPLAN)
  END
/*

If the program starts but fails during SQL execution, move from DSN diagnostics to application diagnostics: SQLCODE, SQLSTATE, SQLERRMC, package name, plan name, and the business key being processed.

DCLGEN and SPUFI usage

DCLGEN

DCLGEN creates host-variable declarations that match a table or view. For COBOL teams, it helps keep copybooks consistent with Db2 column names and data types. Regenerate and review DCLGEN output after table changes, especially nullable columns and decimal precision changes.

SPUFI

SPUFI is the SQL processor using file input under ISPF. It is practical for testing SQL, checking catalog rows, or validating a small query before it is added to a program. Keep production fixes in controlled jobs or approved tooling rather than ad hoc foreground edits.

Common mistakes

• Running BIND against the wrong subsystem after copying JCL from test to production.
• Binding a package into the wrong collection and then running a plan that does not reference it.
• Using REBIND without recording the prior access path or fallback plan.
• Deleting a package with FREE before checking dependent jobs and online transactions.
• Ignoring SYSTSPRT and looking only at the job return code.

Quick troubleshooting checklist

Symptom	First checks
Bind job fails	Subsystem name, DBRM library, member name, package collection, owner, qualifier, and bind authority.
Program cannot find package or plan	Collection, package name, plan name, package list, and runtime JCL.
Access path changed after REBIND	RUNSTATS timing, catalog statistics, bind options, and package copy/fallback procedure.
SPUFI SQL works but program fails	Host variables, indicator variables, package bind options, authorization ID, and SQLCA handling.

Related Db2 topics

Use this reference with Db2 Binding Application, Db2 Binding and Rebinding, Db2 Packages, Db2 Commands Quick Reference, and Db2 Application Environment.

FAQ

What is the DSN command in Db2 for z/OS?

DSN is the Db2 command processor that runs as a TSO command. It starts a session where subcommands such as BIND, REBIND, RUN, DCLGEN, and SPUFI can be issued.

Can DSN run in batch?

Yes. DSN commands are often run in batch through IKJEFT01, with the command stream placed in SYSTSIN and output written to SYSTSPRT.

What is the difference between BIND and REBIND?

BIND creates or replaces a package or plan using DBRM input. REBIND refreshes an existing package or plan, often after catalog statistics or environment changes.

Db2 Commands Quick Reference: DISPLAY, START, STOP, ALTER, and CANCEL



Check status before changing Db2.

A Db2 command can change subsystem availability, stop distributed access, cancel a thread, or terminate a utility. Before an operator types -STOP DATABASE or -TERM UTILITY, the command target and scope must be clear. A missing database name, wrong member, or broad command scope can turn a small support action into a larger outage.

This Db2 commands quick reference groups common Db2 for z/OS commands by the job they do: checking status, starting and stopping resources, changing runtime values, managing logs, handling utilities, and diagnosing threads.

Where Db2 commands can be issued

Most Db2 commands begin with a hyphen, such as -DISPLAY DATABASE. Depending on site setup and authority, commands may be issued from a z/OS console, TSO or DB2I, an APF-authorized program, CICS or IMS paths, or an IFI application. -START DB2 is normally a console-level action; do not treat it like an ordinary application command.

• Check whether the command is allowed from your interface.
• Confirm whether the command acts on one object, one member, a data sharing group, or the whole subsystem.
• Capture command output in the ticket or job log when it affects production.

DISPLAY commands

Use DISPLAY commands when you need current facts before taking action. In support work, this is usually the safest first step.

Command	Use	Common support question
-DISPLAY DATABASE	Shows database or table space status.	Is the object stopped, restricted, copy-pending, or unavailable?
-DISPLAY THREAD	Shows local or distributed thread information.	Which plan, auth ID, correlation ID, or connection is holding work?
-DISPLAY DDF	Shows Distributed Data Facility status.	Is distributed access active, stopped, or limited?
-DISPLAY BUFFERPOOL	Shows buffer pool status and activity.	Is the buffer pool active, and what does the activity look like?
-DISPLAY LOG	Shows active log and offload status.	Is logging healthy, or is archive/offload work falling behind?
-DISPLAY UTILITY	Shows Db2 utility execution status.	Which utility ID is active, stopped, or waiting?
-DISPLAY GROUP	Shows data sharing group information.	Which members are active, and what mode is the group using?

START and STOP commands

START and STOP commands change availability. Use the narrowest object scope that solves the problem.

Command	Use	Check first
-START DATABASE	Makes a database or table space available.	Object name, access mode, and whether utilities are still running.
-STOP DATABASE	Makes a database or table space unavailable or restricted.	Active threads, batch schedule, and online transaction impact.
-START DDF	Starts distributed data access.	Network, location, and security readiness.
-STOP DDF	Stops distributed data access.	Remote applications, DRDA clients, and application owners.
-START TRACE	Starts trace activity.	Trace class, destination, expected volume, and stop plan.
-STOP TRACE	Stops trace activity.	Trace identifier and whether diagnostic capture is complete.

ALTER and SET commands

ALTER and SET commands change runtime behavior. Keep the before-and-after values in the change record.

Command	Use	Production note
-ALTER BUFFERPOOL	Changes buffer pool attributes.	Coordinate with DBA performance checks before changing size or thresholds.
-ALTER GROUPBUFFERPOOL	Changes group buffer pool attributes in data sharing.	Check coupling facility impact and data sharing member scope.
-ALTER UTILITY	Changes selected utility processing values.	Verify the utility ID and current phase before changing behavior.
-SET ARCHIVE	Controls archive log allocation behavior.	Use with storage and operations awareness.
-SET LOG	Changes logging checkpoint-related values.	Record the reason and expected duration of the change.
-SET SYSPARM	Loads selected subsystem parameter values.	Confirm site procedures; not every subsystem parameter can be changed casually.

Thread and utility commands

Commands such as -CANCEL THREAD and -TERM UTILITY can interrupt work. They should be driven by command output, not guesswork.

-DISPLAY THREAD(*) TYPE(ACTIVE)
-CANCEL THREAD(token)

-DISPLAY UTILITY(*)
-TERM UTILITY(utility-id)

Before canceling a thread, identify the connection, correlation ID, authorization ID, and unit of work. Before terminating a utility, capture the utility ID, phase, object name, and restart instructions.

Archive and log commands

Log commands affect recovery posture. -ARCHIVE LOG closes the current active log and starts use of the next available log data set. -DISPLAY LOG helps confirm logging and offload state before and after action.

• Use -DISPLAY LOG before forcing archive activity.
• Confirm archive destinations and offload health.
• Record log-related messages in the incident or change ticket.

Command safety checklist

• Confirm the subsystem ID and data sharing member.
• Confirm the object name, utility ID, thread token, or trace number.
• Run a matching DISPLAY command first when possible.
• Check authority and site operations rules.
• Know how to reverse the command or restore availability.
• Save the command output for the ticket.

Related Db2 topics

Use this reference with Db2 DSN Command Reference, Db2 Utilities, Db2 Buffer Pool, Db2 Packages, and Db2 Application Environment.

FAQ

What is a Db2 command on z/OS?

A Db2 command is an operational command used to display or change Db2 subsystem, object, thread, log, utility, trace, or distributed access state. Many Db2 commands begin with a hyphen.

Which Db2 command should I run first during an incident?

Use a matching DISPLAY command first when possible, such as -DISPLAY THREAD, -DISPLAY DATABASE, -DISPLAY UTILITY, or -DISPLAY LOG.

Are Db2 commands the same as DSN subcommands?

No. DSN starts the Db2 command processor and supports subcommands such as BIND and RUN. Db2 commands such as -DISPLAY DATABASE and -STOP DDF are operational subsystem commands.

Db2 Directory Guide: SPT01, SCT02, DBD01, SYSLGRNX, and SYSUTILX



Db2 manages the directory internally.

A Db2 package can be present in the catalog and still fail at execution time if the internal execution structures are damaged, unavailable, or out of sync. When recovery, bind, or utility processing is involved, Db2 uses directory objects that normal application SQL does not query.

The Db2 directory stores internal control information used by Db2 for operation, package and plan execution, database descriptors, log range tracking, and utility restart. Unlike the catalog, the directory is not a normal SQL reporting source. Db2 and supported utilities maintain it.

What the Db2 directory does

The directory supports Db2 execution and recovery work that must be fast and controlled. It stores internal forms of packages and plans, database descriptors, log ranges used for recovery, and utility status needed for restart. Developers usually learn about it when a bind, run, utility, or recovery problem points below normal catalog metadata.

Directory versus catalog

Area	Purpose	Normal access
Db2 catalog	Stores queryable metadata about objects, privileges, packages, plans, routines, and statistics.	Read with SQL when authorized.
Db2 directory	Stores internal control data used by Db2 for execution, recovery, utility restart, and package or plan processing.	Managed by Db2 and supported utilities, not normal application SQL.

Important Db2 directory objects

Directory object	What it supports	Why it matters
SPT01	Skeleton package table, often called SKPT.	Contains internal package information and access-path data created by BIND PACKAGE and removed by FREE PACKAGE.
SCT02	Skeleton cursor table, often called SKCT.	Contains internal plan information and access-path data created by BIND PLAN and removed by FREE PLAN.
DBD01	Database descriptors.	Stores internal database descriptor information for table spaces, indexes, tables, constraints, and related structures.
SYSLGRNX	Log range tracking.	Helps Db2 locate log ranges needed for recovery of updated table spaces or partitions.
SYSUTILX	Utility execution and restart state.	Stores utility status so Db2 can restart, recover, or terminate utility work correctly.

How directory objects show up in support work

Package or plan execution

When a static SQL program runs, Db2 uses package or plan structures created at bind time. Catalog rows help you identify the package, collection, and validity, but execution also depends on internal structures in the directory. If a bind or free operation fails, support teams often check both catalog state and Db2 messages tied to directory processing.

Recovery and log ranges

SYSLGRNX helps Db2 find the log ranges needed for recovery. If an object has been updated, Db2 can use recorded ranges instead of searching all log data blindly. This matters during RECOVER, restart, and problem diagnosis after an outage.

Utility restart

SYSUTILX is involved when utilities such as REORG, LOAD, COPY, or RECOVER need restart or cleanup handling. If a utility stops, do not delete anything by hand. Use supported commands such as -DISPLAY UTILITY, restart procedures, or -TERM UTILITY only when site rules allow it.

-DISPLAY UTILITY(*)
-TERM UTILITY(utility-id)

Safe handling rules

• Do not update or delete Db2 directory data manually.
• Use supported Db2 commands, bind actions, utilities, and recovery procedures.
• Check the catalog first when the question is about object names, package names, privileges, or statistics.
• Check Db2 messages, utility output, and recovery documentation when the problem points to directory-managed state.
• Escalate directory damage, utility restart confusion, or recovery inconsistencies to the DBA or systems programmer team.

Common mistakes

Treating the directory like catalog tables

The catalog is meant to be queried for metadata. The directory is internal. Treating both as ordinary application data is a support risk.

Terminating utilities without restart context

A utility entry can represent recoverable work. Capture the utility ID, phase, object name, and messages before taking action.

Looking only at package catalog rows

Package catalog rows help identify the package, but a runtime issue may also involve bind output, plan references, load libraries, directory-managed structures, or subsystem messages.

Related Db2 topics

Use this guide with Db2 Catalog, Db2 Packages, Db2 Utilities, Db2 Commands Quick Reference, and Db2 Binding and Rebinding.

FAQ

What is the Db2 directory?

The Db2 directory stores internal control information used by Db2 for package and plan execution, database descriptors, log range tracking, utility restart, and recovery processing.

Can I query the Db2 directory with SQL?

No for normal application or support work. The catalog is the SQL-queryable metadata source. The directory is maintained by Db2 and supported utilities.

What is SYSUTILX used for?

SYSUTILX stores Db2 utility execution and restart information. It helps Db2 restart or clean up utility work after interruption.