Ec-1096-Db2 Udb-db2 Udb SQL Coding

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

    [email protected]

    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

    [email protected]

    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

    [email protected]

    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