Results 1 to 8 of 8
  1. #1
    Join Date
    Sep 2013
    Posts
    4

    Unanswered: db2 help for an oracle user : create database hangs without any output

    I am new to db2 but have worked on oracle before. Trying to create a database and it isn't succeeding. Quite possibly I am missing something obvious.

    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.

    How do I even get started with trying to diagnose what's wrong? I am new to db2, but familiar with oracle and linux/posix.

  2. #2
    Join Date
    Jul 2013
    Location
    Moscow, Russia
    Posts
    666
    Provided Answers: 55
    Hi,

    What do you see in the ~db2inst1/sqllib/db2dump/db2diag.log file?
    Regards,
    Mark.

  3. #3
    Join Date
    Nov 2003
    Posts
    2,933
    Provided Answers: 12
    Quote Originally Posted by alok.kumar View Post
    db2 => create database mytestdb
    And this hangs, without providing any output.
    Just a wild guess: add a semicolon to the end of the statement.
    Maybe the command line client is simply waiting for your to terminate (=finish) the statement?
    Code:
    db2 => create database mytestdb;
    I will not read nor answer questions where the SQL code is messy and not formatted properly using [code] tags: http://www.dbforums.com/misc.php?do=bbcode#code

    Tips for good questions:

    http://tkyte.blogspot.de/2005/06/how...questions.html
    http://wiki.postgresql.org/wiki/SlowQueryQuestions
    http://catb.org/esr/faqs/smart-questions.html

  4. #4
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    no in command prompt - you do not need ;
    try to open another session with db2Inst1
    db2 list applications
    db2 get snapshot for application agentid xx
    check diaglog
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  5. #5
    Join Date
    Sep 2013
    Posts
    4
    Quote Originally Posted by mark.b View Post
    Hi,

    What do you see in the ~db2inst1/sqllib/db2dump/db2diag.log file?
    This is what the log file contains(I deleted the db2diag.log file, then initiated the session and closed it to generate the incremental log):

    It was too long so I have attached it as a txt file.

    2013-09-03-16.36.26.466014+330 I30379E1015 LEVEL: Error
    PID : 2613 TID : 140140810856192 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : EIGHTBYT
    APPHDL : 0-114 APPID: *LOCAL.db2inst1.130903110629
    AUTHID : DB2INST1 HOSTNAME: cxps044
    EDUID : 132 EDUNAME: db2agent (EIGHTBYT)
    FUNCTION: DB2 UDB, base sys utilities, sqleIsDatabaseDirectoryConnectable, probe:6626
    MESSAGE : ZRC=0x8005006D=-2147155859=SQLE_CA_BUILT
    "SQLCA has been built and saved in component specific control block."
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1035 sqlerrml: 0
    sqlerrmc:
    sqlerrp : SQLE529
    sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0x00000000 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate:

    2013-09-03-16.36.26.466668+330 I31395E596 LEVEL: Severe
    PID : 2613 TID : 140140810856192 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : EIGHTBYT
    APPHDL : 0-114 APPID: *LOCAL.db2inst1.130903110629
    AUTHID : DB2INST1 HOSTNAME: cxps044
    EDUID : 132 EDUNAME: db2agent (EIGHTBYT)
    FUNCTION: DB2 UDB, base sys utilities, sqeDBMgr:tartUsingLocalDatabase, probe:3691
    RETCODE : ZRC=0x8005006D=-2147155859=SQLE_CA_BUILT
    "SQLCA has been built and saved in component specific control block."

    2013-09-03-16.36.26.469770+330 I31992E1015 LEVEL: Error
    PID : 2613 TID : 140140810856192 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : EIGHTBYT
    APPHDL : 0-115 APPID: *LOCAL.db2inst1.130903110630
    AUTHID : DB2INST1 HOSTNAME: cxps044
    EDUID : 132 EDUNAME: db2agent (EIGHTBYT)
    FUNCTION: DB2 UDB, base sys utilities, sqleIsDatabaseDirectoryConnectable, probe:6626
    MESSAGE : ZRC=0x8005006D=-2147155859=SQLE_CA_BUILT
    "SQLCA has been built and saved in component specific control block."
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1035 sqlerrml: 0
    sqlerrmc:
    sqlerrp : SQLE529
    sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0x00000000 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate:

    2013-09-03-16.36.26.470443+330 I33008E596 LEVEL: Severe
    PID : 2613 TID : 140140810856192 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : EIGHTBYT
    APPHDL : 0-115 APPID: *LOCAL.db2inst1.130903110630
    AUTHID : DB2INST1 HOSTNAME: cxps044
    EDUID : 132 EDUNAME: db2agent (EIGHTBYT)
    FUNCTION: DB2 UDB, base sys utilities, sqeDBMgr:tartUsingLocalDatabase, probe:3691
    RETCODE : ZRC=0x8005006D=-2147155859=SQLE_CA_BUILT
    "SQLCA has been built and saved in component specific control block."

    2013-09-03-16.36.26.473767+330 I33605E1011 LEVEL: Error
    PID : 2613 TID : 140140810856192 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : TUT_DB
    APPHDL : 0-116 APPID: *LOCAL.db2inst1.130903110631
    AUTHID : DB2INST1 HOSTNAME: cxps044
    EDUID : 132 EDUNAME: db2agent (TUT_DB)
    FUNCTION: DB2 UDB, base sys utilities, sqleIsDatabaseDirectoryConnectable, probe:6626
    MESSAGE : ZRC=0x8005006D=-2147155859=SQLE_CA_BUILT
    "SQLCA has been built and saved in component specific control block."
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1035 sqlerrml: 0
    sqlerrmc:
    sqlerrp : SQLE529
    sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0x00000000 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate:

    2013-09-03-16.36.26.474421+330 I34617E592 LEVEL: Severe
    PID : 2613 TID : 140140810856192 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : TUT_DB
    APPHDL : 0-116 APPID: *LOCAL.db2inst1.130903110631
    AUTHID : DB2INST1 HOSTNAME: cxps044
    EDUID : 132 EDUNAME: db2agent (TUT_DB)
    FUNCTION: DB2 UDB, base sys utilities, sqeDBMgr:tartUsingLocalDatabase, probe:3691
    RETCODE : ZRC=0x8005006D=-2147155859=SQLE_CA_BUILT
    "SQLCA has been built and saved in component specific control block."

    2013-09-03-16.36.26.477101+330 I35210E1011 LEVEL: Error
    PID : 2613 TID : 140140810856192 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : TUT_DB
    APPHDL : 0-117 APPID: *LOCAL.db2inst1.130903110632
    AUTHID : DB2INST1 HOSTNAME: cxps044
    EDUID : 132 EDUNAME: db2agent (TUT_DB)
    FUNCTION: DB2 UDB, base sys utilities, sqleIsDatabaseDirectoryConnectable, probe:6626
    MESSAGE : ZRC=0x8005006D=-2147155859=SQLE_CA_BUILT
    "SQLCA has been built and saved in component specific control block."
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1035 sqlerrml: 0
    sqlerrmc:
    sqlerrp : SQLE529
    sqlerrd : (1) 0x00000000 (2) 0x00000000 (3) 0x00000000
    (4) 0x00000000 (5) 0x00000000 (6) 0x00000000
    sqlwarn : (1) (2) (3) (4) (5) (6)
    (7) (8) (9) (10) (11)
    sqlstate:

    2013-09-03-16.36.26.477822+330 I36222E592 LEVEL: Severe
    PID : 2613 TID : 140140810856192 PROC : db2sysc
    INSTANCE: db2inst1 NODE : 000 DB : TUT_DB
    APPHDL : 0-117 APPID: *LOCAL.db2inst1.130903110632
    AUTHID : DB2INST1 HOSTNAME: cxps044
    EDUID : 132 EDUNAME: db2agent (TUT_DB)
    FUNCTION: DB2 UDB, base sys utilities, sqeDBMgr:tartUsingLocalDatabase, probe:3691
    RETCODE : ZRC=0x8005006D=-2147155859=SQLE_CA_BUILT
    "SQLCA has been built and saved in component specific control block."

  6. #6
    Join Date
    Sep 2013
    Posts
    4
    Quote Originally Posted by przytula_guy View Post
    no in command prompt - you do not need ;
    try to open another session with db2Inst1
    db2 list applications
    db2 get snapshot for application agentid xx
    check diaglog

    $ 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


    So the database did get created, but the db2 session hanged. That's strange. Any comments on this behavior?



    $ db2 get snapshot for application agent id 85
    SQL0104N An unexpected token "agent" was found following "APPLICATION".
    Expected tokens may include: "APPLID". SQLSTATE=42601
    $ db2 get snapshot for application applid 85
    SQL1611W No data was returned by Database System Monitor.
    $ db2 get snapshot for application applid "*LOCAL.db2inst1.130903105030"

    Application Snapshot

    Application handle = 85
    Application status = Commit Active
    Status change time = Not Collected
    Application code page = 1208
    Application country/region code = 1
    DUOW correlation token = *LOCAL.db2inst1.130903105030
    Application name = db2bp
    Application ID = *LOCAL.db2inst1.130903105030
    Sequence number = 00086
    TP Monitor client user ID =
    TP Monitor client workstation name =
    TP Monitor client application name =
    TP Monitor client accounting string =

    Connection request start timestamp = 09/03/2013 16:20:30.454156
    Connect request completion timestamp = 09/03/2013 16:20:32.066770
    Application idle time = Not Collected
    CONNECT Authorization ID = DB2INST1
    Client login ID = db2inst1
    Configuration NNAME of client = cxps044
    Client database manager product ID = SQL10050
    Process ID of client application = 17254
    Platform of client application = LINUXAMD64
    Communication protocol of client = Local Client

    Inbound communication address = *LOCAL.db2inst1

    Database name = MYTESTDB
    Database path = /home/db2inst1/db2inst1/NODE0000/SQL00004/MEMBER0000/
    Client database alias = MYTESTDB
    Input database alias =
    Last reset timestamp =
    Snapshot timestamp = 09/03/2013 16:45:05.346620
    Authorization level granted =
    User authority:
    DBADM authority
    SECADM authority
    DATAACCESS authority
    ACCESSCTRL authority
    Group authority:
    SYSADM authority
    CREATETAB authority
    BINDADD authority
    CONNECT authority
    IMPLICIT_SCHEMA authority
    Coordinator member number = 0
    Current member number = 0
    Coordinator agent process or thread ID = 46
    Current Workload ID = 2
    Agents stolen = 0
    Agents waiting on locks = 0
    Maximum associated agents = 1
    Priority at which application agents work = 0
    Priority type = Dynamic

    Lock timeout (seconds) = -1
    Locks held by application = 2
    Lock waits since connect = 0
    Time application waited on locks (ms) = Not Collected
    Deadlocks detected = Not Collected
    Lock escalations = 0
    Exclusive lock escalations = 0
    Number of Lock Timeouts since connected = 0
    Total time UOW waited on locks (ms) = Not Collected

    Total sorts = 1
    Total sort time (ms) = Not Collected
    Total sort overflows = 0

    Buffer pool data logical reads = Not Collected
    Buffer pool data physical reads = Not Collected
    Buffer pool temporary data logical reads = Not Collected
    Buffer pool temporary data physical reads = Not Collected
    Buffer pool data writes = Not Collected
    Buffer pool index logical reads = Not Collected
    Buffer pool index physical reads = Not Collected
    Buffer pool temporary index logical reads = Not Collected
    Buffer pool temporary index physical reads = Not Collected
    Buffer pool index writes = Not Collected
    Buffer pool xda logical reads = Not Collected
    Buffer pool xda physical reads = Not Collected
    Buffer pool temporary xda logical reads = Not Collected
    Buffer pool temporary xda physical reads = Not Collected
    Buffer pool xda writes = Not Collected
    Total buffer pool read time (milliseconds) = Not Collected
    Total buffer pool write time (milliseconds)= Not Collected
    Time waited for prefetch (ms) = Not Collected
    Unread prefetch pages = Not Collected
    Direct reads = Not Collected
    Direct writes = Not Collected
    Direct read requests = Not Collected
    Direct write requests = Not Collected
    Direct reads elapsed time (ms) = Not Collected
    Direct write elapsed time (ms) = Not Collected

    Number of SQL requests since last commit = 0
    Commit statements = 85
    Rollback statements = 0
    Dynamic SQL statements attempted = 3265
    Static SQL statements attempted = 88
    Failed statement operations = 0
    Select SQL statements executed = 271
    Xquery statements executed = 0
    Update/Insert/Delete statements executed = 0
    DDL statements executed = 1765
    Inactive stmt history memory usage (bytes) = 0
    Internal automatic rebinds = 0
    Internal rows deleted = 0
    Internal rows inserted = 0
    Internal rows updated = 0
    Internal commits = 53
    Internal rollbacks = 0
    Internal rollbacks due to deadlock = 0
    Binds/precompiles attempted = 292
    Rows deleted = 0
    Rows inserted = 0
    Rows updated = 0
    Rows selected = 270
    Rows read = 76635
    Rows written = 30818

    UOW log space used (Bytes) = Not Collected
    Previous UOW completion timestamp = Not Collected
    Elapsed time of last completed uow (sec.ms)= Not Collected
    UOW start timestamp = Not Collected
    UOW stop timestamp = Not Collected
    UOW completion status = Not Collected

    Open remote cursors = 0
    Open remote cursors with blocking = 0
    Rejected Block Remote Cursor requests = 0
    Accepted Block Remote Cursor requests = 271
    Open local cursors = 0
    Open local cursors with blocking = 0
    Total User CPU Time used by agent (s) = 22.949748
    Total System CPU Time used by agent (s) = 0.000000
    Host execution elapsed time = Not Collected

    Package cache lookups = 6330
    Package cache inserts = 6330
    Application section lookups = 3808
    Application section inserts = 3301
    Catalog cache lookups = 37624
    Catalog cache inserts = 5133
    Catalog cache overflows = 0
    Catalog cache high water mark = 0

    Workspace Information


    Most recent operation = Static Commit
    Most recent operation start timestamp = Not Collected
    Most recent operation stop timestamp = Not Collected
    Agents associated with the application = 1
    Number of hash joins = 0
    Number of hash loops = 0
    Number of hash join overflows = 0
    Number of small hash join overflows = 0
    Number of OLAP functions = 0
    Number of OLAP function overflows = 0

    Memory usage for application:

    Memory Pool Type = Application Heap
    Current size (bytes) = 458752
    High water mark (bytes) = 589824
    Configured size (bytes) = 1048576

    Agent process/thread ID = 46
    Agent Lock timeout (seconds) = -1
    Memory usage for agent:

    Memory Pool Type = Other Memory
    Current size (bytes) = 1310720
    High water mark (bytes) = 1703936
    Configured size (bytes) = 6163300352



    Looks like it is up and running, (but I could be wrong), does this output explain why db2 shell hanged on the create command?

  7. #7
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    no
    I would just break this command
    use db2 command line and try to connect to db
    Best Regards, Guy Przytula
    Database Software Consultant
    Good DBAs are not formed in a week or a month. They are created little by little, day by day. Protracted and patient effort is needed to develop good DBAs.
    Spoon feeding : To treat (another) in a way that discourages independent thought or action, as by overindulgence.
    DB2 UDB LUW Certified V7-V8-V9-V9.7-V10.1-V10.5 DB Admin - Advanced DBA -Dprop..
    Information Server Datastage Certified
    http://www.infocura.be

  8. #8
    Join Date
    Sep 2013
    Posts
    4
    Well it did come out finally when I tried this again. Thanks for the diagnostic help.

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

Tags for this Thread

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •