By using this site, you agree to our updated Privacy Policy and our Terms of Use. Manage your Cookies Settings.
438,514 Members | 1,702 Online
Bytes IT Community
+ Ask a Question
Need help? Post your question and get tips & solutions from a community of 438,514 IT Pros & Developers. It's quick & easy.

Insert is slow are there tweaks?

P: n/a
Hello

I am running an insert that takes an awfully long time compared to the
same workload on Oracle.
It takes 44 minutes to insert 2.5 million records. It takes less time
to insert the records into the source table loading from disk.
The statement is below as is information about the table space.

So my questions are

1. does the lock affect the insert?
2. would a lock on the target table help?
3. would separate table spaces help/
4. do I have the right buffer size would a larger help?
5. what other parameters can I tweak?
6. is DB2 just slower than Oracle at inserts?

Thanks

Bob

The statements

LOCK TABLE KPI_VALUE_STAGE IN EXCLUSIVE MODE;

INSERT INTO kvsr_temp(YEAR_NR, TIME_PERIOD_CD, PERIOD_NR, DAY_NR,
KPI_ID,
VALUE_TYPE_CD, KPI_VALUE, CURRENCY_CD,
VALID_AS_AT_DT, KPI_NM, SCORECARD_ID,
SCORECARD_NM,
KPI_CLASS_ID, DATA_SOURCE_ID, DATA_SOURCE_NM,
PARENT_SCORECARD_ID, QUALIFIER_ID,
QUALIFIER_NM, STAGING_CREATED_DT)
SELECT YEAR_NR, ucase(TIME_PERIOD_CD), PERIOD_NR, DAY_NR, KPI_ID,
VALUE_TYPE_CD, KPI_VALUE, ucase(CURRENCY_CD),
VALID_AS_AT_DT,
KPI_NM, SCORECARD_ID, SCORECARD_NM, KPI_CLASS_ID,
DATA_SOURCE_ID,
DATA_SOURCE_NM, PARENT_SCORECARD_ID, QUALIFIER_ID,
QUALIFIER_NM, CREATED_DT
FROM KPI_VALUE_STAGE AS KVS;
Source table is created

NOT LOGGED INITIALLY
IN USERSPACE4K

and the destination table is created

NOT LOGGED INITIALLY
IN USERSPACE4K
it has no indexes but has an identity.

KVSR_TEMP_SID INT GENERATED ALWAYS AS IDENTITY(start with 1, increment
by 1, cache 20000, CYCLE)
NOT NULL,

The buffer pool

CREATE BUFFERPOOL "BP4K"
SIZE 1920
PAGESIZE 4 K
NOT EXTENDED STORAGE

The tablespace

CREATE REGULAR TABLESPACE USERSPACE4K
IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 4 K
MANAGED BY SYSTEM
USING ('USERSPACE4K')
EXTENTSIZE 8
PREFETCHSIZE 8
BUFFERPOOL BP4K
DROPPED TABLE RECOVERY ON
Nov 12 '05 #1
Share this Question
Share on Google+
4 Replies


P: n/a
1. Yes.
2. Yes.
3. Depends on disk layout
4 . No. Yes (you asked 2 questions)
5. Make sure you have no Autocommit. Blocking.
6. No, it's faster.

"Bob Sparks" <Bo*******@excite.com> wrote in message
news:9b**************************@posting.google.c om...
Hello

I am running an insert that takes an awfully long time compared to the
same workload on Oracle.
It takes 44 minutes to insert 2.5 million records. It takes less time
to insert the records into the source table loading from disk.
The statement is below as is information about the table space.

So my questions are

1. does the lock affect the insert?
2. would a lock on the target table help?
3. would separate table spaces help/
4. do I have the right buffer size would a larger help?
5. what other parameters can I tweak?
6. is DB2 just slower than Oracle at inserts?

Thanks

Bob

The statements

LOCK TABLE KPI_VALUE_STAGE IN EXCLUSIVE MODE;

