Results 1 to 7 of 7
  1. #1
    Join Date
    Nov 2011
    Posts
    24

    Unanswered: incrementing numbers for insert statement

    DB2 Z/OS VERSION 10.1

    create table mytable (
    sno int not null,
    desc varchar(15)
    )
    sno has unique index.

    I have a insert statement that gets its values from a select statement

    insert into mytable
    (desc)
    select column2 from table2;

    Is there a way I can populate the sno value with sequential numbers without using identity/sequence number/rownumber?
    Essentially something like i+1?

    I tried the following, (table2 has 10 rows)
    but all the rows selected have the same value (2) and index is violated...
    declate i int;
    set i = 1;
    insert into mytable
    (sno,desc)
    select i+1,column2 from table2;

    If its possible what would the correct syntax be?

  2. #2
    Join Date
    May 2009
    Posts
    508
    Provided Answers: 1
    techday, I am not in a place to test this out but this should work:
    Code:
    INSERT INTO MYTABLE
    WITH MAX_TAB(SNO_MAX)
      AS (
          SELECT MAX(SNO)
          FROM MYTABLE
         )
    SELECT ROW_NUMBER() OVER(ORDER BY COLUMN2) + SNO_MAX, COLUMN2
    FROM MAX_TAB
       , TABLE2
    However, if more than one person is running the process at one time, there could be situations where SNO value is duplicated.

  3. #3
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    This should work for DB2 LUW (but not sure about DB2 z/OS) :

    Code:
    CREATE TABLE "DBA     "."VENDOR"  (
                      "VENDOR_ID" INTEGER NOT NULL ,
                      "VENDOR_NM" VARCHAR(25) )
                     IN "USERSPACE1" ;
    
    ALTER TABLE "DBA     "."VENDOR"
            ADD CONSTRAINT "PK_VENDOR" PRIMARY KEY
                    ("VENDOR_ID");
    
    CREATE TABLE "DBA     "."NEXT_AVAIL_ID"  (
                      "APPLICATION_ID" SMALLINT NOT NULL ,
                      "NEXT_ID" INTEGER NOT NULL )
                     IN "USERSPACE1" ;
    
    ALTER TABLE "DBA     "."NEXT_AVAIL_ID"
            ADD CONSTRAINT "PK_NEXT_AVAIL_ID" PRIMARY KEY
                    ("APPLICATION_ID");
    
    
    create procedure dba.test ()
    begin
    declare v_next_id int;
    set v_next_id = (select next_id from final table (update dba.next_avail_id set next_id = next_id + 1 where application_id = 1));
    insert into dba.vendor values (v_next_id, 'IBM');
    commit;
    end@
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  4. #4
    Join Date
    Nov 2011
    Posts
    24
    Quote Originally Posted by Stealth_DBA View Post
    techday, I am not in a place to test this out but this should work:
    Code:
    INSERT INTO MYTABLE
    WITH MAX_TAB(SNO_MAX)
      AS (
          SELECT MAX(SNO)
          FROM MYTABLE
         )
    SELECT ROW_NUMBER() OVER(ORDER BY COLUMN2) + SNO_MAX, COLUMN2
    FROM MAX_TAB
       , TABLE2
    However, if more than one person is running the process at one time, there could be situations where SNO value is duplicated.
    Stealth, that was a nice trick to add to row number.
    But I do not want to use row number...
    Also, small modifcation to do IFNULL in case mytable has no records..
    Code:
    INSERT INTO MYTABLE
    WITH MAX_TAB(SNO_MAX)
      AS (
          SELECT IFNULL(MAX(SNO),0)
          FROM MYTABLE
         )
    SELECT ROW_NUMBER() OVER(ORDER BY COLUMN2) + SNO_MAX, COLUMN2
    FROM MAX_TAB
       , TABLE2

  5. #5
    Join Date
    Nov 2011
    Posts
    24
    Quote Originally Posted by Marcus_A View Post
    This should work for DB2 LUW (but not sure about DB2 z/OS) :

    Code:
    create procedure dba.test ()
    begin
    declare v_next_id int;
    set v_next_id = (select next_id from final table (update dba.next_avail_id set next_id = next_id + 1 where application_id = 1));
    insert into dba.vendor values (v_next_id, 'IBM');
    commit;
    Marcus, not sure how this works..
    First we need to have a row inserted in dba.next_avail_id with application_id 1.?
    Even after this, I think only one row is going to have the next seq number.
    But i need to assign seq num for mutiple rows with source as another select.

  6. #6
    Join Date
    May 2003
    Location
    USA
    Posts
    5,737
    Quote Originally Posted by techday View Post
    Marcus, not sure how this works..
    First we need to have a row inserted in dba.next_avail_id with application_id 1.?
    Even after this, I think only one row is going to have the next seq number.
    But i need to assign seq num for mutiple rows with source as another select.
    My example allows for multiple next available numbers for multiple tables. But inserts for a given table would only use one of the next available number rows designated by the application_id.
    M. A. Feldman
    IBM Certified DBA on DB2 for Linux, UNIX, and Windows
    IBM Certified DBA on DB2 for z/OS and OS/390

  7. #7
    Join Date
    Nov 2011
    Posts
    24
    ok thanks.

Posting Permissions

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