You will have to monitor it and manually do it of launching from the
command line. (You need a runstats after each table reorg to get new
query plans).
Or
Here is some script code to help you start building a script. The red is
just my comments. There is a lot of other code necessary to manage this
but once it is done, it works great.
DB_CHECK_DONE()
{
index=${1} An array pointer with a table name like
someschemaname.sometablename
tabName=`echo "${TableArray[${index}]}" | cut -f 2 -d "."`
db2 -x "select REORG_STATUS,REORG_END from
table(sysproc.snapshot_tbreorg('${Database}',-1)) as tb \
where TABLE_SCHEMA = '${Schema}' and TABLE_NAME = '${tabName}'
and \
REORG_START >= '${StartArray[${index}]}' FOR FETCH ONLY WITH UR"
> ${TempFile}.history I kept track of the time I started the reorg into
an array
read -r status endTime < ${TempFile}.history
if [[ ${status} == "" ]]; then
let "CkTableArray[${index}] = ${CkTableArray[${index}]} + 1"
fi
if (( ${CkTableArray[${index}]} > 10 )); then
return 2 # Never started I have been sleeping at least 5 second but
db2 may not start right away.
fi
if [[ ${status} == "" ]]; then
return 0
fi
case ${status} in
1 ) return 0;; # running;;
2 ) return 3;; # paused;;
3 ) return 4;; # stopped;;
# 4 ) this is a completed status and does not return
5 ) return 0;; # running on truncate;;
esac
return code of 4 I get here an I am done to launch the runstats and the
next table.
EndArray[${index}]=`echo ${endTime} | cut
-c1-4,6-7,9-10,12-13,15-16,18-19`
rm ${TempFile}.history
return 1
}
What a pain........................................
Craig Lavezzo
California Child Support Automation System (CCSAS)
Phone: (916) 868-0519
Email: craig.[Email address protected]
-----Original Message-----
From: quigleyd
[mailto:db2udbdba-ezmlmshield-x41388309.[Email address protected]
Sent: Monday, October 29, 2007 8:12 AM
To: LazyDBA Discussion
Subject: inpace reorg
I am doing inplace re-orgs with the following command:
db2 'reorg table <schema.table> inplace
The issue is, if put allot of them in a script, it runs all of them at
once, I
want it to wait until the previous one is finsihed. How can i get it
to wait?
----------------------------------------------------------------
This message was sent using IMP, the Internet Messaging Program.
---------------------------------------------------------------------
TO REPLY TO EVERBODY , PLEASE CLICK REPLY-ALL, NOT JUST REPLY
To post a dba job: http://jobs.lazydba.com
To Subscribe : http://www.LazyDBA.com
To unsubscribe: http://www.lazydba.com/unsubscribe.html
________________________________________________________________________________________________________________
CONFIDENTIALITY NOTICE: This email from the State of California is for the sole use of the intended recipient and may contain confidential and privileged information. Any unauthorized review or use, including disclosure or distribution, is prohibited. If you are not the intended recipient, please contact the sender and destroy all copies of this email.
DB2 & UDB email list listserv db2-l LazyDBA home page