Redbrick List Forum

Re: attaching additional segments to index

Posted By: William Gates
Date: Friday, 18 July 2003, at 4:19 p.m.

In Response To: Re: attaching additional segments to index (Mohd Shah M. Sood)

Shah,

A word of advise would be to use an upper bound. As you have seen, RB
requires that the bound you are trying to add or modify the range to be
empty. By creating an upper bound you would be relatively certain that it
would be empty. Since you are loading July. Create a segment for July and
then carve out another segment for August : Max. Most customers typically
create all segments for an entire year instead of having to create them
each month. Either way, make sure you have an upper bound that will be
data free that you can modify.

Now .. for your question. I assume that you haven't modified the PSU sizes
of your 200306 segment. That being the case, I would say that you're out
of file system space.

-- Bill


***************************************************************************
William S. Gates IBM
Consulting I/T Specialist 300 Nichols Avenue
Data Management Svcs. Wilmington, DE 19803

Business Intelligence

IBM Red Brick Warehouse (Certified System Administrator)
DB2 Universal Database V8.1 (Certified Database Associate)

Cell: (302) 438-0779 www.ibm.com
Office: (302) 477-1805 gatesw@us.ibm.com
Fax: (302) 477-1806
eFax: (845) 491-3405

***************************************************************************




"Mohd Shah M...."
<md_shah@tm.net.m To: redbrick-list@iiug.org
y> cc:
Sent by: Subject: Re: attaching additional segments to index [111]
forum.subscriber@
iiug.org


07/17/2003 02:03
AM






This is a multi-part message in MIME format.

--Boundary_(ID_HvWjj+pCC6s+Ij76ZWonww)
Content-type: text/plain; charset=iso-8859-1
Content-transfer-encoding: 7BIT

Thanks for all the feedback. I'm currently trying to attach the index
segment 200307 (i.e. yyyymm). What i found out is that when we have data in
the 200306 segment, I can't attach the 200307 index segment! So what i did
was basically unload and delete all data in my 200306_datasegment before
attaching the index segment... and it worked!

but i'm facing a new problem now. when i try to load the 200306 data back
into the segment, it complained that the 200306 segment is full.. and
stopped after loading about 7 mill rows. (total about 21 mill rows
unloaded). I believe that the space previously used by the deleted data (21
mill row) was not being reused. Anyone knows how I can reuse these space?

fyi, i did a 'delete from table where timestamp >= timestamp'2003-06-01
00:00:00'

I read something about 'alter segment clear' but not too sure if it'll help
solve my problem. what i now have is 200306 data segment with 7 mill rows
of data loaded. What i think i need to do the to delete these 7 mill rows
and somehow get back all the freespace in the segment before i repeat the
loading process.

hope to get some feedback. TIA
shah


"Mohd Shah M...." <md_shah@tm.net.my>
Sent by: forum.subscriber@iiug.org
07/09/2003 06:42 PM


To: redbrick-list@iiug.org
cc:
Subject: attaching additional segments to index [106]





Hi, I'm having trouble attaching additional segments to an index. below are
the scripts i ran:

create segment FACTEVENT_IDX_200307
storage '/dm08/factevent_idx_200307_psu01' maxsize 1000000 initsize 500000
extendsize 100000;
=> this is executed successfully.

alter segment FACTEVENT_IDX_200307 attach to index DM_FACT_EVENT_PK_IDX
range ( TIMESTAMP'2003-07-01 00:00:00': MAX );
=> [Red Brick][ODBC Driver]** FATAL ** (10410) Could not receive from the
server. OS error: No error (0).

and from the logs...
Jul 10 08:25:39 rbwapid[98088] ERR076A: Server PID:110202 terminated with
signal:11 User time:250 System time:730
Jul 10 08:25:48 rbwpchk[108160] OPE157A: Abnormally terminated process has
been detected. PID: 110202.
Jul 10 08:25:48 rbwpchk[108160] OPE6053A: All the shared resources cleanup
has completed.


similarly i've attached additional data segment with identical range values
without any problem. Anyone had this problem before? I've tried running the
script, also tried doing it through the 'manage segments' GUI wizard...
both
failed. hope to get some suggestions on how to fix this.

i'm running redbrick 6.11 on AIX5.1, and it's a BTREE index

thanks
shah





--Boundary_(ID_HvWjj+pCC6s+Ij76ZWonww)
Content-type: text/html; charset=iso-8859-1
Content-transfer-encoding: 7BIT

