DB2 SQL for Experienced Users

start > courses and registration > training feature sheet

This course has been upgraded to:

»	CE130 DB2 SQL Workshop for Experienced Users

This course is no longer publicly scheduled and displayed for reference purposes only. To find more information about this course expiry or request this archived course, send us an email.

DB2 SQL for Experienced Users

course schedule			who can benefit
prerequisites			skills gained
other notes			course content
related solutions

code.

CF131

length.

2.5 days

type.

Instructor-Led

partner.

IBM

price.

$1,438

IACET credits 2.0

Learn how to code simple Data Definition Language (DDL) statements and complex SELECT statements. Learn to use outer joins, complex subqueries, recursive SQL, and table expressions. Also, learn about features, such as triggers, user-defined data types, and user-defined functions.

If you are interested in iSeries, AS/400, or AS/400e, review the following DB2 SQL course:

Accessing the iSeries Database Using SQL (S6137)

There are currently no scheduled dates for this course. If you are interested in this course, request a course date with the links below.

who can benefit

This is an intermediate course for experienced SQL end users, analysts, application programmers, administrators, and other technical individuals, who need more advanced knowledge to plan, implement, and maintain SQL for DB2 UDB for z/OS Version 8 or DB2 UDB for Linux, UNIX and Windows Version 8.

prerequisites

You should complete:

	DB2 Family Fundamentals (CF030)
	DB2 SQL Workshop (CF120)
	OR have equivalent experience

skills gained

	Use advanced SQL constructs, including recursive SQL, table expressions, check constraints, and triggers
	Discuss basic relational databases concepts, including databases, tables, indexes, and referential integrity
	Create tables, views, and indexes
	Use outer join
	Use CASE and CAST
	Use complex subqueries
	Use several of the most popular scalar SQL functions
	Use the new grouping functions CUBE, ROLLUP, and RANK
	Describe the impact of summary tables
	Describe User-defined Distinct Types (UDT) and User-Defined Functions (UDF)
	Avoid several of the most common causes of poorly performing SQL

course materials

The course materials cover DB2 UDB for z/OS Version 8 and DB2 UDB for Linux, UNIX and Windows Version 8.

hands-on labs

Eight labs are included to address using either DB2 UDB for z/OS or DB2 UDB for Linux, UNIX and Windows.

certification

Help to prepare for IBM Certification test 702: DB2 UDB V8.1 for z/OS Database Administration.

ibm education advantage program eligibility:

	Yes - IBM Education Card
	Yes - IBM Education Pack
	Yes - IBM Corporate Education Pass
	Yes - IBM Education Pack - online account

related courses, exams and materials

	before related training and products

	CE030 DB2 Family Fundamentals
	CE120 SQL Workshop
	U5732 DB2 Family Introduction Mini-Curriculum

course content details

	Introduction

	describe the key differences among the IBM DB2 platforms
	identify the purpose of the clauses in the SELECT statement
	use the new grouping functions ROLLUP, CUBE, and RANK

	Create objects

	code statements to create tables and views
	code statements to alter tables
	code statements to create indexes
	code statements to implement Referential Integrity (RI)
	code statements to define triggers and check constraints
	identify the impacts and advantages of RI, including impacts of delete rules
	identify considerations when using triggers and check constraints

	Joins

	retrieve data from more than one table via inner joins
	use outer joins

	CASE, CAST, and summary tables

	identify when CASE expressions can be used
	code CASE expressions in SELECT list and in the WHERE
	identify when CAST specifications can be used
	list the advantages of summary tables

	Subqueries

	code subqueries using ALL, ANY/SOME, and EXISTS
	code correlated subqueries
	choose the proper type of subquery to use in each case

	Scalar functions

	code SQL statements that use various scalar functions on columns of the following data types: SMALLINT, INTEGER, DECIMAL, FLOAT, CHAR, VARCHAR, DATE, TIME, and TIMESTAMP
	use the scalar functions effectively
	use SUBSTR()
	use COALESE()
	use VALUE()
	use DECIMAL()
	use DIGITS()
	use CHAR()
	use LENGTH()
	use LTRIM() and RTRIM()
	use DATE(), YEAR(), MONTH(), DAY(), and DAYS()
	use TIME(), HOUR(), MINUTE(), and SECOND()
	use TIMESTAMP() and MICROSECOND()
	use DAYOFYEAR(), DAYOFWEEK(), and WEEK()
	use JULIAN_DAY() and JULIAN_WEEK()
	perform calculations involving dates and date/time intervals

	Table expressions and recursive SQL

	identify reasons for using table expressions and recursive SQL
	use nested and common table expressions
	identify the difference between views and table expressions
	code recursive SQL
	control the depth of recursion when coding recursive SQL

	UDT/UDF and performance

	Describe the concepts behind UDT and UDF
	Predict when queries will use indexes to get better performance