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.

Db2 Relational Database Anatomy: Tables, Rows, Columns, Keys, and Views

Tables give SQL a structure to query.

A COBOL program does not read a Db2 table by track number or page location. It issues SQL against a logical structure: tables, rows, columns, keys, relationships, and views. That separation is one reason a Db2 application can keep working even when storage, indexes, or access paths change underneath it.

This refresh explains the anatomy of a relational database from a Db2 application developer's point of view. The focus is not catalog administration. The focus is the structure a COBOL or SQL developer needs to understand before writing SELECT, INSERT, UPDATE, DELETE, and cursor logic.

What Relational Database Anatomy Means

A relational database stores data in tables. A table has rows and columns. Keys identify rows, foreign keys connect tables, and views present selected data from one or more base tables.

Db2 uses that logical model while the engine handles physical storage, access paths, locking, logging, and recovery. A developer usually works with the logical model first.

Tables Represent One Subject

A table should represent one subject, such as a customer, account, policy, claim, employee, transaction, or order. Mixing subjects in one table makes SQL harder to read and makes update rules harder to control.

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

In this example, the subject is one customer. Account balances, claim lines, and policy coverage rows belong in separate tables unless the business rule says otherwise.

Columns Store Named Values

A column stores one named value about the table subject. The column name should tell the reader what the value means, and the data type should match how Db2 and COBOL will use it.

Column	Meaning	Db2 type example	COBOL concern
CUST_NO	Customer identifier	CHAR(10)	Match host variable length exactly.
CUST_NAME	Customer display name	VARCHAR(60)	Use a varying-length host structure when needed.
STATUS	Business state	CHAR(1)	Validate expected values such as A or I.
OPEN_DATE	Date the customer was opened	DATE	Use the date format expected by the program and Db2.

Rows Represent Individual Facts

A row is one instance of the table subject. In a CUSTOMER table, one row represents one customer. In an ACCOUNT_TXN table, one row represents one transaction.

Rows have no guaranteed physical order from a SQL point of view. If a COBOL report needs account transactions by date, the SQL must say so:

SELECT TXN_DATE,
       TXN_AMT
  FROM ACCOUNT_TXN
 WHERE ACCT_NO = :WS-ACCT-NO
 ORDER BY TXN_DATE

Without ORDER BY, a program should not depend on row order.

Primary Keys Identify Rows

A primary key identifies one row in a table. It can be one column or a set of columns. A good primary key is stable, unique, and not nullable.

CREATE TABLE ACCOUNT
 (ACCT_NO     CHAR(12) NOT NULL,
  CUST_NO     CHAR(10) NOT NULL,
  BALANCE     DECIMAL(13,2),
  PRIMARY KEY (ACCT_NO));

In application work, primary keys show up in WHERE clauses, cursor predicates, update statements, delete statements, and joins. A weak key design usually becomes a maintenance problem later.

Composite Keys Use More Than One Column

A composite key uses two or more columns to identify a row. Linking tables often use composite keys because a row is unique only when two parent keys are combined.

CREATE TABLE ENROLLMENT
 (STUD_ID     CHAR(8) NOT NULL,
  COURSE_ID   CHAR(8) NOT NULL,
  ENROLL_DATE DATE,
  PRIMARY KEY (STUD_ID, COURSE_ID));

A COBOL program must provide all key columns when it reads or updates a row identified by a composite key.

Foreign Keys Connect Tables

A foreign key stores a value that points to a parent table key. This is how relational databases avoid disconnected business facts, such as an order without a customer.

ALTER TABLE ACCOUNT
  ADD CONSTRAINT FK_ACCOUNT_CUSTOMER
  FOREIGN KEY (CUST_NO)
  REFERENCES CUSTOMER (CUST_NO);

The related Db2 Relationships article explains one-to-one, one-to-many, and many-to-many patterns in more detail.

Views Present a Useful Shape of Data

