Results 1 to 11 of 11
  1. #1
    Join Date
    Apr 2004
    Posts
    54

    Unanswered: DB2 Exp-C UDB v9.7.1 64bit, is it possible to create INDEX in a different tablespace?

    Hello everyone!!
    I'm trying to create one index using follow statement:

    CREATE UNIQUE INDEX XAK1TABLE ON TABLE ("CNAME" ASC) IN TB16

    but the answer is:
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0109N The "IN" clause is not allowed. SQLSTATE=42601

    System used is DB2 Exp-C UDB v9.7.1 64bit on Linux.

    Here some examples that I found on Infocenter (http://publib.boulder.ibm.com/infoce...r7/index.jsp):
    Example 8: Create a unique index named A_IDX on a partitioned table named MYNUMBERDATA in table space IDX_TBSP.

    CREATE UNIQUE INDEX A_IDX ON MYNUMBERDATA (A) IN IDX_TBSP

    Example 9: Create a non-unique index named B_IDX on a partitioned table named MYNUMBERDATA in table space IDX_TBSP.

    CREATE INDEX B_IDX ON MYNUMBERDATA (B)
    NOT PARTITIONED IN IDX_TBSP



    Have you idea how fix it??

    Thank you in advance!
    Fabio

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    petitof, I believe it depends on the type of table space being used. With a DMS table space you can put tables and indexes in separate table spaces. With a SMS table space, the table and index must be in the same table space.

  3. #3
    Join Date
    Nov 2005
    Location
    IL
    Posts
    557
    In your statement

    CREATE UNIQUE INDEX XAK1TABLE ON TABLE ("CNAME" ASC) IN TB16
    Is your table name = TABLE? If not, then try using an actual table name.

    And your table has to be partitioned in order for this clause to work.
    --
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows

    DB2 v9.7.0.6 os 6.1.0.0

  4. #4
    Join Date
    Apr 2004
    Posts
    54
    Hi!! Thank you for the answers!

    X Stealth_DBA: I tried to do it with DMS (thanks for the tip, I didn't know) but the output doesn't change:
    DB21034E The command was processed as an SQL statement because it was not a
    valid Command Line Processor command. During SQL processing it returned:
    SQL0109N The "IN" clause is not allowed. SQLSTATE=42601

    Here the statement that I have used to create DMS tablespace (sql command from Control Center client 9.7.1):
    CREATE REGULAR TABLESPACE <name tablespace> PAGESIZE 4 K MANAGED BY DATABASE USING ( DEVICE '/home/db2inst1/db2/tablespaces/database/<name tablespace>' 25600 ) EXTENTSIZE 16 OVERHEAD 10.5 PREFETCHSIZE 16 TRANSFERRATE 0.14 BUFFERPOOL BF4GR DROPPED TABLE RECOVERY ON;


    X Cougar8000: Yes TABLE is the name of the table, and it's not partitioned! I have to do it???

    Thank you!
    Fabio

  5. #5
    Join Date
    Apr 2004
    Posts
    54
    Could it be a limitation of DB2 Express-C?

  6. #6
    Join Date
    Jun 2003
    Location
    Toronto, Canada
    Posts
    5,516
    Provided Answers: 1
    Quote Originally Posted by petitof View Post
    X Cougar8000: Yes TABLE is the name of the table, and it's not partitioned! I have to do it???
    You wouldn't be so surprised if you had a chance to read the fine manual:
    Quote Originally Posted by RTFM
    IN tablespace-name
    Specifies the table space in which the index is to be created. This clause is only supported for indexes on partitioned tables. You can specify this clause even if the INDEX IN clause was specified when the table was created. This will override that clause.
    ---
    "It does not work" is not a valid problem statement.

  7. #7
    Join Date
    Apr 2004
    Posts
    54
    Thank you, n_i!
    But you should see the Infocenter of the correct release, in fact:

    "This clause is not supported for indexes on a created temporary table or a declared temporary table (SQLSTATE 42601)"

    so, is "temporary table" equal to partitioned tables? Just to confirm, can I know the source of the quote that you gave me? Maybe is it relative oldest release of DB2?

    From: http://publib.boulder.ibm.com/infoce.../r0000919.html

    that is:

    IN tablespace-name
    The IN clause is supported only for nonpartitioned indexes. Specifying the IN clause for partitioned indexes results in SQLSTATE 42601.

    Specifies the table space in which the index is to be created. This clause is not supported for indexes on a created temporary table or a declared temporary table (SQLSTATE 42601). You can specify this clause even if the INDEX IN clause was specified when the table was created. This will override that clause.

    The table space specified by tablespace-name must be in the same database partition group as the data table spaces for the table and manage space in the same way as the other table spaces of the partitioned table (SQLSTATE 42838); it must be a table space on which the authorization ID of the statement holds the USE privilege.

    If the IN clause is not specified, the index is created in the table space that was specified by the INDEX IN clause on the CREATE TABLE statement. If no INDEX IN clause was specified, the table space of the first visible or attached data partition of the table is used. This is the first partition in the list of data partitions that are sorted on the basis of range specifications. If the IN clause is not specified, the authorization ID of the statement is not required to have the USE privilege on the default table space.



    So: Is "This clause is not supported for indexes on a created temporary table or a declared temporary table (SQLSTATE 42601)" equal to


    Thank you in advance!
    Fabio

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    Quote Originally Posted by petitof View Post
    IN tablespace-name
    The IN clause is supported only for nonpartitioned indexes. Specifying the IN clause for partitioned indexes results in SQLSTATE 42601.
    I believe this is not accurate.


    Please take a look at Note #11 here:
    IBM DB2 9.7 for Linux, UNIX and Windows Information Center


    ....If the table is a partitioned table, the index table space for a nonpartitioned index can be specified with the IN clause of the CREATE INDEX statement.

  9. #9
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    db2 "create table test (c1 int, c2 int) in dmst partition by range(c1) (starting 1 ending 10)"
    DB20000I The SQL command completed successfully.

    db2 "create index testi on test (c2) not partitioned in dmsi"
    DB20000I The SQL command completed successfully.

  10. #10
    Join Date
    Apr 2004
    Posts
    54
    thank you, db2girl!!
    I also made a test specifying "AUTOMATIC STORAGE NO" during the creation of the database, but nothing is changed.

    thanks to all!!

    Fabio

  11. #11
    Join Date
    Jan 2010
    Posts
    335
    Do you have Tables with Range Partitioning or temporary Tables?

    You have to specify the Tablespace for the Indexes with the 'create table' Statement. The tablespace-type has to be DMS and it does not matter if you use AUTOMATIC STORAGE or not.

Posting Permissions

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