DB2 tutorial is good for both DB2 and mainframe developers is it is quick to read and given in the form of questions and answers.
Read my part-2 post on DB2 tutorial.
Data Definition Language(DDL)
CREATE: –This statement is used to create objects
Syntax : For Creating a Table
CREATE TABLE <tabname> (Col Definitions)
PRIMARY KEY(Columns) / FOREIGN KEY
UNIQUE (Colname) (referential constraint)
[LIKE Table name / View name]
[IN Database Tablespace Name ]
- Foreign Key references dbname.table on ‘relation condition for delete’
- Table1 references table2(target) – Table2’s Primary key is the foreign key defined in Table1
- The Conditions that can be used are CASCADE, RESTRICT & SET NULL (referential constraint for the foreign key definition)
- Inserting (or updating ) rows in the target is allowed only if there are no rows in the referencing table
ALTER:- This statement is used for altering all DB2 objects
Syntax : For altering a Table
ALTER TABLE <Tablename>
ADD Column Data-type [ not null with default]
- Alter allows primary & Foreign key specifications to be changed
- It does not support changes to width or data type of a column or dropping a column
DROP :- This statement is used for dropping all DB2 objects
Syntax : For dropping a table
DROP TABLE <Tablename>
Some general rules for RI & Table Parameters
- Avoid nulls in columns participating in Arithmetic logic or comparisons
- Primary key cols cannot be nulls
- Limit referential structures to no more than three levels in a direction
- Use DB2’s inherent features rather than program coded RI’s.
SQL – Selection & Projection
- Select retrieves a specific number of rows from a table
- Projection operation retrieves a specified subset of columns(but all rows) from the table
E.g.. : SELECT CUST_NO, CUST_NAME FROM CUSTOMER;
- The WHERE clause defines the Predicates for the SQL operation.
- The above WHERE clause can have multiple conditions using AND & OR .
Many other clauses can be used in conjunction with the WHERE clause to code the required predicate, some are :-
- Between / Not Between
- In / Not In
- Like / Not Like
- IS NULL / IS NOT NULL
SELECT using a range :
SELECT CUST_NO, CUST_NAME, CUST_ADDR FROM CUSTOMER WHERE CUST_NO BETWEEN 1000 AND 2000;
E.g. SELECT CUST_NO, CUST_NAME, CUST_ADDR FROM CUSTOMER
WHERE CUST_NO IN(1000, 1001,1002);
E.g. SELECT CUST_NO, CUST_NAME, CUST_ADDR
WHERE CUST_ID like/not like ‘425%’
Note :- ‘_’ for a single char ; ‘%’ for a string of chars
Escape ‘\’ – escape char; if precedes ‘_’ or ‘%’ overrides their meaning
NULL Clause: To check null the syntax is ‘IS NULL’
SELECT CUST_NO, CUST_NAME, ORDER_NO WHERE ORDER_NO IS NULL;
However if there are null values for ORDER_NO, then these are always evaluated as a ‘Not True’ condition in a Query.
Order by and Group by clauses :
- Order by sorts retrieved data in the specified order; uses the WHERE clause
- Group by operator causes the table represented by the FROM clause to be rearranged into groups, such that within one group all rows have the same value for the Group by column (not physically in the database). The Select clause is applied to the grouped data and not to the original table.
Here ‘HAVING’ is used to eliminate groups, just like WHERE is used for rows.
SELECT ORDER_NO, SUM(NO_PRODUCTS) FROM ORDER GROUP BY ORDER_NO HAVING AVG(NO_PRODUCTS) < 10 ORDER BY ORDER_NO ;