| Author |
Message
|
| Milesi |
 Posted: Wed Oct 20, 2004 6:51 pm Post subject: db2 rebind vs fmczutil vs fmczrdb |
 |
|
Apprentice
Joined: 15 Apr 2002
Posts: 37
|
Hello,
We currently run the following commands every night:
db2cmd /w /i /c db2 -tvf db2_maintenance.sql
Where db2_maintenance.sql :
force application all;
connect to FMCDB;
reorgchk update statistics on table all;
connect reset;
Followed by:
fmczrdb.exe -o b -y WF_ENV
According to the WF best practice guide, rebind can be achieved using db2 rebind as well as fmczutil using a record file.
What would be the difference between the three methods?
Moreover, the db2 runstats show similar result every night in log files. When checking the documentation, reorgchk checks if tables needs to be reorganized but doesn't seem to perform any operation that will actually reorg the data. The documentation mentions under REORG command:
Tables that have been modified so many times that data is fragmented and access performance is noticeably slow are candidates for reorganization. Use REORGCHK to determine whether a table needs reorganizing. Be sure to complete all database operations and release all locks before invoking REORGANIZE TABLE. This may be done by issuing a COMMIT after closing all cursors opened WITH HOLD, or by issuing a ROLLBACK. After reorganizing a table, use RUNSTATS to update the table statistics, and REBIND to rebind the packages that use this table. The reorganize utility will implicitly close all the cursors.
I am a little confused here, does reorgchk do a reorg + runstats or simply checks if a reorg is necessary without actually doing it?
Should any other operation be added to ensure db2 tables are optimized?
Thanks for the help. |
|
| Back to top |
|
 |
| jmac |
| Posted: Thu Oct 21, 2004 5:21 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
reorgchk does not do a database reorg. Your DBA should be able to tell you if a reorg is necessary.
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| Milesi |
| Posted: Wed Nov 17, 2004 2:34 am Post subject: |
|
|
Apprentice
Joined: 15 Apr 2002
Posts: 37
|
Thank you John for this clarification. What troubles me then is when is a reorg necessary? Is it a necessary maintenance task to perform on DB2?
Such description and explanation are not detailled in the Websphere MQ Worklow Best Practices Guide.
For a clearer understanding, is it true to say that runstats will update the table statistics which are used by the query optimizer when SQL statements are run on a server. Then what is the purpose and use of table and index reorg? |
|
| Back to top |
|
|
| jmac |
| Posted: Wed Nov 17, 2004 5:05 am Post subject: |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
I would expect that your DBA should be able to determine when a database can use a Reorg. Need for reorg would probably be dependent on platform.
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| sac |
| Posted: Mon Dec 20, 2004 10:41 pm Post subject: |
|
|
Apprentice
Joined: 10 Feb 2003
Posts: 44
|
Hi,
This is what's mentioned in the pdf "Considerations for production rollout" for Workflow;
* Execute the following command to create database statistics:
"db2 reorgchk update statistics on table all"
* Execute the following command to rebind all the applications using newly generated statistics:
"fmczrdb -o b -y FMC "
So what i understand from the above is that reorgchk enables creation/generation of DB statistics, against which you rebind DB.
Also other than using fmczrdb command to rebind DB, you can also fmczutil as well for rebinding of DB. |
|
| Back to top |
|
|
| dputzeys |
| Posted: Tue Dec 21, 2004 2:26 am Post subject: can it be done online |
|
|
Novice
Joined: 27 Oct 2004
Posts: 10
|
interesting discussion,
however 3 questions remain open :
1) can I run the "reorgchk update..." command while MQWF is running (and thus applications are using it ) ?
2) can I run the fmczutil,s,r,b (bind packages via fmczutil) while MQWF is running (and thus applications are running it ) ? (the commas indicate menu selections)
3) there is an option in fmczutil, s,r, called "optimize database". Anyone any clue on what this might do/mean, and if so, whether I can run it online (i.e. while applications are connected
rgds
dirkp |
|
| Back to top |
|
|
| jmac |
| Posted: Tue Dec 21, 2004 5:02 am Post subject: Re: can it be done online |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
1) Yes
2) No
3) Don't see this option when I run fmczutil v3.4 on Windows w/DB2 database.
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| dputzeys |
| Posted: Tue Dec 21, 2004 5:22 am Post subject: hmm |
|
|
Novice
Joined: 27 Oct 2004
Posts: 10
|
1) reorgchk : OK thanks, I thought as mutch, but the IBM DB2 manual is not that clear on this issue
2) MQSeries and MQWF need to be running in order to have fmczutil to be able to connect to it. Strange.
I ran the rebind things without any problem from the mentioned menu (up till now). However, I will shut down the applications which use MQWF in the future
3) I use MQWF 3.5.1, and probably this feature has been added in this version. I checked it out on a test machine, and it basically allows you to do the DB2 admin optimisation tasks through fmczutil.
It allows you to:
- reorgchk and rebind
- reorgchk, reorg and rebin
either all MQWF tables or all tables in the system db (e.g. FMCDB).
However, again it is not clear whether you are allowed to do this online or whether you need to shutdown all MQWF using applications
rgds
dirkp |
|
| Back to top |
|
|
| jmac |
| Posted: Tue Dec 21, 2004 5:30 am Post subject: Re: hmm |
|
|
Jedi Knight
Joined: 27 Jun 2001
Posts: 3081
Location: EmeriCon, LLC
|
| dputzeys wrote: |
| 1) MQSeries and MQWF need to be running in order to have fmczutil to be able to connect to it. |
This is not correct. I am not running a 3.5 system at the moment, but I would be shocked if this changed. There is no need to have MQWF running when you run fmczutil. Perhaps you are thinking of fmcautil? Or maybe it's just too early in the morning for me and I am misunderstanding something.
_________________
John McDonald
RETIRED |
|
| Back to top |
|
|
| vennela |
| Posted: Tue Dec 21, 2004 7:04 am Post subject: |
|
|
Jedi Knight
Joined: 11 Aug 2002
Posts: 4055
Location: Hyderabad, India
|
Agree with John on this.
You don't need MQ or WMQ Workflow to be running to run the commands like fmczutil fmcibie fmczrdb fmczzdba etc. These deal with only the database and do not require MQ or Workflow. |
|
| Back to top |
|
|
| sshaker |
| Posted: Thu Jan 13, 2005 12:32 pm Post subject: |
|
|
Disciple
Joined: 20 Sep 2002
Posts: 185
|
This is an interesting discussion and I am sure we all get into these issues frequently. The answer requires a DB2 + OS + WF expert to be perfect and I am none of them. The following sequence may help add to the discussion:
0. stopwf: bring down the workflow servers
1. reorgchk: this tells us the 'current' (may change in due course) status of the database; using the results identify the 'culprit' or the candidates for reorg
2. reorg: do a reorg using a 'proper' index; the reorgchk may say something but sometimes we may have to use our 'wfsense' and identify the 'best' index to use - what queries we make most and what indexes they need
3. runstats: are recommended after reorg
4. rebind: i prefer to use fmczutil to rebind at the end
5. startwf: now i am ready for the next run
I come across people saying that why IBM does not give us a 'script' to take care of this. But I dont think it is that simple as I mentioned earlier selecting the right index may not be same or easy everytime. We can automate this process to some extent but an intervention is suggested.
If somebody can through more light (what it is doing in the background, any script it is using, the logic used etc.) on wf3.5's new features that will be helpful.
Looking forward for comments and others' experiences
regards
_________________
shaker |
|
| Back to top |
|
|
|
|
