IDUG NA 2025

Are your DB2 queries slowing down your applications? Are you struggling to meet performance SLAs? It's time to take control of your database performance with expert query tuning techniques.
This session will delve into the essential principles and best practices for optimizing DB2 queries. You'll learn how to:
 * Identify Performance Bottlenecks: Discover powerful tools and techniques to pinpoint the root causes of query slowdowns.
 * Leverage Explain Plans: Understand the execution plan of your queries to identify inefficiencies and opportunities for optimization.
 * Optimize Query Structure: Learn how to rewrite queries to improve their performance, including techniques like indexing, partitioning, and query hints.
 * Utilize DB2 Performance Tools: Explore the suite of DB2 performance monitoring and tuning tools to gain valuable insights into your database behavior.
 * Implement Effective Monitoring Strategies: Establish a robust monitoring framework to proactively identify and address performance issues.
By attending this session, you'll gain the skills and knowledge to significantly improve the performance of your DB2 queries, leading to faster response times, reduced system load, and enhanced overall database efficiency.

IBM Db2 Query Monitor for z/OS, V3.4 offers current and historical views of query activity throughout Db2 subsystems. It gives you the ability to efficiently customize and tune your SQL workload and Db2 objects to validate the effectiveness of your Db2 subsystems and improve overall performance. It also offers you extensive choices so you can determine what monitoring information you will gather, and when.
DB2 Query Monitor for z/OS enables you to identify problem SQL activity and applications, allowing you to focus your efforts on improvement. DB2 Query Monitor enables you to do the following:  – Proactively manage DB2 resources  – React quickly and effectively to supply and demand–related DB2 problems (such as those resulting from inefficient SQL or inadequate object structures)  – Identify SQL requests that prevent critical requests from completing on schedule
Db2 Query Monitor comes with two interfaces, ISPF and a Web Client.  This hands on lab will take the user through the Web Client interface.   From your web browser, you can review query activity in Db2 Query Monitor, the SQL/CA, Db2 Commands, view alerts, and configure actions and responses. 

Distributed client applications (from a server) are the most important and common of new workload using Db2 Z.  
There are many concepts and terms thrown around which we often too often gloss over without a complete understanding.
This presentation will re–explain basic terms from my user perspective plus share some experiences learned (the hard way?) over recent years.
 
This presentation covers THREADS and DBATS and CONNECTION.   
Db2 command DISPLAY DDF DETAIL will show valuable information about current DBAT usage (in–use and disconnected).  
Db2 command DISPLAY THREAD LOCATION(*) will show information about all the in–use DBAT (active, disconnecting or connecting).
Db2 itself monitors current in–use DBATS and spits out interesting message DSNL077I when in–use DBAT reaches 85% of MAXDBAT.  This is an important clue that something is probably going wrong!
Db2 statistics tell us all the same meta data about DBAT info.  Reviewing Db2 statistics, via a good Db2 performance database, can provide reports and trends on DBAT usage over time along with CPU consumption by DDF
The presentation will end with my user experience of an application issue of hitting max in–use DBAT and understanding what was going on!

This presentation examines a complex strategic migration of a mission–critical COBOL banking application from Db2 z/OS hosted on IBM mainframe to Db2 LUW on Power Linux, drawing from a Polish banking institution's transformation initiative serving 12 million customers. We explore our comprehensive approach to database performance monitoring and capacity planning while migrating a 30+ TB database to a distributed architecture. The discussion focuses on practical methodologies for performance testing, benchmarking, and risk mitigation, highlighting key differences between Db2 z/OS and Db2 LUW environments. We will share our experience in addressing platform–specific considerations and architectural adaptations required for successful migration. Through real–world scenarios and lessons learned, attendees will gain actionable insights into our approach for designing monitoring frameworks and implementing effective migration strategies for similar large–scale database modernization projects.

Db2 offers many rich data monitoring sources to collect, but how do you decide what is important or useful to collect? This session looks at regular db2mon data collection with  a focus on extracting insights from the data, both from a single db2mon report and also from multiple data collections over time.
This session will cover many performance metrics including
– system basics – activity, utilization– bottleneck checks – waiting on disk, network, locks, etc.– bufferpool activity and effectiveness– insights over time – looking at metrics from multiple reports and charting important trends
R provides a rich and repeatable set of data manipulations that provide a flexible and composable set of tools to dig into and chart the db2mon data. This is supplemented by custom Db2–developed tooling that is now open–sourced to convert multiple db2mon reports into data files grouped by report types for deeper insights on database behaviour.

Running Db2 on z/OS without a monitor is like driving a car without a speedometer or gas gauge; it’s something you need.   While monitoring at the subsystem level is important, you may ask yourself, do I really need a method of monitoring SQL?  You can try to export the Dynamic Statement Cache, or start an expensive SQL trace within your subsystem monitoring tool, but when there’s a problem, you need a quick and easy way to monitor the SQL statements.  You don’t want to recreate the issue; you want to go back in time to find out what happened.  The key software vendors for Db2 have a tool that monitors SQL.  
If you're new to Db2, just purchased a monitoring tool or maybe you don't have a tool at all, come listen to this session.  As a Db2 DBA most of my life, I'll go over the key reasons why you need to monitor your SQL.  Without an SQL monitoring tool:

Can you quickly determine the most expensive SQL statement on your subsystem?
Are you able to list all SQL statements within a package, sorted by most expensive in terms of getpages, CPU or elapsed times?
Do you receive alerts and email notes telling you there’s a resource unavailable condition or that a package failed?
Can you see who stopped your tablespace in production?

