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

Note

For examples of table definitions (WITHOUT ROLLBACK option), see the HiRDB Version 9 Installation and Design Guide.

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
 
                :
 

Explanation:

1. Inserts 1 in the numbering management table as the initial value for the form numbers.
2. Declares the cursor CUR1 used to retrieve the most recent form number from the numbering management table.
3. Opens the cursor CUR1.
4. Retrieves the form number in x_NUMBERING.
5. Increments the number (from the most recent number) for the next user to retrieve a form number. When this processing is finished, the row is released from locked status without waiting for commit.
6. Closes the cursor CUR1.
7. Performs user-defined processing on the basis of the form number retrieved in x_NUMBERING.

Steps 3 through 7 are repeated each time numbering is performed.

(a) About locking

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.

(b) About rollback

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.

Example 1:

This example assigns sequential numbers by using a table with WITHOUT ROLLBACK specified and a stored procedure.

It assigns numbers up to the maximum value of INTEGER, whose initial value is 1 and increment value is 1.

If the maximum value of INTEGER is exceeded, an overflow error is returned. Note that if the default value setting facility (PDDFLNVAL) is used, the null value is assumed (no overflow error occurs), resulting in a NOT NULL constraint violation. If no row containing the initial value has been inserted, the table is treated as being empty, in which case if the UPDATE statement is executed, an error is caused by the cursor not being positioned in a row. If multiple rows have been inserted, only the first row is used and any subsequent rows are ignored.

 
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);
                :
 

Explanation:

1. Defines the table owner_id.sequence_tbl used to assign the value of INTEGER.
2. Defines the procedure owner_id.nextval that assigns a sequential number and outputs it by using the next_no parameter.
3. Searches the table owner_id.sequence_tbl for the column sequence_no.
4. Places the retrieved value in the next_no parameter.
5. Updates the value of the column sequence_no in the table owner_id.sequence_tbl by adding the increment value 1 to it.
6. Commits the transaction to apply the table and procedure definitions.
7. Uses the INSERT statement to insert a row whose initial value is 1.
8. Commits the transaction to apply the inserted row.
9. Calls the procedure owner_id.nextval by using the CALL statement, assigns a sequential number, and then acquires the value by using the next_no parameter. Each time the CALL statement is executed, the next sequential number is assigned.

Example 2:

This example assigns more than one type of sequential number by using a table with WITHOUT ROLLBACK specified and a stored procedure.

For each key that identifies the sequence number, this example assigns numbers up to the maximum value of INTEGER, whose initial value is 1 and increment value is 1.

If the maximum value of INTEGER is exceeded, an overflow error is returned. Note that if the default value setting facility (PDDFLNVAL) is used, the null value is assumed (no overflow error occurs), resulting in a NOT NULL constraint violation. If no row containing the initial value has been inserted for the key value used to identify sequential numbers, the table is treated as being empty, in which case if the UPDATE statement is executed, an error is caused by the cursor not being positioned in a row. If multiple rows have been inserted for the key value used to identify sequential numbers, only the first row is used and any subsequent rows are ignored.

Note 1

No index can be defined for a table with WITHOUT ROLLBACK specified. In order to prevent lock contention, you must specify PDLOCKSKIP=YES in the client environment definition.

Note 2

Because no index can be defined for a table with WITHOUT ROLLBACK specified, if you use many different types of sequential numbers, provide multiple tables and procedures.

 
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);
                  :
 

Explanation:

1. Defines the table owner_id.sequence_tbl used to assign the value of INTEGER for each key that identifies sequence numbers.
2. Defines the procedure owner_id.nextval that inputs a key for identifying sequential numbers by using the input_key parameter. Then assigns a sequential number and outputs it by using the next_no parameter.
3. Specifies a key used to identify sequential numbers for the column sequence_key in the table owner_id.sequence_tbl and then searches the column sequence_no.
4. Places the retrieved value in the next_no parameter.
5. Updates the value of the column sequence_no in the table owner_id.sequence_tbl by adding increment value 1 to it.
6. Commits the transaction to apply the table and procedure definitions.
7. Uses the INSERT statement to insert a row whose initial value is 1 for each key that identifies sequential numbers.
8. Commits the transaction to apply the inserted row.
9. Calls the procedure owner_id.nextval by using the CALL statement, assigns a sequential number, and then acquires the value by using the next_no parameter. Each time the CALL statement is executed, the next sequential number is assigned.

Example 3:

This example assigns sequential numbers by rotating numbers between minimum and maximum values and using a table with WITHOUT ROLLBACK specified and a stored procedure.

If no row containing the initial value has been inserted, the table is treated as empty, in which case if the UPDATE statement is executed, an error is caused by the cursor not being positioned in a row. If multiple rows have been inserted, only the first row is used and any subsequent rows are ignored.

 
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);
                :
 

Explanation:

1. Defines the table owner_id.sequence_tbl used to assign the value of INTEGER.
2. Defines the procedure owner_id.nextval that assigns sequential numbers to the column sequence_no in the table owner_id.sequence_tbl by rotating numbers whose minimum value is -2,147,483,648, maximum value is 2,147,483,647, and increment value is 1, in such a manner that the value is reset to the minimum value once the maximum value is reached.
3. Searches the column sequence_key in the table owner_id.sequence_tbl.
4. Places the retrieved value in the next_no parameter.
5. If the retrieved value is the maximum value (2,147,483,647), the next value will be the minimum value -2,147,483,648; otherwise, it will be the retrieved value plus 1 (increment value).
6. Updates the sequence number in the column sequence_no in the table owner_id.sequence_tbl to the next value.
7. Commits the transaction to apply the table and procedure definitions.
8. Uses the INSERT statement to insert a row whose initial value is 1.
9. Commits the transaction to apply the inserted row.
10. Calls the procedure owner_id.nextval by using the CALL statement, assigns a sequential number, and then acquires the value by using the next_no parameter. Each time the CALL statement is executed, the next sequential number is assigned.