Actual work operations involve numbering, including management of form and document numbers. There are instances when two users may try to get form numbers at the same time. The form numbers must be counted to ensure that when a user requests a form number, he or she does not get the same number that was issued earlier to another user. If a duplicate number is generated, a user may be forced to wait while another user is getting a form number. HiRDB provides a numbering function to minimize the effects of lock-release waiting.

For the most efficient numbering, tables must be designed to minimize the effects of lock-release waiting. HiRDB minimizes the locking effects on tables that manage numbers by providing a facility that releases the lock on a row without waiting for the UAP to commit the transaction. This facility releases the lock when the table update (including addition and deletion) process is completed and disables rollback after that point. To use this facility, the table designer must specify the WITHOUT ROLLBACK option in the CREATE TABLE statement when defining the table.

If the WITHOUT ROLLBACK option is specified when a table is defined, rollback becomes disabled as soon as a row is updated. Thus, if the UAP or the HiRDB system terminates abnormally, the active table with obtained numbers is rolled back correctly when the HiRDB system is restarted, and data integrity is maintained. However, the point to which the process that updates the number management table was rolled back is unknown. In this case, the operation has assigned a number but is no longer using it. Therefore, the WITHOUT ROLLBACK option is not suitable for operations that are adversely affected if a number is skipped. Use the WITHOUT ROLLBACK option only if the numbers do not have to be continuous.

Figure 4-63 shows an example of a table that manages numbers.

Figure 4-63 Example of a table that manages numbers

Note

For details about the table definition example (specification of without ROLLBACK option), see the HIRDB Version 8 Installation and Design Guide.

Following is an example of a numbering application program. The application program that manipulates the number management table and the operation table is assumed to be the same transaction.

This table manages form numbers and document numbers. In the following example, the SQL obtains the latest form number from the number management table and uses it in the operation.

INSERT INTO number-management-table VALUE ('form-number',1)   1
:
DECLARE CUR1 CURSOR FOR                                2
SELECT number FROM number-management-table
WHERE type='form-number' FOR UPDATE OF number
OPEN CUR1                                              3
FETCH CUR1 INTO :x_number                                4
UPDATE number-management-table SET number=:x_number+1         5
WHERE CURRENT OF CUR1
CLOSE CUR1                                             6
:
Access to operation table that used obtained number    7
:

Explanation

1. Enters 1 as the initial form number value in the number management table.
2. Retrieves the latest form number from the number management table and declares cursor CUR1.
3. Opens cursor CUR1.
4. Fetches the form number into x_number.
5. Increments the number for the next user who retrieves a number (sets the latest number). When this process is completed, the lock on the row is released without waiting for the COMMIT statement.
6. Closes cursor CUR1.
7. Executes the user-defined operation based on the form number that was fetched into x_number.

Steps 3 through 7 are repeated for each number.

(a) Lock processing

If multiple rows are stored in a table for which the WITHOUT ROLLBACK option is specified and an index is not defined for that table, all rows are temporarily locked because the retrieval targets all rows. In such cases, a lock-release wait may occur between the form number numbering process and the document number numbering process. To avoid lock-release wait, specify YES for PDLOCKSKIP in the client environment definitions. This sets a search using condition evaluation with no lock. When a search using condition evaluation with no lock is set, the rows are not locked during retrieval processing, and only the rows that satisfy the retrieval condition are locked.

(b) Rollback processing

When handling multiple number types, do not execute processes that update multiple rows with one SQL execution. Lock release and rollback become disabled in each row when update processing for that row is completed. Therefore, if a UAP that updates multiple rows terminates abnormally, the HiRDB system may not be able to roll back the update of some of the rows.

Registering a numbering process as a stored procedure is useful because numbering is often processed according to a fixed pattern.

Examples 1 through 3 show table definitions and stored procedures.

Example 1 defines a table with the WITHOUT ROLLBACK specification and uses a stored procedure to assign sequential numbers.

This example assigns number values that start from an initial value of 1 and increment by 1 up to the maximum integer value.

If the maximum integer value is exceeded, the HiRDB system returns an overflow error. However, if the default value setting facility (PDDFLNVAL) is used, a null value is set instead of an overflow error, and a NOT NULL constraint violation error occurs. If a row with the initial value is not inserted beforehand, the table will not have any rows, and a cursor positioning error (the cursor is not positioned on any row) will occur when the UPDATE statement is executed. If multiple rows are inserted beforehand, the second and 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
<Sequence number assignment>                                 9
CALL owner_id.nextval (OUT:xnext_no);
:
Process that uses sequence number xnext_no that was assigned
:
CALL owner_id.nextval (OUT:xnext_no);
:

Explanation

1. Defines the owner_id.sequence_tbl key for assigning INTEGER values.
2. Defines the owner_id.nextval procedure, which assigns a sequence number and outputs it with the next_no parameter.
3. Retrieves the value in the sequence_no column of the owner_id.sequence_tbl table.
4. Sets the retrieved value to the next_no parameter.
5. Increments the sequence_no column in the owner_id.sequence_tbl table by 1.
6. Commits the transaction to validate the table and procedure definitions.
7. Inserts a row that has the initial value of 1 using an INSERT statement.
8. Commits the transaction to validate the inserted row.
9. Calls the owner_id.nextval procedure with a CALL statement, assigns a sequence number, and gets the value with the next_no parameter. The next sequence number is assigned each time a CALL statement is executed.

