UPDATED: HOWTO: Extract Permissions from SAP Sybase IQ

Back in 2013 I posted a method of extracting the table/view permissions from SAP IQ. With later 15.4 patches and v16 the USER_NAME() function would fail on the grantee column of sys.systableperm. An easy way to extract the users is to simply join the grantee column with sys.sysuser. I broke up the case statement to we don’t have a leading comma in the grant statement: “grant , insert on “. Since we’re dealing with tiny tables, there shouldn’t be any undue harm in simply running each grant generation statement separately and union them (to make dbisql happy for those of you that don’t have multiple results enabled).

    SELECT 'GRANT SELECT ON ' + USER_NAME(st.creator) + '.' + st.table_name + ' TO ' + su.user_name + ';'
    FROM
        sys.systable st,
        sys.systableperm stp,
        sys.sysuser su
    WHERE stp.stable_id = st.table_id
        and su.user_id = stp.grantee
        and stp.selectauth = 'Y'
        and lower(su.user_name) not in ('dba', 'public', 'sys', 'dbo', 'diagnostics', 'rs_systabgroup', 'sa_debug', 'extenv_main', 'extenv_worker', 'sys_spatial_admin_role')
    
    UNION ALL 
    
    SELECT 'GRANT DELETE ON ' + USER_NAME(st.creator) + '.' + st.table_name + ' TO ' + su.user_name + ';'
    FROM
        sys.systable st,
        sys.systableperm stp,
        sys.sysuser su
    WHERE stp.stable_id = st.table_id
        and su.user_id = stp.grantee
        and stp.deleteauth = 'Y'
        and lower(su.user_name) not in ('dba', 'public', 'sys', 'dbo', 'diagnostics', 'rs_systabgroup', 'sa_debug', 'extenv_main', 'extenv_worker', 'sys_spatial_admin_role')
    
    UNION ALL 
    
    SELECT 'GRANT UPDATE ON ' + USER_NAME(st.creator) + '.' + st.table_name + ' TO ' + su.user_name + ';'
    FROM
        sys.systable st,
        sys.systableperm stp,
        sys.sysuser su
    WHERE stp.stable_id = st.table_id
        and su.user_id = stp.grantee
        and stp.updateauth = 'Y'
        and lower(su.user_name) not in ('dba', 'public', 'sys', 'dbo', 'diagnostics', 'rs_systabgroup', 'sa_debug', 'extenv_main', 'extenv_worker', 'sys_spatial_admin_role')
    
    UNION ALL
    
    SELECT 'GRANT ALTER ON ' + USER_NAME(st.creator) + '.' + st.table_name + ' TO ' + su.user_name + ';'
    FROM
        sys.systable st,
        sys.systableperm stp,
        sys.sysuser su
    WHERE stp.stable_id = st.table_id
        and su.user_id = stp.grantee
        and stp.alterauth = 'Y'
        and lower(su.user_name) not in ('dba', 'public', 'sys', 'dbo', 'diagnostics', 'rs_systabgroup', 'sa_debug', 'extenv_main', 'extenv_worker', 'sys_spatial_admin_role')
    
    UNION ALL 
    
    SELECT 'GRANT REFERENCE ON ' + USER_NAME(st.creator) + '.' + st.table_name + ' TO ' + su.user_name + ';'
    FROM
        sys.systable st,
        sys.systableperm stp,
        sys.sysuser su
    WHERE stp.stable_id = st.table_id
        and su.user_id = stp.grantee
        and stp.referenceauth = 'Y'
        and lower(su.user_name) not in ('dba', 'public', 'sys', 'dbo', 'diagnostics', 'rs_systabgroup', 'sa_debug', 'extenv_main', 'extenv_worker', 'sys_spatial_admin_role')
    
    UNION ALL
    
    SELECT 'GRANT INSERT ON ' + USER_NAME(st.creator) + '.' + st.table_name + ' TO ' + su.user_name + ';'
    FROM
        sys.systable st,
        sys.systableperm stp,
        sys.sysuser su
    WHERE stp.stable_id = st.table_id
        and su.user_id = stp.grantee
        and stp.insertauth = 'Y'
        and lower(su.user_name) not in ('dba', 'public', 'sys', 'dbo', 'diagnostics', 'rs_systabgroup', 'sa_debug', 'extenv_main', 'extenv_worker', 'sys_spatial_admin_role')

