Nonstop Database, HiRDB Version 9 UAP Development Guide
![[Contents]](FIGURE/CONTENT.GIF)
![[Index]](FIGURE/INDEX.GIF)
![[Back]](FIGURE/FRONT.GIF)
4.19.2 Defining a sequence generator
You use CREATE SEQUENCE to define a sequence generator. For details about CREATE SEQUENCE, see the manual HiRDB Version 9 SQL Reference.
The following shows an example of a definition of a sequence generator:
CREATE SEQUENCE SEQ1..........1
AS INTEGER..................2
START WITH 10...............3
INCREMENT BY 10.............4
MAXVALUE 999................5
MINVALUE 10.................6
CYCLE.......................7
LOG INTERVAL 3..............8
IN RD01.......................9
- Explanation:
- Defines sequence generator SEQ1 consisting of conditions 2 through 9.
- Data type
- Start value
- Increment value
- Maximum value
- Minimum value
- Cycle specification
Cycles the numbers so that the maximum value (999) is followed immediately by the minimum value (10) with an increment value of 10.
- Log output interval
- Storage RDAREA name
- Note
- When the cycle specification is used, the sequence generator is capable of generating duplicate sequence numbers.
- Organization of this subsection
- (1) Specifying the sequence generator storage RDAREA
- (2) Specifying the log output interval
(1) Specifying the sequence generator storage RDAREA
When you define a sequence generator, you can specify any RDAREA for the storage for the sequence generator that satisfies the following conditions:
- RDAREA in which fewer than 500 tables and sequence generators combined are defined
- RDAREA that is not shut down
- RDAREA to which the inner replica facility is not being applied
- Notes
- In a HiRDB/Parallel Server, if the sequence generator and the table that uses the sequence generator (and which is not partitioned among multiple servers) are stored in separate servers, communication will occur each time the sequence generator is used, which will have an adverse effect on performance. For a table that is partitioned among multiple servers, you might be able to reduce the overhead for communication by storing the sequence generator and the table that uses the sequence generator in an RDAREA on the same server. Therefore, we recommend that you store a sequence generator and the table that uses the sequence generator in an RDAREA on the same server.
- The inner replica facility cannot be used on an RDAREA that stores a sequence generator.
(2) Specifying the log output interval
You can improve performance by how you specify the log output interval when you define the sequence generator.
Sequence generator logs are output at the following times:
- The first time the NEXT VALUE expression is used after the sequence generator has been defined
- When the number of times a sequence number is collected equals the log output interval that has been specified
The following figure shows an example of a sequence generator for which a log output interval is specified.
![[Figure]](FIGURE/ZU041974.GIF)
- Notes
- Setting a small value for the sequence generator's log output interval will reduce the number of skipped numbers in the event of a system failure, but performance will be affected adversely because the number of log output operations is increased. On the other hand, if you set a large value for the log output interval, more numbers will be skipped in the event of a system failure, but performance will be improved because you reduce the number of log output operations.
- The maximum number of skipped numbers will be the same as the value specified for the log output interval.
All Rights Reserved. Copyright (C) 2011, Hitachi, Ltd.