Avatar of Enuda
 asked on

I am getting seemingly incomplete/inaccurate result from these SQL and I don't know why. Can you help debug?

Hi all, I have RHEL 4.x Linux, DB2UDBv8.2, run the followinf snapshot sql from my windows control center. I am getting results that don't seem correct to me and I would appreciate it if one or two experts can confirm my suspicion and perhaps point out the needed corrections to make:
THE SQL run from control center:

select total_log_available, total_log_used, sec_logs_allocated,sec_log_used_top,
      int(total_log_used/1024/1024) as "Log Used (Meg)",
      int(total_log_available/1024/1024) as "Log Space Free (Meg)",
      int((float(total_log_used) / float(total_log_used+total_log_available))*100) as "Pct Used",
      int(tot_log_used_top/1024/1024) as "Max Log Used (Meg)",
      int(sec_log_used_top/1024/1024) as "Max Sec. Used (Meg)",
      int(sec_logs_allocated) as "Secondaries"
from db2$mon_db
TOTAL_LOG_AVAILABLE     TOTAL_LOG_USED     Log Used (Meg)     Log Space Free (Meg)
  2609024909                               2175091                          2                             2488

Pct Used                   Max Log Used (Meg)      Max Sec. Used (Meg)    Secondaries
  0 (WHY 0 ??)                          85                                      0                          0
  1 record(s) selected

And here's a linux   df -h disk usage showing the /dmi mount point where the log files are located.
[db2inst1@fsfntudb1 ~]$ df -h
Filesystem            Size  Used Avail Use% Mounted on
                             66G   34G   29G  55% /
                             Size  Used Avail Use% Mounted on
/dev/cciss/c0d0p1      97M   41M   52M  45% /boot
none                           16G     0   16G   0% /dev/shm
/dev/sda1                 493G  370G   98G  80% /ald
/dev/sdb1                  99G   18G   76G  19% /ali
/dev/sdc1                  99G   93M   94G   1% /alt
/dev/sdd1               493G  242G  227G  52% /bbd
/dev/sde1               394G  224G  150G  60% /bbi
/dev/sdf1                 99G  7.8G   86G   9% /bbt
/dev/sdg1              345G  170G  158G  52% /dmd
/dev/sdh1                99G   53G   42G  56% /dmi  <<<---------logs
/dev/sdi1                 50G   85M   47G   1% /dmt
/dev/sdj1               345G  223G  105G  69% /dwd
/dev/sdk1                99G   11G   84G  11% /dwi
/dev/sdl1                 50G  4.0G   43G   9% /dwt
/dev/sdm1               99G  7.3G   87G   8% /stuff
/dev/sdn1              690G  176G  479G  27% /zled

Futhermore, a du command in the /dmi directory shows me this number (in bytes?) ---->>>53088276.
My question is: (1) Does anyone know what formula the linux df -h command uses to compute the above storage space numbers? (2) If I issue a linux du command, the result is in bytes or kilobytes?
(3) Looking at the SQL above, why is the "Pct Used" column zero (0)? Is it a rounding issue and how to solve it with an example?
(4) Why is TOTAL_LOG_USED (217509 meg) so different from du (directory usage 53088276 (in bytes and converted to meg?)).

Can someone please educate me what I am looking at when I see these numbers. They don't add up for me and I need to be able to rely on these numbers.


Avatar of undefined
Last Comment

8/22/2022 - Mon

2) i believe the result is in bytes, but you can issue du -k to get it in kilobytes, du -m for megabytes etc...
3) yes, try to use this
int((float(total_log_used)*100) / float(total_log_used+total_log_available))
but it won't solve your problem for sure,
if you have big active log files, and when you issue the query you did not have many transactions running, then it is reasonable to get 0 pct_used
4) the numbers you get drom db2 depends on used and free space within db2's log file, so if you have a filesystem of 100GB, but db2's log files are only 100mb in size, total_log_avaiable will not be more than 100mb

Hello momi_sabaq,
"...if you have big active log files, and when you issue the query you did not have many transactions running, then it is reasonable to get 0 pct_used"...
So that is how it is computed! I was of the opinion that the column shows the % of already used log space and not what is currently being used. My question then is why would I want to know what is currently being used? It does not make sense to me! Even if transactio(s) were running, I would think that it is more relevant to know the % of used space against the total available log space? That is what I am interested in and how can I compute that? Do you know?

At any rate, I want to know how to calculate % used from available space for the log mount point (the number you will get from the du command). Anyway you can supply this info?


View this solution by signing up for a free trial.
Members can start a 7-Day free trial and enjoy unlimited access to the platform.
See Pricing Options
Start Free Trial
Ask your own question & get feedback from real experts
Find out why thousands trust the EE community with their toughest problems.
Experts Exchange is like having an extremely knowledgeable team sitting and waiting for your call. Couldn't do my job half as well as I do without it!
James Murphy