1 / 22

SQL Sequence Numbers

SQL Sequence Numbers. 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 |----------------------------+--*-----------> ...

daria
Télécharger la présentation

SQL Sequence Numbers

An Image/Link below is provided (as is) to download presentation Download Policy: Content on the Website is provided to you AS IS for your information and personal use and may not be sold / licensed / shared on other websites without getting consent from its author. Content is provided to you AS IS for your information and personal use only. Download presentation by click this link. While downloading, if for some reason you are not able to download a presentation, the publisher may have deleted the file from their server. During download, if you can't get a presentation, the file might be deleted by the publisher.

E N D

Presentation Transcript

  1. Database Projects SQL Sequence Numbers

  2. 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 |-'

  3. 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--)-----------------'

  4. 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----+-----------------------'

  5. 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), ...

  6. 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

  7. 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.

  8. 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.”

  9. Database Projects DB2 Documentation Link: http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp ?topic=/com.ibm.db2.udb.doc/admin/r0000927.htm

  10. 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.

  11. Database Projects NextSeqNum Mechanism • Problem 1: Create a table that contains the next available sequence number (ID). • 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 CREATE TABLE NextSeqNum ( TableName varchar(30), ColumnName varchar(30), SeqNum INTEGER);

  12. 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.

  13. 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.

  14. 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

  15. 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

  16. 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);

  17. 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;

  18. 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

  19. 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;

  20. 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; ...

  21. 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

  22. 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 @

More Related