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