Monitoring an SQL statement is different than monitoring a Db2 thread in your subsystem monitor.  In this presentation, I’ll show you key metrics I look for.  Should you look at total getpage activity per query or the average number of getpages?  What are the delays telling me?  Are there certain SQL codes we should be monitoring?
Would extracting the host variables help in resolving SQL Code 100 return codes where no records are found?  While I will be using the IBM Db2 Query Monitor tool for screen shots, I hope to cover SQL accounting data used by all vendor tools.

This session provides an indepth overview of the best practices in running Db2 for z/OS Utiliites.  We will discuss various tips and recommendations on how to minimize application impacts and optimizing utility performance for each of the core utilities, using the latest functionalities delivered in Db2 13 up to level FL506.

There are many sessions about the EXPLAIN facility in Db2 LUW, but they all lack the concise statements to find the right SQL queries, set everything up, do the EXPLAIN and draw the right conclusions by examples and working statements to copy–and–paste.
This is a back–to–basics session, enhanced with experiences from real situations and best practices.
The material was used for internal training of colleagues.

Quickly and Easily solve your most demanding Db2 utility challenges using the latest capabilites available in the BMC AMI Db2 utilities. This presentation covers the latest enhnacements in the BMC utilities for Db2 zOS. Come learn how BMC customers solve their hardest operational problems every day using the Db2 utilities from BMC. Gain deep insights on the advanced performance, useability and automation features available today in the BMC Db2 Utilities that set us apart from the competion.

Accurate catalog statistics are the lifeblood of the Db2 query optimizer. Db2 has had automatics statistics support since version 8.2 and real–time statistics were introduced in version 9.5. Automatic statistics collection is enabled by default for new databases and is the recommend best practice. So if you are still hesitant to use them, attend this session to gain a deeper understanding of how they work and why you can trust them to simplify your DB administration and improve overall query performance. In addition to providing details on the automatic statistics architecture, this presentation will provide details on ways to monitor and control automatic statistics collection so that it works well for any type of Db2 system.

This is a back–to–basics session that will cover the fundamentals of Db2 Large Objects (LOBs), their structure, different LOB types, how they are implemented, considerations for daily management and how it is possible to Improve application performance transforming standard lobs to Inline Lobs.
Db2 LOBs enable you to store large amounts of data in a single column. The data can be structured in nature, but more often contain unstructured data such as images or audio data. LOB implementation requires auxiliary objects to be created, which increases the complexity of object administration, space utilization, and utility processing.
This session provides an overview of Db2 LOBs, things to watch out for, and tips for tuning and space allocation.
Objectives:1.    Provide an overview of LOB types, and how they are structured and implemented.2.    Provide tips for performance tuning and space utilization using the Inline Lobs.3.    How to avoid LOB data corruption scenarios and ensure data integrity.

An engaging exploration of the hot topic sweeping through the tech world, with a particular focus on mainframe modernization.
This interactive session will demystify the concepts surrounding modernization, including rehosting, refactoring, replatforming, repurchasing, retiring, and retaining.
Through a fun game of Buzzword Bingo, attendees will gain insights into how modernization, concepts and all things new, can elevate their skills and keep their organizations competitive in the digital age. The presentation will highlight the importance of embracing modernization while also appreciating the enduring value of legacy ... legendary systems.
Don't be left behind – join us to understand why modernization is more than just a trend and how it can shape the future of your career and industry.

We will illustrate how applications developers can easily use the self–service facilities to make changes, test, commit, push, automate deployment with automated CI/CD pipeline. Such changes can only be done within pre–established rules set by DBA and other administrators.
During this session we will show you the steps administrators would take to set the framework followed by the steps a developer would take to modify an application including database changes and additional objects.

(IDUG EMEA 2024 BEST USER SPEAKER)
SQL stored procedures are a very common method of delivering Db2 functionality to applications.  But when they need performance tuning how do you achieve this.   In this presentation we will explore the components behind SQL stored procedures and how to tune the individual elements.

Join the IDUG Content Committee for this fun interactive session where you will deepen your knowledge of SQL. This session will focus on the use of table expressions. We will have a short presentation and there will be an opportunity to sharpen your skills with others dojo style.

Issue: Files are transferred between two LPARS that have a  DASD as storage. Due to high volume of file transfer there is issue with CPU utilisation going high that affects the performance strategies and customers need to pay more. 

Solution:In this scenario,I have added an alarm in the LPAR using  utility  software. The mechanism is once the CPU utilisation is going high  and the task has reached a threshold limit  an alarm on the LPAR will get triggered to inbox stating recycle of that task is required.

New to Db2 for z/OS? Or maybe you just need a refresher on the basics? No matter where you are on your Db2 journey, put your knowledge and what you have learned this week to the test in a session styled after the famous game show we all know and love. Welcome to Db2pardy!

Are you struggling with Db2 subsystem and application performance? Join this comprehensive workshop to enhance your skills in Db2 performance management, covering everything from foundational concepts to advanced techniques. We will examine the critical Db2 components that influence subsystem and application throughput, and learn how to identify, monitor, and optimize key health indicators. Additionally, we will share best practices for effective performance analysis and demonstrate how modern open tools can simplify these processes. You'll leave with practical knowledge to improve Db2 performance and streamline your tuning efforts.