IDS Forum

Re: ARCHECKER Table level Recovery

Posted By: John Miller iii
Date: Sunday, 21 September 2008, at 8:26 p.m.

In Response To: ARCHECKER Table level Recovery (ROBERT CLOW)

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
ontape
or 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.
>

Messages In This Thread

• ARCHECKER Table level Recovery
  ROBERT CLOW -- Thursday, 18 September 2008, at 10:27 a.m.
  • Re: ARCHECKER Table level Recovery
    John Miller iii -- Sunday, 21 September 2008, at 8:26 p.m.
    • Re: ARCHECKER Table level Recovery
      ROBERT CLOW -- Monday, 22 September 2008, at 1:26 a.m.

IDS Forum is maintained by Administrator with WebBBS 5.12.