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

DBA-Jr · #1 (**permalink**) 03-14-07, 10:48

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.

vini_srcna · #2 (**permalink**) 03-15-07, 04:26

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.

stolze · #3 (**permalink**) 03-16-07, 16:40

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.

DBA-Jr · #4 (**permalink**) 03-16-07, 22:38

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.

ARWinner · #5 (**permalink**) 03-18-07, 01:44

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

stolze · #6 (**permalink**) 03-19-07, 09:36

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

Internet Services

Web Development

Digital Marketing

Consumer Tech