Create Your First Db2 Table

What you are going to build

A relational table stores facts in rows and describes each fact with columns. Our STUDENT table will hold a generated identifier, a first name, a last name, an optional email address, an enrollment date, an active/inactive status, and the timestamp at which the row was created. That is enough variety to practice common data types and rules without hiding the basic CREATE TABLE structure.

Creating a table is a data definition language (DDL) operation. Unlike a SELECT, DDL changes Db2 catalog metadata and creates a persistent database object. Treat even a beginner exercise as a controlled change. Work only in a subsystem, database, and table space assigned for training. Never copy the sample into production merely because the syntax is valid.

Before you start: choose safe names and a target

Ask your instructor or DBA for four pieces of information: the Db2 subsystem identifier, your schema or authorization ID, the database name, and the table-space name. The examples use schema TRAINING, database TRNDB, and table space STUDTS. Those names are teaching placeholders. Replace them with approved values before execution.

• Confirm that you are connected to development or training Db2, not production.
• Check that TRAINING.STUDENT does not already exist; CREATE TABLE does not replace an existing object.
• Obtain permission to use the assigned database and table space.
• Save your reviewed SQL in a versioned PDS or PDSE member so the exact input is known.
• Do not add DROP TABLE to make a rerunnable script. A drop is destructive and should require a separate, explicit decision.

Understand schemas before CREATE TABLE

A schema is a logical qualifier for database objects. In the two-part name TRAINING.STUDENT, TRAINING is the schema and STUDENT is the table name. Another schema could own a different STUDENT table, so the qualifier removes ambiguity. Schema qualification also makes review easier: the target is visible in the SQL instead of being inherited silently from the current SQL authorization ID.

On Db2 for z/OS, an unqualified object name is resolved using rules that include the current SQLID and execution context. That can be convenient for interactive work, but it can surprise a beginner when the same member runs under another ID. Use qualified names in DDL, validation queries, and deployment scripts. Your ability to create an object in a schema still depends on privileges; typing a qualifier does not grant ownership or authority.

The complete CREATE TABLE example

Read the statement once before running it. A comma separates each column definition and the table-level primary-key constraint. The final IN clause chooses the physical database and table space. A semicolon ends each SQL statement for tools that accept semicolon termination.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
-- Training example: replace all site-specific names before execution.
CREATE TABLE TRAINING.STUDENT
(
    STUDENT_ID     INTEGER
                   GENERATED ALWAYS AS IDENTITY
                   (START WITH 1 INCREMENT BY 1),
    FIRST_NAME     VARCHAR(40) NOT NULL,
    LAST_NAME      VARCHAR(60) NOT NULL,
    EMAIL_ADDRESS  VARCHAR(254),
    ENROLL_DATE    DATE NOT NULL DEFAULT CURRENT DATE,
    STATUS_CODE    CHAR(1) NOT NULL DEFAULT 'A',
    CREATED_TS     TIMESTAMP NOT NULL DEFAULT CURRENT TIMESTAMP,
    CONSTRAINT PK_STUDENT
        PRIMARY KEY (STUDENT_ID)
)
IN TRNDB.STUDTS;
 
-- Use an approved index name and storage conventions.
CREATE UNIQUE INDEX TRAINING.IX_STUDENT_PK
    ON TRAINING.STUDENT (STUDENT_ID);

Db2 needs a unique enforcing index for a primary key. The explicit CREATE UNIQUE INDEX makes the intended key access path and index name visible, but local standards can control who creates it, where it is stored, and which options it needs. Ask your DBA whether the table and index statements should be submitted together and whether an existing site automation process supplies the enforcing index.

Choose beginner-friendly Db2 data types

INTEGER for whole-number identifiers

INTEGER stores whole numbers. It is a practical first identity type because it is easy to read and supports a large range. Use SMALLINT when the range is definitely small and BIGINT when billions of values may not be enough. A generated key is an internal identifier, not a student's meaningful business number, phone number, or government identifier.

CHAR and VARCHAR for text

CHAR(1) always reserves a fixed one-character value, which suits a compact status code such as A or I. VARCHAR(40) stores text up to the declared limit and is more appropriate for names whose lengths vary. VARCHAR(254) leaves room for typical email addresses, but the type alone does not prove that a value is a valid email address. Length choices should come from requirements and encoding standards, not guesswork.

