Functions in IQ 16

[robert_kratschmann]

Hi,

if the FROM clause is omitted or SYS.DUMMY is used SQL Anywhere will process the query, not IQ. Forcing IQ to process the query will be done by creating the iq_dummy table.

Beside this, are there any functions which will be executed for sure in SQL Anywhere (with other words - are there any functions which don't exists in the Sybase IQ part)?

Many thanks

Robert

[tayeb_hadjou · Accepted solution]

Hi Robert,

I suggest tip below to know whether a query is processed by SA or IQ.

Enable SA text plan and see whether SA cache is used or not. Also when processed by SA, plan is more detailed.

Hereafter, you can see difference in plans when query is processed by SA or not :

set temporary option QUERY_PLAN_TEXT_ACCESS='on';

1-  Processed by IQ

select plan('SELECT user_name() FROM iq_dummy');

Result :

;n3;tScrolling Cursor Store;s ;d1;c1;e|;n1;tLeaf;siq_dummy;d1;e|

2- Processed by SA

select plan('SELECT user_name()');

Result :

( Plan [ Estimated cost: 0, Estimated rows: 0, Estimated Cache Pages: 5966 ] [B]   ( RowConstructor SYS.DUMMY ) )

create table sa_dummy (a int) in SYSTEM;

insert sa_dummy values(1) ;

3- Processed by SA

select plan('SELECT user_name() FROM sa_dummy');

Result :

( Plan [ Estimated cost: 0, Estimated rows: 0, Estimated Cache Pages: 5966 ] [B]   ( TableScan sa_dummy ) ) ;

Hope this helps.

Regards,

Tayeb.

[Former Member]

Hi ,

As Mark pointed correctly ,Other than functions, many of the utilities & commands options of sqlanywhere will not work on IQ.

For example Clauses permitted in the BACKUP and RESTORE statements differ in SAP Sybase IQ and SQL Anywhere

The following SQL Anywhere utilities are not supported by SAP Sybase IQ: • backup • compression • console • initialization • license • log transfer • log translation • rebuild • spawn • some transaction log options (-g, -il, -ir, -n, -x, -z) • uncompression • unload • upgrade • write file.

[markmumy]

We don't have a definitive guide of which functions work solely in SA vs those that work in IQ and SA.

I would point you to the Reference Manual on Building Blocks, Tables, and Procedures.  In here it makes this remark:

10.1 About SAP SQL Anywhere

SAP IQ is an extension of SAP SQL Anywhere.

In most cases, SQL syntax, functions, options, utilities, procedures, and other features are common to both products. There are, however, important differences. Do not assume that all features described in SAP SQL Anywhere documentation are supported for SAP IQ. Use the SAP IQ documentation.

What this is trying to say is that if the function is in the IQ manual, then the function will run in the IQ engine.  If it isn't there, but is in the SA manual, then it will likely not run in IQ and have to be handled by SA.

For example, take the HASH function.  It is not in the IQ manuals but it in in the SA manuals.   Thus function only runs in SA and not in IQ.  If you were to follow Tayeb's process you should see that the SA engine handles the function.

Mark