0

I am using the following query to get some tablespaces usage at a glance:

db2 "select substr(tbsp_name,1,30) as Tablespace_Name, tbsp_type as Type, substr(tbsp_state,1,20) as Status, (tbsp_total_size_kb / 1024 ) as Size_Meg, smallint((float(tbsp_free_size_kb)/ float(tbsp_total_size_kb))*100) as Percent_Free_Space, int((tbsp_free_size_kb) / 1024 )as Meg_Free_Space from sysibmadm.tbsp_utilization where smallint((float(tbsp_free_size_kb)/ float(tbsp_total_size_kb))*100) < 20 order by Percent_Free_Space"

however, I'm stuck with the following error:

SQL0801N Division by zero was attempted. SQLSTATE=22012

I understand you can fix this error with a NULLIF however I can't find the correct way to set in in the query, thanks for the help.

( using "DB2 v9.7.0.11", "s150922", "IP23937", and Fix Pack "11")

marc_s
  • 732,580
  • 175
  • 1,330
  • 1,459
Mike
  • 1
  • 2

2 Answers2

0

Maybe you should use case:

CASE WHEN tbsp_total_size_kb=0 THEN NULL ELSE (tbsp_total_size_kb / 1024 ) END as Size_Meg

if tbsp_total_size_kb can be null IFNULL like this:

CASE WHEN IFNULL(tbsp_total_size_kb,0)=0 THEN NULL ELSE (tbsp_total_size_kb / 1024 ) END as Size_Meg
Muhammad Dyas Yaskur
  • 6,914
  • 10
  • 48
  • 73
  • Thanks for your comment brother, how would it be the complete query with this modification? thanks again – Mike Mar 11 '20 at 02:04
0

Try this:

smallint(float(tbsp_free_size_kb) / float(nullif(tbsp_total_size_kb, 0)) * 100)

Mark Barinstein
  • 11,456
  • 2
  • 8
  • 16