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

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.
Who is Participating?
momi_sabagConnect With a Mentor Commented:
just a plain
select * from syscat.databases
which platform and which db2 version are you using?
by build credentials you refer to the user that issued the create database command right?
have you looked at the owner column of syscat.databases?
Free Tool: Subnet Calculator

The subnet calculator helps you design networks by taking an IP address and network mask and returning information such as network, broadcast address, and host range.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

LongFistAuthor Commented:
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!
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?
LongFistAuthor Commented:
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.
wolfgang_93Connect With a Mentor Commented:
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
   select owner,count(*) from SYSCAT.TABLES group by owner

To get a summary of counts of objects defined by assorted
   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'

> 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.

LongFistAuthor Commented:
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!
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.