fr
en
IBM i: SQL Language
Your IBM i applications depend on consistent data management. With DB2 and SQL, connect data structures, querying and operations to gain greater control over processing. Strengthen your ability to produce verifiable results and discuss technical choices.
- Duration
- 5 days 35 hours
- Code
- OL38FR Code
Presentation
SQL is essential in the IBM i (AS/400) environment, not only for querying and manipulating data but also for administering the system itself. Many administration, monitoring and diagnostic tasks can be performed directly in SQL using IBM's system views and functions.
This operational course develops solid SQL skills on IBM i, from simple queries to advanced features such as complex joins, views, stored procedures and JSON data integration. With no prerequisites, it is intended for developers, analysts and administrators who want to make full use of DB2 on IBM i.
Objectives
By the end of the course, participants will be able to:
- Understand the fundamentals of a relational database on IBM i (DB2).
- Write effective SQL queries, from simple to complex.
- Manipulate and maintain data through SQL.
- Create and manage database objects, including tables, views, indexes and procedures.
- Use advanced DB2 capabilities, including CLOB, BLOB, JSON and web services.
- Optimise query performance.
Program
1. DB2 architecture and fundamentals on IBM i
- Overview of DB2/400 and its operating system integration.
- Database objects: physical files, logical files and SQL tables.
- Data types and schema concepts.
🧪 Lab: explore DB2 through the 5250 emulator and SQL scripts.
2. Writing SQL queries with SELECT
- SELECT syntax, WHERE filters and ORDER BY sorting.
- String, date and conversion functions.
🧪 Lab: create simple queries with filters and expressions.
3. Joins and complex combinations
- INNER, LEFT, RIGHT and FULL JOIN.
- Multiple joins and self-joins.
🧪 Lab: write multi-table queries with conditional joins.
4. Aggregation and grouping
- Aggregate functions: SUM, AVG, COUNT, MIN and MAX.
- GROUP BY and HAVING.
🧪 Lab: create dashboards and data summaries.
5. Subqueries and table expressions
- Scalar and correlated subqueries, and subqueries in FROM.
- Common Table Expressions (CTEs) using WITH.
🧪 Lab: write nested queries with CTEs.
6. Data manipulation and maintenance
- INSERT, UPDATE and DELETE.
- Using MERGE.
- Transactions, COMMIT and ROLLBACK.
🧪 Lab: update records and perform conditional processing.
7. Creating SQL objects
- Tables, views, indexes and aliases.
- Primary key, foreign key and uniqueness constraints.
🧪 Lab: design and create a small relational model.
8. Stored procedures and triggers
- Define and execute procedures with CREATE PROCEDURE.
- Triggers: BEFORE, AFTER and FOR EACH ROW/STATEMENT.
🧪 Lab: develop an automated business process.
9. User-defined and table functions
- Custom scalar functions.
- Table-returning functions.
🧪 Lab: write a reusable business function.
10. Import, export and integration
- Export data to Excel or by email.
- Format and select exported data.
🧪 Lab: generate an XLSX file from an SQL table.
11. Security and integrity
- Access control and user authorities.
- Referential integrity constraints.
🧪 Lab: configure restricted access and test violations.
12. Performance and optimisation
- Performance indicators, indexes and access plans.
- Optimisation techniques.
🧪 Lab: analyse and improve slow queries.
13. Advanced types and modern integration
- CLOB and BLOB for storing large files.
- XML and JSON in SQL.
- Consuming web services with SQL.
🧪 Lab: extract JSON data and simulate requests to a web service.
Audience
Developers, analyst-programmers and DB2 administrators on IBM i.
Prerequisites
No prerequisites are required.
Teaching and assessment methods
- Initial skills assessment
- Training materials provided to participants
- Continuous assessment throughout the course
- End-of-course feedback questionnaire
- Combination of theory and practical application
- Attendance records
- Post-course follow-up evaluation
- Quiz / multiple-choice questions
- Practical exercises
Course highlights
- Practical, production-focused training.
- Expert IBM i trainer.
- Labs on cloud servers running V7R5 or V7R6.
Dates and sessions
Choose the date and delivery format that suit you.
No upcoming sessions are currently available.