Update statistics for a table named "auto"

Hello, i have a slight problem with the update statistics (in ids 12.10.FC4_UE). We have a table named "auto" (which means "car" in french). I try to run the following queries but i get "-201 A syntax error has occurred": UPDATE STATISTICS FOR TABLE auto DROP DISTRIBUTIONS ; UPDATE STATISTICS MEDIUM FOR TABLE auto DISTRIBUTIONS ONLY; UPDATE STATISTICS HIGH FOR TABLE auto(nhop); But when i read the syntax of the update statement (http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_1278.htm), it seems right/allowed to me. Based on the syntax, "DROP DISTRIBUTIONS" comes after the "Table and Column Scope" and so i think "...FOR TABLE auto DROP..." should had been understood as a table or synonym, not the reserved keyword AUTO. Based on the syntax, "... FOR TABLE auto(nhop);" should had been read as a table(column), again not the keyword AUTO. Isn't there a bug in the query parser or an error in the syntax? Thanx in advance, Marc PS: adding the "owner." before the tablename prevents ambiguity in the statements PPS: the table was created by one of our supplier for its program under informix 9.40.FC7

Hello, Marc. AUTO is a reserverd word in Informix SQL, please confirm that you Informix Migration Guide section, in Informix Knowledge Center v12 website, explains that. You will have to rename your table, and I believe you can still use a synonym for the original one. That should allow you to update the table statistics, and still use your application (even if the best way should stop using that reserved word, anyway). Hope it helps. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: marc.honore@chirec.be > Subject: Update statistics for a table named "auto" [35019] > Date: Tue, 28 Apr 2015 08:29:01 -0400 > > Hello, > > i have a slight problem with the update statistics (in ids 12.10.FC4_UE). > > We have a table named "auto" (which means "car" in french). > > I try to run the following queries but i get "-201 A syntax error has > occurred": > > UPDATE STATISTICS FOR TABLE auto DROP DISTRIBUTIONS ; > UPDATE STATISTICS MEDIUM FOR TABLE auto DISTRIBUTIONS ONLY;> > UPDATE STATISTICS HIGH FOR TABLE auto(nhop);> > But when i read the syntax of the update statement > (http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_1278.htm), > it seems right/allowed to me. > Based on the syntax, "DROP DISTRIBUTIONS" comes after the "Table and Column > Scope" and so i think "...FOR TABLE auto DROP..." should had been understood > as a table or synonym, not the reserved keyword AUTO. > Based on the syntax, "... FOR TABLE auto(nhop);" should had been read as a > table(column), again not the keyword AUTO. > > Isn't there a bug in the query parser or an error in the syntax? > > Thanx in advance, > Marc > > PS: adding the "owner." before the tablename prevents ambiguity in the > statements > PPS: the table was created by one of our supplier for its program under > informix 9.40.FC7 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Hi, you might succeed in adressing the table as "owner".auto. Marcus Haarmann ----- Ursprüngliche Mail ----- Von: "MARC HONORE" <marc.honore@chirec.be> An: ids@iiug.org Gesendet: Dienstag, 28. April 2015 14:29:01 Betreff: Update statistics for a table named "auto" [35019] Hello, i have a slight problem with the update statistics (in ids 12.10.FC4_UE). We have a table named "auto" (which means "car" in french). I try to run the following queries but i get "-201 A syntax error has occurred": UPDATE STATISTICS FOR TABLE auto DROP DISTRIBUTIONS ; UPDATE STATISTICS MEDIUM FOR TABLE auto DISTRIBUTIONS ONLY; UPDATE STATISTICS HIGH FOR TABLE auto(nhop); But when i read the syntax of the update statement (http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_1278.htm), it seems right/allowed to me. Based on the syntax, "DROP DISTRIBUTIONS" comes after the "Table and Column Scope" and so i think "...FOR TABLE auto DROP..." should had been understood as a table or synonym, not the reserved keyword AUTO. Based on the syntax, "... FOR TABLE auto(nhop);" should had been read as a table(column), again not the keyword AUTO. Isn't there a bug in the query parser or an error in the syntax? Thanx in advance, Marc PS: adding the "owner." before the tablename prevents ambiguity in the statements PPS: the table was created by one of our supplier for its program under informix 9.40.FC7 ******************************************************************************* Forum Note: Use "Reply" to post a response in the discussion forum.