SAP Sybase Replication Server ERROR -99999 Severity 5 Values exceed buffer length – SOLVED

Running SAP Sybase SAP SybaseReplication Server has always been interesting and rather frustrating that in its fragility. Today’s lesson is not exactly that clear. Take the following error message:

ERROR #1027 DSI EXEC(104(1) repdb_svr.rep_db) - /dsiutil.c(390)
    Open Client Client-Library error: Error: -99999, Severity 5 -- 'Values exceed buffer length'.
ERROR #5215 DSI EXEC(104(1) repdb_svr.rep_db) - /dsiutil.c(393)
    The interface function 'SQLPrepare' returns FAIL for database 'repdb_svr.rep_db'. The errors are retryable. The DSI thread will restart automatically. See messages from the interface function for more information.

RANT: While Replication Server says it is retryable, it never actually retries.
It is for the DSI connection but which buffer?? Replication Server doesn’t list any “buffers” for the DSI explicitly. There are a myriad of caches for the DSI connection. In the error message I see two hints to narrow it down: “Values exceed” and “SQLPrepare”. The most likely cache candidates, to me, would be the batch size (dsi_cmd_batch_size) and the dynamic sql cache (dynamic_sql_cache_size).
A simple check would be to disable dynamic SQL and see if we get the same error message:

suspend connection to repdb_svr.rep_db
go
alter connection repdb_svr.rep_db set dynamic_sql to 'off'
go
resume connection to repdb_svr.rep_db
go

Within a few seconds, I received the same message, so that wasn’t the culprit. Before we do anything else, let’s reenable the dynamic sql:

suspend connection to repdb_svr.rep_db
go
alter connection repdb_svr.rep_db set dynamic_sql to 'on'
go

That leaves the batch size as the most likely culprit. So let’s increase that and see what happens:

