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

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

CodePudding user response：

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.

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.

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.

CodePudding user response：

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.

CodePudding user response：

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.