Sorry, Marc, I just looked at the reserved words list, and AUTO is not there. http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.mig.doc/ids_ mig_217.htm?lang=en-us But, take note that your actual version allows you two new methods of updating statistics. I am absolutely sure that as you've mentioned AUTO in your statement, Informix syntax diagram is identifying you are trying to run a table in AUTO mode. http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids _sqs_2044.htm?lang=en-us But, as I said before, I think my suggestion should fit your issue. Renaming the AUTO table, and creating a synonym for that should not impact your applications, and update statistics should work fine for the new name... Best regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero BRIUG website administrator Informix independent consultant From: alexandre@briug.org To: ids@iiug.org Subject: RE: Update statistics for a table named "auto" [35019] Date: Tue, 28 Apr 2015 10:11:07 -0300 Hello, Marc. AUTO is a reserverd word in Informix SQL, please confirm that you Informix Migration Guide section, in Informix Knowledge Center v12 website, explains that. You will have to rename your table, and I believe you can still use a synonym for the original one. That should allow you to update the table statistics, and still use your application (even if the best way should stop using that reserved word, anyway). Hope it helps. Regards. Alexandre Marini IBM Informix Certified Professional v10 / v11.50 / v11.70 / v12.10 IBM Information Management Informix Technical Professional IBM Certified Developer - Informix Genero BRIUG website administrator Informix independent consultant > To: ids@iiug.org > From: marc.honore@chirec.be > Subject: Update statistics for a table named "auto" [35019] > Date: Tue, 28 Apr 2015 08:29:01 -0400 > > Hello, > > i have a slight problem with the update statistics (in ids 12.10.FC4_UE). > > We have a table named "auto" (which means "car" in french). > > I try to run the following queries but i get "-201 A syntax error has > occurred": > > UPDATE STATISTICS FOR TABLE auto DROP DISTRIBUTIONS ; > UPDATE STATISTICS MEDIUM FOR TABLE auto DISTRIBUTIONS ONLY;> > UPDATE STATISTICS HIGH FOR TABLE auto(nhop);> > But when i read the syntax of the update statement > (http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_1278.htm), > it seems right/allowed to me. > Based on the syntax, "DROP DISTRIBUTIONS" comes after the "Table and Column > Scope" and so i think "...FOR TABLE auto DROP..." should had been understood > as a table or synonym, not the reserved keyword AUTO. > Based on the syntax, "... FOR TABLE auto(nhop);" should had been read as a > table(column), again not the keyword AUTO. > > Isn't there a bug in the query parser or an error in the syntax? > > Thanx in advance, > Marc > > PS: adding the "owner." before the tablename prevents ambiguity in the > statements > PPS: the table was created by one of our supplier for its program under > informix 9.40.FC7 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

You are just going to have to include the owner in the table "name" when you update statistics for it. If you use my dostats utility that is what it does for this reason and to better support ANSI mode databases. Art Art S. Kagel, President and Principal Consultant ASK Database Management www.askdbmgt.com Blog: http://informix-myview.blogspot.com/ Disclaimer: Please keep in mind that my own opinions are my own opinions and do not reflect on the IIUG, nor any other organization with which I am associated either explicitly, implicitly, or by inference. Neither do those opinions reflect those of other individuals affiliated with any entity with which I am affiliated nor those of the entities themselves. On Tue, Apr 28, 2015 at 8:29 AM, MARC HONORE <marc.honore@chirec.be> wrote: > Hello, > > i have a slight problem with the update statistics (in ids 12.10.FC4_UE). > > We have a table named "auto" (which means "car" in french). > > I try to run the following queries but i get "-201 A syntax error has > occurred": > > UPDATE STATISTICS FOR TABLE auto DROP DISTRIBUTIONS ; > UPDATE STATISTICS MEDIUM FOR TABLE auto DISTRIBUTIONS ONLY;> > UPDATE STATISTICS HIGH FOR TABLE auto(nhop);> > But when i read the syntax of the update statement > ( > http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/ids _sqs_1278.htm > ), > it seems right/allowed to me. > Based on the syntax, "DROP DISTRIBUTIONS" comes after the "Table and Column > Scope" and so i think "...FOR TABLE auto DROP..." should had been > understood > as a table or synonym, not the reserved keyword AUTO. > Based on the syntax, "... FOR TABLE auto(nhop);" should had been read as a > table(column), again not the keyword AUTO. > > Isn't there a bug in the query parser or an error in the syntax? > > Thanx in advance, > Marc > > PS: adding the "owner." before the tablename prevents ambiguity in the > statements > PPS: the table was created by one of our supplier for its program under > informix 9.40.FC7 > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a11c3e712e88a390514c9354c

