473,320 Members | 2,004 Online
Bytes | Software Development & Data Engineering Community
Post Job

Home Posts Topics Members FAQ

Join Bytes to post your question to a community of 473,320 software developers and data experts.

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
-----------------------------------

Nov 12 '05 #1
5 6360
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! =-----
Nov 12 '05 #2
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! =-----

Nov 12 '05 #3
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
-----------------------------------


Nov 12 '05 #4
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
-----------------------------------

Nov 12 '05 #5
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 ?


Nov 12 '05 #6

This thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

5
by: White Wolf | last post by:
Hi, I would like to double check how long a temporary returned by a function lives? Suppose I have an instance of a class type C, which has a member function returning some sort of...
18
by: zebi | last post by:
hello, What's your opinion : The best type of temporary tablespace (DB2V7.2 SP 7 in AIX 4.3 / multiprocessor ) is DMS ou SMS ? Thanks ZEB
4
by: prasad | last post by:
I am getting sql error during binding a program which access a temporary table. The temporary table declaration and access methods are given below. EXEC SQL DECLARE GLOBAL TEMPORARY TABLE TEM88...
6
by: gimme_this_gimme_that | last post by:
I'm new to DB2 ... The following statement results in a SQL1585N message : "A system temporary table space with sufficient page size does not exist" Note that the column FIELD_DETAIL is a...
6
by: Neo The One | last post by:
On my development machine, I often delete all folders/files under C:\WINDOWS\Microsoft.NET\Framework\v1.1.4322\Temporary ASP.NET Files. That has not caused me any trouble til now. So now we have...
4
by: Anbu | last post by:
Hi all, I have developed an application in VS .NET 2003 (framework 1.1) on a XP platform. It is working fine in that PC. Now, I setup new PC with same configuration for another develper....
1
by: thermate | last post by:
diapers, wig, knife ... guys i am dying of laughter, someone help me ... Seems like the Neoconish spirit of Bush/Cheney has permeated the whole country thru the cell phones and cause the yanks...
11
by: rawu | last post by:
hi , all . I got a question about creating indexes in db2 . a table has 44236333 rows an index planed to be build include 2 field ( 6 byte ) accordding to db2 document temporary...
1
by: ellenraju | last post by:
When we create an object in oracle we can specify table space, if not it will take default table space, while creating global temporary table how can we specify the table space...and even if we...
0
by: ryjfgjl | last post by:
ExcelToDatabase: batch import excel into database automatically...
0
by: Vimpel783 | last post by:
Hello! Guys, I found this code on the Internet, but I need to modify it a little. It works well, the problem is this: Data is sent from only one cell, in this case B5, but it is necessary that data...
0
by: ArrayDB | last post by:
The error message I've encountered is; ERROR:root:Error generating model response: exception: access violation writing 0x0000000000005140, which seems to be indicative of an access violation...
1
by: PapaRatzi | last post by:
Hello, I am teaching myself MS Access forms design and Visual Basic. I've created a table to capture a list of Top 30 singles and forms to capture new entries. The final step is a form (unbound)...
1
by: CloudSolutions | last post by:
Introduction: For many beginners and individual users, requiring a credit card and email registration may pose a barrier when starting to use cloud servers. However, some cloud server providers now...
1
by: Defcon1945 | last post by:
I'm trying to learn Python using Pycharm but import shutil doesn't work
1
by: Shællîpôpï 09 | last post by:
If u are using a keypad phone, how do u turn on JavaScript, to access features like WhatsApp, Facebook, Instagram....
0
by: af34tf | last post by:
Hi Guys, I have a domain whose name is BytesLimited.com, and I want to sell it. Does anyone know about platforms that allow me to list my domain in auction for free. Thank you
0
isladogs
by: isladogs | last post by:
The next Access Europe User Group meeting will be on Wednesday 3 Apr 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 former...

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.