Exploring SQL Advancements on IBM i: Insights from Birgitta Hauser

Introduction:

The IBM i platform continues to evolve, offering powerful tools for developers. In a recent discussion at the European Conference Center in Milano, Birgitta Hauser, an expert in traditional RPG programming and SQL, shared her insights on the latest advancements in SQL databases on IBM i. This blog highlights key takeaways from her conversation, focusing on SQL features that can enhance productivity and efficiency for developers.

1. Birgitta Hauser: A Background

Birgitta Hauser has a rich history in traditional RPG programming, having started her career in the early ’90s. Her passion for databases and SQL was evident from the beginning, with her first programs incorporating embedded SQL. Despite the challenges of the pandemic, Birgitta transitioned to self-employment, continuing her work with SQL and IBM i.

2. New Features in SQL on IBM i

SQL has evolved significantly over the years, especially on IBM i. Birgitta highlighted several new features introduced in recent technology updates: JSON Handling: Developers can now generate and consume JSON directly within SQL. OLAP Specifications: These allow for more complex analytical queries. SQL Services: SQL now wraps around many system APIs, making tasks like job analysis more straightforward and efficient.

3. Accessing and Managing IFS Files with SQL

One of the standout features Birgitta discussed was the ability to access and manipulate IFS files using SQL. Developers can now perform operations like reading, modifying, and comparing directories directly through SQL commands, which simplifies tasks that previously required more complex API programming.

4. Enhancing Developer Efficiency with SQL

SQL on IBM i has made strides in reducing the complexity of common tasks. Birgitta emphasized features like: Spool File Conversion: Converting spool files into PDFs or Excel files can now be done with a single SQL statement. Immediate Emailing: Developers can generate spreadsheets and send them via email directly from SQL, streamlining reporting processes.

5. Optimizing SQL Performance

Birgitta shared valuable tips for optimizing SQL queries on IBM i. She recommended using common table expressions (CTEs) to break down complex queries into manageable pieces. This approach not only improves readability but also enhances performance. By rewriting queries and utilizing the index advisor, she was able to reduce query run times from over an hour to under one minute.

6. The Future of Programming with SQL

Birgitta concluded by discussing the future of programming on IBM i, emphasizing the importance of moving business logic into SQL. This shift allows for more modular and maintainable code, making it easier to adapt to changing requirements and leverage the full power of SQL’s optimization capabilities.

Conclusion:

Birgitta Hauser’s insights highlight the ongoing advancements in SQL on IBM i and the importance of staying informed about these developments. For developers working on the platform, understanding and utilizing these new features can lead to significant improvements in productivity and application performance. Whether you’re an RPG programmer or working with another language on IBM i, these tools offer new opportunities to optimize and streamline your work.

For those interested in learning more, explore the resources available from IBM and other experts like Birgitta. Attend conferences, participate in workshops, and stay up-to-date with the latest SQL advancements on IBM i.