Query Needed

Database
Enthusiast

Query Needed

Hi Gurus,

as a part of Capacity Planning
the task is to : Write a couple of queries and pivot reports to identify the 10 largest tables and the 10 tables with the largest growth over last week. Develop a couple of graphs that will allow us to report table growth over time.

for this task i created a table:
CREATE SET TABLE tablespace_hst ,NO FALLBACK ,
NO BEFORE JOURNAL,
NO AFTER JOURNAL,
CHECKSUM = DEFAULT
(
LogDate DATE FORMAT 'YYYY/MM/DD',
CollectTime FLOAT,
Tablename VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
DatabaseName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
AccountName VARCHAR(30) CHARACTER SET LATIN NOT CASESPECIFIC,
CURRENTPERM FLOAT,
PEAKPERM FLOAT,
CURRENTPERMSKEW FLOAT,
PEAKPERMSKEW FLOAT)
UNIQUE PRIMARY INDEX UPI_TableSpace_Hst ( LogDate ,CollectTime ,
Tablename ,DatabaseName ,AccountName );

then did a insert into tablespace_hst sel coresspoding cols from DBC.TableSize... on the daily basis.

How to write query showing the 10 tables with the largest growth over last week???

can somebody send me the query since i have to present it to the client.

thanks
TD_DBA
3 REPLIES
Enthusiast

Re: Query Needed

Would it be any fun at all if someone were to do all your work for you?

Try using something similar to

SELECT TOP 10 ___
FROM ___
ORDER BY yoursizevalue DESC
Enthusiast

Re: Query Needed

HI

You can change and use it for getting nth Largest table as follows

SELECT TOP 1 table_col
FROM (
SELECT DISTINCT TOP n table_col
FROM Table_name
ORDER BY yoursizevalue DESC) a
ORDER BY yoursizevalue
where n > 1

Enthusiast

Re: Query Needed

thanks...