Solved

I need help with .ksh script to REORCHK and output only Recommended tables to a file. Anyone have any example to share?

Posted on 2008-06-14
13
400 Views
Last Modified: 2013-12-26
Hi all,
I am comming along with all this Linus/DB2/scripting business...I am no longer scared of it!! (LOL). But,
I need to create a .ksh script that does a REORGCHK and output only tables recommended for reorg.
My goal is to reorgchk and run reorgs based on entries in this reorg file as shown in the example below.
I have tried my hand at the following failing script and hope that gurus here can throw me a lifeline of examples on how to script this. Here is what I tried to do that is failing: (Actually, I found it someplace):
                                       START SHELL CODE
#!/bin/ksh
DBLIST="EMPLOYEE"

for DB_NAME in $DBLIST
do
db2 connect to $DB_NAME
db2 reorgchk current statistics on table all | grep -v SYSIBM | grep '*' | awk '/^DB2INST1/ && NF == 12 { print " REORG TABLE DB2INST1."$2 ";" }' > ReorgTables.sql
/db2home/db2inst1/sqllib/bin/db2 -tvf ReorgTables.sql &
done
exit 0
                               END SHELL CODE
Any help will be highly appreciated...

Thanks


0
Comment
Question by:Enuda
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 6
13 Comments
 
LVL 9

Expert Comment

by:ghostdog74
ID: 21787227
what is it that fails? or doesn't meet your expectation?
0
 

Author Comment

by:Enuda
ID: 21787266
What happens is that when I execute the script posted earlier, I get zero table recommendation. However, when I run a d2mon (a freeware DB2 monitoring tool), I get three tables (RED) flagged as candidates for immediate REORG and several others in YELLOW as potential candidates for REORG. (See attachment).
oughts and help highly appreciated.
So, either the DB2 monitor is wrong or the db2 utility is wrong. Obviously I will like to err on the side of IBM and the db2 reorgchk utility.

Your thoughts? Any help or comment on the script? Any example you can share?
REORG-Recommentations-from--DB2-.doc
0
 
LVL 9

Expert Comment

by:ghostdog74
ID: 21787326
facing such problems, its best to run them one by one on the command line to verify that they work.
does running "db2 reorgchk current statistics on table all" produce any results?
0
Space-Age Communications Transitions to DevOps

ViaSat, a global provider of satellite and wireless communications, securely connects businesses, governments, and organizations to the Internet. Learn how ViaSat’s Network Solutions Engineer, drove the transition from a traditional network support to a DevOps-centric model.

 

Author Comment

by:Enuda
ID: 21787497
Yes, running "db2 reorgchk current statistics on table all" did produce results with some tables flagged with "*" indicating reorg candidates.

There were more indexes flagged for reorg than tables and that by itself is not an issue. Now, the need is to get the code to output only those with '*' to a flat file not all tables and indexes on the database.
Also, it to be able to eliminate certain schemas such as SYSIBM or any other user schema that may be there.

0
 
LVL 9

Expert Comment

by:ghostdog74
ID: 21787851
show the output of db2 reorgchk command, and then describe what you actually wanted the output to be
0
 

Author Comment

by:Enuda
ID: 21787893
Hello again ghostdog74,

Here's a snippet of what "db2 reorgchk current statistics on table all" produces:
$ db2 reorgchk current statistics on table all |more

Table statistics:

F1: 100 * OVERFLOW / CARD < 5
F2: 100 * (Effective Space Utilization of Data Pages) > 70
F3: 100 * (Required Pages / Total Pages) > 80

SCHEMA    NAME                  CARD    OV    NP    FP ACTBLK    TSIZE  F1  F2  F3 REORG
-----------------------------------------------------------------------------------------------------------
DB2ADMIN  AES_HH_MBR_LINKS1     1389     0    80    80      -   306969   0  96 100 ---
Table: DB2ADMIN.AES_HH_MBR_T
DB2ADMIN  AES_HH_MBR_T          3602     0   218   218      -   846470   0  96 100 ---
Table: DB2ADMIN.AES_ICWSIS_ID_PRSN
DB2ADMIN  AES_ICWSIS_ID_PRSN   25372     0  1455  1455      -  5607212   0  95 100 ---
Table: DB2ADMIN.AES_ID_CASE_PRSN_LIST
DB2ADMIN  AES_ID_CASE_PRSN_>   37407     0   430  3419      -  1720722   0  12  12 -**
Table: DB2ADMIN.AES_ID_PRSN
DB2ADMIN  AES_ID_PRSN           6982     0   451   451      -  1752482   0  96 100 ---
Table: DB2ADMIN.AES_ID_SIBLING
DB2ADMIN  AES_ID_SIBLING        2215     0    12    12      -    44300   0  99 100 ---
Table: DB2ADMIN.AES_NULL_ID_PRSN
DB2ADMIN  AES_NULL_ID_PRSN                          104     0     1     1      -     1976   0   -  100 ---
Table: DB2ADMIN.AES_SG_T
DB2ADMIN  AES_SG_T                                   4235     0   138   138      -   512435   0  92 100 ---
Table: DB2ADMIN.AES_SG_WEB_INFO_V
DB2ADMIN  AES_SG_WEB_INFO_V             4235     0   106   106      -   402325   0  95 100 ---
Table: DB2ADMIN.AFCARS_ADPT_CHLDRN
DB2ADMIN  AFCARS_ADPT_CHLDRN               0     0      0     102      -             0   0   0      0 -**
Table: DB2ADMIN.AFCARS_ADPT_EXCPT
DB2ADMIN  AFCARS_ADPT_EXCPT                  0     0     0      108      -             0   0   0   0 -**
Table: DB2ADMIN.AFCARS_EPISODES

