How to set up Log shipping in DB2
What is log shipping?
It is a method where transaction logs are automatically backed up
from primary to standby.
How to set up Log shipping in DB2?
Step1: Create a folder with name "mylogs"
Step2: Here we are using userexit method to do log shipping so we need a program called db2uext2.cdisk it will be located in "C:\IBM\SQLLIB\samples\c"
Step3: copy it into a working directory and rename it as db2uext2.c
Step4: After this create two instances(DB2 & DB2INST1) and create tstls database in each instance separately. In this video DB2 is primary instance and DB2INST1 is secondary database instance.
Step5: Now in DB2 instance enable the USEREXIT parameter as below
"db2 update db cfg for tstls using USEREXIT ON"
Step6: Lets check what are the parameters got effected after this.
"db2 get db cfg for tstls | findstr -i user"
output:
User exit for logging status = YES
User exit for logging enabled (USEREXIT) = ON
First log archive method (LOGARCHMETH1) = USEREXIT
Step7: Here logarchmeth1 parameter also enabled so we must take the backup of that database otherwise we can not connect to the database so now we have to take backup of tstls database
"db2 backup db tstls to C:\Users\GANESH\Desktop\primary compress"
Step8: Now we have to change some parameters in db2uext2.c file
#define ARCHIVE_PATH "C:\\Users\\Administrator\\Desktop\\mylogs\\"
#define RETRIEVE_PATH "C:\\Users\\Administrator\\Desktop\\mylogs\\"
#define AUDIT_ACTIVE 1 /* enable audit trail logging */
#define ERROR_ACTIVE 0 /* enable error trail logging */
#define AUDIT_ERROR_PATH "C:\\Users\\Administrator\\Desktop\\mylogs\\" /* path must end with a slash */
#define AUDIT_ERROR_ATTR "a" /* append to text file */
#define BUFFER_SIZE 32 /* # of 4K pages for output buffer */
Step9: Now we need to compile this c program so we need c compiler in our system I have ms visual studio so I'll use developer command prompt to compile my c program.
"cl db2uext2.c" now you will get an .exe file
step10: Now we have to place the executable file in the below folder. "C:\IBM\SQLLIB\BIN"
Step11: Now we have to stop and start the DB manager for instance DB2
Now the log shipping process is done. If you create a table in tstls which is present in DB2 instance those logs will be archived from "C:\DB2\NODE0000\SQL00003\SQLOGDIR" to "C:\Users\GANESH\Administrator\mylogs"
In this video I used a procedure to insert multiple rows at a time into a table to generate more logs you can create it sample table and insert data to generate one log file
Thanks for watching the video.
Please follow us on Google+: https://plus.google.com/b/11093236285...
Please subscribe for more: