DB2 and SQL Programming Workshop

M/UX
Atlanta, Georgia, United States

Course Format: Classroom | E-learning | Virtual Class | Online | On-site | Blended | Self-paced

Course Description:

'' This is a basic course for programmers, developers, database administrators, system administrators, analysts, or managers, who need to understand the process involved in developing and maintaining application programs that manipulate DB2 UDB for z/OS Version 8 databases.


Skills Taught

--------------------------------------------------------------------------------

Write basic Data Manipulation Language (DML) SQL statements: SELECT, INSERT, UPDATE, and DELETE
Write basic Data Definition Language (DDL) SQL statements: CREATE TABLE and CREATE VIEW
Incorporate static SQL statements in an application program
Prepare the program for execution
Validate execution results are correct
Produce code to support multiple rows being returned from the database manager using cursors
Identify considerations regarding units of work, concurrency, and restart of programs
Identify differences between static SQL and dynamic SQL
Provide test data for applications
Discuss program and DB2 options relative to performance of static SQL


Course outline

--------------------------------------------------------------------------------

Topics from DB2 SQL Workshop (CF120)

SQL introduction

define the terms: database, table, row, column, and view
State the most common DB2 UDB data types
list the most commonly used SQL statements in the Data Definition Language (DDL)
list the most commonly used SQL statements in the Data Manipulation Language (DML)
list the most commonly used SQL statements in the Data Control Language (DCL)
state three null characteristics, and their meaning
Simple SQL queries

describe four SELECT statement clauses (SELECT, FROM, WHERE, and ORDER BY) and their purposes
retrieve all rows of a table
retrieve specific columns
retrieve rows based on comparisons to numeric or alphanumeric column values
use the keywords BETWEEN, IN, LIKE, and DISTINCT
order the resulting rows in a desired sequence
Retrieving data from multiple tables

retrieve data from more than one table or view
specify JOIN predicates
use correlation names in queries
Scalar functions and arithmetic

use arithmetic in the SELECT and WHERE clauses
specify basic scalar functions, such as COALESCE/VALUE, DECIMAL, and SUBSTR
use date and time scalar functions
use the CONCAT operator
Column functions and grouping

describe the difference between scalar and column functions
list the most common DB2 column functions
group rows into sets based on one or more columns
Union

produce a single result table containing data from more than one query
state the UNION rules
state the difference between UNION and UNION ALL
Using subqueries

use subqueries in the WHERE and HAVING clauses
code subqueries using basic predicates
code subqueries using the IN keyword
Maintaining data

create a simple table for testing DML statements
create a simple view
insert rows into a table
update data in a table
delete rows from a table
drop a table
Topics from DB2 UDB Application Programming Workshop (CF820)

DB2 concepts

identify DB2 family
explain DB2 workstation component functions
identify DB2 objects
identify the key differences between static SQL and other application alternatives for accessing DB2 data
Program structure I

embed INSERT, UPDATE, DELETE, and single-row SELECT statements in application programs
effectively communicate with DB2 when processing NULL values and determining the success of statement execution
demonstrate use of DB2 coding aids
code CONNECT statements within an application program
identify connection types and impacts on a unit of work
Program preparation

identify the additional steps necessary to prepare a program that contains embedded SQL for execution
describe the functions of the DB2 PRECOMPILE and BIND processes
describe factors relevant to the BIND process, including RUNSTATS positioning, package status, parameters, and authorization requirements
Program structure II

use DECLARE, OPEN, FETCH, and CLOSE CURSOR statements to handle select criteria that may return multiple rows in application programs
issue positioned UPDATE and DELETE statements
identify how scrollable cursors can be used (z/OS)
Recovery and locking concepts

define a unit of recovery
identify the basic locking strategies used by DB2
Dynamic SQL introduction

describe the difference between static SQL and dynamic SQL
list the types of dynamic statements
code dynamic SQL in a program
Managing test data

identify methods to insert data into a table
use the LOAD or IMPORT utility
identify the purpose of the RUNSTATS utility
identify the purpose of the REORG utility
Performance considerations

use programming techniques that enhance DB2 application performance by following general guidelines, using indexable predicates, and avoiding unnecessary sorts
identify the access paths available to DB2
list common causes of deadlocks and avoid such causes when possible
use the EXPLAIN tools as aids to develop applications that emphasize performance
Daily Agenda

Day 1

(CF120) welcome
(CF120) introduction
(CF120) simple SQL queries
(CF120) exercises
(CF120) retrieving data from multiple tables
(CF120) exercises
(CF120) scalar functions and arithmetic
(CF120) exercises
Day 2

(CF120) column functions and grouping
(CF120) exercises
(CF120) union
(CF120) exercises
(CF120) using subqueries
(CF120) exercises
(CF120) maintaining data
(CF120) exercises
Day 3

(CF820) DB2 concepts
(CF820) lab - DDL
(CF820) program structure - part 1
(CF820) lab - program structure I
Day 4

(CF820) program preparation
(CF820) lab - program preparation
(CF820) program structure - part 2
(CF820) lab - program structure II
(CF820) Recovery and Locking Concepts
(CF820) lab - Cursor Restart
Day 5

(CF820) Dynamic SQL introduction
(CF820) lab - Dynamic SQL
(CF820) managing test data
(CF820) lab - loading data
(CF820) performance considerations
(CF820) lab - performance

...''

Phone:866-700-9811

School Address:

pSeries/AIX, CISCO, Linux, Microsoft, Professional Skills, Project Management, RedHat LINUX Enrollments 866-700-9811 (Toll Free) or 916-226-2537

CICS, iSeries/OS400, Enterprise/zSeries and WebSphere Enrollments 888-689-3326 (Toll Free) or 916-226-2511

DB2 UDB, Rational Software, and Tivoli Enrollments 866-700-9817 (Toll Free) or 916-226-2533

M/UX
9245 Laguna Springs Drive
Suite 315
Elk Grove, CA 95758 USA

Jobs & Resumes: Atlanta
Houses & Roommates: Atlanta

DB2 9 for LUW Multiple Partition DBA Workshop
DB2 UDB for z/OS and OS/390 Data Sharing Implementation
DB2 for z/OS and OS/390 Data Sharing Recovery and Restart
DB2 for Linux, UNIX, and Windows Performance Tuning and Monitoring Workshop
DB2 9 for LUW Advanced Database Recovery
DB2 Stored Procedures Programming Workshop
DB2 UDB Application Programming Workshop
DB2 UDB for z/OS System/Disaster Recovery Workshop
Query and Manage XML data with DB2 9
DB2 UDB for z/OS Stored Procedures using COBOL
iSeries RPG IV Version 5 Programming Fundamentals Workshop
iSeries RPG IV Version 5 Programming Advanced Workshop
iSeries Advanced System Operator Workshop Version 5
Backup and Recovery Media Services/400 Workshop
System i Performance Analysis Workshop
IBM WebSphere Application Server V6.0 Administration on System i
IBM System Storage Solutions for System i
System i System Administration and Control
AIX 5L Configuring TCP/IP

Notice: The course description on this page was captured from the Internet as historical reference or submitted by visitors. It was archived statically and not updated from day to day.
 

Tips: Want to find a place to stay close to the school? Or homestay in a different country? Try the house rental message board where you can search for roommates and post shared rooms or apartments for rent.