Skip to Content

Functions in IQ 16

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

Add a comment
10|10000 characters needed characters exceeded

Assigned Tags

Related questions

3 Answers

  • Best Answer
    Posted on Jan 07, 2016 at 11:15 AM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

  • Posted on Jan 07, 2016 at 02:32 PM

    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

    Add a comment
    10|10000 characters needed characters exceeded

  • author's profile photo Former Member
    Former Member
    Posted on Jan 16, 2016 at 09:09 PM

    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.

    Add a comment
    10|10000 characters needed characters exceeded

Before answering

You should only submit an answer when you are proposing a solution to the poster's problem. If you want the poster to clarify the question or provide more information, please leave a comment instead, requesting additional details. When answering, please include specifics, such as step-by-step instructions, context for the solution, and links to useful resources. Also, please make sure that you answer complies with our Rules of Engagement.
You must be Logged in to submit an answer.

Up to 10 attachments (including images) can be used with a maximum of 1.0 MB each and 10.5 MB total.