Home | Contact Us | FAQ | Search & Site Map | Link to Us
Sign In | Join | Other 45 Sites in Network
Home
Discussion Groups
Database Servers
DB2InformixIngresMS SQLOraclePervasive.SQLPostgreSQLProgressSybase
Desktop Databases
FileMakerFoxProMS AccessParadox
General
General DB TopicsDatabase Theory
Related Topics
Java Development.NET DevelopmentVB DevelopmentMore Topics ...

Database Forum / DB2 Topics / January 2007

Tip: Looking for answers? Try searching our database.

Snapshot functions monitoring -801 ???

Thread view: 
Enable EMail Alerts  Start New Thread
Thread rating: 
aj - 10 Jan 2007 14:00 GMT
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
mike - 10 Jan 2007 15:16 GMT
> 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 ?
fboldt@fastmail.fm - 10 Jan 2007 15:21 GMT
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
>
[quoted text clipped - 32 lines]
>
> aj
Knut Stolze - 10 Jan 2007 15:56 GMT
> aj,
>
[quoted text clipped - 8 lines]
> 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

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

aj - 10 Jan 2007 18:39 GMT
>> aj,
>>
[quoted text clipped - 14 lines]
> 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
Knut Stolze - 11 Jan 2007 09:14 GMT
>> - 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.

Signature

Knut Stolze
DB2 z/OS Utilities Development
IBM Germany

aj - 11 Jan 2007 13:54 GMT
Thanks for your help Knut.

>>> - how exactly have you set DFT_SQLMATHWARN?
>> UPDATE DB CFG FOR <mydb> USING DFT_SQLMATHWARN YES
[quoted text clipped - 29 lines]
>
> So the change is not active yet.
 
Sign In
Join
My Latest Posts
My Monitored Threads
My Blog
My Photo Gallery
My Profile
My Homepage

Start New Thread
Enable EMail Alerts
Rate this Thread



©2009 Advenet LLC   Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.