Informix

 View Only
  • 1.  FRAGMENT BY LIST syntax error

    Posted Tue July 02, 2019 10:27 AM
    Edited by System Fri January 20, 2023 04:31 PM
    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


  • 2.  RE: FRAGMENT BY LIST syntax error

    Posted Tue July 02, 2019 11:04 AM

    Didn't try it... but:

    CREATE TABLE ... FRAGMENT BY LIST ...

    instead of "PARTITION BY LIST"

    Regards.



    ------------------------------
    FERNANDO NUNES
    ------------------------------



  • 3.  RE: FRAGMENT BY LIST syntax error

    Posted Tue July 02, 2019 11:26 AM
    Thanks Fernando. 
    Using FRAGMENT does work. 

    Best regards.

    ------------------------------
    Luis Marques
    ------------------------------



  • 4.  RE: FRAGMENT BY LIST syntax error

    Posted Tue July 02, 2019 12:36 PM

    Try "CREATE TABLE ... FRAGMENT BY LIST ..." instead of "PARTITION BY LIST"

    (didn't try it myself...)
    Regards.



    ------------------------------
    FERNANDO NUNES
    ------------------------------



  • 5.  RE: FRAGMENT BY LIST syntax error

    IBM Champion
    Posted Tue July 02, 2019 12:36 PM
    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


    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.








  • 6.  RE: FRAGMENT BY LIST syntax error

    Posted Fri July 05, 2019 10:55 AM
    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
    ------------------------------



  • 7.  RE: FRAGMENT BY LIST syntax error

    Posted Fri July 05, 2019 11:11 AM
    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
    ------------------------------



  • 8.  RE: FRAGMENT BY LIST syntax error

    IBM Champion
    Posted Fri July 05, 2019 04:16 PM
    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


    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.




    Virus-free. www.avast.com





  • 9.  RE: FRAGMENT BY LIST syntax error

    IBM Champion
    Posted Fri July 05, 2019 04:18 PM
    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


    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.




    Virus-free. www.avast.com





  • 10.  RE: FRAGMENT BY LIST syntax error

    IBM Champion
    Posted Fri July 05, 2019 04:24 PM
    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


    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.




    Virus-free. www.avast.com