DBD::DB2: why does a `$dbh->disconnect` cause gaps in an autoincrement column?

Why does the $dbh->disconnect cause gaps in the auto-increment column?

use DBI;

my $db = 'MYDB2';
my $table = 'SCHEMA.TABLE';
my $user = 'user';
my $pass = 'passwd';
my $dsn = "dbi:DB2:$db";

my $dbh = DBI->connect( $dsn, $user, $pass );
$dbh->do( "DROP TABLE IF EXISTS $table" );
$dbh->do( "CREATE TABLE $table (ID INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY, NAME CHAR(3))" );
my $sth = $dbh->prepare( "INSERT INTO $table (NAME) VALUES(?)" );
$sth->execute( 'aaa' );
$sth->execute( 'bbb' );
$sth = $dbh->prepare( "SELECT * FROM $table" );
$sth->execute();
$sth->dump_results;
$sth->finish;
$dbh->disconnect;

$dbh = DBI->connect( $dsn, $user, $pass );
$sth = $dbh->prepare( "INSERT INTO $table (NAME) VALUES(?)" );
$sth->execute( 'ccc' );
$sth->execute( 'ddd' );
$sth = $dbh->prepare( "SELECT * FROM $table" );
$sth->execute();
$sth->dump_results;

'1', 'aaa'
'2', 'bbb'
2 rows
'1', 'aaa'
'2', 'bbb'
'21', 'ccc'
'22', 'ddd'
4 rows

Without disconnect the auto-increment column is created without gaps:

use DBI;

my $db = 'MYDB2';
my $table = 'SCHEMA.TABLE';
my $user = 'user';
my $pass = 'passwd';
my $dsn = "dbi:DB2:$db";

my $dbh = DBI->connect( $dsn, $user, $pass );
$dbh->do( "DROP TABLE IF EXISTS $table" );
$dbh->do( "CREATE TABLE $table (ID INT NOT NULL GENERATED ALWAYS AS IDENTITY PRIMARY KEY, NAME CHAR(3))" );
my $sth = $dbh->prepare( "INSERT INTO $table (NAME) VALUES(?)" );
$sth->execute( 'aaa' );
$sth->execute( 'bbb' );
$sth = $dbh->prepare( "SELECT * FROM $table" );
$sth->execute();
$sth->dump_results;
$sth->finish;
#$dbh->disconnect;

$dbh = DBI->connect( $dsn, $user, $pass );
$sth = $dbh->prepare( "INSERT INTO $table (NAME) VALUES(?)" );
$sth->execute( 'ccc' );
$sth->execute( 'ddd' );
$sth = $dbh->prepare( "SELECT * FROM $table" );
$sth->execute();
$sth->dump_results;

'1', 'aaa'
'2', 'bbb'
2 rows
'1', 'aaa'
'2', 'bbb'
'3', 'ccc'
'4', 'ddd'
4 rows

Solution

This is working as designed, IDENTITY columns GENERATED ALWAYS are not guaranteed to be consecutive and gaps should be expected.

This is nothing to do with Perl or DBD:DB2, it is just how Db2 implements identity columns that are GENERATED ALWAYS. Db2 internally maintains a small cache of values for identity values for such columns per connection, and you can get gaps after ROLLBACK of a transaction that consumed a value, or after a crash or abnormal termination, or if other apps (other connections) are inserting values into the same identity column, or the increment/decrement value is not 1, or database deactivation.

Although you can use the "NO CACHE" option when specifying your identity column, or specify a lower cache value, these options are undesirable for performance / concurrency reasons.

You can also reduce database deactivation (but not eliminate it) by arranging to have the database explicitly activated before the first connection happens ( db2 activate database $dbname) . This will ensure the database does not automatically deactivate itself when the last connection does the disconnect , and the cache of pre-allocated numbers for the identity column(s) will not be lost in this case.

If you want to enforce zero gaps, you have to use a different technique, which usually has performance implications for high-insert-frequency apps and/or scalability/concurrency challenges.