Transcript Document

®
IBM Software Group
Informix Table Level Point in Time Restore
for IDS and XPS
John F. Miller III
© IBM Corporation 2004
© 2005 IBM Corporation
IBM Software Group
Agenda
What is Table Level Point in Time Restore (TLPITR)
 Syntax and Semantics
 Content Release Phases
 TLPITR Testing
2
IBM Software Group
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.
3
IBM Software Group
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
4
IBM Software Group
Agenda
 What is Table Level Point in Time Restore (TLPITR)
Syntax and Semantics
 Content Release Phases
 TLPITR Testing
5
IBM Software Group
System Overview
ixbar
file
archecker
AC_CONFIG
file
archecker
schema
command file
Database
AC_MSGPATH file
(AC_CONFIG
parameter)
6
IBM Software Group
Schema Command File
 Database
 Insert into … Select
 Create table
 Set
 Restore
 SQL comments
7
IBM Software Group
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]
8
IBM Software Group
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
9
IBM Software Group
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
10
IBM Software Group
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
11
IBM Software Group
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
12
IBM Software Group
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
13
IBM Software Group
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
14
IBM Software Group
Insert into Select From Statement (cont’ed)
Feature
Supported
UnSupported
Where
Clause
1.
2.
3.
4.
5.
6.
7.
8.
Joins
Subqueries
Functions
Procedures
Math Expr.
Equal, Not Equal ( =, ==, <> )
Less than, Less than equal to
Greater than, Greater than equal to
Match, Not Match, Like, Not Like
IS NULL, NOT NULL
AND
OR
TODAY/CURRENT
Having
Clause
All Parts
Group By
All Parts
Into Temp
All Parts
Order by
All Parts
Union
All Parts
15
IBM Software Group
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
16
IBM Software Group
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
17
IBM Software Group
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
18
IBM Software Group
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
Use ontape interface
b
Use onbar interface
s
Print information to the screen
v
Verbose
f
Schema command file, implies X
X
Table Level unloads
19
IBM Software Group
Example Screen Output
archecker -bvs -f schema_cmd.txt
AC_STORAGE
/tmp
AC_MSGPATH
/tmp/ac.log
AC_VERBOSE
on
AC_TAPEBLOCK
32
AC_PAGESIZE
4096
AC_IXBAR
/vobs/tristarm/sqldist/etc/Bixbar_olympia.85
Extracting table dbs:source_table into dbs1:target_table
Scan PASSED
Control page checks PASSED
Table checks PASSED
Table extraction commands 1
Tables/fragments found on archive 1
LOADED: dbms1:m_frag_new produced 2008 rows.
20
IBM Software Group
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;
21
IBM Software Group
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’;
22
IBM Software Group
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;
23
IBM Software Group
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
24
IBM Software Group
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;
25
IBM Software Group
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;
26
IBM Software Group
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;
27
IBM Software Group
Agenda
 What is Table Level Point in Time Restore (TLPITR)
 Syntax and Semantics
Content Release Phases
 TLPITR Testing
28
IBM Software Group
Data Types Not Support in the First Release
• Tables can not be restored
• This mean these data types can
not be in any of tables being
restored
• If these data types do appear an
error will be given when
processing the command file
• Table can be restored but
the column in the table will
be set to NULL
• Future IDS versions will
support extended data
types, clob, blob
Extended Data Type
LISTS
MULTISET
SET
ROW
DISTINCT
OPAQUE
Built-in Data Type
CLOB
BLOB
TEXT IN BLOBSPACE
BYTE IN BLOBSPACE
29
IBM Software Group
Agenda
 What is Table Level Point in Time Restore (TLPITR)
 Syntax and Semantics
 Content Release Phases
TLPITR Testing
30
IBM Software Group
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
31
IBM Software Group
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
32
IBM Software Group
Parallel Streams - Full Table Restore
dbspaces (dbslice)
XBSA
Legato
Archecker
XPS
Archecker
Archecker
Archecker
1 archecker for each
dbspace
33
IBM Software Group
Serial Stream - “External Enhancement”
XBSA
Legato
Archecker
Named Pipe
XPS
PLOAD
(dbaccess/external tables)
34
IBM Software Group
Serial Stream - “DevNull”
XBSA
Legato
Archecker
/dev/null
35
IBM Software Group
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
36
IBM Software Group
Questions
37