Skip to main content

DML and DQL Introduction​

In IBM Db2 for z/OS, SQL (Structured Query Language) is used to interact with data stored in tables. SQL is divided into different categories based on what kind of operation you're performing. Two of the most important categories are:

  • DML (Data Manipulation Language): Used to read and change data inside tables.
  • DQL (Data Query Language): A subset of DML focused only on reading (querying) data.

Think of it like this:

  • DML = Read + Write
  • DQL = Read only

Real-World Usage​

In a real mainframe environment, you use DML to:

  • Insert new rows into a table (e.g., new customers)
  • Update values (e.g., change an address)
  • Delete rows (e.g., remove inactive accounts)
  • Select data to display on a report

You use DQL when you just want to fetch data for viewing, analyzing, or reporting—without changing anything in the table.

SQL Syntax Examples​

tip
  • SELECT (DQL) statements can run without COMMIT.
  • INSERT/UPDATE/DELETE (DML) usually require a COMMIT to make changes permanent, or ROLLBACK to cancel them.
  • On z/OS, DML statements might be controlled by application programs using host variables in COBOL or PL/I.
  • Be cautious when using DELETE or UPDATE without a WHERE clause — it could affect all rows in the table.
  • Locks may be taken when using DML — affecting performance or causing contention in multi-user environments.

DQL Example (SELECT)​

SELECT EMP_ID, NAME, SALARY FROM EMP;
  • Reads employee names from department D01.
  • No changes made to the data — this is DQL.

INSERT - DML​

INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY)  
VALUES (880, 'John Smith', '93242924', 55000);
INSERT INTO EMP (EMP_ID, NAME, PHONE, SALARY)  
VALUES (NEXT VALUE FOR EMP_SEQ, 'Test Smith', '93782924', 51000);

Update - DML​

UPDATE EMP SET Salary = 60000 WHERE EMP_ID = 880;

Delete - DML​

DELETE FROM EMP WHERE EMP_ID = 880;

Aggregrate functions​

Aggregate functions summarize multiple rows of data into a single value. They're perfect for reports and dashboards, helping answer questions like:

  • How many employees are there?
  • What's the average salary?
  • How many mentors have a specific skill?

IBM Docs - Aggregrate Functions

Real-World Usage for Aggregrate functions​

Think of your EMP and MENTOR tables:

  • Use aggregate functions to calculate overall salary stats.
  • Group by department or skill to get summaries per group.
  • Count mentors per skill, or find departments with the highest salaries.

Common Db2 Aggregate Functions​

FunctionPurpose
COUNT()Count rows or values
SUM()Add numeric values
AVG()Calculate average value
MAX()Find highest value
MIN()Find lowest value
tip
  • COUNT(*) includes rows even with NULLs.
  • COUNT(column) ignores NULLs in that column.
  • Always use GROUP BY when selecting non-aggregate columns.
  • Use HAVING to filter after aggregation (e.g., skills with more than 2 mentors).
SELECT 
  SKILLS,
  COUNT(*) AS MENTOR_COUNT
FROM MENTOR
GROUP BY SKILLS
HAVING COUNT(*) > 2;

SQL Syntax Example 1: Employee Salary Summary​

SELECT 
  COUNT(*) AS TOTAL_EMPLOYEES,
  SUM(SALARY) AS TOTAL_SALARY,
  AVG(SALARY) AS AVG_SALARY,
  MAX(SALARY) AS HIGHEST_SALARY,
  MIN(SALARY) AS LOWEST_SALARY
FROM EMP;

📌 Purpose: This gives a snapshot of all employee salaries.

SQL Syntax Example 2: Salary by Department​

SELECT 
  DEPARTMENT,
  COUNT(*) AS EMP_COUNT,
  AVG(SALARY) AS AVG_SALARY
FROM EMP
GROUP BY DEPARTMENT;

📌 Purpose: How many employees are in each department, and their average salaries.

SQL Syntax Example 3: Mentors by Skill​

SELECT 
  SKILLS,
  COUNT(*) AS TOTAL_MENTORS
FROM MENTOR
GROUP BY SKILLS;

📌 Purpose: See how many mentors have each skill.