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
('C000000002XYT NF1020202018550 000000750005196 000405470000032 56510
000000000000000 0000000000SIM CAR ADJ JOHN, SMITHJA
CPRM SIM CARMBCORL XYTNF1020282726
000410202069003 81468
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
('C000000002XYT NF1020202018550 000000750005196 000405470000032 56510
000000000000000 0000000000SIM CAR ADJ JOHN, SMITHJA
CPRM SIM CARMBCORL XYTNF1020282726
000410202069003 81468
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
('C000000002XYT NF1020202018550 000000750005196 000405470000032 56510
000000000000000 0000000000SIM CAR ADJ JOHN, SMITHJA
CPRM SIM CARMBCORL XYTNF1020282726
000410202069003 81468
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 8 4318 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 ('C000000002XYT NF1020202018550 000000750005196 000405470000032 56510 000000000000000 0000000000SIM CAR ADJ JOHN, SMITHJA CPRM SIM CARMBCORL XYTNF1020282726 000410202069003 81468 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 ('C000000002XYT NF1020202018550 000000750005196 000405470000032 56510 000000000000000 0000000000SIM CAR ADJ JOHN, SMITHJA CPRM SIM CARMBCORL XYTNF1020282726 000410202069003 81468 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 ('C000000002XYT NF1020202018550 000000750005196 000405470000032 56510 000000000000000 0000000000SIM CAR ADJ JOHN, SMITHJA CPRM SIM CARMBCORL XYTNF1020282726 000410202069003 81468 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.SYSSEQUE NCES.
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
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. 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
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. 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
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. 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 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.SYSSEQUE NCES
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.SYSSEQUE NCES
every N inserts (where N = the size of the cache). This thread has been closed and replies have been disabled. Please start a new discussion. Similar topics |
by: Rathtap |
last post by:
I want to use the Identity field (increment 1,1) as a primary key and
have a unique constraint on my other field which is of type char.
I am worried that related data in other tables may lose referntial
integrity if records in the ID table get messed up and need to be
re-entered.
Can you please advice on best way to do this. I definitely need a
numeric id field because it makes the joins and queries so much
faster.
|
by: grzes |
last post by:
MS SQL Server 2000.
My case is: I have the table T with primary key calling __recid int
without identity property. This table includes a lot of records (about
1000000). I need to convert __recid's data type to IDENTITY.
As you know sql sentence: "alter table T alter column __recid int
IDENTITY not null" does not work with not-empty tables.
I use the SQL Enterprise Manager which can convert the field __recid
into identity but I need...
|
by: Devesh Aggarwal |
last post by:
Hi,
I have a backup and restore module in my project. The backup uses a typed
dataset object (XSD) to get the data from database and creates a xml file as
the backup file (using the WriteXml method of dataset). When doing the
restore i have to overwrite the data from xml back to database.
these are the steps that i follow.
1. get the data from database.
|
by: Who.Really.Really.Cares |
last post by:
Hi!
I guess this must be a FAQ but I'll give it a try.
I've searched the web and usenet archive and found only negative
answers. But most of them were dated like 3-4 years back. Hasn't
anything changed since then? DB2 V7.2.
The problem:
I have a table with a primary key GENERATED BY DEFAULT AS IDENTITY.
Then I INSERT several rows and force their PK's value. Then I see that
my INSERTs haven't modified the value of DB2's internal IDENTITY
|
by: .Net Newbie |
last post by:
Hello,
I am somewhat new to .Net and currently working on an intranet site using C#
going against SQL Server 2k. I am accepting personal information on a
single webform and trying to insert the information into three separate
tables (all in a single aspx page -- without using stored procedures, yet).
The first SQL Statement accepts the persons most detailed information, like
name, address, phone, etc and inserts the single record into...
| |
by: Veeru71 |
last post by:
Given a table with an identity column (GENERATED BY DEFAULT AS
IDENTITY),
is there any way to get the last generated value by DB2 for the
identity column?
I can't use identity_val_local() as the INSERTS are happening in a
different session.
Eg, We have the following table....
|
by: gunnar.sigurjonsson |
last post by:
I´m having some problem retrieving identity value from my newly
inserted row into a view.
I have two tables T1 and T2 which I define as following
CREATE TABLE T1
(
id BIGINT GENERATED ALWAYS AS IDENTITY
(
START WITH 1
|
by: PinkBishop |
last post by:
I am using VS 2005 with a formview control trying to insert a record
to my access db. The data is submitted to the main table no problem,
but I need to carry the catID to the bridge table CatalogImage where
imgID also needs to be placed.
Below is my code behind to carry the catID using the Select @@Identity
and insert imgID to the bridge table.
No data is being entered into the bridge table.
|
by: Frank Swarbrick |
last post by:
So we're trying to decide if it's better to use IDENTITY columns or
sequences to create a surrogate key as the primary key for our tables. I
kind of like the identity column, because it's more 'tightly integrated' in
to the table. With a sequence you have to make sure that each application
that inserts records uses the same sequence. (Probably not likely that it
wouldn't, but...)
One thing where it seems like a SEQUENCE would be...
|
by: marktang |
last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look !
Part I. Meaning of...
|
by: jinu1996 |
last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth.
The Art of Business Website Design
Your website is...
| |
by: Hystou |
last post by:
Overview:
Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
|
by: agi2029 |
last post by:
Let's talk about the concept of autonomous AI software engineers and no-code agents. These AIs are designed to manage the entire lifecycle of a software development project—planning, coding, testing, and deployment—without human intervention. Imagine an AI that can take a project description, break it down, write the code, debug it, and then launch it, all on its own....
Now, this would greatly impact the work of software developers. The idea...
|
by: isladogs |
last post by:
The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM).
In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules.
He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms.
Adolph will...
|
by: TSSRALBI |
last post by:
Hello
I'm a network technician in training and I need your help.
I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs.
The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols.
I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
|
by: adsilva |
last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
|
by: muto222 |
last post by:
How can i add a mobile payment intergratation into php mysql website.
| |
by: bsmnconsultancy |
last post by:
In today's digital era, a well-designed website is crucial for businesses looking to succeed. Whether you're a small business owner or a large corporation in Toronto, having a strong online presence can significantly impact your brand's success. BSMN Consultancy, a leader in Website Development in Toronto offers valuable insights into creating effective websites that not only look great but also perform exceptionally well. In this comprehensive...
| |