DB2Advisor error

[Bartosz Gorzynski]

I currently test 8.2 Enterprise eval.
When i run "db2advis -d testowa -m I -f -i c:\zap.sql" all is ok and i
get index recomendation.
When i run "db2advis -d testowa -m IM -f -i c:\zap.sql" ( to show
recommended MQTs) i get information (sorry from poor translation from
polish)

SQL0104N Founds unexpected element "," after text "1048576,
000000) > 4". Dposiible elements are: "SELECTIVIT Y". SQLSTATE=42601

*************** *************** *******
* SQLCA CONTENTS *
*************** *************** *******

SQLCABC = 136
SQLCODE = -104
SQLERRMC = ,ÿ1048576,00000 0) > 4ÿSELECTIVITY

SQLERRP = SQLNP012
sqlerrd[0] = 2149187693
sqlerrd[1] = 0
sqlerrd[2] = 0
sqlerrd[3] = 0
sqlerrd[4] = 4294966591
sqlerrd[5] = 0
SQLWARN =
SQLSTATE = 42601
Critical SQLCODE error. Closing db2advis

[Ivan Popivanov]

Looks like an error in the SQL statements, there is a ",".

Ivan

Bartosz Gorzynski wrote:

I currently test 8.2 Enterprise eval.
When i run "db2advis -d testowa -m I -f -i c:\zap.sql" all is ok and i get index recomendation.
When i run "db2advis -d testowa -m IM -f -i c:\zap.sql" ( to show
recommended MQTs) i get information (sorry from poor translation from polish)

SQL0104N Founds unexpected element "," after text "1048576,
000000) > 4". Dposiible elements are: "SELECTIVIT Y". SQLSTATE=42601

*************** *************** *******
* SQLCA CONTENTS *
*************** *************** *******

SQLCABC = 136
SQLCODE = -104
SQLERRMC = ,ÿ1048576,00000 0) > 4ÿSELECTIVITY

SQLERRP = SQLNP012
sqlerrd[0] = 2149187693
sqlerrd[1] = 0
sqlerrd[2] = 0
sqlerrd[3] = 0
sqlerrd[4] = 4294966591
sqlerrd[5] = 0
SQLWARN =
SQLSTATE = 42601
Critical SQLCODE error. Closing db2advis

[Bartosz Gorzynski]

Maybe, but analysed statement is
SELECT
SPRZEDAZ.SPRZED AZ.ZAKLAD,SPRZE DAZ.SPRZEDAZ.DA TAFAK/100,SUM(SPRZEDA Z.SPRZEDAZ.WART OSCN)
FROM SPRZEDAZ.SPRZED AZ GROUP BY
SPRZEDAZ.SPRZED AZ.ZAKLAD,SPRZE DAZ.SPRZEDAZ.DA TAFAK/100 ORDER BY
SPRZEDAZ.SPRZED AZ.ZAKLAD,SPRZE DAZ.SPRZEDAZ.DA TAFAK/100;

or this same without ; using -s option (anad works in command center)
select
sprzedaz.sprzed az.zaklad,sprze daz.sprzedaz.da tafak/100,sum(sprzeda z.sprzedaz.wart oscn)
from sprzedaz.sprzed az group by
sprzedaz.sprzed az.zaklad,sprze daz.sprzedaz.da tafak/100 order by
sprzedaz.sprzed az.zaklad,sprze daz.sprzedaz.da tafak/100;

Ivan Popivanov napisa³(a):

Looks like an error in the SQL statements, there is a ",".

Ivan

Bartosz Gorzynski wrote:

I currently test 8.2 Enterprise eval.
When i run "db2advis -d testowa -m I -f -i c:\zap.sql" all is ok and

i

get index recomendation.
When i run "db2advis -d testowa -m IM -f -i c:\zap.sql" ( to show
recommended MQTs) i get information (sorry from poor translation from

polish)

SQL0104N Founds unexpected element "," after text "1048576,
000000) > 4". Dposiible elements are: "SELECTIVIT Y". SQLSTATE=42601

