See below for some answers in your message.
Villa Horacio wrote:
> Hi,
> we are installing DB2 V7.1 on AIX 4.3.3 in a RS6000 with 2 processors & 1 GB
> Ram.
> This is for a very simple application that for now has only inserts &
> selects, there are no updates or deletes.
> The main table in this application has this "logical" design: (there are
> other small tables)
>
> CREATE TABLE acta (
> actcod CHAR(9) NOT NULL constraint unique,
> acttipo CHAR(1) NOT NULL,
> actfmtimagen CHAR(3) NOT NULL,
> cdnro INTEGER NOT NULL,
> actimagen BLOB(512K) NOT NULL)
> NOT LOGGED INITIALLY
IN tblsp1
INDEX IN tblsp2
LONG IN tblsp3
>
> The application is programmed in Java (JSP, servlets) & run in WebSphere AS.
> It just an online query to show actimagen (which are jpgs & tiffs) in the
> browser, accesing by actcod which is unique.
1) I would create three DMS tablespaces. 2 regular, first for the data and
second for index.
The third would be a LONG tablespace for the images. This way you get fast
index scans
and they are not affected by image retrieval as long as the containers of LONG
tablespace are on
different drives from the regulas tblsps.
> We have to load about 3M rows initially & expect to grow at a rate of about
> 100000 rows/month on the first year.
> I'm new to the environment & have lots of doubts on the physical design of
> this table and the strategy for backup/recover.
> We are going to use db2 load initially but don't know if we can do it after
> that while there are people querying the table. Is that possible using
> "dirty read"?
The load places very restrictive locks on your objects. Super exclusive locks
on both the tblsp. and the table.
Even with appls. running with uncommitted read (UR) isolation level which is
"dirty read" the super
exclusive locks won't let those touch the table.
You should create the table with an added parm. NOT LOGGED INITIALLY.
Then when you run your insert jobs, you do:
A) Ensure that auto commit is off if running from CLP.
b) ALTER TABLE acta NOT LOGGED INITIALLY
Insert...
Insert...
and so on.
c) COMMIT
d) DB2 backup db DBNAME tablespaces (tblsp1,tblsp2,tblsp3) online to......
YThe alter will stop logging all inserts (any table DML as a matter of fact)
until the commit.
The commit will force the inserts to the table in the tablespace.
The backup will give you recoverability.
The alter places an EXCLUSIVE lock on the table so UR isolation appls. should be
able to query the data.
Note that they will be able to "see" the uncommitted inserts that are inflight
from their queries.
Since the backup is online you can also query the table whenever required.
HTH, Pierre.
> Remember there are no updates.
> Is it worth to log the rows? (I think it's not)
> If I do not log, how should I backup & recover?
> Will the table grow too large & should I "split" it on n tables (say
> acta01,..,acta0n) to manage it more easyly?
>
> Can anybody comment on this?
> Any question you had & ideas will be wellcome
> TIA
> Horacio Villa
> TTI S.A.
> Buenos Aires - Argentina
>
> =====
> To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
> For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
begin:vcard
n:Saint-Jacques;Pierre
tel;cell:514-233-8679
tel;fax:514-737-1268
tel;work:514-737-4515
x-mozilla-html:FALSE
org:SES Consultants Inc.
adr:;;233 Simcoe Cr.;Mount-Royal;QC;H3P 1X1;Canada
version:2.1
email;internet:[EMAIL PROTECTED]
title:President
fn:Pierre Saint-Jacques
end:vcard
