rollforwardbackup.out

derby database source code.good for you.

ij> CREATE PROCEDURE RENAME_FILE(LOCATION VARCHAR(32000), NAME VARCHAR(32000), NEW_NAME  VARCHAR(32000)) DYNAMIC RESULT SETS 0 LANGUAGE JAVA EXTERNAL NAME 'org.apache.derbyTesting.functionTests.util.FTFileUtil.renameFile' PARAMETER STYLE JAVA;
0 rows inserted/updated/deleted
ij> values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.logArchiveMode');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
NULL                                                                                                                            
ij> --check whether log archive mode  enabling method is working
call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
    'extinout/mybackup', 0);
0 rows inserted/updated/deleted
ij> values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.logArchiveMode');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
true                                                                                                                            
ij> --check whether the logArchive Mode is persistent across boots
disconnect;
ij> connect 'wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> connect 'wombat';
ij> values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.logArchiveMode');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
true                                                                                                                            
ij> --check whether log archive mode  disabling method is working
call SYSCS_UTIL.SYSCS_DISABLE_LOG_ARCHIVE_MODE(1);
0 rows inserted/updated/deleted
ij> values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.logArchiveMode');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
false                                                                                                                           
ij> --check whether the logArchive Mode disabling persistent across boots
disconnect;
ij> connect 'wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> connect 'wombat';
ij> values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.logArchiveMode');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
false                                                                                                                           
ij> -- reenable the log archive mode again to see whether the 
-- disabling has any side effects.
call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
    'extinout/mybackup', 0);
0 rows inserted/updated/deleted
ij> values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.logArchiveMode');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
true                                                                                                                            
ij> disconnect;
ij> connect 'wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> connect 'wombat';
ij> values SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('derby.storage.logArchiveMode');
1                                                                                                                               
--------------------------------------------------------------------------------------------------------------------------------
true                                                                                                                            
ij> --END OF PROPERTY ARCHIVE CHECKS
---PERFORM DIFFERENT TYPES OF RESTORE
create table t1(a int ) ;
0 rows inserted/updated/deleted
ij> insert into t1 values(1) ;
1 row inserted/updated/deleted
ij> insert into t1 values(2) ;
1 row inserted/updated/deleted
ij> insert into t1 values(3 ) ;
1 row inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
    'extinout/mybackup', 0);
0 rows inserted/updated/deleted
ij> insert into t1 values(4) ;
1 row inserted/updated/deleted
ij> insert into t1 values(5);
1 row inserted/updated/deleted
ij> insert into t1 values(6);
1 row inserted/updated/deleted
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> disconnect;
ij> --performa rollforward recovery
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';
ij> select * from t1 ;
A          
-----------
1          
2          
3          
4          
5          
6          
ij> insert into t1 values(7);
1 row inserted/updated/deleted
ij> insert into t1 values(8);
1 row inserted/updated/deleted
ij> insert into t1 values(9);
1 row inserted/updated/deleted
ij> --take a backup again
call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
    'extinout/mybackup', 1);
0 rows inserted/updated/deleted
ij> insert into t1 values(10);
1 row inserted/updated/deleted
ij> insert into t1 values(11);
1 row inserted/updated/deleted
ij> insert into t1 values(12);
1 row inserted/updated/deleted
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> disconnect;
ij> --perform complete version restore
connect 'jdbc:derby:wombat;restoreFrom=extinout/mybackup/wombat';
ij> select * from t1 ;
A          
-----------
1          
2          
3          
4          
5          
6          
7          
8          
9          
ij> insert into t1 values(10);
1 row inserted/updated/deleted
ij> insert into t1 values(11);
1 row inserted/updated/deleted
ij> insert into t1 values(12);
1 row inserted/updated/deleted
ij> insert into t1 values(13);
1 row inserted/updated/deleted
ij> insert into t1 values(14);
1 row inserted/updated/deleted
ij> insert into t1 values(15);
1 row inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
    'extinout/mybackup', 1);
0 rows inserted/updated/deleted
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> disconnect;
ij> --create a new database using wombat db backup copy with a different database name
connect 'jdbc:derby:wombat1;createFrom=extinout/mybackup/wombat';
ij> select * from t1;
A          
-----------
1          
2          
3          
4          
5          
6          
7          
8          
9          
10         
11         
12         
13         
14         
15         
ij> insert into t1 values(16);
1 row inserted/updated/deleted
ij> insert into t1 values(17);
1 row inserted/updated/deleted
ij> insert into t1 values(18);
1 row inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
    'extinout/mybackup', 1);
