OD47: IBM i DB2 and SQL School
DURATION: 5 days
SCHEDULED DATES: TBD – Contact Us for more Information
PURPOSE
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 i system high-level languages as an alternate means to access the database.
This course is designed to teach i programmers and highly skilled i system users how to access the database using SQL. Students will learn how to create, maintain, and query the database using the interactive SQL interface and System i Navigator.
AUDIENCE
This course is designed primarily for experienced IBM i programmers. However, very skilled i system users who want to learn how to use the SQL programming language as a means to access the DB2 for i database might also consider attending this class.
This course is not recommended for users who need to perform simple queries. Query for IBM i is an end user oriented tool designed for this purpose.
This course is not recommended for users who need to perform WebQuery queries. WebQuery for IBM i is an end user, programmer and administrator oriented tool designed for this purpose. You can learn about WebQuery for IBM i by attending such courses as OV550 – DB2 WebQuery for Advanced Users, Developers and Administrators – ILO or OD050, the classroom equivalent.
PREREQUISITES
Before attending this class, the student should be able to:
• Use a Windows-based PC
• Navigate and use a Windows-based desktop
• Use basic IBM i navigation tools, including:
– CL commands
– Menus
– Online Help
– WRKSPLF and related commands to manage output
– WRKJOB, DSPMSG, DSPJOB commands and so forth to perform basic problem determination
• Use the Program Development Manager/Source Entry Utility or the RSE LPEX Editor to create and maintain source files
• List and explain the use of data types supported by the DB2 for i database
These skills can be gained by attending OL49/OL490 IBM i Application Programming Facilities Workshop.
OBJECTIVES
After attending this class, the student should be able to:
• List IBM i database objects
• Use both SQL and native i terminology to describe objects
• State two ways that SQL can be used in a program
• Use interactive SQL and System i Navigator to create DB2 objects
• Explain how SQL uses journaling and commitment control
• Use UPDATE, INSERT, ALTER, and DELETE to maintain an SQL database
• Use Data Definition Language (DDL) to describe tables and views
• Use UPDATE, INSERT, ALTER, and DELETE to modify SQL tables and maintain a database
• Use 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
• Use SQL Query Manager for i tool
• Describe how SQL stored procedures are created
• Describe and use the System i Navigator database tools
CONTENT
Course units:
1. Introduction to relational database management and DB2 for i
2. Introducing Structured Query Language
3. Extracting data using SELECT
4. Functions
5. JOIN and UNION
6. SubSELECTs
7. Query tools
8. Database maintenance
9. Using embedding SQL
10. An overview of stored procedures
11. Database tools with System i Navigator
