Professional Web Applications Themes

create index - IBM DB2

hi, i am trying to create index, but looks like i can do this create index AXDLIB.AXWZWL02 on AXDLIB.AXWZW ( ZWKMCD ASC) create index AXDLIB.AXWZWL03 on AXDLIB.AXWZW ( ZWKMCD ASC) so there is two different index but on same condition, why this is allowed on db2 v8.1 UDB window? is there anyway to eliminate this ? is this duplicate index will be lower down the database performance? thanks...

  1. #1

    Default create index

    hi, i am trying to create index, but looks like i can do this

    create index AXDLIB.AXWZWL02 on AXDLIB.AXWZW ( ZWKMCD ASC)
    create index AXDLIB.AXWZWL03 on AXDLIB.AXWZW ( ZWKMCD ASC)

    so there is two different index but on same condition, why this is
    allowed on db2 v8.1 UDB window?

    is there anyway to eliminate this ? is this duplicate index will be
    lower down the database performance? thanks
    xixi Guest

  2. #2

    Default Re: create index

    Can you post the DDL of the table? You should get this error message:

    SQL0605W The index was not created because the index
    "AXDLIB.AXWZWL02"
    already exists with the required description. SQLSTATE=01550

    xixi wrote:
    > hi, i am trying to create index, but looks like i can do this
    >
    > create index AXDLIB.AXWZWL02 on AXDLIB.AXWZW ( ZWKMCD ASC)
    > create index AXDLIB.AXWZWL03 on AXDLIB.AXWZW ( ZWKMCD ASC)
    >
    > so there is two different index but on same condition, why this is
    > allowed on db2 v8.1 UDB window?
    >
    > is there anyway to eliminate this ? is this duplicate index will be
    > lower down the database performance? thanks
    Blair Kenneth Adamache Guest

  3. #3

    Default Re: create index

    hello,

    i don't have problem now, since when i use the program to generate
    index, i don't see the error, but i check the server, there is no
    duplicate generate.
    xixi Guest

  4. #4

    Default create index

    hello,

    first i mistakely create a regular index on table column id, then i
    alter the table create a primary key on id, then i want to drop the
    first created regular index, but it is not allowed, is there anyway i
    can drop it ? thanks
    xixi Guest

  5. #5

    Default Re: create index

    "xixi" <dai_xi> wrote in message
    news:c0f33a17.0307071211.47dbf184posting.google.c om...
    > hello,
    >
    > first i mistakely create a regular index on table column id, then i
    > alter the table create a primary key on id, then i want to drop the
    > first created regular index, but it is not allowed, is there anyway i
    > can drop it ? thanks
    Alter table to remove primary index. Drop the index. Alter table to create
    primary key (this will automatically create a unique index on the primary
    key).


    Mark A Guest

  6. #6

    Default Re: create index

    ALTER the table to DROP the primary key, drop the index, and then
    recreate the primary key. If there are foreign keys that depend on
    primary key, they will have to be dropped/readded (or suspended).

    xixi wrote:
    > hello,
    >
    > first i mistakely create a regular index on table column id, then i
    > alter the table create a primary key on id, then i want to drop the
    > first created regular index, but it is not allowed, is there anyway i
    > can drop it ? thanks
    Blair Adamache Guest

  7. #7

    Default Re: create index


    "Mark A" <maswitchboard.net> wrote in message
    news:LPkOa.684$4n6.55435news.uswest.net...
    > "xixi" <dai_xi> wrote in message
    > news:c0f33a17.0307071211.47dbf184posting.google.c om...
    > > hello,
    > >
    > > first i mistakely create a regular index on table column id, then i
    > > alter the table create a primary key on id, then i want to drop the
    > > first created regular index, but it is not allowed, is there anyway i
    > > can drop it ? thanks
    >
    > Alter table to remove primary index. Drop the index. Alter table to create
    > primary key (this will automatically create a unique index on the primary
    > key).
    >
    First sentence should have said:

    Alter table to remove primary key.


    Mark A Guest

  8. #8

    Default Re: create index

    based on the doc, i can specify seperate table spaces for indexes ,
    how to do that on create index ?

    [email]ak_tiredofspam[/email] (AK) wrote in message news:<46e627da.0307080752.42cb3b0dposting.google. com>...
    > [email]dai_xi[/email] (xixi) wrote in message news:<c0f33a17.0307071211.47dbf184posting.google. com>...
    > > hello,
    > >
    > > first i mistakely create a regular index on table column id, then i
    > > alter the table create a primary key on id, then i want to drop the
    > > first created regular index, but it is not allowed, is there anyway i
    > > can drop it ? thanks
    >
    > xixi,
    >
    > PK is a constraint. It is implemented via an index. If an appropriate
    > index already exests at the time you are creating a PK, DB2 will reuse
    > the index and issue a warning.
    >
    > Why do you wish do drop the index? I guess it is used to implement the
    > PK constraint
    xixi Guest

  9. #9

    Default Re: create index

    You need to read CREATE TABLE in the SQL Reference - it's not done on
    the CREATE INDEX statement, it's done on the CREATE TABLE statement:

    CREATE TABLE SALARY.....
    IN ACCOUNTING INDEX IN ACCOUNT_IDX

    In the example above, ACCOUNTING is the tablespace for the table data,
    and ACCOUNT_IDX is the tablespace for the index.

    xixi wrote:
    > based on the doc, i can specify seperate table spaces for indexes ,
    > how to do that on create index ?
    >
    > [email]ak_tiredofspam[/email] (AK) wrote in message news:<46e627da.0307080752.42cb3b0dposting.google. com>...
    >
    >>dai_xi (xixi) wrote in message news:<c0f33a17.0307071211.47dbf184posting.google. com>...
    >>
    >>>hello,
    >>>
    >>>first i mistakely create a regular index on table column id, then i
    >>>alter the table create a primary key on id, then i want to drop the
    >>>first created regular index, but it is not allowed, is there anyway i
    >>>can drop it ? thanks
    >>
    >>xixi,
    >>
    >>PK is a constraint. It is implemented via an index. If an appropriate
    >>index already exests at the time you are creating a PK, DB2 will reuse
    >>the index and issue a warning.
    >>
    >>Why do you wish do drop the index? I guess it is used to implement the
    >>PK constraint
    Blair Adamache Guest

  10. #10

    Default Re: create index

    No, this is the first I have heard of these requirements. If they are
    critical to you, can you pursue them through your IBM sales rep? thanks,

    Gert van der Kooij wrote:
    > In article <bef8uc$sej$1hanover.torolab.ibm.com>,
    > [email]badamache2muchspam.[/email] says...
    >
    >>You need to read CREATE TABLE in the SQL Reference - it's not done on
    >>the CREATE INDEX statement, it's done on the CREATE TABLE statement:
    >>
    >> CREATE TABLE SALARY.....
    >> IN ACCOUNTING INDEX IN ACCOUNT_IDX
    >>
    >>In the example above, ACCOUNTING is the tablespace for the table data,
    >>and ACCOUNT_IDX is the tablespace for the index.
    >>
    >
    >
    > It would be a very valuable enhancement to be able to specify the
    > tablespace on the create index statement, also an 'alter table index
    > in <different tbspace>' would be usefull. Any change this will be
    > implemented in the next release ?
    Blair Adamache Guest

  11. #11

    Default Re: create index

    In article <befc07$smh$1hanover.torolab.ibm.com>,
    [email]badamache2muchspam.[/email] says...
    > No, this is the first I have heard of these requirements. If they are
    > critical to you, can you pursue them through your IBM sales rep? thanks,
    >
    The last time I did add a requirement it took over half a year to get
    some reaction, I wasn't very found of that.

    Besides that, it isn't critical but it could be usefull. Splitting
    indexes over multiple tablespaces means you can put them on different
    disks and use different bufferpools.
    Just my 2 cents :)
    Gert van der Kooij Guest

  12. #12

    Default Re: create index

    > > No, this is the first I have heard of these requirements. If they are
    > > critical to you, can you pursue them through your IBM sales rep? thanks,
    > >
    >
    > The last time I did add a requirement it took over half a year to get
    > some reaction, I wasn't very found of that.
    >
    > Besides that, it isn't critical but it could be usefull. Splitting
    > indexes over multiple tablespaces means you can put them on different
    > disks and use different bufferpools.
    > Just my 2 cents :)
    You can create multiple containers (on one or more disks) in a tablespace
    that is used just for an index (or multiple indexes) and achieve what you
    want.

    But the best way to get requirements known to IBM is through a user group
    like GUIDE, SHARE, or IDUG (not sure which of these has the inside track
    these days). This is much better than through an IBM sales rep. Having
    formally been DB2 technical support person in IBM Sales and Marketing, I can
    vouch for this myself.

    IBM wants the user groups to prioritize the requirements to deflect some of
    the criticism away from IBM about which enhancements are scheduled. But IBM
    does make the final decision based on what IBM thinks it will take to get
    new customers, in addition to meeting the needs of existing customers.


    Mark A Guest

  13. #13

    Default Re: create index

    In article <_2HOa.359$uX6.50661news.uswest.net>, [email]maswitchboard.net[/email]
    says...
    > > > No, this is the first I have heard of these requirements. If they are
    > > > critical to you, can you pursue them through your IBM sales rep? thanks,
    > > >
    > >
    > > The last time I did add a requirement it took over half a year to get
    > > some reaction, I wasn't very found of that.
    > >
    > > Besides that, it isn't critical but it could be usefull. Splitting
    > > indexes over multiple tablespaces means you can put them on different
    > > disks and use different bufferpools.
    > > Just my 2 cents :)
    >
    > You can create multiple containers (on one or more disks) in a tablespace
    > that is used just for an index (or multiple indexes) and achieve what you
    > want.
    >
    That's doesn't give the same possibilities if those indexes are
    defined on the same heavy used table (which I was thinking of but
    forgot to mention). But I do agree, in most cases the current features
    should provide enough options to spread IO and bufferpool usage.
    Gert van der Kooij Guest

  14. #14

    Default Re: create index

    > > You can create multiple containers (on one or more disks) in a
    tablespace
    > > that is used just for an index (or multiple indexes) and achieve what
    you
    > > want.
    > >
    >
    > That's doesn't give the same possibilities if those indexes are
    > defined on the same heavy used table (which I was thinking of but
    > forgot to mention). But I do agree, in most cases the current features
    > should provide enough options to spread IO and bufferpool usage.
    Coming from DB2 OS/390, where indexes are always in their own indexspace, I
    was a bit miffed at first that I had to explicitly define a separate
    tablespace up front in the create table if I wanted indexes in their own
    tablespace. But that is the least of my concerns right now, and there are a
    lot more important enhancements I would like to see.

    Chief among them is the ability to stop a database and/or tablespace and
    start it in read-only or utility mode like is possible on the mainframe DB2.

    Also, an online backup should be smart enough to know that if no updates
    occur on the table during the backup, then the logs should not be necessary
    for a recovery to a backup (when roll forward is not required)..


    Mark A Guest

  15. #15

    Default Re: create index

    In article <auIOa.372$uX6.56796news.uswest.net>, [email]maswitchboard.net[/email]
    says...
    > > > You can create multiple containers (on one or more disks) in a
    > tablespace
    > > > that is used just for an index (or multiple indexes) and achieve what
    > you
    > > > want.
    > > >
    > >
    > > That's doesn't give the same possibilities if those indexes are
    > > defined on the same heavy used table (which I was thinking of but
    > > forgot to mention). But I do agree, in most cases the current features
    > > should provide enough options to spread IO and bufferpool usage.
    >
    > Coming from DB2 OS/390, where indexes are always in their own indexspace, I
    > was a bit miffed at first that I had to explicitly define a separate
    > tablespace up front in the create table if I wanted indexes in their own
    > tablespace. But that is the least of my concerns right now, and there are a
    > lot more important enhancements I would like to see.
    >
    > Chief among them is the ability to stop a database and/or tablespace and
    > start it in read-only or utility mode like is possible on the mainframe DB2.
    >
    > Also, an online backup should be smart enough to know that if no updates
    > occur on the table during the backup, then the logs should not be necessary
    > for a recovery to a backup (when roll forward is not required)..
    >
    >
    Also:
    - real synchronous tion for HA
    - removing the C-compiler requirement when using SQL stored procedures
    - a fast performing Control Center with a small footprint
    - full-functional Object REXX interface (SQL and admin commands)
    - configurable system wide default table prefix for user tables



    Gert van der Kooij Guest

  16. #16

    Default Re: create index

    answers inline

    Mark A wrote:
    >>>You can create multiple containers (on one or more disks) in a
    >
    > tablespace
    >
    >>>that is used just for an index (or multiple indexes) and achieve what
    >
    > you
    >
    >>>want.
    >>>
    >>
    >>That's doesn't give the same possibilities if those indexes are
    >>defined on the same heavy used table (which I was thinking of but
    >>forgot to mention). But I do agree, in most cases the current features
    >>should provide enough options to spread IO and bufferpool usage.
    >
    >
    > Coming from DB2 OS/390, where indexes are always in their own indexspace, I
    > was a bit miffed at first that I had to explicitly define a separate
    > tablespace up front in the create table if I wanted indexes in their own
    > tablespace. But that is the least of my concerns right now, and there are a
    > lot more important enhancements I would like to see.
    >
    > Chief among them is the ability to stop a database and/or tablespace and
    > start it in read-only or utility mode like is possible on the mainframe DB2.
    Can you give us more information on why this would help? Keep in mind
    that in v8, LOAD, REORG and BACKUP are all online operations.
    >
    > Also, an online backup should be smart enough to know that if no updates
    > occur on the table during the backup, then the logs should not be necessary
    > for a recovery to a backup (when roll forward is not required)..
    >
    >
    Have a look at the v8 registry variable DB2_COLLECT_TS_REC_INFO.
    More improvements in this area are planned for Fixpak 4 and in a future
    release.

    Blair Adamache Guest

  17. #17

    Default Re: create index

    > > Chief among them is the ability to stop a database and/or tablespace and
    > > start it in read-only or utility mode like is possible on the mainframe
    DB2.
    >
    > Can you give us more information on why this would help? Keep in mind
    > that in v8, LOAD, REORG and BACKUP are all online operations.
    >
    What you mean is that a Backup has the "option" to be Online.

    The DB2 online backup for Linux, UNIX and Windows has many flaws. There are
    many horror stories about people who try to restore from a backup, but don't
    have the logs, and the restore fails. Without the logs, the online backup is
    useless.

    Online backups are very dangerous for that reason and I would never use one
    unless it was absolutely required. If I did use an online backup, I would
    supplement it with offline backups whenever it was feasible (when no users
    could connect to the database).

    Most people don't really need an online backup, except that there is often a
    problem doing an offline backup while there is a connection to the DB still
    active (even if not being used) which causes the offline backup to fail. I
    have even had trouble with the Control Center connections to cause
    contention problems with offline backups (when no other user has connected
    to the DB).

    Rather than worrying about killing all the connections, it would be
    preferable (IMO) to allow the database (or tablespace) to be stopped and
    restarted in read-only mode or utility mode that would always allow an
    offline backup to occur. Or at the very least allow an online backup to be
    created that does not require any logs to be valid (assuming that no updates
    have occurred).

    On DB2 for OS/390, it has always been possible to take a self-contained
    backup (image copy) while the database is available to applications for read
    only that does not need any logs to use in a recovery. Such a backup can be
    taken when the database is in utility-only mode, or read-only mode, or
    really can be taken any time the utility can get an exclusive lock on the
    resource (without having to worry about any active benign connections from
    other applications).

    DBA's should be able to take offline backups (or an online backup that does
    not require logs) when the database is in read-only mode.

    I really think that the Toronto team needs to look at the DB2 OS/390 backup
    and recovery capabilities and try to get some of the same functionality. A
    DB2 for OS/390 DBA could never live without the ability to start a database
    in read-only or utility-only mode.

    In general production DBA's on Linux, UNIX, and Windows spend a large
    percentage of their time dealing with contention caused by database
    connections that are not even being used. A DB2 for OS/390 DBA rarely
    worries about disconnecting threads from a database to perform DBA work
    (usually only in the case of application problem).


    Mark A Guest

  18. #18

    Default Re: create index

    Gert van der Kooij <gk-ibm-db2xs4all.nl> wrote in message news:<MPG.19754b7136fd5a69989892news.xs4all.nl>.. .
    > In article <bef8uc$sej$1hanover.torolab.ibm.com>,
    > [email]badamache2muchspam.[/email] says...
    > > You need to read CREATE TABLE in the SQL Reference - it's not done on
    > > the CREATE INDEX statement, it's done on the CREATE TABLE statement:
    > >
    > > CREATE TABLE SALARY.....
    > > IN ACCOUNTING INDEX IN ACCOUNT_IDX
    > >
    > > In the example above, ACCOUNTING is the tablespace for the table data,
    > > and ACCOUNT_IDX is the tablespace for the index.
    > >
    >
    > It would be a very valuable enhancement to be able to specify the
    > tablespace on the create index statement, also an 'alter table index
    > in <different tbspace>' would be usefull. Any change this will be
    > implemented in the next release ?
    I would completely agree.
    Oracle lets you do that no problem since when? Since Oracle 7 I guess.
    AK Guest

  19. #19

    Default Re: create index

    Okay, we hear you. Work to better package required logs with an online
    backup image is being prototyped now. We hope to get this into a future
    release.

    With respect to your comments on the GUIs, do you have specific
    criticisms about anything you've seen in v8? w.r.t. to them being
    written in Java, that decision is pretty final as long as Linux and
    Windows remain strong clients. If Linux ever went away, we would look at
    C++ again, but Linux is not going away.

    Mark A wrote:
    >>>Chief among them is the ability to stop a database and/or tablespace and
    >>>start it in read-only or utility mode like is possible on the mainframe
    >
    > DB2.
    >
    >>Can you give us more information on why this would help? Keep in mind
    >>that in v8, LOAD, REORG and BACKUP are all online operations.
    >>
    >
    >
    > What you mean is that a Backup has the "option" to be Online.
    >
    > The DB2 online backup for Linux, UNIX and Windows has many flaws. There are
    > many horror stories about people who try to restore from a backup, but don't
    > have the logs, and the restore fails. Without the logs, the online backup is
    > useless.
    >
    > Online backups are very dangerous for that reason and I would never use one
    > unless it was absolutely required. If I did use an online backup, I would
    > supplement it with offline backups whenever it was feasible (when no users
    > could connect to the database).
    >
    > Most people don't really need an online backup, except that there is often a
    > problem doing an offline backup while there is a connection to the DB still
    > active (even if not being used) which causes the offline backup to fail. I
    > have even had trouble with the Control Center connections to cause
    > contention problems with offline backups (when no other user has connected
    > to the DB).
    >
    > Rather than worrying about killing all the connections, it would be
    > preferable (IMO) to allow the database (or tablespace) to be stopped and
    > restarted in read-only mode or utility mode that would always allow an
    > offline backup to occur. Or at the very least allow an online backup to be
    > created that does not require any logs to be valid (assuming that no updates
    > have occurred).
    >
    > On DB2 for OS/390, it has always been possible to take a self-contained
    > backup (image copy) while the database is available to applications for read
    > only that does not need any logs to use in a recovery. Such a backup can be
    > taken when the database is in utility-only mode, or read-only mode, or
    > really can be taken any time the utility can get an exclusive lock on the
    > resource (without having to worry about any active benign connections from
    > other applications).
    >
    > DBA's should be able to take offline backups (or an online backup that does
    > not require logs) when the database is in read-only mode.
    >
    > I really think that the Toronto team needs to look at the DB2 OS/390 backup
    > and recovery capabilities and try to get some of the same functionality. A
    > DB2 for OS/390 DBA could never live without the ability to start a database
    > in read-only or utility-only mode.
    >
    > In general production DBA's on Linux, UNIX, and Windows spend a large
    > percentage of their time dealing with contention caused by database
    > connections that are not even being used. A DB2 for OS/390 DBA rarely
    > worries about disconnecting threads from a database to perform DBA work
    > (usually only in the case of application problem).
    >
    >
    Blair Adamache Guest

Similar Threads

  1. Trying to create an Index
    By Gabriel_Ayala@adobeforums.com in forum Adobe Indesign Windows
    Replies: 0
    Last Post: April 22nd, 07:59 PM
  2. create index stalls entire informix server
    By Steven Kurlander in forum Informix
    Replies: 3
    Last Post: October 13th, 04:18 PM
  3. Nested mySQL queries create issue with validity of result index?
    By jerrygarciuh in forum PHP Development
    Replies: 6
    Last Post: September 8th, 10:09 PM
  4. Logging with CREATE INDEX
    By Andy Kent in forum Informix
    Replies: 1
    Last Post: July 29th, 08:53 PM
  5. Create index VS Set Index Enabled - IDS 7.31
    By Stéphane Gadoury in forum Informix
    Replies: 2
    Last Post: July 21st, 02:00 AM

Bookmarks

Posting Permissions

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

1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 69 70 71 72 73 74 75 76 77 78 79 80 81 82 83 84 85 86 87 88 89 90 91 92 93 94 95 96 97 98 99 100 101 102 103 104 105 106 107 108 109 110 111 112 113 114 115 116 117 118 119 120 121 122 123 124 125 126 127 128 129 130 131 132 133 134 135 136 137 138 139