Transcript Slide 1

SQL Sequence Numbers
Database Projects
Auto-Generated Sequences in Tables:

SQL provides a syntax for the create table statement that allows
for auto-generation of values.
>>-CREATE--TABLE--table-name------------------------------------>
>--+-| element-list |----------------------------+--*----------->
...
element-list:
.-,--------------------------.
V
|
|--(----+-| column-definition |------+-+--)---------------------|
...
column-definition:
|--column-name--+--------------------+-------------------------->
|
(2) |
'-| data-type |-----'
>--+--------------------+---------------------------------------|
'-| column-options |-'
Database Projects
Auto-Generated Sequences in Tables:

SQL provides a syntax for the create table statement that allows
for auto-generation of values.
column-options:
.------------------------------------------------------.
V
|
|----+-----------------------------------------------+-+--|
+-NOT NULL------------------------------+
...
+-| generated-column-spec |------------+
...
generated-column-spec:
|--+-| default-clause |-----------------------------------------------+--|
+-GENERATED--+-ALWAYS-----+--AS IDENTITY--+----------------------+-+
|
'-BY DEFAULT-'
'-| identity-options |-' |
'-GENERATED ALWAYS AS--(--generation-expression--)-----------------'
Database Projects
Auto-Generated Sequences in Tables:

