Login or Register to Ask a Question and Join Our Community


UNIX for Advanced & Expert Users

Script to reformat output

Login to Discuss or Reply to this Discussion in Our Community

 
Thread Tools Search this Thread
Top Forums UNIX for Advanced & Expert Users Script to reformat output
# 1  
Old 06-22-2012
Script to reformat output
Hi colleagues,

I have the followind script.

Code:
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.
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 desired output is this, please:

Code:
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.
Last edited by joeyg; 06-22-2012 at 09:59 AM.. Reason: Please wrap data and sripts with CodeTags
# 2  
Old 06-22-2012
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
# 3  
Old 06-22-2012
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.
# 4  
Old 06-22-2012
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:-
Code:
$ 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:-
Code:
$ 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:-
Code:
$ 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:-
Code:
$ 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
# 5  
Old 06-25-2012
Hi colleagues, thank you for you fast response,
I have run this commands

Code:
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

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

I need to do a multiplication for the output to be like this.

Code:
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.
Moderator's Comments:
Mod Comment Code tags for code, please.
Last edited by Corona688; 06-25-2012 at 03:11 PM..
# 6  
Old 06-26-2012
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:-
Code:
#!/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
Login or Register to Ask a Question

Previous Thread | Next Thread

10 More Discussions You Might Find Interesting

1. Shell Programming and Scripting

awk to reformat output if input file is empty, but not if file has data in it

The below awk improved bu @MadeInGermany, works great as long as the input file has data in it in the below format: input chrX 25031028 25031925 chrX:25031028-25031925 ARX 631 18 chrX 25031028 25031925 chrX:25031028-25031925 ARX 632 14... (3 Replies)
Discussion started by: cmccabe
3 Replies

2. Shell Programming and Scripting

Reformat awk output

I need to rearrange the output but i am unable to arrange it to match the format. In the output i need NAME=\"To in the column . Bash: #!/bin/bash cd /cygdrive/c/output/a cat *.txt > output.txt i=/cygdrive/c/output/a/output.csv #echo "NE_Name, Source, Destination, OSPF_AREA_ID"... (4 Replies)
Discussion started by: adgjmpt
4 Replies

3. Shell Programming and Scripting

Alter awk script to reformat output

Data: 0,mfrh_green_screen,1455432969,37540,/prod/test/system/sys/unikixmain.log,3.0M,mfrh_green_screen,3120660,0,36964--37540 0,mfrh_green_screen,1455433269,38100,/prod/test/system/sys/unikixmain.log,3.1M,mfrh_green_screen,3164223,0,37540--38100... (1 Reply)
Discussion started by: SkySmart
1 Replies

4. Shell Programming and Scripting

Using awk to reformat file output

Hi there. I need to reformat a large file. Here is a sample of the file. NETIK0102_UCS_Boot_a,NETIK0102_UCS_Boot_b 5200 2438 70G 5200 2439 70G NETIK0102_UCS_HBA0_a,NETIK0102_UCS_HBA1_b,NETIK0102_UCS_HBA2_a,NETIK0102_UCS_HBA3_b 2673 19D7 55G 2673 19C0 30G 2673 19F5 120G... (5 Replies)
Discussion started by: kieranfoley
5 Replies

5. Shell Programming and Scripting

Use search pattern to reformat the output

I have below file listing ] ls -1 *.txt MISTradesReport_141105_d130240_VOLCKER_EMEA_LOANIQ_FEED_2013-12-24.txt MISTradesReport_141106_d130240_VOLCKER_NA_LOANIQ_FEED_2013-12-24.txt MISTradesReport_141107_d130240_VOLCKER_EMEA_CDS_CRDI_FEED_2013-12-24.txt... (4 Replies)
Discussion started by: krg.sati
4 Replies

6. Shell Programming and Scripting

need awk or sed help to reformat output

We have the following output: server1_J00_data_20120711122243 server1_J00_igs_20120711122243 server1_J00_j2ee_20120711122243 server1_J00_sec_20120711122243 server1_J00_data_20120711131819 server1_J00_igs_20120711131819 server1_J00_j2ee_20120711131819 server2_J00_data_20120711122245... (10 Replies)
Discussion started by: ux4me
10 Replies

7. Shell Programming and Scripting

Shell Script to Reformat a flat file

Hi , I have a text file noname.txt containing 1000+ records like this. One of the record I have given below. Input will b e like this BOT: 2010/06/01 00:25:59 21 = "private" Access-Method = 31 NCC = GBR 01 = "340806@osiris.fr.ft" 04 =... (2 Replies)
Discussion started by: smalya
2 Replies

8. Shell Programming and Scripting

reformat data with a shell script

Can anyone help me with a shell script that can do the following: I have a data in fasta format (first line is the header, followed by a sequence of characters). >ALLLY GGCCCCTCGAGCCTCGAACCGGAACCTCCAAATCCGAGACGCTCTGCTTATGAGGACCTC GAAATATGCCGGCCAGTGAAAAAATCTTGTGGCTTTGAGGGCTTTTGGTTGGCCAGGGGC... (5 Replies)
Discussion started by: manishabh
5 Replies

9. UNIX for Advanced & Expert Users

reformat ps output

I often use "ps -ef" command to list all running processes. Now i want to customize the output to show only 2 parts: CMD and UID as below: /bin/bash /usr/bin/run-parts /etc/cron.weekly root /usr/sbin/httpd apache /usr/sbin/httpd apache /usr/sbin/httpd apache I use ps -ef | awk '{print $8"... (3 Replies)
Discussion started by: fongthai
3 Replies

10. Shell Programming and Scripting

reformat the output from "diff" command

Hi all, I use the diff command and got the output: $> diff -e file1.txt file2.txt 15a 000675695 Yi Chen Chen 200520 EASY 50 2/28/05 0:00 SCAD Debit Card Charge . 12a 000731176 Sarah Anderson 200520 EASY 25 2/28/05 0:00 SCAD Debit Card Charge . 11a... (5 Replies)
Discussion started by: CamTu
5 Replies
Login or Register to Ask a Question