SEO

DB2 LUW 8.2.7 FP14 Red Hat AS2.1

(w/ thanks to Chris Eaton for the cool scripts)

I did this:
create view DB2$MON_BP
as select * from table(snapshot_BP('',-1)) as sntable;

then this:
create view db2$vn_bphitratio as
with bpsnap as (
select bp_name,
float(pool_data_p_reads) as dp_read,
float(pool_data_l_reads) as dl_read,
float(pool_index_p_reads) as ip_read,
float(pool_index_l_reads) as il_read
from db2$mon_bp )
select bp_name,
dec((1 - (dp_read / dl_read))*100,4,1) as "Data Hit Ratio",
dec((1 - (ip_read / il_read))*100,4,1) as "Index Hit Ratio"
from bpsnap ;

Now, I get this:
db2 => select * from oltp.db2$vn_bphitratio
SQL1476N The current transaction was rolled back because of error
"-801". SQLSTATE=40506
db2 =>

-801 is a divide by zero error. My DB CFG DFT_SQLMATHWARN *is* set to
YES for this database. My BUFFERPOOL monitor is also ON.

Any ideas?

TIA

aj

On Jan 10, 2:00 pm, aj <ron...@mcdonalds.com> wrote:
> Now, I get this:
> db2 => select * from oltp.db2$vn_bphitratio
> SQL1476N The current transaction was rolled back because of error
> "-801". SQLSTATE=40506
> db2 =>
> Any ideas?

Maybe you need to exclude the rows that have dl_read or il_read = 0 ?

aj,

You could just try to output the values:
select bp_name,
float(pool_data_p_reads) as dp_read,
float(pool_data_l_reads) as dl_read,
float(pool_index_p_reads) as ip_read,
float(pool_index_l_reads) as il_read
from db2$mon_bp )

I guess you would see some Zeros for dl_read or il_read which would
definately lead to a -804.

Just a guess. So you can use case statements to deal with the zeros.

Is that help?

cheers
Florian


aj schrieb:

> DB2 LUW 8.2.7 FP14 Red Hat AS2.1
>
> (w/ thanks to Chris Eaton for the cool scripts)
>
> I did this:
> create view DB2$MON_BP
> as select * from table(snapshot_BP('',-1)) as sntable;
>
> then this:
> create view db2$vn_bphitratio as
> with bpsnap as (
> select bp_name,
> float(pool_data_p_reads) as dp_read,
> float(pool_data_l_reads) as dl_read,
> float(pool_index_p_reads) as ip_read,
> float(pool_index_l_reads) as il_read
> from db2$mon_bp )
> select bp_name,
> dec((1 - (dp_read / dl_read))*100,4,1) as "Data Hit Ratio",
> dec((1 - (ip_read / il_read))*100,4,1) as "Index Hit Ratio"
> from bpsnap ;
>
> Now, I get this:
> db2 => select * from oltp.db2$vn_bphitratio
> SQL1476N The current transaction was rolled back because of error
> "-801". SQLSTATE=40506
> db2 =>
>
> -801 is a divide by zero error. My DB CFG DFT_SQLMATHWARN *is* set to
> YES for this database. My BUFFERPOOL monitor is also ON.
>
> Any ideas?
>
> TIA
>
> aj

fboldt@fastmail.fm wrote:

> aj,
>
> You could just try to output the values:
> select bp_name,
> float(pool_data_p_reads) as dp_read,
> float(pool_data_l_reads) as dl_read,
> float(pool_index_p_reads) as ip_read,
> float(pool_index_l_reads) as il_read
> from db2$mon_bp )
>
> I guess you would see some Zeros for dl_read or il_read which would
> definately lead to a -804.

That's probably the case here. However, the setting of DFT_SQLMATHWARN
should prevent the SQL0801!

So there are a few questions to be answered by the OP first:
- how exactly have you set DFT_SQLMATHWARN?
- have you restarted DB2 after setting it?
- what does GET DB CFG SHOW DETAIL show you for this config parameter?
- what does the following statement produce?

