Please be aware that the content in SAP SQL Anywhere Forum will be migrated to the SAP Community in June and this forum will be retired.

May DBISQL skip SELECT statements in a SQL batch?

1

I'm doing some comparisons with a bunch of SELECT statements that call the same (complex) stored function with different parameters, say something like

-- notice start time
-- output start data
select MyFunc(0, 1);
select MyFunc(0, 2);
...
select MyFunc(0, 100);
-- output end data
-- notice end time

When using dbisqlc and DBISQL with v12.0.1.4178, I notice that sometimes all or nearly all of the function calls are skipped without any warning or the like. (They use MESSAGE statements to print out their actions, so I can track their calls).

Why is that?

I explicitly declared the stored function as NOT DETERMINISTIC (though I don't call it with identical arguments).

MAX_QUERY_TASKS and MAX_PLANS_CACHED are on their default values, in case some kind of (unexpected) "intra-batch" parallelsm could apply here.

May this be anyhow related to DBISQL's options w.r.t. the output of result sets from multiple statements / procedures with multiple result sets? - I have modified those options as following, and now all function calls are executed:

alt text

Before I had chosen the "Show results from the last statement" option, however, I would certainly not expect that "not showing a result set" would mean "not calling that statement"...

EDIT: When I set back that option to "Show results from the first statement", all the function calls are skipped in DBISQL. So I guess that seems to be the problem's source...

asked 07 Aug '15, 04:00

Volker%20Barth's gravatar image

Volker Barth
40.2k●361●550●822
accept rate: 34%

edited 07 Aug '15, 04:10

dbisqlc seems to be influenced by that option, as well, though it is not even visible/settable there AFAIK:

When DBISQL is set to "Results from the last statement", dbisqlc seems to execute just "some" of the SELECT statements, in my current case the first 16 (in one run) or the first 4 (in the next run) from around 50 calls. The following statements are normally executed. Very strange.

When DBISQL is set to "each statement", dbisqlc seems to behave as expected. That has been that way in one run, but after restarting dbisqlc, it again just executes some of the calls. Even stranger.

Note: I'm aware that the actual reason may be related to a fully different issue, I'm just trying to get some clues...

(07 Aug '15, 04:16) Volker Barth
Comment Text Removed

Note, that problem is reproducible, so a restart (as suggested by Breck) won't help.

Though I prefer dbisqlc for timings (DBISQL does too much under the covers, AFAIK), I cannot use dbisqlc (neither 32-bit nor 64-bit) here, as the SQL batch is not executed reliably, and that makes the answer to my general question "Do both versions behave identically here?" quite hard to answer:(

FWIW: The same happens with v16.0.0.2158, too. I'm stuck.

(07 Aug '15, 06:45) Volker Barth
Replies hidden

Maybe it will help to know the function definition.

FWIW this is the second dbisqlc issue you have reported that others seem uncapable of reproducing. Not certain what can be causing these differences yet ... so any additional info. may prove valuable.

(07 Aug '15, 09:59) Nick Elson S...
Replies hidden
Comment Text Removed

...and it also appears with dbisqlc v17.0.0.1062 - DBISQL runs fine here, possibly because it always displays all result sets of all statements, i.e. the option is gone.

(07 Aug '15, 10:03) Volker Barth

Hm, it's a high-level function that basically runs a loop that calls several other complex functions within a savepoint - I guess that won't be helpful without the whole database schema...

The function header is as following:

create or replace function dbo.MyFctn(nPkPersonStart int, nFkStruktur int) returns int
not deterministic
begin
    declare bSuccess int;
    savepoint strSavepoint; -- named savepoint
    message 'MyFctn starting with pk ' || nPkPersonStart || ...';

    for forDubl as crsDubl cursor for
    -- for loop calling a second function (and evaluating bSuccess)
    end for;
    if bSuccess <> 0 then
        release savepoint strSavepoint;
    else
        rollback to savepoint strSavepoint;
        set nResult = -1;
    end if;

    message 'MyFctn: Result = ' || nResult;

    return nResult;
end;

The ISQL batch (note: without a BEGIN/END, so no code block) consists of some timing calculations and then a bunch of "select MyFctn(0, x);" calls. The console log shows that dbisqlc just executes some first n of these calls as the initial message statement for these other calls is not reported in the log.

(07 Aug '15, 10:17) Volker Barth
Comment Text Removed

That's good advice. My current problem is that the specific stored function and the underlying schema is way to complex to show here...

And I have been to lazy to try to figure out a simple reproducible but that's on my to-do list...

(10 Aug '15, 04:38) Volker Barth

Tip: The first step in the reproducible should be dbinit :)

(10 Aug '15, 04:49) Breck Carter

Sorry, I started with MD...

Here we ware.

(10 Aug '15, 06:20) Volker Barth
showing 3 of 8 show all flat view
3 Answers:
active answersoldest answersnewest answerspopular answers
0

Let me start over with a few (better informed) observations about dbisqlc to see if that helps.

It is actually not clear how you are measuring this. Are you watching the statements sent to the database server by reading the 'requestlogging' output? Or are you just watching the result set displayed? If you are looking at the request log, and if you are seeing statements getting skipped maybe you can post that to show us more detail about this. {from my tests I did not see that happening at all}

DBISQLc when presented with multiple statements (separated by either ';' or '\ngo\n') will only ever display the last result set. To get multiple result sets to display you need to bracket those inside of BEGIN ... END; statements and issue RESUME operations for each 'next' result set you want to see. The RESUME statement works the same for WSQL procedures that return multiple results so packaging those statements up as a stored procedure will also allow you to step through the result sets using the RESUME statement. [The 'BEGIN' and 'END;' statements make the block of SQL in your script script a single SQL batch, which is the equivalent of an unnamed SQL procedure; so it is the same feature.]

As you can see DBISQLc handles result sets in a very different way than the Java DBISQL utility does. And observed behaviours or features seen from using dbisql are not guaranteed to be available in dbisqlc {many will not be}.

As to when the 2 started diverging, that is documented as starting in 9.0.x. As of the 9.0.2 doc set, the "Deploying administration tools" article started talking about this stating "....does not contain all the features of [the java DBISQL] and compatibility between the two is not guaranteed." !

And finally there has be non-GUI versions of dbisqlc and if you are running one of those (Unix and possibly Linux) the behavior of those may be slightly different again.

Basically dbisqlc is provided to allow legacy DBA crafted scripts, legacy OEM scripts, and non-gui platforms have backward support available. Some additions do make it into dbisqlc (to allow for new data types for example) but you should consider it to be a dead-end component as far as new feature development goes.

I hope something in here helps.

permanent link

answered 07 Aug '15, 11:21

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k●35●107
accept rate: 32%

Hm, just to provide more information (I'm out of office now...):

  • As stated in my last comment on the question, the series of function-calls is not part of a code block, there is no enclosing BEGIN/END. (I certainly could try if that makes a difference.) - It's basically as presented in the code snippet in the question.
  • I'm not looking at the RLL, the function has its own MESSAGE statements to log its calls (as shown above), and these messages clearly show that dbisqlc does only execute some of the calls and omits others, and DBISQL does skip them all unless the noted ISQL option is set. (And as these calls are making data modifications, I can certainly tell that the according calls have not taken place.) - The individual calls also take considerable time (from some seconds to some minutes), so the absolute runtime is very different when all or none or just a few of these function calls happen.
  • When using DBISQL with the according option set to "Show results from the first statement", the DBISQL GUI skips over the function calls in a quite visually noticeable way. - And therefore I don't take that as a "dbisqlc-only" issue.
(07 Aug '15, 13:11) Volker Barth
Replies hidden

Further tests reveal:

  • It does not matter for DBISQL (to shift the topic back from "legacy" dbisqlc) whether the according function calls are inside a BEGIN/END block or not - if I set DBISQL's option to "Show results from the first statement" it does skip all function calls.
(10 Aug '15, 02:27) Volker Barth
0

Hello Again Volker,

Is possible you are just benefitting from "client side statement caching"? . . . and that all of the results are getting processed but you are just not seeing the PREPAREs you were expecting to see?

If that is the case then just [temporarily] setting the option max_client_statements_cached=0 will cause them to show up again.

With caching enabled (which is on by default) many of your statement PREPARES may not be showing up in the request log because those are not needed if the statement was found in the client-side cache. IE the PREPAREs are not getting logged as they have been replaced by various CACHED_DROP_STMT and VALIDATE_STMT entries instead. You may not seeing the same number of PREPARE entries but you should see the same number of OPEN operations.

{testing with verbose logging reveals everything}

HTH

permanent link

answered 07 Aug '15, 12:21

Nick%20Elson%20SAP%20SQL%20Anywhere's gravatar image

Nick Elson S...
7.3k●35●107
accept rate: 32%

Hm, that option is set to its default, however, as the individual function calls all have different arguments. Given that and the explicit NOT DETERMINISTIC clause, I would think they should not be cached at all. - I will check that after the weekend...

(07 Aug '15, 13:15) Volker Barth
Replies hidden

Further tests seem to reveal that client statement caching is not the problem here: Neither do the connection properties "ClientStmtCacheHits" and "ClientStmtCacheMisses" show relevant values nor does the behaviour change if I disable client statement caching by setting the max_client_statements_cached option to 0.

(It's still my impression that the function-call statements are "silently" skipped, see my comments on the answer answer.)

Just to clarify:

Will client statement caching cache only statements with the exact SQL test (including the exact same arguments), or would it try to cache "SELECT myFunc(1)" and "SELECT myFunc(2)", too? - Note that when using DBISQL, I do not explicitly tell the client library whether I want to use a prepared statement or not...

(10 Aug '15, 02:37) Volker Barth

I just got back from a short vacation and have noticed you have continued the discussion on another thread. Obviously the information there is interesting and shows a more subtle definition for this behavior. http://sqlanywhere-forum.sap.com/questions/25326/may-dbisql-skip-select-statements-in-a-sql-batch-part-ii

(17 Aug '15, 12:57) Nick Elson S...
Replies hidden

Yep, according to Breck's advice, I created that new FAQ - see our discussion in the comments on the question itself...

(17 Aug '15, 15:25) Volker Barth
0

Same answer in part II, but just in case:

I have found that when I have multiple select statements that call functions that return results, I have to use a begin / end and select the results into a variable to get them all to execute.

Your "SET myVar = test();" becomes my:

BEGIN
declare myVar long varchar;
select test(1) into myVar where Column1 = 1;
select test(2) into myVar where Column2 = 2;
select test(3) into myVar where Column1 = 3;
END

I had always suspected it was because DBISQL didn't have a way to handle the multiple result sets, but I never dug into it. Once I had this as the answer I used it and moved on.

Note that I'm not storing myVar and I don't care to return its results. For my case I just needed it to be sure each procedure call executed.

permanent link

answered 14 Sep '15, 12:07

Siger%20Matt's gravatar image

Siger Matt
3.3k●56●72●101
accept rate: 15%

Your answer
toggle preview

Follow this question

By Email:

Once you sign in you will be able to subscribe for any updates here

By RSS:

Answers

Answers and Comments

Markdown Basics

  • *italic* or _italic_
  • **bold** or __bold__
  • link:[text](http://url.com/ "title")
  • image?![alt text](/path/img.jpg "title")
  • numbered list: 1. Foo 2. Bar
  • to add a line break simply add two spaces to where you would like the new line to be.
  • basic HTML tags are also supported

learn more about Markdown

Question tags:

×105
×63
×28
×20

question asked: 07 Aug '15, 04:00

question was seen: 3,314 times

last updated: 14 Sep '15, 12:07

Related questions

May DBISQL skip SELECT statements in a SQL batch? - Part II

%TEMP% in SQL File?

What are the differences between DBISQL and dbisqlc w.r.t. query processing?

dbisqlc Logging Options

How to use the DBISQL import facility to import tables from a database owned by dbo?

Trouble connecting to dbisql with special characters

Does the upgrade to 12.0.1 MR reset DBISQL options?

Make DBISQL/dbisqlc ask for credentials before trying to connect

What is the effect of DBISQL option "auto_commit"?

Running a script file with dbisql (command-line)