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

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.

You can change that my using the CACHE option for identity columns. Basically, for performance reasons, a number of identities is cached and used, so that syncing is avoided. You can reduce the gap but risk a reduction in performance.

The reason is, that the database is deactivated highly likely upon your application session disconnection, since it's the only application working with the database.
When a database is deactivated, all unused sequence cache values are lost.
You may avoid implicit database activation / deactivation with the ACTIVATE DATABASE command. But it doesn't help you to avoid gaps in case of rollbacks and the instance restart.