Category Archives: How To

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

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

The Perl Data Language Book: PDF fixed to work with tablet/phone PDF readers like Aldiko

Perl Data Language: Scientific computing with Perl

Aldiko Book Reader Premium Full v2.2.3.apkMany PDF readers for smart phones (Android/iphone) and tablets manage the pdf files Calibre_Logobased solely on the Title and Author fields in the PDF file. While for this is fine for your average book, it is not all that helpful with manuals that tend to have abbreviated or no data in the title/author fields.

How to fix? Easy. Go get Calibre. Drop the PDF files on to the running Calibre. Edit them by hitting the E key.

In my case, I edited the “Title”, “Author”, “Tags”, “Publisher” and “Languages”:

Calibre

Calibre doesn’t modify the PDF files themselves so I will need to export the files to a custom directory. In Calibre nomenclature, this is “Saving”. Highlight all the titles you want to export and hit “S” twice. Why twice? No idea. Choose the directory.

Perl Data Language Book for PDL 2.006

Perl Data Langauge

You can now copy the exported PDF files to your phone, tablet, whatever without fear of the v2.006 version of the PDL Book being rejected by Aldiko because the v2.004 version is already added.

Share Button

Open Watcom C/C++ & FORTRAN manuals fixed to work with tablet/phone PDF readers like Aldiko

Aldiko Book Reader Premium Full v2.2.3.apkMany PDF readers for smart phones (Android/iphone) and tablets manage the pdf files Calibre_Logobased solely on the Title and Author fields in the PDF file. While for this is fine for your average book, it is not all that helpful with manuals that tend to have abbreviated or no data in the title/author fields. In the case of the manuals for OpenWatcom C/C++ and FORTRAN, I’m unable to load the manuals as they have the same Title/Author data (WATCOM GML).

How to fix? Easy. Go get Calibre. Drop the PDF files on to the running Calibre. Edit them by hitting the E key.

In my case, I edited the “Title”, “Author”, “Tags”, “Publisher” and “Languages”:

Calibre

Calibre doesn’t modify the PDF files themselves so I will need to export the files to a custom directory. In Calibre nomenclature, this is “Saving”. Highlight all the titles you want to export and hit “S” twice. Why twice? No idea. Choose the directory.

You can now copy the exported PDF files to your phone, tablet, whatever without fear of the manuals being rejected by Aldiko.

Here are the OpenWatcom 1.8 manuals that I’ve ‘fixed’ to work with Aldiko. They are identical to the PDFs at OpenWatcom with the exception of the PDF fields I mentioned previously.

Open Watcom 1.8 – Debugger User’s Guide
Open Watcom 1.8 – FORTRAN 77 Getting Started
Open Watcom 1.8 – FORTRAN 77 Graphics Library Reference
Open Watcom 1.8 – FORTRAN 77 Language Reference
Open Watcom 1.8 – FORTRAN 77 Programmer’s Guide
Open Watcom 1.8 – FORTRAN 77 Tools User’s Guide
Open Watcom 1.8 – FORTRAN 77 User’s Guide
Open Watcom 1.8 – Graphical Tools User’s Guide
Open Watcom 1.8 – Linker User’s Guide Open Watcom
Open Watcom 1.8 – Script_GML Tutorial and Reference Manual
Open Watcom 1.8 – VI Editor Reference and User’s Guide
Open Watcom 1.8 – C Language Reference
Open Watcom 1.8 – C Library Reference
Open Watcom 1.8 – C++ Class Library Reference
Open Watcom 1.8 – C/C++ Getting Started
Open Watcom 1.8 – C/C++ Programmer’s Guide
Open Watcom 1.8 – C/C++ Tools User’s Guide
Open Watcom 1.8 – C/C++ User’s Guide

Share Button

SAP Sybase SQL Anywhere 12.0.1 manuals fixed to work with tablet/phone PDF readers like Aldiko

Aldiko Book Reader Premium Full v2.2.3.apkMany PDF readers for smart phones (Android/iphone) and tablets manage the pdf files Calibre_Logobased solely on the Title and Author fields in the PDF file. While for this is fine for your average book, it is not all that helpful with manuals that tend to have abbreviated or no data in the title/author fields. In the case of the manuals for Sybase IQ, I’m unable to load the manuals for say v11.0 and v12.0.1 as they have the same Title/Author data.

How to fix? Easy. Go get Calibre. Drop the PDF files on to the running Calibre. Edit them by hitting the E key.

In my case, I edited the “Title”, “Author”, “Tags”, “Publisher” and “Languages”:

Calibre

Calibre doesn’t modify the PDF files themselves so I will need to export the files to a custom directory. In Calibre nomenclature, this is “Saving”. Highlight all the titles you want to export and hit “S” twice. Why twice? No idea. Choose the directory.

SQL Anywhere 12.0.1 (r) Server – Database Administration – SAP, Inc_

You can now copy the exported PDF files to your phone, tablet, whatever without fear of the v12.0.1 version of the P&T Guide being rejected by Aldiko because the v12.0 version is already added.

Here are the SQL Anywhere v12.0.1 manuals that I’ve ‘fixed’ to work with Aldiko. They are identical to the PDFs on sybooks with the exception of the PDF fields I mentioned previously.

No copyright infringement is intended. SAP/Sybase, please feel free to take these and host them.

SQL Anywhere 12.0.1 (r) Server – Programming – SAP, Inc_
SQL Anywhere 12.0.1 (r) Server – Spatial Data Support – SAP, Inc_
SQL Anywhere 12.0.1 (r) Server – SQL Reference – SAP, Inc_
SQL Anywhere 12.0.1 (r) Server – SQL Usage – SAP, Inc_
SQL Anywhere 12.0.1 – Changes and Upgrading – SAP, Inc_
SQL Anywhere 12.0.1 – Error Messages – SAP, Inc_
SQL Anywhere 12.0.1 – Introduction – SAP, Inc_
SQL Anywhere 12.0.1 – MobiLinkTM – Client Administration – SAP, Inc_
SQL Anywhere 12.0.1 – MobiLinkTM – Getting Started – SAP, Inc_
SQL Anywhere 12.0.1 – MobiLinkTM – Server Administration – SAP, Inc_
SQL Anywhere 12.0.1 – MobiLinkTM – Server-Initiated Synchronization – SAP, Inc_
SQL Anywhere 12.0.1 – QAnywhereTM – SAP, Inc_
SQL Anywhere 12.0.1 – Relay Server – SAP, Inc_
SQL Anywhere 12.0.1 – SQL RemoteTM – SAP, Inc_
SQL Anywhere 12.0.1 – UltraLite(r) – .NET Programming – SAP, Inc_
SQL Anywhere 12.0.1 – UltraLite(r) – C and C__ Programming – SAP, Inc_
SQL Anywhere 12.0.1 – UltraLite(r) – Database Management and Reference – SAP, Inc_
SQL Anywhere 12.0.1 – UltraLite(r) – Java Programming – SAP, Inc_
SQL Anywhere 12.0.1 – UltraLite(r) – M-Business Anywhere Programming (deprecated) – SAP, Inc_

Share Button