CTE is not supported by db2advis

[sandeep.iitk]

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, "[e-mail]" <[e-mail]>
wrote:

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

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

[sandeep.iitk]

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, "[e-mail]" <[e-mail]>
wrote:

Hi Lennart,
* * I will again check my query. as *ialready told you it is havinga
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