Accessing the System i Database Using SQL

start > courses and registration > training feature sheet

code.

OL370

length.

2 days

type.

Instructor-Led

partner.

IBM

price.

$1,400

SQL is a very powerful programming and database language that is widely used in business today. For example, SQL can be used to perform interactive database queries as well as to extract data from a server and pass that data to a Java applet running on a client PC. SQL is frequently used in System i high-level languages as an alternate means to access the database.

This course is designed to teach System i programmers and highly skilled System i users how to access the DB2 UDB database using SQL. Students will learn how to create, maintain, and query the database using the interactive SQL interface and iSeries Navigator.

Phoenix, AZ	oct.6.2008 - oct.7.2008	register
El Segundo/Los Angeles, CA	oct.6.2008 - oct.7.2008	register
Santa Clara, CA	oct.6.2008 - oct.7.2008	register
Sacramento/Elk Grove, CA	oct.6.2008 - oct.7.2008	register
Englewood/Denver, CO	oct.6.2008 - oct.7.2008	register
Philadelphia, PA	sep.30.2008 - oct.1.2008	register
Houston, TX	dec.2.2008 - dec.3.2008	register
Bellevue/Seattle, WA	oct.6.2008 - oct.7.2008	register

who can benefit

This is an intermediate course for experienced System I programmers. However, very skilled System i users who want to learn how to use the SQL programming language as a means to access the DB2 UDB for iSeries database may also consider attending this class.

This course is not recommended for users who need to perform simple queries. IBM Query for iSeries is an end user oriented tool designed for this purpose. You can learn about IBM Query for iSeries by attending such courses as AS020 (AS02)- IBM Query for iSeries Workshop

prerequisites

For those students who need to learn about the i5/OS DB2 UDB features and functions, attendance at OL45/S6145, DB2 UDB for System i Fundamentals, prior to this class, is recommended. System i users who want to attend this class may not have a broad programming background. However, some prior experience in programming would facilitate their learning experience

Before attending this class, you should be able to:

	Use a Windows-based PC
	Navigate and use a Windows-based desktop
	Navigate System i menus
	Use System i commands
	List the data types that are supported by the i5/OS DB2 UDB
	Explain the use of the data types supported by the i5/OS DB2 UDB
	These skills can be gained by attending OL49/S6149 iSeries Application Programming Facilities Workshop.
	Other courses that may enhance your knowledge are:
	System i Application Programming Facilities Workshop (OL49) (S6149)
	DB2 UDB for System I Fundamentals (OL45) (S6145)

skills gained

After attending this class, you should be able to:

	Apply data definition language (DDL) to describe tables and views
	Use UPDATE, INSERT, ALTER, and DELETE to modify SQL tables and maintain a database
	Make use of the SELECT statement to extract data from tables and views
	Code SQL queries that include column and scalar functions
	Code SQL queries that include inner and outer joins
	Utilize SQL Query Manager
	Describe several features of QMF for Windows

note

Assuming a System i programming background, a programmer would attend the database classes in this order:

	DB2 UDB for System i Fundamentals (OL45) (S6145)
	Accessing the System i Database Using SQL (OL37) (S6137)
	Developing System i Applications Using SQL (OL38) (S6138)
	DB2 UDB for System i Database Advanced Programming (OL39) (S6139)

ibm education advantage program eligibility:

Yes - IBM Education Pack - online account

related courses, exams and materials

	before related training and products

	S6145 DB2 UDB for iSeries Fundamentals
	S6149 iSeries Application Programming Facilities Workshop

	after related training and products

	S6137 Accessing the iSeries Database Using SQL
	S6138 Developing iSeries Applications Using SQL

course content details

	Course units:

	SQL Concepts
	Extracting Data Using SELECT
	Functions
	JOIN and UNION
	SubSELECTs
	Query Tools
	Database Maintenance

	Day 1

	(00:15) Welcome and Administration
	(01:15) Unit 1. SQL Concepts
	(00:30) Introducing SQL
	(00:15) Checkpoint Exercise
	(00:30) Workstation Exercise - Getting to Know SQL
	(03:30) Unit 2. Extracting Data Using SELECT
	(00:15) The SELECT Statement
	(00:30) Workstation Exercise - How SELECT Works
	(00:45) The SELECT Statement (Continued)
	(00:30) Using iSeries Navigator (aka Operations Navigator)
	(00:15) Using 5250 STRSQL
	(00:15) Checkpoint
	(01:00) Workstation Exercise - Using the SELECT Statement
	(01:50) Unit 3. Functions
	(00:15) Column Functions
	(00:30) Scalar Functions
	(00:15) CASE and CAST
	(00:05) Checkpoint
	(00:45) Workstation Exercise - Using SQL Functions

	Day 2

	(01:30) Unit 4. JOIN and UNION
	(00:30) Using JOIN
	(00:30) UNION
	(00:30) Workstation Exercise - JOIN and UNION
	(01:05) Unit 5. SubSELECTs
	(00:30) Using SubSELECTs / Subqueries
	(00:05) Checkpoint
	(00:30) Workstation Exercise - Coding SubSELECTs
	(02:30) Unit 6. Query Tools
	(01:00) Using Query Manager
	(01:00) Workstation Exercise - Using SQL Query Manager
	(00:30) QMF for Windows for iSeries
	(04:15) Unit 7. Database Maintenance
	(00:15) SQL Data Types
	(00:30) SQL Data Definition and Manipulation
	(00:30) Workstation Exercise - Enhancing an SQL Table
	(00:30) Data Integrity
	(00:15) SQL SYS Objects
	(00:30) Creating SQL Objects on the iSeries
	(00:15) Checkpoint
	(01:30) Workstation Exercise - Maintaining Database Objects