INSERT INTO kvsr_temp(YEAR_NR, TIME_PERIOD_CD, PERIOD_NR, DAY_NR,
KPI_ID,
VALUE_TYPE_CD, KPI_VALUE, CURRENCY_CD,
VALID_AS_AT_DT, KPI_NM, SCORECARD_ID,
SCORECARD_NM,
KPI_CLASS_ID, DATA_SOURCE_ID, DATA_SOURCE_NM,
PARENT_SCORECARD_ID, QUALIFIER_ID,
QUALIFIER_NM, STAGING_CREATED_DT)
SELECT YEAR_NR, ucase(TIME_PERIOD_CD), PERIOD_NR, DAY_NR, KPI_ID,
VALUE_TYPE_CD, KPI_VALUE, ucase(CURRENCY_CD),
VALID_AS_AT_DT,
KPI_NM, SCORECARD_ID, SCORECARD_NM, KPI_CLASS_ID,
DATA_SOURCE_ID,
DATA_SOURCE_NM, PARENT_SCORECARD_ID, QUALIFIER_ID,
QUALIFIER_NM, CREATED_DT
FROM KPI_VALUE_STAGE AS KVS;
Source table is created

NOT LOGGED INITIALLY
IN USERSPACE4K

and the destination table is created

NOT LOGGED INITIALLY
IN USERSPACE4K
it has no indexes but has an identity.

KVSR_TEMP_SID INT GENERATED ALWAYS AS IDENTITY(start with 1, increment
by 1, cache 20000, CYCLE)
NOT NULL,

The buffer pool

CREATE BUFFERPOOL "BP4K"
SIZE 1920
PAGESIZE 4 K
NOT EXTENDED STORAGE

The tablespace

CREATE REGULAR TABLESPACE USERSPACE4K
IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 4 K
MANAGED BY SYSTEM
USING ('USERSPACE4K')
EXTENTSIZE 8
PREFETCHSIZE 8
BUFFERPOOL BP4K
DROPPED TABLE RECOVERY ON

Nov 12 '05 #2

P: n/a
You have to activate not logged initially in the same unit of work,
else it will log. Are you doing that? Can also reduce number of FSCRs
searched and also look at append on if inserts are the priority.
Rgds, Phil
"Mark Yudkin" <my***********************@nospam.org> wrote in message news:<cf**********@ngspool-d02.news.aol.com>...
1. Yes.
2. Yes.
3. Depends on disk layout
4 . No. Yes (you asked 2 questions)
5. Make sure you have no Autocommit. Blocking.
6. No, it's faster.

"Bob Sparks" <Bo*******@excite.com> wrote in message
news:9b**************************@posting.google.c om...
Hello

I am running an insert that takes an awfully long time compared to the
same workload on Oracle.
It takes 44 minutes to insert 2.5 million records. It takes less time
to insert the records into the source table loading from disk.
The statement is below as is information about the table space.

So my questions are

1. does the lock affect the insert?
2. would a lock on the target table help?
3. would separate table spaces help/
4. do I have the right buffer size would a larger help?
5. what other parameters can I tweak?
6. is DB2 just slower than Oracle at inserts?

Thanks

Bob

The statements

LOCK TABLE KPI_VALUE_STAGE IN EXCLUSIVE MODE;

INSERT INTO kvsr_temp(YEAR_NR, TIME_PERIOD_CD, PERIOD_NR, DAY_NR,
KPI_ID,
VALUE_TYPE_CD, KPI_VALUE, CURRENCY_CD,
VALID_AS_AT_DT, KPI_NM, SCORECARD_ID,
SCORECARD_NM,
KPI_CLASS_ID, DATA_SOURCE_ID, DATA_SOURCE_NM,
PARENT_SCORECARD_ID, QUALIFIER_ID,
QUALIFIER_NM, STAGING_CREATED_DT)
SELECT YEAR_NR, ucase(TIME_PERIOD_CD), PERIOD_NR, DAY_NR, KPI_ID,
VALUE_TYPE_CD, KPI_VALUE, ucase(CURRENCY_CD),
VALID_AS_AT_DT,
KPI_NM, SCORECARD_ID, SCORECARD_NM, KPI_CLASS_ID,
DATA_SOURCE_ID,
DATA_SOURCE_NM, PARENT_SCORECARD_ID, QUALIFIER_ID,
QUALIFIER_NM, CREATED_DT
FROM KPI_VALUE_STAGE AS KVS;
Source table is created

NOT LOGGED INITIALLY
IN USERSPACE4K

and the destination table is created

NOT LOGGED INITIALLY
IN USERSPACE4K
it has no indexes but has an identity.

KVSR_TEMP_SID INT GENERATED ALWAYS AS IDENTITY(start with 1, increment
by 1, cache 20000, CYCLE)
NOT NULL,

The buffer pool

CREATE BUFFERPOOL "BP4K"
SIZE 1920
PAGESIZE 4 K
NOT EXTENDED STORAGE

