Results 1 to 3 of 3
  1. #1
    Join Date
    Feb 2006
    Posts
    5

    Unanswered: SET TRANSACTION ISOLATION LEVEL not working

    Using: Command Line Processor for DB2 SDK 8.2.2

    In my C program (with embedded SQL), I can't get the
    "SET TRANSACTION ISOLATION LEVEL SERIALIZABLE" statement to work.

    Here is the C code that it's in:
    --------------------------------------

    int checkIn(void) {
    char garbage[2];
    int id = 0;

    printf("Please enter transaction number: ");
    scanf("%d", &id);
    readstdin(garbage, 2);

    sprintf(SQL, "UPDATE transaction SET onBoard = 1 WHERE transactionID = %d", id);

    // lock transaction for updating
    EXEC SQL SET TRANSACTION ISOLATION LEVEL SERIALIZABLE;

    printf("Executing SQL: '%s'\n", SQL);
    EXEC SQL EXECUTE IMMEDIATE QL;
    EMB_SQL_CHECK("update -- transaction");
    printf("- success\n");

    printf("Committing changes\n");
    EXEC SQL COMMIT;
    EMB_SQL_CHECK("commit -- update transaction");
    printf("- success\n");
    }

    --------------------------------------


    It's returning the error message (during compilation):
    "LINE MESSAGES FOR checkin.sqc
    ------ --------------------------------------------------------------------
    SQL0060W The "C" precompiler is in progress.
    43 SQL0104N An unexpected token "SET TRANSACTION" was found
    following "BEGIN-OF-STATEMENT". Expected tokens may
    include: "<set_keyword>". SQLSTATE=42601
    SQL0095N No bind file was created because of previous
    errors.
    SQL0091W Precompilation or binding was ended with "2"
    errors and "0" warnings."



    Does anyone know what's wrong with it? The syntax is the same as samples found online.

  2. #2
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    I thought that syntax it is only for Java. See the Application Programming Client Application Guide for information on how to set for C. You can always use the "WITH RR" at the end of you SQL statement to do the same thing.

    Note that isolation level normally only applies to select statements, as it determines how long a share lock is held. Exclusive locks that are a result of a insert, update, delete, or "select for update of" are held until a commit is issued (explicitly or implicitly).
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Adding to what Marcus has said, the correct syntax would be SET CURRENT ISOLATION RR. You may want to keep the DB2 SQL reference handy just in case you need to look up something else.

    By the way, why are you using dynamic SQL? A static update would be better.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •