Solved

Execute multiple SQL statements

Posted on 2004-10-29
653 Views
Last Modified: 2012-05-05
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?

Related:  
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
0
Question by:DanRollins
    9 Comments
     
    LVL 13

    Expert Comment

    by:ghp7000
    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.
    0
     
    LVL 4

    Expert Comment

    by:pluim
    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.
    0
     
    LVL 49

    Author Comment

    by:DanRollins
    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.

    --Dan
    0
     
    LVL 13

    Accepted Solution

    by:
    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
    0
     
    LVL 49

    Author Comment

    by:DanRollins
    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...
    0
     
    LVL 4

    Assisted Solution

    by:pluim
    Locking the index should work, e.g.

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

    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.

    0
     
    LVL 49

    Author Comment

    by:DanRollins
    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.
    0
     
    LVL 13

    Expert Comment

    by:ghp7000
    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
    0
     
    LVL 49

    Author Comment

    by:DanRollins
    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.

    Thanks.

    -- Dan
    0

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    Course: JavaScript Coding - Massive 12-Part Bundle

    Regardless of your programming skill level, you'll go from basics to advanced concepts in a vast array of JavaScript subjects including Sammy.js, Agility.js, Ember.js, Node.js, jQuery, AJAX, Extjs, AngularJS, Knockout.js, and JSON.

    Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
    Recursive SQL in UDB/LUW (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
    This video is in connection to the article "The case of a missing mobile phone (https://www.experts-exchange.com/articles/28474/The-Case-of-a-Missing-Mobile-Phone.html)". It will help one to understand clearly the steps to track a lost android phone.
    In this seventh video of the Xpdf series, we discuss and demonstrate the PDFfonts utility, which lists all the fonts used in a PDF file. It does this via a command line interface, making it suitable for use in programs, scripts, batch files — any pl…

    846 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    9 Experts available now in Live!

    Get 1:1 Help Now