suspend connection to repdb_svr.rep_db
go
admin who, dsi, repdb_svr, rep_db
go
--  record Cmd_batch_size (default is 8192
--  Increase dsi_cmd_batch_size 
alter connection repdb_svr.rep_db set dsi_cmd_batch_size to '32768'
go
resume connection to repdb_svr.rep_db
go

The error message did not reoccur and I see replication moving by monitoring the admin,sqm in rep server and the rs_lastcommit table in the replicate database to ensure we’re moving.

You may ask what changed that would require increasing the batch size. Well, a very large transaction that was trying to insert data that already existed but since not all the data already existed, we needed to change INSERT into DELETE followed by INSERT:

alter connection to repdb_svr.rep_db set dsi_command_convert to 'i2di'

Why would that cause it to go boom? Well, the dsi_command_convert is applied AFTER replication server slices and dices the transactions into batches.

Wanted: SAP manuals in ePub format

Every time SAP comes out with a new set ofSybase SAP pdf manuals, the meta data has to be corrected. Often the stored titles, description, etc are wildly wrong. Very sloppy and unprofessional for a mega corp the size of SAP.

The ePub book format has been out for many years and has many features that make it tablet, phone, PC, whatever friendly. Reading a SAP manual at night? No problem, change the font color to white on black so you don’t wake your spouse. The font is too small? No problem, choose a larger or different font. You can’t do any of that with a PDF. Try reading the ASE Admin guides on a 4″ iPhone. I dare you. You might as well pour salt in your eye sockets.

SAP IQ: dbisql is unable to load the SybaseIQ SQLAnywhere plugins. SOLVED!

I recently patched an SAP IQ server to 16.0 SP8 PL30 and ran into an interesting error message when trying to start dbisql:

$ dbisql
Interactive SQL could not load the "SQLAnywhere" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL could not load the "SybaseIQ" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL could not load the "HANA" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL could not load the "GenericODBC" plug-in.
Its "ngdbc.jar" file has moved or has been deleted. You will not be able to connect to the databases handled by that plug-in.
Interactive SQL cannot start because it is not installed correctly. No database plug-ins has been registered.
To fix this problem, you should reinstall the program.

If you scan your IQ directory, you will notice there isn’t a “ngdbc.jar” file. You take a look at another IQ box that is working and it doesn’t have the ngdbc.jar file either. The error message is incorrect. The message should report that it isn’t able to access the saip16.jar (or saip11.jar if you’re on IQ 15.x) and/or the jodbc4.jar file in the $SYBASE/IQ-(IQ RELEASE)/java directory (e.g. $SYBASE/IQ-16_0/java).
Verify that the two files exist and the permissions are correct:

316 -rwxr-xr-x.  1 sybase sybase  320071 Mar 20 14:51 jodbc4.jar
112 -rwxr-xr-x.  1 sybase sybase  112325 Mar 20 14:56 saip16.jar

If everything looks okay and it still gives the error, you will need to re-register the plugins. The first thing is to move the dbisql ‘registry’:

$ mv $SYBASE/IQ-(IQ RELEASE)/bin64/dbisql_64.rep $SYBASE/IQ-(IQ RELEASE)/bin64/dbisql_64.rep.old

Next, re-register (for IQ 16):

$ cd $SYBASE/IQ-(IQ RELEASE)/java
$ dbisql -Xregister sa16 SybaseIQ com.sybase.saisqlplugin.IQISQLPlugin "$(pwd)/saip16.jar:$(pwd)/jodbc4.jar"

for IQ 15:

$ cd $SYBASE/IQ-(IQ RELEASE)/java
$ dbisql -Xregister sa11 SybaseIQ com.sybase.saisqlplugin.IQISQLPlugin "$(pwd)/saip11.jar:$(pwd)/jodbc4.jar"

Newer IQ 15 patches use SQL Anywhere 12, so if you have saip12.jar instead of saip11.jar in your java dir, use that:

$ cd $SYBASE/IQ-(IQ RELEASE)/java
$ dbisql -Xregister sa12 SybaseIQ com.sybase.saisqlplugin.IQISQLPlugin "$(pwd)/saip12.jar:$(pwd)/jodbc4.jar"

dbisql should now work 🙂

The dbisql_64.rep is simply a glorified ini file that contains the following which should be transferrable but I’ve found that dbisql is very finicky with regards to this file.

[SybaseIQ]
classLoaderName=sa16
mainclass=com.sybase.saisqlplugin.IQISQLPlugin
classpath=/opt/client/SAP-IQ/IQ-16_0/java/saip16.jar:/opt/client/SAP-IQ/IQ-16_0/java/jodbc4.jar

You may need to update the permissions if other users rely on this particular dbisql installation.

SAP SYBASE IQ 16.0 SP8 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 v15.4 and v16.0 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.

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

Here are the IQ v16.0 SP8 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.

IQ 16.0 SP8 – SAP IQ Cockpit – SAP, Inc_
IQ 16.0 SP8 – Installation and Configuration Guide [ Windows ] – SAP, Inc_
IQ 16.0 SP8 – Installation and Configuration Guide [ Solaris ] – SAP, Inc_
IQ 16.0 SP8 – Installation and Configuration Guide [ HP-UX ] – SAP, Inc_
IQ 16.0 SP8 – Administration_ User Management and Security – SAP, Inc_
IQ 16.0 SP8 – Reference_ Statements and Options – SAP, Inc_
IQ 16.0 SP8 – User-Defined Functions – SAP, Inc_
IQ 16.0 SP8 – Administration_ In-Memory Row-Level Versioning – SAP, Inc_
IQ 16.0 SP8 – Reference_ Building Blocks, Tables, and Procedures – SAP, Inc_
IQ 16.0 SP8 – Reference_ Programming – SAP, Inc_
IQ 16.0 SP8 – Release Bulletin [ Windows ] – SAP, Inc_
IQ 16.0 SP8 – Release Bulletin [ Linux ] – SAP, Inc_
IQ 16.0 SP8 – Administration_ Multiplex – SAP, Inc_
IQ 16.0 SP8 – What’s New in SAP IQ 16.0 – SAP, Inc_
IQ 16.0 SP8 – Migration [ Windows ] – SAP, Inc_
IQ 16.0 SP8 – Administration_ Load Management – SAP, Inc_
IQ 16.0 SP8 – Guide to Licensed Options – SAP, Inc_
IQ 16.0 SP8 – Migration [ Linux and UNIX ] – SAP, Inc_
IQ 16.0 SP8 – Quick Start [ Windows ] – SAP, Inc_
IQ 16.0 SP8 – Quick Start [ Linux and UNIX ] – SAP, Inc_
IQ 16.0 SP8 – Administration_ Spatial Data – SAP, Inc_
IQ 16.0 SP8 – Performance and Tuning Guide – SAP, Inc_
IQ 16.0 SP8 – Release Bulletin [ IBM AIX ] – SAP, Inc_
IQ 16.0 SP8 – Release Bulletin [ HP-UX ] – SAP, Inc_
IQ 16.0 SP8 – Release Bulletin [ Solaris ] – SAP, Inc_
IQ 16.0 SP8 – Administration_ Database – SAP, Inc_
IQ 16.0 SP8 – Unstructured Data Analytics – SAP, Inc_
IQ 16.0 SP8 – Utility Guide – SAP, Inc_
IQ 16.0 SP8 – Administration_ Backup, Restore, and Data Recovery – SAP, Inc_
IQ 16.0 SP8 – Installation and Configuration Guide [ IBM AIX ] – SAP, Inc_
IQ 16.0 SP8 – Administration_ Globalization – SAP, Inc_
IQ 16.0 SP8 – Installation and Configuration Guide [ Linux ] – SAP, Inc_
IQ 16.0 SP8 – Interactive SQL Guide – SAP, Inc_
IQ 16.0 SP8 – Introduction to SAP IQ – SAP, Inc_

SAP Sybase IQ: How many connections are in use? SOLVED

Very simple question. Very simple answer.

select 
    @@max_connections as 'max_connections', 
    count(*) as 'active_connections', 
    (1 - (@@max_connections - count(*)) / convert(numeric, @@max_connections)) * 100 as 'percent_active' 
  from sp_iqconnection();

Output:

 max_connections active_connections percent_active
 --------------- ------------------ ---------------------
             350                 68               19.4286

@@max_ connections: For the network server, the maximum number of active clients (not database connections, as each client can support multiple connections). For Adaptive Server Enterprise, the maximum number of connections to the server. — Sybase IQ > Reference: Building Blocks, Tables, and Procedures > SQL Language Elements > Variables

HOWTO: SAP Sybase IQ / SQL Anywhere : searching date, datetime, time fields

If you’re new to IQ (or SQL Anywhere) you may run into an oddity that if you connect with ISQL the following code works but doesn’t work in DBISQL:

SELECT col1_date
FROM mytable
WHERE col1_date >= '06/01/2014'

This is because any connection to IQ using the native connection (SQL Anywhere connection) that is used by dbisql and the like expects string to datetime format of “YYYY-MM-DD HH:NN:SS.SSS”. This is not changeable by setting an option. The string needs to be converted using a style (datetime string style # 103) prior to being used as a SARG.

SELECT col1_date
FROM mytable
WHERE col1_date >= convert(datetime, '06/01/2014', 103)

Even those of us that have been around for eons tend to forget ‘little’ gotchas like this.

From http://infocenter.sybase.com/help/index.jsp?topic=/com.sybase.infocenter.dc36271.1570/html/blocks/X41864.htm :

Table 2-3: Date format conversions using the style parameter
Without century (yy) With century (yyyy) Standard Output
Key “mon” indicates a month spelled out, “mm” the month number or minutes. “HH ”indicates a 24-hour clock value, “hh” a 12-hour clock value. The last row, 23, includes a literal “T” to separate the date and time portions of the format.
0 or 100 Default mon dd yyyy hh:mm AM (or PM)
1 101 USA mm/dd/yy
2 2 SQL standard yy.mm.dd
3 103 English/French dd/mm/yy
4 104 German dd.mm.yy
5 105 dd-mm-yy
6 106 dd mon yy
7 107 mon dd, yy
8 108 HH:mm:ss
9 or 109 Default + milliseconds mon dd yyyy hh:mm:ss AM (or PM)
10 110 USA mm-dd-yy
11 111 Japan yy/mm/dd
12 112 ISO yymmdd
13 113 yy/dd/mm
14 114 mm/yy/dd
14 114 hh:mi:ss:mmmAM(or PM)
15 115 dd/yy/mm
16 or 116 mon dd yyyy HH:mm:ss
17 117 hh:mmAM
18 118 HH:mm
19 hh:mm:ss:zzzAM
20 hh:mm:ss:zzz
21 yy/mm/dd HH:mm:ss
22 yy/mm/dd HH:mm AM (or PM)
23 yyyy-mm-ddTHH:mm:ss

The default values (style 0 or 100), and style 9 or 109 return the century (yyyy). When converting to char or varchar from smalldatetime, styles that include seconds or milliseconds show zeros in those positions.

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

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'

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 ();