DDL - Data Definition Language
DDL (Data Definition Language) is a subset of SQL used in IBM DB2 for z/OS to define, modify, and manage the structure of database objects. It allows database administrators and developers to create the logical and physical design of a database, including tables, indexes, views, and storage groups. Data Definition Language (DDL) is a subset of SQL used to define, modify, and manage the structure of database objects within a relational database management system like DB2 for z/OS. It allows database administrators and developers to create, alter, and drop database schemas, tables, indexes, views, and other related structures.
Key Characteristics
-
- Schema Definition: DDL statements are used to define the database schema, specifying the structure of data storage and access paths within DB2 for z/OS.
- Declarative Syntax: DDL statements are declarative, meaning they describe the desired state of the database objects (e.g.,
CREATE TABLE) rather than the procedural steps to achieve it. - Impact on DB2 Catalog: Executing DDL statements updates the DB2 Catalog, which is a comprehensive set of system tables containing metadata about all database objects and their attributes.
- Primary Statements: The core DDL statements include
CREATE(to build new objects),ALTER(to modify existing objects), andDROP(to remove objects). - Execution Context: DDL can be executed interactively via tools like DB2I (DB2 Interactive) or SPUFI (SQL Processor Using File Input), or in batch mode through JCL jobs using utilities like DSNTEP2.
Use Cases
-
- Database Object Creation: Defining new tables, indexes, views, stored procedures, user-defined functions, and triggers to support new applications or data requirements.
- Schema Evolution: Modifying existing table structures, such as adding or dropping columns, changing data types, or altering constraints, to adapt to evolving business needs without data loss.
- Performance Tuning: Creating or dropping indexes to optimize query performance for COBOL or PL/I applications, or managing storage space effectively within DB2 table spaces.
- Database Maintenance: Dropping obsolete tables, views, or other objects that are no longer needed, freeing up resources and simplifying the database environment.
Related Concepts
DDL is a fundamental component of SQL (Structured Query Language), working alongside DML (Data Manipulation Language) for data interaction and DCL (Data Control Language) for security. It directly impacts the DB2 Catalog, which stores the metadata it defines. Application programs written in COBOL or PL/I interact with the database objects whose structures are defined by DDL, relying on the schema for data access and manipulation. DDL statements are often embedded within JCL jobs for batch execution and automation, particularly during system maintenance or application deployments.
- Version Control: Treat DDL scripts as critical source code; store them in a source code management system (e.g., Endevor, Git) to track changes, enable rollbacks, and facilitate schema evolution.
- Test in Non-Production: Always test DDL changes thoroughly in development and test environments before applying them to production to prevent unexpected outages or data corruption.
- Naming Conventions: Adhere to consistent and meaningful naming conventions for database objects (tables, columns, indexes) to improve readability, maintainability, and collaboration among mainframe teams.
- Grant Privileges Carefully: Use DCL statements (
GRANT,REVOKE) to assign appropriate privileges to users and applications, ensuring the principle of least privilege for DDL execution. - Consider REORG: After significant DDL changes (e.g., adding many columns, dropping large indexes), schedule a DB2 REORG utility to reorganize the affected table spaces and indexes, optimizing physical storage and access paths.