IBM i > ADMINISTRATOR > DB2

Data Orchestration


Do you want to encrypt DB2* Universal Database* (UDB) data on your iSeries* server? Have you ever wished data could be auto-incremented in a table? Have you ever wanted to reorganize a table while your production applications are still active? Do you desire easier integration of ILE RPG and SQL? IBM* DB2 UDB for iSeries V5R3 is helping make these dreams come true. V5R3 offers several enhancements requested by users that help simplify the usage and programming experience. This article details these enhancements and more.

 

Sequence Objects
New SQL objects, called sequence objects, have been added that allow numeric values to be automatically generated. While this is similar to identity columns introduced in V5R2, sequence objects can be shared across multiple SQL objects. They can be defined for any numeric data type that has a scale of zero, including user-defined types. On the i5/OS* side, they appear as data area objects. However, they have a signature attached that invalidates the sequence object if its altered from a non-SQL interface.

When defining a sequence object, specify the data type, starting value, increment value, minimum value, maximum value, cycling, caching and ordering. The starting value can be positive or negative. The incremental value can be any whole number to tell how many steps from the current to the next value. If this value is zero, a constant value is generated. If the value is negative, the sequence goes in descending, rather than ascending, order. When the sequence object hits the minimum or maximum value, it either cycles or stops generating values depending on how its set up.

To improve performance, an application may cache a set of values to be used. While this may enhance performance, it may also cause the sequence values to not be generated in order. In these cases, ordering can be forced. Assume we have a sequence with a cache of 20 with ordering, a starting value of 1 and an increase value of 1. If two jobs are going to be used to insert records-job A inserting a row, then job B, and job A again-the values would be 1, 2 and 3. However, if the same sequence object was created without ordering, the values would be 1, 21 and 2.

Sequence objects use the NEXT VALUE clause. For example, assuming a sequence S1 exists, to insert the next value into the table, use:

INSERT INTO TABLE1(NUM1, CHAR1) VALUES(NEXT VALUE FOR S1, Text value)

If NEXT VALUE is used multiple times in the same SQL statement, the same value will be returned for each reference to NEXT VALUE in the statement. The value last generated within that application process can be retrieved with the PREVIOUS VALUE clause. This can be useful when multiple tables must be updated as the first table generates the NEXT VALUE, and then the additional tables use the PREVIOUS VALUE clause. All tables then have the same value. Sequence objects can be used to generate non-numeric values by concatenating the sequence value with a character such as:

CONCAT(N, CAST(NEXT VALUE FOR S1 AS CHAR(4)))

Sequence objects can also be altered, dropped, labeled, commented and authority granted. When altering a sequence, almost all attributes can be changed including data type, next value, increment, minimum and maximum values. Cycling, caching and ordering can be altered as well. When a sequence is altered, all currently cached values for applications are discarded.

 

With V5R3, online or cancelable reorganize was introduced. This allows a file to be reorganized while it's being used. The file is journaled while the reorganize occurs and rows are moved one at a time.

Robert Andrews is an advisory software engineer with IBM Global Services. Robert can be reached at robert.andrews@us.ibm.com.



Advertisement

Advertisement

2017 Solutions Edition

A Comprehensive Online Buyer's Guide to Solutions, Services and Education.

Untangling Web Query

How metadata can reduce query and report complexity

Protection From Slow Queries

Introducing DB2 for i Adaptive Query Processing

Intelligent Queries

DB2 for i Learning Statistics Engine works smarter

IBM Systems Magazine Subscribe Box Read Now Link Subscribe Now Link iPad App Google Play Store
IBMi News Sign Up Today! Past News Letters