find DB2 V 8.1 DB table index by using Toad for DB2

Dear all,

how to find the index of a table by using Toad for DB2 V 5.1 ? anyway to find if the index has been used or hit ?
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenData Warehouse Architect / DBACommented:
Hi Marrowyung,

I'm not aware of anything in DB2 that records index usage.  You can generate an explain plan that will show if DB2 will (or would have) used the index, but nothing records that the index was used.


Kent
0
marrowyungSenior Technical architecture (Data)Author Commented:
" You can generate an explain plan that will show if DB2 will (or would have) used the index, but nothing records that the index was used."

From Toad, how ?

Today I know how to find the index owns by a table already, quick straight forward by Toad.
0
marrowyungSenior Technical architecture (Data)Author Commented:
any method to find the index created size ?
0
Cloud Class® Course: Microsoft Exchange Server

The MCTS: Microsoft Exchange Server 2010 certification validates your skills in supporting the maintenance and administration of the Exchange servers in an enterprise environment. Learn everything you need to know with this course.

Kent OlsenData Warehouse Architect / DBACommented:
I don't know what you mean by "index created size" .  :(
0
marrowyungSenior Technical architecture (Data)Author Commented:
"index created size" means the total size of index once it is created, possible to know ?
0
Kent OlsenData Warehouse Architect / DBACommented:
There is a size estimator in the DB2 Control Center.

For an existing index, just right click on the index name and click on Estimate Size.

For a new index, use the Create Table / Index wizard, enter all of the data for the new index, and click on Estimate Size.

You can download the control center here:

  http://www-01.ibm.com/software/data/ims/imscc/


Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
"For an existing index, just right click on the index name and click on Estimate Size."

from DB2 Control Center. you mean?

the Toad I use can do it but only for paid edition.

Any script based method for me to fund script and find out the result ?
0
Kent OlsenData Warehouse Architect / DBACommented:
I did mean from the DB2 Control Center.  You should be able to download and install it for free.

And I don't know of any external tool to do that.
0
marrowyungSenior Technical architecture (Data)Author Commented:
".  You should be able to download and install it for free"

I tried download the client center, the installation has problem already, cna't see why and it just keep crashing.
0
Kent OlsenData Warehouse Architect / DBACommented:
Where's the crash occurring?  Downloading the Control Center?  Installing it?  Or Running it?
0
marrowyungSenior Technical architecture (Data)Author Commented:
when Installing it/..


so no script anyway ?
0
Kent OlsenData Warehouse Architect / DBACommented:
Very strange.

What's your desktop O/S and version?
0
marrowyungSenior Technical architecture (Data)Author Commented:
Windows 7 32 bits. can see why .
0
marrowyungSenior Technical architecture (Data)Author Commented:
This is the screenshot when I am installing the control client:

IBM CC installation screen.
from the installation log file, it said:

