3

I am new to db2 but have worked on oracle before. Trying to create a database and it succeeds, but the session where I issued a database creation command hangs.

I have installed db2 10.5 on a 64 bit ubuntu machine.

First, I logged in as db2inst1 which is the instance owner, and invoked db2 on the command line, to get to the shell.

alok@mylaptop:~$ su - db2inst1
शब्दकूट: 
$ uname -a
Linux cxps044 3.2.0-52-generic #78-Ubuntu SMP Fri Jul 26 16:21:44 UTC 2013 x86_64 x86_64 x86_64 GNU/Linux
$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.0

You can issue database manager commands and SQL statements from the command 
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside 
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

Next I attached an instance to db2inst1, which I believe was successful considering the output. I followed instructions from db2 cli install for this.

db2 => attach to db2inst1

   Instance Attachment Information

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

Next I attempted to create the database using this command:

db2 => create database mytestdb

And this hangs, without providing any output.

After checking a few hours later, I found that the database did get created.

$ db2 list applications

Auth Id Application Appl. Application Id DB # of
Name Handle Name Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST1 db2bp 85 *LOCAL.db2inst1.130903105030 MYTESTDB 1 
DB2INST1 db2bp 86 *LOCAL.db2inst1.130903105031 EIGHTBYT 1 
DB2INST1 db2bp 99 *LOCAL.db2inst1.130903110149 TUT_DB 1

However, how does one figure out how long to wait before giving up? Is there any log or other output that can give a cue on whether to keep waiting for the command to complete execution?

I tried another create database command, which hanged, and then I checked in another session to find that the database has indeed been created.

Session 1:

$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.0

You can issue database manager commands and SQL statements from the command 
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside 
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => attach to db2inst1

   Instance Attachment Information

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

db2 => create database mytestd2 automatic storage yes

While this is hanging here, session 2:

$ db2
(c) Copyright IBM Corporation 1993,2007
Command Line Processor for DB2 Client 10.5.0

You can issue database manager commands and SQL statements from the command 
prompt. For example:
    db2 => connect to sample
    db2 => bind sample.bnd

For general help, type: ?.
For command help, type: ? command, where command can be
the first few keywords of a database manager command. For example:
 ? CATALOG DATABASE for help on the CATALOG DATABASE command
 ? CATALOG          for help on all of the CATALOG commands.

To exit db2 interactive mode, type QUIT at the command prompt. Outside 
interactive mode, all commands must be prefixed with 'db2'.
To list the current command option settings, type LIST COMMAND OPTIONS.

For more detailed help, refer to the Online Reference Manual.

db2 => attach to db2inst1

   Instance Attachment Information

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


db2 => list applications

Auth Id  Application    Appl.      Application Id                                                 DB       # of
         Name           Handle                                                                    Name    Agents
-------- -------------- ---------- -------------------------------------------------------------- -------- -----
DB2INST1 db2bp          226        *LOCAL.db2inst1.130903115709                                   MYTESTD2 1    

db2 =>

^C, ^X, ^Z and so on in session 1 has no impact.

I am new to db2, but familiar with oracle and linux/posix.

A few minutes later:

I finally got the prompt back in session 1:

db2 => create database mytestd2 automatic storage yes
DB20000I  The CREATE DATABASE command completed successfully.
db2 =>

So this question is kind of "auto resolved", but the observations might be useful to someone else.

Improve this question

1 Answer 1

Reset to default
1

hmm, scratch my earlier answer about a trailing ';'
I still suspect it's "hanging" because it is not a complete statement, it's waiting on you to complete it. (don't have a db2 instance available at the moment to test unfortunately)

try this:

CREATE DATABASE mytestdb
  AUTOMATIC STORAGE YES

It's a good idea to run

db2 list applications

in another session while you wait for the command to complete. That would tell you if something is indeed happening.

Improve this answer
6
  • I found out a few hours later that it did get created, but the hanging is still unexplained. I'm modifying the question to reflect this behavior.
    – alok
    Commented Sep 3, 2013 at 11:55
  • 2
    create database mydb is indeed a valid command, though the database name that's shown is not -- I believe it is limited to 8 ASCII characters. However, it should have produced an error anyway. Note that creating a database can take considerable amount of time, so may be one just needs to wait a bit longer. The file /home/db2inst1/sqllib/db2dump/db2diag.log will contain additional diagnostic information if there are problems.
    – mustaccio
    Commented Sep 3, 2013 at 11:56
  • yes but the database has indeed been created, even though the db2 session hasn't yet returned. I have updated the question to reflect this behavior
    – alok
    Commented Sep 3, 2013 at 12:08
  • I finally got back the prompt in session 1 :)
    – alok
    Commented Sep 3, 2013 at 12:11
  • Glad to hear it got solved. If it takes hours to return something odd is going on though. I try to avoid working with the db2 cli directly. OT, but IBM has a good free UI named data studio to work with db2. the installer can be a pain but imho it's worth the effort.
    – r.m
    Commented Sep 3, 2013 at 20:27

Your Answer

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

Not the answer you're looking for? Browse other questions tagged or ask your own question.