Results 1 to 11 of 11
  1. #1
    Join Date
    Jan 2011
    Posts
    8

    Unanswered: Db2Command with Compound SQL

    I am trying to execute Db2Command where the command text contains 2 blocks of code wrapped with begin atomic example:


    --#SET TERMINATOR /
    begin atomic

    insert into B1.TESTSEQUENCE (AppSequenceId
    , AppLocalTime
    , AppSynchTime
    , Remarks
    )

    values (@AppSequenceId
    , @AppLocalTime
    , @AppSynchTime
    , @Remarks
    )
    ;
    insert into B1.TESTSEQUENCE (AppSequenceId
    , AppLocalTime
    , AppSynchTime
    , Remarks
    )

    values (@AppSequenceId_a8
    , @AppLocalTime
    , @AppSynchTime
    , @Remarks_a9
    )
    ;
    end
    /

    The error I receive is:
    ERROR [42601] [IBM][DB2/NT64] SQL0104N An unexpected token "end / begin " was found following " , ? , ? ) ; ". Expected tokens may include: "<space>". LINE NUMBER=32. SQLSTATE=42601

    Can anyone provide insight into the correct syntax for this operation.

    Using .Net Framework 4.0 IBM DB2/UDB 9.7.2.2

    Thanks

  2. #2
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    You'll need to tell whatever tool you are using that "/" is the statement delimiter. "--#SET TERMINATOR" is only valid for DB2 CLP.

  3. #3
    Join Date
    Jan 2011
    Posts
    8
    thank you very much. Would you know what the syntax for the Db2Command? Or is it a property of the command or connection?

    thanks

  4. #4
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    What's "Db2Command"?

  5. #5
    Join Date
    Jan 2011
    Posts
    8
    A Db2Command is the object that is used when communicating via .Net (IBM DataProvider). I am working on a windows application that communicates to DB2 via IBM's DataProvider for .Net.

    Db2Command is the class that contains the command to be executed.

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    It expects a single statement. You cannot execute multiple statements at once. "begin atomic ... end" is a (compound) statement. "begin atomic ... end / begin" is not.

  7. #7
    Join Date
    Jan 2011
    Posts
    8

    solution

    Wrap entire block in a BEGIN / END block:
    No Terminator char definition needed.
    No semicolon need at end of outer begin block, but they are needed
    at the end of every begin atomic block.

    BEGIN

    begin atomic

    insert into B1.TESTSEQUENCE (AppSequenceId
    , AppLocalTime
    , AppSynchTime
    , Remarks
    )

    values (@AppSequenceId
    , @AppLocalTime
    , @AppSynchTime
    , @Remarks
    )
    ;
    insert into B1.TESTSEQUENCE (AppSequenceId
    , AppLocalTime
    , AppSynchTime
    , Remarks
    )

    values (@AppSequenceId_a8
    , @AppLocalTime
    , @AppSynchTime
    , @Remarks_a9
    )
    ;
    end ;

    END

  8. #8
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    apboic, If you have DB2 LUW V9.7 you can Insert both rows with one statement:
    Code:
    INSERT INTO B1.TESTSEQUENCE
            ( AppSequenceId
            , AppLocalTime
            , AppSyncTime
            , Remarks)
      VALUES( @AppSequenceId
            , @AppLocalTime
            , @AppSynchTime
            , @Remarks
            )
           ,( @AppSequenceId_a8
            , @AppLocalTime
            , @AppSynchTime
            , @Remarks_a9
            )
    I am not sure when this became available so it may be valid in V9.5 or earlier.

    It is only one statement so it should be passed by you DB2Command. And either both rows will be inserted or they won't so it will be an Atomic transaction.

  9. #9
    Join Date
    Feb 2008
    Location
    Japan
    Posts
    3,483
    I could see multiple rows VALUES clause on DB2 5.2 for UNIX and Windows.

    IBM DB2 Universal Database SQL Reference Version 5.2
    values-clause
    Derives a result table by specifying the actual values, using expressions, for each
    column of a row in the result table. Multiple rows may be specified.

  10. #10
    Join Date
    Jan 2011
    Posts
    8
    Thanks for the help. My original example did not illustrate the complexity of what i was trying to do. I agree, the example could have been down without the begin atomic. However, if I wanted to send multiple transactions in a single Db2Command, then the syntax would be different: This is a working example: (There are 2 transx sent to the db in 1 call)

    BEGIN

    BEGIN ATOMIC
    INSERT INTO B1.TESTSEQUENCE
    ( AppSequenceId
    , AppLocalTime
    , AppSyncTime
    , Remarks)
    VALUES( @AppSequenceId
    , @AppLocalTime
    , @AppSynchTime
    , @Remarks
    )
    ,( @AppSequenceId_a8
    , @AppLocalTime
    , @AppSynchTime
    , @Remarks_a9
    );

    INSERT INTO B1.TESTSEQUENCE
    ( AppSequenceId
    , AppLocalTime
    , AppSyncTime
    , Remarks)
    VALUES( @AppSequenceId
    , @AppLocalTime
    , @AppSynchTime
    , @Remarks
    )
    ,( @AppSequenceId_a8
    , @AppLocalTime
    , @AppSynchTime
    , @Remarks_a9
    );
    END ;
    INSERT INTO B1.TESTSEQUENCE
    ( AppSequenceId
    , AppLocalTime
    , AppSyncTime
    , Remarks)
    VALUES( @AppSequenceId
    , @AppLocalTime
    , @AppSynchTime
    , @Remarks
    )
    ,( @AppSequenceId_a8
    , @AppLocalTime
    , @AppSynchTime
    , @Remarks_a9
    );
    END ;
    END

  11. #11
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by apboic View Post
    if I wanted to send multiple transactions in a single Db2Command,
    This will still be a single transaction (or even a part of one transaction), not multiple transactions.

Tags for this Thread

Posting Permissions

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