A view is a named SQL definition that presents data from one or more base tables. A view can hide columns, join tables, simplify a query, or expose a controlled read-only shape to programs.

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

A program can then read the view as if it were a table:

SELECT CUST_NAME
  INTO :WS-CUST-NAME
  FROM ACTIVE_CUSTOMER_V
 WHERE CUST_NO = :WS-CUST-NO

For view types and update considerations, see Db2 View Classification.

Logical Design Is Separate from Physical Storage

The relational model lets the application work with tables and columns while Db2 manages physical storage. A table can have indexes, table spaces, buffer pools, partitions, and statistics that affect performance without changing the SELECT list a program uses.

That separation is useful, but it is not a reason to ignore physical design. A COBOL cursor can be logically correct and still slow if the access path is poor. Use Db2 Indexing and Db2 SQL Optimization Tips for COBOL Programs when performance matters.

How COBOL Uses the Anatomy

A COBOL program usually sees the relational anatomy through host variables and embedded SQL:

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

The SELECT list maps to columns. The WHERE clause uses a key. The host variables must match the Db2 column types closely enough to avoid conversion problems or unexpected truncation.

Common Beginner Mistakes

• Treating row order as fixed without using ORDER BY.
• Using SELECT * in production cursors when the program needs only a few columns.
• Putting multiple values in one column instead of using child rows.
• Choosing a primary key that can change during normal business processing.
• Confusing a view with stored data when the view only stores a query definition.

Anatomy Checklist Before Writing SQL

Question	Why it matters
What table owns the data?	Prevents joining to a table that only looks similar.
What row should qualify?	Drives the WHERE clause and host variables.
What columns are actually needed?	Keeps SELECT lists and COBOL target variables clean.
What key identifies the row?	Reduces accidental duplicate reads or updates.
Is a view hiding any filter or join?	Prevents surprise predicates or read-only behavior.

How This Fits with Db2 Objects

This article covers logical relational anatomy: tables, rows, columns, keys, relationships, and views. The later Db2 Objects article is the better place for a wider object list such as databases, table spaces, indexes, aliases, and packages.

For current product context, see IBM's Db2 for z/OS page and the ISO SQL framework.

FAQ

What are the basic parts of a relational database?

The basic parts are tables, rows, columns, keys, relationships, and views. Db2 also has physical and administrative objects, but those are separate from the basic logical model.

What is the difference between a row and a column?

A row is one instance of the table subject, such as one customer. A column is one named value about that subject, such as customer number or status.

Is a Db2 view the same as a table?

No. A view presents data through a stored query definition. The data comes from one or more base tables unless a specific Db2 feature stores results separately.

When the table subject, columns, keys, and relationships are clear, the embedded SQL in a COBOL program becomes easier to write, test, bind, and maintain.

Db2 View Classification: Simple, Join, Aggregate, and Updatable Views



Db2 views are classified by definition and update rules.

A COBOL program can select from a Db2 view exactly as it selects from a table, but not every view behaves the same way. A simple view over one table might support updates. A join view or aggregate view is normally read-only. A security view might hide salary columns from most programs while still showing employee names and departments.

This refresh keeps the existing classification URL and focuses on the practical question: what kind of view are you using, and what does that mean for COBOL SQL, data changes, security, and performance?

What Is a Db2 View?

A Db2 view is a named query definition. IBM's CREATE VIEW documentation states that the statement creates a view on tables or views at the current server, and the fullselect defines the rows returned by the view.

A view does not usually store its own base-table rows. When a program queries the view, Db2 processes the view definition with the SQL statement that references it.

Simple Views

A simple view is based on one table and usually selects a subset of columns or rows. It is often used to give programs a cleaner interface or to hide columns that should not be exposed.

CREATE VIEW HR.ACTIVE_EMP_VIEW AS
  SELECT EMPNO, FIRSTNME, LASTNAME, WORKDEPT
    FROM HR.EMPLOYEE
   WHERE STATUS = 'A';

