Db2 Objects Guide: Tables, Indexes, Views, Sequences, and Packages

Db2 objects are the named pieces SQL uses.

A COBOL program that runs EXEC SQL SELECT depends on more than one table name. Behind that statement are Db2 objects: a database, table space, table, columns, indexes, views, packages, and sometimes sequences or aliases. If one of those objects is missing, changed, or rebound incorrectly, the application can fail before it fetches a single row.

This refresh explains common Db2 objects from a mainframe application point of view. It keeps the broad object-reference intent, while linking out to the focused articles for table spaces, packages, views, indexing, and sequences.

What Is a Db2 Object?

A Db2 object is a named database item that Db2 can create, store, reference, secure, or use while processing SQL. Some objects hold data, some describe access paths, and some control how applications run static SQL.

A developer usually meets these objects through DDL, embedded SQL, bind jobs, promotion scripts, and catalog queries.

Db2 Object Map

Object	Main purpose	Where a developer sees it
Database	Logical container for table spaces and index spaces.	DDL, standards, storage planning.
Table space	Stores table data in Db2-managed structures.	CREATE TABLESPACE, storage, partitioning.
Table	Stores rows and columns.	SELECT, INSERT, UPDATE, DELETE.
Index	Supports access paths and uniqueness.	EXPLAIN, tuning, unique constraints.
View	Presents a named SELECT over tables or views.	Security, simplified SQL, reporting.
Sequence	Generates numeric values.	INSERT processing and surrogate keys.
Package	Stores bound static SQL for a program.	BIND PACKAGE, REBIND, promotion.
Storage group	Defines storage choices for physical objects.	DBA-managed storage definitions.

Database

In Db2 for z/OS, a database is a logical grouping for table spaces and index spaces. It is not the same as a separate server or subsystem. A database can provide defaults and ownership structure for dependent objects.

CREATE DATABASE CUSTDB;

Application developers may not create databases every day, but database names appear in DDL reviews, migration scripts, and object naming standards.

Table Space

A table space is where table data is stored. Table space design affects space, partitioning, locking behavior, utilities, and performance operations. It is one of the key places where logical design meets physical storage.

CREATE TABLESPACE CUSTTS
  IN CUSTDB
  USING STOGROUP SYSDEFLT;

For a deeper treatment of PBG, PBR, page size, and partitioning choices, see Db2 Table Spaces Guide.

Table

A table stores rows and columns for one subject. Tables are the objects that most SQL statements directly reference.

CREATE TABLE CUSTOMER
 (CUST_NO     CHAR(10) NOT NULL,
  CUST_NAME   VARCHAR(60),
  STATUS      CHAR(1),
  PRIMARY KEY (CUST_NO));

The logical structure of tables, rows, columns, and keys is covered in Db2 Relational Database Anatomy.

Column and Data Type

Columns are part of a table definition. Each column has a data type, and that data type matters when a COBOL host variable receives or sends a value.

EXEC SQL
    SELECT CUST_NAME,
           STATUS
      INTO :WS-CUST-NAME,
           :WS-STATUS
      FROM CUSTOMER
     WHERE CUST_NO = :WS-CUST-NO
END-EXEC.

The host variables in the program must be compatible with the Db2 column definitions.

Index

An index is an object based on one or more table columns. Db2 can use indexes to avoid scanning a whole table, enforce uniqueness, support clustering, and improve join access.

CREATE INDEX IX_ACCOUNT_CUST
  ON ACCOUNT (CUST_NO);

An index is not a guarantee of speed by itself. Db2 chooses access paths based on SQL predicates, statistics, indexes, and cost. The related Db2 Indexing article covers this in more detail.

View

A view is a named SQL definition. It can hide columns, join tables, apply filters, or give a program a stable query shape while base tables change behind the view.

CREATE VIEW ACTIVE_CUSTOMER_V AS
SELECT CUST_NO,
       CUST_NAME
  FROM CUSTOMER
 WHERE STATUS = 'A';

Views can be simple, joined, aggregate, read-only, or updatable depending on their definition. See Db2 View Classification for the practical types.

Alias and Synonym

Aliases and synonyms let SQL reference another object through a different name. They are often used to simplify names or hide location details. In older Db2 notes, aliases and synonyms are often discussed together, but their scope and behavior are not identical.

For application teams, the main check is simple: confirm what object the name resolves to before changing SQL, grants, or deployment scripts.

Sequence

A sequence is a Db2 object that generates numeric values. Programs often use sequences when inserting rows that need generated identifiers.

INSERT INTO ORDER_HDR
       (ORDER_ID, CUST_NO, ORDER_DATE)
VALUES (NEXT VALUE FOR ORDER_SEQ,
        :WS-CUST-NO,
        CURRENT DATE);

For sequence options such as CACHE, NO CACHE, CYCLE, and NEXT VALUE FOR, see Db2 Sequences.

Package and Plan

Packages and plans are application-facing Db2 objects. A package contains bound static SQL for a program. A plan can include packages and is used at runtime by applications.

BIND PACKAGE(COLLID) MEMBER(PROG1) ACTION(REPLACE)
BIND PLAN(APPPLAN) PKLIST(COLLID.PROG1)

When a COBOL program changes its SQL, the DBRM and package path matters. The Db2 Packages Guide for COBOL Static SQL covers DBRM, BIND PACKAGE, REBIND, collections, and promotion checks.

Storage Group and Index Space

Storage groups and index spaces are usually DBA-facing objects. A storage group defines storage choices. An index space holds index data. Application developers do not normally change them in COBOL work, but these objects appear during DDL review, utility planning, and performance discussions.

Materialized Query Table

A materialized query table stores the result of a query definition. It can help avoid repeated expensive joins or aggregations when the design and refresh rules fit the workload. Use MQTs only when the maintenance cost is justified by the query savings.

Object Dependencies

Db2 objects depend on each other. A view depends on its base tables. A package depends on the SQL it was bound with. An index depends on a table. A table depends on a table space.

If this changes	Check this next
Table column definition	Views, packages, COBOL copybooks, host variables.
Index	EXPLAIN output, access paths, RUNSTATS.
Table space	Utilities, storage, partitioning, recovery process.
Package	Bind options, collection, plan/package list, runtime job.

Common Mistakes

• Calling every named item a table when it might be a view, alias, or synonym.
• Changing a column without checking packages and COBOL host variables.
• Adding an index without running statistics or checking the access path.
• Confusing database, table space, and table as if they were the same level.
• Refreshing a package in the wrong collection during promotion.

FAQ

What are the main Db2 objects a COBOL developer should know?

A COBOL developer should know tables, columns, indexes, views, sequences, packages, plans, table spaces, and the basic dependency between those objects.

Is a Db2 package a database object?

Yes. A package is a Db2 object that stores bound static SQL for an application program. It is central to COBOL static SQL execution.

What is the difference between a table and a table space?

A table is the logical object that stores rows and columns. A table space is the storage object that holds table data.

When you know which object you are changing, you know what to check next: SQL text, DDL, indexes, packages, utilities, or COBOL host variables.