Temporary Space during LOAD

[N]

Hi,
I got an error during load on a couple of
the tables. And it seems to be complaining that
I'm running out of tempspace (possibly during
index rebuild). Below is the load command and
error. What I do not understand is why DB2 is
trying to rebuild the index in TBS_STEMP03 even
though I explicitly tell it to use TBS_STEMP01
in the load statement. According to my calculation,
TBS_STEMP01 should have enough space for LOAD
to rebuild the indexes.
--------------------------------
Tablespace ID = 4
Name = TBS_STEMP01
Type = Database managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1024000
Useable pages = 1023984
Used pages = 48
Free pages = 1023936
High water mark (pages) = 48
Page size (bytes) = 4096
Extent size (pages) = 12
Prefetch size (pages) = 12
Number of containers = 1
--------------------------------
Tablespace ID = 115
Name = TBS_STEMP03
Type = Database managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 256000
Useable pages = 255996
Used pages = 6
Free pages = 255990
High water mark (pages) = 195552
Page size (bytes) = 16384
Extent size (pages) = 3
Prefetch size (pages) = 3
Number of containers = 1
--------------------------------
CREATE TABLE db2.vch_benefit
(
vch_benefit_id INTEGER GENERATED BY DEFAULT
AS IDENTITY NOT NULL,
vh_rate_id INTEGER,
tax_burden_id CHAR(2) NOT NULL,
subj_type_id CHAR(10) NOT NULL,
aff_lcl_cnt_eff_id INTEGER NOT NULL,
ben_rate_id INTEGER,
voucher_id INTEGER NOT NULL,
base_amt FLOAT DEFAULT 0.0000000000000000 NOT NULL,
amount FLOAT DEFAULT 0.0000000000000000 NOT NULL,
subject_amt FLOAT DEFAULT 0.0000000000000000 NOT NULL,
user_id INTEGER,
is_benefit CHAR(1) DEFAULT 'N' NOT NULL,
accrual_amt FLOAT DEFAULT 0.0000000000000000 NOT NULL,
ytd_amount FLOAT DEFAULT 0.0000000000000000,
CONSTRAINT y_n10380 CHECK (is_benefit IN ('Y','N'))
) IN tbsdata08;

CREATE UNIQUE INDEX db2.xak1vch_benefit
ON db2.vch_benefit(voucher_id,ben_rate_id,vh_rate_id,
subj_type_id,tax_burden_id);
CREATE UNIQUE INDEX db2.xpkvch_benefit
ON db2.vch_benefit(vch_benefit_id);
CREATE INDEX db2.xif1277vch_benefit
ON db2.vch_benefit(ben_rate_id);
CREATE INDEX db2.xif1790vch_benefit
ON db2.vch_benefit(tax_burden_id);
CREATE INDEX db2.xif1748vch_benefit
ON db2.vch_benefit(aff_lcl_cnt_eff_id);
CREATE INDEX db2.xif1798vch_benefit
ON db2.vch_benefit(vh_rate_id);
CREATE INDEX db2.xif430vch_benefit
ON db2.vch_benefit(voucher_id);
CREATE INDEX db2.xif1789vch_benefit
ON db2.vch_benefit(subj_type_id);
-----------------------------------
-- VCH_BENEFIT.out FILE IS 1.65GB large holding approximately 22,000,000
rows
-- LOAD statement
LOAD CLIENT FROM
D:\MTK_infx\projects\\DataOutScripts\db2_vch_benef it.out of
DEL MODIFIED BY coldel| anyorder delprioritychar
keepblanks METHOD P(1,2,3,4,5,6,7,8,9,10,11,12,13,14)
MESSAGES messege.out INSERT INTO db2.vch_benefit
INDEXING MODE REBUILD ALLOW READ ACCESS USE TBS_STEMP01
-- ERROR CODE
SQL0289N Unable to allocate new pages in table space "TBS_STEMP03".
SQLSTATE=57011
-----------------------------------

[Ian]

N wrote:

