Chapter 11: Key Takeaways

These are the essential points from Chapter 11 that you will need throughout the rest of this book. If you can explain each of these clearly and from memory, you have a solid foundation for database structure definition and management.

Core Concepts to Remember

  1. DDL decisions outlive the people who make them. A CREATE TABLE statement defines a structure that may persist in production for years or decades. Data type choices, constraint definitions, and physical placement decisions are consequential in ways that DML statements are not. Get them right the first time.

  2. Data types are commitments, not suggestions. DECIMAL(15,2) for money — always. INTEGER or BIGINT for keys, chosen based on volume projections, not today's row count. VARCHAR with realistic maximums. DATE for dates. Never FLOAT for financial values, never VARCHAR for dates, never oversized maximums "just in case."

  3. Constraints are the database's enforcement layer. PRIMARY KEY, UNIQUE, FOREIGN KEY, and CHECK constraints guarantee data integrity regardless of which application, user, or batch process writes the data. Name every constraint explicitly — system-generated names are useless in production debugging.

  4. z/OS and LUW differ fundamentally in tablespace architecture. z/OS requires explicit creation of a multi-level storage hierarchy (STOGROUP > DATABASE > TABLESPACE) before tables can be created. LUW's automatic storage handles physical management with far less DBA intervention. Both platforms use buffer pools, but the binding of tablespaces to buffer pools and page sizes is an irrevocable design decision.

  5. Universal Table Spaces are the z/OS standard. Partition-by-range UTS for large tables with predictable partitioning keys. Partition-by-growth UTS for tables with unpredictable growth. Legacy tablespace types (segmented, classic partitioned) should not be used for new development.

  6. Indexes enable query performance but cost write performance. Every index on a table must be maintained for every INSERT, UPDATE, and DELETE. Index judiciously: primary keys, foreign keys, frequently filtered columns, and covering indexes for critical queries. Do not create indexes on low-cardinality columns or columns rarely used in predicates.

  7. ALTER TABLE is your tool for schema evolution. ADD COLUMN and VARCHAR length increases are safe and often online. Data type changes and column removals are expensive and may require table rebuilds. Plan for evolution by choosing flexible types up front.

  8. DROP is permanent. There is no UNDO for a DROP TABLE. Guard DROP privileges, maintain current backups, use aliases as abstraction layers, and require two-person approval for production drops.

  9. Sequences and identity columns generate unique values with different scopes. Identity columns serve single-table primary keys. Sequences serve multi-table ID generation and explicit-value scenarios. Both use caching for performance, and both produce gaps — which is normal and expected.

  10. Generated columns and row change timestamps provide computed values and change tracking without triggers. On z/OS, ROW CHANGE TIMESTAMP is maintained automatically. On LUW, triggers or application logic achieve the same result.

What to Carry Forward

As you proceed through the remaining chapters, keep these principles in mind:

  • The Meridian Bank schema is your reference. Every subsequent chapter — views, triggers, stored procedures, performance tuning, security, administration — builds on the tables, constraints, and indexes defined in this chapter. Know the schema intimately.

  • Physical design decisions have performance implications. The tablespace choices, page sizes, buffer pool assignments, and index strategies from this chapter directly affect the query performance you will tune in Part V. When a query is slow, the first question is often: "Was the table designed correctly?"

  • Schema changes require operational planning. The ALTER TABLE operations from this chapter are not just SQL statements — they are operational events that may require REORG, CHECK DATA, or application restarts. Chapter 17 (DB2 Utilities) will show you how to manage these operations.

  • DDL belongs in version control. Treat your DDL scripts like application code: versioned, reviewed, tested, and deployed through a consistent pipeline. The migration scripts you write with ALTER TABLE are as important as the initial CREATE TABLE scripts.

Preview of Chapter 12

In the next chapter, you will build the programmable layer on top of the physical structures defined here:

  • Views that provide logical abstractions over your tables
  • Triggers that execute automatically in response to data changes
  • Stored procedures that encapsulate business logic in the database

Each of these objects depends on the tables, columns, and constraints you learned to create in this chapter. The quality of your DDL determines the quality of everything built on top of it.

Return to Chapter 11 | Continue to Further Reading