Insert sample data in Db2 for z/OS

Before inserting sample rows

Begin by understanding the target table. An INSERT must satisfy its data types, nullability rules, generated-column rules, unique keys, check constraints, and referential integrity. A script that looks valid can still fail because a department code has no matching parent row or because a salary is outside an allowed range. Looking at the table definition first is faster than guessing from an error later.

The examples below assume a training table named TRAINING.EMPLOYEE. Your authorization ID may use a different schema. Qualifying the table with its schema makes the destination unambiguous and avoids depending on CURRENT SQLID.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
CREATE TABLE TRAINING.EMPLOYEE (
    EMP_ID       INTEGER
                 GENERATED ALWAYS AS IDENTITY
                 (START WITH 1, INCREMENT BY 1),
    EMP_NAME     VARCHAR(60)   NOT NULL,
    DEPT_CODE    CHAR(3)       NOT NULL,
    JOB_TITLE    VARCHAR(40)   NOT NULL WITH DEFAULT 'TRAINEE',
    HIRE_DATE    DATE          NOT NULL WITH DEFAULT CURRENT DATE,
    SALARY       DECIMAL(9,2),
    ACTIVE_FLAG  CHAR(1)       NOT NULL WITH DEFAULT 'Y',
    CONSTRAINT PK_TRAIN_EMP PRIMARY KEY (EMP_ID),
    CONSTRAINT CK_TRAIN_ACTIVE CHECK (ACTIVE_FLAG IN ('Y', 'N')),
    CONSTRAINT CK_TRAIN_SALARY CHECK (SALARY IS NULL OR SALARY >= 0)
);

This design gives Db2 responsibility for EMP_ID. JOB_TITLE, HIRE_DATE, and ACTIVE_FLAG have defaults. SALARY is nullable, so a row can represent an employee whose salary is not known yet. EMP_NAME and DEPT_CODE are required and have no defaults; every INSERT must provide them.

INSERT VALUES: add one sample row

Use INSERT with VALUES when you already know the values for a row. Put target columns after the table name, then put values in exactly the same order. The number of values must equal the number of listed columns, and each expression must be compatible with its target data type.

1
2
3
4
INSERT INTO TRAINING.EMPLOYEE
       (EMP_NAME, DEPT_CODE, JOB_TITLE, HIRE_DATE, SALARY)
VALUES ('ALEX MORGAN', 'A10', 'APPLICATION DEVELOPER',
        DATE('2026-01-12'), 62000.00);

EMP_ID is omitted, so Db2 generates it. ACTIVE_FLAG is also omitted, so it receives its default of Y. Date conversion is explicit, which makes the intent clear and avoids depending on an installation-specific date format. Decimal salary is written without currency symbols or thousands separators because it is a numeric value, not display text.

Always prefer an explicit column list

Db2 permits an INSERT without a target column list when values are supplied for the table columns in their defined order. That shortcut is fragile. A future ALTER TABLE can add a column, and a reader must inspect the DDL to discover what each value means. Explicit names make review easier and let omitted columns use their defaults.

1
2
3
4
5
6
7
-- Clear and resilient
INSERT INTO TRAINING.EMPLOYEE
       (EMP_NAME, DEPT_CODE, SALARY)
VALUES ('PRIYA SHAH', 'B20', 58500.00);
 
-- Avoid positional inserts that depend on every table column
-- INSERT INTO TRAINING.EMPLOYEE VALUES (...);

The first statement documents the mapping directly: the first value is EMP_NAME, the second is DEPT_CODE, and the third is SALARY. JOB_TITLE becomes TRAINEE, HIRE_DATE becomes the current date, and ACTIVE_FLAG becomes Y. That is deliberate defaulting, not missing data.

Identity values, DEFAULT, and NULL

These three ideas are easy to confuse. An identity column generates a unique sequence value according to its definition. The DEFAULT keyword asks Db2 to apply a column's default. NULL means the value is unknown or not applicable; it does not mean zero, blank, or default.