DATE and TIMESTAMP for time values

DATE stores a calendar date without a time of day. TIMESTAMP records date and time with fractional seconds according to its precision. Do not store dates in VARCHAR columns: native temporal types validate the value and support date arithmetic, comparison, and formatting. CURRENT DATE and CURRENT TIMESTAMP are special registers evaluated by Db2 when the row is inserted.

DECIMAL for exact numeric values

This first table does not need an amount, but a tuition or account table commonly uses DECIMAL. For example, DECIMAL(9,2) permits nine total digits, two after the decimal point. Use DECIMAL for exact money-like values instead of a floating-point type. The precision and scale must be large enough for the maximum valid business value.

NOT NULL, defaults, and identity values

A nullable column can contain the null marker, meaning the value is unknown or absent. EMAIL_ADDRESS is nullable because a student may not have supplied an email address. FIRST_NAME and LAST_NAME are NOT NULL because this training design requires them for every row. NOT NULL does not reject an empty string or blanks by itself; application validation or an appropriate CHECK constraint handles those rules.

A DEFAULT supplies a value when an INSERT omits that column. ENROLL_DATE defaults to the current date, STATUS_CODE defaults to A, and CREATED_TS defaults to the current timestamp. Defaults reduce repeated input, but they must express a truthful rule. Do not default a required fact to a made-up value merely to avoid an error.

STUDENT_ID is GENERATED ALWAYS AS IDENTITY. Db2 generates 1, then 2, then 3, following the declared start and increment. Applications normally omit this column from INSERT. Identity values provide uniqueness, not a promise of gap-free numbering: rollback, caching, restart, or administrative activity can leave gaps. Never use an identity sequence where every number must be present for legal or accounting reasons.

Define the primary key

A primary key identifies each row. The constraint PK_STUDENT says that STUDENT_ID values must be unique and cannot be null. Naming the constraint makes catalog inspection and error investigation clearer than relying on a generated name. For a multi-column key, list all key columns inside the parentheses in the correct order.

The primary key is a data-integrity rule, while its unique index is the physical structure Db2 uses to enforce uniqueness and find keys efficiently. Extra indexes for searches such as LAST_NAME are separate design decisions. Do not create indexes on every column: each index consumes storage and adds maintenance work to INSERT, UPDATE, DELETE, LOAD, and utilities.

Table-space considerations on Db2 for z/OS

A table does not live in a workstation file. Its rows occupy a Db2 table space within a database. The clause IN TRNDB.STUDTS places STUDENT in the assigned space. Modern sites commonly use universal table spaces, but page size, segmentation, partitioning, buffer pool, encoding, logging, compression, storage groups, and data-set allocation are site design choices.

A DBA may create the database and table space before the application team creates the table. Some environments permit implicit database or table-space creation when IN is omitted, but relying on that behavior can place an object in an unsuitable default location. For a learning exercise, use a pre-created space. For production, document expected row size, growth, availability, recovery, utility windows, partitioning needs, and related indexes before selecting storage attributes.

Authorization: syntax is not permission

Db2 checks the authorization ID that runs the DDL. The exact privileges depend on the target, ownership model, security administration, and subsystem configuration. You may need authority to create tables, use or create objects in the selected database and table space, use the schema, and create the enforcing index. Elevated authorities such as DBADM or installation-level privileges are not appropriate shortcuts for ordinary learners.

If execution returns an authorization SQLCODE, stop and read the full message. Record the authorization ID, subsystem, object, privilege named in the diagnostic, and the SQLSTATE. Ask the security administrator or DBA for the least privilege required by the approved exercise. Repeatedly changing qualifiers until something succeeds risks creating an object in the wrong schema or location.

Run the DDL with SPUFI

SPUFI, the SQL Processor Using File Input, is available through DB2I in many TSO/ISPF environments. Put the reviewed statements in an input data-set member and allocate a sequential output data set. On the SPUFI panel, verify the subsystem, input member, output data set, statement terminator, and processing options. Be especially aware of autocommit and the fact that Db2 DDL has commit behavior that differs from treating every statement as freely reversible DML.

• Open the input member and verify all schema, database, and table-space names.
• Execute only the approved CREATE TABLE and CREATE INDEX statements.
• Browse the output immediately and find the result for each statement.
• Require SQLCODE 0 for ordinary success; investigate warnings rather than ignoring them.
• Retain the input and output as evidence of what ran and where.

