how to view a vertical table in horizontal way
in [DB2]
|
Prev: Fetching MAX rows with one index scan.
Next: function with input parameter not found (SQL0440N)
From: pzeng on 19 Jan 2010 17:04 Hello, I have a puzzle and hope there's an easy SQL solution (meaning no need to write a script). Firstly I'm on DB2 9.7 for AIX. I want to capture the current memory usage using the snapshot view: db2 "select SNAPSHOT_TIMESTAMP,POOL_ID,POOL_CUR_SIZE from SYSIBMADM.SNAPDB_MEMORY_POOL where pool_id in ('PACKAGE_CACHE','CAT_CACHE','SHARED_SORT','LOCK_MGR','DATABASE') or (pool_id='BP' and POOL_SECONDARY_ID='1')" SNAPSHOT_TIMESTAMP POOL_ID POOL_CUR_SIZE -------------------------- -------------- -------------------- 2010-01-19-16.28.21.291644 PACKAGE_CACHE 23068672 2010-01-19-16.28.21.291644 CAT_CACHE 6815744 2010-01-19-16.28.21.291644 BP 3203923968 2010-01-19-16.28.21.291644 SHARED_SORT 655360 2010-01-19-16.28.21.291644 LOCK_MGR 113377280 2010-01-19-16.28.21.291644 DATABASE 43646976 6 record(s) selected. However, I would like to view this result in the following fashion: SNAPSHOT_TIMESTAMP PACKAGE_CACHE CAT_CACHE BP SHARED_SORT LOCK_MGR DATABASE -------------------------------------- --------------------------- ------------------ ------------------- ---------------------- ------------------- -------------------------- 2010-01-19-16.28.21.291644 23068672 6815744 3203923968 655360 113377280 43646976 Any good idea how I can achieve this result? Appreciated! Thanks, Patrick
From: pzeng on 19 Jan 2010 17:26 On Jan 19, 5:04 pm, pzeng <patrick.pucheng.z...(a)gmail.com> wrote: > Hello, I have a puzzle and hope there's an easy SQL solution (meaning > no need to write a script). > > Firstly I'm on DB2 9.7 for AIX. > > I want to capture the current memory usage using the snapshot view: > > db2 "select SNAPSHOT_TIMESTAMP,POOL_ID,POOL_CUR_SIZE from > SYSIBMADM.SNAPDB_MEMORY_POOL where pool_id in > ('PACKAGE_CACHE','CAT_CACHE','SHARED_SORT','LOCK_MGR','DATABASE') or > (pool_id='BP' and POOL_SECONDARY_ID='1')" > > SNAPSHOT_TIMESTAMP POOL_ID POOL_CUR_SIZE > -------------------------- -------------- -------------------- > 2010-01-19-16.28.21.291644 PACKAGE_CACHE 23068672 > 2010-01-19-16.28.21.291644 CAT_CACHE 6815744 > 2010-01-19-16.28.21.291644 BP 3203923968 > 2010-01-19-16.28.21.291644 SHARED_SORT 655360 > 2010-01-19-16.28.21.291644 LOCK_MGR 113377280 > 2010-01-19-16.28.21.291644 DATABASE 43646976 > > 6 record(s) selected. > > However, I would like to view this result in the following fashion: > SNAPSHOT_TIMESTAMP PACKAGE_CACHE CAT_CACHE BP > SHARED_SORT LOCK_MGR DATABASE > -------------------------------------- --------------------------- > ------------------ ------------------- ---------------------- > ------------------- -------------------------- > 2010-01-19-16.28.21.291644 23068672 6815744 > 3203923968 655360 113377280 43646976 > > Any good idea how I can achieve this result? Appreciated! > > Thanks, > > Patrick BTW, my purpose is to be able to insert this SINGLE record into a performance history table on a regular basis. Thanks, Patrick
From: The Boss on 19 Jan 2010 18:46 pzeng wrote: > Hello, I have a puzzle and hope there's an easy SQL solution (meaning > no need to write a script). > > Firstly I'm on DB2 9.7 for AIX. > > I want to capture the current memory usage using the snapshot view: > > db2 "select SNAPSHOT_TIMESTAMP,POOL_ID,POOL_CUR_SIZE from > SYSIBMADM.SNAPDB_MEMORY_POOL where pool_id in > ('PACKAGE_CACHE','CAT_CACHE','SHARED_SORT','LOCK_MGR','DATABASE') or > (pool_id='BP' and POOL_SECONDARY_ID='1')" > > SNAPSHOT_TIMESTAMP POOL_ID POOL_CUR_SIZE > -------------------------- -------------- -------------------- > 2010-01-19-16.28.21.291644 PACKAGE_CACHE 23068672 > 2010-01-19-16.28.21.291644 CAT_CACHE 6815744 > 2010-01-19-16.28.21.291644 BP 3203923968 > 2010-01-19-16.28.21.291644 SHARED_SORT 655360 > 2010-01-19-16.28.21.291644 LOCK_MGR 113377280 > 2010-01-19-16.28.21.291644 DATABASE 43646976 > > 6 record(s) selected. > > However, I would like to view this result in the following fashion: > SNAPSHOT_TIMESTAMP PACKAGE_CACHE CAT_CACHE BP > SHARED_SORT LOCK_MGR DATABASE > -------------------------------------- --------------------------- > ------------------ ------------------- ---------------------- > ------------------- -------------------------- > 2010-01-19-16.28.21.291644 23068672 6815744 > 3203923968 655360 113377280 43646976 > > Any good idea how I can achieve this result? Appreciated! > > Thanks, > > Patrick Same answer as I gave 2 years ago to a similar question: What you are looking for is called a "Pivot Query", and the best description for it is in a presentation "SQL On Fire" given by Serge Rielau during the European IDUG 2006. Check this blog-entry from Chris Eaton's blog where Chris gives an example based on Serge's material: http://blogs.ittoolbox.com/database/technology/archives/pivot-query-12757 In a comment to that blog-entry I've given links to the "SQL On Fire" material (pivoting is in part 1, iirc). HTH Cheers! -- Jeroen
From: The Boss on 19 Jan 2010 18:48 The Boss wrote: > pzeng wrote: >> Hello, I have a puzzle and hope there's an easy SQL solution (meaning >> no need to write a script). >> >> Firstly I'm on DB2 9.7 for AIX. >> >> I want to capture the current memory usage using the snapshot view: >> >> db2 "select SNAPSHOT_TIMESTAMP,POOL_ID,POOL_CUR_SIZE from >> SYSIBMADM.SNAPDB_MEMORY_POOL where pool_id in >> ('PACKAGE_CACHE','CAT_CACHE','SHARED_SORT','LOCK_MGR','DATABASE') or >> (pool_id='BP' and POOL_SECONDARY_ID='1')" >> >> SNAPSHOT_TIMESTAMP POOL_ID POOL_CUR_SIZE >> -------------------------- -------------- -------------------- >> 2010-01-19-16.28.21.291644 PACKAGE_CACHE 23068672 >> 2010-01-19-16.28.21.291644 CAT_CACHE 6815744 >> 2010-01-19-16.28.21.291644 BP 3203923968 >> 2010-01-19-16.28.21.291644 SHARED_SORT 655360 >> 2010-01-19-16.28.21.291644 LOCK_MGR 113377280 >> 2010-01-19-16.28.21.291644 DATABASE 43646976 >> >> 6 record(s) selected. >> >> However, I would like to view this result in the following fashion: >> SNAPSHOT_TIMESTAMP PACKAGE_CACHE CAT_CACHE BP >> SHARED_SORT LOCK_MGR DATABASE >> -------------------------------------- --------------------------- >> ------------------ ------------------- ---------------------- >> ------------------- -------------------------- >> 2010-01-19-16.28.21.291644 23068672 6815744 >> 3203923968 655360 113377280 43646976 >> >> Any good idea how I can achieve this result? Appreciated! >> >> Thanks, >> >> Patrick > > Same answer as I gave 2 years ago to a similar question: > > What you are looking for is called a "Pivot Query", and the best > description for it is in a presentation "SQL On Fire" given by Serge > Rielau during the European IDUG 2006. > Check this blog-entry from Chris Eaton's blog where Chris gives an > example based on Serge's material: > > http://blogs.ittoolbox.com/database/technology/archives/pivot-query-12757 > > In a comment to that blog-entry I've given links to the "SQL On Fire" > material (pivoting is in part 1, iirc). > > HTH > > Cheers! A presentation of part 1 can also be viewed online here: http://www.powershow.com/view.php?id=P1254845937tHBsM&t=SQL+on+Fire+Part+1 Pivoting starts on slide 17. Cheers! -- Jeroen
From: pzeng on 19 Jan 2010 19:12 On Jan 19, 6:48 pm, "The Boss" <use...(a)No.Spam.Please.invalid> wrote: > The Boss wrote: > > pzeng wrote: > >> Hello, I have a puzzle and hope there's an easy SQL solution (meaning > >> no need to write a script). > > >> Firstly I'm on DB2 9.7 for AIX. > > >> I want to capture the current memory usage using the snapshot view: > > >> db2 "select SNAPSHOT_TIMESTAMP,POOL_ID,POOL_CUR_SIZE from > >> SYSIBMADM.SNAPDB_MEMORY_POOL where pool_id in > >> ('PACKAGE_CACHE','CAT_CACHE','SHARED_SORT','LOCK_MGR','DATABASE') or > >> (pool_id='BP' and POOL_SECONDARY_ID='1')" > > >> SNAPSHOT_TIMESTAMP POOL_ID POOL_CUR_SIZE > >> -------------------------- -------------- -------------------- > >> 2010-01-19-16.28.21.291644 PACKAGE_CACHE 23068672 > >> 2010-01-19-16.28.21.291644 CAT_CACHE 6815744 > >> 2010-01-19-16.28.21.291644 BP 3203923968 > >> 2010-01-19-16.28.21.291644 SHARED_SORT 655360 > >> 2010-01-19-16.28.21.291644 LOCK_MGR 113377280 > >> 2010-01-19-16.28.21.291644 DATABASE 43646976 > > >> 6 record(s) selected. > > >> However, I would like to view this result in the following fashion: > >> SNAPSHOT_TIMESTAMP PACKAGE_CACHE CAT_CACHE BP > >> SHARED_SORT LOCK_MGR DATABASE > >> -------------------------------------- --------------------------- > >> ------------------ ------------------- ---------------------- > >> ------------------- -------------------------- > >> 2010-01-19-16.28.21.291644 23068672 6815744 > >> 3203923968 655360 113377280 43646976 > > >> Any good idea how I can achieve this result? Appreciated! > > >> Thanks, > > >> Patrick > > > Same answer as I gave 2 years ago to a similar question: > > > What you are looking for is called a "Pivot Query", and the best > > description for it is in a presentation "SQL On Fire" given by Serge > > Rielau during the European IDUG 2006. > > Check this blog-entry from Chris Eaton's blog where Chris gives an > > example based on Serge's material: > > >http://blogs.ittoolbox.com/database/technology/archives/pivot-query-1... > > > In a comment to that blog-entry I've given links to the "SQL On Fire" > > material (pivoting is in part 1, iirc). > > > HTH > > > Cheers! > > A presentation of part 1 can also be viewed online here:http://www.powershow.com/view.php?id=P1254845937tHBsM&t=SQL+on+Fire+P... > Pivoting starts on slide 17. > > Cheers! > > -- > Jeroen- Hide quoted text - > > - Show quoted text - Hi Jeroen, Thank you so much! Exactly what I want. :-) It works quite nicely now: WITH DBMEM AS ( select SNAPSHOT_TIMESTAMP,POOL_ID,POOL_CUR_SIZE from SYSIBMADM.SNAPDB_MEMORY_POOL where pool_id in ('PACKAGE_CACHE','CAT_CACHE','SHARED_SORT','LOCK_MGR','DATABASE') or (pool_id='BP' and POOL_SECONDARY_ID='1') ) SELECT SNAPSHOT_TIMESTAMP, MAX(CASE WHEN POOL_ID = 'BP' THEN POOL_CUR_SIZE END) AS BUFFERPOOL, MAX (CASE WHEN POOL_ID = 'PACKAGE_CACHE' THEN POOL_CUR_SIZE END) AS PACKAGE_CACHE, MAX(CASE WHEN POOL_ID = 'CAT_CACHE' THEN POOL_CUR_SIZE END) AS CAT_CACHE, MAX(CASE WHEN POOL_ID = 'SHARED_SORT' THEN POOL_CUR_SIZE END) AS SORTHEAP, MAX(CASE WHEN POOL_ID = 'LOCK_MGR' THEN POOL_CUR_SIZE END) AS LOCKLIST, MAX(CASE WHEN POOL_ID = 'DATABASE' THEN POOL_CUR_SIZE END) AS DBHEAP from DBMEM GROUP BY SNAPSHOT_TIMESTAMP SNAPSHOT_TIMESTAMP BUFFERPOOL PACKAGE_CACHE CAT_CACHE SORTHEAP LOCKLIST DBHEAP -------------------------- -------------------- -------------------- -------------------- -------------------- -------------------- -------------------- 2010-01-19-19.09.09.633127 3203923968 95158272 10813440 1310720 113377280 46268416 1 record(s) selected. Cheers, Patrick
|
Next
|
Last
Pages: 1 2 Prev: Fetching MAX rows with one index scan. Next: function with input parameter not found (SQL0440N) |