FRAGMENT BY LIST syntax error
Luis Marques got error 201 using PARTITION BY LIST in CREATE TABLE on 14.10FC1. Fernando Nunes and Art Kagel said the engine accepts only FRAGMENT BY LIST, which worked. Luis and Art argued the documentation syntax diagram wrongly presents PARTITION BY as equivalent. Pradeep Natarajan asked what was missing. The mismatch was left unresolved.
Auto-generated by Claude from the posts below — may be imperfect; read the full thread.
Using Informix 14.10FC1 I am trying to create a table partitioned by LIST, as described in the documentation:
FRAGMENT BY clause
List fragment clause
CREATE TABLE dummy
(
id INT,
val CHAR(2)
)
PARTITION BY LIST ( val )
PARTITION dummy01 VALUES ( 'A' ) IN dbs1,PARTITION dummy02 VALUES ( 'B' ) IN dbs2
;
This simple example is returning:
201: A syntax error has occurred.Can someone provide a working example ? I keep reading the documentation, but I am not seeing what is the mistake I am making in this DDL.
EDIT:
Followed Fernando sugestion, and using FRAGMENT works ( I was too fixated on using the PARTITION keyword ).
CREATE TABLE dummy
(
id INT,
val CHAR(2)
)
FRAGMENT BY LIST ( val )
PARTITION dummy01 VALUES ( 'A' ) IN dbs1,PARTITION dummy02 VALUES ( 'B' ) IN dbs2
;
However, it does seem that either the documentation or the Informix syntax parser needs to be corrected.
------------------------------
Luis Marques
------------------------------
#Informix
Didn't try it... but: CREATE TABLE ... FRAGMENT BY LIST ... instead of "PARTITION BY LIST" Regards. ------------------------------ FERNANDO NUNES ------------------------------
Thanks Fernando. Using FRAGMENT does work. Best regards. ------------------------------ Luis Marques ------------------------------
Try "CREATE TABLE ... FRAGMENT BY LIST ..." instead of "PARTITION BY LIST" (didn't try it myself...) Regards. ------------------------------ FERNANDO NUNES ------------------------------
The correct syntax would be:
CREATE TABLE dummy
(
id INT,
val CHAR(2)
)
FRAGMENT BY LIST ( val )
PARTITION dummy01 VALUES ( 'A' ) IN dbs1,PARTITION dummy02 VALUES ( 'B' ) IN dbs2
;
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.
Luis, I am not sure I understand what correction in the documentation (or the parser) you are recommending. As shown in the documentation and in your (and Art's) modified SQL statement, when you use "FRAGMENT BY" clause it is working fine. Is there something else you feel missing in the documentation? ------------------------------ Pradeep Natarajan Head of Engineering - Informix ------------------------------
Pradeep, If I am correctly interpreting the the syntax diagram in the online documentation ( FRAGMENT BY clause ) them I should also be able to use "PARTITION BY LIST", as they are described as being equivalent: FRAGMENT BY clause for tables |--+-------------+--+-FRAGMENT -+--BY---------------------------> '-WITH ROWIDS-' '-PARTITION ' .-,-------. V | >--+-ROUND ROBIN--+-IN----dbspace-+-----------------------+------+--| | | .-,---------------------------------. | | | | V | | | | '---PARTITION--partition--IN--dbspace-+-' | | (1) | +-EXPRESSION--| Expression Fragment Clause |------------------+ | (2) | +-RANGE--(--fragment_key--)--| Interval Fragment Clause |-----+ | (3) | '-LIST--(--fragment_key--)--| List Fragment Clause |----------' ------------------------------ Luis Marques ------------------------------
Pradeep: The issue is that the documention says that you can use the keyword PARTITION BY and FRAGMENT BY interchangably and that these two syntaxes areboth acceptable and identical in effect: create table ...( ... ) partition by list ... create table ... ( ... ) fragment by list ... Yet, that is not the case. The engine does not accept PARTITION BY LIST, only FRAGMENT BY LIST. 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.
FWIW, here is the text from the SQL Syntax Guide manual page. I have highlighted the contested syntax: FRAGMENT BY clause for tables |--+-------------+--+- FRAGMENT -+--BY---------------------------> '-WITH ROWIDS-' '- PARTITION ' .-,-------. V | >--+-ROUND ROBIN--+-IN---- dbspace -+-----------------------+------+--| | | .-,---------------------------------. | | | | V | | | | '---PARTITION-- partition --IN-- dbspace -+-' | | (1) | +-EXPRESSION--| Expression Fragment Clause |------------------+ | (2) | +-RANGE--(-- fragment_key --)--| Interval Fragment Clause |-----+ | (3) | '-LIST--(-- fragment_key --)--| List Fragment Clause |----------' 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.
Hmm, that got messed up by the forum software. It should look some thing like this: FRAGMENT BY clause for tables: '-WITH ROWIDS-' |--+-------------+--+- FRAGMENT -+--BY---------------------------> |_ PARTITION _| >--+-ROUND ROBIN--+-IN---- dbspace -+-----------------------+------+--| >--+-EXPRESSION--| Expression Fragment Clause |------------------+ >--+-RANGE--(-- fragment_key --)--| Interval Fragment Clause |-----+ >--+LIST--(-- fragment_key --)--| List Fragment Clause |----------' 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.