DB2 9.7 Permissions Issue

Something in the permissions aren't like you think they are.

Check your permissions with this:

  select * from SYSCAT.DBAUTH  where GRANTEE = 'JYOUMANSSO'

The "List Applications" command requires that the user (you) have any one of SYSADM, SYSCTRL, SYSMAINT, or SYSMON.

I have a Y for everything except for LIBRARYADMAUTH.

Also odd is that if I run GET DB CFG FOR database on 10.5 it works fine.  If I run it on this 9.7 database I get "SQL5060N  The configuration parameter token specified is invalid."

can you run GET DB CFG without the FOR clause?

Same error.

Are you connecting with the default user?  

  "Connect to {database}"
or
   "Connect to {database} user {username}"

Check to make sure that DB2 "knows" the user.

  SELECT current_user FROM sysibm.sysdummy1

Something seems pretty screwed up with that database.  Can you stop and restart it during the day?

And check the diaglog for anything unusual.

So when I select Current_User I get JYOUMANSSO.  Yeah, I think this thing is hosed.  I will check the DIAG.

 Got dumped in my lap so not sure what all has bee done to it.  I will try a restart tonight or tomorrow.  Thanks for the help.  You are the best as always!!

So I can run the GET DB CFG from the command line on the server but not from TOAD.  Same permissions issue otherwise.

Ahhh.....  Good info.

TOAD is a database client tool that runs SQL statements.  (It has other features, but that's its core.)

The Command Line Interface is an IBM client for maintaining the database and has its own commands, but it will also pass SQL to DB2 for execution.

Unless Toad for DB2 has changed since I last used it, the command line processor's built-in commands are not available in DB2.

I have never had an issue with running most command line commands from TOAD but that is with 10.5 and this is 9.7.

Hi Jim,

Did you make any headway on this?  I don't have the necessary versions of Toad/DB2 to test for you.

Kent

Hi,

Note that in Windows you should run commands with "elevated" rights. That is open an command line with "run as Administrator" and try from there. :)
Use for example db2cmdadmin instead of db2cmd.

The difference could be due to how strict security you initialy configured each database.

Log into Windows as the database owner and configure the database from that user and add/grant your username to SYSADM group etc.

Regards,
    Tomas Helgi

So I am still having issues.  This is a DB2 9.7 installed on Windows Server 2008 R2.  The dbm config lists DB2ADMNS as the SYSADMN group.  When I look a the groups there is a DB2ADMNS group and JYOUMANSSO is a member as well as the DB2Service account.  DB2Service is an AD Group.  DB2Service user is also running the DB2-DB2COPY1-DB2-0 service.

When I am logged in as JYOUMANSSO or DB2Service I can't run LIST APPLICATION or BACKUP or UPDATE DBM CFG parameters.

Looking at the history, the daily backups were running until early April as DB2Service.  Then they just stop and the error started saying account did not have permissions to run BACKUP command.

So now I have this server which is production and no backups since early April.  The server was rebooted on May 11th as part of the monthly windows maintenance.

When I run DB2DIAG > c:\temp\diaglog.txt it seems to hang.  The first couple of dozen entries are written to the file but they are from September 2017.  So that seems wrong.

Kind of at a loss here.  Any help is greatly appreciated.  

Jim

One more thing.  Looking at the DBM config parameters for DIAG I see this.  I don't see a path or size set for the diag log.  That can't be right can it?  What would be the default path for the DIAG Log?


 Diagnostic error capture level                       (DIAGLEVEL) = 3
 Notify Level                                                       (NOTIFYLEVEL) = 3
 Diagnostic data directory path                       (DIAGPATH) =
 Alternate diagnostic data directory path      (ALT_DIAGPATH) =
 Size of rotating db2diag & notify logs (MB)  (DIAGSIZE) = 0

So when I run
DB2SET DB2_GRP_LOOKUP = LOCAL

I get this
DBI1303W Variable not set
Explanation: the variable was not set in the profile registry.
User Response: No further action is required.

Sorry the =
 should not be there.

Same thing.

Hi Jim,

Has a database been recently created on that server?  I don't remember all of the details of a system that old, but I don't remember ever seeing a system where the diaglog file location was registered as blank (or null).  I'm wondering if a database was created or restored that perhaps failed or took a shortcut, particularly one where a database alias conflicted.

Not sure.  I did find the DIAG log in the C:\ProgramData\IBM\DB2\DB2COPY1\DB2 directory.  The reason it was timing out is that the file is from 2017 and has never been rolled over so there are over 3 million rows in it.  I will update that as soon as I get control of the db again.  I have updated a few parameters and I hope that once I get permission to restart the database and server I will be able to do what I need.  I love just having these things dropped in my lap.  I will let you know if this works.

OK, got it working.   Here are all the things I ended up doing.  Not sure if which one fixed it or if they all did.

 I updated the DIAG file size and location so we won't have freakishly large logs.
I updated the the DBADMNS group to include the group named in the DB2extsecurity.log.
I ran the DB2SET DB2_GRP_LOOKUP = LOCAL until it took.
I added the DB2ADMNS local group to the DB2 Groups and gave it full rights.

I then stopped db2 with force and rebooted the server.

Once it came back up, I was able to connect with my user account and run the LIST APPLICATION command and the BACKUP command.

Thank you Kent and Tomas for your help, I really, really appreciate it!!!!

Jim

How do I credit both of you as the solution to my issue?

I ran the DB2SET DB2_GRP_LOOKUP = LOCAL until it took.

I've tried something similar in the past, but found that the stupid computer is more hard headed than I am....

Hi,

To credit multiple solutions see here

Regards,
    Tomas Helgi