Backup & Restoration of DB2 using TSM API

khurram

ADSM.ORG Member
Joined
Mar 15, 2004
Messages
98
Reaction score
0
Points
0
Website
Visit site
PREDATAR Control23

Hi Guys



Need urgent help from you guys ... I have inherited recently a DB2 integrated TSM setup which was setup by a consultant company ( which unfortunately lost guy who setup this mess ).



Now DB2 Database admin says that he is taking DB2 online backups using TSM API.. but he is not sure where it is going and how to check, verify or restore it.



There is one policy domain exist in TSM called DB2_domain with standard MC as default MC. This MC has Backup settings pointing to a tape pool... but problem is that how i can make sure that DB2 backups are going to the tape pool pointed by this MC??????





What are the DB2 commands which MC or plicy its api is using for backups ?? How can check my DB2 configuration parameters??





Any more ideas . things , to check here for validity of this setup... i will really appreciate all help as i am not a Db2 Guy ???





Many thanks for help!!





Regards

Khurram
 
PREDATAR Control23

Hi ALL



I am still waiting for any response for this query .... Have read whole manuals but not able to find something related.... I will appreciate for help on this subject from any one of you guys!!!



Many Thanks in advance!!!



Regards



Khurram
 
PREDATAR Control23

Ok - an attempt to entertain you...



What version of DB2 are you using, and on what OS?

What version of API are you using?
 
PREDATAR Control23

Hi Khurram,



you can check the MC used by db2 by checking db2 config manager. run the command "db2 get db cfg for ????db |grep -i tsm". I assume unix, also ????db will be the name of your database. Other settings for TSM will be shown when you do a grep -i tsm. Also, check the ".profile" for the db2 instance owner you should have entries in here for DSMI, this is used by the TSM API.



cheers





Wil
 
PREDATAR Control23

The setup for DB2 backups to TSM using v8.1 (FP6 or lower) is different from v8.1 (FP 7 or higher, which makes it essentially v8.2).

This will make a difference in what you see for "db2 get db cfg for SID" as Wil mentions.

It won't, however, affect the way you recover the database using "db2 restore"...

But you do need to know if you are setup for logging, and if so, where the logs are going. The default is no logging. But since you say the previous person was doing online backups, it must be setup for logging.

Having said that, with DB2 v8.1 the logs can use userexit, with DB2 v 8.2 you do not user userexit, so likely they might be shipping off directly to TSM...



Can you run the "db2 get db cfg for SID" command and paste the output? (use your DB name in place of SID)

Also, run "db2level" to see what you are working with.

Still interested in the type of OS and TSM Client/API info...
 
PREDATAR Control23

Also - I should add that you can look on the server side -



So on the TSM server side, of help would be the command:



q nodedata <nodename>



That will tell you where the data has gone - storagepool-wise...



It will look something along the lines of:



tsm: SERVER1>q nodedata DB2_SID



Node Name Volume Name Storage Pool Physical

Name Space

Occupied (MB)

---------------- --------------------------- ---------------- --------

DB2_SID A00111 DB_DATA_TAPE 32,603.96

DB2_SID A00211 DB_LOGS_TAPE 0.01



Hope that gets you going.

If you don't have direct access to the TSM server, ask the Admins to get you that bit of info.
 
PREDATAR Control23

Thanks Guys Very Much for your great help again!!



I have now finally found out by using db2 get cfg command to see TSM nodes names for these DB2 SIDS and then glad to see one DB2LTOPOOL ( associated with MC for these TSM nodes ) is available to take all these DB2 backups .



Now question remains only for DB2 Logs ... The DB2 is infact set for online database backups ... so logs are definetely backed up....



DB2 version is 8.1.3 so as per Cheffern it will be using userexit technique.... Can any body explain a bit , what is the difference between using userexit and not using it.



