Results 1 to 6 of 6
  1. #1
    Join Date
    Mar 2005
    Posts
    108

    Unanswered: LOAD with cursor does not preserve the sequence of input record?

    Greeting All.
    I am using DB2 V8.2 on Linux.
    I have a table:

    Code:
     
    CREATE TABLE DB2_SCHEMA (
    REC_NO INT NOT NULL GENERATED BY DEFAULT AS IDENTITY,
    TABLE_NAME VARCHAR (30),
    TYPE CHAR(3),
    COL_IX_TRI VARCHAR(30),
    COL_TYPE_KEY VARCHAR(30),
    COL_NULL CHAR(1)
    )
    ;
    CREATE INDEX IX_DB2SCHEMA ON DB2_SCHEMA(TABLE_NAME,TYPE,COL_IX_TRI)
    ;
    and a LOAD with cursor:

    Code:
     
    declare cur1 cursor for
    SELECT ... 
    ORDER BY TBNAME
    ;
    load from cur1 of cursor insert  into db2_schema(table_name,type,col_ix_tri,col_type_key,col_null);
    After LOAD was finished, I checked the result.

    Code:
     
    SELECT * FROM DB2_SCHEMA ORDER BY REC_NO
    Because REC_NO is an identity column (the first record loaded should have REC_NO=1), I expected this gave me the same result set as "SELECT ... ORDER BY TBNAME" which defines the cursor, but I got a result of different sequence.

    Could you please explain why, or where I am wrong?
    Thanks in advance.

  2. #2
    Join Date
    May 2006
    Posts
    82
    I dont think the values generated for the REC_NO will be in the sequential order like 1,2,3. If am right it generates random values to create a unique values.
    Vinay,

  3. #3
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    If you want to assign a specific REC_NO to each record, then I would do that in the cursor declaration. The LOAD itself writes the data as quickly as possible, which does not mean that the first record is loaded first. Afterall, you could have parallel operations going on, i.e. multiple LOAD-threads inserting data. Each of those threads would have its own batch of identity values.
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

  4. #4
    Join Date
    Mar 2005
    Posts
    108
    Your explanation makes sense, Stolze. Thanks. Do you have any good suggestion on how to define a sequence number in a cursor. The base tables don't have any sequence # to use. I need something like:
    Code:
     
    declare cur1 cursor for
    Select seq#, A.col1, B.col2
      from T1 A, T2 B
      where ...
      order by A.col1;
    Thanks again.
    Last edited by DBA-Jr; 03-17-07 at 00:44.

  5. #5
    Join Date
    Jan 2003
    Posts
    4,292
    Provided Answers: 5
    Since you declared your table with an IDENTITY column that is "GENERATED BY DEAULT", you can supply values for that column as well as letting the DB generate them. So for your cursor, use ROW_NUMBER() OVER (ORDER BY ...) as a column and then use that column for your IDENTITY column in your table. After the load, you will need to change the IDENTITY start value so you do not get any errors letting the DB supply the values after the load. Use ALTER TABLE DB2SCHEMA ALTER COLUMN REC_NO RESTART WITH xxx. Where xxx is one larger than the largest value of REC_NO currently in the table.

    Andy

  6. #6
    Join Date
    Jan 2007
    Location
    Jena, Germany
    Posts
    2,721
    Adding a counter mechanism can be done in many ways:
    • Using the "counter" UDF that is provided in the DB2 samples
    • Using ROW_NUMBER() OVER ()
    • Using sequences
    • Using recursive SQL
    Knut Stolze
    IBM DB2 Analytics Accelerator
    IBM Germany Research & Development

Posting Permissions

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