On 28/04/15 13:29, MARC HONORE wrote: > Hello, > > i have a slight problem with the update statistics (in ids 12.10.FC4_UE). > > We have a table named "auto" (which means "car" in french). > > I try to run the following queries but i get "-201 A syntax error has > occurred": > > UPDATE STATISTICS FOR TABLE auto DROP DISTRIBUTIONS ; > UPDATE STATISTICS MEDIUM FOR TABLE auto DISTRIBUTIONS ONLY;> > UPDATE STATISTICS HIGH FOR TABLE auto(nhop);> > But when i read the syntax of the update statement > (http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_1278.htm), > it seems right/allowed to me. > Based on the syntax, "DROP DISTRIBUTIONS" comes after the "Table and Column > Scope" and so i think "...FOR TABLE auto DROP..." should had been understood > as a table or synonym, not the reserved keyword AUTO. > Based on the syntax, "... FOR TABLE auto(nhop);" should had been read as a > table(column), again not the keyword AUTO. > > Isn't there a bug in the query parser or an error in the syntax? > > Thanx in advance, > Marc > > PS: adding the "owner." before the tablename prevents ambiguity in the > statements > PPS: the table was created by one of our supplier for its program under > informix 9.40.FC7 You do have a point! Could you open a PMR and have a defect logged? It's easily fixable in 12.10 - 11.70 and earlier, not so much... -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm

1) Marcus and Art, "update statistics" succeeds when i qualify the tablename with the owner 2) Alexandre, it is true that AUTO is not in the "SQL keyword changes by version" but it is in the "Complete list of keywords of SQL" (unfortunatly for me^^) Thank you all for your suggestions and avices! I will contact my supplier to see what we can do with those identifiers. Best regards, Marc PS: my mistake was that i thought that the syntax diagram was supposed to disambiguate how the statement parser works but it is rather a quick reference for the syntax.

Skip the vendor and just get and use dostats. B^) Art On Apr 28, 2015 7:56 AM, "MARC HONORE" <marc.honore@chirec.be> wrote: > 1) Marcus and Art, "update statistics" succeeds when i qualify the > tablename > with the owner > > 2) Alexandre, it is true that AUTO is not in the "SQL keyword changes by > version" but it is in the "Complete list of keywords of SQL" (unfortunatly > for > me^^) > > Thank you all for your suggestions and avices! > I will contact my supplier to see what we can do with those identifiers. > > Best regards, > Marc > > PS: my mistake was that i thought that the syntax diagram was supposed to > disambiguate how the statement parser works but it is rather a quick > reference > for the syntax. > > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > --001a113ea43e53e3cd0514ca2a8a

I think i'll use dostat;) (It is not vendor, it is an home-made script based on what i read about statistics and distributions. We are migrating on informix 12 and i wanted to write a script to roughly generate the update stat after loading datas (I drop distributions on tables, do a medium update on tables for distrib only, a high update on every columns within an index and then a low update on other columns))

On 28/04/15 13:29, MARC HONORE wrote: > Hello, > > i have a slight problem with the update statistics (in ids 12.10.FC4_UE). > > We have a table named "auto" (which means "car" in french). > > I try to run the following queries but i get "-201 A syntax error has > occurred": > > UPDATE STATISTICS FOR TABLE auto DROP DISTRIBUTIONS ; > UPDATE STATISTICS MEDIUM FOR TABLE auto DISTRIBUTIONS ONLY;> > UPDATE STATISTICS HIGH FOR TABLE auto(nhop);> > But when i read the syntax of the update statement > (http://www-01.ibm.com/support/knowledgecenter/SSGU8G_12.1.0/com.ibm.sqls.doc/id s_sqs_1278.htm), > it seems right/allowed to me. > Based on the syntax, "DROP DISTRIBUTIONS" comes after the "Table and Column > Scope" and so i think "...FOR TABLE auto DROP..." should had been understood > as a table or synonym, not the reserved keyword AUTO. > Based on the syntax, "... FOR TABLE auto(nhop);" should had been read as a > table(column), again not the keyword AUTO. > > Isn't there a bug in the query parser or an error in the syntax? > > Thanx in advance, > Marc > > PS: adding the "owner." before the tablename prevents ambiguity in the > statements > PPS: the table was created by one of our supplier for its program under > informix 9.40.FC7 > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. > > I have been having a look at the parser, and I can see how the -201 comes about: UPDATE STATISTICS FOR TABLE AUTO is a perfectly valid statement, whether a table named 'auto' exists or not. What it means is "update statistics for all tables and only recalculate distributions where stale or missing". The problem here is that the parser successfully shifts all tokens up to AUTO, and then reduces the matching sentence. Since FORCE and AUTO are the last legal token in any valid update statistics statement, anything following it will generate a syntax error. On that grounds - there isn't any way to resolve that ambiguity in the parser except by using table owners or delimited identifiers. -- Ciao, Marco ______________________________________________________________________________ Marco Greco /UK /IBM Standard disclaimers apply! Structured Query Scripting Language http://www.4glworks.com/sqsl.htm 4glworks http://www.4glworks.com Informix on Linux http://www.4glworks.com/ifmxlinux.htm