0 rows inserted/updated/deleted
ij> connect 'jdbc:derby:wombat1;shutdown=true';
ERROR 08006: Database 'wombat1' shutdown.
ij> disconnect;
ij> ---BACKUP AND RESTORE USING LOGDEVICE.
connect 'jdbc:derby:crwombat;createFrom=extinout/mybackup/wombat;logDevice=extinout/crwombatlog';
ij> values SUBSTR(SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('logDevice'), LOCATE('crwombatlog',SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('logDevice')),11);
1          
-----------
crwombatlog
ij> select * from t1;
A          
-----------
1          
2          
3          
4          
5          
6          
7          
8          
9          
10         
11         
12         
13         
14         
15         
ij> insert into t1 values(19);
1 row inserted/updated/deleted
ij> insert into t1 values(20);
1 row inserted/updated/deleted
ij> insert into t1 values(21);
1 row inserted/updated/deleted
ij> connect 'jdbc:derby:crwombat;shutdown=true';
ERROR 08006: Database 'crwombat' shutdown.
ij> disconnect;
ij> --do a plain boot , we should have the log device specified earlier.
connect 'jdbc:derby:crwombat';
ij> values SUBSTR(SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('logDevice'), LOCATE('crwombatlog',SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('logDevice')),11);
1          
-----------
crwombatlog
ij> select * from t1;
A          
-----------
1          
2          
3          
4          
5          
6          
7          
8          
9          
10         
11         
12         
13         
14         
15         
19         
20         
21         
ij> insert into t1 values(22);
1 row inserted/updated/deleted
ij> insert into t1 values(23);
1 row inserted/updated/deleted
ij> insert into t1 values(24);
1 row inserted/updated/deleted
ij> connect 'jdbc:derby:crwombat;shutdown=true';
ERROR 08006: Database 'crwombat' shutdown.
ij> disconnect;
ij> ---check the error case of log device only existing when
-- we try to do createFrom .
--following connection shoul fail.
connect 'jdbc:derby:erwombat;createFrom=extinout/mybackup/wombat;logDevice=extinout/crwombatlog';
ERROR XJ040: Failed to start database 'erwombat', see the next exception for details.
ERROR XSLAT: The log directory 'extinout<ps>crwombatlog<ps>log' exists. The directory might belong to another database. Check that the location specified for the logDevice attribute is correct.
ij> connect 'jdbc:derby:wombat;restoreFrom=extinout/mybackup/wombat;logDevice=extinout/wombatlog';
ij> values SUBSTR(SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('logDevice'), LOCATE('wombatlog',SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('logDevice')),9);
1        
---------
wombatlog
ij> select * from t1 ;
A          
-----------
1          
2          
3          
4          
5          
6          
7          
8          
9          
10         
11         
12         
13         
14         
15         
ij> insert into t1 values(19);
1 row inserted/updated/deleted
ij> insert into t1 values(20);
1 row inserted/updated/deleted
ij> insert into t1 values(21);
1 row inserted/updated/deleted
ij> call SYSCS_UTIL.SYSCS_BACKUP_DATABASE_AND_ENABLE_LOG_ARCHIVE_MODE(
    'extinout/mybackup', 1);
0 rows inserted/updated/deleted
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> disconnect;
ij> --restore again from backup case to make sure
--backups are getting the log device property.	
connect 'jdbc:derby:wombat;restoreFrom=extinout/mybackup/wombat';
ij> values SUBSTR(SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('logDevice'), LOCATE('wombatlog',SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('logDevice')),9);
1        
---------
wombatlog
ij> select * from t1;
A          
-----------
1          
2          
3          
4          
5          
6          
7          
8          
9          
10         
11         
12         
13         
14         
15         
19         
20         
21         
ij> insert into t1 values(22);
1 row inserted/updated/deleted
ij> insert into t1 values(23);
1 row inserted/updated/deleted
ij> insert into t1 values(24);
1 row inserted/updated/deleted
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> disconnect;
ij> --do a vannila boot and see the device to make sure the log device is still intact.
connect 'jdbc:derby:wombat';
ij> values SUBSTR(SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('logDevice'), LOCATE('wombatlog',SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('logDevice')),9);
1        
---------
wombatlog
ij> select * from t1;
A          
-----------
1          
2          
3          
4          
5          
6          
7          
8          
9          
10         
11         
12         
13         
14         
15         
19         
20         
21         
22         
23         
24         
ij> autocommit off;
ij> insert into t1 values(25);
1 row inserted/updated/deleted
ij> insert into t1 values(26);
1 row inserted/updated/deleted
ij> insert into t1 values(27);
1 row inserted/updated/deleted
ij> rollback;
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> disconnect;
ij> --performa rollforward recovery with logDevice specified at backup
connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat';
ij> values SUBSTR(SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('logDevice'), LOCATE('wombatlog',SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('logDevice')),9);
1        
---------
wombatlog
ij> select * from t1 ;
A          
-----------
1          
2          
3          
4          
5          
6          
7          
8          
9          
10         
11         
12         
13         
14         
15         
19         
20         
21         
22         
23         
24         
ij> insert into t1 values(25);
1 row inserted/updated/deleted
ij> insert into t1 values(26);
1 row inserted/updated/deleted
ij> insert into t1 values(27);
1 row inserted/updated/deleted
ij> connect 'jdbc:derby:wombat;shutdown=true';
ERROR 08006: Database 'wombat' shutdown.
ij> disconnect;
ij> --perform a rollforward recovery with log device is moved
--to some other place than what it was when backup was taken.
--move the log to different dir name.
connect 'jdbc:derby:dummycondb;createFrom=extinout/mybackup/wombat;logDevice=extinout/wombatlog1';
ij> call RENAME_FILE(null,'extinout/wombatlog','extinout/wombatlogmoved');
0 rows inserted/updated/deleted
ij> disconnect;
ij> connect 'jdbc:derby:wombat;rollForwardRecoveryFrom=extinout/mybackup/wombat;logDevice=extinout/wombatlogmoved';
ij> values SUBSTR(SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('logDevice'), LOCATE('wombatlogmoved',SYSCS_UTIL.SYSCS_GET_DATABASE_PROPERTY('logDevice')),14);
1             
--------------
wombatlogmoved
ij> select * from t1 ;
A          
-----------
1          
2          
3          
4          
5          
6          
7          
8          
9          
10         
11         
12         
13         
14         
15         
19         
20         
21         
22         
23