ARCHECKER Table level Recovery

A little frustrated with this utility - What is your experience? Can you help me? (Sorry for the story...) My experience: 1) if target table exists, simply appends. ;-( documentation implies it has to be a virgin table... and hence implying appending won't append... (?) but it does ;-( 2) Indexes are disabled (backup, truncate table then restore). Okay fair enough, but when I "Set indexes for <table> enabled;" it didn't, even if they should have been valid... ;-( 3) very dependent on dbspace info - I understand why from a physical restore perspective but from a local perspective we teach this as logical... ;-( 4) STDIO doesn't appear to be supported... tough when you are using STDIO 5) Nice that it stops 'recovering' on an alter table... (I hope - not tested) Ok - now to come clean... I was hoping I could use this to restore... a schema. I have a client who is treating schema's very much like a DB2 mainframe schema concept - ie they want a schema to be database And they want / need to restore schema's to a point in time different from other schema's... ( I apologise in advance on the distortion of a clean IDS concept). They point out two or more instances could work for them - but that means management and not nicely sharing resources (only one of these schemas will really be production, the other is for accounts to 'play' before applying to 'production') And yes we are talking very small here 7 GBs per schema in total. Archecker should/could meet some of this need but now appears to be a very big hammer for a small problem. Any experiences?

Some comments are below. John F. Miller III STSM, Support Architect miller3@us.ibm.com IBM Informix Dynamic Server (IDS) ids-bounces@iiug.org wrote on 09/18/2008 07:27:03 AM: > A little frustrated with this utility - What is your experience? Canyou help > me? > (Sorry for the story...) > > My experience: > > 1) if target table exists, simply appends. ;-( documentation impliesit has to > be a virgin table... and hence implying appending won't append... (?) but it > does ;-( Archecker attempt to create the table, if the creation fails because the table already exists then this error is ignored. The existing table is used, then archecker does an SQL insert operation to populate the data into the table. > > 2) Indexes are disabled (backup, truncate table then restore). Okay fair > enough, but when I "Set indexes for <table> enabled;" it didn't, even if they > should have been valid... ;-( > Table Level Restore does NOT restore index, constraints or triggers. You must create these objects yourself. Table level restore will only restore the data in side the table. Due to the fact that archecker is using SQL insert to restore the data, it must temporarily turn on parent child relationships, along with constrains and triggers. The data can come of the archive in any order so archecker might get the child rows long before it gets the parent rows. At the end of the restore it will turn on the constrains and index unless instructed otherwise. It issues the following SQL command to accomplish this: SET INDEXES, CONSTRAINTS, TRIGGERS FOR {tabname} ENABLED > 3) very dependent on dbspace info - I understand why from a physical restore > perspective but from a local perspective we teach this as logical... ;-( The usefulness of the dbspace information depends on a few items. First ontapeor onbar. Since onbar can retrieve objects by dbspace name, providing a list of dbspace names the original table was saved in originally reduce the number of object to retrieve from the storage manager. If you list all dbspaces it will not hurt anything but the performance on the restore. In addition, if you just want to retrieve a single fragment of a table this is great way to accomplish this. One customer who had a large fragmented table started several archecker each restoring it own fragment of the table. (parallel table level restore). Also for the target table definition, if archecker is to create the table many customer want to control where the table will be residing. If you have already create the table then this can be omitted. > > 4) STDIO doesn't appear to be supported... tough when you are using STDIO STDIO is supported, but you must break apart the physical restore and the logical restore into two separate command line actions. The first action feed the level 0 archive to archecker, the second action feed archecker the logical logs. If you attempt to use stdio and not break the two action up you will receive an error. > > 5) Nice that it stops 'recovering' on an alter table... (I hope - not tested) By default it Table Level Restore (TLR) will stop on drop tables and truncate tables. You can ask Table Level Restore to continue through Truncate tables, but this is not the default. The thought behind the design was the DBA was trying to recover after an accident and to not delete/remove good data. The DBA would not asked to recovery a table that in the end just got dropped. > > Ok - now to come clean... I was hoping I could use this to restore... a > schema. > > I have a client who is treating schema's very much like a DB2 > mainframe schema > concept - ie they want a schema to be database And they want / need > to restore > schema's to a point in time different from other schema's... ( I apologise in > advance on the distortion of a clean IDS concept). > > They point out two or more instances could work for them - but that means > management and not nicely sharing resources (only one of these schemas will > really be production, the other is for accounts to 'play' before applying to > 'production') And yes we are talking very small here 7 GBs per schema in > total. > > Archecker should/could meet some of this need but now appears to be > a very big > hammer for a small problem. > > Any experiences? Point in Time Table Level Restore intent was really to a limited set of tables. Now what if you want to do an entire database. It will work... but here are some items to think of: 1. Performance, If you use ontape to restore your system and compare that to Table Level Restore restoring every table in your instance. I hope your expectation are such that ontape will perform much better. 2. Resource. TLR will consume more resource as the number of tables restored increases. The biggest two resource are consumed during logical recovery. If you are limiting this action to a physical only restore the extra resources should be very small and revolve around tables with long row and partition blobs. For the logical recover resources, TLR restore adds temporarily 8 bytes to every row restored. In addition to those above, you need to consider connections. Depending on how your TLR is setup, you can use a separate database connection for each table restore. If you are using separate database connections for each table then an error encounter while restoring on one table will not effect the other tables. If you attempt to share connections then errors will effect all tables involved in the recent set of inserts. During logical recovery we have 1 connection for each simultaneous transaction. 3. Data Types While all of the traditional data types are supported (i.e. all built in data types) Most of the extensible data types are not supported. > > > ******************************************************************************* > Forum Note: Use "Reply" to post a response in the discussion forum. >

Thanks John, I appreciate the response and the knowledge. Robert