Hi,
I got an error during load on a couple of
the tables. And it seems to be complaining that
I'm running out of tempspace (possibly during
index rebuild). Below is the load command and
error. What I do not understand is why DB2 is
trying to rebuild the index in TBS_STEMP03 even
though I explicitly tell it to use TBS_STEMP01
in the load statement. According to my calculation,
TBS_STEMP01 should have enough space for LOAD
to rebuild the indexes.
--------------------------------
Tablespace ID = 4
Name = TBS_STEMP01
Type = Database managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1024000
Useable pages = 1023984
Used pages = 48
Free pages = 1023936
High water mark (pages) = 48
Page size (bytes) = 4096
Extent size (pages) = 12
Prefetch size (pages) = 12
Number of containers = 1
--------------------------------
Tablespace ID = 115
Name = TBS_STEMP03
Type = Database managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 256000
Useable pages = 255996
Used pages = 6
Free pages = 255990
High water mark (pages) = 195552
Page size (bytes) = 16384
Extent size (pages) = 3
Prefetch size (pages) = 3
Number of containers = 1
--------------------------------
CREATE TABLE db2.vch_benefit
(
vch_benefit_id INTEGER GENERATED BY DEFAULT
AS IDENTITY NOT NULL,
vh_rate_id INTEGER,
tax_burden_id CHAR(2) NOT NULL,
subj_type_id CHAR(10) NOT NULL,
aff_lcl_cnt_eff_id INTEGER NOT NULL,
ben_rate_id INTEGER,
voucher_id INTEGER NOT NULL,
base_amt FLOAT DEFAULT 0.0000000000000000 NOT NULL,
amount FLOAT DEFAULT 0.0000000000000000 NOT NULL,
subject_amt FLOAT DEFAULT 0.0000000000000000 NOT NULL,
user_id INTEGER,
is_benefit CHAR(1) DEFAULT 'N' NOT NULL,
accrual_amt FLOAT DEFAULT 0.0000000000000000 NOT NULL,
ytd_amount FLOAT DEFAULT 0.0000000000000000,
CONSTRAINT y_n10380 CHECK (is_benefit IN ('Y','N'))
) IN tbsdata08;

CREATE UNIQUE INDEX db2.xak1vch_benefit
ON db2.vch_benefit(voucher_id,ben_rate_id,vh_rate_id,
subj_type_id,tax_burden_id);
CREATE UNIQUE INDEX db2.xpkvch_benefit
ON db2.vch_benefit(vch_benefit_id);
CREATE INDEX db2.xif1277vch_benefit
ON db2.vch_benefit(ben_rate_id);
CREATE INDEX db2.xif1790vch_benefit
ON db2.vch_benefit(tax_burden_id);
CREATE INDEX db2.xif1748vch_benefit
ON db2.vch_benefit(aff_lcl_cnt_eff_id);
CREATE INDEX db2.xif1798vch_benefit
ON db2.vch_benefit(vh_rate_id);
CREATE INDEX db2.xif430vch_benefit
ON db2.vch_benefit(voucher_id);
CREATE INDEX db2.xif1789vch_benefit
ON db2.vch_benefit(subj_type_id);
-----------------------------------
-- VCH_BENEFIT.out FILE IS 1.65GB large holding approximately 22,000,000
rows
-- LOAD statement
LOAD CLIENT FROM
D:\MTK_infx\projects\\DataOutScripts\db2_vch_benef it.out of
DEL MODIFIED BY coldel| anyorder delprioritychar
keepblanks METHOD P(1,2,3,4,5,6,7,8,9,10,11,12,13,14)
MESSAGES messege.out INSERT INTO db2.vch_benefit
INDEXING MODE REBUILD ALLOW READ ACCESS USE TBS_STEMP01
-- ERROR CODE
SQL0289N Unable to allocate new pages in table space "TBS_STEMP03".
SQLSTATE=57011
-----------------------------------

What's the page size of the TBDATA08 tablespace?

I would guess that it's 16K, and that's why the indexes get built in
STEMP03 (16k page) instead of STEMP01 (4k page).
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[N]

TBSDATA08 has 4K Page Size
TBS_STEMP01 is 4K
TBS_STEMP02 is 8K
TBS_STEMP03 is 16K

That's what I don't get either. Why is it trying to
build in TBS_STEMP03 ? From what you see,
are my TEMP SPACES big enough ?

Thank you for your input.

"Ian" <ia*****@mobileaudio.com> wrote in message
news:41**********@corp.newsgroups.com...

