Insert is slow are there tweaks?

[Bob Sparks]

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_SCORECAR D_ID, QUALIFIER_ID,
QUALIFIER_NM, STAGING_CREATED _DT)
SELECT YEAR_NR, ucase(TIME_PERI OD_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_SCORECAR D_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

[Mark Yudkin]

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*******@exci te.com> wrote in message
news:9b******** *************** ***@posting.goo gle.com...

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_SCORECAR D_ID, QUALIFIER_ID,
QUALIFIER_NM, STAGING_CREATED _DT)
SELECT YEAR_NR, ucase(TIME_PERI OD_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_SCORECAR D_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

[Phil Gunning]

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************ ***********@nos pam.org> wrote in message news:<cf******* ***@ngspool-d02.news.aol.co m>...

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*******@exci te.com> wrote in message
news:9b******** *************** ***@posting.goo gle.com...

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_SCORECAR D_ID, QUALIFIER_ID,
QUALIFIER_NM, STAGING_CREATED _DT)
SELECT YEAR_NR, ucase(TIME_PERI OD_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_SCORECAR D_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

[Bob Sparks]

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******@gunnin gts.com (Phil Gunning) wrote in message news:<34******* *************** ****@posting.go ogle.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************ ***********@nos pam.org> wrote in message news:<cf******* ***@ngspool-d02.news.aol.co m>...

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*******@exci te.com> wrote in message
news:9b******** *************** ***@posting.goo gle.com...

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_SCORECAR D_ID, QUALIFIER_ID,
QUALIFIER_NM, STAGING_CREATED _DT)
SELECT YEAR_NR, ucase(TIME_PERI OD_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_SCORECAR D_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

[Serge Rielau]

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