Example 2 defines a table with the WITHOUT ROLLBACK specification and uses a stored procedure to assign two or more types of sequence numbers.

For each sequence number identification key, this example assigns number values that start from an initial value of 1 and increment by 1 up to the maximum integer value.

If the maximum integer value is exceeded, the HiRDB system returns an overflow error. However, if the default value setting facility (PDDFLNVAL) is used, a null value is set instead of an overflow error, and a NOT NULL constraint violation error occurs. If a row with the initial value is not inserted beforehand for a sequence value identification key, the table will not have any rows and a cursor positioning error (the cursor is not positioned on any row) will occur when the UPDATE statement is executed. If multiple rows are inserted beforehand for a sequence number identification key, the second and subsequent rows are ignored.

Notes

1. An index cannot be defined for a table when WITHOUT ROLLBACK is specified. To prevent lock contention, specify PDLOCKSKIP=YES in the client environment definitions.
2. Because an index cannot be defined for a table when WITHOUT ROLLBACK is specified, divide the tables and the procedures if the number of sequence number types is extremely large.

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
       :
(Initial value row is inserted for each sequence number type)
 
<Assignment of 'key_value_1' sequence number>  ............................9
xinput_key <-- 'key_value_1'
CALL owner_id.nextval(IN:xinput_key,OUT:xnext_no);
                  :
Processing when sequence number xnext_no assigned to 'seqkey_value_1' is used
                  :
xinput_key <-- 'key_value_1'
CALL owner_id.nextval(IN:xinput_key,OUT:xnext_no);
                  :
 
<'Assignment of key_value_2' sequence number>  ............................9
xinput_key <-- 'key_value_2'
CALL owner_id.nextval(IN :xinput_key,OUT:xnext_no);
                  :
Processing when sequence number xnext_no assigned to 'key_value_2' is used
                  :
xinput_key <-- 'key_value_2'
CALL owner_id.nextval(IN:xinput_key,OUT:xnext_no);
                  :

Explanation

1. Defines the owner_id.sequence_tbl table for assigning INTEGER values to each sequence number identification key.
2. Defines the owner_id.nextval procedure, which enters a sequence number identification key using the input_key parameter, assigns a sequence number for that key, and outputs the sequence number with the next_no parameter.
3. Specifies a sequence number identification key for the sequence_key column in the owner_id.sequence_tbl table and retrieves the value in the sequence_no column.
4. Sets the retrieved value to the next_no parameter.
5. Increments the sequence_no column in the owner_id.sequence_tbl table by 1.
6. Commits the transaction to validate the table and procedure definitions.
7. Inserts a row that has the initial value of 1 using an INSERT statement, for each sequence number identification key.
8. Commits the transaction to validate the inserted row.
9. Calls the owner_id.nextval procedure with a CALL statement, assigns a sequence number, and gets the value with the next_no parameter. The next sequence number is assigned each time a CALL statement is executed.

Example 3 defines a table with the WITHOUT ROLLBACK specification and uses a stored procedure to assign sequence numbers that are incremented between a minimum value and a maximum value.

If a row with the initial value is not inserted beforehand, the table will not have any rows, and a cursor positioning error (where the cursor is not positioned on any row) will occur when the UPDATE statement is executed. If multiple rows are inserted beforehand, the second and 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=214783647 THEN
      SET update_no=-214783648;
    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
<Sequence number assignment>                                10
CALL owner_id.nextval(OUT:xnext_no);
                  :
Process that uses sequence number xnext_no that was assigned
                  :
CALL owner_id.nextval(OUT:xnext_no);
                  :

Explanation

1. Defines the owner_id.sequence_tbl key for assigning INTEGER values.
2. Defines the owner_id.nextval procedure, which assigns a sequence number to the sequence_no column of the owner_id.sequence_tbl table. The sequence numbers increment in values of 1, a minimum value of -2,147,483,648, and a maximum value of 2,147,483,647. The number incremented after the maximum number is the minimum value.
3. Retrieves the value in the sequence_no column of the owner_id.sequence_tbl table.
4. Sets the retrieved value to the next_no parameter.
5. If the retrieved value is the maximum value of 2,147,483,647, this section sets the minimum value of -2,147,483,648, as the next sequence number. Otherwise, this section increments the retrieved value by 1 and sets the result as the next sequence number.
6. Updates the sequence_no column in the owner_id.sequence_tbl table to the next sequence number value.
7. Commits the transaction to validate the table and procedure definitions.
8. Inserts a row that has the initial value of 1 with an INSERT statement.
9. Commits the transaction to validate the inserted row.
10. Calls the owner_id.nextval procedure with a CALL statement, assigns a sequence number, and gets the value with the next_no parameter. The next sequence number is assigned each time a CALL statement is executed.