Run repeatable DDL with DSNTEP2

DSNTEP2 is an IBM-supplied sample dynamic SQL program commonly run in batch. Your shop provides approved JCL, libraries, plan or package names, subsystem attachment, input DDs, and output conventions. Place the same reviewed SQL in the designated input and submit the standard job. Then inspect the job return code, DSNTEP2 report, SQLCODE, and SQLSTATE for every statement.

DSNTEP2 is useful when the action must be repeatable and auditable. The batch job records the exact target, execution identity, input, timing, and output. Do not invent JCL by copying STEPLIB, plan, or subsystem values from another environment. Start with a site-owned skeleton, and route production DDL through the organization's change and deployment process.

Validate the table safely

“The job ended” does not mean “the right object exists.” First, confirm that each statement received the expected SQLCODE. Next, query the catalog with uppercase values for an ordinary unquoted identifier. Finally, issue a harmless SELECT against the new, empty table. These checks establish the table's identity, its columns, and its usability without inserting or deleting data.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
-- 1. Confirm the table and its table-space placement.
SELECT CREATOR, NAME, DBNAME, TSNAME, TYPE
  FROM SYSIBM.SYSTABLES
 WHERE CREATOR = 'TRAINING'
   AND NAME = 'STUDENT';
 
-- 2. Confirm column order, types, lengths, nullability, and defaults.
SELECT COLNO, NAME, COLTYPE, LENGTH, SCALE, NULLS, DEFAULT
  FROM SYSIBM.SYSCOLUMNS
 WHERE TBCREATOR = 'TRAINING'
   AND TBNAME = 'STUDENT'
 ORDER BY COLNO;
 
-- 3. Safely reference the empty table.
SELECT STUDENT_ID, FIRST_NAME, LAST_NAME
  FROM TRAINING.STUDENT
 FETCH FIRST 10 ROWS ONLY;

The final SELECT should normally return no rows on a brand-new table, and that is a successful result. If the catalog shows another database or table space, do not continue to insert data. Preserve the output and contact the DBA. If only the table exists because a later index statement failed, investigate the object state and complete the approved definition instead of blindly rerunning the entire member.

Common first-table mistakes

• Wrong subsystem: the SQL is correct, but the TSO or batch connection points to another Db2 environment.
• Unqualified name: STUDENT is created under an unexpected current SQLID.
• Wrong comma: a missing comma between columns or an extra comma before the closing parenthesis produces a syntax error.
• Weak data type: a date is stored as text, an exact amount uses floating point, or a VARCHAR length cannot hold valid values.
• Misused default: a fake zero or blank hides missing business data.
• Assumed rollback: the operator expects DDL to behave like an uncommitted row update and discovers that catalog changes are already committed.
• Partial success: CREATE TABLE succeeds and CREATE INDEX fails, but the first successful statement is overlooked.

Explain It Like I'm Five

Imagine making a new box for student cards. The schema is the label on the cupboard, and STUDENT is the label on the box. Columns are the blanks printed on every card: name, email, date, and status. NOT NULL means a blank must be filled. DEFAULT means Db2 fills it with an agreed answer when you do not. The identity column is a ticket machine that gives each card a new number. The primary key rule says two cards cannot use the same ticket. The table space is the shelf that holds the box. Before putting the box there, you ask the librarian for permission and check that you are in the training library.

Exercises

1. Replace TRAINING, TRNDB, and STUDTS with approved training names. Circle every part that is site-specific and must be reviewed before execution.
2. Add an optional BIRTH_DATE column using DATE. Explain why it should not default to CURRENT DATE.
3. Add a NOT NULL PROGRAM_CODE column with no default. Describe what an INSERT must supply.
4. Design a DECIMAL column for an exact amount up to 999,999.99 and explain its precision and scale.
5. Write the catalog query that finds your table by creator and name. Predict what it should show for DBNAME and TSNAME.
6. Compare SPUFI and DSNTEP2 for a one-time training experiment and a reviewed production deployment.
7. Explain why GENERATED ALWAYS identity values can have gaps and why gaps do not violate a primary key.
8. Review the example as if CREATE TABLE succeeded but CREATE UNIQUE INDEX failed. List the evidence you would collect before taking another action.

Frequently asked questions