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

European Space Agency: Landing on the Comet 67P/Churyumov-Gerasimenko comet with the Rosetta probe!

Truly amazing: http://rosetta.esa.int/

A quick intro comes from the Ambition movie

Share Button

Jono Bacon: Dealing With Disrespect: How to handle your critics, no matter what they throw at you

We’ve all been disrespected or negatively criticized at one point or another in our lives. Jono Bacon provides helpful advice on how to deal with it.

Share Button