473,719 Members | 2,033 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

Need help w/.ksh script to REORCHK & output Recommended tables to a file

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="EMPLOYE E"

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

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

Jun 27 '08 #1
16 4850


Okonita via DBMonster.com wrote:
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
In what way does it fail?
/Lennart
>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="EMPLOYE E"

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

--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1
Jun 27 '08 #2
Hi,
It fails to select tables that certainly needs to be reorged - output file is
empty. I know this because when I run "db2 reorgchk current statistics on
table all", the list shows several tables and indexes that has '*' in either
F1 thru F8.

So, that leads to believe I am doing something wrong but what I don't know...
All I am looking for is the correct syntax for executing reorgcheck, grep '*'
and awk/print the find to the output file that I specify.

Can you help with the correct shell script? Will be much obliged...

Thanks
Lennart wrote:
>Hi all,
I am comming along with all this Linus/DB2/scripting business...I am no
[quoted text clipped - 4 lines]
>shown in the example below.
I have tried my hand at the following failing script

In what way does it fail?

/Lennart
>>and hope that gurus here
can throw me a lifeline of examples on how to script this. Here is what I
[quoted text clipped - 22 lines]
>Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

Jun 27 '08 #3
Okonita via DBMonster.com wrote:
#!/bin/ksh
DBLIST="EMPLOYE E"

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
REORGCHK, at least on my 9.5 FP1 box, outputs the table name and its
statistics on /separate/ lines. So simply grepping for *'s is not going
to work. It'd be possible to bodge something together with awk, but why
not do this properly ... ish ... ;-)

The following's got a lot more comments in it than I'd normally bother
with, but it should be pretty obvious as a result. Couple of
differences to the original script - the actual bit that reorg's the
tables is currently commented out. Test it, then uncomment it.
Secondly, I haven't bothered filtering the SYSIBM tables - after all,
why not reorg them too if they need it?

Also, the bit for reorg'ing the tables isn't set to do it in parallel
in the background (the trailing & in your original script). Unless
you've got an array with /lots/ of drives, I doubt you'll gain much by
trying to do it in parallel (which is not mention the problem with
using the same continually rewritten source script with multiple
background processes in your original script ;-).

Anyway - here's the script (it's Python: if this is a Linux box, you've
likely already got it, if it's some flavour of UNIX it shouldn't be
difficult to install from the source tarball).

#!/usr/bin/env python

from subprocess import Popen, PIPE, STDOUT

# List of database names to REORGCHK
databases = ['BOBIW']

for database in databases:
# Generate the SQL to run through thte CLP
sql = 'CONNECT TO %s;\n' % database
sql += 'REORGCHK CURRENT STATISTICS ON TABLE ALL;\n'
# Each element is a separate parameter
cmdline = ['db2', '-t', '+p']
# Start the CLP redirecting stdin et al. to pipes
p = Popen(cmdline, stdin=PIPE, stdout=PIPE, stderr=STDOUT)
# Pass the SQL to the CLP, read the output (the method returns
# a tuple of stdout, stderr but we've redirected stderr to
# stdout, so only grab the first element)
output = p.communicate(s ql)[0]
# Parse the output
table = None
reorg = []
for line in output.split('\ n'):
# Terminate once we reach index stats
if line == 'Index statistics:':
break
if line.startswith ('Table:'):
# Grab the table name (7th character onwards on lines
# beginning with 'Table:'
table = line[7:]
elif table:
# Check the REORG column (last 3 chars on lines
# immediately following a 'Table:' line). If a * is
# present, add it to the reorg list
if '*' in line[-4:]:
reorg.append(ta ble)
table = None
# Loop through the reorg list running the CLP with a REORG
# command for each table
for table in reorg:
print 'Reorganizing table %s' % table
# Same as above, run the CLP with pipes for std handles,
# pass the REORG command and print the output
#sql = 'CONNECT TO %s;\n' % database
#sql += 'REORG TABLE %s;\n' % table
#p = Popen(cmdline, stdin=PIPE, stdout=PIPE, stderr=STDOUT)
#print p.communicate(s ql)[0]
Cheers,

Dave.
Jun 27 '08 #4
Ian
Okonita via DBMonster.com wrote:
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.
[...]
Any help will be highly appreciated...
Parsing REORGCHK output makes your job a lot harder, now that you can
use the REORGCHK_TB_STA TS and REORGCHK_IX_STA TS stored procedures.


