Based on the last above links in my edit to my question I found my answer. I cannot tweak the system account since our logons to our system are from an LDAP and I cannot control the groups and what not.
So I did the following:
- Opened the DB2 GUI (was easiest to do this way).
- Connected to the desired database as db2admin.
- Added my logon to the database as a user.
- GRANT all authorities to that id (my id).
- Disconnect.
- Connect to the desired database as my id.
- GRANT all authorities to db2admin.
- Disconnect.
Voila! The db2admin logon now as all authorities.
EDIT: I'm going to leave the above as it helped me learn how to do some interesting stuff in DB2. However, I have learned that the
DBADM/SECADM with DATAACCESS and ACCESSCTRL authorities granted the
instance owner (in my case the db2admin id) have all the authority
needed to interact with the database. I could have actually just
commented out those grant lines above in the script. Those were left
over from a script which ran against an older version of DB2. I have
also found if I need to have the instance owner DBADM after doing a
restore to database A from database B, it is easiest just to set the
registry variable DB2_RESTORE_GRANT_ADMIN_AUTHORITY to YES (available
in Fix Pack 2 and above). Then I don't have to try to grant instance
owner DBADM. It automatically is granted that to any database restored
into the instance. If you are not at Fix Pack 5, you have to bounce
the instance for this to take affect.
As per Information Center:
"If this option is specified when restoring from an online backup
image, error SQL2537N will be returned."
So you you are left with rolling forward. That being said, you could try the following
db2 rollforward db mydb to end of backup and complete
According to the information for rollforward command
END OF BACKUP
Specifies that all partitions in the partitioned database should be rolled forward to the minimum recovery time. See Examples section
below for an example.
This should restore whatever it is you are attempting to restore up to the point of the end of the backup and not include any rolling forward through transactions logs that may be occurring after the backup. Normally you still have to issue at least this statement for a restore from an offline backup where you use archived logs.
I personally haven't tried nor had the need to do an online and only rollforward to the end of the backup. Give it a shot and let us all know what happens.
EDIT: To answer your questions below: You must have archival logging
enabled in order to take online backups. You can still take offline
backups with archival logging. You do not have to switch back to
circular to do so. However, once you have archival logging enabled,
regardless of whether you are restoring from an offline or an online
backup, you must perform a rollforward when you are done. No way
around it. See my own previous question on this.
In order to set up archival logging, you will need to alter the
database configuration parameter LOGARCHMETH1
. By default, this is
set to OFF
, which means circular logging. Your choices for it are as
follows:
DISK
TSM
USEREXIT
VENDOR
LOGRETAIN
LOGRETAIN
tells DB2 to leave the archived logs in the logging
directory. It will not clean up any of these logs for you (leaving you
as the DBA to do so). USEREXIT
leaves it up to a non-IBM third party
tool to take care of storing and archiving your logs (usually hand coded in C). Not too
many people use this anymore from what I understand. DISK
and TSM
seem
to be used a fair amount. DISK
tells DB2 where you want to archive
your logs off to (although with a few other parameters set, DB2 can
automatically clean up logs no longer needed-I'll show you that
below). TSM
tells DB2 to let IBM's Tivoli Storage Manager to archive
off the logs. There are a few other settings you have to get into for
that to work. Where I work, we haven't tried that yet.
And lastly, VENDOR
is used to use a third-party vendor product to archive off your logs.
To set up archival logging of to a different disk spot and have DB2
automatically clean things for you, you can do the following
(NOTE:these are in Unix path)
db2 connect to mydb user <admin user> using <admin user password>
#NOTE THE double and single quotes needed on the command line in Unix
db2 "update db cfg using logarchmeth1 'DISK:/dbarch/dblogs'"
db2 update db cfg using num_db_backups 2
db2 update db cfg using rec_his_retentn 2
db2 update db cfg using auto_del_rec_obj on
db2 terminate
This will tell DB2 to keep 2 full backups for at least two full days.
Two backups have been kept for two days and another one is taken, the
oldest backup will be deleted by DB2. DB2 will also then clean up any
logs that were between the now deleted backup and the next oldest
retained backup.
Also note: once you switch to archival logging, DB2 will require you
to immediately take an offline backup as your next action (ie, the
database will be placed into BACKUP PENDING
mode) and you will be
unable to do anything else until you do so.
Hope this helps!
Best Answer
Yes you should be able to have backups and archived logs go to different places (ie, backups could go to your
VENDOR
tool and archived logs to disk).To send archived logs to disk you can do one of two things:
The first would be to set
LOGARCHMETH1
toDISK
:The second method would be to have two forms of archiving logs. You can leave
LOGARCHMETH1
to yourVENDOR
and set upLOGARCHMETH2
to point toDISK
.I've not played with two forms of log archiving before, but I know that it is available.
In our company we write our backups and archived logs to disk and then sometime after midnight we have IBM's Tivoli Storage Manager (TSM) come along and take a file system snapshot so it will have a copy of the backups and archived logs. So then we have a backup of our backups. We probably could have worked in
LOGARCHMETH2
in our company, but so far this has been simplest for us.Just giving you ideas of what you can do. :)