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

Popular cosplayer speaks out about being groped outside of NYCC

Respect for your fellow human being. It doesn’t matter if you *think* she or he wants your attention, unless that person consents, keep your paws off!

Popular cosplayer speaks out about being groped outside of NYCC

http://www.dailydot.com/geek/cosplayer-harassment-nycc-yaya-han/

Share Button

SAP ASE: Using unix domain sockets for turbo bulk copying (BCP)

Cory Sane back in March SAP Sybase2013 on SCN wrote about using unix domain sockets for bulk copying of data in/out of SAP Sybase ASE 15.7.

If you don’t know what unix domain sockets are, Thomas Stover over at Tech Deviancy wrote up an excellent Demystifying Unix Domain Sockets post. Highly recommended!
As usual, Wikipedia also has an article.

What is so great about unix domain sockets? You bypass the networking layer completely. This translates into faster communication between bcp (or similar) and ASE. The downside? The bcp file(s) must be on the same host as the ASE instance and your user must have read/write access to the ‘file’.

Using unix domain sockets with a 16k packet size appears to be ideal for the bcp out (1 million rows in the test). Notice the difference of time between using normal (tcp) connection of 5K rows / second compared to 104k rows / second connecting with unix domain socket.

Unix Domain Sockets

tcp - packet size 65024
test #1  : Clock Time (ms.): total = 184821  Avg = 0 (5410.64 rows per sec.)
test #2  : Clock Time (ms.): total = 178612  Avg = 0 (5598.73 rows per sec.)

uds - packet size 2048
test #3  : Clock Time (ms.): total = 10843  Avg = 0 (92225.40 rows per sec.)
test #4  : Clock Time (ms.): total = 11012  Avg = 0 (90810.03 rows per sec.)

uds - packet size 8192
test #5  : Clock Time (ms.): total = 9823  Avg = 0 (101801.89 rows per sec.)
test #6  : Clock Time (ms.): total = 9965  Avg = 0 (100351.23 rows per sec.)

uds - packet size 12288
test #7  : Clock Time (ms.): total = 9735  Avg = 0 (102722.14 rows per sec.)
test #8  : Clock Time (ms.): total = 9745  Avg = 0 (102616.73 rows per sec.)

uds - packet size 16384
test #9  : Clock Time (ms.): total = 9587  Avg = 0 (104307.92 rows per sec.)
test #10 : Clock Time (ms.): total = 9558  Avg = 0 (104624.40 rows per sec.)

uds - packet size 32768
test #11 : Clock Time (ms.): total = 13205  Avg = 0 (75728.89 rows per sec.)
test #12 : Clock Time (ms.): total = 12961  Avg = 0 (77154.54 rows per sec.)

uds - packet size 65024
test #13 : Clock Time (ms.): total = 13254  Avg = 0 (75448.92 rows per sec.)
test #14 : Clock Time (ms.): total = 13179  Avg = 0 (75878.29 rows per sec.)

With unix domain sockets, we come close to DTU speeds without the penalty of exclusive table locks and a DTU process you can’t kill. It really is like pushing the turbo button!

Obviously, if you are bulk copying out of a view that uses a monster index you you may not see a huge difference, if any, because the majority of the time will transverse that index.

You will see a larger boost with bulk copying out of data than in of data. Why? The network layer is usually not the bottleneck when inserting of data. The bottleneck tends to be more allocating/populating pages in the database or index population if you haven’t dropped the indexes. YMMV

Setting up unix domain sockets is very simple and does NOT require a reboot. Let’s me show you how:

The format of the query master lines you would add to the interfaces file is simple:

query afunix unused //hostname/directory_you_can_write_to/file_name
master afunix unused //hostname/directory_you_can_write_to/file_name

for example:

query afunix unused //myase/dbms/sybase/ASE.socket
master afunix unused //myase/dbms/sybase/ASE.socket

If you want to start the listener without restarting, use sp_listener:

sp_listener 'start', 'afunix://hostname:/directory_you_can_write_to/file_name'

for example:

sp_listener 'start', 'afunix://myase:/dbms/sybase/ASE.socket'
Share Button