=== Logging started: 10/17/2013  17:27:40 ===
Action 17:27:40: INSTALL. 
Action start 17:27:40: INSTALL.
Action 17:27:40: AppSearch. Searching for installed applications
Action start 17:27:40: AppSearch.
AppSearch: Property: MDAC28IE4SIG, Signature: NewSignature11.E7D6BCFA_9BFA_49D4_A1EE_767B7FD61E38
AppSearch: Property: MDAC28INSTVER, Signature: NewSignature1.E7D6BCFA_9BFA_49D4_A1EE_767B7FD61E38
AppSearch: Property: IE4FOUND, Signature: sigShdocvwISUS
AppSearch: Property: XPFOUND, Signature: sigKernel32
AppSearch: Property: PROP_RSP_DB2_OLEDB_GUID, Signature: PROP_RSP_DB2_OLEDB_GUID
AppSearch: Property: PROP_RSP_DB2_OLEDB_ADVANCED_PAGE_GUID, Signature: PROP_RSP_DB2_OLEDB_ADVANCED_PAGE_GUID
AppSearch: Property: PROP_RSP_DB2_OLEDB_CONNECTION_PAGE_GUID, Signature: PROP_RSP_DB2_OLEDB_CONNECTION_PAGE_GUID
AppSearch: Property: PROP_RSP_DB2_OLEDB_ENUMERATOR_GUID, Signature: PROP_RSP_DB2_OLEDB_ENUMERATOR_GUID
AppSearch: Property: PROP_RSP_DB2_OLEDB_ERROR_LOOKUP_GUID, Signature: PROP_RSP_DB2_OLEDB_ERROR_LOOKUP_GUID
Action ended 17:27:40: AppSearch. Return value 1.
Action 17:27:40: LaunchConditions. Evaluating launch conditions
Action start 17:27:40: LaunchConditions.
Action ended 17:27:40: LaunchConditions. Return value 1.
Action 17:27:40: CheckProductUseJavaCA. 
Action start 17:27:40: CheckProductUseJavaCA.
Action ended 17:27:40: CheckProductUseJavaCA. Return value 1.
Action 17:27:40: SetupInitialization. 
Action start 17:27:40: SetupInitialization.
Info 2898.For Tahoma8 textstyle, the system created a 'Tahoma' font, in 0 character set, of 13 pixels height.
Info 2898.For TahomaBold10 textstyle, the system created a 'Tahoma' font, in 0 character set, of 16 pixels height.
Info 2898.For TahomaBold8 textstyle, the system created a 'Tahoma' font, in 0 character set, of 13 pixels height.
DEBUG: Error 2836:  The control image_noJava on the dialog SetupInitialization can not take focus
Internal Error 2836. SetupInitialization, image_noJava
Action 17:27:40: SetupInitialization. Dialog created
Action ended 17:27:40: SetupInitialization. Return value 1.
Action 17:27:40: FindRelatedProducts. Searching for related applications
Action start 17:27:40: FindRelatedProducts.
Action ended 17:27:40: FindRelatedProducts. Return value 1.
Action 17:27:40: ValidateProductID. 
Action start 17:27:40: ValidateProductID.
Action ended 17:27:40: ValidateProductID. Return value 1.
Action 17:27:40: Check32bitDB2on64bitSystemCA. 
Action start 17:27:40: Check32bitDB2on64bitSystemCA.
Action ended 17:27:41: Check32bitDB2on64bitSystemCA. Return value 1.
Action 17:27:41: CostInitialize. Computing space requirements
Action start 17:27:41: CostInitialize.
Action ended 17:27:41: CostInitialize. Return value 1.
Action 17:27:41: FileCost. Computing space requirements
Action start 17:27:41: FileCost.
Action ended 17:27:41: FileCost. Return value 1.
Action 17:27:41: ResolveSource. 
Action start 17:27:41: ResolveSource.
Action ended 17:27:41: ResolveSource. Return value 1.
Action 17:27:41: setInstallLangCA. 
Action start 17:27:41: setInstallLangCA.
Action ended 17:27:41: setInstallLangCA. Return value 1.
Action 17:27:41: ResetREINSTALLCA. 
Action start 17:27:41: ResetREINSTALLCA.
Action ended 17:27:41: ResetREINSTALLCA. Return value 1.
Action 17:27:41: SetInitialPropertiesCA. 
Action start 17:27:41: SetInitialPropertiesCA.
Action ended 17:27:41: SetInitialPropertiesCA. Return value 1.
Action 17:27:41: StreamLibrarysCA. 
Action start 17:27:41: StreamLibrarysCA.
1: The current directory for the process has now been changed to C:\DOCUME~1\ADMINI~1\LOCALS~1\Temp\1\_DB20000 
Action ended 17:27:43: StreamLibrarysCA. Return value 1.
Action 17:27:43: InitializeURERunTimeLocationCA. 
Action start 17:27:43: InitializeURERunTimeLocationCA.
Action ended 17:27:43: InitializeURERunTimeLocationCA. Return value 1.
Action 17:27:43: StartInstallEventLogCA. 
Action start 17:27:43: StartInstallEventLogCA.
1: EventID=20131017172743 
1: StartInstallEventlog is invoked successfully 
Action ended 17:27:43: StartInstallEventLogCA. Return value 1.
Action 17:27:43: EnableTracingUISequenceCA. 
Action start 17:27:43: EnableTracingUISequenceCA.
Action ended 17:27:44: EnableTracingUISequenceCA. Return value 1.
Action 17:27:44: EnableUREObserversCA. 
Action start 17:27:44: EnableUREObserversCA.
Action ended 17:27:44: EnableUREObserversCA. Return value 1.
Action 17:27:44: setProcessIDOfLauncherCA. 
Action start 17:27:44: setProcessIDOfLauncherCA.
Action ended 17:27:44: setProcessIDOfLauncherCA. Return value 1.
Action 17:27:44: DetectOldDB2ProductsCA. 
Action start 17:27:44: DetectOldDB2ProductsCA.
Action ended 17:27:44: DetectOldDB2ProductsCA. Return value 1.
Action 17:27:44: IsolateComponents. 
Action start 17:27:44: IsolateComponents.
Action ended 17:27:44: IsolateComponents. Return value 1.
Action 17:27:44: CostFinalize. Computing space requirements
Action start 17:27:44: CostFinalize.
Action ended 17:27:44: CostFinalize. Return value 1.
Action 17:27:44: CreateTextExtenderLvLFileCA. 
Action start 17:27:44: CreateTextExtenderLvLFileCA.
1: Successfully created registry entries for selected features. 
Action ended 17:27:44: CreateTextExtenderLvLFileCA. Return value 1.
Action 17:27:44: CommonGuiSilentCA. 
Action start 17:27:44: CommonGuiSilentCA.
Action ended 17:27:44: CommonGuiSilentCA. Return value 1.
Action 17:27:44: setGenericDialogTitlePropertyCA. 
Action start 17:27:44: setGenericDialogTitlePropertyCA.
Action ended 17:27:44: setGenericDialogTitlePropertyCA. Return value 1.
Action 17:27:44: ModifySpecificFeatureConditionsCA. 
Action start 17:27:44: ModifySpecificFeatureConditionsCA.
Action ended 17:27:44: ModifySpecificFeatureConditionsCA. Return value 1.
Action 17:27:44: VerifyPrereqsCA. 
Action start 17:27:44: VerifyPrereqsCA.
Action ended 17:27:45: VerifyPrereqsCA. Return value 1.
Action 17:27:45: WarnUserAboutPreviousProductsCA. 
Action start 17:27:45: WarnUserAboutPreviousProductsCA.
Action ended 17:27:45: WarnUserAboutPreviousProductsCA. Return value 1.
Action 17:27:45: DetectProdForFixpakCA. 
Action start 17:27:45: DetectProdForFixpakCA.
Action ended 17:27:45: DetectProdForFixpakCA. Return value 1.
Action 17:27:45: CheckFixpakLevelCA. 
Action start 17:27:45: CheckFixpakLevelCA.
Action ended 17:27:45: CheckFixpakLevelCA. Return value 1.
Action 17:27:45: CheckForUNCInPathCA. 
Action start 17:27:45: CheckForUNCInPathCA.
Action ended 17:27:45: CheckForUNCInPathCA. Return value 1.
Action 17:27:45: DetectDOTNETEnvCA. 
Action start 17:27:45: DetectDOTNETEnvCA.
1: TRACE_DATA--30: VS80_INSTALLDIR = C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\ 
1: TRACE_DATA--40: VS80_ENVDIR = C:\Program Files (x86)\Microsoft Visual Studio 8\Common7\IDE\ 
Action ended 17:27:45: DetectDOTNETEnvCA. Return value 1.
Action 17:27:45: SetPropertiesForConditionsCA. 
Action start 17:27:45: SetPropertiesForConditionsCA.
Action ended 17:27:45: SetPropertiesForConditionsCA. Return value 1.
Action 17:27:45: SetupRegistryPropertiesCA. 
Action start 17:27:45: SetupRegistryPropertiesCA.
Action ended 17:27:45: SetupRegistryPropertiesCA. Return value 1.
Action 17:27:45: CheckExistInstancesCA. 
Action start 17:27:45: CheckExistInstancesCA.
Action ended 17:27:46: CheckExistInstancesCA. Return value 1.
Action 17:27:46: GetDatabaseObjectsInfoCA. 
Action start 17:27:46: GetDatabaseObjectsInfoCA.
Action ended 17:27:46: GetDatabaseObjectsInfoCA. Return value 1.
Action 17:27:46: CheckControlDBTypeCA. 
Action start 17:27:46: CheckControlDBTypeCA.
Action ended 17:27:46: CheckControlDBTypeCA. Return value 1.
Action 17:27:46: SetARPReadme. 
Action start 17:27:46: SetARPReadme.
Action ended 17:27:46: SetARPReadme. Return value 1.
Action 17:27:46: ChgShortcutFolderCA. 
Action start 17:27:46: ChgShortcutFolderCA.
Action ended 17:27:46: ChgShortcutFolderCA. Return value 1.
Action 17:27:46: SetPrimaryFolderCA. 
Action start 17:27:46: SetPrimaryFolderCA.
Action ended 17:27:46: SetPrimaryFolderCA. Return value 1.
Action 17:27:46: SetARPINSTALLLOCATIONCA. 
Action start 17:27:46: SetARPINSTALLLOCATIONCA.
Action ended 17:27:46: SetARPINSTALLLOCATIONCA. Return value 1.
Action 17:27:46: set_INSTALL_driveCA. 
Action start 17:27:46: set_INSTALL_driveCA.
Action ended 17:27:46: set_INSTALL_driveCA. Return value 1.
Action 17:27:46: AddDefaultLangCA. 
Action start 17:27:46: AddDefaultLangCA.
Action ended 17:27:46: AddDefaultLangCA. Return value 1.
Action 17:27:46: GetAvailableLangsCA. 
Action start 17:27:46: GetAvailableLangsCA.
Action ended 17:27:46: GetAvailableLangsCA. Return value 1.
Action 17:27:46: MigrateFeatureStates. Migrating feature states from related applications
Action start 17:27:46: MigrateFeatureStates.
Action ended 17:27:46: MigrateFeatureStates. Return value 0.
Action 17:27:46: InstallWelcome. 
Action start 17:27:46: InstallWelcome.
Action 17:27:46: InstallWelcome. Dialog created
Action 17:31:00: LAPCheckAndSetCA. 
Action start 17:31:00: LAPCheckAndSetCA.
Action ended 17:31:00: LAPCheckAndSetCA. Return value 1.
Action 17:31:00: CheckUserGroupsCA. 
Action start 17:31:00: CheckUserGroupsCA.
Action ended 17:31:00: CheckUserGroupsCA. Return value 1.
Action 17:31:00: CheckInfoCenterCA. 
Action start 17:31:00: CheckInfoCenterCA.
Action ended 17:31:01: CheckInfoCenterCA. Return value 1.
Info 2898.For MSSWhiteSerif8 textstyle, the system created a 'Tahoma' font, in 0 character set, of 13 pixels height.
Info 2898.For MSSansBold8 textstyle, the system created a 'Tahoma' font, in 0 character set, of 13 pixels height.
Action 17:31:01: LicenseAgreementLAP. Dialog created
Action 17:31:03: InstallTypePlus_0. Dialog created
Action 17:31:05: set_INSTALL_TYPE_propertyCA. 
Action start 17:31:05: set_INSTALL_TYPE_propertyCA.
Action ended 17:31:05: set_INSTALL_TYPE_propertyCA. Return value 1.
Action 17:31:05: reevalFeatureConditionsCA. 
Action start 17:31:05: reevalFeatureConditionsCA.
Action ended 17:31:06: reevalFeatureConditionsCA. Return value 1.
Action 17:31:06: selectLanguageFeaturesCA. 
Action start 17:31:06: selectLanguageFeaturesCA.
Action ended 17:31:07: selectLanguageFeaturesCA. Return value 1.
Action 17:31:07: InstallAction. Dialog created
Action 17:31:09: CustomSetup. Dialog created
Action 17:31:27: CheckInstallDirAttributesCA. 
Action start 17:31:27: CheckInstallDirAttributesCA.
Action ended 17:31:27: CheckInstallDirAttributesCA. Return value 1.
Action 17:31:27: stopThreadsCA. 
Action start 17:31:27: stopThreadsCA.
Action ended 17:31:28: stopThreadsCA. Return value 1.
Action 17:31:28: set_INSTALLDIR_directory. 
Action start 17:31:28: set_INSTALLDIR_directory.
Action ended 17:31:28: set_INSTALLDIR_directory. Return value 1.
Action 17:31:28: set_FILE_property. 
Action start 17:31:28: set_FILE_property.
Action ended 17:31:28: set_FILE_property. Return value 1.
Action 17:31:28: ValidatePanelCA. 
Action start 17:31:28: ValidatePanelCA.
Action ended 17:31:28: ValidatePanelCA. Return value 1.
Action 17:31:28: VerifyOneFeatureSelectedCA. 
Action start 17:31:28: VerifyOneFeatureSelectedCA.
Action ended 17:31:28: VerifyOneFeatureSelectedCA. Return value 1.
Action 17:31:28: DisplayMDACWarningCA. 
Action start 17:31:28: DisplayMDACWarningCA.
Action ended 17:31:28: DisplayMDACWarningCA. Return value 1.
Action 17:31:28: set_INSTALL_driveCA. 
Action start 17:31:28: set_INSTALL_driveCA.
Action ended 17:31:28: set_INSTALL_driveCA. Return value 1.
Action 17:31:28: CheckSelectedFeaturesCA. 
Action start 17:31:28: CheckSelectedFeaturesCA.
Action ended 17:31:28: CheckSelectedFeaturesCA. Return value 1.
Action 17:31:28: CheckProtocolCompsCA. 
Action start 17:31:28: CheckProtocolCompsCA.
Action ended 17:31:29: CheckProtocolCompsCA. Return value 1.
Action 17:31:29: LanguageSelection. Dialog created
Action 17:31:31: stopThreadsCA. 
Action start 17:31:31: stopThreadsCA.
Action ended 17:31:32: stopThreadsCA. Return value 1.
Action 17:31:32: set_INSTALL_driveCA. 
Action start 17:31:32: set_INSTALL_driveCA.
Action ended 17:31:32: set_INSTALL_driveCA. Return value 1.
Action 17:31:32: CustomSetup. Dialog created
Action 17:31:53: CheckInstallDirAttributesCA. 
Action start 17:31:53: CheckInstallDirAttributesCA.
Action ended 17:31:53: CheckInstallDirAttributesCA. Return value 1.
Action 17:31:53: stopThreadsCA. 
Action start 17:31:53: stopThreadsCA.
Action ended 17:31:54: stopThreadsCA. Return value 1.
Action 17:31:54: set_INSTALLDIR_directory. 
Action start 17:31:54: set_INSTALLDIR_directory.
Action ended 17:31:54: set_INSTALLDIR_directory. Return value 1.
Action 17:31:54: set_FILE_property. 
Action start 17:31:54: set_FILE_property.
Action ended 17:31:54: set_FILE_property. Return value 1.
Action 17:31:54: ValidatePanelCA. 
Action start 17:31:54: ValidatePanelCA.
Action ended 17:31:54: ValidatePanelCA. Return value 1.
Action 17:31:54: VerifyOneFeatureSelectedCA. 
Action start 17:31:54: VerifyOneFeatureSelectedCA.
Action ended 17:31:54: VerifyOneFeatureSelectedCA. Return value 1.
Action 17:31:54: DisplayMDACWarningCA. 
Action start 17:31:54: DisplayMDACWarningCA.
Action ended 17:31:54: DisplayMDACWarningCA. Return value 1.
Action 17:31:54: set_INSTALL_driveCA. 
Action start 17:31:54: set_INSTALL_driveCA.
Action ended 17:31:54: set_INSTALL_driveCA. Return value 1.
Action 17:31:54: CheckSelectedFeaturesCA. 
Action start 17:31:54: CheckSelectedFeaturesCA.
Action ended 17:31:55: CheckSelectedFeaturesCA. Return value 1.
Action 17:31:55: CheckProtocolCompsCA. 
Action start 17:31:55: CheckProtocolCompsCA.
Action ended 17:31:55: CheckProtocolCompsCA. Return value 1.
Action 17:31:55: LanguageSelection. Dialog created
Action 17:31:56: CheckInstallDirAttributesCA. 
Action start 17:31:56: CheckInstallDirAttributesCA.
Action ended 17:31:56: CheckInstallDirAttributesCA. Return value 1.
Action 17:31:56: stopThreadsCA. 
Action start 17:31:56: stopThreadsCA.
Action ended 17:31:58: stopThreadsCA. Return value 1.
Action 17:31:58: set_INSTALLDIR_directory. 
Action start 17:31:58: set_INSTALLDIR_directory.
Action ended 17:31:58: set_INSTALLDIR_directory. Return value 1.
Action 17:31:58: set_FILE_property. 
Action start 17:31:58: set_FILE_property.
Action ended 17:31:58: set_FILE_property. Return value 1.
Action 17:31:58: ValidatePanelCA. 
Action start 17:31:58: ValidatePanelCA.
Action ended 17:31:58: ValidatePanelCA. Return value 1.
Action 17:31:58: SetDB2InstanceCA. 
Action start 17:31:58: SetDB2InstanceCA.
Action ended 17:31:58: SetDB2InstanceCA. Return value 1.
Action 17:31:58: GetSelectedLanguagesCA. 
Action start 17:31:58: GetSelectedLanguagesCA.
Action ended 17:31:58: GetSelectedLanguagesCA. Return value 1.
Action 17:31:58: selectLanguageFeaturesCA. 
Action start 17:31:58: selectLanguageFeaturesCA.
Action ended 17:31:59: selectLanguageFeaturesCA. Return value 1.
Action 17:31:59: isNextDlgSummaryCA. 
Action start 17:31:59: isNextDlgSummaryCA.
Action ended 17:31:59: isNextDlgSummaryCA. Return value 1.
Action 17:31:59: set_INSTALL_driveCA. 
Action start 17:31:59: set_INSTALL_driveCA.
Action ended 17:31:59: set_INSTALL_driveCA. Return value 1.
Action 17:32:00: set_INSTALL_NAME_AND_SELECT_propertyCA. 
Action start 17:32:00: set_INSTALL_NAME_AND_SELECT_propertyCA.
Action ended 17:32:00: set_INSTALL_NAME_AND_SELECT_propertyCA. Return value 3.
DEBUG: Error 2896:  Executing action set_INSTALL_NAME_AND_SELECT_propertyCA failed.
Internal Error 2896. set_INSTALL_NAME_AND_SELECT_propertyCA
Action ended 17:32:00: InstallWelcome. Return value 3.
Action 17:32:00: SetupCompleteError. 
Action start 17:32:00: SetupCompleteError.

Open in new window

0
Kent OlsenData Warehouse Architect / DBACommented:
Everything above line 288 looks normal.

The error at lines 288/289 is typical of an installation clashing with a previous installation of a DB2 product.

What other DB2 products are (or were) installed on your desktop?  It could be that there are remnants of an uninstalled product still lying around.
0
marrowyungSenior Technical architecture (Data)Author Commented:
just Toad for DB2, how can we bypass it?
0
Kent OlsenData Warehouse Architect / DBACommented:
Run the Toad uninstaller and try to reinstall.
0
marrowyungSenior Technical architecture (Data)Author Commented:
tried many time, still crash, both on my desktop and a VM client that connectable to the DB2.
0
Kent OlsenData Warehouse Architect / DBACommented:
I'm thinking that there's something being left in the registry that is "confusing" the installer.

Do you have an image that has never had a DB2 related product installed?  I suspect that the installation will work fine there.  It would tell us that the problem is the registry.


Kent
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.