Results 1 to 6 of 6
  1. #1
    Join Date
    Jan 2003
    Posts
    1,605

    Unanswered: Reorg returns error when index is longer then 20 characters

    Hi,
    I am trying to run "reorg table" command from db2cmd on DB2 v9.5 fixpack 2a on Linux but getting the following error:
    Code:
    db2 REORG TABLE IMP.PLNSIZDELEK_SKUPINA INDEX IMP.PK_SKUSIZDELEK_SKUPINA
    SQL0104N  An unexpected token "IMP.PK_SKUSIZDELEK_SKUPINA" was found following
    "INDEX".  Expected tokens may include:  "<valid-index-name>".  SQLSTATE=42601
    In help there is message:
    Code:
    SQL0104N  An unexpected token "<token>" was found following "<text>".
          Expected tokens may include: "<token-list>".
    
    Explanation:
    
    A syntax error in the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure was detected at the specified token
    following the text "<text>". The "<text>" field indicates the 20
    characters of the SQL statement or the input command string for the
    SYSPROC.ADMIN_CMD procedure that preceded the token that is not valid.
    
    As an aid, a partial list of valid tokens is provided in the SQLERRM
    field of the SQLCA as "<token-list>". This list assumes the statement is
    correct to that point.
    
    The statement cannot be processed.
    
    User response:
    
    Examine and correct the statement in the area of the specified token.
    
     sqlcode: -104
    
     sqlstate: 42601
    I have checked index name for my table:
    select indschema, indname from syscat.indexes where tabschema='IMP' and TABNAME='PLNSIZDELEK_SKUPINA'
    Code:
    INDSCHEMA INDNAME
    --------- ------------------------------------
    IMP       PK_SKUSIZDELEK_SKUPINA
    It looks like when creating index there is no limitation to 20 characters for index name, but when reorg is executed name must not be more then 20 characters long. It looks like a joke to me. Is this a bug or something?
    Regars

  2. #2
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    It could be that linux is doing something to the command to cause this error. Try this:

    Code:
    db2 "REORG TABLE IMP.PLNSIZDELEK_SKUPINA INDEX IMP.PK_SKUSIZDELEK_SKUPINA"
    Andy

  3. #3
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Is IMP.PLNSIZDELEK_SKUPINA a regular table?
    ---
    "It does not work" is not a valid problem statement.

  4. #4
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    SQL0104 is a syntax issue. If it were semantically, wrong (table/index doesn't exist, etc.) you'd get some error in the SQL02xx range or something else altogether.

    In any case, the statement is working fine for me on DB2 9.7 on Linux. So either the was some screw-up by the shell as Andy suggested, or this issue has been fixed in more recent versions on DB2 LUW.
    Code:
    $ db2 "REORG TABLE IMP.PLNSIZDELEK_SKUPINA INDEX IMP.PK_SKUSIZDELEK_SKUPINA"
    SQL2211N  The specified table does not exist.
    $ db2 "create table IMP.PLNSIZDELEK_SKUPINA ( a int )"
    DB20000I  The SQL command completed successfully.
    $ db2 "create index IMP.PK_SKUSIZDELEK_SKUPINA on IMP.PLNSIZDELEK_SKUPINA(a)"
    DB20000I  The SQL command completed successfully.
    $ db2 "REORG TABLE IMP.PLNSIZDELEK_SKUPINA INDEX IMP.PK_SKUSIZDELEK_SKUPINA"
    DB20000I  The REORG command completed successfully.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  5. #5
    Join Date
    Jan 2003
    Posts
    1,605
    Actually I have run this command from db2cmd from Windows connecting to Linux db2 v9.5. Sorry for not being clear enough.

    Now I have tried to run this command (with and without quotation marks) from db2-server on Linux and it works fine. Also tried from other Windows db2 client and it works fine.


    On this particular computer I am using db2 v8.2 fp9 client connection to several different databases. I know v8.2 is not officially supported anymore, but I am stuck with this client, because our warehouse is running on DB2 v8.2 using Warehouse Center which was removed from any latter version of DB2.

    OK, what I have learned this time, it looks like DB2 clients are performing some kind of syntax checking before passing command to DB2 server, is it? I was strongly convinced that db2 client just passes the command to db2 server and db2 server checks for syntax and executes the query.

    Thanks for help

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    The command line processor parses and executes REORG and other commands (e.g. LOAD, RUNSTATS, etc.) that are not SQL statements. Only SQL statements are passed to the server for parsing and execution.
    ---
    "It does not work" is not a valid problem statement.

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •