Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Table statistics information required from system catalog

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2
View previous topic :: :: View next topic  
Author Message
amarjit singh

New User


Joined: 08 Mar 2013
Posts: 30
Location: india

PostPosted: Fri Mar 08, 2013 3:03 pm    Post subject: Table statistics information required from system catalog
Reply with quote

Hello Experts,

I need your help on the following requirement.
I need to generate a report on daily basis which will have the count of rows getting inserted, updated, deleted in a DB2 table within last 24 hours (from mid night to mid night).

Is there any system catalog table which maintain information regardng above operations performed against a perticular table on daily basis?

i donot want the overall statistics since last reorg but want the statistics in last 24 hours on daily basis. i need to generate the same for 20 more tables. please suggest.

thank you ,
amarjit
Back to top
View user's profile Send private message

GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Mar 08, 2013 4:34 pm    Post subject:
Reply with quote

- in sysibm.systablespacestats are some numbers since last reorg/runstats. You probably will have to remember those numbers since the last run somewhere and calculate the difference.
- Maybe some log-analyzer tool might be able to produce this.
- triggers defined on the tables is an (expensive) option.
Back to top
View user's profile Send private message
amarjit singh

New User


Joined: 08 Mar 2013
Posts: 30
Location: india

PostPosted: Fri Mar 08, 2013 7:16 pm    Post subject:
Reply with quote

GuyC wrote:
- in sysibm.systablespacestats are some numbers since last reorg/runstats. You probably will have to remember those numbers since the last run somewhere and calculate the difference.
- Maybe some log-analyzer tool might be able to produce this.
- triggers defined on the tables is an (expensive) option.


hello GuyC,

thank you for your quick response.
i did try fetching data from SYSIBM.SYSTABLESPACESTATS table.

STATSINSERTS, STATSUPDATES and STATSDELETES columns contain
the count. But the count is coming against the Database as a whole, not against a perticular table. The required tables are mapped to different databases and single databas contains multiple tables.

is there any catalog table which will give table specific statistics?
please suggest. thank you.
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Mar 08, 2013 7:31 pm    Post subject:
Reply with quote

systablespacestats is per database.tablespace.
If you have followed "best practices", each tablespace should contain only 1 table, and thus the numbers are relevant for 1 table.
Back to top
View user's profile Send private message
amarjit singh

New User


Joined: 08 Mar 2013
Posts: 30
Location: india

PostPosted: Fri Mar 08, 2013 8:45 pm    Post subject:
Reply with quote

GuyC wrote:
systablespacestats is per database.tablespace.
If you have followed "best practices", each tablespace should contain only 1 table, and thus the numbers are relevant for 1 table.


Thanks a lot sir. i got it. Regards
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Mar 08, 2013 8:53 pm    Post subject:
Reply with quote

you're welcome
Back to top
View user's profile Send private message
amarjit singh

New User


Joined: 08 Mar 2013
Posts: 30
Location: india

PostPosted: Fri Mar 15, 2013 5:22 pm    Post subject:
Reply with quote

hello GuyC,

Again in doubt...
I ran the following query:

SELECT NAME, COUNT(NAME) AS COUNT, SUM(STATSINSERTS) AS INSERT, SUM(STATSDELETES) AS DELETE, SUM(STATSUPDATES) AS UPDATE FROM SYSIBM.SYSTABLESPACESTATS WHERE NAME IN
('TS1','TS2','TS3',......'TS15') GROUP BY NAME WITH UR:

When i SUBSTRACT day before yesterday's result from yesterday's result, i got negative count against some table space. please check below:

NAME COUNT INSERT DELETE UPDATE
TS1 16 2 0 21551
TS2 1 0 0 0
TS3 16 556 0 8216
TS4 271 120039 0 6375602
TS5 270 -1534487 0 -1356191
TS6 340 868836 0 3424005
TS7 270 216277 0 0
TS8 340 1927304 0 0
TS9 16 7566 1457 35183
TS10 270 -4619517 -6215989 -13660807
TS11 270 -201650 -980518 0

TS12 16 71259 0 0
TS13 16 223 571 0
TS14 270 270100 0 51656
TS15 16 18 0 17

similarly when i SUBSTRACT yesterday's result from today's ,
again i got negative count against some table space. please check below:

NAME COUNT INSERT DELETE UPDATE
TS1 16 1 0 14733
TS2 1 0 0 0
TS3 16 470 0 8668
TS4 271 26700 0 4635251
TS5 270 409533 0 375481
TS6 340 40687 0 2690323
TS7 270 237761 0 0
TS8 340 1329988 0 0
TS9 16 5882 6876 29258
TS10 270 741199 763616 2082946
TS11 270 -111410 -109104 0

TS12 16 54847 0 0
TS13 16 441 10908 0
TS14 270 149010 0 11834
TS15 16 14 0 13

multi rows shows the Table space has several partitions. so i select the sum of all partitions and compare with previous day's result.
please suggest how to deal with -ve count as i guess every day we should get count in incremntal order.

thanks.
amarjit
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Mar 15, 2013 5:58 pm    Post subject:
Reply with quote

the numbers are since last RUNSTATS.
So when a runstats runs, the numbers are reset to zero.
you can check this in the column STATSLASTTIME.
if this is different from STATSLASTTIME from yesterday, then your numbers are reset.
Back to top
View user's profile Send private message
amarjit singh

New User


Joined: 08 Mar 2013
Posts: 30
Location: india

