3 
db2 CREATE DATABASE XYZ

db2 DROP   DATABASE XYZ

These db2 commands which doesn't provide support to specify username/password while executing on db2cmd/db2clp for a specific user other than machine logged-in user. Hence result a exception

"SQL1092N The requested command or operation failed because the user ID does not have the authority to perform the requested command or operation."

And when the same commands run as by choosing 'Run as different user', it works properly.

  1. But I need to use same mechanism from Java code, but don't know how. Is it possible to open installed db2's clp as a different user form Java code??
  2. Is it possible to associate username/password in create/drop commands?
Improve this question
9
  • 1
    @Sebastian_H create and drop command is the part of Db2 so why jdbc? i am using db2. Commented Sep 5, 2012 at 8:35
  • 1
    Sabastian, CREATE/DROP database are not the part of JDBC, these are the parts of database API only. You can't run JDBC statement for creating/droping database, it won't be executed. Commented Sep 5, 2012 at 9:37
  • 3
    @Kishore I did a (very) quick research via Google and found several tutorials explaining how to create databases via JDBC. These were all for MySQL however. So JDBC can execute such statements, if it's supported by the database. I also found a post claiming that in DB2 for LUW create database is indeed only available on the command line, while in DB2 for z/OS it's supposed to also work as an SQL statement. I assume he uses DB2 for LUW, but I didn't know beforehand that it doesn't support the command via SQL. So, my bad if I made a useless suggestion. Commented Sep 5, 2012 at 12:49
  • 2
    I just want to ask you that if no database created initially (first time), then how would I then use jdbc for Create database command. Reason: JDBC connection can only be established, if u specify a database name in connection URL, after then u can use statements to create tables/procedures etc, but if not database is there (initially), then how u will use JDBC? Commented Sep 5, 2012 at 13:15
  • 1
    @RTA In JDBC it's dependent on the subprotocol/drivers if you have to specify a database. You can see this here, for example: docs.oracle.com/javase/tutorial/jdbc/basics/… Apparently DB2 always requires a database in the URL. Which, again, I wasn't aware off beforehand. I just assumed it would work and didn't know the limitations of DB2 in that matter. It would've been valid for a different type of database like MySQL though. Commented Sep 5, 2012 at 14:00

1 Answer 1

Reset to default
3

From the CLP you can first ATTACH to the instance using whatever user ID you need, and then issue the CREATE DATABASE command.

If the instance is running on your local machine, you just attach like this:

$ db2 attach to db2inst1 user db2inst1
Enter current password for db2inst1: 

   Instance Attachment Information

 Instance server        = DB2/LINUXX8664 9.7.5
 Authorization ID       = DB2INST1
 Local instance alias   = DB2INST1

If the instance is running on a remote server, you attach to the "node name" that you have defined in your database catalog:

$ db2 list db directory

 System Database Directory

 Number of entries in the directory = 1

Database 1 entry:

 Database alias                       = SAMPLE
 Database name                        = SAMPLE
 Node name                            = SRV01
 Database release level               = d.00
 Comment                              =
 Directory entry type                 = Remote
 Catalog database partition number    = -1
 Alternate server hostname            =
 Alternate server port number         =

In this example, the Node Name shown in the output from list database directory is SRV01, so you would attach using: db2 attach to srv01 user <username>.

Improve this answer
Sign up to request clarification or add additional context in comments.

1 Comment

C:\> db2 attach to db2 user *** using *** Saved my life!

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Start asking to get answers

Find the answer to your question by asking.

Ask question

Explore related questions

See similar questions with these tags.