What I will like to see as output to the file should only be records with '*' in the REORG column. For example, the file should contain only these rows/records without the numbers and asterisks:
DB2ADMIN.AES_ID_CASE_PRSN_>   37407     0   430  3419             -  1720722   0  12  12 -**  
DB2ADMIN.AFCARS_ADPT_CHLDRN               0     0      0     102      -             0   0   0      0 -**
DB2ADMIN.AFCARS_ADPT_EXCPT                  0     0     0      108      -             0   0   0      0 -**

If you look at thie following attempt, I think you see what I am going after - just the fully qualified table names in the file for those tables identified as needing to be reorged.
I hope I have been able to answer the question...

Thanks


0
 
LVL 9

Expert Comment

by:ghostdog74
ID: 21790422
ok. So just the lines with ** at the end ? here's it
awk '/\*\*$/' outputfile

Open in new window

0
 

Author Comment

by:Enuda
ID: 21790449
How about a complete and working code jus like I tried to do?
0
 
LVL 9

Accepted Solution

by:
ghostdog74 earned 500 total points
ID: 21790862

DBLIST="EMPLOYEE"
 
for DB_NAME in $DBLIST
do
db2 connect to $DB_NAME
db2 reorgchk current statistics on table all | awk '!/SYSIBM/ && /\*\*$/ && /^DB2INST1/ && NF == 12
{
  print " REORG TABLE DB2INST1."$2 ";" > "ReorgTables.sql"
}' 
 
/db2home/db2inst1/sqllib/bin/db2 -tvf ReorgTables.sql &
done

Open in new window

0
 

Author Comment

by:Enuda
ID: 21799156
ghostdog74,
I am not having much luck with this script. I code and run this script and I get all tables: Example:

 REORG TABLE DB2INST1.AAOKEKE.ADVISE_MQT;
 REORG TABLE DB2INST1.ADVISE_MQT;
 REORG TABLE DB2INST1.AAOKEKE.ADVISE_PARTITION;
 REORG TABLE DB2INST1.ADVISE_PARTITION;
 REORG TABLE DB2INST1.AAOKEKE.ADVISE_TABLE;
 REORG TABLE DB2INST1.ADVISE_TABLE;
 .....
The requirement is for only reorgable tables and indexes.

How do I fix this?


0
 
LVL 9

Expert Comment

by:ghostdog74
ID: 21799229
this part of the code

'!/SYSIBM/ && /\*\*$/ && /^DB2INST1/ && NF == 12

says, to find lines without the word SYSIBM and there are two asterixes at the end, and the line starts with DB2INST1 and the number of fields in the line separated by spaces is 12. Make sure the output of command db2 reorgchk current statistics on table all satisfy all these criteria, which in the first place, i picked up from your original post.
0
 

Author Comment

by:Enuda
ID: 21799279
Thank you ghostdod74...very educational. I think I'll need to study the output and determine where the error is comming from...I need to take a nap, I've been up 18, the brain is mashed up!!!

Thank you for your patience...
0
 

Author Closing Comment

by:Enuda
ID: 31467282
Excellent thread and recommendation...thanks
0

Featured Post

MIM Survival Guide for Service Desk Managers

Major incidents can send mastered service desk processes into disorder. Systems and tools produce the data needed to resolve these incidents, but your challenge is getting that information to the right people fast. Check out the Survival Guide and begin bringing order to chaos.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Recently, an awarded photographer, Selina De Maeyer (http://www.selinademaeyer.com/), completed a photo shoot of a beautiful event (http://www.sintjacobantwerpen.be/verslag-en-fotoreportage-van-de-sacramentsprocessie-door-antwerpen#thumbnails) in An…
In the first part of this tutorial we will cover the prerequisites for installing SQL Server vNext on Linux.
Learn how to find files with the shell using the find and locate commands. Use locate to find a needle in a haystack.: With locate, check if the file still exists.: Use find to get the actual location of the file.:
Get a first impression of how PRTG looks and learn how it works.   This video is a short introduction to PRTG, as an initial overview or as a quick start for new PRTG users.

726 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question