Why DB2 Command Center can not execute the following SQL statement? The error is:

DBA2191E SQL execution error.

com.ibm.db.DataException: A database manager error occurred. : [IBM][CLI Driver][DB2/6000] SQL0104N An unexpected token "END-OF-STATEMENT" was found following "entory) AS inventory". Expected tokens may include: "<table_expr>". SQLSTATE=42601


But other tools, such as Embarcardero Rapid SQL, can. What's the problem with the DB2 Command Center?

SQL Statement:

WITH SARS AS (SELECT DISTINCT c.contact_id, CONCAT ...;

When you're in the Command Center, at the bottom it should have a "Statement termination character". Can you double-check that it's set to ';'? Just a guess.

Oh, also the tool works on what's selected in the pane, make sure you haven't selected some of the statement and not the whole thing...

Hi Jonathan,

I guess that's the problem. But where can I set "Statement termination character"? My DB2 is 7.2.9. I don't see any message about "Statement termination character" at the bottom of the Command Center.

When it is executed, nothing or the entire statement is selected.

Thanks,

Unfortunately I don't have v7 on my machine, and in v8 it's right on the Command Center screen at the bottom. Maybe someone else here with v7 can point you in the right direction.

tools options : will by default indicate no terminator
in V8 the default is ;
this can be done from any gui tool (tools - options)
Best Regards, Guy Przytula

or put all in a regular file with your editor and from command window
db2 -tvf filename --> if terminator ;
otherwise
db2 -td@ -vf filename --> if terminator @ or any character at choice
Best Regards, Guy Przytula

Hi guys,

From Command window, can you get "Access Plan"?
So I need to know how to set the terminator of SQL with V7.2.9 with Command Center. Could anybody tell?

Thanks,

Hi,

you can set the "Statement termination character" when you go to the menu of the command center. Under "Tools" / "Tools Preferences" you can find the "Statement termination character" on the first page.

Best Regards, Toni

Yes,it is.

Thanks all!