Sybase ASE 11.5+
Sybase IQ 12.7+
Last updated: 21 August 2011
Using IQ functionality in ASE
 
Contents:

For a full IQ quickref guide, covering IQ 15.4, go to www.sypron.nl/iqqr.


Introduction
As described in the April 2011 quiz question, it is relatively simple to use IQ functionality in ASE. This can be useful for areas where IQ 15.x has SQL features, but ASE does not, for example with OLAP functions.
The basic idea here is that, if you have Sybase IQ available anyway (with all required licensing in place), you could use the CIS functionality (for remote data access through proxy tables and RPCs) in ASE and IQ to allow ASE to use IQ-specific SQL features.

In a nutshell, this is how it works (full details are described below):
  • the actual data resides in an ASE table; in IQ, a proxy table is created that maps to the ASE table
  • in IQ, a stored procedure is written that performs the required processing on the ASE data (through the IQ proxy table)
  • ASE executes the IQ stored procedure through a remote procedure call (RPC)
Before proceeding, it should be clear that using a proxy table this way has a lot of overhead, and you should expect a severe performance penalty compared to running the same IQ functionality natively inside IQ with the data in IQ tables. But the idea of the trick described here is to use some of the rich IQ SQL functionality to perform a function that ASE couldn't do, or would require implementing the functionality manually in SQL in ASE. The price you pay for using that IQ functionality is potentially a loss of performance.
Having said that, the performance overhead in IQ 15.2 should be less than in earlier IQ versions.

Setup steps
Before getting to the setup details, let's take as an example the requirement to do some OLAP-style processing in ASE, such as calculating the 6-month exponentially weighted moving average over some monthly sales data. IQ 15.1 contains the OLAP function exp_weighted_avg() which makes this very easy -- if only you can figure out how to let ASE invoke it.

This is the schema of the ASE table holding the actual data:
-- run this in ASE:
use my_db
go
create table my_table 
	( year int,
	  month varchar(30),
	  sales money )
go
Before configuring the ASE & IQ servers, let's assume the following:
  • our ASE server (named 'MY_ASE') runs on port 5001 on server 'myasehost'
  • we're connecting to 'MY_ASE' with login 'my_ase_login', with password 'my_ase_passwd' (without the quotes); this user is assumed to be 'dbo' in database 'my_db'
  • our IQ 15.1+ server (named 'MY_IQ') runs on port 2639 on server 'myiqhost'
  • we're connecting to 'MY_IQ' with login 'my_iq_login', with password 'my_iq_passwd' (without the quotes)
First, we create a proxy table in IQ that points to our ASE table my_db..my_table. This takes a couple of setup steps:
-- run these steps in IQ, as the DBA user:

-- create mapping to remote ASE server :
-- (also see this note)
create server MY_ASE class 'asejdbc' using 'myasehost:5001';

-- create externlogin
create externlogin my_iq_login to MY_ASE 
remote login my_ase_login identified by my_ase_passwd;

-- test the CIS connection from IQ to ASE:
-- this should show the ASE servername and version
forward to MY_ASE {select @@servername, @@version};

-- create proxy table mapping to ASE table:
create existing table ase_proxy_tab at 'MY_ASE.my_db.dbo.my_table';

-- test proxy table (run as user 'my_iq_login'):
select * from ase_proxy_tab;
There are different ways to set up the proxy table, and you could potentially run into some issues while setting things up (go here for more details).

Now that we can access ASE data from IQ, the next step is to write an IQ stored procedure that calculates the moving average, accessing the ASE data through the IQ proxy table. This procedure will be kicked off from within ASE.
-- create this proc in IQ 15.1 or later:
-- (NB: 'create or replace' requires IQ 15.1+; otherwise use 'create')
create or replace procedure iq_proc_for_ase (in @nr_months int)
as
begin
	-- IQ analytic functions cannot operate on proxy tables directly,
	-- so we need to copy the data
	select * into #asecopy from ase_proxy_tab

	select year, month, 
	moving_avg_6month = exp_weighted_avg(sales, @nr_months) 
	over (order by year, month(month)) 
	from #asecopy
	order by year, month(month)

	drop table #asecopy
