Experienced Data Processing personnel who need use SQL to access DB2 data.

Prerequisites

At least six months of TSO/ISPF is recommended. No previous database experience is needed.

Course duration

2 days

Course outline

DAY 1
	Introduction to DB2 Course Introduction DB2 - Concepts, and Terminology Structured Query Language (SQL) SQL 1 - The SELECT Statement Database 2 Interactive SQL 2 - SQL Features SQL 3 - ORDER BY, GROUP BY, HAVING
DAY 2
	Structured Query Language (SQL)(Continued) SQL 4 - Join, Sub-select, UNION SQL 5 - Data Definition Language SQL 6 - INSERT, UPDATE, DELETE SQL 7 - Data Control Language
Appendices
	A - Sample Tables B - Bibliography F - SQLCODES / SQLSTATES I - "Common" SQL Errors
TABLE OF CONTENTS
	DB2 - Concepts and Terminology 1.1 DataBase Management Systems (DBMS) 1.2 Relational DataBase Management System (RDBMS) DB2 History 1.3 Overview of DB2 Terms 1.4 DB2 and MVS SQL - Three major areas of SQL - DML, DCL, DDL 1.5 SQL Examples 1.6 Application Programming - Overview 1.7 DB2 Catalog Other Products - Related to DB2 1.8 (Logical) Views of a table 1.9 DB2 "Objects" ("components") DB2 Naming Rules SQL 1 - The Select Statement 2.1 SELECT - Overview 2.2 SELECT - Retrieve all columns - * SELECT - Retrieve specific columns 2.3 SELECT - AS - "Changing" a column's name 2.4 SELECT - Retrieve unique values - DISTINCT 2.5 WHERE clause - "Search Conditions" The BASIC Predicates 2.6 WHERE clause - the BOOLEAN AND, OR 2.7 WHERE clause - the IN Predicate WHERE clause - the BETWEEN Predicate 2.8 WHERE clause - the IS NULL Predicate WHERE clause - The LIKE Predicate 2.9 WHERE clause - the NOT parameter 2.10 "What really is happening" 2.1 The sub-SELECT feature 2.12 A Composite Example Other SELECT Examples Examples of major SQLCODES 2.13 Exercises - SELECT - SQL1 DataBase 2 Interactive (DB2I) 3.1 Primary Option Menu 3.2 DB2I HELP Facility SPUFI - Main Pane 3.3 SPUFI - (ISPF) Edit Panel (ISPF) Browse Panel -- WHERE clause - "Search Conditions"(2.5) 3.4 DB2 Utilities 3.5 Load utility - Example 3.6 DB2 Commands SQL 2 - Special Features 4.1 Special Registers USER, CURRENT DATE, CURRENT TIME, CURRENT TIMESTAMP 4.2 Concatenation Operator - or CONCAT Column functions for the SELECT statement AVG, MIN, MAX, SUM, COUNT(*) 4.3 Data/Time Functions CHAR, DATE 4.4 - DAY, MONTH, YEAR, HOUR, MINUTE, SECOND 4.5 Data/Time Arithmetic 4.6 Scalar Functions - DECIMAL, SUBSTR, 4.7 DAYS, FLOAT, HEX, DIGITS 4.8 INTEGER, LENGTH, MICROSECOND,TIME,TIMESTAMP 4.9 VALUE, VARGRAPHIC 4.10 Behavior of "nulls" 4.11 STRIP, NULLIF, Simple CASE 4.12 Exercises - SELECT - Special Features - SQL2 SQL 3 - ORDER BY, GROUP BY, HAVING 5.1 ORDER BY - Overview ORDER BY - "SORT" - ASCending, DESCending 5.2 ORDER BY - more than 1 column COLUMN NUMBER formerly "unnamed" column using AS 5.3 GROUP BY - Overview GROUP BY - ONE Column, TWO Columns 5.4 GROUP BY - Errors and Strange Results 5.5 GROUP BY for an "un-named" column 5.6 HAVING - Overview HAVING - Groups with ONE condition 5.7 "What really is happening" (revisited) 5.8 Exercises - Group by, having, Order by - SQL3 SQL 4 - Join, Sub-select, UNION 6.1 Join - Overview 6.2 The Join operation - putting tables (back) together 6.3 The Join operation - a PICTORIAL Look 6.4 INNER JOIN THE OLD WAY The N-E-W INNER JOIN 6.5 FULL OUTER JOIN 6.6 LEFT OUTER JOIN 6.7 RIGHT OUTER JOIN 6.8 "Nested Table" / SubSelect as operand of a JOIN 6.9 The sub-SELECT 6.10 The Sub-SELECT - Basic, IN 6.11 The OLD Style "outer" JOIN 6.12 The Sub-SELECT - ANY, ALL, EXISTS 6.13 Correlated sub-SELECT 6.14 The UNION statement 6.16 UNION ALL 6.17 The Case Construct ("Searched" CASE) 6.18 Join vs UNION Exercises - SQL4 SQL 5 - Data Definition Language (DDL) 7.1 Define (CREATE) and Destroy (DROP) DB2 Objects General format of the CREATE statement 7.2 Create Tablespace 7.3 Create Table - Example Create a Table LIKE another Table Create Table - Column Data-Types 7.4 Table Constraints 7.5 Create Index 7.6 The ALTER statement - change an Object 7.7 DB2 Object Dependencies 7.8 Create View 7.9 View Restrictions View Operation 7.10 Create "Alternate names" The DROP Statement SQL 6 - INSERT, UPDATE, DELETE 8.1 The SQL "change" statements - Overview DELETE 8.2 INSERT Specify all column values Specify some column values Rows from another table 8.3 UPDATE 8.4 Controlling the effects of SQL "changes" 8.5 Referential Integrity (RI) 8.6 Application Referential Integrity - Delete 8.7 Correlated sub-SELECT - REVISITED A Testing Method using SPUFI 8.8 Exercise - Create Tables and Indexes, INSERT, UPDATE, DELETE - SQL6 SQL 7 - Data Control Language (DCL) 9.1 DB2 Security - Overview 9.2 Privileges combine into "collections" 9.3 Checking Authorizations 9.4 Administrative Authorities - Named Groups 9.5 General format of the GRANT statement Examples of Granting Privileges 9.6 General format of the REVOKE statement Examples of Revoking Privileges 9.7 Cascading effect of the REVOKE statement Implicit Privileges