Upload
sadineni
View
227
Download
0
Embed Size (px)
Citation preview
8/14/2019 Ec-1096-Db2 Udb-db2 Udb SQL Coding
1/3
Australia | Canada | China | France | India | New Zealand | Singapore | Switzerland | UAE | UK | USA
www.keane.com/education
DB2 UDB SQL Coding
|
1096
In this hands-on course the student will execute over 60 SQL statements to read and update DB2 UDB tables. In
addition, the student will become familiar with factors, which affect DB2 UDB performance.
This course is suggested for Application Developers, Analysts and Technical Support Personnel who will be working
directly with DB2 UDB for Z/OS or OS/390.
Experience using Windows (if the DB2 UDB Command Center is to be utilized) or experience using TSO/ISPF on a
mainframe server. A high-level knowledge of relational databases is helpful also.
Utilize either the mainframe or GUI to execute queries
Read and update tables using all the features of the SQL SELECT, INSERT, UPDATE, and DELETE statements
Code Scalar functions
Create ordered results using ORDER BY clause
Code efcient search predicates in the WHERE clause
Code inner joins, including same-table joins
Code outer joins
Code subqueries and unions
Code nested table expressions
Utilize column functions along with the GROUP BY and HAVING
DB2 UDB Overview
SQL Overview
SQL SELECT statements
SQL WHERE Clauses
SQL Updating
SQL: Using Multiple Tables
SQL Group By/Having
Lecture, discussion, and hands-on workshops.
3 days
Description
Course No.
Audience
Prerequisites
Objectives
Major Topics
Duration
Format
8/14/2019 Ec-1096-Db2 Udb-db2 Udb SQL Coding
2/3
Australia | Canada | China | France | India | New Zealand | Singapore | Switzerland | UAE | UK | USA
www.keane.com/education
Scalar Functions
Replacing Null Values
VALUE/COALESCE
For Character Fields Only
SUBSTR
LENGTH
STRIP
String-Handling Functions
LCASE, UCASE
LOCATE
LTRIM, RTRIM and CONCAT
REPEAT
REPLACE
For Numeric Fields Only
ROUND, CEILING and FLOOR
TRUNCATE
SIGN
The Need for Type Conversion Functions
Date-Handling Functions
DAYOFWEEK
DAYOFYEAR
WEEK
QUARTER
Changing the Date Display Format
Finding Date Differences
Incrementing Dates
Extracting Parts of Dates
Returning Nulls for an Expression
CASE Expressions
Times and Timestamps
Scalar Function Summary
4. SQL WHERE Clause
WHERE Clause
Compound Predicates with AND and OR
Three Value Logic
Null Values
Ranges with BETWEEN
Lists, with IN
Using an Expression in the In Clause
String Matching, with LIKE
Using the Escape Clause
Course Contents
1. DB2 UDB Overview
What DB2 UDB Is
How DB2 UDB Interfaces with Applications
Components of DB2 UDB TM Database
DB2 UDB World Example
String Data Types
Binary Data Types
Numeric Data Types
Date/Time Data Types
Attribute Qualiers
Check Constraints
DB2 UDB World Example
Relationships
A View
Views
Special Data Types
Table/View names
Synonyms and Aliases
A DB2 UDB Sybsystem
2. SQL Overview
SQL
SQL Syntax Rules
DDL, DCL, DML
Static SQL
Dynamic SQLMainframe Dynamic SQL
Workstation Access The Control Center
Exercise: Setting DB2 Defaults
Workstation Access The Control Center
Exercise: Starting the Command Center
Exercise: Execute DML from the Command Center
3. SQL SELECT Statements
Terminology
SELECT Clauses
SQL SELECT Clause
Selecting All Rows and Columns
Selecting Columns Names
Eliminating Duplicates with DISTINCT
Arithmetic Expressions
Columns Aliases, Using AS
Literals
Concatenating Strings
8/14/2019 Ec-1096-Db2 Udb-db2 Udb SQL Coding
3/3
Australia | Canada | China | France | India | New Zealand | Singapore | Switzerland | UAE | UK | USA
www.keane.com/education
5. SQL: Using Multiple Tables
Various Ways of Combining Tables
A Test Case
Why Joins?
Joins 101
Inner Joins
Local Predicates
Joining Three of More Tables
Joining a Table to Itself (Self-Joins)
Outer Joins
Full Outer Joins
Using Subqueries to Find the Intersection of Sets
Using EXISTS
Difference
Other Correlated Subqueries
UNION
UNION ALLNested Table Expressions
Solving Difference Using Nested Table Expressions
6. SQL Summarizing
Column Function Summary
MAX and MIN
AVG and SUM
Counting Row with COUNT (*)
Cardinality with COUNT(DISTINCT)
Variance
Standard Deviation
How Nulls Behave
Grouping Rows with GROUP BY
Filtering Groups with HAVING
How Grouping is Processed
7. SQL Updating
Statements Which Update Tables
INSERT: A Single Row
Using and Expression in the VALUES Clause
INSERT: From Another TableInserting Into Views
UPDATE
Using Multiple Values in the SET Statement
DELETE
Referential Integrity
Constraints
8. DB2 UDB Triggers, User Defned Function & User
defned Types
Whats a Trigger?
Why Do We need Triggers?
Some Common Uses of Triggers
Trigger Example 1
Trigger Example 2
Trigger Denition
Activation Time & Granularity
Trigger Denition: Mode
Triggers: Transition Variables and Tables
Referencing Transition Variables
Trigger Denition: Condition and Action
Calling Procedures from Triggers
Calling User Dened Functions from Triggers
Unconditionally
Calling User Dened Functions from Triggers Conditionally
Signaling Error Conditions From Triggers
Example History Table
Example Using the Case Statement
Dropping a Trigger
Order of Multiple Triggers
Trigger Cascading
Trigger and Referential Constraints
User Dened Functions
Types of UDFs
Creating UDFs
Example of UDF
UDFs Shipped with DB2
DAYNAME
MONTHNAME
UDT
Appendices
A. DB2 UDB World Database
Tables
DB2 UDB World Database
Continent
Feature
Feature Tables
State
State Tables
B. Limits in DB2TM for OS/390 and z/OS