Informix

Informix

Connect with Db2, Informix, Netezza, open source, and other data experts to gain value from your data, share insights, and solve problems.

 View Only

  • 1.  FRAGMENT BY LIST syntax error

    Posted 07/02/19 10:27 AM
    Edited by System Admin 01/20/23 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 07/02/19 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 07/02/19 11:26 AM
    Thanks Fernando. 
    Using FRAGMENT does work. 

    Best regards.

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

    Original Message


  • 4.  RE: FRAGMENT BY LIST syntax error

    Posted 07/02/19 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

    Posted 07/02/19 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.





    ------Original Message------

    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.




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

    #Informix


  • 6.  RE: FRAGMENT BY LIST syntax error

    Posted 07/05/19 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 07/05/19 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
    ------------------------------

    Original Message


  • 8.  RE: FRAGMENT BY LIST syntax error

    Posted 07/05/19 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


    ------Original Message------

    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
    ------------------------------

    #Informix


  • 9.  RE: FRAGMENT BY LIST syntax error

    Posted 07/05/19 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


    ------Original Message------

    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



    #Informix


  • 10.  RE: FRAGMENT BY LIST syntax error

    Posted 07/05/19 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


    ------Original Message------

    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



    #Informix


Related Content