Information Management

CE131G : IBM DB2 SQL Workshop for Experienced Users

Course Duration

3 Days

Delivery

ILT VILT

Overview

Overview Course Curriculum Training Schedule Exam & Certification FAQs

The course teaches you how to make use of advanced SQL techniques to access DB2 databases in different environments. This course is appropriate for customers working in all DB2 environments, specifically for z/OS, Linux, UNIX, and Windows.

What You'll Learn

Discuss basic relational database concepts
Use some of the OLAP features of DB2, such as GROUPing and RANKing functions
Create tables, views and indexes
Use referential integrity, check constraints and triggers
Use outer joins, and join tables to themselves
Use CASE expressions, and the CAST function
Identify the impact of Summary Tables, Materialized Query Tables, and temporary tables
Use complex subqueries
Use a greater number of scalar SQL functions
Use advanced SQL constructs, such as recursive SQL and table expressions
Define User-Defined Distinct Types and User-Defined Functions
Avoid several of the most common causes for poorly-performing SQL

Who Should Attend

This intermediate course is for experienced SQL end users, application programmers, database administrators, and user support staff who need more advanced knowledge of SQL.

Prerequisites

You should have experience with:

Coding and executing basic SQL statements.

These skills can be developed by attending:

SQL Workshop (CE120), or equivalent experience.

Learning Journey

Coming Soon...

Introduction

Identify the purpose of the clauses in the SELECT statement
Describe the key differences among the IBM DB2 platforms
Describe and use some of the OLAP features of DB2, such as GROUPING functions like CUBE and ROLLUP, and the RANK, DENSE_RANK and ROW_NUMBER functions

Create Objects

Code statements to Create tables and views, Alter tables, Create indexes, Implement referential integrity (RI), and Define triggers and check constraints
Identify impacts and advantages of referential integrity, including impacts of delete rules
Identify considerations when using triggers and check constraints
Define and make use of INSTEAD OF triggers

Join

Retrieve data from more than one table via inner and outer joins
Use outer joins (LEFT, RIGHT, FULL)
Use ANTI JOINS
Join a table to itself
Use UNION and UNION ALL
Use EXCEPT and INTERCEPT

CASE, CAST, Summary Tables, and Materialized Query Tables

Identify when CASE expressions can be used
Code CASE expressions in SELECT list and in the WHERE clause
Identify when CAST specifications can be used
Identify the advantages of using Summary (Materialized Query) Tables and Temporary tables
Identify the advantages of using Materialized Query Tables (MQTs)
Identify when and how to use Temporary tables

Using Subqueries

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

Scalar Functions

Extend your knowledge of scalar functions which: Manipulate arithmetic data, Manipulate data values, and Manipulate character data
Examples of scalar functions that are addressed in this course:

SUBSTR
POSSTR
COALESCE/VALUE
DECIMAL
ROUND
DIGITS
CHAR
DATE/TIME

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

UDTs/UDFs and Performance

Describe the concepts behind User-Defined Types, User-Defined Function and Stored Procedures
Predict when queries will use indexes to get better performance
Identify concepts of predicate processing
State introductory concepts about index structure
State general best practices advice

This course is not associated with any Certification.

Frequently Asked Questions (FAQs)

Why get IBM certified?

IBM certifications validate your skills and expertise in a wide range of technologies, including hybrid cloud, data & AI, security, IT infrastructure, and business applications.
These certifications are globally recognized and can help you advance your career, increase your earning potential, and demonstrate your commitment to professional development.
IBM-certified professionals are in high demand, making them valuable assets to any organization seeking to leverage IBM's innovative solutions.
What to expect for the examination?

IBM offers a variety of certification exams at different levels (Basic, Intermediate, and Advanced) covering various technologies and job roles.
Exams typically consist of multiple-choice questions and may include scenario-based questions that assess your ability to apply your knowledge in real-world situations.
Note: Certification requirements and policies may be updated by IBM from time to time. We apologize for any discrepancies; do get in touch with us if you have any questions.
How long is IBM certification valid for?

The validity period of IBM certifications varies. Some certifications, particularly those focused on specific product versions or technologies, may have expiration dates.
However, many IBM certifications, especially those focused on broader skills or newer technologies, do not have an expiration date.
Note: Certification requirements and policies may be updated by IBM from time to time. We apologize for any discrepancies; do get in touch with us if you have any questions.
Why take this course with Trainocate?

Here’s what sets us apart:
- Global Reach, Localized Accessibility: Benefit from our geographically diverse training hubs in 16 countries (and counting!).
- Top-Rated Instructors: Our team of subject matter experts (with high average CSAT and MTM scores) are passionate to help you accelerate your digital transformation.
- Customized Training Solutions: Choose from on-site, virtual classrooms, or self-paced learning to fit your organization and individual needs.
- Experiential Learning: Dive into interactive training with our curated lesson plans. Participate in hands-on labs, solve real-world challenges, and take on comprehensive assessments.
- Learn From The Best: With 30+ authorized training partnerships and countless awards from Microsoft, AWS, Google – you're guaranteed learning from the industry's elite.
- Your Bridge To Success: We provide up-to-date course materials, helpful exam guides, and dedicated support to validate your expertise and elevate your career.

Keep Exploring

Course Curriculum

Training Schedule

Exam & Certification

FAQs

Frequently Asked Questions

Improve yourself and your career by taking this course.

Enroll Now

More Courses By IBM

Browse All Courses

System Z

Ready to Take Your Business from Great to Awesome?

Level-up by partnering with Trainocate. Get in touch today.

Name

Phone

I'm inquiring for

Myself
Company

Inquiry Details

By providing your contact details, you agree to our Privacy Policy.

CE131G : IBM DB2 SQL Workshop for Experienced Users

What You'll Learn

Who Should Attend

Prerequisites

Learning Journey

Frequently Asked Questions (FAQs)

Why get IBM certified?

What to expect for the examination?

How long is IBM certification valid for?

Why take this course with Trainocate?

Keep Exploring

Course Curriculum

Training Schedule

Exam & Certification

Frequently Asked Questions

Improve yourself and your career by taking this course.

More Courses By IBM

Advanced Parallel Sysplex Operations and Recovery Workshop

IBM Cognos Analytics: Author Reports Fundamentals v11.1.x

IBM z/OS Management Facility Implementation and Use 2.1

Ready to Take Your Business from Great to Awesome?

Request More Information