CTE is not supported by db2advis

[sandee...@gmail.com]

I am using a query into db2advis for index recommendation. this query
is having a CTE (with clause). when I run db2advis it returned some
error like

Database is reporting the following error:
excluding statement [0] from the workload.

Error. No valid statements were found in this input file or in the
workload table.

I am using following statement to run it.

db2advis -d dbname -l 1000 -n schemaname -m I -i inputfile

while this syntax is working fine for other sql statement without CTE.

please help

Thanks
Sandy

[Lennart]

On 17 Okt, 23:55, "sandeep.i...@gmail.com" <sandeep.i...@gmail.com>
wrote:

You don't mention db2 version and platform, but AFAIK it should work.
There is likely some other error in your inputfile such as a line
break, or a missing stmt terminator. If you can't find out what it is,
try posting the content of the inputfile.

/Lennart

[sandee...@gmail.com]

Hi Lennart,
I will again check my query. as ialready told you it is having a
CTE expression. so it is a rather big query . which is not coming in a
single line so i am writing it in multiple lines. Is there any line
break symbol in db2. if yes please guide me. FYI i am using stmt
completion terminator. I am on db2 v8.2 on AIX.
Certainly line break is there in input file. may be that is the
reason of problem.

Thanks
Sandy

[Lennart]

On 19 Okt, 17:30, "sandeep.i...@gmail.com" <sandeep.i...@gmail.com>
wrote:

> Hi Lennart,
>     I will again check my query. as  ialready told you it is having a
> CTE expression. so it is a rather big query . which is not coming in

> single line so i am writing it in multiple lines.

That is fine as long as you don't break a line in the middle of say a
table name. FWIW nothing prevents one from creating a very long
line :-)

>Is there any line
> break symbol in db2. if yes please guide me. FYI i am using stmt
> completion terminator. I am on db2 v8.2 on AIX.
>    Certainly line break is there in input file. may be that is the
> reason of problem.
>

The default terminator is ; so as long as you are using that in your
file you should be fine.

Try creating a simple CTE to see whether you can get that to work
first

/Lennart