How to link DB2 tables

Question

I have header and detail table, and the relation ship between those 1:N both table has auto SEQ number field, i want to link up those two tables using some technique so i can trace back. I have two solution in my mind, need your help if you advice one from those or another better option.

(1) Use data area and store a 10000000 number to begin with and fetch it for each header insertion and multiple detail insertion ... increase number and update data area.(this case both table will be having there own auto SEQ number and a linked key) (2) fetch header table SEQ number while inserting record in it and use that as a key in detail table. (this case header will have Auto seq key and detail table will have auto SEQ key and linked key as header table's auto key)

Please advise.

Thanks

jmarkmurphy jmarkmurphy · Accepted Answer · 2019-01-08T15:28:31

This sounds like a job for a sequence. A sequence is an SQL object that returns the next sequential number each time it is requested. It is implemented in a data area, but that is on no consequence to you. The big thing is that a sequence is created and accessed using SQL.

To create a sequence, in it's simplest form, you issue the following SQL statement:

CREATE SEQUENCE NEXT_ORDER_NO;

Additional information about creating sequences can be found in the Knowledge Center.

To generate a number from this sequence you can include NEXT VALUE FOR <sequence name> in an SQL statement like this:

INSERT INTO ORDER_MASTER (ORDER_NO, CUSTOMER_NO)
  VALUES (NEXT VALUE FOR NEXT_ORDER_NO, 15);

This inserts a new record into the table ORDER_MASTER with the next order number from the sequence, and the value 15 as the customer number.

To get the most recent value generated from the sequence you use PREVIOUS VALUE FOR <sequence name>, like this:

INSERT INTO ORDER_DETAIL (ORDER_NO, ORDER_LINE, ITEM_NO)
  VALUES (PREVIOUS VALUE FOR NEXT_ORDER_NO, 1, 'AX14450')

This inserts a record into table ORDER_DETAIL with the most recently generated ORDER_NO, an order line of 1, and an item number of 'AX14450'. You can use PREVIOUS VALUE FOR <sequence name> as many times as you want, and it will always return the same value. You can also find more information in the Knowledge Center.

There are a few caveats. NEXT VALUE and PREVIOUS VALUE behave as you would expect, even if multiple jobs are using the same sequence. Job 1 will not have access to Job 2's generated values and vice versa. But this means that depending on how you define the sequence, you could end up with gaps in your sequence numbers, or they could be issued out of sequence. Be sure to read both of the references I provided for more in depth explanations.

If you don't want to use NEXT VALUE directly in your insert, you can also use VALUES INTO like this:

VALUES NEXT VALUE FOR NEXT_ORDER_NO INTO :<local variable>

You can find more info about this statement in the Knowledge Center.

As an alternative to a sequence, you can use an identity column (this is what it seems you want based on your comments), but you don't have to do anything crazy in your SQL to retrieve the last identity value assigned. Use IDENTITY_VAL_LOCAL like this:

INSERT INTO HTABLE (NAME, AGE) VALUES ('Mark', 51);

INSERT INTO DTABLE (hseq,Address,Zip) 
  VALUES (IDENTITY_VAL_LOCAL(),'1701 creek ave', 19670);

INSERT INTO DTABLE (hseq,Address,Zip) 
  VALUES (IDENTITY_VAL_LOCAL(), '22701 creek ave', 19270);

The value of IDENTITY_VAL_LOCAL() only changes when an INSERT is executed on a table with an identity column. Like PREVIOUS VALUE, IDENTITY_VAL_LOCAL() will return a null if it is not preceded by an INSERT on a table with an identity column. And also like PREVIOUS VALUE, the INSERT must be in the same job as the call to IDENTITY_VAL_LOCAL() to have any affect.

Find a comparison of identity columns and sequences here.

How to link DB2 tables

1 Answers