PostPosted: Fri Mar 15, 2013 6:38 pm    Post subject:
Reply with quote

GuyC wrote:
the numbers are since last RUNSTATS.
So when a runstats runs, the numbers are reset to zero.
you can check this in the column STATSLASTTIME.
if this is different from STATSLASTTIME from yesterday, then your numbers are reset.


Hello,
Yes, in some partion we have recent time stamp in STATSLASTTIME while rest have much older time stamp in same Table space.
so i need to further compare time stamp of each partition within table space to come up with result..

if the STATSLASTTIME is yesterday's then i need to take the count as it is.

if the STATSLASTTIME is older than yesterday's then i need to take the difference of count from yesterday.

finally need to sum all the partion's results to get the total count against one table space. Then finaly, yesterday's result count of this tablespace can be substract from this value to get the required count for one day..

Is there any coulmn where we can uniquely identify each partion's row of the table spaces for the above approach?

please correct if i miss something.

thank you,
amarjit
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Mar 15, 2013 6:51 pm    Post subject:
Reply with quote

maybe the column called "PARTITION" ? I don't know, it's just a wild guess [/sarcasm]

something like this :
Code:
SELECT A.DBNAME
     , A.NAME
     sum(case when a.statslasttime = b.statslasttime then a.statsinsert - b.statsinsert else a.statsinsert end)
FROM "SYSIBM".SYSTABLESPACESTATS A
JOIN myHISTORYtable B ON (A.DBNAME = b.dbname) and (a.name   = b.name) and (a.partition = b.partition)
where ...
group by a.dbname, a.name
 



And you must understand that any inserts/updates between your last run and the runstats are lost forever.
Back to top
View user's profile Send private message
amarjit singh

New User


Joined: 08 Mar 2013
Posts: 30
Location: india

PostPosted: Fri Mar 15, 2013 7:05 pm    Post subject:
Reply with quote

GuyC wrote:
maybe the column called "PARTITION" ? I don't know, it's just a wild guess [/sarcasm]

something like this :
Code:
SELECT A.DBNAME
     , A.NAME
     sum(case when a.statslasttime = b.statslasttime then a.statsinsert - b.statsinsert else a.statsinsert end)
FROM "SYSIBM".SYSTABLESPACESTATS A
JOIN myHISTORYtable B ON (A.DBNAME = b.dbname) and (a.name   = b.name) and (a.partition = b.partition)
where ...
group by a.dbname, a.name
 



And you must understand that any inserts/updates between your last run and the runstats are lost forever.





so is there no solution to get it done as you correctly mentioned that
'any inserts/updates between your last run and the runstats are lost forever'.....

thanks,
amarjit
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Mar 15, 2013 7:08 pm    Post subject:
Reply with quote

only if you amend your runstats-jobs to somehow store those numbers, just before the runstats
Back to top
View user's profile Send private message
amarjit singh

New User


Joined: 08 Mar 2013
Posts: 30
Location: india

PostPosted: Fri Mar 15, 2013 7:14 pm    Post subject:
Reply with quote

GuyC wrote:
only if you amend your runstats-jobs to somehow store those numbers, just before the runstats


Hello,

lets see if i can do this, though i doubt it.
that PARTITION column is not part of table icon_smile.gif

anyway thanks a lot for clearing many doubts on this log tables.

-amarjit
Back to top
View user's profile Send private message
GuyC

Senior Member


Joined: 11 Aug 2009
Posts: 1278
Location: Belgium

PostPosted: Fri Mar 15, 2013 8:02 pm    Post subject:
Reply with quote

column 28 : PARTITION
Quote:
PARTITION SMALLINT NOT NULL
The data set number within the table space. This column is used to map a data set number in a table space to its statistics. For partitioned table spaces, this value corresponds to the partition number for a single partition. For non-partitioned table spaces, this value is 0.

http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sysibmsystablespacestatstable.htm
http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sysibmsystablespacestatstable.htm
Back to top
View user's profile Send private message
amarjit singh

New User


Joined: 08 Mar 2013
Posts: 30
Location: india

PostPosted: Fri Mar 15, 2013 8:55 pm    Post subject:
Reply with quote

GuyC wrote:
column 28 : PARTITION
Quote:
PARTITION SMALLINT NOT NULL
The data set number within the table space. This column is used to map a data set number in a table space to its statistics. For partitioned table spaces, this value corresponds to the partition number for a single partition. For non-partitioned table spaces, this value is 0.

http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z9.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sysibmsystablespacestatstable.htm
http://pic.dhe.ibm.com/infocenter/dzichelp/v2r2/index.jsp?topic=%2Fcom.ibm.db2z10.doc.sqlref%2Fsrc%2Ftpc%2Fdb2z_sysibmsystablespacestatstable.htm


my mistake... i missed it
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> DB2 All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts IMS BMP program causes 878 system abend Artemk IMS DB/DC 7 Tue Nov 22, 2016 8:26 pm
No new posts Check System time(Minute) Using TIME1... balaji81_k DFSORT/ICETOOL 5 Fri Nov 11, 2016 10:53 am
No new posts Obtaining file information pahiker COBOL Programming 25 Tue Sep 27, 2016 7:25 pm
No new posts Need Rexx Program to Load in to DB2 T... Hareesh Verma CLIST & REXX 22 Tue Sep 20, 2016 2:59 pm
No new posts Get system time in micro second or cl... lind sh CICS 11 Mon Sep 05, 2016 11:14 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us