> So, if I don't missunderstand it, if I had an error I would lost the
table.
> Is that so?
>
Villa, you're right in that the table would be lost however:
1) ALTER tablespace Dropped Table Recovery ON
2) CREATE Table ........Not Logged Initially.
3) LOAD data in your table. Backup tablespace.
There is data in the table.
When it's time to run your 100k row inserts:
4)Alter tabname activate not logged initially
insert into.... select from....
commit
If you have a failure, then after the roll back.
1) DROP the table.
2) RESTORE tblsp
3) ROLLFORWARD ...... RECOVER DROPPED TABLE....... TO THE END OF LOGS
AND STOP
4) BACKUP TABLESPACE.
It is a bit "rough" but if you really don't want to log the inserts then
it's about the only way to protect yourself.
It also allows you to have access in UR isolation to the table while
inserting and not paying the logging cost of the inserts.
HTH, Pierre.
I need to check on something first. Let me get back to you.
You're right you would loose the table but there may ba a way to protect it and
recover it.
Regards, Pierre.
Villa Horacio wrote:
> 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
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
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
