|
View previous topic :: View next topic
|
| Author |
Message |
amarjit singh
New User
Joined: 08 Mar 2013
Posts: 30
Location: india
|
|
|
|
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 |
|
 |
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
- 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 |
|
|
amarjit singh
New User
Joined: 08 Mar 2013
Posts: 30
Location: india
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
amarjit singh
New User
Joined: 08 Mar 2013
Posts: 30
Location: india
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| you're welcome |
|
| Back to top |
|
|
amarjit singh
New User
Joined: 08 Mar 2013
Posts: 30
Location: india
|
|
|
|
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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
amarjit singh
New User
Joined: 08 Mar 2013
Posts: 30
Location: india
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
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 |
|
|
amarjit singh
New User
Joined: 08 Mar 2013
Posts: 30
Location: india
|
|
|
|
| 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 |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
|
|
| only if you amend your runstats-jobs to somehow store those numbers, just before the runstats |
|
| Back to top |
|
|
amarjit singh
New User
Joined: 08 Mar 2013
Posts: 30
Location: india
|
|
|
|
| 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 
anyway thanks a lot for clearing many doubts on this log tables.
-amarjit |
|
| Back to top |
|
|
GuyC
Senior Member
Joined: 11 Aug 2009
Posts: 1281
Location: Belgium
|
|
| Back to top |
|
|
amarjit singh
New User
Joined: 08 Mar 2013
Posts: 30
Location: india
|
|
|
|
my mistake... i missed it |
|
| Back to top |
|
|
|
|