SQL provides a syntax for the create table statement that allows
for auto-generation of values.
identity-options:
|--+---------------------------------------------------------+--|
| .------------------------------------------------------------------. |
| V (1)
.-1-------------------------. | |
'-(-----------+-START WITH--+-numeric-constant-+---+-+--)-'
|
.-1--------------------------. |
+-INCREMENT BY--+-numeric-constant-+-+
| .-NO MINVALUE-------------------------.
|
+-+-MINVALUE--numeric-constant-+-----+
| .-NO MAXVALUE-------------------------.
|
+-+-MAXVALUE--numeric-constant-+-----+
| .-NO CYCLE-.
|
+-+-CYCLE----+-----------------------+
| .-CACHE 20--------------------.
|
+-+-NO CACHE----------------+--------+
| '-CACHE--integer-constant-'
|
| .-NO ORDER-.
|
'-+-ORDER----+-----------------------'
Database Projects
Examples:
create table MyTable (
ID INTEGER NOT NULL GENERATED BY DEFAULT AS IDENTITY,
...
create table MyTable (
ID INTEGER NOT NULL GENERATED ALWAYS AS IDENTITY
(START WITH 10 INCREMENT BY 2),
...
Database Projects
IDENTITY Columns:


IDENTITY columns are used when you do not want to provide
values for that column and want the DBMS to provide them
instead.
They are referred to as “auto-generated” columns
create table MyTable(
ID INT GENERATED ALWAYS AS IDENTITY,
Desc VARCHAR(20));
insert into MyTable (Desc) values ('a first value');
insert into MyTable (Desc) values ('a second value');
select * from MyTable;
ID
DESC
----------- -------------------1 a first value
2 a second value
Database Projects
DB2 Says:



“GENERATED ALWAYS is the recommended value unless data
propagation or unload and reload operations are being done.”
This is because the column is a primary key column and so
likely to be a secondary key elsewhere.
If you try to upload the secondary key table using a LOAD or
IMPORT statement, you will not know the correct secondary
key values since they were not determined by you or your
script.
Example: Suppose the borrowerid column in Cardholder is
GENERATED ALWAYS and we now try to IMPORT the
Borrows table. We can not provide the Borrows.borrowerid
values because we do not know what they are.
Database Projects
Alternative:





Use GENERATED BY DEFAULT instead.
This allows the user to specify values if so desired and the
values are auto-generated only as an default alternative.
If you use a value that the auto-generated mechanism might
otherwise use then you could end up with duplicate values
Create a unique index on the column and this won't happen.
“BY DEFAULT is the recommended value when using data
propagation or performing an unload and reload operation.”
Database Projects
DB2 Documentation Link:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp
?topic=/com.ibm.db2.udb.doc/admin/r0000927.htm
Database Projects
User Alternative:

What if you do not want to use auto-generated columns?

How can you guarantee column values are unique?

You can maintain your own unique identifier mechanism.
Database Projects
NextSeqNum Mechanism

Problem 1: Create a table that contains the next available
sequence number (ID).
CREATE TABLE NextSeqNum (
TableName varchar(30),
ColumnName varchar(30),
SeqNum INTEGER);


TableName: The name of the table that last used this
mechanism.
ColumnName: The name of the column that last used this
mechanism.

SeqNum: The next available value.

This table has one row for each (TableName,ColumnName) pair
Database Projects
NextSeqNum Mechanism:

Problem 2: Spec out a stored procedure that will allow us to
manage acquiring a sequence number that has not been
previously used.
This stored procedure manages the NextSeqNum table.
NextSeqNum [ TableName, ColumnName, SeqNum ]
The table contains a row for each TableName.ColumnName
column updated by this mechanism..
The SeqNum column contains the next available SeqNum
that can be used in TableName.ColumnName.
Database Projects
NextSeqNum Permitted Calls:
NextSeqNum ('RESET',null, null, null, v_NextSeqNum)
This call resets the value of NextSeqNum.SeqNum
to be 1 more than the largest value in the column
of every table managed by this procedure
Returns 0 if successful, in which case p_SeqNum contains the next
value available.
NextSeqNum ('RESET',v_TabName, v_ColName, null, v_NextSeqNum)
This call resets the value of NextSeqNum.SeqNum
to be 1 more than the largest value in the column
of columns v_TabName.v_ColName
Returns 0 if successful, in which case p_SeqNum contains the next
value available.
Database Projects
NextSeqNum Permitted Calls:
NextSeqNum ('GET','<TableName>', '<ColumnName>', <block_size>, v_NextSeqNum)
This call asks the user to specify a table name, column name and block size
and returns the next available sequence number that can be used by that
table on that column. In fact the table is authorized to use a sequence of
numbers: v_NextSeqNum, v_NextSeqNum+1, ..., v_NextSeqNum+block_size-1
Returns:
0 - success; p_SeqNum is valid
1 - NextSeqNum table is unexplainably empty
2 - an attempt to update NextSeqNum failed.
Exceptions:
SQLSTATE = '80000': negative blocksize
SQLSTATE = '80003': Bad message sent
SQLSTATE = '80004': illegal TableName:ColumnName pair
Database Projects
NextSeqNum Header:
create procedure NextSeqNum
(IN p_Msg varchar(30),
IN p_TableName varchar(30),
IN p_ColumnName varchar(30),
IN p_BlockSize int, OUT p_SeqNum int)
SPECIFIC NextSeqNum
MODIFIES SQL DATA
CALLED ON NULL INPUT
LANGUAGE SQL
Database Projects
NextSeqNum Declarations:
BEGIN ATOMIC
DECLARE errmsg varchar(50);
DECLARE v_RowCount INT;
DECLARE v_ReturnValue INT DEFAULT 0;
DECLARE v_NameList varchar(1000) ;
DECLARE v_SearchTerm, v_TabName, v_ColName varchar(40);
DECLARE v_SeqNum, v_Ndx, v_MaxValue, v_Length, v_OldNdx INT;
DECLARE v_QueryString VARCHAR(500);
DECLARE v_Statement STATEMENT;
DECLARE c_MaxValCursor CURSOR FOR v_Statement;
DECLARE CONTINUE HANDLER FOR SQLSTATE '80001'
SET v_ReturnValue = 1;
DECLARE CONTINUE HANDLER FOR SQLSTATE '80002'
SET v_ReturnValue = 2;
SET p_TableName = UPPER(p_TableName);
SET p_ColumnName = UPPER(p_ColumnName);
Database Projects
NextSeqNum Initial Values
-- A list of all acceptable ,TableName:ColumnName, pairs
-- bracketed by commas(,) and separated by colons(:)
-- must be updated each time you want to allow a
-- new table to use this mechanism
-- always put ‘,’ at the beginning and end
SET v_NameList=',MYTABLE1:ID,MYTABLE2:SERIALNUM,';
SET p_SeqNum = -99;
Database Projects
NextSeqNum RESET Message:
IF (p_Msg = 'RESET') THEN
IF (p_TableName is null or p_ColumnName is null) THEN
SET v_OldNdx = 1;
REPEAT
SET v_Ndx = LOCATE(‘,’,substr(v_NameList,v_OldNdx+1));
SET v_SearchTerm = substr(v_NameList,v_OldNdx+1,v_Ndx – v_OldNdx);
SET v_TabName = substr(v_SearchTerm,1,LOCATE(v_SearchTerm,’:’)-1);
SET v_ColName = substr(v_SearchTerm,LOCATE(v_SearchTerm,’:’)+1);
SET v_QueryString = ‘Update NextSeqNum set SeqNum = ‘ ||
‘(select max(‘ || v_ColName || ‘ + 1 from ‘ || v_TabName || ‘)’ ||
‘ where TableName = ‘ || p_TableName || ‘ and ColumnName = ‘ ||
p_ColumnName ;
EXECUTE IMMEDIATE v_QueryString;
SET v_OldNdx = v_Ndx;
UNTIL ( v_OldNdx = v_Length)
END REPEAT;
ELSE -- reset just one table
Database Projects
NextSeqNum RESET Message:
IF (p_Msg = 'RESET') THEN
-- previous slide
ELSE -- reset just one table
SET v_QueryString = ‘Update NextSeqNum set SeqNum = ‘ ||
‘(select max(‘ || p_ColumnName || ‘ + 1 from ‘ || p_TableName || ‘)’ ||
‘ where TableName = ‘ || p_TableName || ‘ and ColumnName = ‘ ||
p_ColumnName ;
EXECUTE IMMEDIATE v_QueryString;
END IF;
END IF;
Database Projects
NextSeqNum GET Message:
IF (p_Msg = 'GET' ) THEN
SET v_SearchTerm = ',' || p_TableName || ':' || p_ColumnName || ',';
SET v_Ndx = LOCATE(v_SearchTerm,v_NameList);
IF (v_Ndx > 0 ) THEN
IF ( p_BlockSize <= 0 ) THEN
SET errmsg = 'Illegal BlockSize = ' || char(p_BlockSize) ;
-- unhandled error
SIGNAL SQLSTATE VALUE '80000' SET message_text = errmsg;
END IF;
...
Database Projects
NextSeqNum GET Message:
select SeqNum into p_SeqNum
from NextSeqNum
where TableName = p_TableName,
ColumnName = p_ColumnName;
update NextSeqNum
set SeqNum = SeqNum + p_BlockSize
where TableName = p_TableName,
ColumnName = p_ColumnName;
-- ROLLBACK automatically on failure
GET DIAGNOSTICS v_RowCount = ROW_COUNT;
IF (v_RowCount = 0) THEN
SIGNAL SQLSTATE VALUE '80002' ;
END IF;
RETURN v_ReturnValue;
END IF; -- table name, column name pair exist
Database Projects
NextSeqNum Unrecognized Table/Message and Return:
ELSE -- TableName:ColumnName pair not in list
SET errmsg = 'Illegal Table/Column Name';
-- unhandled error
SIGNAL SQLSTATE VALUE '80004' SET message_text = errmsg;
END IF;
ELSE -- Not RESET and not GET
SET errmsg = 'Bad Message Sent';
SIGNAL SQLSTATE VALUE '80003' ;
END IF;
RETURN v_ReturnValue;
END @
Database Projects