Nicolae,
I agree with you that the performance results can be spectacular if one regular uses the ACS Plan Cache tuning to address the most time consuming and expensive queries on the system.
Based on my Technology Services team experiences performing SQL Performance Assessment for clients, this Plan Cache analysis work is not being done enough. Part of the reason this work is not being done, is that too many customers have not invested in a Database Engineer (DBE) for their teams. One of the roles the DBE would perform is regular analysis of the Plan Cache. Last year I wrote a
blog entry describing this DBE role.
I'll also point there's
Db2 for i SQL community where performance tuning is discussed.
------------------------------
Kent Milligan
------------------------------
Original Message:
Sent: Wed January 25, 2023 11:18 AM
From: Nicolae Chirea
Subject: IBMi Database SQL Tuning
Hi Carsten,
I'm working for a business partner and yes, I did some performance analysis and tunning (not as often as I would want). In general, the results are quite spectacular with processes that reduce the run time more than 50 %.
You are talking about IBMi Navigator – you must switch to ACS and SQL performance center. There is where Plan Cache statement analysis must be done. Sometime with just few indexes the result could be surprising but, in general, more in depth investigation has to be done. In SQL example you will find all necessary to do Plan cache snapshots. That helps with an exhaustive research when a performance problem happen.
Take a look at the book IBM Db2 for i indexing methods and strategies ( https://www.ibm.com/support/pages/system/files/inline-files/Indexing%20and%20Statistics_1.pdf)
I'm sure you will find there many good ideas about how the tunning must be done.
Nicolae
------------------------------
Nicolae Chirea
System architect
Saytel Informatica SL
Madrid
+34607868409
Original Message:
Sent: Tue January 24, 2023 01:19 PM
From: Carsten Schulz
Subject: IBMi Database SQL Tuning
Hi
I am asking me how often are you doing performance tuning at your customers or your own IBMi databases. Particular the SQL statement investigation with the IBMi Navigator where you can see how often an SQL is processed or which one are the long running SQL statements.
From my point of view this is a great possibility to speed up complex applications.
Carsten
------------------------------
Carsten Schulz
------------------------------