• GENERATED ALWAYS identity — normally omit the column or specify DEFAULT so Db2 creates the key.
• Defaulted column — omit it from the target list or place DEFAULT in its matching VALUES position.
• Nullable column — specify NULL when “unknown” is the intended information.
• NOT NULL without a default — supply a valid non-null value or the statement fails.

1
2
3
INSERT INTO TRAINING.EMPLOYEE
       (EMP_NAME, DEPT_CODE, JOB_TITLE, HIRE_DATE, SALARY, ACTIVE_FLAG)
VALUES ('MATEO RUIZ', 'A10', DEFAULT, DEFAULT, NULL, DEFAULT);

This row intentionally says that Mateo's salary is unknown. The other DEFAULT markers request TRAINEE, the current date, and Y. If SALARY were NOT NULL, the explicit NULL would fail. If EMP_NAME were omitted, that would also fail because its required column has no default.

Insert several known sample rows

The most portable approach is a sequence of ordinary INSERT statements inside one transaction. It works across supported Db2 for z/OS releases, is easy to diagnose, and lets you give each row a readable comment. Run them without committing between rows when the complete sample set should succeed or fail together.

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO TRAINING.EMPLOYEE
       (EMP_NAME, DEPT_CODE, JOB_TITLE, SALARY)
VALUES ('LINDA CHEN', 'A10', 'SYSTEMS ANALYST', 71000.00);
 
INSERT INTO TRAINING.EMPLOYEE
       (EMP_NAME, DEPT_CODE, JOB_TITLE, SALARY)
VALUES ('NOAH WILLIAMS', 'B20', 'DATABASE ADMINISTRATOR', 79000.00);
 
INSERT INTO TRAINING.EMPLOYEE
       (EMP_NAME, DEPT_CODE, JOB_TITLE, SALARY, ACTIVE_FLAG)
VALUES ('SARA IBRAHIM', 'C30', 'TEST ANALYST', 54000.00, 'Y');

Recent Db2 levels offer a shorter multi-row VALUES list. On Db2 13 for z/OS this form requires function level 506 and an APPLCOMPAT level that enables the feature. Do not copy it into an older subsystem without checking the package and subsystem level.

1
2
3
4
5
6
-- Db2 13 FL506 with APPLCOMPAT V13R1M506 or later
INSERT INTO TRAINING.EMPLOYEE
       (EMP_NAME, DEPT_CODE, JOB_TITLE, SALARY)
VALUES ('EMMA DAVIS', 'A10', 'PROGRAMMER', 57000.00),
       ('JAMES KIM', 'B20', 'PROGRAMMER', 59000.00),
       ('OLIVIA MARTIN', 'C30', 'BUSINESS ANALYST', 61000.00);

Application programs can also use multiple-row INSERT with host-variable arrays and FOR n ROWS. That reduces trips between a batch program and Db2. ATOMIC behavior treats the array as one statement; NOT ATOMIC CONTINUE ON SQLEXCEPTION can preserve successful array elements while reporting failures. Choose deliberately: partial success is useful only when the program records exactly which inputs failed.

INSERT SELECT: generate or copy sample data

INSERT SELECT is the set-based choice when source rows already exist or when a query can generate the desired rows. The SELECT is called a fullselect. Its number of output columns must match the INSERT target list, in order, and the source data types must be assignable to the targets.

1
2
3
4
5
6
7
8
9
10
11
INSERT INTO TRAINING.EMPLOYEE
       (EMP_NAME, DEPT_CODE, JOB_TITLE, HIRE_DATE, SALARY, ACTIVE_FLAG)
SELECT LASTNAME CONCAT ', ' CONCAT FIRSTNME,
       WORKDEPT,
       JOB,
       HIREDATE,
       SALARY,
       'Y'
  FROM DSN8C10.EMP
 WHERE WORKDEPT IN ('A00', 'B01')
   AND SALARY IS NOT NULL;

