Execute multiple SQL statements

In my C++ app, I use ODBC to access one of three different DBMS systems (MsSQL 2K, Sybase, and IBM DB2 v8.1).  I have a handy function I wrote that executes a SQL statement:

    SqlPrintfExec( "UPDATE Idxs SET nNextIdx=(nNextIdx+1)" );

I have another that makes a query and obtains a single data value:

    nInqIdx= SqlPrintfGetInt( "SELECT nNextIdx FROM Idxs" );

As you can see, I am trying to update a single field in a single record so that I can generate sequential unique index numbers that are used elsewhere.  

Using MsSQL, I can combine the two statements by separating them with a semicolon:

    sSql= "UPDATE Idxs SET nNextIdx=(nNextIdx+1); SELECT nNextdx FROM Idxs";
    nNextIdx= SqlPrintfGetInt( sSql );

I can't figure out how to do this with IBM DB2.   I'm needing to do it in two steps:

    SqlPrintfExec( "UPDATE Idxs SET nNextIdx=(nNextIdx+1)" );
    nInqIdx= SqlPrintfGetInt( "SELECT nNextIdx FROM Idxs" );

That means that is is *possible* that a secondary thread or process could mess me up... a different UPDATE could be executed before the SELECT.

Do you have any ideas on how to do this (an UPDATE immediatly followed by a SELECT) with IBM DB2 in a single statement?

With MsSQL, I "batch execute" large blocks of SQL (eg, 32K of INSERT statements) using the same technique (semicolons separate the statements).  This is VERY efficient -- it make my code run about 100 times faster.  But I can't make IBM DB2 do the same thing ... I must make separate calls to execute each INSERT.

Your expert help -- exspecially tried and tested solutions as opposeed to random untested suggeestions -- will be much apperciated.  Thanks!

-- Dan
LVL 50
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

No matter what DB you are using, the DB engine can only execute one statement (operation) at a time, so even if in mySQL you can combine the statement in one string, the engine executes them sequentially and not concurrently.
I think you are forgetting that the DB2 concurrency model can prevent secondary threads from updating the same record, for example if you use select some column from some table for update of a column then you effectively lock that record and prevent other users from updating it. In addition, update statements impose U locks on the rows, and U locks are not compatible with other U locks from other users.

So, change you select statement (if your IBM UDB is not running in RS isolation level and is at least v7.2) to
select.....from table..... FOR UPDATE OF .... WITH RS, the result is the row or rows are now locked and no other user can update the row(s).
You can easily see this at the command line by opening two sessions, select in one session and try and update in the other.
Another suggestion is to have DB2 automatically generate the unique number for you. This ensures the number is always unique, and can be safely retrieved after the insert without worrying about concurrency. This takes the headache out of sequencing statements and worrying about the isolation level.
Of course, this only works for inserts. I'm not sure why you are doing this as part of an update - are you creating the record first without a sequence number, then setting it afterwards?

If you need something more complex, or require a specific format for your unique identifier, then ghp7000's method is the way to go.
DanRollinsAuthor Commented:
How does the FOR UPDATE OF clause work?  That is, how can it tell that the update is complete and that the record should be unlocked?  For instance, what if I never update the specified table?

>> ... have DB2 automatically generate the unique number for you.

The database should probably have been designed with an automatically-generated (auto-increment) index value for the table, but when the project started, concurrency was not an issue -- we assumed that only one process would be adding new records, so we just used MAX(nIdx)+1 in the INSERT statement.

Then we went with this strategy of using a separate table to track the current highest index -- and that works perfectly for the other two DBMSes becasue I get back the new value with the SELECT which is executed atomically with the UPDATE.

What I need is a way to get back the new (next) index value that is certain to be unique.

But that is only part of the the larger problem... I am stuck with this "one-SQL-statement-per-call" limitation when using IBM DB2 and it is cramping my style :-)  I'm thinking there must be a simple way to concatenate SQL statements, but I can't seem to make it happen.

Cloud Class® Course: SQL Server Core 2016

This course will introduce you to SQL Server Core 2016, as well as teach you about SSMS, data tools, installation, server configuration, using Management Studio, and writing and executing queries.

the update statement is complete with you issue a commit or rollback statement, this will generate sqlcode 0 (zero). When your application receives sqlcode 0, you know the update is complete, that is committedor rolled back)

if you want to use auto increment, create a sequence (db2 create sequence statement) and reference the next highest sequence value with values(nextval)

lol you can try until you are blue in the face, but db2 wont accept a concatated sql statement

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
DanRollinsAuthor Commented:
I also thought of creating a simple Stored Procedure, but when I try, it seems to want to run a C++ or Java compiler.  

Looking into it, it appears that DB2 does not support the idea of a stored procedure consisting of just a couple of straight SQL statements.  Is that right or is the documentation just confiusing?  It is hard for me to understand because I was able to write TRIGGER procedures of just SQL statements with no problem...
Locking the index should work, e.g.

.. process resultset, get value of next ID
.. nobody else can access the record right now
UPDATE Idxs SET nNextIdx=(nNextIdx+1)

If you never commit or rollback the transaction, the table remains locked until a specified interval. DB2 has a global timeout setting after which locks are released. That's the last recourse of course, since the application is stuck in the mean time.

If you cannot have DB2 generate the ID for you, there are ways to mitigate the deadlock problem. For example, you can have the application reserve identifiers in blocks of 25, 100, 1000 etc. Using the SQL above, get the next available ID, then update with a value of (nextID + 25). You now have 25 identifiers to use before having to lock the table again. Of course, you may end up with "gaps" because not all reserved identifiers are used.

DanRollinsAuthor Commented:
That looks feasible...
In my situation, autocommit will be off only long enough to do the select after the update.

    SqlPrintfExec( "SetAutoCommit(off)" );
    SqlPrintfExec( "UPDATE Idxs SET nNextIdx=(nNextIdx+1)" );
    nInqIdx= SqlPrintfGetInt( "SELECT nNextIdx FROM Idxs" );
    SqlPrintfExec( "commit" );

Alas, SETAUTOCOMMIT does not seem to be a recognized SQL statement.
to turn autocommit on or off:

from db2 command line processor:
db2 list command options
(this tells you the current settings)
if c=-c then
dbset db2options=+c
autocommit is now off for the databases running under the same instance

to create stored procedures, you need to have c++ compiler installed
then you can write simple stored procedures using simple SQL, see the create procedure statement for help
DanRollinsAuthor Commented:
Well, I didn't find what I wanted (the FOR UPDATE OF clause usage still eludes me), but I guess I could use the CREATE SEQUENCE.


-- Dan
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.