Ec-1096-db2 Udb-db2 Udb Sql Coding

DB2 UDB® SQL Coding Course No.

1096

Description

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.

Audience

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.

Prerequisites

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.

Objectives

• 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 efficient 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

Major Topics

• DB2 UDB® Overview • SQL Overview • SQL SELECT statements • SQL WHERE Clauses • SQL Updating • SQL: Using Multiple Tables

©2007-08 | Keane, Inc.

• SQL Group By/Having



Format

Lecture, discussion, and hands-on workshops.

Duration

3 days

Australia | Canada | China | France | India | New Zealand | Singapore | Switzerland | UAE | UK | USA [email protected]

www.keane.com/education/

Course Contents • Scalar Functions

1. DB2 UDB® Overview • What DB2 UDB Is

• Replacing Null Values

• How DB2 UDB Interfaces with Applications

• VALUE/COALESCE

• Components of DB2 UDBTM Database

• For Character Fields Only

• DB2 UDB® “World” Example

• SUBSTR

• String Data Types

• LENGTH

• Binary Data Types

• STRIP

• Numeric Data Types

• String-Handling Functions

• Date/Time Data Types

• LCASE, UCASE

• Attribute Qualifiers

• LOCATE

• Check Constraints

• LTRIM, RTRIM and CONCAT

• DB2 UDB “World” Example

• REPEAT

• Relationships

• REPLACE

• A View

• For Numeric Fields Only

• Views

• ROUND, CEILING and FLOOR

• ‘Special’ Data Types

• TRUNCATE

• Table/View names

• SIGN

• Synonyms and Aliases

• The Need for Type Conversion Functions

• A DB2 UDB Sybsystem

• Date-Handling Functions

®

®

®

®

◦◦DAYOFWEEK

2. SQL Overview

◦◦DAYOFYEAR

• SQL

◦◦WEEK

• SQL Syntax Rules

◦◦QUARTER

• DDL, DCL, DML

• Changing the Date Display Format

• Static SQL

• Finding Date Differences

• Dynamic SQL

• Incrementing Dates

• Mainframe Dynamic SQL

• Extracting Parts of Dates

• Workstation Access – The Control Center

• Returning Nulls for an Expression

• Exercise: Setting DB2 Defaults

• CASE Expressions

• Workstation Access – The Control Center

• Times and Timestamps

• Exercise: Starting the Command Center • Exercise: Execute DML from the Command Center

• Scalar Function Summary

4. SQL WHERE Clause

3. SQL SELECT Statements

• WHERE Clause

• Terminology

• Compound Predicates with AND and OR

• SELECT Clauses

• Three Value Logic

• SQL SELECT Clause

• Null Values

• Selecting All Rows and Columns

• Ranges with BETWEEN

• Selecting Columns Names

• Lists, with IN

• Eliminating Duplicates with DISTINCT

• Using an Expression in the In Clause

• Arithmetic Expressions

• String Matching, with LIKE

• Columns Aliases, Using “AS”

• Using the Escape Clause

• Literals • Concatenating Strings

Australia | Canada | China | France | India | New Zealand | Singapore | Switzerland | UAE | UK | USA [email protected]

www.keane.com/education/

5. SQL: Using Multiple Tables

8. DB2 UDB® Triggers, User Defined Function & User defined Types

• Various Ways of Combining Tables • A Test Case

• What’s a Trigger?

• Why Joins?

• Why Do We need Triggers?

• Joins 101

• Some Common Uses of Triggers

• Inner Joins

• Trigger – Example 1

• Local Predicates

• Trigger – Example 2

• Joining Three of More Tables

• Trigger Definition

• Joining a Table to Itself (Self-Joins)

• Activation Time & Granularity

• Outer Joins

• Trigger Definition: Mode

• Full Outer Joins

• Triggers: Transition Variables and Tables

• Using Subqueries to Find the Intersection of Sets

• Referencing Transition Variables

• Using EXISTS

• Trigger Definition: Condition and Action

• Difference

• Calling Procedures from Triggers

• Other Correlated Subqueries

• Calling User Defined Functions from Triggers Unconditionally

• UNION

• Calling User Defined Functions from Triggers Conditionally

• UNION ALL

• Signaling Error Conditions From Triggers

• Nested Table Expressions • Solving Difference Using Nested Table Expressions

6. SQL Summarizing

• Example – Using the Case Statement • Dropping a Trigger

• Column Function Summary

• Order of Multiple Triggers

• MAX and MIN

• Trigger Cascading

• AVG and SUM

• Trigger and Referential Constraints

• Counting Row with COUNT (*)

• User Defined Functions

• Cardinality with COUNT(DISTINCT…)

• Types of UDFs

• Variance

• Creating UDFs

• Standard Deviation

• Example of UDF

• How Nulls Behave

• UDFs Shipped with DB2

• Grouping Rows with GROUP BY

• DAYNAME

• Filtering Groups with HAVING

• MONTHNAME

• How Grouping is Processed

• UDT

7. SQL Updating

Appendices

• Statements Which Update Tables

A. DB2 UDB® “World” Database

• INSERT: A Single Row

◦◦Tables

• Using and Expression in the VALUES Clause

◦◦DB2 UDB® “World” Database

• INSERT: From Another Table

◦◦Continent

• Inserting Into Views

◦◦Feature

• UPDATE

◦◦Feature Tables

• Using Multiple Values in the SET Statement

◦◦State

• DELETE

◦◦State Tables

• Referential Integrity

B. Limits in DB2TM for OS/390 and z/OS

• Constraints



• Example – History Table

Australia | Canada | China | France | India | New Zealand | Singapore | Switzerland | UAE | UK | USA [email protected]

www.keane.com/education/