This is what Jan and I both thought but I believe it must be the other way around. The example I quote from the ADVISE_WORKLOAD table shows a COST_BEFORE of 15,212,166,000 and a FREQUENCY of 400,320,159. If you multiply those two figures, you get a colossal number. If you divide COST_BEFORE by FREQUENCY you get 37.
If you run the statement text that you can extract from ADVISE_WORKLOAD through db2expln, you get an Estimated Cost of just over 37. The only logical conclusion I can come to is that the COST_BEFORE value is the total cost.
But that isn't what the Knowledge Centre suggests.
Regards
Mark Gillis
------------------------------
Mark Gillis
Principal Consultant
Triton Consulting
------------------------------
Original Message:
Sent: Sat February 07, 2026 12:30 PM
From: Madhusudan S M
Subject: COST_BEFORE in ADVISE_WORKLOAD Table
In the ADVISE_WORKLOAD table in Db2, there are 2 columns namely STATEMENT_TEXT and STATEMENT_NO
Example
- Query A: COST_BEFORE = 10,000 timerons, FREQUENCY = 1
→ Total contribution = 10,000 - Query B: COST_BEFORE = 500 timerons, FREQUENCY = 100
→ Total contribution = 50,000
Original Message:
Sent: 2/7/2026 11:25:00 AM
From: Madhusudan S M
Subject: RE: COST_BEFORE in ADVISE_WORKLOAD Table
Hi Mark,
Would you like to run the following statement?
db2advis -d dbname -s "sql statement query"
Original Message:
Sent: 2/6/2026 11:16:00 AM
From: Mark Gillis
Subject: RE: COST_BEFORE in ADVISE_WORKLOAD Table
That is what I thought originally too Jan. But a recent experiment has given me a COST_BEFORE of 15212166000.00 for a query in ADVISE_WORKLOAD. If I extract the SQL and put it through db2expln, I get an Estimated Cost of 37.996162.
And the FREQUENCY stored for this SQL in the ADVISE_WORKLOAD is 400320159 so if you reverse the calculation:
db2 values "15212166000 / 400320159"
1
--------------------
37
So it seems to be total cost before but that isn't what the Knowledge Centre suggests
Regards
Mark
------------------------------
Mark Gillis
Principal Consultant
Triton Consulting
------------------------------
Original Message:
Sent: Fri February 06, 2026 11:04 AM
From: Jan Nelken
Subject: COST_BEFORE in ADVISE_WORKLOAD Table
Hi Mark,
I was using COST_BEFORE and COST_AFTER assuming it is a cost of a single query execution; so you would need to multiply it by frequency to get weighted difference, e.g:
select decimal(cost_before,25,2) as before , decimal(cost_after,25,2) as after , frequency , decimal((cost_before-cost_after)*frequency,20,5) as weighted_diff , decimal(((cost_before-cost_after)*frequency)/(cost_before*frequency),5,2) as diff_pct , substr(statement_text,1,100) as stmtfrom advise_workloadwhere cost_before is not null and workload_name='my_example'order by weighted_diff descfetch first 20 rows onlywith ur
------------------------------
Jan Nelken
Db2 (LUW) DBA
Open for work
Katowice or Krakow
+48 783 109 863