A COBOL program can query this view without carrying the active-status predicate in every cursor.

EXEC SQL
    SELECT LASTNAME, WORKDEPT
      INTO :WS-LASTNAME, :WS-DEPT
      FROM HR.ACTIVE_EMP_VIEW
     WHERE EMPNO = :WS-EMPNO
END-EXEC.

Join Views

A join view combines columns from two or more tables. It can make reporting SQL easier to read, but it can also hide join cost from the caller.

CREATE VIEW HR.EMP_DEPT_VIEW AS
  SELECT E.EMPNO,
         E.LASTNAME,
         D.DEPTNAME
    FROM HR.EMPLOYEE E
    JOIN HR.DEPARTMENT D
      ON D.DEPTNO = E.WORKDEPT;

Before using a join view in a high-volume batch program, explain the final SQL that references the view. Do not assume the view is cheap because the outer query looks short.

Aggregate Views

An aggregate view uses functions such as COUNT, SUM, MIN, or MAX, usually with GROUP BY. These views are useful for summaries, dashboards, and control totals.

CREATE VIEW HR.DEPT_HEADCOUNT AS
  SELECT WORKDEPT,
         COUNT(*) AS EMP_COUNT
    FROM HR.EMPLOYEE
   GROUP BY WORKDEPT;

Aggregate views are not a replacement for proper summary tables when the data volume is large and the summary is queried constantly. In those cases, discuss materialized query tables or batch-maintained summary tables with the DBA.

Read-Only and Updatable Views

The most important classification for application code is whether a view can be updated. A simple one-table view may allow insert, update, or delete operations when it satisfies Db2 rules. Views that include joins, aggregates, grouping, distinct results, or certain expressions are normally read-only.

View pattern	Typical classification	Application impact
One table, direct columns	Potentially updatable	Can sometimes support update through the view.
Join view	Usually read-only	Use for selection/reporting, not data maintenance.
Aggregate view	Read-only	Use for summary queries.
View with calculated columns	Often read-only or partly restricted	Do not assume every column can be updated.

WITH CHECK OPTION

WITH CHECK OPTION matters when a view is updatable. It tells Db2 to reject inserts or updates through the view when the resulting row would not satisfy the view definition.

CREATE VIEW HR.ACTIVE_EMP_MAINT AS
  SELECT EMPNO, FIRSTNME, LASTNAME, STATUS
    FROM HR.EMPLOYEE
   WHERE STATUS = 'A'
  WITH CASCADED CHECK OPTION;

Without a check option, an update through a view can sometimes make the row disappear from that same view. That behavior is confusing in maintenance programs, so make the rule explicit when the view is intended for updates.

Security Views

A security view exposes only the rows and columns a caller should see. For example, one view might show employee number, name, and department while hiding salary and tax identifiers.

Security views are only part of the control. Grants, ownership, package authorization, and application roles still matter. Do not treat a view as a complete security model by itself.

Performance Checks

A view can make SQL easier to read, but Db2 still has to process the underlying fullselect. For performance work, explain the complete statement that references the view.

• Check whether predicates can be pushed into the view definition.
• Review indexes on base tables, not on the view name.
• Watch for hidden joins or aggregations in views used by batch jobs.
• Avoid stacking several views if it hides the real query shape.
• Use the related Db2 SQL Optimization Tips for COBOL Programs guide for access-path checks.

FAQ

Is a Db2 view the same as a table?

No. A view is a named query definition over tables or other views. The base-table rows remain in the underlying table spaces.

Can COBOL update a Db2 view?

Sometimes. The view must satisfy Db2 rules for updatable views, and the program must have the required privileges. Join and aggregate views are normally read-only.

Does a view improve performance?

Not by itself. A view can simplify SQL and security, but Db2 still processes the underlying query. Use EXPLAIN to check the full access path.

Classify the view before using it in application code. A reporting view, a security view, and an updatable maintenance view need different tests and different grants.