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
376 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
  • 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
 

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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
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

Do You Know the 4 Main Threat Actor Types?

Do you know the main threat actor types? Most attackers fall into one of four categories, each with their own favored tactics, techniques, and procedures.

Join & Write a Comment

Over the last ten+ years I have seen Linux configuration tools come and go. In the early days there was the tried-and-true, all-powerful linuxconf that many thought would remain the one and only Linux configuration tool until the end of times. Well,…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
Learn how to get help with Linux/Unix bash shell commands. Use help to read help documents for built in bash shell commands.: Use man to interface with the online reference manuals for shell commands.: Use man to search man pages for unknown command…
This demo shows you how to set up the containerized NetScaler CPX with NetScaler Management and Analytics System in a non-routable Mesos/Marathon environment for use with Micro-Services applications.

747 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now