The tablespace

CREATE REGULAR TABLESPACE USERSPACE4K
IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 4 K
MANAGED BY SYSTEM
USING ('USERSPACE4K')
EXTENTSIZE 8
PREFETCHSIZE 8
BUFFERPOOL BP4K
DROPPED TABLE RECOVERY ON

Nov 12 '05 #3

P: n/a
Just to be sure a unit of work is everything between begin transaction
and commit or rollback? Or is it everything on the connection until
log off? Is it true the "not logged" feature get turned off after a
commit?

Thanks

Bob

pg******@gunningts.com (Phil Gunning) wrote in message news:<34**************************@posting.google. com>...
You have to activate not logged initially in the same unit of work,
else it will log. Are you doing that? Can also reduce number of FSCRs
searched and also look at append on if inserts are the priority.
Rgds, Phil
"Mark Yudkin" <my***********************@nospam.org> wrote in message news:<cf**********@ngspool-d02.news.aol.com>...
1. Yes.
2. Yes.
3. Depends on disk layout
4 . No. Yes (you asked 2 questions)
5. Make sure you have no Autocommit. Blocking.
6. No, it's faster.

"Bob Sparks" <Bo*******@excite.com> wrote in message
news:9b**************************@posting.google.c om...
Hello

I am running an insert that takes an awfully long time compared to the
same workload on Oracle.
It takes 44 minutes to insert 2.5 million records. It takes less time
to insert the records into the source table loading from disk.
The statement is below as is information about the table space.

So my questions are

1. does the lock affect the insert?
2. would a lock on the target table help?
3. would separate table spaces help/
4. do I have the right buffer size would a larger help?
5. what other parameters can I tweak?
6. is DB2 just slower than Oracle at inserts?

Thanks

Bob

The statements

LOCK TABLE KPI_VALUE_STAGE IN EXCLUSIVE MODE;

INSERT INTO kvsr_temp(YEAR_NR, TIME_PERIOD_CD, PERIOD_NR, DAY_NR,
KPI_ID,
VALUE_TYPE_CD, KPI_VALUE, CURRENCY_CD,
VALID_AS_AT_DT, KPI_NM, SCORECARD_ID,
SCORECARD_NM,
KPI_CLASS_ID, DATA_SOURCE_ID, DATA_SOURCE_NM,
PARENT_SCORECARD_ID, QUALIFIER_ID,
QUALIFIER_NM, STAGING_CREATED_DT)
SELECT YEAR_NR, ucase(TIME_PERIOD_CD), PERIOD_NR, DAY_NR, KPI_ID,
VALUE_TYPE_CD, KPI_VALUE, ucase(CURRENCY_CD),
VALID_AS_AT_DT,
KPI_NM, SCORECARD_ID, SCORECARD_NM, KPI_CLASS_ID,
DATA_SOURCE_ID,
DATA_SOURCE_NM, PARENT_SCORECARD_ID, QUALIFIER_ID,
QUALIFIER_NM, CREATED_DT
FROM KPI_VALUE_STAGE AS KVS;
Source table is created

NOT LOGGED INITIALLY
IN USERSPACE4K

and the destination table is created

NOT LOGGED INITIALLY
IN USERSPACE4K
it has no indexes but has an identity.

KVSR_TEMP_SID INT GENERATED ALWAYS AS IDENTITY(start with 1, increment
by 1, cache 20000, CYCLE)
NOT NULL,

The buffer pool

CREATE BUFFERPOOL "BP4K"
SIZE 1920
PAGESIZE 4 K
NOT EXTENDED STORAGE

The tablespace

CREATE REGULAR TABLESPACE USERSPACE4K
IN DATABASE PARTITION GROUP IBMDEFAULTGROUP
PAGESIZE 4 K
MANAGED BY SYSTEM
USING ('USERSPACE4K')
EXTENTSIZE 8
PREFETCHSIZE 8
BUFFERPOOL BP4K
DROPPED TABLE RECOVERY ON

Nov 12 '05 #4

P: n/a
Yes, a unit of work is ended with COMMIT or ROLLBACK at which point teh
next unit of work implicitly starts.
The NOT LOGGED INITIALLY property gets reset at teh end of a unit of work.
If you want to do some work not logged maybe global temporary tables are
an option? They can permanently be without logging.

Cheers
Serge
Nov 12 '05 #5

This discussion thread is closed

Replies have been disabled for this discussion.