All Forums Database
Sarah_07 19 posts Joined 05/13
28 Sep 2014
Finding PI for a table created already

Hi All,
I have tables which are already created in DB. Almost half the tables are having more skewness. Now, I need to change PI for those tables which are having high skewness. Instead of checking each and every combination of columns manually, is there any way to check them at a stretch? I have nearly 200 tables.
Any help would be appreciated. Thanks in advance.
 
 
 

Thanks, Sarah
SAP 73 posts Joined 08/14
29 Sep 2014

Even , i have 400 tables with same sceanrio . Someone pls help !!!

SAP

Raja_KT 1246 posts Joined 07/09
29 Sep 2014

It seems you are aware of the query  to find skewness, so I will not re-iterate it.
How skewed is your data?
I m not sure if there is any tool that gives those details. However, if it is me,  I will write an automation script based on the query HASHAMP ...HASHBUCKET...HASHROW on IDs(hoping that data modeler has toed the line of proper naming convention :)), reading for each column and or combination of columns.
From performance point of view, maybe you can see TD Index wizard, because you may think of partitioning, STJI, SI but not PI.

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

ulrich 816 posts Joined 09/09
29 Sep 2014

The PI has two purposes:
1. access and join support
2. distribution
A good PI will serve BOTH purposes and using a automated procedure which focus on distribution will often not result in best perfromance.
PI selection is one mayor part of the physical data modeling!
 

feel free to donate bitcoin:12kgAUHFUqvG2sQgaRBXFhCwyf9HXdkGud

frnewbrough 41 posts Joined 03/08
29 Sep 2014

What you need to do is collect data demographics and factor in how the table is being queried/used. I've found that in most cases choosing a unique pi is beneficial but this is by no means always the case. I can envison writing an automated tool to give you the most unique combination of columns though I'm not aware of one. 
I did write a simple stored proc to make it easier to alter the primary index columns and modify the index to be unique when I had a situation where I needed make a mass change of PIs to tables. I determined the correct unique PIs and then used a spreadsheet to generate the procedure calls.

REPLACE PROCEDURE ALTER_PI_UNIQUE

(

    IN DB VARCHAR(30),

    IN TB VARCHAR(30),

    IN INDEX_COLS VARCHAR(2000),

    OUT ReturnCode INTEGER

)

-----------------------------------------------------------------------------------------------------------------------------------------------------------

-- Procedure Name        : ALTER_PI_UNIQUE

-- Purpose               : To alter the primary index columns and or whether the index is unique.

-- Version               : V 1.00

-- Create Date           : 2014-04-17

-- Created by            : Fred Newbrough

-----------------------------------------------------------------------------------------------------------------------------------------------------------

-- Parameters     : DB = Database Name

--                       : TB = Table Name   

--                       : INDEX_COLS = INDEX Columns (can be null) otherwise will change the index to match passed columns.                                   

--                       : ReturnCode  =Indicates run status 0 indicates success.

-----------------------------------------------------------------------------------------------------------------------------------------------------------

-- Change History       

-----------------------------------------------------------------------------------------------------------------------------------------------------------

  --======================   STORED PROCEDURE CODE STARTS HERE

 

BEGIN

      

    DECLARE TEMP_TBL_NM VARCHAR(30);

    DECLARE v_sql_stmt VARCHAR(2000);

 

SET TEMP_TBL_NM=SUBSTRING( TB FROM 1 FOR 19)||(SESSION (FORMAT '9(11)') (CHAR(11)));

 

SET v_sql_stmt ='CREATE TABLE '|| TRIM(DB) || '.' || TEMP_TBL_NM || ' AS '|| TRIM(DB) ||'.'|| TRIM(TB)||'  WITH NO DATA;';

 

CALL DBC.SYSEXECSQL(v_sql_stmt);

 

 IF COALESCE(INDEX_COLS,'') = '' THEN

 SET v_sql_stmt ='ALTER TABLE '|| TRIM(DB)||'.'||TEMP_TBL_NM||' MODIFY UNIQUE PRIMARY INDEX ;';

ELSE 

 SET v_sql_stmt ='ALTER TABLE '|| TRIM(DB)||'.'||TEMP_TBL_NM||' MODIFY UNIQUE PRIMARY INDEX ('||TRIM(INDEX_COLS)||');';

END IF;

 

CALL DBC.SYSEXECSQL(v_sql_stmt);

 

SET v_sql_stmt ='INSERT INTO '|| TRIM(DB)||'.'||TEMP_TBL_NM||' SELECT * FROM '|| TRIM(DB)||'.'||TRIM(TB)||';';

 

CALL DBC.SYSEXECSQL(v_sql_stmt);

 

SET v_sql_stmt ='DROP TABLE '|| TRIM(DB)||'.'||TRIM(TB)||';';

 

CALL DBC.SYSEXECSQL(v_sql_stmt);

 

SET v_sql_stmt ='RENAME TABLE '||TRIM(DB)||'.'||TEMP_TBL_NM||' TO '|| TRIM(DB)||'.'||TRIM(TB)||';';

 

CALL DBC.SYSEXECSQL(v_sql_stmt);

 

     SET ReturnCode = 0;

END

;

 

 

Sarah_07 19 posts Joined 05/13
01 Oct 2014

Thank you Fred :) and thank you all for your valuable suggestion :)

Thanks,
Sarah

fnewbrough 17 posts Joined 05/11
01 Oct 2014

Welcome Sarah!

Rkgowda82 5 posts Joined 09/14
02 Oct 2014

Hi,
I am calling the below procedur but i am getting error as 'CALL failed. 3812: The positional Assignment list has too few values' 
CALL ALTER_PI_UNIQUE ('DS_DAT','PROD_PARTY','PRDCT_ID');
 
Please correct me incsae am calling a wrong procedure?
 
thanks,

Raja_KT 1246 posts Joined 07/09
02 Oct 2014

I did not test the above proc. 3 in parms ,1 out parm:
CALL ALTER_PI_UNIQUE ('DS_DAT','PROD_PARTY','PRDCT_ID',parm5678);

Raja K Thaw
My wiki: http://en.wikipedia.org/wiki/User:Kt_raj1
Street Children suffer not by their fault. We can help them if we want.

Rkgowda82 5 posts Joined 09/14
02 Oct 2014

Thanks Raja..

You must sign in to leave a comment.