fr
en
Advanced IBM i administration with SQL: automate and secure your system
Your systems need protection without losing sight of operational requirements. With IBM i, review access and security mechanisms to structure your administration. Strengthen your ability to identify areas requiring attention and prepare appropriate measures.
- Duration
- 3 days 21 hours
- Code
- ASR001FR Code
Presentation
The IBM i (AS/400) environment is becoming more open and modern, notably through full integration of SQL as a system administration tool. Once reserved for database access, SQL syntax now enables interaction with system components (jobs, users, queues, locks, etc.), providing an effective alternative to many traditional WRK* and DSP* commands.
This 3-day advanced IBM i course is designed for experienced administrators seeking greater efficiency, automation of recurring tasks and full use of the system views provided by IBM. Based on real-world examples, it offers a modern approach to system administration, overcoming CLP limitations through the power of stored procedures, custom functions and UDTFs.
You will also learn to audit user profile security and permissions on objects and libraries directly in SQL. You will be able to produce automated reports (Excel, JSON, web service) for reliable, actionable monitoring of your IBM i environment.
Objectives
By taking this IBM i administration course, you will be able to:
- use SQL to query the IBM system catalogue (SQL views);
- replace traditional administration methods (WRK*/DSP* commands) with SQL queries that can be automated;
- design stored procedures, scalar functions and user-defined table functions (UDTFs) for administration;
- automate administration tasks using SQL rather than CLP;
- examine user profile security and manage permissions on objects and libraries using SQL;
- produce automated reports in Excel or JSON format, or through a web service, using SQL procedures.
Program
Module 1: introduction to system administration with SQL
- Introduction to IBM's SQL catalogue (QSYS2, SYSIBMADM, SYSTOOLS).
- Using ACS Run SQL Scripts to administer the system.
- The various system views, functions, table functions and procedures.
- Querying active jobs, spooled files, open files, CPU, memory, disk and system history.
Labs
- Reproduce the results of WRK* commands in SQL.
Module 2: SQL programming for administration
- Creating stored procedures for administration (e.g. automatic spooled file cleanup or monitoring scheduled job history).
- Creating scalar functions (e.g. converting numeric values into dates).
- Introduction to user-defined SQL table functions (UDTFs).
- Implementing lock tracking (QSYS2.LOCK_INFO) and system monitoring (QSYS2.SYSTEM_STATUS_INFO).
- Querying system journals and messages with SQL.
Labs
- Develop a small SQL monitoring tool, including email delivery.
Module 3: automate, secure and apply best practices
- Analysing user profiles and permissions on objects and libraries (USER_INFO, USER_PRIVILEGES).
- Creating automated reports in Excel or JSON format, or through a web service, using SQL procedures.
- Using the scheduler or integrating CLP scripts for automation.
- Introduction to best practices for auditing, logging and optimising SQL scripts.
Labs
- Automate administration tasks and generate security reports.
Audience
This course is designed for professionals experienced in IBM i administration, including:
- experienced IBM i administrators seeking to optimise and automate their administration tasks using SQL;
- system technicians who want to replace traditional commands with modern SQL scripts;
- IT operations managers seeking to automate and secure the administration of their IBM i system.
Prerequisites
This course requires the following prerequisites:
- Knowledge of the IBM i environment: familiarity with the IBM i environment is essential, including navigation and fundamental system concepts.
- Standard IBM i commands: proficiency in standard commands such as WRKACTJOB, DSPJOB and WRKSPLF is required to interact with the system.
- Basic SQL on IBM i: an understanding of SQL fundamentals, particularly reading tables and running simple queries on IBM i, is essential.
- Basic CLP knowledge: familiarity with CLP (Control Language Programs) is useful but not mandatory.
OR completion of the IBM i: Management and Operations (AS24FR) course.
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
- Expert IBM i instructor: training is delivered by an IBM i expert with real production experience.
- Practical, field-focused training: the programme is highly practical and grounded in real-world operations, promoting a hands-on approach to IBM i administration with SQL.
- Progressive replacement of CLP with modern SQL: learn to progressively replace Control Language Programs (CLP) with more efficient, modern SQL methods.
- Hands-on exercises: training alternates theory and practice through labs and demonstrations.
Dates and sessions
Choose the date and delivery format that suit you.
No upcoming sessions are currently available.
IBM and IBM i are registered trademarks of International Business Machines Corporation in the United States and/or other countries.