VALUES 1 / 0

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Knut Stolze wrote:
> fboldt@fastmail.fm wrote:
>
>> aj,
>>
>> You could just try to output the values:
>> select bp_name,
>> float(pool_data_p_reads) as dp_read,
>> float(pool_data_l_reads) as dl_read,
>> float(pool_index_p_reads) as ip_read,
>> float(pool_index_l_reads) as il_read
>> from db2$mon_bp )
>>
>> I guess you would see some Zeros for dl_read or il_read which would
>> definately lead to a -804.
>
> That's probably the case here. However, the setting of DFT_SQLMATHWARN
> should prevent the SQL0801!
>
> So there are a few questions to be answered by the OP first:
> - how exactly have you set DFT_SQLMATHWARN?
UPDATE DB CFG FOR <mydb> USING DFT_SQLMATHWARN YES

> - have you restarted DB2 after setting it?
No. I didn't think I had to. DB2 typically tells me if I need to,
and it didn't.

> - what does GET DB CFG SHOW DETAIL show you for this config parameter?
db2 get db cfg for <mydb> show detail | grep -i math
Continue upon arithmetic exceptions(DFT_SQLMATHWARN) = NO YES


I think you just answered my question. I need an instance bounce (or
perhaps a DB deactivate/activate?) before this is actually set?

btw - I had no idea you could use SHOW DETAIL on DBM CFG output to see
current value and delayed value. Nice tip, thanks.

> - what does the following statement produce?>
> VALUES 1 / 0
SQL0801N Division by zero was attempted. SQLSTATE=22012

aj

aj wrote:

>> - how exactly have you set DFT_SQLMATHWARN?
> UPDATE DB CFG FOR <mydb> USING DFT_SQLMATHWARN YES
>
>> - have you restarted DB2 after setting it?
> No. I didn't think I had to. DB2 typically tells me if I need to,
> and it didn't.

Well, I made the same change and DB2 did tell me to restart:

SQL1363W One or more of the parameters submitted for immediate modification
were not changed dynamically. For these configuration parameters, all
applications must disconnect from this database before the changes become
effective.

>> - what does GET DB CFG SHOW DETAIL show you for this config parameter?
> db2 get db cfg for <mydb> show detail | grep -i math
> Continue upon arithmetic exceptions(DFT_SQLMATHWARN) = NO YES
>
> I think you just answered my question. I need an instance bounce (or
> perhaps a DB deactivate/activate?) before this is actually set?

deactivate/activate is sufficient, i.e. make sure that no connection to the
DB exists and if the DB was activated explicitly, run the DEACTIVATE
command.

> btw - I had no idea you could use SHOW DETAIL on DBM CFG output to see
> current value and delayed value. Nice tip, thanks.
>
>> - what does the following statement produce?>
>> VALUES 1 / 0
> SQL0801N Division by zero was attempted. SQLSTATE=22012

So the change is not active yet.

--
Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

Thanks for your help Knut.

Knut Stolze wrote:
> aj wrote:
>
>>> - how exactly have you set DFT_SQLMATHWARN?
>> UPDATE DB CFG FOR <mydb> USING DFT_SQLMATHWARN YES
>>
>>> - have you restarted DB2 after setting it?
>> No. I didn't think I had to. DB2 typically tells me if I need to,
>> and it didn't.
>
> Well, I made the same change and DB2 did tell me to restart:
>
> SQL1363W One or more of the parameters submitted for immediate modification
> were not changed dynamically. For these configuration parameters, all
> applications must disconnect from this database before the changes become
> effective.
>
>>> - what does GET DB CFG SHOW DETAIL show you for this config parameter?
>> db2 get db cfg for <mydb> show detail | grep -i math
>> Continue upon arithmetic exceptions(DFT_SQLMATHWARN) = NO YES
>>
>> I think you just answered my question. I need an instance bounce (or
>> perhaps a DB deactivate/activate?) before this is actually set?
>
> deactivate/activate is sufficient, i.e. make sure that no connection to the
> DB exists and if the DB was activated explicitly, run the DEACTIVATE
> command.
>
>> btw - I had no idea you could use SHOW DETAIL on DBM CFG output to see
>> current value and delayed value. Nice tip, thanks.
>>
>>> - what does the following statement produce?>
>>> VALUES 1 / 0
>> SQL0801N Division by zero was attempted. SQLSTATE=22012
>
> So the change is not active yet.
>