SAP Sybase IQ Data Warehouse Index Advisor and awk

If you are running SAP’s IQ Data Warehouse SAP_IQsomewhere in your organization, there will be a time when you need to enable the Index Advisor (see SAP Sybase IQ: Index Advisor for a user) and parse the output. One problem is that the output will go to the IQ message log (IQ.iqmsg) which is already polluted with other information. So how can you easily pull out the recommendations and still keep your sanity?

A little AWK will do the majority of the work for you:

 awk 'BEGIN {COLSTART=6;COLEND=17}/Add |Rebuild /{ for (i=COLSTART;i<=COLEND;i++) printf("%s%s",$(i), i<COLEND ? OFS :"\n"); }' IQ.iqmsg | sort | uniq -c | sort -n |tail -10

produces the following “top 10” index advisories:

 8 Add a unique HG index to join key column SCHEMA_B.STUFF_SKILL_SYSTEM.STUFF_SKILL_ID
 8 Add unique LF index on SCHEMA_A.STUFF_SKILL_SYSTEM.STUFF_SKILL_ID
 9 Add a unique HG index to join key column SCHEMA_A.STUFF_SYSTEM.STUFF_ID
12 Add a HG index to join key column SCHEMA_B.STUFF_SYSTEM.SOURCE_STUFF_ID
12 Add a unique HG index to join key column SCHEMA_B.STUFF_SKILL_ID_MAP.TYPE_ID
12 Condition 1 Index Advisor: Add LF index on SCHEMA_B.STORAGE_SYSTEM.SYSTEM_ID
22 Condition 2 Index Advisor: Add LF index on SCHEMA_B.STORAGE_SYSTEM.STATUS_C
32 Add LF index on SCHEMA_A.MOON_TREE.MOON_TREE_CONTEXT_ID
34 Add unique LF index on SCHEMA_A.STUFF_OBTUSE_CALLER_MAP.STUFF_CALLER_OBTUSE_ID
34 Add unique LF index on SCHEMA_A.STUFF_OBTUSE_MAP.STUFF_OBTUSE_ID

That AWK code looks pretty ugly doesn’t it? Well, it is simpler than you think.

BEGIN {
    COLSTART=6;
    COLEND=17
}
/Add |Rebuild /{
    for (i=COLSTART; i< =COLEND; i++) 
        printf("%s%s",$(i), i<COLEND ? OFS :"\n"); 
}

The BEGIN block runs before any data is parsed. We need to print columns 6 to 17 for each line that matches. We set the COLSTART (starting column) to 6 and COLEND (ending column) to be 17.

/Add |Rebuild /

Simply means if the line contains “Add ” or “Rebuild ” then do whatever is in the code block (between the curly braces).

for (i=COLSTART; i< =COLEND; i++)

For each number between 6 and 17 execute the following line.

printf("%s%s",$(i), i<colend ? OFS :"\n");

Print the column and the field separator. If we’re looking at column 17, then we print the column and a new line.

Next we sort the output of awk: sort

Now that we have sorted output, we count any duplicate index recommendations, print the # of duplicates and the unique line

Let’s sort on the number of duplicate index recommendations with sort -n

In this case, we only want the top 10 index recommendations: tail -10

This is based on the recommendation from SAP consultant Steve Bologna:

egrep -i "Add " IQ.iqmsg | awk '{ print $6 " " $7 " " $8 " " $9 " " $10 " " $11 " " $12 " " $13 " " $14 " " $15 " " $16 " " $17 }' | sort | uniq -c | sort -n
Share Button

SAP Sybase IQ: Index Advisor for a user

There are times when you need individual users the ability to determine what indexes are suggested by the index advisor but you don’t want or are unable to give them full dba access. All you need to do is grant the execute permission on the sp_iqindexadvice stored procedure.

Grant execute on sp_iqindexadvice to user;

create table test (col1 int, col2 varchar(10));
insert into test values (1, 'test1');
insert into test values (2, 'test2');
insert into test values (3, 'test3');
insert into test values (4, 'test4');
commit;

SET OPTION index_advisor = 'ON';
SET OPTION index_advisor_max_rows = 100;
commit;

select * from test where col1 =1 ;
commit;

call sp_iqindexadvice ();
Share Button