N wrote:

Hi,
I got an error during load on a couple of
the tables. And it seems to be complaining that
I'm running out of tempspace (possibly during
index rebuild). Below is the load command and
error. What I do not understand is why DB2 is
trying to rebuild the index in TBS_STEMP03 even
though I explicitly tell it to use TBS_STEMP01
in the load statement. According to my calculation,
TBS_STEMP01 should have enough space for LOAD
to rebuild the indexes.
--------------------------------
Tablespace ID = 4
Name = TBS_STEMP01
Type = Database managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 1024000
Useable pages = 1023984
Used pages = 48
Free pages = 1023936
High water mark (pages) = 48
Page size (bytes) = 4096
Extent size (pages) = 12
Prefetch size (pages) = 12
Number of containers = 1
--------------------------------
Tablespace ID = 115
Name = TBS_STEMP03
Type = Database managed space
Contents = System Temporary data
State = 0x0000
Detailed explanation:
Normal
Total pages = 256000
Useable pages = 255996
Used pages = 6
Free pages = 255990
High water mark (pages) = 195552
Page size (bytes) = 16384
Extent size (pages) = 3
Prefetch size (pages) = 3
Number of containers = 1
--------------------------------
CREATE TABLE db2.vch_benefit
(
vch_benefit_id INTEGER GENERATED BY DEFAULT
AS IDENTITY NOT NULL,
vh_rate_id INTEGER,
tax_burden_id CHAR(2) NOT NULL,
subj_type_id CHAR(10) NOT NULL,
aff_lcl_cnt_eff_id INTEGER NOT NULL,
ben_rate_id INTEGER,
voucher_id INTEGER NOT NULL,
base_amt FLOAT DEFAULT 0.0000000000000000 NOT NULL,
amount FLOAT DEFAULT 0.0000000000000000 NOT NULL,
subject_amt FLOAT DEFAULT 0.0000000000000000 NOT NULL,
user_id INTEGER,
is_benefit CHAR(1) DEFAULT 'N' NOT NULL,
accrual_amt FLOAT DEFAULT 0.0000000000000000 NOT NULL,
ytd_amount FLOAT DEFAULT 0.0000000000000000,
CONSTRAINT y_n10380 CHECK (is_benefit IN ('Y','N'))
) IN tbsdata08;

CREATE UNIQUE INDEX db2.xak1vch_benefit
ON db2.vch_benefit(voucher_id,ben_rate_id,vh_rate_id,
subj_type_id,tax_burden_id);
CREATE UNIQUE INDEX db2.xpkvch_benefit
ON db2.vch_benefit(vch_benefit_id);
CREATE INDEX db2.xif1277vch_benefit
ON db2.vch_benefit(ben_rate_id);
CREATE INDEX db2.xif1790vch_benefit
ON db2.vch_benefit(tax_burden_id);
CREATE INDEX db2.xif1748vch_benefit
ON db2.vch_benefit(aff_lcl_cnt_eff_id);
CREATE INDEX db2.xif1798vch_benefit
ON db2.vch_benefit(vh_rate_id);
CREATE INDEX db2.xif430vch_benefit
ON db2.vch_benefit(voucher_id);
CREATE INDEX db2.xif1789vch_benefit
ON db2.vch_benefit(subj_type_id);
-----------------------------------
-- VCH_BENEFIT.out FILE IS 1.65GB large holding approximately 22,000,000
rows
-- LOAD statement
LOAD CLIENT FROM
D:\MTK_infx\projects\\DataOutScripts\db2_vch_benef it.out of
DEL MODIFIED BY coldel| anyorder delprioritychar
keepblanks METHOD P(1,2,3,4,5,6,7,8,9,10,11,12,13,14)
MESSAGES messege.out INSERT INTO db2.vch_benefit
INDEXING MODE REBUILD ALLOW READ ACCESS USE TBS_STEMP01
-- ERROR CODE
SQL0289N Unable to allocate new pages in table space "TBS_STEMP03".
SQLSTATE=57011
-----------------------------------

What's the page size of the TBDATA08 tablespace?

I would guess that it's 16K, and that's why the indexes get built in
STEMP03 (16k page) instead of STEMP01 (4k page).
-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[Philip Sherman]