Db2 evaluates the query and inserts every qualifying result row. Identity values are generated separately for the new target rows because EMP_ID is not in the target list. Constraints and triggers still run for each inserted row. INSERT SELECT does not bypass data quality rules merely because its source is another table.

Prevent accidental duplicate sample rows

A rerunnable setup script needs a stable rule. If the table has a business key, filter source rows with NOT EXISTS. In this training table, names are not guaranteed unique, so this pattern is suitable only if your exercise defines name plus department as its temporary sample key. Production tables should enforce a real business key with a unique constraint.

1
2
3
4
5
6
7
8
9
INSERT INTO TRAINING.EMPLOYEE
       (EMP_NAME, DEPT_CODE, JOB_TITLE, HIRE_DATE, SALARY)
SELECT S.EMP_NAME, S.DEPT_CODE, S.JOB_TITLE, S.HIRE_DATE, S.SALARY
  FROM TRAINING.EMPLOYEE_STAGE S
 WHERE NOT EXISTS
       (SELECT 1
          FROM TRAINING.EMPLOYEE T
         WHERE T.EMP_NAME = S.EMP_NAME
           AND T.DEPT_CODE = S.DEPT_CODE);

Another safe classroom approach is to delete only rows tagged with a dedicated test batch identifier before reinserting them. Avoid deleting all rows from a shared table. Sample-data cleanup must be as carefully scoped as the insert itself.

How constraints protect the sample set

Constraints are not obstacles to turn off; they are the table's safety checks. A primary key or unique constraint rejects duplicate keys, commonly reported as SQLCODE -803. Referential integrity rejects a child row whose parent key does not exist, commonly SQLCODE -530. A NOT NULL violation can produce SQLCODE -407, while a check constraint violation can produce SQLCODE -545.

• Insert parent rows before child rows when a foreign key links the tables.
• Use valid code values so CHECK constraints accept the row.
• Match character lengths and numeric precision to avoid truncation or overflow.
• Do not assume a generated identity prevents every duplicate; business keys may still need their own unique constraint.

When one ordinary INSERT statement fails, that statement's changes are backed out, but earlier successful statements in the same unit of recovery remain uncommitted. Your program or script must decide whether to correct and continue or issue ROLLBACK to undo the whole sample set.

Transactions: COMMIT or ROLLBACK the sample set

A transaction groups related changes into a unit of recovery. COMMIT makes all changes since the previous commit permanent. ROLLBACK backs out the uncommitted changes. For a small teaching data set, insert all rows, validate them, and commit once only when the set is complete.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
INSERT INTO TRAINING.EMPLOYEE
       (EMP_NAME, DEPT_CODE, JOB_TITLE, SALARY)
VALUES ('AVA THOMPSON', 'A10', 'DEVELOPER', 64000.00);
 
INSERT INTO TRAINING.EMPLOYEE
       (EMP_NAME, DEPT_CODE, JOB_TITLE, SALARY)
VALUES ('LIAM BROWN', 'B20', 'DATA ANALYST', 60000.00);
 
SELECT DEPT_CODE, COUNT(*) AS ROW_COUNT
  FROM TRAINING.EMPLOYEE
 WHERE EMP_NAME IN ('AVA THOMPSON', 'LIAM BROWN')
 GROUP BY DEPT_CODE;
 
-- Choose one after checking the result:
COMMIT;
-- ROLLBACK;

Never place COMMIT before validation merely to make an error disappear. Once committed, ROLLBACK cannot remove those rows. In CICS or IMS, the transaction manager normally owns the syncpoint; use the environment's transaction API instead of independently issuing SQL COMMIT. In JDBC, check whether autocommit is enabled because it may commit every statement immediately.

Batch safety and useful diagnostics

Large sample sets and test-data generators need the same operational discipline as production batch. Establish the expected input count, record the starting state, use a repeatable key strategy, and stop on unexpected negative SQLCODEs. A warning or zero-row result can also be important even when it is not a hard SQL error.