end;

-- local test in IQ if this procedure works 
-- (NB: result set will be empty at this point, since the
-- ASE table is still empty!)
execute iq_proc_for_ase 6;
We've set up the IQ side now. The remaining part is to configure ASE so that we can kick off the stored procedure iq_proc_for_ase above, which resides in IQ:
-- run these steps in ASE (run as 'sa' user):

sp_configure 'enable cis', 1
go
-- if it was not already set to 1, shutdown & restart ASE first

sp_configure 'cis rpc handling', 1 
go
-- now disconnect & reconnect the session

-- create mapping to remote IQ server :
sp_addserver MY_IQ, ASIQ, 'myiqhost:2639'
go

-- create externlogin:
sp_addexternlogin MY_IQ, my_ase_login, my_iq_login, my_iq_passwd
go

-- test the connection to IQ:
connect to MY_IQ
go
select @@version  -- should show the IQ version
go
disconnect
go

-- run as user 'my_ase_login': test executing the IQ stored procedure 
-- from within ASE:
exec MY_IQ...iq_proc_for_ase 6
go
The only thing missing now is some test data.
Use this query will quickly generate some rows:
-- run this in ASE:
insert my_db..my_table
select year(m), datename(mm,m),
       sales=1000+n*10+rand()*10*n -- simulate varying sales numbers
from (select m=dateadd(mm, number, 'Jan 01, 2003'), 
             n=number from master..spt_values where type='P' and number < 100
     ) as x order by x.n
go
...and now run the IQ stored procedure:
-- run this in ASE:
-- 6 means: 6-month moving average
exec MY_IQ...iq_proc_for_ase 6
go
 year        month                          moving_avg_6month
 ----------- ------------------------------ --------------------
[...]
        2006 July                                    1632.958134
        2006 August                                  1667.054181
        2006 September                               1618.518615
        2006 October                                 1586.282182
        2006 November                                1643.579587
        2006 December                                1697.787791
        2007 January                                 1644.576679
        2007 February                                1729.625285
[...]


Using IQ Full Text Search in ASE
  • Another example of using IQ features in ASE is with IQ full text search.
    IQ 15.2 introduced the new 'full text search' feature, which performs text search functions, incl. proximity searches, on CLOB/long varchar data. Using the ASE CIS infrastructure, the IQ text search functionality can also be used inside ASE. Details are described in the technical whitepaper: "Using Sybase IQ as a Text Search Engine for ASE" (note that the setup described in this whitepaper is different from the setup above, though both use CIS features).


General notes
  • The approach described above also works for IQ 12.7. Note that the OLAP function used above, exp_weighted_avg(), was introduced only in IQ 15.1 (just as weighted_avg() and a bunch of other statistical functions). Also note that the full text search feature was introduced in IQ 15.2.


Notes on IQ-to-ASE connectivity
  • In the example above, we're using the 'asejdbc' driver to set up a remote server mapping to ASE (with the IQ command create server). In principle, it is better to use the 'aseodbc' driver since it has less performance overhead than 'asejdbc'. Unfortunately, it is also a little more complicated to set up than with 'asejdbc'; for this reason, 'asejdbc' was used for the example.
    Go here for full details on how to set up an ODBC connection instead.
  • The efficiency of proxy tables created in IQ has been improved significantly in IQ 15.2. You should therefore see less performance impact when using the trick described on this page in IQ 15.2 than in earlier iQ versions.
  • When using 'asejdbc' to set up the remote server mapping in IQ, I ran into the following error when connecting to ASE (it's unlikely you'll hit the same error, but just in case...):
    SQL Anywhere Error -667: Could not access column information 
    for the table 'my_db.dbo.my_table'
    
    The reason for the error was that my IQ 15.2 ESD2 server used jConnect version 6 for the 'asejdbc' driver, but unfortunately that didn't work together well with the stored procedures for jConnect version 7 which were installed in my ASE 15.5 server. I believe this issue has been fixed now, but should you hit this error anyway, run sp_version in ASE to find the jConnect version mentioned for the installjdbc script, and compare it with the jConnect version in the $SYBASE directory for your IQ installation (there's a jConnect-X_Y directory).
    In my case, the problem was solved by installing the jConnect 6.0 stored procs into ASE by manually running the script ./jConnect-6_0/sp/sql_server15.0.sql (in the ASE installation directory).


