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

Posted on 2010-11-29
Medium Priority
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.
Question by:LongFist
  • 3
  • 3
  • 2
  • +1
LVL 37

Expert Comment

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?
LVL 37

Expert Comment

ID: 34232546
have you looked at the owner column of syscat.databases?

Author Comment

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!
Fill in the form and get your FREE NFR key NOW!

Veeam is happy to provide a FREE NFR server license to certified engineers, trainers, and bloggers.  It allows for the non‑production use of Veeam Agent for Microsoft Windows. This license is valid for five workstations and two servers.

LVL 37

Accepted Solution

momi_sabag earned 1200 total points
ID: 34235262
just a plain
select * from syscat.databases

Expert Comment

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?

Author Comment

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.

Assisted Solution

wolfgang_93 earned 800 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
   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'


Expert Comment

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.


Author Closing Comment

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!

Featured Post

Veeam and MySQL: How to Perform Backup & Recovery

MySQL and the MariaDB variant are among the most used databases in Linux environments, and many critical applications support their data on them. Watch this recorded webinar to find out how Veeam Backup & Replication allows you to get consistent backups of MySQL databases.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

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 (it really isn't that hard to do) Recursive SQL is most often used to convert columns to rows or rows to columns.  A previous article described the process of converting rows to columns.  This article will build off of th…
This lesson discusses how to use a Mainform + Subforms in Microsoft Access to find and enter data for payments on orders. The sample data comes from a custom shop that builds and sells movable storage structures that are delivered to your property. …
Screencast - Getting to Know the Pipeline
Suggested Courses
Course of the Month17 days, 7 hours left to enroll

831 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