Hi colleagues,
I have the followind script.
db2 -x "select substr(TBSPACE,1,20) TABLESPACE from syscat.tables where tabschema = 'SCHEMA' and tabname like '%XXXX' group by TBSPACE order by TBSPACE" | awk '{print $1}' | while read tablespace
do
db2 "list tablespaces show detail" |grep -p -w $tablespace |egrep "Name|Total pages|Free pages|Page size" |awk '{print $1 " " $2 " " $3 " " $4 " " $5}' | while read a b c d e
do
done
done
The output of this script is this.
Name = TSADOR
Total pages = 14991360
Free pages = 5345152
Page size (bytes) = 32768
Name = TSHISTNEW
Total pages = 4186112
Free pages = 1276544
Page size (bytes) = 32768
Name = TSHISTTI
Total pages = 6279168
Free pages = 1991168
Page size (bytes) = 32768
The desired output is this, please:
Tableaspace Total Pages Free Pages Page Zise
TSADOR 14991360 5345152 32768
TSHISTNEW 4186112 1276544 32768
TSHISTTI 6279168 1991168 32768
T O T A L ------------- ------------
25456640 8612864
Thank you very match for you help.
What have you tried so far? What went wrong?
I could give you a ksh script to call the db2 command with if that helps.
Robin
Liverpool/Blackburn
Thank you rBatte1,
I tried this so far
db2 -x "select substr(TBSPACE,1,20) TABLESPACE from syscat.tables where tabschema = 'SCHEMA' and tabname like '%XXXX' group by TBSPACE order by TBSPACE" | awk '{print $1}' | while read tablespace
do
db2 "list tablespaces show detail" |grep -p -w $tablespace |egrep "Name|Total pages|Free pages|Page size" |awk '{print $1 " " $2 " " $3 " " $4 " " $5}' | while read a b c d e
do
done
done
and the output is this:
Code:
Name = TSADOR Total pages = 14991360 Free pages = 5345152 Page size (bytes) = 32768 Name = TSHISTNEW Total pages = 4186112 Free pages = 1276544 Page size (bytes) = 32768 Name = TSHISTTI Total pages = 6279168 Free pages = 1991168 Page size (bytes) = 32768
The output desired is this:
Tableaspace Total Pages Free Pages Page Zise
TSADOR 14991360 5345152 32768
TSHISTNEW 4186112 1276544 32768
TSHISTTI 6279168 1991168 32768
T O T A L ------------- ------------
25456640 8612864
if you can give me a script so be it.
Would you not expect to execute something between the last do and done for the while loop?
If the output from a single query using tablespace TSADOR is:-
$ db2 "list tablespaces show detail" |grep -p -w TSADOR |egrep "Name|Total pages|Free pages|Page size"
Name = TSADOR
Total pages = 14991360
Free pages = 5345152
Page size (bytes) = 32768
$
...then I'm wondering why the awk is here.
Could you try:-
$ db2 "list tablespaces show detail" |grep -p -w TSADOR | egrep "Name|Total pages|Free pages|Page size" | while read label equals value rest
do
echo "$label\t\c" # Display third item then tab but don't end the output line.
done ; echo # End the output line
... and see if that gives you the output you need for one tablespace.
If so, then you can blend that back to what you already have to loop round for each tablespace. If not, show us the output and we'll see if we can fix it. I might be good to see the plain output from:-
$ db2 "list tablespaces show detail"
by itself so we can consider if this is the best approach. It might be possible to write the query to format the output instead. If it was Oracle, I'd do something like:-
$ cat my_query.sql
column tablespace_name format a15 ;
column total_pages format 99999 ;
column free_pages format 99999 ;
column page_size format 99999 ;
select tablespace_name 'Tablespace name', total_pages 'Total Pages', free_pages 'Free Pages', page_size 'Page Size' from dba_tablespaces
where tablespace_name in
( select unique tablespace_name from dba_segments where owner = 'SCHEMA' and segment_name like '%XXXX%' ) ;
$ sqlplus / @my_query.sql
I can't remember the exact syntax and don't have a server to try this on, but obviously there will be variations to drive against DB2.
Do these help?
Robin
Liverpool/Blackburn
Hi colleagues, thank you for you fast response,
I have run this commands
db2 -x "select substr(TBSPACE,1,20) TABLESPACE from syscat.tables where tabschema = 'MIATLAS' and tabname like '%TH' group by TBSPACE order by 1" | while read tablespace
do
db2 "list tablespaces show detail" |grep -p -w $tablespace | egrep "Name|Total pages|Free pages|Page size" | while read label equals value rest
do
echo "$label $equals $value $rest\t\c"
done ; echo
done ; echo
the output is as follows
Name = TSADOR Total pages = 14991360 Free pages = 5345152 Page size (bytes) = 32768
Name = TSHISTNEW Total pages = 4186112 Free pages = 1276544 Page size (bytes) = 32768
Name = TSHISTTI Total pages = 6279168 Free pages = 1991168 Page size (bytes) = 32768
I need to do a multiplication for the output to be like this.
Name = TSADOR Total pages = 14991360 Free pages = 5345152 Page size (bytes) = 32768 Mb=Free pages*Page size (bytes)
Name = TSHISTNEW Total pages = 4186112 Free pages = 1276544 Page size (bytes) = 32768 Mb=Free pages*Page size (bytes)
Name = TSHISTTI Total pages = 6279168 Free pages = 1991168 Page size (bytes) = 32768 Mb=Free pages*Page size (bytes)
thanks for your valuable help.
If the output from your db2 is as below, then we can simply read in the values and process them to something output like this:-
#!/bin/ksh
typeset -L20 tablespace_name
typeset -R10 tablespace_size
typeset -R10 free_size
typeset -R10 used_size
typeset -R10 page_size
echo "Tablespace name Page size Tblsp size Free size Used size"
db2 -x \
"select substr(TBSPACE,1,20) TABLESPACE from syscat.tables where tabschema = 'MIATLAS' and tabname like '%TH' group by TBSPACE order by 1" | \
while read tablespace
do
db2 "list tablespaces show detail" |grep -p -w $tablespace | egrep "Name|Total pages|Free pages|Page size" | \
while read a b tablespace_name
do
read a b c tablespace_pages
read a b c free_pages
read a b c d page_size
((tablespace_size=$tablespace_pages*$page_size))
((free_size=$free_pages*$page_size))
((used_pages=$tablespace_pages-$free_pages))
((used_size=$used_pages*$page_size))
echo "$tablespace_name $page_size $tablespace_size $free_size $used_size"
done
done
The typeset statements are assuming you are in ksh or a similar shell that recognises that. Excluding the first one, it sets the variables to by right justified, 10 characters. The first one being the name, is set to left justify, 20 characters. Hopefully this will make the output appear in neat columns. You may have to adjust these values to make the fit with the headings that follow (if you want headings at all)
The echo statement in a purple colour is free for you to adjust as you think best. If I've missed something, please feel free to query what I've done.
I've broken up the long lines to try to make it more readable by inserting a \ before a new line.
So long as there is nothing (not even a space, comment or anything) after the \ then the shell should just read it as a single line.
I hope that this helps
Robin
Liverpool/Blackburn
UK