Jun 27 '08 #5
lan,
Ok, but how about an example how to do that? I have problem because I am
relatively new at writing scripts and can use a working example...

Thanks

Ian wrote:
>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.
[...]
Any help will be highly appreciated...

Parsing REORGCHK output makes your job a lot harder, now that you can
use the REORGCHK_TB_STA TS and REORGCHK_IX_STA TS stored procedures.
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

Jun 27 '08 #6
Dave,
Thanks for the example. I am new to shell scripting and Linux. I am only able
to deal with simple code at this time and your script eample is a little bit
complicated for me.

If you have someting simpler like I have in the initial post and strictly
kshell, I'll appreciate it. Later, I will have time to read about "python"
and learn about your code/script.

Please help if you can..

Thanks

Dave Hughes wrote:
>#!/bin/ksh
DBLIST="EMPLOY EE"
[quoted text clipped - 8 lines]
>done
exit 0

REORGCHK, at least on my 9.5 FP1 box, outputs the table name and its
statistics on /separate/ lines. So simply grepping for *'s is not going
to work. It'd be possible to bodge something together with awk, but why
not do this properly ... ish ... ;-)

The following's got a lot more comments in it than I'd normally bother
with, but it should be pretty obvious as a result. Couple of
differences to the original script - the actual bit that reorg's the
tables is currently commented out. Test it, then uncomment it.
Secondly, I haven't bothered filtering the SYSIBM tables - after all,
why not reorg them too if they need it?

Also, the bit for reorg'ing the tables isn't set to do it in parallel
in the background (the trailing & in your original script). Unless
you've got an array with /lots/ of drives, I doubt you'll gain much by
trying to do it in parallel (which is not mention the problem with
using the same continually rewritten source script with multiple
background processes in your original script ;-).

Anyway - here's the script (it's Python: if this is a Linux box, you've
likely already got it, if it's some flavour of UNIX it shouldn't be
difficult to install from the source tarball).

#!/usr/bin/env python

from subprocess import Popen, PIPE, STDOUT

# List of database names to REORGCHK
databases = ['BOBIW']

for database in databases:
# Generate the SQL to run through thte CLP
sql = 'CONNECT TO %s;\n' % database
sql += 'REORGCHK CURRENT STATISTICS ON TABLE ALL;\n'
# Each element is a separate parameter
cmdline = ['db2', '-t', '+p']
# Start the CLP redirecting stdin et al. to pipes
p = Popen(cmdline, stdin=PIPE, stdout=PIPE, stderr=STDOUT)
# Pass the SQL to the CLP, read the output (the method returns
# a tuple of stdout, stderr but we've redirected stderr to
# stdout, so only grab the first element)
output = p.communicate(s ql)[0]
# Parse the output
table = None
reorg = []
for line in output.split('\ n'):
# Terminate once we reach index stats
if line == 'Index statistics:':
break
if line.startswith ('Table:'):
# Grab the table name (7th character onwards on lines
# beginning with 'Table:'
table = line[7:]
elif table:
# Check the REORG column (last 3 chars on lines
# immediately following a 'Table:' line). If a * is
# present, add it to the reorg list
if '*' in line[-4:]:
reorg.append(ta ble)
table = None
# Loop through the reorg list running the CLP with a REORG
# command for each table
for table in reorg:
print 'Reorganizing table %s' % table
# Same as above, run the CLP with pipes for std handles,
# pass the REORG command and print the output
#sql = 'CONNECT TO %s;\n' % database
#sql += 'REORG TABLE %s;\n' % table
#p = Popen(cmdline, stdin=PIPE, stdout=PIPE, stderr=STDOUT)
#print p.communicate(s ql)[0]

Cheers,

Dave.
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

Jun 27 '08 #7
Okonita via DBMonster.com wrote:
Dave,
Thanks for the example. I am new to shell scripting and Linux. I am
only able to deal with simple code at this time and your script
eample is a little bit complicated for me.

If you have someting simpler like I have in the initial post and
strictly kshell, I'll appreciate it. Later, I will have time to read
about "python" and learn about your code/script.

Please help if you can..
"Strictly" ksh? What's strictly? No grep? No awk? I assume not as you
used them in the original ;-) Anyway...

#!/bin/ksh