<!DOCTYPE HTML PUBLIC "-//W3C//DTD HTML 4.0 Transitional//EN">
<HTML><HEAD>
<META http-equiv=Content-Type content="text/html; charset=iso-8859-1">
<META content="MSHTML 6.00.2800.1106" name=GENERATOR>
<STYLE></STYLE>
</HEAD>
<BODY bgColor=#ffffff>
<DIV><FONT face=Arial size=2>Thanks for all the feedback. I'm currently
trying
to attach the index segment 200307 (i.e. yyyymm). What i found out is that
when
we have data in the 200306 segment, I can't attach the 200307 index
segment! So
what i did was basically unload and delete&nbsp;all data in my
200306_datasegment before attaching the index segment... and it
worked!</FONT></DIV>
<DIV>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>but i'm facing a new problem now. when i try
to
load the 200306 data back into the segment, it complained that the 200306
segment is full.. and stopped after loading about 7 mill rows. (total about
21
mill rows unloaded). I believe that the space previously used by the
deleted
data (21 mill row) was not being reused. Anyone knows how I can reuse these

space? </FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>fyi, i did a 'delete from table where
timestamp
&gt;= timestamp'2003-06-01 00:00:00'</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>I read something about 'alter segment clear'
but
not too sure if it'll help solve my&nbsp;problem. what i now have is 200306
data
segment with 7 mill rows of data loaded. What i think i need to do the to
delete
these 7 mill rows and somehow get back all the freespace in the segment
before i
repeat the loading process.</FONT></DIV>
<DIV><FONT face=Arial size=2></FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2>hope to get some feedback. TIA</FONT></DIV>
<DIV><FONT face=Arial size=2>shah</FONT>&nbsp;</DIV>
<DIV><FONT face=Arial size=2></FONT><FONT face=Arial
size=2></FONT><BR>&nbsp;</DIV>
<DIV>
<TABLE width="100%">
<TBODY>
<TR vAlign=top>
<TD>
<TD><FONT face=sans-serif size=1><B>"Mohd Shah M...."
&lt;md_shah@tm.net.my&gt;</B></FONT> <BR><FONT face=sans-serif
size=1>Sent
by: forum.subscriber@iiug.org</FONT>
<P><FONT face=sans-serif size=1>07/09/2003 06:42 PM</FONT> <BR></P>
<TD><FONT face=Arial size=1>&nbsp; &nbsp; &nbsp; &nbsp;
</FONT><BR><FONT
face=sans-serif size=1>&nbsp; &nbsp; &nbsp; &nbsp; To: &nbsp; &nbsp;
&nbsp; &nbsp;redbrick-list@iiug.org</FONT> <BR><FONT face=sans-serif
size=1>&nbsp; &nbsp; &nbsp; &nbsp; cc: &nbsp; &nbsp; &nbsp;
&nbsp;</FONT>
<BR><FONT face=sans-serif size=1>&nbsp; &nbsp; &nbsp; &nbsp; Subject:

&nbsp; &nbsp; &nbsp; &nbsp;attaching additional segments to index
[106]</FONT> <BR></TR></TBODY></TABLE></DIV>
<DIV><BR><BR><BR><FONT face="Courier New" size=2>Hi, I'm having trouble
attaching additional segments to an index. below are<BR>the scripts i
ran:<BR><BR>create segment FACTEVENT_IDX_200307<BR>storage
'/dm08/factevent_idx_200307_psu01' maxsize 1000000 initsize
500000<BR>extendsize
100000;<BR>=&gt; this is executed successfully.<BR><BR>alter segment
FACTEVENT_IDX_200307 attach to index DM_FACT_EVENT_PK_IDX<BR>range (
TIMESTAMP'2003-07-01 00:00:00': MAX );<BR>=&gt; [Red Brick][ODBC Driver]**
FATAL
** (10410) Could not receive from the<BR>server. OS error: No error
(0).<BR><BR>and from the logs...<BR>Jul 10 08:25:39 rbwapid[98088] ERR076A:

Server PID:110202 terminated with<BR>signal:11 User time:250 System
time:730<BR>Jul 10 08:25:48 rbwpchk[108160] OPE157A: Abnormally terminated
process has<BR>been detected. PID: 110202.<BR>Jul 10 08:25:48
rbwpchk[108160]
OPE6053A: All the shared resources cleanup<BR>has
completed.<BR><BR><BR>similarly i've attached additional data segment with
identical range values<BR>without any problem. Anyone had this problem
before?
I've tried running the<BR>script, also tried doing it through the 'manage
segments' GUI wizard... both<BR>failed. hope to get some suggestions on how
to
fix this.<BR><BR>i'm running redbrick 6.11 on AIX5.1, and it's a BTREE
index<BR><BR>thanks<BR>shah<BR><BR><BR></FONT><BR><BR></DIV></BODY></HTML>

--Boundary_(ID_HvWjj+pCC6s+Ij76ZWonww)--

Messages In This Thread

• attaching additional segments to index
  Mohd Shah M. Sood -- Wednesday, 9 July 2003, at 9:42 p.m.
  • Re: attaching additional segments to index
    Mohd Shah M. Sood -- Thursday, 17 July 2003, at 3:03 a.m.
    • Re: attaching additional segments to index
      William Gates -- Friday, 18 July 2003, at 4:19 p.m.

Redbrick List Forum is maintained by Administrator with WebBBS 5.12.