Also Guys kindly also help me for identifying STG for DB2 Logs also .... I am 90% sure that logs are also going to same STG , but just for assurance i need that info too...



One more thing , there is one copy pool associated with DB2LTOPOOL; can i do normal disaster recovery techniques ( as we usually do in case of normal TSM ) with this pool;

Meaning taking cartridges out of library for this copy pool and sending it to DR side....



Please note that this company have no DRM available so i will be doing it in manual way...



Kindly advice....



I remain thankfull to all u guys for this great help again



Regards

Khurram.
 
PREDATAR Control23

Hi Khurram -



Glad things are looking up for you.



1) Userexit is like this funky program thing that you compile to manage the DB2 logs. (When a user 'exits' or saves and a log file is turned, userexit tells it where to go.) V8.2 can use this (backwards compatible) but only to a certain degree (which I have not had much luck with). Userexit in this case is the compiled file under $db2home/sqllib/adm called "db2uext2"



The v8.2 part that is different is that it uses what is called the 'DB2 Log Manager'. If you are backing up a database that is v8.1, you need to set userexit and logretain to ON for it to work. V8.2 you don't. Instead you can do things like direct logs to TSM via the DB2 Log Manager internals.



For us, with userexit on v8.1, we use automatic archiving of logs to TSM (to tape) via the userexit program (where userexit and logretain are both set to "YES" in 'db2 get db cfg for SID').



2) If 'q nodedata <nodename>' only shows you one thing - that is probably ok. I have never seen it where the data was not separated from the logs (that is how we run it) - but assuming that when you back something up using nodenameA, all the contents for nodenameA will be shown when you query the nodedata for that node (nodenameA). Does that make more sense? You probably would not ever want to back up the db as 1 node and then the logs as another node...



Look around for the "inclexcl" portion of the db2node. It can be either IN the dsm.sys file, or another file called from there. You can specifically bind the datafiles and logfiles to MGMT_CLASSES in there.



~



A third set of things you can try - given that you are on db2 v8.1:



db2 list history

db2adutl query



The db2 list history command will help you identify which backup image to use if tablespaces are lost or corruption is an issue. The command lists entries in the history file, which contain recovery events, such as backups, restores and roll-forwards.

There is a location associated with each backup set at the bottom so you figure out where it went. (like adsm/libtsm.a...etc...online or offline...)



db2adutl query will show you more detail - pretty cool command, really.



~



The ultimate best thing for you to do is to get ahold of a test DB to practice this on so you can see what you need and don't need to do full recoveries and partial (just logs, etc) if you are going to be responsible for this. That way you can test a log recover and see where everything is coming from. (You don't want to have to test this in a production scenario if this is your first time - if you can at all avoid it)



Hope this long-winded note clarifies some things up for you. ;)
 
PREDATAR Control23

Hi Cheffern



Thanks for ur great help!!



I am attaching output from db2 get cfg command

-----------------------------------------------------------------------

Group commit count (MINCOMMIT) = 1

Percent log file reclaimed before soft chckpt (SOFTMAX) = 100

Log retain for recovery enabled (LOGRETAIN) = RECOVERY

User exit for logging enabled (USEREXIT) = OFF



Auto restart enabled (AUTORESTART) = ON

Index re-creation time (INDEXREC) = SYSTEM (RESTART)

Default number of loadrec sessions (DFT_LOADREC_SES) = 1

Number of database backups to retain (NUM_DB_BACKUPS) = 12

Recovery history retention (days) (REC_HIS_RETENTN) = 366



TSM management class (TSM_MGMTCLASS) = standard

TSM node name (TSM_NODENAME) = bksa_db2

------------------------------------------------------------------------------



& output of q nodedata from TSM for bksa_db2 node is



----------------------------------------------------------------



q nodedata bksa_db2



Node Name Volume Name Storage Pool Physical

Name Space

Occupied

(MB)

---------------- ------------------------------ ---------------- --------

