COGNOiSe.com - The IBM Cognos Community

ETL, App Development and other tools => COGNOS DecisionStream/Data Manager => Topic started by: camboo on 14 Apr 2014 08:15:00 AM

Title: Analyzing Database tables by using DataManager?
Post by: camboo on 14 Apr 2014 08:15:00 AM
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 :)
Title: Re: Analyzing Database tables by using DataManager?
Post by: nblank on 14 Apr 2014 08:46:53 AM
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