Results 1 to 7 of 7

Thread: Sqlcode = -2205

  1. #1
    Join Date
    Feb 2002
    Location
    Hamilton
    Posts
    150

    Unanswered: Sqlcode = -2205

    Hello

    We run nightly maintenance job on our OnDemand database and the last three nights we have experienced the following problems.

    Unable to reorganize table arsseg. SQLCODE = -2205

    I checked for this code but was unable to locate it anywhere.

    If someone can help me with this or direct me to the proper documentation I would appreciate it

    thanks

    Mark

  2. #2
    Join Date
    Jul 2002
    Posts
    3
    Here is what db2 "? sql2205" gives as the reason.

    db2 => ? sql2205


    SQL2205N The specified index is not valid. Reason code:
    "<reason-code>".

    Explanation:

    The specified index parameter is not valid for the requested
    operation. Following is a list of reason codes:


    1 The specified index does not exist for the specified
    "<schema>"."<table-name>" or
    "<schema>"."<index-name>".

    2 The specified index is an extended index. The Reorganize Table
    utility does not support indexes based on index extensions.

    3 All indexes defined on the table must be pseudo delete enabled
    for the following operations: Inplace Table REORG, Online LOAD in
    incremental mode, REORG INDEXES with the CLEANUP ONLY option.

    4 An index other than the clustering index was specified on the
    REORG command.

    5 Inplace table REORG is not allowed if any spatial indexes
    exist on the table.

    6 An index cannot be specified for a REORG of a table that has
    one or more dimensions using the ORGANIZE BY clause of the CREATE
    TABLE command.

    Federated system users: this situation can also be detected by
    the data source.

    The utility or operation stops processing.

    User Response:

    Resubmit the command with a valid index or without an index.

  3. #3
    Join Date
    May 2012
    Posts
    2
    I have the same problem however I detected that the problem has to do with indexes associated to Primary Key constraints.

    If I alter a table to add a Primary Key constraint but the table already has a matching index that can support the constraint, DB2 doesn't recognize the index anymore.

    For example: if I try to REORG the table using that index I receive the SQL2205N error message reason code 1 (as the manual says this error should appear when the index does not exist but in this case the index does exist).

    If I ALTER the table later to drop the PK and try to DROP the index DB2 says again that the index does not exist. However if I ALTER the table again to re-create the PK DB2 DOES USE the index to support it.

    Could it be a DB2 UDB bug? I have DB2 LUW 9.7 FixPack 2 on Windows XP SP3 32 bits

    Thank you!

    Regards

  4. #4
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18

  5. #5
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18

  6. #6
    Join Date
    May 2012
    Posts
    2

    SQLcode = -2205

    Thank you very much.

    In my case, I'm running REORG command, using the DB2 CLP on Windows. I checked the APAR description but it doesn't correspond to my case because I'm specifying the fully qualified index name with schema.idxname.

    The strange situation here is that if I create a table and ALTER it to add a PRIMARY KEY without specifying a matching index created previously, I can run REORG succesfully just specifying the name of the system-generated index.

    BUT, if I create the index first with a PRIMARY KEY-matching column list, and then ALTER the table to add the key, automatically, I can't use the index any more to REORG, or even I can't DROP it after ALTERING the table to remove the PRIMARY KEY. It seems that once the index is associated with a PRIMARY KEY, it can't be referenced individually by DB2, unless it is a system-generated index! My problem is that I want to specify physical characteristics to the index like CLUSTER, PCTFREE, etc. which can't be done (I believe) inside ALTER TABLE ADD PRIMARY KEY syntax.

    Regards.

    Alejandro Gonzalez

  7. #7
    Join Date
    Apr 2012
    Posts
    1,035
    Provided Answers: 18
    I can't recreate your symptom.
    Can you give a trivial example ?
    I did 'create table', 'create unique index', 'alter table ... add constraint ... primary key (..)'.
    This re-used the previously created unique index for supporting the primary-key.
    I then populated the table.
    What combination of options to the REORG command causes you the SQL2205N ?

Posting Permissions

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