DB2 Restore on new install not working

DB2 10.5 on Windows

So I am trying to teach myself how to install DB2 and restore a dev database.  So I install DB2 10.5 on windows without issue.  I then run the sample database setup and I can connect to it no problem.

When I copy over the DEV database backup and try to restore it, problem.  I get this.

C:\Windows\system32>db2 restore database DEV FROM X:\dbbackups
SQL1092N  The requested command or operation failed because the user ID does
not have the authority to perform the requested command or operation.  User
ID: "xxx".  SQLSTATE=00000

Now XXX is in the Admin group on the windows server and it is running the DB2 instance service.  It should have full control of the server.  I looked at the sys roles (sysmon, sysadm,sysctrl, and sysmaint group names and set them all to Administrators which is the admin group name on the windows server.  I just don't get it.  What am I missing?

FRUSTRATED IN STL

Jim
Jim YoumansSr Database AdministratorAsked:
Who is Participating?
 
Kent OlsenData Warehouse Architect / DBACommented:
Ok.  I suspect that the error is legit, but we're not seeing the reason that the error is thrown.  The user should have the authority to perform the the restore operation, but the underlying objects don't already exist so the low level interface is returning a "you can't do that" response which DB2 is translating into a generic permissions issue message.

I asked earlier, but kind of glossed over, the backup coming from a different server.   Put the "REDIRECT" keyword on the command.

RESTORE DATABASE DEV FROM {path} INTO {newpath} redirect;
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Hi Jim,

Let's make sure that DB2 recognizes the administrative permissions of that user.

  SELECT * FROM TABLE (sysproc.auth_list_groups_for_authid('xxx'))

And does the DEV database already exist on this server?  You may need the "REPLACE EXISTING" or "REDIRECT" clause on the RESTORE statement.

Kent
0
 
Jim YoumansSr Database AdministratorAuthor Commented:
The database I am trying to restore does not exist on the new server.  Only the SAMPLE database does.

So the account I use to run the server is AITDB2Service and when I run the above query I get...

AITDB2ADMINS
DOMAIN USERS

When I do the same for my network account which is an admin on the server I get...

BITBUCKET-SOFTWARE-USERS,CENTRIC_LEGALFEESDB_RWM,CENTRICIT,CITRIX_ACCESSMANAGEMENTCONSOLE,CONFLUENCE-USERS,
DEPSVCSRO,DEV_BIDATAREAD_ADMIN,DOMAIN USERS,EXTRAHOP_READONLY,ICINGANORMALACCESS,IS,IT,JIRA-SOFTWARE-USERS,
LASTPASSACCESS,LINUXLOGONKCN,LINUXLOGONKEEFEDATACENTER,OFFICE_365_USERS,PRD_BIDATAREAD_ADMIN,
PRD_PHONESYS_MGR,QAT_BIDATAREAD_ADMIN,QAT_PHONESYS_MGR,SCCM_REPORT_PURCHASING,SCOMSQLADMINS,
SCSM_NOTIFY_APPLICATION DEVELOPMENT,SCSM_NOTIFY_BUSINESS INTELLIGENCE,SCSM_NOTIFY_DATABASE SERVICES,
SCSM_SG_APPLICATION DEVELOPMENT,SCSM_SG_BUSINESS INTELLIGENCE,SCSM_SG_DATABASE SERVICES,
SCSM_TIER1,SCSM_TIER2_STANDARD,SW_DPA_ADMIN,SW_SQLDBAS,VMWARE_READ_ONLY,VPN_ACCESS,WHSLOCATORDB_RWM,
WSUS ADMINISTRATORS

So what should I see?  For DB2 permissions?
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
Kent OlsenData Warehouse Architect / DBACommented:
AITDB2Service seems to be in the AITDB2ADMINS group, so use it.
0
 
Jim YoumansSr Database AdministratorAuthor Commented:
I tried but it gives me the same error.  How do I add a user to the AITDB2ADMINS group?
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Through the Windows interface.

Right click in My Computer, then Manage, then Users.
0
 
Jim YoumansSr Database AdministratorAuthor Commented:
I have obviously done something wrong.  Looking at my groups in windows there is a DB2ADMNS group and a DB2USERS group. Those were created when I installed DB2.  There is no AITDB2ADMINS group.  Not sure where that is coming from.

I have 3 users in the DB2ADMNS group, jyoumans (me), CVADMIN (commvault), and AITDB2Service (which runs DB2 server).  I have tried the restore with jyoumans and AITDB2Service, both get the same not authorized error.

I installed DB2 with the AITDB2Service user.  I checked the DB2 windows service and AITDB2Service is listed as the LOG ON AS of that service.

What am I missing?

Jim
0
 
Kent OlsenData Warehouse Architect / DBACommented:
Are you logging in with a different user to try the restore or are you passing the username/password to the RESTORE command?  I've never seen this behavior and would be shocked if it happened when the username is passed on the command.
0
 
Jim YoumansSr Database AdministratorAuthor Commented:
I logged into the server using the AITDB2Service ID and then ran the restore like this.

db2 restore database DEPSVCS user AITDB2Service password "xxx' from x:\backups

And I get

SQL1092N  The requested command or operation failed because the user ID does
not have the authority to perform the requested command or operation.  User
ID: "AITDB2SERVICE".  SQLSTATE=00000

This should not be this hard.  Please note that DEPSVCS is not on the new server.  Just sample.

Jim
0
 
Jim YoumansSr Database AdministratorAuthor Commented:
So, I ended up wiping out all the sys*_group definitions and leaving them to NULL so that the server admin group would default to them.  Then I stopped and restarted the db2 instance and then bounced the server.  That seems to have worked.  Thank you so much!!
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.