bksa_db2 159ABZ DB2LTOPOOL 141,680.

64

bksa_db2 158ABZ COPYPOOL 10,585.3

3

bksa_db2 157ABZ COPYPOOL 131,095.

31

--------------------------------------------------------------------------------





So it is showing that userexit is off here and we are maintaining database backups of last 12 days only ..... but whether we are backing up and retaining DB2 logs in enough for recovery??? . this is the question bothering me...



I have checked dsm.sys and there is no INCL-EXCL file so all database & log files are associated with sam MC and hence same storage pool



I will thankfull to you if you can have a look on these DB parameters and advice me about some improvements ( if required ) for saving DB2 log files too..



many thanks again



Regards



Khurram.
 
PREDATAR Control23

Hi Khurram -



First off let me point out your #'s:



-----------------------------------------------------------------------

q nodedata bksa_db2



Node Name Volume Name Storage Pool Physical

Name Space

Occupied

(MB)

---------------- ------------------------------ ---------------- --------

bksa_db2 159ABZ DB2LTOPOOL 141,680.

64

bksa_db2 158ABZ COPYPOOL 10,585.3

3

bksa_db2 157ABZ COPYPOOL 131,095.

31

--------------------------------------------------------------------------------



Both Copypools add up exactly to the DB2LTOPOOL amount shown above - which probably means in your copy pool there you have 131 GB of DB Data and 10.5 GB of DB2 logs.

Make sense to you???



~

Keeping 12 database backups will keep 12 versions. ie, 1 backup + logs = 1 version.

How large is your SLA, and how many times do you back up your DB full - once a day?

Really?

~



I have to say - having the userexit off is perplexing me!

According to things I have read, it should really be set up for userexit.



Please read this excerpt taken directly from IBM's website:



"logretain - Log retain enable configuration parameter



Logretain [No;Recovery]



The values are as follows:



No, to indicate that logs are not retained.

Recovery, to indicate that the logs are retained, and can be used for forward recovery.

If logretain is set to Recovery or userexit is set to Yes, the active log files will be retained and become online archive log files for use in roll-forward recovery. This is called log retention logging.



After logretain is set to Recovery or userexit is set to Yes (or both), you must make a full backup of the database. This state is indicated by the backup_pending flag parameter.



If logretain is set to No and userexit is set to No, roll-forward recovery is not available for the database because logs are not retained. In this situation, the database manager deletes all log files in the logpath directory (including online archive log files), allocates new active log files, and reverts to circular logging. "



Read it from here:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000247.htm



~

According to that, your LOGRETAIN value is correct - it means specifically "If set, this parameter indicates that log files are being retained for use in roll-forward recovery. "

~



I am not sure about the USEREXIT parameter being NO though -

Please read this excerpt taken directly from IBM's website:



"userexit - User exit enable configuration parameter



Userexit [No;Yes]



If this parameter is enabled, log retention logging is performed regardless of how the logretain parameter is set. This parameter also indicates that a user exit program should be used to archive and retrieve the log files. Log files are archived when the database manager closes the log file. They are retrieved when the ROLLFORWARD utility needs to use them to restore a database.



After logretain, or userexit, or both of these parameters are enabled, you must make a full backup of the database. This state is indicated by the backup_pending flag parameter.



If both of these parameters are de-selected, roll-forward recovery becomes unavailable for the database because logs will no longer be retained. In this case, the database manager deletes all log files in the logpath directory (including online archive log files), allocates new active log files, and reverts to circular logging. "



Read it from here:

http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/r0000249.htm



~



So I guess basically what this means - even though they never actually say it - is that you are log enabled for recovery, but you are not using the userexit. (I *think*)



Take a close look at all this and see if you can glean the meaning here.

Any chance you have any documentation of why it was setup this way?



Also - does anyone else out there have this setup - and have it working???

(LOGRETAIN=RECOVERY, USEREXIT=NO)
 
Top