Results 1 to 4 of 4
  1. #1
    Join Date
    Aug 2003
    Posts
    24

    Unanswered: DB2's equivalent of Index columns

    Im currently porting a MySQL DB schema to DB2.
    In MySQL, one can apply an index key to a column (not a primary/unique key).
    For example in MySQL ->
    CREATE TABLE search (
    wid int NOT NULL default '0',
    word varchar(25) NOT NULL default '',
    PRIMARY KEY (wid),
    KEY word (word)
    ) TYPE=MyISAM;

    The index key is the "word" column.

    For DB2, I ran across the "CREATE INDEX" command, but Im not sure if its working for me. Can anyone please verify if this is the "correct" way of indexing the "word" column in DB2 ->
    CREATE TABLE NULLID.search (
    wid int NOT NULL WITH DEFAULT '0',
    word varchar(25) NOT NULL WITH DEFAULT '',
    CONSTRAINT wid PRIMARY KEY (wid)
    );
    CREATE INDEX word ON NULLID.search (word);

    Thanks for any pointers you may provide.
    Last edited by trini0; 08-15-03 at 09:27.

  2. #2
    Join Date
    Aug 2001
    Location
    UK
    Posts
    4,650

    Re: DB2's equivalent of Index columns

    The DB2 Equivalent of your MySQL is

    create table search(wid int not null default 0 primary key,
    word varchar(25) not null with default)
    create index ind_word on search(word)


    My suggestion will be to avoid using NULLID as the schema of a user defined object ... If you think that is not possible, share the reason with the forum and someone may have a workaround ...

    Cheers
    Sathyaram



    Originally posted by trini0
    Im currently porting a MySQL DB schema to DB2.
    In MySQL, one can apply an index key to a column (not a primary/unique key).
    For example in MySQL ->
    CREATE TABLE search (
    wid int NOT NULL default '0',
    word varchar(25) NOT NULL default '',
    PRIMARY KEY (wid),
    KEY word (word)
    ) TYPE=MyISAM;

    The index key is the "word" column.

    For DB2, I ran across the "CREATE INDEX" command, but Im not sure if its working for me. Can anyone please verify if this is the "correct" way of indexing the "word" column in DB2 ->
    CREATE TABLE NULLID.search (
    wid int NOT NULL WITH DEFAULT '0',
    word varchar(25) NOT NULL WITH DEFAULT '',
    CONSTRAINT wid PRIMARY KEY (wid)
    );
    CREATE INDEX word ON NULLID.mpn_search (word);

    Thanks for any pointers you may provide.
    Visit the new-look IDUG Website , register to gain access to the excellent content.

  3. #3
    Join Date
    Aug 2003
    Posts
    24
    Thanks..

  4. #4
    Join Date
    Aug 2003
    Posts
    24

    Re: DB2's equivalent of Index columns

    Originally posted by sathyaram_s
    My suggestion will be to avoid using NULLID as the schema of a user defined object ... If you think that is not possible, share the reason with the forum and someone may have a workaround ...
    Ok. Basically, the php code currently can use MySQL, PostgreSQL or MSSQL, so the actual SQL statements has to be portable between Databases.
    Because of this, I cannot hardcode <schema>.<tablename> in the SQL statements.
    So what I did was use a generic value for schema that is prepended to each table (Table definations are stored in a separate file from the SQL statements).
    I could have used the user id, instead of NULLID, but that may be encroaching on security issues where a user id *may* be revealed.

    I hope I made why I chose this route clear. If you feel Im in error, Im more than open to discussion.

Posting Permissions

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