Link to home
Start Free TrialLog in
Avatar of marrowyung
marrowyung

asked on

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 ?
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

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
Avatar of marrowyung
marrowyung

ASKER

" 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.
any method to find the index created size ?
I don't know what you mean by "index created size" .  :(
"index created size" means the total size of index once it is created, possible to know ?
ASKER CERTIFIED SOLUTION
Avatar of Kent Olsen
Kent Olsen
Flag of United States of America image

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
"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 ?
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.
".  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.
Where's the crash occurring?  Downloading the Control Center?  Installing it?  Or Running it?
when Installing it/..


so no script anyway ?
Very strange.

What's your desktop O/S and version?
Windows 7 32 bits. can see why .
This is the screenshot when I am installing the control client:

User generated image
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

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.
just Toad for DB2, how can we bypass it?
Run the Toad uninstaller and try to reinstall.
tried many time, still crash, both on my desktop and a VM client that connectable to the DB2.
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