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

You'll need to tell whatever tool you are using that "/" is the statement delimiter. "--#SET TERMINATOR" is only valid for DB2 CLP.

thank you very much. Would you know what the syntax for the Db2Command? Or is it a property of the command or connection?

thanks

What's "Db2Command"?

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.

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.

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

apboic, If you have DB2 LUW V9.7 you can Insert both rows with one statement:
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.

I could see multiple rows VALUES clause on DB2 5.2 for UNIX and Windows.

IBM DB2 Universal Database SQL Reference Version 5.2

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

This will still be a single transaction (or even a part of one transaction), not multiple transactions.