• Check SQLCODE after every data-changing statement. SQLCODE 0 means success; negative values are errors; positive values require review.
• Check the affected count. In embedded SQL, SQLERRD(3) normally holds the number of rows inserted. GET DIAGNOSTICS can retrieve statement information in supported application contexts.
• Use reasonable commit points. For a large batch, commit restartable chunks to limit locks and rollback time, not arbitrary chunks that cannot be reconstructed.
• Record a restart position. Save the last committed source key or batch identifier so a restart does not insert the same rows again.
• Reconcile counts. Compare source rows selected, rows inserted, rows rejected, and rows already present. Those categories should explain the complete input.

1
2
3
4
5
6
7
8
9
10
11
12
13
       EXEC SQL
            INSERT INTO TRAINING.EMPLOYEE
                   (EMP_NAME, DEPT_CODE, JOB_TITLE, SALARY)
            VALUES (:WS-NAME, :WS-DEPT, :WS-JOB,
                    :WS-SALARY:WS-SALARY-IND)
       END-EXEC.
 
       IF SQLCODE = 0
           ADD 1 TO WS-INSERT-COUNT
       ELSE
           MOVE SQLCODE TO WS-FAILED-SQLCODE
           EXEC SQL ROLLBACK END-EXEC
       END-IF.

The indicator paired with WS-SALARY lets the program insert NULL when salary is unknown. In real COBOL, stop normal processing after rollback, log the source record key and SQL diagnostic fields, and return a failing condition code. Continuing without understanding an error can turn one rejected row into a misleading partial data set.

Verify the rows before moving on

Verification should answer three questions: did the expected number of rows arrive, did defaults and generated values behave correctly, and are the business values accurate? Select named columns, sort the result for repeatable viewing, and aggregate counts by a useful category.

1
2
3
4
5
6
7
8
9
10
11
12
13
14
SELECT EMP_ID, EMP_NAME, DEPT_CODE, JOB_TITLE,
       HIRE_DATE, SALARY, ACTIVE_FLAG
  FROM TRAINING.EMPLOYEE
 ORDER BY EMP_ID;
 
SELECT DEPT_CODE, COUNT(*) AS EMPLOYEE_COUNT
  FROM TRAINING.EMPLOYEE
 GROUP BY DEPT_CODE
 ORDER BY DEPT_CODE;
 
SELECT COUNT(*) AS INVALID_ACTIVE_FLAGS
  FROM TRAINING.EMPLOYEE
 WHERE ACTIVE_FLAG NOT IN ('Y', 'N')
    OR ACTIVE_FLAG IS NULL;

The last query should return zero. Although the constraint already prevents invalid active flags, validation queries document your expectations and teach you how to test a load. Do not use SELECT * in long-lived application code; named columns make the expected result shape clear.

Explain It Like I'm Five

Imagine the table is a box of employee cards. INSERT VALUES means you write one card yourself. INSERT SELECT means a copier reads cards from another box and makes new cards for this box. The column list labels the blanks so a name never lands in the salary space. DEFAULT is a helper stamp that fills in a normal answer, NULL means “we do not know this answer,” and identity is the ticket machine giving every card a number. Constraints are the grown-up checking that every card follows the rules. COMMIT locks the finished cards into the box; ROLLBACK takes out all cards you added since the last time you locked it.

Exercises

1. Insert an employee named Jordan Lee in department A10. Supply EMP_NAME and DEPT_CODE only, then predict the generated or defaulted values before running a SELECT.
2. Write an INSERT that stores NULL for SALARY and uses DEFAULT for JOB_TITLE. Explain why NULL and DEFAULT communicate different meanings.
3. Create an INSERT SELECT that copies only active staging rows into the employee table. Make the target and SELECT column order explicit.
4. Add a NOT EXISTS predicate that prevents your INSERT SELECT from copying a known business key twice.
5. Design a test with two INSERT statements where the second violates ACTIVE_FLAG. Run ROLLBACK and verify that neither uncommitted row remains.
6. Sketch a restart plan for 10,000 sample rows committed every 1,000 rows. State what key or batch identifier you would save after each successful commit.