INDEXING MODE REBUILD ALLOW READ ACCESS USE TBS_STEMP01 specifies that
the new indexes are to be built in TBS_STEMP01 then copied to the target
location for the index. This avoids requiring the index tablespace to be
large enough to hold two copies of the index at the same time.

This also says nothing about the work space needed to build the indexes!

You are concurrently constructing 8 indexes totalling over 175 million
rows. Each row, during construction of the indexes, must contain the
index data, a locator for the data row, an identifier for the target
index and probably some sort overhead. The merge technique used during
large sorts also effects the size of the workspace needed. Your index
rows are very small (most contain a single integer) and may also run
into maximum rows/page limits during the sorting phase. (Note that only
someone who is intimately aquainted with the internals of how db2's sort
and the load process works would be able to make a definitive statement
about rows/page during sorts. I'd assume that the sort overflow work
pages are subject to the same rows/page limits as other pages.)

My guess is that the 16k work space is being used as a work area to
support the sorting processes and it ran out of space.

Phil Sherman
N wrote:

Hi, -----------------------------------
-- VCH_BENEFIT.out FILE IS 1.65GB large holding approximately 22,000,000
rows
-- LOAD statement
LOAD CLIENT FROM
D:\MTK_infx\projects\\DataOutScripts\db2_vch_benef it.out of
DEL MODIFIED BY coldel| anyorder delprioritychar
keepblanks METHOD P(1,2,3,4,5,6,7,8,9,10,11,12,13,14)
MESSAGES messege.out INSERT INTO db2.vch_benefit
INDEXING MODE REBUILD ALLOW READ ACCESS USE TBS_STEMP01
-- ERROR CODE
SQL0289N Unable to allocate new pages in table space "TBS_STEMP03".
SQLSTATE=57011
-----------------------------------

[N]

So I will need to add more spaces to TBS_STEMP03 ?
To what size ? Is there a way to calculate how much
space is being used during the LOAD process in the
temp space ?

"Philip Sherman" <ps******@ameritech.net> wrote in message
news:pi*****************@newssvr15.news.prodigy.co m...

INDEXING MODE REBUILD ALLOW READ ACCESS USE TBS_STEMP01 specifies that
the new indexes are to be built in TBS_STEMP01 then copied to the target
location for the index. This avoids requiring the index tablespace to be
large enough to hold two copies of the index at the same time.

This also says nothing about the work space needed to build the indexes!

You are concurrently constructing 8 indexes totalling over 175 million
rows. Each row, during construction of the indexes, must contain the
index data, a locator for the data row, an identifier for the target
index and probably some sort overhead. The merge technique used during
large sorts also effects the size of the workspace needed. Your index
rows are very small (most contain a single integer) and may also run
into maximum rows/page limits during the sorting phase. (Note that only
someone who is intimately aquainted with the internals of how db2's sort
and the load process works would be able to make a definitive statement
about rows/page during sorts. I'd assume that the sort overflow work
pages are subject to the same rows/page limits as other pages.)

My guess is that the 16k work space is being used as a work area to
support the sorting processes and it ran out of space.

Phil Sherman
N wrote:

Hi,

-----------------------------------
-- VCH_BENEFIT.out FILE IS 1.65GB large holding approximately 22,000,000
rows
-- LOAD statement
LOAD CLIENT FROM
D:\MTK_infx\projects\\DataOutScripts\db2_vch_benef it.out of
DEL MODIFIED BY coldel| anyorder delprioritychar
keepblanks METHOD P(1,2,3,4,5,6,7,8,9,10,11,12,13,14)
MESSAGES messege.out INSERT INTO db2.vch_benefit
INDEXING MODE REBUILD ALLOW READ ACCESS USE TBS_STEMP01
-- ERROR CODE
SQL0289N Unable to allocate new pages in table space "TBS_STEMP03".
SQLSTATE=57011
-----------------------------------

[Philip Sherman]

We're getting into where you should be requesting billable assistance.
Hopefully, I've given you enough ideas that you can research your own
answer.

Phil Sherman
N wrote:

So I will need to add more spaces to TBS_STEMP03 ?
To what size ? Is there a way to calculate how much
space is being used during the LOAD process in the
temp space ?