All Forums Database
v_gabrielli 33 posts Joined 04/10
18 Dec 2014
create a composite Collect stats command

SELECT  databasename,tablename,indexnumber,columnName, columnPosition,
'COLLECT STATISTICS ON ' || TRIM(databasename)   ||'.'||   TRIM(tablename) ||' INDEX (' || TRIM(columnName) || ');' 
FROM dbc.indices
WHERE indextype in ('P','S')
    AND databasename='MENSA_TABLE'
   AND tablename='TLOCATION_HIST'
ORDER BY indexnumber,columnposition;
Output:

 

            

TLOCATION_HIST               

4

EXPY_DT                      

1

COLLECT STATISTICS ON MENSA_TABLE.TLOCATION_HIST INDEX (EXPY_DT);

3

MENSA_TABLE                  

TLOCATION_HIST               

8

EFF_DT                        

1

COLLECT STATISTICS ON MENSA_TABLE.TLOCATION_HIST INDEX (EFF_DT);

4

MENSA_TABLE                  

TLOCATION_HIST               

8

EXPY_DT                      

2

COLLECT STATISTICS ON MENSA_TABLE.TLOCATION_HIST INDEX (EXPY_DT);

5

MENSA_TABLE                  

TLOCATION_HIST               

12

LOCATION_ID                  

1

COLLECT STATISTICS ON MENSA_TABLE.TLOCATION_HIST INDEX (LOCATION_ID);

 

 

 

 

 

 

 

 

 

 

 

 

 

 

Lines 3,4 need to be

merged together

 

 

 

 

3

MENSA_TABLE                  

TLOCATION_HIST               

8

EFF_DT                       

1

COLLECT STATISTICS ON MENSA_TABLE.TLOCATION_HIST INDEX (EFF_DT,EXPY_DT);

4

MENSA_TABLE                   

TLOCATION_HIST               

8

EXPY_DT                      

2

 

 
… does anyone know how to do this or does anyone have a script that will compose the correct Collect statistics command .. using composite index where applicable.

Vince
Rohan_Sawant 55 posts Joined 07/14
19 Dec 2014

Hi v_gabrielli,
The following recursive query will do the task. Enter your filter. If your tables have keywords as columns use "SYSLIB.SQLRESTRICTEDWORDS" and put " around keywords.

WITH RECURSIVE CREATE_COLLECT_STATS
(
	DATABASENAME
,	TABLENAME
,	COLUMNPOSITION
,	COLLECT_STATS
)
AS
(
	SELECT 
		DATABASENAME
	,	TABLENAME
	,	COLUMNPOSITION
	,	CAST(TRIM(COLUMNNAME) AS VARCHAR(1000)) AS COLLECT_STATS
	FROM 
		DBC.INDICES
	WHERE
		COLUMNPOSITION = 1
	UNION ALL
	SELECT 
		A.DATABASENAME
	,	A.TABLENAME
	,	B.COLUMNPOSITION
	,	A.COLLECT_STATS || ',' || TRIM(B.COLUMNNAME)
	FROM 
		CREATE_COLLECT_STATS A
	JOIN 
		DBC.INDICES B
	ON	A.DATABASENAME = B.DATABASENAME
	AND A.TABLENAME = B.TABLENAME
	AND A.COLUMNPOSITION + 1 = B.COLUMNPOSITION
)
SELECT
	A.DATABASENAME
,	A.TABLENAME
,	'COLLECT STATISTICS ON ' || UPPER(TRIM(A.DATABASENAME))   ||'.'||   UPPER(TRIM(A.TABLENAME)) ||' INDEX (' || UPPER(TRIM(A.COLLECT_STATS)) || ');' AS COLLECT_STATS_STATEMENT
FROM
    CREATE_COLLECT_STATS A
INNER JOIN
(
    SELECT 
        DATABASENAME
 	,	TABLENAME
    ,   MAX(COLUMNPOSITION) AS MAX_COLUMNPOSITION
    FROM
        CREATE_COLLECT_STATS
    GROUP BY 1,2
) B
ON	A.DATABASENAME = B.DATABASENAME
AND A.TABLENAME = B.TABLENAME
AND A.COLUMNPOSITION = B.MAX_COLUMNPOSITION;

Thanks & Regards,

Rohan Sawant

v_gabrielli 33 posts Joined 04/10
19 Dec 2014

Hi Rohan, thank you for the reply.. much appreciated.. however

your script produces this with extra fileds and the wrong order indexOrder is not used

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (BRAND_ID,EXPY_DT);

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (ACCOUNT_ID,EXPY_DT);

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (EXPY_DT,EXPY_DT);

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (BLG_LOCATION_ID,EXPY_DT);

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (ACCOUNT_KEY,EXPY_DT);

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (STATUS_ID,EXPY_DT);

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (EFF_DT,EXPY_DT);

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (BLG_PARTY_ID,EXPY_DT);

 

what I need is this... in this order

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (ACCOUNT_KEY);

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (EXPY_DT);

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (EFF_DT,EXPY_DT);

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (ACCOUNT_ID);

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (STATUS_ID);

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (BLG_LOCATION_ID);

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (BLG_PARTY_ID);

COLLECT STATISTICS ON MENSA_TABLE.TACCOUNT_HIST INDEX (BRAND_ID);

pls let me know Vince

Vince

Rohan_Sawant 55 posts Joined 07/14
20 Dec 2014

Hi,

 

Can you please share your table "MENSA_TABLE.TACCOUNT_HIST INDEX " DDL so that I could map your requirement with DDL. I thought you just want to know how to combine rows. 

 

Thanks,

Rohan Sawant

dnoeth 4628 posts Joined 11/04
22 Dec 2014

Hi Vince/Rohan, 
you need to add IndexNumber to the join condition, too.
 
Btw, you can also utilize my StatsInfo view for creating COLLECT STATS on indexes:
pre-TD14: How to decode the binary statistics stored in dbc tables
TD14: New StatsInfo query for TD14
The new version for TD14 doesn't include missing index stats anymore, but you can borrow the code from the old one :-) 

Dieter

You must sign in to leave a comment.