Alo Pierre,
thanks for your ideas.
I went to the SQL Reference and found this:
An error in any operation in the unit of work in which the NOT LOGGED
INITIALLY attribute is active will result in the entire unit of work being
rolled back (SQLSTATE 40506).
Furthermore, the table for which the NOT LOGGED INITIALLY attribute was
activated is marked inaccessible after the rollback has occurred and can
only be dropped.
So, if I don't missunderstand it, if I had an error I would lost the table.
Is that so?
Other one:
Any hints on page size?
Would COMPACT help defining the BLOB? I put BLOB(512k) but there are many
different sizes, starting from 32K.
Would you define only one table for all the images? The backup file would be
huge.
Thanks again
Horacio
-----Mensaje original-----
De: [EMAIL PROTECTED] En nombre de Pierre Saint-Jacques
Enviado el: Mi�rcoles 25 de Julio de 2001 01:47
Para: [EMAIL PROTECTED]
Asunto: Re: DB2EUG: Question on physical design & recovery
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
=====
To unsubscribe, send 'unsubscribe' to [EMAIL PROTECTED]
For other info (and scripts), see http://people.mn.mediaone.net/scottrmcleod
