473,324 Members | 2,581 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,324 software developers and data experts.

Snapshot functions monitoring -801 ???

aj
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
Jan 10 '07 #1
6 6730


On Jan 10, 2:00 pm, aj <ron...@mcdonalds.comwrote:
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 ?

Jan 10 '07 #2
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
Jan 10 '07 #3
fb****@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
Jan 10 '07 #4
aj
Knut Stolze wrote:
fb****@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 <mydbUSING 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 <mydbshow 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
Jan 10 '07 #5
aj wrote:
>- how exactly have you set DFT_SQLMATHWARN?
UPDATE DB CFG FOR <mydbUSING 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 <mydbshow 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
Jan 11 '07 #6
aj
Thanks for your help Knut.

Knut Stolze wrote:
aj wrote:
>>- how exactly have you set DFT_SQLMATHWARN?
UPDATE DB CFG FOR <mydbUSING 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 <mydbshow 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.
Jan 11 '07 #7

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

8
by: Jean-Marc Blaise | last post by:
Dear all, It seems there is a problem with some snapshot table functions, if you try to divide by some element that is unset or equal to 0. This generates a trap file with the db2fmp.exe...
2
by: hype | last post by:
Hi, 1) If I need to monitor a resource eg, bufferpools at the database level across all users, how can I do this ? If snapshot monitoring needs to be done wouldn't enabling the switch at the DBM...
3
by: Paul Reddin | last post by:
Hi, V8.1 fp4a We have been performing some performance measurements on SQL functions and have observed the following and need to verify if this is expected behaviour. Our SQL Functions...
4
by: Jennie | last post by:
I need to collect dynamic SQL execution times for a production database. However, the totals are not being updated, despite setting the DFT_MON_TIMESTAMP on. The DFT_MON_TIMESTAMP is listed in...
1
by: sudhakar_krao | last post by:
Hi, I have installed DB2 UDB v8.2 on Solaris 5.8. Primarily an Oracle DBA, I am new to DB2 and looking for the sqlmon.h file every where to get the values for monitoring status like...
4
by: Civilian_Target | last post by:
Is there any way in DB2 to automate the taking of a snapshot, for example, a function that will cause a db2 snapshot to be taken every 5 minutes and written to a uniquely named file? Am I better...
0
by: Issam | last post by:
When I reset a monitoring switch using a command such as follows: reset monitor all I note that the monitoring switch, which can be accessed as follows, works as expected (values are reset to...
9
by: bluedolphin | last post by:
Hello All: I have been brought onboard to help on a project that had some performance problems last year. I have taken some steps to address the issues in question, but a huge question mark...
0
by: aj | last post by:
I wrote the following little *nix monitoring script when I needed to know more about a nocturnal critter that was grabbing too much CPU.. Perhaps you can use it. Just put something like this in...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
1
isladogs
by: isladogs | last post by:
The next Access Europe meeting will be on Wednesday 6 Mar 2024 starting at 18:00 UK time (6PM UTC) and finishing at about 19:15 (7.15PM). In this month's session, we are pleased to welcome back...
0
by: jfyes | last post by:
As a hardware engineer, after seeing that CEIWEI recently released a new tool for Modbus RTU Over TCP/UDP filtering and monitoring, I actively went to its official website to take a look. It turned...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: Faith0G | last post by:
I am starting a new it consulting business and it's been a while since I setup a new website. Is wordpress still the best web based software for hosting a 5 page website? The webpages will be...
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.