Solved

How do I get the build credentials for a DB2 Database?

Posted on 2010-11-29
9
627 Views
Last Modified: 2012-06-21
Disclaimer: I'm a SQL-Server develolper, and freely admit that I'm totally out of my depth on this one.  But I need to figure this out.

During a server replacement I run an app called DM2Migrate - which performs a backup-copy-"restore" from the old machine to the new one.  Most of the time.  One on five installs I encounter a DB2 database that was built under different credentials, and the DB2Migrate fails.

The database should always have been created under the special Admin account, but some of the contractors got a little lazy, and created the database under a different account, one that they could apparently remember easier.  (I'm not too sure about this, but corporate lore points to this excuse, so...)

Since it takes forty or fifty minutes to determine whether the Migration failed (or not), it makes sense to programatically figure out what credentials built the database first, then run the proper DB2Migrate app.  The only problem is that I have no idea how to craft the SQL statement that would shed light on this issue.

First: is what I'm asking for possible?  And Second: if so, how would one go about figuring it out?  Perhaps logging in as one "creator" account and running a particular SQL statement and looking at the resulting error?  I'm not at all sure how to approach this - in Oracle that information is available in the system tables.

Thanks (in advance) for your patience and attention.
0
Comment
Question by:LongFist
  • 3
  • 3
  • 2
  • +1
9 Comments
 
LVL 37

Expert Comment

by:momi_sabag
ID: 34232544
which platform and which db2 version are you using?
by build credentials you refer to the user that issued the create database command right?
0
 
LVL 37

Expert Comment

by:momi_sabag
ID: 34232546
have you looked at the owner column of syscat.databases?
0
 
LVL 1

Author Comment

by:LongFist
ID: 34235241
It is running on Windows 2000 Server; I'm not sure which version we're using (I'll sneak a peek tomorrow at work).  And yes, I need to know what the credentials were of the creator of the table.  They should be from one account, but some otherwise lazy contractors chose to use a different account because "it still works".  Except for us, when we attempt to migrate the data over to the new server.

As for looking at the owner column of syscat.databases - I honestly have no depth on the bench with DB2, so am in the dark where that is concerned.what would my SQL statement look like: "SELECT owner from syscat.databases where [databasename] = progTable" ?  Or would it be a little different, because DB2 is a little different?

Thanks for your patience and rapid responses!
0
 
LVL 37

Accepted Solution

by:
momi_sabag earned 300 total points
ID: 34235262
just a plain
select * from syscat.databases
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 
LVL 8

Expert Comment

by:mustaccio
ID: 34235778
Firstly, I don't think there is any DB2 version that has a catalog view or table called SYSCAT.DATABASES. Secondly, if they way you explain the migration process (backup - copy - restore) is indeed what happens, it does not really matter what authorization (user) ID built the database: all you need is sufficient authority to perform the restore on the target machine.

Having said that, you can be sure that if you have sufficient authorization to perform _one_ restore, you should have no problem restoring _any_ database. In other words, either there is some other problem with the restore, unrelated to the user ID in question, or the migration process works differently. It would greatly help if you could provide the actual DB2 version that you run and more details about the DB2Migrate application. Is it actually a compiled binary application, or just a batch script?
0
 
LVL 1

Author Comment

by:LongFist
ID: 34242140
Under these circumstances, and with today's paranoid security measures, you cannot assume anything.  Since the security facet of this probvlem is the crux of our issue, and is beyond my ability to control, let's just assume that DBMigrate Script A cannot be run against Database A unless it was created by DBAccount A.  If the contractors created Database A using other credentials, which has happened a time or two, then the DBMigrate Script A - which is enclosed in an Executable far from our capacity to manipulate (advanced security at work!) in the field - will fail, causing us to lose 45 minutes to an hour, and result in further complications.

That having been said: Oracle, SQL Server, MySQL, Access, and RealDB all seem to have a common "SystemDB" that describes all of the databases, tables, columns, etc. contained therein.  Why wouldn't DB2?  I just have no idea what it would be called, nor how I would get the information out of it.  I'm going to try "select * from syscat.databases " and see what I get.  I'll post results as soon as I get them.

Thanks again for your kind patience while I try to resolve this.
0
 
LVL 8

Assisted Solution

by:wolfgang_93
wolfgang_93 earned 200 total points
ID: 34242654
Some useful DB2 commands to issue to check things out as DBA
(these are ones that I have issued just now successfully on one of our
DB2 servers, so should work for you):

List databases cataloged on the server:
  list db directory

Command to connect to database FUBAR to examine:
  connect to FUBAR

In DB2 each table has an "owner" and a "definer".
To get a summary of counts of objects owned by assorted
owners:
   select owner,count(*) from SYSCAT.TABLES group by owner

To get a summary of counts of objects defined by assorted
accounts:
   select definer,count(*) from SYSCAT.TABLES group by definer

To list all the objects (type field is T, V, etc. depending on object being
a table, view, etc.) for a particular account ABC:
   select tabschema,tabname,type from SYSCAT.TABLES where owner='ABC'


0
 
LVL 8

Expert Comment

by:mustaccio
ID: 34243256
> with today's paranoid security measures, you cannot assume anything.  

I would argue that you can, if you know how the authentication and authorization work in a particular database engine.

> Oracle, SQL Server, MySQL, Access, and RealDB all seem to have a common "SystemDB"
> that describes all of the databases, tables, columns, etc. contained therein.  

The problem with this statement is that each DBMS has its own concept of what constitutes a database. Taking DB2 as an example, the list of known local databases is maintained "outside" those databases and, as wolfgang_93 described above, can be obtained by the LIST DATABASE DIRECTORY command. However, all database- and object-level authorization data are stored in the system catalog tables of each particular database. In other words, to determine authorizations of a given user ID, one needs to connect to the database first.

Furthermore, authorization controls are very granular and vary from version to version, and it's hard to tell what particular authorizations your DBMigrate script needs without knowing exactly what it does. For example, you could determine the user IDs that have DBADM authority by querying the appropriate catalog view:

select grantee from syscat.dbauth where dbadmauth = 'Y'

but this is not necessarily what you are looking for.

0
 
LVL 1

Author Closing Comment

by:LongFist
ID: 34277647
I was lucky (if you can call it that) the following night: another one of the "bad build" machines was encountered and we were able to use DB2CMD (and various SQL statements) to determine the difference between "good" installs and "bad" installs.  Momi_sabag - I thank you for your rapid responses which ultimately lead us to determine the actual problem.  Wolfgang_93, some of your other pointers enabled us to further identify attributes that made identification spot-on.

Unfortunately, before I could copy the SQL statement that ultimately identified the difference, it was whisked away by a programmer (along with the offending server) with a promise that we'd get an identifying script "in a week or so".

Sorry about that, but as a contractor in corporate America, I really don't have too many privileges.

Thanks again, guys - we were successful nonetheless!
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

November 2009 Recently, a question came up in the DB2 forum regarding the date format in DB2 UDB for AS/400.  Apparently in UDB LUW (Linux/Unix/Windows), the date format is a system-wide setting, and is not controlled at the session level.  I'm n…
Recursive SQL in UDB/LUW (you can use 'recursive' and 'SQL' in the same sentence) A growing number of database queries lend themselves to recursive solutions.  It's not always easy to spot when recursion is called for, especially for people una…
This video gives you a great overview about bandwidth monitoring with SNMP and WMI with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're looking for how to monitor bandwidth using netflow or packet s…
This tutorial demonstrates a quick way of adding group price to multiple Magento products.

747 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now