DBLIST="EMPLOYE E"

rm -f reorgscript.sql
touch reorgscript.sql

for DBNAME in $DBLIST; do
echo "CONNECT TO $DBNAME;" >reorgscript.sq l
db2 "CONNECT TO $DBNAME"
db2 -x "CALL SYSPROC.REORGCH K_TB_STATS('T', 'ALL')" | grep "\*" | awk
'{print "REORG TABLE " $1 "." $2 ";"}' >reorgscript.sq l
echo "CONNECT RESET;" >reorgscript.sq l
done

db2 -tvf reorgscript.sql
That should do the trick. Again, I haven't bothered filtering out
SYSIBM tables, or running reorgs in background tasks. It just builds a
script of everything that needs reorg'ing and then runs it.
Cheers,

Dave.
Jun 27 '08 #8
Hi Dave,

Yep, your code did the trick for tables by outputing just those tables that I
know for a fact needed reorg.

Not to dampen the spirit, I did notice that when I run the same code for
indexes, I get tables as entries instead of indexes. Here is what I did with
the code for indexes:

DBLIST="FSFNDM0 1"
rm -f reorgscript.sql
touch reorgscript.sql
for DBNAME in $DBLIST; do
echo "CONNECT TO $DBNAME;" >reorgscript_ix .msg
db2 "CONNECT TO $DBNAME"
db2 -x "CALL SYSPROC.REORGCH K_IX_STATS('S', 'DB2ADMIN')" | grep "\*" | awk '
{print "REORG INDEX " $1 "." $2 ";"}' >Reorgscript_In dexes.sql
echo "CONNECT RESET;" >reorgscript_ix .msg
done

The result show tables being outputed:

REORG INDEX DB2ADMIN.PAYR;
REORG INDEX DB2ADMIN.PAYR;
REORG INDEX DB2ADMIN.PAYR;
REORG INDEX DB2ADMIN.ALERT;
REORG INDEX DB2ADMIN.ALERTS _HISTORY;
REORG INDEX DB2ADMIN.CASE;
REORG INDEX DB2ADMIN.CASE_H ISTORY;
REORG INDEX DB2ADMIN.ELIGIB ILITY;
REORG INDEX DB2ADMIN.ELIGIB ILITY;
REORG INDEX DB2ADMIN.LOCATI ON_ENTITIES;
REORG INDEX DB2ADMIN.LOCATI ON_ENTITIES;
REORG INDEX DB2ADMIN.LOCATI ON_ENTITIES;
REORG INDEX DB2ADMIN.LOCATI ON_ENTITIES;
REORG INDEX DB2ADMIN.TRANS_ EVENTS;
REORG INDEX DB2ADMIN.TRANS_ EVENTS;
REORG INDEX DB2ADMIN.TRANS_ EVENTS;
REORG INDEX DB2ADMIN.TRANS_ EVENTS;
REORG INDEX DB2ADMIN.TRANS_ EVENTS;
REORG INDEX DB2ADMIN.TRANS_ EVENTS;
REORG INDEX DB2ADMIN.TRANS_ EVENTS;

All indexes in our system starts with 'X', for example XP1TRANS_EVENTS for
index on TRANS_EVENTS.

How can I fix this?

Thanks

Dave Hughes wrote:
>Dave,
Thanks for the example. I am new to shell scripting and Linux. I am
[quoted text clipped - 6 lines]
>>
Please help if you can..

"Strictly" ksh? What's strictly? No grep? No awk? I assume not as you
used them in the original ;-) Anyway...

#!/bin/ksh

DBLIST="EMPLOY EE"

rm -f reorgscript.sql
touch reorgscript.sql

for DBNAME in $DBLIST; do
echo "CONNECT TO $DBNAME;" >reorgscript.sq l
db2 "CONNECT TO $DBNAME"
db2 -x "CALL SYSPROC.REORGCH K_TB_STATS('T', 'ALL')" | grep "\*" | awk
'{print "REORG TABLE " $1 "." $2 ";"}' >reorgscript.sq l
echo "CONNECT RESET;" >reorgscript.sq l
done

db2 -tvf reorgscript.sql

That should do the trick. Again, I haven't bothered filtering out
SYSIBM tables, or running reorgs in background tasks. It just builds a
script of everything that needs reorg'ing and then runs it.

Cheers,