Notes on the IQ stored procedure 'iq_proc_for_ase'
  • The IQ stored procedure iq_proc_for_ase described above uses a proxy table to copy the data from ASE into a temporary table in IQ. It is quite possible that using the IQ command insert..location performs better, so you could prefer to use that instead. The reason a proxy table was used in the example above is that it is simpler as it copies the column names and datatypes from the ASE table automatically.
  • Kevin Sherlock kindly pointed out that although some of the IQ OLAP functions cannot operate on proxy tables, they can operate on a derived table. The advantage of this is that you don't need the additional select-into statement to copy from the proxy table into the local IQ temporary table (and neither do you have to drop that temp table).
    The only thing is that you need a trick to make the derived table work, since you'd get an error if you just put the proxy table in a derived table clause as follows: from (select year, month, sales from ase_proxy_tab) as derived_tb.
    As more often in IQ, you need to be a little persuasive by making a regular IQ table part of the query; here, I'm using the 1-row iq_dummy table for this (as the IQ documentation often uses itself):
    -- create iq_dummy
    create table iq_dummy (dummy_col int not null unique check (dummy_col=0));
    insert iq_dummy values (0);
    grant select on iq_dummy to public;
    
    -- create the proc, this time using a derived table
    create or replace procedure iq_proc_for_ase (in @nr_months int)
    as
    begin
    	-- IQ analytic functions cannot operate on proxy tables directly,
    	-- but they can operate on a derived table if that also includes 
    	-- an IQ table (here, the famous 1-row iq_dummy table)
    	select year, month, 
    	moving_avg_6month = exp_weighted_avg(sales, @nr_months) 
    	over (order by year, month(month)) 
    	from 
    	   (select year, month, sales 
    	    from ase_proxy_tab, iq_dummy) as derived_tb
    	order by year, month(month)
    end;
    
  • The IQ stored procedure iq_proc_for_ase above sends its output to the client. It is not difficult to change it such that it writes its results back to a table in ASE instead.
    Below is an enhanced version of the procedure which takes an additional parameter specifying the ASE table where the results must be placed into. The IQ procedure will automatically create a proxy table to this ASE table, and insert the results there.
    Note that the result table must exist in ASE before calling the IQ procedure; obviously, you must ensure the number of columns and the datatypes are correct for the result set inserted into it.
    -- run this in IQ:
    create or replace procedure iq_proc_for_ase 
         (in @nr_months int, 
    	  in @result_name varchar(50))
    as
    begin
    	declare @cmd varchar(100)
    	set @cmd = 'create existing table ase_result_tab ' + 
    			   ' at ''MY_ASE.' + @result_name + ''''
    	execute(@cmd)
    
    	-- IQ analytic functions cannot operate on proxy tables directly,
    	-- so we need to copy the data
    	select * into #asecopy from ase_proxy_tab
    
    	select year, month,
    	moving_avg_6month = exp_weighted_avg(sales, @nr_months)
    	over (order by year, month(month))
    	into #aseresult
    	from #asecopy
    	order by year, month(month)
    
    	insert ase_result_tab
    	select * from #aseresult
    
    	drop table ase_result_tab
    
    	drop table #asecopy
    end;
    
    
    
    -- run this in ASE:
    use my_db
    go
    create table my_results (yy int, mm varchar(20), avg_6month float)
    go
    exec MY_IQ...iq_proc_for_ase 6, 'my_db.dbo.my_results'
    go
    
    -- now, the results are in table 'my_results':
    select * from my_results
    go
    
    
 
 This document is located at www.sypron.nl/ase_iq_cis.html