Identity Column slow down the insert

[shenanwei]

I have 2 same windows machine, same instance configure and Database ,
all run DB2 UDB V8.1.5

Test 1 :
create table OUT_1 (LINE VARCHAR(350), LINENUMBER INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE));
insert into out_1 (line) values
('C000000002XYTNF102020201855000000075000519600040 547000003256510
0000000000000000000000000SIM CAR ADJ JOHN, SMITHJA
CPRM SIM CARMBCORL XYTNF1020282726
00041020206900381468
00000000000');

On machine A, db2batch give the reslut of insert 0.066 second
On machine B, db2batch give the result of insert 0.001 second
If no Identity column
Test ddl_2 :
create table OUT_1 (LINE VARCHAR(350));
insert into out_1 (line) values
('C000000002XYTNF102020201855000000075000519600040 547000003256510
0000000000000000000000000SIM CAR ADJ JOHN, SMITHJA
CPRM SIM CARMBCORL XYTNF1020282726
00041020206900381468
00000000000');
2 Machine give the same result of insert 0.001 second

Test ddl_3 :
create table OUT_1 (LINE VARCHAR(350), LINENUMBER INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE
200));
CARert into out_1 (line) values
('C000000002XYTNF102020201855000000075000519600040 547000003256510
0000000000000000000000000SIM CAR ADJ JOHN, SMITHJA
CPRM SIM CARMBCORL XYTNF1020282726
00041020206900381468
00000000000');
2 Machine give the same reulst of insert 0.001 second

My question is what is the secret of GENERATED NO CACHE, why takes 50
times longer?
Thanks

[Serge Rielau]

sh*******@gmail.com wrote:

I have 2 same windows machine, same instance configure and Database ,
all run DB2 UDB V8.1.5

Test 1 :
create table OUT_1 (LINE VARCHAR(350), LINENUMBER INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO CACHE));
insert into out_1 (line) values
('C000000002XYTNF102020201855000000075000519600040 547000003256510
0000000000000000000000000SIM CAR ADJ JOHN, SMITHJA
CPRM SIM CARMBCORL XYTNF1020282726
00041020206900381468
00000000000');

On machine A, db2batch give the reslut of insert 0.066 second
On machine B, db2batch give the result of insert 0.001 second
If no Identity column
Test ddl_2 :
create table OUT_1 (LINE VARCHAR(350));
insert into out_1 (line) values
('C000000002XYTNF102020201855000000075000519600040 547000003256510
0000000000000000000000000SIM CAR ADJ JOHN, SMITHJA
CPRM SIM CARMBCORL XYTNF1020282726
00041020206900381468
00000000000');
2 Machine give the same result of insert 0.001 second

Test ddl_3 :
create table OUT_1 (LINE VARCHAR(350), LINENUMBER INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, CACHE
200));
CARert into out_1 (line) values
('C000000002XYTNF102020201855000000075000519600040 547000003256510
0000000000000000000000000SIM CAR ADJ JOHN, SMITHJA
CPRM SIM CARMBCORL XYTNF1020282726
00041020206900381468
00000000000');
2 Machine give the same reulst of insert 0.001 second

My question is what is the secret of GENERATED NO CACHE, why takes 50
times longer?
Thanks

Evreey time DB2 has to load a cache (which with NOCACHE means always)
DB2 needs to update the LASTASSIGNVAL column in SYSIBM.SYSSEQUENCES.
Also DB2 needs to write a log record for forward logging.
You should have very special circumstances that require the use of
NOCACHE... what are they?

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[shenanwei]

It is a temp table used in one job (create when job start and drop
after finish).
This job run fine in machine B and machine A for years, but very slow
in machine A suddenly.
I get this "NO CACHE" DDL from dynamic sql snapshot, found the insert
is the most time comsuing sql.

[Serge Rielau]

sh*******@gmail.com wrote:

It is a temp table used in one job (create when job start and drop
after finish).
This job run fine in machine B and machine A for years, but very slow
in machine A suddenly.
I get this "NO CACHE" DDL from dynamic sql snapshot, found the insert
is the most time comsuing sql.

If it's a temp table there is really no reason for NOCACHE.
Also could it be the temp table is declared as LOGGED?
Did someone change the USER TEMPORARY TABLESPACE (SMS vs DMS), hot
tablespace, ...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[shenanwei]

It is not GLOBAL TEMP table defined by "DECLARE GLOBAL TEMPORARY
TABLE", it is a temp REGULAR table created within a job by "create
table OUT_1 (LINE VARCHAR(350), LINENUMBER INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO
CACHE));", use regular tablespace.
I could not find the reason the insert slow down suddenly.

[Serge Rielau]

sh*******@gmail.com wrote:

It is not GLOBAL TEMP table defined by "DECLARE GLOBAL TEMPORARY
TABLE", it is a temp REGULAR table created within a job by "create
table OUT_1 (LINE VARCHAR(350), LINENUMBER INTEGER NOT NULL
GENERATED ALWAYS AS IDENTITY (START WITH 1, INCREMENT BY 1, NO
CACHE));", use regular tablespace.
I could not find the reason the insert slow down suddenly.

How did you arrive at this statement? Purely by elapsed time?
Take a look at the basics. Do you have an I/O bottleneck?
Is it limitted to this tablespace? ...

If I were you I'd remove the NOCACHE
(on V8.2 it's just an ALTER TABLE ALTER COLUMN statement, online)
That will exclude the idnetity column as culprit right there.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[shenanwei]

I get this statement from the dynamic sql snapshot, found this insert
has the longest execution time.
And make the sript above accordingly, benchmarked using db2batch and
proved the "No Cache" identity slow down the insert.
Have told the developer to optimize the code to use cache 500, but why
slow down suddenly for this "No Cache" insert but other insert with
"Cache" is still OK.
If there are bottleneck on this tbs, how come it doesnot affect other
insert. The "No Cache" identity insert take 50 times more than general
insert.

[Serge Rielau]

sh*******@gmail.com wrote:

I get this statement from the dynamic sql snapshot, found this insert
has the longest execution time.
And make the sript above accordingly, benchmarked using db2batch and
proved the "No Cache" identity slow down the insert.
Have told the developer to optimize the code to use cache 500, but why
slow down suddenly for this "No Cache" insert but other insert with
"Cache" is still OK. You mean "NOCACHE" right? If there are bottleneck on this tbs, how come it doesnot affect other
insert. The "No Cache" identity insert take 50 times more than general
insert.

Other insert against the same table? When is thsi other insert run?
Eg. you could run into a concurrency issue. Possibly you're waiting on a
lock...? There are many possibilities, but to understand requires more
than simply staring at teh individual statement.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
DB2 UDB for Linux, Unix, Windows
IBM Toronto Lab

[Ian]

sh*******@gmail.com wrote:

I get this statement from the dynamic sql snapshot, found this insert
has the longest execution time.
And make the sript above accordingly, benchmarked using db2batch and
proved the "No Cache" identity slow down the insert.
Have told the developer to optimize the code to use cache 500, but why
slow down suddenly for this "No Cache" insert but other insert with
"Cache" is still OK.

Serge answered that already.

Adding the NOCACHE option to an identity means that every single
insert into the table will ALSO require a write to SYSIBM.SYSSEQUENCES
and the corresponding logging.

When DB2 caches the identity column values, they are held in memory;
thus you only incur the overhead of writing to SYSIBM.SYSSEQUENCES
every N inserts (where N = the size of the cache).