If you are unable to create a new account, please email support@bspsoftware.com

 

Analyzing Database tables by using DataManager?

Started by camboo, 14 Apr 2014 08:15:00 AM

Previous topic - Next topic

camboo

Hello,

How it is possible to run analyze command for database tables in DataManager 10.2 jobstreams? Our DM catalog seems to do that trick somehow, but I cannot find the place where that is implemented...?

All tips are welcome :)

nblank

By my knowledge this is not done by data manager itself. In our case we create Procedure Nodes for this:

sql('<connection>','begin DBMS_STATS.GATHER_TABLE_STATS(''<schema>'',''<table_name>'',ESTIMATE_PERCENT=>DBMS_STATS.AUTO_SAMPLE_SIZE,method_opt=>(''for all indexed columns size auto''),CASCADE=>TRUE);end;')

It is also possible that the database itself (in our case Oracle 10g) runs update statistics on tables that changed a lot (insert/deleted data).

Perhaps what you are looking for is explained in the IBM Data Manager User Guide (10.1) on page 60:

STATISTICS [ON|OFF]
Determines whether timing statistics are returned after exe-
cuting each SQL statement. This command is particularly
useful in determining SQL statement efficiency.

Regards,

Nanno