************* *************** *********
* SQLCA CONTENTS *
************* *************** *********

SQLCABC = 136
SQLCODE = -104
SQLERRMC = ,ÿ1048576,00000 0) > 4ÿSELECTIVITY

SQLERRP = SQLNP012
sqlerrd[0] = 2149187693
sqlerrd[1] = 0
sqlerrd[2] = 0
sqlerrd[3] = 0
sqlerrd[4] = 4294966591
sqlerrd[5] = 0
SQLWARN =
SQLSTATE = 42601
Critical SQLCODE error. Closing db2advis

[zilio]

"Bartosz Gorzynski" wrote:

Maybe, but analysed statement is
SELECT
SPRZEDAZ.SPRZED AZ.ZAKLAD,SPRZE DAZ.SPRZEDAZ.DA TAFAK/100,SUM(SPR
ZEDAZ.SPRZEDAZ. WARTOSCN)

FROM SPRZEDAZ.SPRZED AZ GROUP BY
SPRZEDAZ.SPRZED AZ.ZAKLAD,SPRZE DAZ.SPRZEDAZ.DA TAFAK/100 ORDER
BY
SPRZEDAZ.SPRZED AZ.ZAKLAD,SPRZE DAZ.SPRZEDAZ.DA TAFAK/100;

or this same without ; using -s option (anad works in command
center)
select
sprzedaz.sprzed az.zaklad,sprze daz.sprzedaz.da tafak/100,sum(spr
zedaz.sprzedaz. wartoscn)

from sprzedaz.sprzed az group by
sprzedaz.sprzed az.zaklad,sprze daz.sprzedaz.da tafak/100 order
by
sprzedaz.sprzed az.zaklad,sprze daz.sprzedaz.da tafak/100;

Ivan Popivanov napisa³(a):

Looks like an error in the SQL statements, there is a ",".

Ivan

Bartosz Gorzynski wrote:

 >>I currently test 8.2 Enterprise eval.
 >>When i run "db2advis -d testowa -m I -f -i c:zap.sql"
all is ok and

i

 >>get index recomendation.
 >>When i run "db2advis -d testowa -m IM -f -i
c:zap.sql" ( to show
 >>recomme nded MQTs) i get information (sorry from poor
translation from

 >>poli sh)
 >>
 >>SQL0104 N Founds unexpected element "," after text
"1048576,
 >>0000 00) > 4". Dposiible elements are:
"SELECTIVIT Y". SQLSTATE=42601
 >>
 >>******* *************** ***************
 >>* SQLCA CONTENTS *
 >>******* *************** ***************
 >>
 >>SQLC ABC = 136
 >>SQLC ODE = -104
 >>SQLERRM C = ,ÿ1048576,00000 0) > 4ÿSELECTIVITY
 >>
 >>SQLE RRP = SQLNP012
 >>sqle rrd[0] = 2149187693
 >>sqle rrd[1] = 0
 >>sqle rrd[2] = 0
 >>sqle rrd[3] = 0
 >>sqle rrd[4] = 4294966591
 >>sqle rrd[5] = 0
 >>SQLW ARN =
 >>SQLSTAT E = 42601
 >>Critica l SQLCODE error. Closing db2advis

This is a known problem whose fix is going into FP10 of V8.2. The PMR
that first reported this was 20433, 265, 631. The problem was
that on systems where commas are used in numbers instead of
the american decimal (e.g., 8,5 instead of 8.5), db2advis was
getting errors such as below when processing statements it
generates with floats.

If this is a statement that is in your workload the fix is to change
the commas to decimals before inputting the queries to db2advis.

If not from your workload queries, then the PMR will fix the problem.
Contact IBM DB2 service to ask for a patch if needed now.

Thanks
Danny

--
Posted using the http://www.dbforumz.com interface, at author's request
Articles individually checked for conformance to usenet standards
Topic URL: http://www.dbforumz.com/DB2-Advisor-...ict218486.html
Visit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=804336