Dave.
--
Message posted via DBMonster.com
http://www.dbmonster.com/Uwe/Forums....m-db2/200806/1

Jun 27 '08 #9
Ian
Okonita via DBMonster.com wrote:
Thank you lan. $3 and $4 did out only indexes and thats what I was looking
for.

Your last post certainly very educational about the way this tool/reorging
works. So, if both the table and indexes need reorging, doing reorg on the
table alone is sufficient?
Yes. Let me be clear though: REORG TABLE X does a "classic" (offline)
reorganization of the table, and all indexes are rebuilt as a part of
this.

REORG TABLE X INPLACE does an online reorg of the table. In this case,
indexes are not automatically rebuilt (they indexes are just updated as
RIDs change). So this is a case where an index reorg might help.

How about where only indexes need reorg? If I reorg the index alone, how does
that affect the table that the index is based on?
Reorganizing an index has no effect on the base table.
Jun 27 '08 #10

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

3
3862
by: D | last post by:
Database 1 is corrupted. Database 2 was created by dba but none of the primary or foreign key constraints were ported over. TOAD won't let me export. I will try ErWin next. What is the best way to recover this information if the views are corrupted and through errors? thanks, Lowly developer
13
4442
by: Greg Strong | last post by:
Hello All, Hello All, What are the ToDo's / Gotchas to convert an MDB Access 2K2 database to an Access Project (i.e. ADP) file for the front end using Microsoft SQL Server 2000 Desktop Engine (MSDE 2000) for the back end? Now for the background. I have a prototype MDB file that was built in Access 2K2, and compiled in Access 2K to provide backward
0
2257
by: ward | last post by:
Greetings. Ok, I admit it, I bit off a bit more than I can chew. I need to complete this "Generate Report" page for my employer and I'm a little over my head. I could use some additional assistance. I say additional because I've already had help which is greatly appreciated. I do try to take the time and understand the provided script in hopes on not having to trouble others on those. But here it goes...
4
3755
by: benwylie | last post by:
I am running IIS 6.0 on Windows 2003. I would like to be able to run a perl script from a web page and include the output. I have tried doing it with an ssi: <form action='docsearch.shtml' method='get'> <!--#exec cgi="/cgi-bin/docsearch.pl--> </form> This correctly ran the script, but it was unable to include the
1
4767
by: Osoccer | last post by:
...to a different folder and in the relocated file concatenates all of the lines in one long string with a space between each line element. Here is a fuller statement of the problem: I need a Visual Basic Script file, call it "Move and Reformat Text File.VBS," that will run from a Windows Script Host command-prompt-based version as follows: C:\> Cscript.exe "Move and Reformat Text File.VBS" The objective of the VBScript file, "Move...
12
3009
by: adamurbas | last post by:
ya so im pretty much a newb to this whole python thing... its pretty cool but i just started today and im already having trouble. i started to use a tutorial that i found somewhere and i followed the instructions and couldnt get the correct results. heres the code stuff... temperature=input("what is the temperature of the spam?") if temperature>50: print "the salad is properly cooked." else:
5
3289
by: camphor | last post by:
hi, I have found an upload script in hotscripts and have implemented it into the website, I followed the installation steps to 'give write permissions to php on the upload folder (which is _uploadedfiles_xxxx) (php must be allowed to move uploaded files to this folder' - uploadedfiles_xxxx. I typed <?php chmod ('_uploadedfiles_xxxx',640); ?> into notepad and saved it as php in the uploaded_xxxx folder, when I went to test it, the error...
3
5190
by: aRTx | last post by:
I have try a couple of time but does not work for me My files everytime are sortet by NAME. I want to Sort my files by Date-desc. Can anyone help me to do it? The Script <? /* ORIGJINALI
0
8828
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...
0
8725
by: Hystou | last post by:
Most computers default to English, but sometimes we require a different language, especially when relocating. Forgot to request a specific language before your computer shipped? No problem! You can effortlessly switch the default language on Windows 10 without reinstalling. I'll walk you through it. First, let's disable language synchronization. With a Microsoft account, language settings sync across devices. To prevent any complications,...
0
9360
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
9208
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
9123
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
9053
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7993
agi2029
by: agi2029 | last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own.... Now, this would greatly impact the work of software developers. The idea...
0
4481
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
3
2124
bsmnconsultancy
by: bsmnconsultancy | last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.