Nonstop Database, HiRDB Version 9 UAP Development Guide
There are two numbering methods:
This subsection describes numbering using a table for which the WITHOUT ROLLBACK option is specified. For details about the automatic numbering facility, see 4.19 Automatic numbering facility.
In actual applications, there are various types of numbering for purposes such as managing form and document numbers. When a user attempts to acquire a form number, another user might also be acquiring a form number at the same time.
When a user attempts to acquire a form number, you must keep track of the form numbers so that the same number is not assigned to two users.
In such a case, one user might be placed on wait status while the other user is acquiring a number. HiRDB provides functions that minimizes the effects of locking while you perform numbering.
In order to perform efficient numbering, you must design the table so as to minimize the effects of locking. HiRDB provides a function that releases locks from table rows and prevents rollback when update processing (including addition and deletion) on the table is completed without having to wait for transmissions to commit. To implement this function, the table designer must specify the WITHOUT ROLLBACK option in CREATE TABLE when the table is designed.
When the WITHOUT ROLLBACK option is specified during table definition, rollback will not occur when rows are updated. If the UAP or HiRDB system terminates abnormally, the correct rollback will be achieved and data integrity will be maintained for the table for a job that uses assigned numbers when the HiRDB system is restarted, but the specific point in the numbering management table update processing at which rollback occurred cannot be determined. In such a case, the numbers assigned by the numbering process might no longer be used by the job. Therefore, such an application is not suitable for a job that requires consecutive numbers. Apply this method to a job that does not require consecutive numbers.
The following figure shows an example of a table used for managing numbering.
Figure 4-7 Example of a table used for managing numbering
This subsection presents an example of a numbering application program. This example assumes the same transactions for an application program that uses the numbering management table and job table.
Example:
There is a numbering management table that manages form numbers and document numbers. The following shows an example SQL statement that acquires the most recent form number from the numbering management table and uses that number in a job.
INSERT INTO NUMBERING_MANAGEMENT_TABLE VALUE('FORM_NUMBER',1) ....1 : DECLARE CUR1 CURSOR FOR .......................2 SELECT NUMBERING FROM NUMBERING_MANAGEMENT_TABLE WHERE TYPE='FORM_NUMBER' FOR UPDATE OF NUMBERING OPEN CUR1 .....................................3 FETCH CUR1 INTO :x_NUMBERING .......................4 UPDATE NUMBERING_MANAGEMENT_TABLE SET NUMBERING=:x_NUMBERING+1 ..........5 WHERE CURRENT OF CUR1 CLOSE CUR1 ....................................6 : Accessing-the-job-table-by-using-the-acquired-number ..7 :
Steps 3 through 7 are repeated each time numbering is performed.
If multiple rows are stored in a table for which the WITHOUT ROLLBACK option is specified, and no index is defined for the table, all rows are locked temporarily because they are all subject to search. In such a case, locking might occur between, for example, a form numbering process and a document numbering process. You can prevent this by specifying YES in PDLOCKSKIP in the client environment definition to perform an unlocked conditional search. If an unlocked conditional search has been performed, only the rows satisfying the search condition are locked and no lock is applied during search processing.
If you perform more than one type of numbering, do not update multiple rows with a single SQL statement. The timing at which lock release and rollback no longer occur is set to the point in time where update processing on each row is completed. If a UAP that updates multiple rows terminates abnormally, some of the rows might not be rolled back.
Because numbering is often performed in a specific pattern, it is convenient if you register a numbering process as a stored procedure.
This subsection presents three examples of table definition and stored procedures.
CREATE FIX TABLE owner_id.sequence_tbl(sequence_no INTEGER NOT NULL) WITHOUT ROLLBACK; .......................................1 CREATE PROCEDURE owner_id.nextval(OUT next_no INTEGER) BEGIN DECLARE update_no INTEGER; ..............................2 DECLARE cr1 CURSOR FOR SELECT sequence_no FROM owner_id.sequence_tbl FOR UPDATE; OPEN cr1; FETCH cr1 INTO update_no; ...............................3 SET next_no=update_no; ..................................4 UPDATE owner_id.sequence_tbl SET sequence_no=update_no+1 WHERE CURRENT OF cr1; .................................5 CLOSE cr1; ..............................................3 END .......................................................2 COMMIT WORK; ................................................6 INSERT INTO owner_id.sequence_tbl(sequence_no) VALUES(1); ...7 COMMIT WORK; ................................................8 <Assigning sequential numbers> ..................................9 CALL owner_id.nextval(OUT:xnext_no); : processing-using-assigned-sequential-number-xnext_no : CALL owner_id.nextval(OUT:xnext_no); :
CREATE FIX TABLE owner_id.sequence_tbl(sequence_key CHAR(30) NOT NULL, sequence_no INTEGER NOT NULL) WITHOUT ROLLBACK; ..........................................1 CREATE PROCEDURE owner_id.nextval(IN input_key CHAR(30), OUT next_no INTEGER) BEGIN DECLARE update_no INTEGER; .................................2 DECLARE cr1 CURSOR FOR SELECT sequence_no FROM owner_id.sequence_tbl WHERE sequence_key=input_key FOR UPDATE OF sequence_no; OPEN cr1; FETCH cr1 INTO update_no; ..................................3 SET next_no=update_no; .....................................4 UPDATE owner_id.sequence_tbl SET sequence_no=update_no+1 WHERE CURRENT OF cr1; ....................................5 CLOSE cr1; .................................................3 END ..........................................................2 COMMIT WORK; ...................................................6 INSERT INTO owner_id.sequence_tbl(sequence_key,sequence_no) VALUES('key_value_1',1); ...................................7 COMMIT WORK; ...................................................8 INSERT INTO owner_id.sequence_tbl(sequence_key,sequence_no) VALUES('key_value_2',1); ...................................7 COMMIT WORK; ...................................................8 : (Insert as many rows containing an initial value as there are types of sequential numbers) <Assigning sequential number to 'key_value_1'> ............................9 xinput_key <-- 'key_value_1' CALL owner_id.nextval(IN:xinput_key,OUT:xnext_no); : processing-using-sequential-number-xnext_no-assigned-to-'key_value_1' : xinput_key <-- 'key_value_1' CALL owner_id.nextval(IN:xinput_key,OUT:xnext_no); : <Assigning sequential number to 'key_value_2'> ............................9 xinput_key <-- 'key_value_2' CALL owner_id.nextval(IN :xinput_key,OUT:xnext_no); : processing-using-sequential-number-xnext_no-assigned-to-'key_value_2' : xinput_key <-- 'key_value_2' CALL owner_id.nextval(IN:xinput_key,OUT:xnext_no); :
CREATE FIX TABLE owner_id.sequence_tbl(sequence_no INTEGER NOT NULL) WITHOUT ROLLBACK; .........................................1 CREATE PROCEDURE owner_id.nextval(OUT next_no INTEGER) BEGIN DECLARE update_no INTEGER; .................................2 DECLARE cr1 CURSOR FOR SELECT sequence_no FROM owner_id.sequence_tbl FOR UPDATE; OPEN cr1; FETCH cr1 INTO update_no; ..................................3 SET next_no=update_no; .....................................4 IF update_no=2147483647 THEN SET update_no=-2147483648; ELSE SET update_no=update_no+1; END IF; ....................................................5 UPDATE owner_id.sequence_tbl SET sequence_no=update_no WHERE CURRENT OF cr1; ....................................6 CLOSE cr1; .................................................3 END ..........................................................2 COMMIT WORK; ...................................................7 INSERT INTO owner_id.sequence_tbl(sequence_no)VALUES(1); .......8 COMMIT WORK; ...................................................9 <Assigning sequential numbers > .........................................10 CALL owner_id.nextval(OUT:xnext_no); : processing-using-assigned-sequential-number-xnext_no : CALL owner_id.nextval(OUT:xnext_no); :
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.