Transcript Table Level Restore - Washington Area Informix User Group

IBM GLOBAL SERVICES
®
The Ins and Outs of Table Level Restore
John F. Miller III
Informix Forum
© IBM Corporation 2005
Agenda
What is Table Level Point in Time
Restore (TLPITR)
• Syntax and Semantics
• Content Release Phases
• TLPITR Testing
TLPITR
TLPITR
• Provide the customer with the ability to
easily extract a set of tables, a table or a
portion of a table from a level 0 archive to a
user specified point in time.
• The extracted data can be placed in an
external table or on a table on the server of
the user’s choice regardless of server version
or machine type as long as the database server
is listed in the sqlhost file.
Benefits
•
•
•
•
•
Extract a table or set of tables
Filter the retrieved data
Retrieve just a subset of columns
Repartition the data
Data may be placed in the same version
database or on a different database
version with a different machine
architecture
Agenda
• What is Table Level Point in Time
Restore (TLPITR)
Syntax and Semantics
• Content Release Phases
• TLPITR Testing
System Diagram
Table Level Point In Time
Ixbar
File
Archecker
Config File
Schema
Command File
Archecker
Command Line
Schema Command File
•
•
•
•
•
Database
Insert into … Select
Create table
Set
Restore
• SQL comments
Database Statement
• Set the current database statement
• All table names referenced following the
database statement are associated with the
current database unless fully qualified
• DATABASE <dbname> [LOG MODE ANSI]
Create Table Statement
• The full create table syntax is
supported
• This statement is used to specify
both target and source tables
• Source Table
– A table that is on the archive backup
• Target Table
– The destination table, where the data
should be placed
Source Table
• A table that is on the archive backup
• For each source table you must
specify the storage options.
– The dbspace names in the fragmentation clause used
to find the table’s data
– The exact fragmentation schema is not required
• Exact column layout is required
– Column types and locations must be exact
– Not able to be completely verify column layout
Target Table
• If the target table
exists then data is
appended
• If the target does
not exist then the
table is created
– All attributes
specified in the
schema file will be
utilized
External Table
• Used to specify a pipe or OS file as the
location of the physical portion of the
restore
• CREATE EXTERNAL TABLE ….
USING (‘<filename>’, ‘<format>’);
• Format
– INFORMIX or (ASCII) DELIMITED
• Logical restore does not happen
Insert into Select From Statement
• Only a subset of the insert/select syntax is
supported
• One table retrieved per statement
• Each statement requires a separate connection
to the database
• A source table may only be extracted once per
restore
– no multi-siting
– A table name may only exist once as a source table
Insert into Select From Statement
Feature
Supported
Unsupported
Insert table 1. fully qualified table name
1. views
2. synonyms
Insert
columns
1. column names
1. column aliases
2. constants
Select
columns
1. column name
2. projection list
3. all columns ‘*’
1. subscripting
2. column aliases
3. constants
4. aggregate
5. expressions
6. stored procedures
From
Clause
1. single table
1. views
2. multiple tables
3. synonyms
Insert into Select From (cont’ed)
Feature
Supported
UnSupported
Where
Clause
1. Equal, Not Equal
( =, ==, <> )
2. Less than, Less than equal to
3. Greater than, Greater than equal
to
4. Match, Not Match, Like, Not Like
5. IS NULL, NOT NULL
6. AND
7. OR
8. TODAY/CURRENT
Joins
Subqueries
Functions
Procedures
Math Expr.
Having
Clause
All Parts
Group By
All Parts
Into Temp
All Parts
Order by
All Parts
Union
All Parts
Set Statement
• COMMIT TO
– Sets the number of records to
insert before committing
– SET COMMIT TO <number>
– Default to 1000
• WORKSPACE TO
<dbspace,….>
– Directs temporary tables and
indexes to the dbspaces listed
Restore Command
• Defines point in time to restore data to
• Defines if logical recovery should
occur
• Default is RESTORE TO CURRENT
• RESTORE
[ TO <timestamp> | CURRENT]
[ WITH NO LOG ]
• Drops and Truncate Tables stops
recovery
Archecker Config File
• Set the existing environment variable
AC_CONFIG to the relative or full path name
of the configuration file
• If ONCONFIG is set correctly, those values
will be used as defaults.
AC_MSGPATH
AC_STORAGE
AC_VERBOSE
/tmp/ac.log
/tmp
1
AC_IXBAR
/tmp/ixbar
AC_TAPEBLOCK 32
AC_SCHEMA
schcmd.txt
# Archecker message log
# Directory used for temp storage
# 1 verbose 0 terse messages
# Path to ixbar file
# Blocksize in KB
# Schema command file
Archecker Command Line
• Start archecker in TLPITR mode w/ schema file
archecker –bsv –f {schema cmd file}
• Start archecker in TLR mode
archecker –bvsX
t
b
s
v
Use ontape interface
Use onbar interface
Print information to the screen
Verbose
f
X
Schema command file, implies X
Table Level unloads
Example Screen Output
Program Name: archecker
CSDK:
IBM Informix CSDK Version 2.90
Compiled: 11/05/04 13:15 on SunOS 5.8 Generic_117350-08
AC_STORAGE
/tmp
AC_MSGPATH
/tmp/ac_msg.log
AC_VERBOSE
on
Extracting table d1:cust into d1:cust_new
Table checks PASSED
Table extraction commands 1
Tables found on archive 1
LOADED: d1:cust_new produced 56 rows.
Creating log control tables
Logically recovered d1:cust_new Inserted 112 Deleted 68 Updated 33
Recovery of Lost Table
• Extracts a table
called test1:tlr
from the most
recent backup of
dbspace1 and
places the data in
table test1:tlr
database test1;
create table tlr (
a_serial
serial,
b_integer integer,
c_char
char(20),
d_decimal decimal,
) in dbspace1;
insert into tlr
select * from tlr;
Restoring from a Previous
Backup
• Extracts a table
called test1:tlr from
the level 0 backup of
dbspace1 just prior
“2003-01-01
01:01:01” and places
the data in table
test1:tlr
database test1;
create table tlr (
a_serial
serial,
b_integer integer,
c_char
char(20),
d_decimal decimal,
) in dbspace1;
insert into tlr
select * from tlr;
restore to
‘2003-01-01 01:01:01’;
Extracting a Subset of Columns
• Extracts a table
called
test1:source_tab
from the most recent
backup of dbspace1
and places the data
in column c_char
into column X_char,
d_decimal into
Y_decimal in table
test1:target_tab
database test1;
create table source_tab (
a_serial
serial,
c_char
char(20),
d_decimal decimal
) in dbspace1;
create table target_tab (
X_char
char(20),
Y_decimal decimal,
Z_name
char(40)
) in dbspace2;
insert into target_tab
( X_char, Y_decimal )
select c_char,d_decimal
from source_tab;
Using Data Filtering
Extracts the physical
records only from a
table called test1:tlr
from the most recent
backup of dbspace1
and places the data in
table test1:tlr only
where the where
conditions are true.
database test1;
create table tlr (
a_serial
serial,
b_integer integer,
c_char
char(20),
d_decimal decimal,
) in dbspace1;
insert into tlr
select * from tlr
where c_char matches ‘j*’
and d_decimal is NOT NULL
and b_integer > 100;
RESTORE WITH NO LOG
Using External Tables
Extracts a table called
dl:source_tab from the
most recent backup of
dbspace1 and sends the
data in ASCII format
with fields delimited to
the file named
/TMP/PIPE.
database d1;
create table source_tab
(cola int)
in dbspace1;
create external table
target_tab
(cola int)
USING (‘/TMP/PIPE’,
‘DELIMITED’);
insert into target_tab
select * from source_tab;
Two Table Example
Extracts a table called
test1:tlr_1 and
test:tlr_2 from the most
recent backup of
dbspace1 and places
the data in table
test1:tlr_1_dest and
test1:tlr_2_dest using
only one scan of the
tape.
database test1;
create table
( columns
create table
( columns
create table
( columns
create table
( columns
tlr_1
) in dbspace1;
tlr_1_dest
);
tlr_2
) in dbspace1;
tlr_2_dest
);
insert into tlr_1_dest
select * from tlr_1;
insert into tlr_2_dest
select * from tlr_2;
Distributed Restore
Extracts a table called
test:source_tab from
the most recent
backup of dbspace1
and places the data on
the database server
rem_srv in the table
target_dbs:tlr_1
database target_dbs;
create table target_tab
( columns );
database test;
create table source_tab
( columns ) in dbspace1;
insert into
target_dbs@rem_srv.target_tab
select * from source_tab;
Agenda
• What is Table Level Point in Time Restore
(TLPITR)
• Syntax and Semantics
Content Release Phases
• TLPITR Testing
Who Can Use What When
• XPS
– 8.50 (Sept 2004) =
Currently Released
• IDS
– 10.00.UC1 =
Currently Released
• All built-in data types, boolean, and LVARCHAR are supported
• Partition blobs (text, byte) supported
• Blobspace blobs not supported
• Clob and Blob are not supported
– 10.00.UC3
Currently Released
• Clob and Blob supported for for level 0, no logical recovery
– <future>
• Clob and Blob supported for logical restore
• Extended data type support
Agenda
• What is Table Level Point in Time Restore
(TLPITR)
• Syntax and Semantics
• Content Release Phases
TLPITR Testing
Testing Environment
Test Table
17 Gig
45M Rows
Test instance
XPS
8.40FC2X2
6 CPU’s
3 Gig RAM
60 Gig Disk
coserver
1
coserver
2
Onbar
worker
threads
1
2
Network
1
2
Backup
Server
“Legato”
9480
Test Cases
Full
Filter
Serial
Index
1
2
3
..
.
• Serial Stream - Full Table Restore
• Serial Stream - Partial Table Restore (Filter)
• Serial Stream - Table with a serial column
• Serial Stream - Table with index
• Parallel Streams - Full Table Restore
Serial Stream - Full Table Restore
dbspaces (dbslice)
XBSA
Legato
Archecker
XPS
Variations on the Theme
• Partial Table ( where clause )
• Table with Index
• Table with Serial Column
Parallel Streams - Full Table Restore
dbspaces (dbslice)
XBSA
Legato
Archecker
Archecker
Archecker
Archecker
1 archecker for each
dbspace
XPS
Performance
1.4
1.2
1.22
1
0.8
0.99
MB/sec
0.6
0.4
0.2
0
Serial
Parallel
Serial Stream - “External
Enhancement”
XBSA
Legato
Archecker
Named Pipe
XPS
PLOAD
(dbaccess/external tables)
Serial Stream - “DevNull”
XBSA
Legato
Archecker
/dev/null
Performance
4.5
4
4.08
3.5
3
MB/sec
2.5
2.76
2
1.5
1
0.5
0.99
1.22
0
Serial
Parallel
External
DevNull
Questions