Dr. Elisabeth Bach IBM

1. H14 Single Table Restore Dr. Elisabeth Bach IBM 4,10, 2006 • 05:30 p.m. – 06:30 p.m.

2. Agenda • What is Table Level Restore (TLR) and why is it interesting? • How to setup TLR • The schema file • Tracking errors

3. Purpose TABLE Level Restore so focus on logical units of data Can restore a table from a backup Can move data from one system to another Is implemented using the archecker Utility

4. Why is it interesting? • Finally a method to recover an accidentally dropped table! • 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

5. How is it implemented? • Uses the tool archecker • Archecker reads through a backup file and tries to verify or find data • New feature adds the logic for recovering partition information to this. • Configuration like archecker • AC_CONFIG File to define archecker • New SCHMEA File to define what table should be restored. • Of course a Level 0 backup is necessary!

6. System Overview Backup (Physical and logical) IDS Instance archecker Database Archecker information files

7. Role of the BACKUP • Needed for the recovery of the information. • Physical Only • Uses only a level 0 archive • Can specify an external table with delimited ASCII or InformixRaw output • Physical & Logical Restore • Can specific any point in time to restore the data to • Using both a level 0 archive and logical logs • During processing temporarily adds two extra integer columns to the destination table along with an index

8. Role of the Server: This is where the work is done Physical Recovery • Construction of the table Logical Recovery • Log Stager and Log Applier • Temp Space is needed This must be a version 10 server

9. Archecker information files AC_CONFIG file • configuration: where to find data Schema command file • schema: what to restore

10. ac_config File • Set the environment variable AC_CONFIG to the relative or full path name of the configuration file • AC_MSGPATH location of the log • AC_STORAGE location of temp files • AC_SCHEMA location of schema file, defines what to restore • AC_IXBAR location of ixbar file, defines what backup to use

11. Common Archecker Command Lines • Start archecker in TLR mode using the schema cmd file • archecker –bdvs –f {schema cmd file} • Start archecker in TLR mode using the command file specified in the configuration file • archecker –bdvsX

12. Schema Command File: What to restore database liesel; create table t1(c1 int, c2 int) in datadbs; create table t1_restore (c1 int) in datadbs; insert into t1_restore select c1 from t1 ; Source table Destination table This defines the relation between source and destination table!

13. What questions must the schema file answer? • In which database is the original table? (database statement) • Which table do we want to restore (source table) • What part of that table is needed (create table statement of destination table) • Where do we want the table to be restored? (destination table) • To what time we want the table to be restored? (time information)

14. Database Statement database liesel [ LOG MODE ANSI ] • This database must exist on the server where we are doing all the work! • Set the current database statement

15. Create Table • The full create table syntax is supported Except the “same as” clause • This statement is used to specify both target and source tables • Source Table A table which exists on the archive backup • Target Table The destination table, where the data should be placed

16. Source Table • For each source table you must specify the storage options. • The dbspace names in the fragmentation clause will search for the tables data • The exact fragmentation schema is not required • Exact column layout required • Column types and locations must be exact • Not able to verify column layout

17. Target Table • Sometimes referred to as a destination table • If the target table exists then data is appended • If the target does not exist then the table is created • Types of target/destination tables allowed • Standard, Raw, Operation, Static • External

18. 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

19. Insert into Select From Statement • Only a subset of the insert/select syntax is supported • One table load per statement • Each statement requires a separate connection to the database • Shared memory connections can not be used because multiple database connections are required • A table may only be extracted once from an archive

20. Restore command • Point in time to which the data is restored • A quoted time • To restore to the most recent time use the keyword CURRENT • To extract data only from a level 0 archive use the “NO LOG RESTORE” • the default is “RESTORE TO CURRENT”

21. 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

22. Simple Schema Command File - Recovery of a Lost Table ONE create statement for source and destination table • database liesel; • create table tlr ( • col1 serial, • col2 integer, • col3 char(20), • col4 decimal • ) in datadbs; • insert into tlr • select * from tlr; Here is the link between both

23. Restoring a Table from a Previous Backup database liesel; create table tlr ( col1 serial, col2 integer, col3 char(20), col4 decimal ) in dbspace1; insert into tlr select * from tlr; • restore to • ‘2006-10-03 16:00:00’; As before, define what to restore Find the right backup and apply the logical logs until the specified date

24. Restoring to a Different Table • database liesel • create table tlr ( • a serial, • b integer • ) in dbspace1; • create table target ( • a serial, • b integer • ) in dbspace2; • insert into target • select * from tlr; • set workspace to dbs1,dbs2; Source and target table are different Here is the link between both This defines the temporary storage space

