DML - Data Manipulation Language

Enhanced Definition

Data Manipulation Language (DML) is a family of programming languages used to retrieve, insert, update, and delete data in a database. In the z/OS environment, the most prominent DMLs are SQL for IBM Db2 and DL/I for IBM IMS databases, serving as the primary interface for applications and users to interact with stored data. Data Manipulation Language (DML) is a family of programming languages used to retrieve, insert, delete, and update data in a database. In the mainframe context, DML primarily refers to SQL (Structured Query Language) for relational databases like DB2 and DL/I (Data Language/I) calls for hierarchical/network databases like IMS DB. It allows application programs and users to interact with the stored data.

Key Characteristics

- SQL for Db2: For relational databases, SQL (Structured Query Language) is the standard DML, encompassing statements like SELECT (retrieve), INSERT (add new rows), UPDATE (modify existing rows), and DELETE (remove rows).
- DL/I for IMS: For hierarchical databases, Data Language/I (DL/I) provides specific calls like GU (Get Unique), GN (Get Next), ISRT (Insert), REPL (Replace), and DLET (Delete) to navigate and manipulate data segments.
- Embedded DML: DML statements are frequently embedded within host programming languages such as COBOL, PL/I, or Assembler, using special preprocessor directives (e.g., EXEC SQL for Db2, CALL CBLTDLI for IMS).
- Transactional Operations: DML operations are typically executed within the scope of a transaction, allowing for COMMIT (make changes permanent) or ROLLBACK (undo changes) to ensure data integrity and consistency.
- Data Integrity and Concurrency: DBMSs enforce data integrity rules (e.g., referential integrity, unique constraints) during DML operations and manage concurrent access to data to prevent conflicts.

Use Cases

- Batch Processing: COBOL batch programs utilize embedded SQL or DL/I calls to process large volumes of data, such as nightly financial reconciliations, payroll calculations, or generating complex reports.
- Online Transaction Processing (OLTP): CICS applications employ embedded DML to handle real-time user interactions, like updating customer records, processing order entries, or querying account balances.
- Ad-hoc Data Query and Analysis: Developers and data analysts use tools like SPUFI (SQL Processor Using File Input) or QMF (Query Management Facility) to execute SQL statements directly against Db2 for troubleshooting, data validation, or exploratory analysis.
- Application Development: Programmers integrate DML into their application logic to perform the fundamental Create, Read, Update, Delete (CRUD) operations required for business functionality.

Related Concepts

DML is intrinsically linked to the Database Management System (DBMS) itself, serving as the language through which applications and users interact with the data managed by Db2 or IMS. It operates on the data structures (tables, indexes, segments) that are defined using Data Definition Language (DDL). DML operations are often managed within the context of transaction processing, which is crucial for maintaining data integrity and recovery, especially when coordinated by transaction monitors like CICS.

Best Practices:

Optimize DML Statements: For SQL, use the EXPLAIN facility to analyze access paths and optimize query performance, ensuring efficient use of indexes and avoiding unnecessary table scans. For DL/I, understand segment search arguments (SSAs) and database design to minimize I/O.
Implement Robust Error Handling: Always check SQLCODE/SQLSTATE (for Db2) or the DL/I status code after each DML operation and implement appropriate error handling, logging, and recovery mechanisms within application programs.
Manage Transactions Prudently: Use COMMIT and ROLLBACK statements strategically to define logical units of work, ensuring data consistency and enabling efficient recovery in case of failures. Avoid excessively long transactions.
Use Host Variables for Dynamic Data: When embedding DML in programs, use host variables to pass data between the application and the database, which helps prevent SQL injection vulnerabilities and improves performance by allowing statement reuse.
Secure DML Access: Grant specific DML privileges (e.g., SELECT, INSERT, UPDATE, DELETE) to users and applications based on the principle of least privilege, leveraging database security features and external security managers like RACF.