Results 1 to 5 of 5

Thread: DB2 performance

  1. #1
    Join Date
    Nov 2002
    Posts
    12

    Question Unanswered: DB2 performance

    At the moment I using a sequence number using a table. I select the number and then update increasing by 1. I am having performance issue.

    How can I use the sequence number in one sql statement. Is there any other alternatives of this.

  2. #2
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Use a sequence object.


    CREATE SEQUENCE YOURSEQUENCE AS INTEGER
    MINVALUE 1 MAXVALUE 2147483647
    START WITH 1 INCREMENT BY 1
    CACHE 12 NO CYCLE NO ORDER;

    INSERT INTO YOURTABLE
    VALUES (NEXTVAL FOR YOURSEQUENCE);

    ... or use an IDENTITY column

    CREATE TABLE YOURTABLE
    (COL1 INT NOT NULL GENERATED ALWAYS AS IDENTITY);

    Use a sequence in the kind of scenario where you would retrieve the sequence value through an application for update/insert into multiple tables.

    Use the identity column if you simply require a sequential key in your table.

  3. #3
    Join Date
    Sep 2002
    Posts
    456

    Re: DB2 performance

    Please provide the version number of RDBMS!!

    Paul

    Originally posted by skplipa
    At the moment I using a sequence number using a table. I select the number and then update increasing by 1. I am having performance issue.

    How can I use the sequence number in one sql statement. Is there any other alternatives of this.

  4. #4
    Join Date
    Jun 2002
    Location
    UK
    Posts
    525
    Use a sequence object.


    CREATE SEQUENCE YOURSEQUENCE AS INTEGER
    MINVALUE 1 MAXVALUE 2147483647
    START WITH 1 INCREMENT BY 1
    CACHE 12 NO CYCLE NO ORDER;

    INSERT INTO YOURTABLE (COL1)
    VALUES (NEXTVAL FOR YOURSEQUENCE);

    ... or use an IDENTITY column

    CREATE TABLE YOURTABLE
    (COL1 INT NOT NULL GENERATED ALWAYS AS IDENTITY);

    Use a sequence in the kind of scenario where you would retrieve the sequence value through an application for update/insert into multiple tables.

    Use the identity column if you simply require a sequential key in your table.

  5. #5
    Join Date
    Nov 2002
    Posts
    12
    Thanks for a quick reply.

    I am using Sybase ASE 11.5 which connects to DB2 V6 in OS390.

    In sybase, DB2 tables are mapper and used as proxy tables.
    In my sybase procedure, I am getting an unique number from a DB2 table incrementing by 1. Then this number is being used to insert into 3 other tables. I am not sure if I can use this SEQUENCE object in sybase. Is there any idea?

Posting Permissions

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