25. Extracting a Subset of Columns database liesel create table tlr ( col1 serial, col2 integer, col3 char(20), col4 decimal ) in datadbs; create table small_target( c1 integer, c2 char(20) ) in datadbs; insert into small_target select col2,col3 from tlr; restore to ‘2006-10-03 16:00:00’; set workspace to dbs1,dbs2; Source and target table have different definitions. REMEMBER: source table definition MUST MATCH original definition of table The “insert into” statement must include only the columns defined in target table

26. Using Data Filtering database liesel; create table tlr ( a serial, b integer, c char(20), d decimal, ) in datadbs; insert into tlr select * from tlr where c matches ‘te*’ and d is NOT NULL and b < 100; restore to ‘2006-10-03 16:00:00’ no log restore; Only one table definition, but filters would work also with two The “insert into” statement here filters the result set. This defines which restore to use

27. Two Table Example database liesel; create table tlr_1 ( columns ) in datadbs; create table tlr_1_target ( columns ); create table tlr_2 ( columns ) in datadbs; create table tlr_2_target ( columns ); insert into tlr_1_target select * from tlr_1; insert into tlr_2_target select * from tlr_2; The command file can include more than one table Archecker will scan the tape only once for all tables within the same dbspaces. Need to define the relation between the different tables

28. Distributed Restore The target is remote in database rem_dbs database rem_dbs; create table target1 ( ( columns ) database liesel; create table tlr ( columns ) in dbspace1; insert into rem_dbs@rem_srv.target1 select * from tlr; The source is in database liesel . This places the data in to the table target1 on a remote server!

29. Using External Tables database liesel; create table tlr ( col1 serial, col2 integer, col3 char(20), col4 decimal ) in datadbs; create external table target_ext ( c1 int , c2 serial, c3 char(20), c4 decimal ) USING ('/tmp/ext', 'delimited'); insert into target_ext select col2, col1, col3, col4 from tlr; Source table The target table is now a file called /tmp/ext in ASCII format

30. Product Limitations • The table schemas must be exact • As this should recover accidentally dropped tables • Drop table stops recovery • Truncate table stops recovery • Detaching fragments stops recovery • Tables/fragments must exist on the Level 0 archive • No In-Place Alter after the Level 0

31. Debugging TLR • Add AC_DEBUG into ac_config • To limit the debug data, level 10 is often a good compromise • Rerun the command that produced the error • Save the following: • Archecker Message Log • Command file used • archecker configuration file used • If a core exists then save it and the archecker executable • The AC_STORAGE directory

32. AC_DEBUG Examples AC_DEBUG 16 Trace everything at its highest level AC_DEBUG 8,parser Trace just the parser in medium mode AC_DEBUG -10,page,chunk,parser Trace everything in medium mode excluding components page,chunk,parser AC_DEBUG 16,onbar,log Trace only onbar and log components in high mode

33. archecker –bdvsX Errors • CRITICAL ERROR: Invalid configuration. • Message log: • ERROR: Unable to access schema file /export/home/ebach/arc/schema.file error 2 • CRITICAL ERROR: Invalid configuration. • Solution: set AC_SCHEMA!

34. archecker -bdvsX Errors CRITICAL ERROR: Unable to initialize extraction Either wrong user Or an error in the schema file Check he message log for further information:

35. Errors in the schema file Check for details in the ac_msg.log: • ERROR: syntax error at line 7, column 4 • ERROR: Parsing error. • Please check that you have not used an unsupported data type. • Complex types and user defined types are not currently supported • for table-level restore. Solution: Correct the schema file

36. Errors in the schema file • Trying to extract two times from the same table: • ERROR: Duplicate target table found liesel.t2 • ERROR: Duplicate target tables not allowed • CRITICAL ERROR: Unable to initialize extraction • Trying to use a # in the schema file: • ERROR: An illegal character has been found at line 6, column 2 • CRITICAL ERROR: Unable to continue -- no tables to restore.

37. archecker –bdvsX Errors….. • CRITICAL ERROR: Connection to the database 'diesel@on10fc4tcp' has failed (SQLCODE: -329). • Restore disabled for diesel:tlr into diesel:target_ext • CRITICAL ERROR: Unable to continue -- no tables to restore. • wrong database….

38. archecker –bdvsX errors • No tables found on archive • Control page checks PASSED • Table checks PASSED • Table extraction commands 1 • Tables found on archive 0 • Tables not located on archive 1 • LOADED: liesel:target_ext produced 0 rows • Check the dbspace !

39. archecker –bdvsX Errors • CRITICAL ERROR: Connection to the database 'liesel@on10fc4shm' has failed (SQLCODE: -27000).Check msg file: • Message log: ERROR: "CONNECT" failed ERROR: -27000: (rgetlmsg() returned -1227) ERROR: Unable to make a connection for liesel.t1_restore CRITICAL ERROR: Connection to the database 'liesel@on10fc4shm' has failed (SQLCODE: -27000). • SOLUTION: use a TCP connection

40. Session H14 Single Table Restore Dr. Elisabeth Bach IBM elisabeth.bach@de.ibm.com