Results 1 to 12 of 12
  1. #1
    Join Date
    Sep 2011
    Posts
    20

    Question Unanswered: A problem with database connection

    Hello,


    we have a problem with one of our production databases.

    Sometimes users aren't able to connect to the database from Toad getting an error below while connections from DataStage or Putty are fine:

    DB2 Database Error: ERROR [08001] [IBM] SQL30081N A communication error has been detected. Communication protocol being used: "TCP/IP". Communication API being used: "SOCKETS". Location where the error was detected: "127.0.0.1". Communication function detecting the error: "selectForRecvTimeout". Protocol specific error code(s): "0", "*", "*". SQLSTATE=08001 (Remembered answer: "OK". Enable).

    db2diag.log contain following:

    2013-09-06-00.05.14.260451-240 E28951737A475 LEVEL: Event
    PID : 1886 TID : 2200172489040PROC : db2sysc 0
    INSTANCE: db2inst NODE : 000 DB : PRODDB
    APPHDL : 0-9873 APPID: 9.190.236.246.2245.130906040458
    AUTHID : USERID
    EDUID : 8915 EDUNAME: db2agent (PRODDB) 0
    FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FirstConnect, probe:1000
    START : DATABASE: PRODDB : ACTIVATED: NO

    2013-09-06-00.05.14.263868-240 I28952213A544 LEVEL: Error
    PID : 1886 TID : 2200172489040PROC : db2sysc 0
    INSTANCE: db2inst NODE : 000 DB : PRODDB
    APPHDL : 0-9873 APPID: 9.190.236.246.2245.130906040458
    AUTHID : USERID
    EDUID : 8915 EDUNAME: db2agent (PRODDB) 0
    FUNCTION: DB2 UDB, common communication, sqlcctcptest, probe:11
    MESSAGE : Detected client termination
    DATA #1 : Hexdump, 2 bytes
    0x00000200447EA5CC : 0036 .6

    2013-09-06-00.05.14.263935-240 I28952758A526 LEVEL: Error
    PID : 1886 TID : 2200172489040PROC : db2sysc 0
    INSTANCE: db2inst NODE : 000 DB : PRODDB
    APPHDL : 0-9873 APPID: 9.190.236.246.2245.130906040458
    AUTHID : USERID
    EDUID : 8915 EDUNAME: db2agent (PRODDB) 0
    FUNCTION: DB2 UDB, common communication, sqlcctest, probe:50
    MESSAGE : sqlcctest RC
    DATA #1 : Hexdump, 2 bytes
    0x00000200447EA6AE : 0036 .6

    2013-09-06-00.05.14.263972-240 I28953285A506 LEVEL: Error
    PID : 1886 TID : 2200172489040PROC : db2sysc 0
    INSTANCE: db2inst NODE : 000 DB : PRODDB
    APPHDL : 0-9873 APPID: 9.190.236.246.2245.130906040458
    AUTHID : USERID
    EDUID : 8915 EDUNAME: db2agent (PRODDB) 0
    FUNCTION: DB2 UDB, base sys utilities, sqeAgent::AgentBreathingPoint, probe:10
    CALLED : DB2 UDB, common communication, sqlcctest
    RETCODE : ZRC=0x00000036=54

    2013-09-06-00.05.14.264130-240 I28953792A1355 LEVEL: Warning
    PID : 1886 TID : 2200172489040PROC : db2sysc 0
    INSTANCE: db2inst NODE : 000 DB : PRODDB
    APPHDL : 0-9873 APPID: 9.190.236.246.2245.130906040458
    AUTHID : USERID
    EDUID : 8915 EDUNAME: db2agent (PRODDB) 0
    FUNCTION: DB2 UDB, base sys utilities, sqeAgent::AgentBreathingPoint, probe:16
    CALLSTCK: (Static functions may not be resolved correctly, as they are resolved to the nearest symbol)
    [0] 0x000002000155B6FC _ZN8sqeAgent19AgentBreathingPointEi + 0x51C
    [1] 0x00000200012EF8B2 _Z15sqldFetchDirectP13SQLD_DFM_WORK + 0x892
    [2] 0x0000020001288348 _Z13sqldDataFetchP8SQLD_CCBP8SQLZ_RIDmP7SQLI_CB + 0x39C
    [3] 0x0000020003B9AF14 /home/db2inst /sqllib/lib64/libdb2e.so.1 + 0x3B4EF14
    [4] 0x0000020003B9CB3A _Z8sqlifnxtP7SQLI_CBP11SQLI_SFGLOB + 0x516
    [5] 0x0000020003B56344 _Z7sqlirdkP8sqeAgentP10SQLI_IXPCRP9SQLD_IXCBmP8SQL D_KEYP12SQLI_KEYDATAP9sqli_scanPi + 0x990
    [6] 0x0000020001278FCE _Z14sqldIndexFetchP8SQLD_CCBP8SQLZ_RID + 0x16A
    [7] 0x0000020001282C0A _Z12sqldRowFetchP8sqeAgentP8SQLD_CCBmmPP10SQLD_VAL UEP8SQLZ_RIDmP12SQLD_ID_LISTP9SQLP_LSN8 + 0x54A
    [8] 0x000002000341EB34 _ZN16sqlrlCatalogScan5fetchEv + 0x190
    [9] 0x000002000342D3C0 _Z8sqlrlgtsP8sqlrr_cbsP18sqlr_tmp_tbsp_infoPjS3_b + 0x510

    2013-09-06-00.05.14.267157-240 I28955148A541 LEVEL: Error
    PID : 1886 TID : 2200172489040PROC : db2sysc 0
    INSTANCE: db2inst NODE : 000 DB : PRODDB
    APPHDL : 0-9873 APPID: 9.190.236.246.2245.130906040458
    AUTHID : USERID
    EDUID : 8915 EDUNAME: db2agent (PRODDB) 0
    FUNCTION: DB2 UDB, catalog services, sqlrl_get_tmp_tbsp, probe:40
    RETCODE : ZRC=0x80040003=-2147221501=SQLD_INTRP "USER INTERRUPT DETECTED"
    DIA8003C The interrupt has been received.

    2013-09-06-00.05.14.267249-240 I28955690A541 LEVEL: Error
    PID : 1886 TID : 2200172489040PROC : db2sysc 0
    INSTANCE: db2inst NODE : 000 DB : PRODDB
    APPHDL : 0-9873 APPID: 9.190.236.246.2245.130906040458
    AUTHID : USERID
    EDUID : 8915 EDUNAME: db2agent (PRODDB) 0
    FUNCTION: DB2 UDB, relation data serv, sqlrr_appl_init, probe:130
    RETCODE : ZRC=0x80040003=-2147221501=SQLD_INTRP "USER INTERRUPT DETECTED"
    DIA8003C The interrupt has been received.

    2013-09-06-00.05.14.267293-240 I28956232A833 LEVEL: Error
    PID : 1886 TID : 2200172489040PROC : db2sysc 0
    INSTANCE: db2inst NODE : 000 DB : PRODDB
    APPHDL : 0-9873 APPID: 9.190.236.246.2245.130906040458
    AUTHID : USERID
    EDUID : 8915 EDUNAME: db2agent (PRODDB) 0
    FUNCTION: DB2 UDB, relation data serv, sqlrr_appl_init, probe:130
    DATA #1 : SQLCA, PD_DB2_TYPE_SQLCA, 136 bytes
    sqlcaid : SQLCA sqlcabc: 136 sqlcode: -1224 sqlerrml: 0
    sqlerrmc:
    sqlerrp : SQL09075
    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-06-00.05.14.267397-240 I28957066A469 LEVEL: Warning
    PID : 1886 TID : 2200172489040PROC : db2sysc 0
    INSTANCE: db2inst NODE : 000 DB : PRODDB
    APPHDL : 0-9873 APPID: 9.190.236.246.2245.130906040458
    AUTHID : USERID
    EDUID : 8915 EDUNAME: db2agent (PRODDB) 0
    FUNCTION: DB2 UDB, base sys utilities, sqle_remap_errors, probe:100
    MESSAGE : ZRC 0x80040003 remapped to SQLCODE -1044

    2013-09-06-00.05.14.267434-240 I28957536A557 LEVEL: Warning
    PID : 1886 TID : 2200172489040PROC : db2sysc 0
    INSTANCE: db2inst NODE : 000 DB : PRODDB
    APPHDL : 0-9873 APPID: 9.190.236.246.2245.130906040458
    AUTHID : USERID
    EDUID : 8915 EDUNAME: db2agent (PRODDB) 0
    FUNCTION: DB2 UDB, base sys utilities, sqeApplication::AppStartUsing, probe:140
    RETCODE : ZRC=0x80040003=-2147221501=SQLD_INTRP "USER INTERRUPT DETECTED"
    DIA8003C The interrupt has been received.


    So if user isn't able connect to the database from Toad he connect from putty first then he is able to connect from Toad.

    But the users should be able to connect to the database from Toad and others tools at any time.

    Does someone know possible causes of this issue and how to solve it? Please help.

    We have some more db2 instances and databases on the same server but such problem is with only one database.

  2. #2
    Join Date
    Apr 2006
    Location
    Belgium
    Posts
    2,514
    Provided Answers: 11
    db2level/platform ??
    is ldap involved ?
    type of connection ?
    try cutting of diaglog - diaglevel to 4
    try to connect from xx (remote)
    see message..
    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

  3. #3
    Join Date
    Aug 2010
    Location
    Singapore
    Posts
    197
    please check you db2set variable and /etc/services is similar to your svce
    - Ananth
    DB2 DBA LUW
    "coming second, just means you are the first loser"

  4. #4
    Join Date
    Sep 2011
    Posts
    20
    DB2 v9.7.0.5
    SUSE Linux Enterprise Server 10 (s390x)
    Memory: 55,333 MB
    Swap: 20,195 MB

    As I understand problem is only when users try to connect from Toad, seems it uses CLI drivers.

    But after connection from putty Toad also connect successfully:

    connect from toad: failed
    connect from putyy: successful
    connect from toad: successful.

  5. #5
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    What happens if you keep the db activate all the time (db2 activate db <db name>)? Do you get a timeout from toad?

  6. #6
    Join Date
    Sep 2011
    Posts
    20
    I assume there should not be such issue. But how to keep it active all time?

    I see many messages in db2diag.log as:

    2013-09-06-00.05.14.260451-240 E28951737A475 LEVEL: Event
    PID : 1886 TID : 2200172489040PROC : db2sysc 0
    INSTANCE: db2inst NODE : 000 DB : PRODDB
    APPHDL : 0-9873 APPID: 9.190.236.246.2245.130906040458
    AUTHID : USERID
    EDUID : 8915 EDUNAME: db2agent (PRODDB) 0
    FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FirstConnect, probe:1000
    START : DATABASE: PRODDB : ACTIVATED: NO


    Doesn it mean the database is not activated? And why it is not activated?

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

    have you already tried to set the ConnectTimeout property to 0 in the db2cli.ini file on this client?
    Regards,
    Mark.

  8. #8
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    You can keep the db active all the time by issuing the following command on the server: db2 activate db proddb

    If you don't activate it using this command, the db will become inactive after the last application disconnects.

    Not sure if this will help, but no harm in trying.

  9. #9
    Join Date
    Sep 2011
    Posts
    20
    But when activate db command should be executed? After each db2 stop/start?
    Could any application deactivate the database, for example DataStage job?

  10. #10
    Join Date
    Aug 2008
    Location
    Toronto, Canada
    Posts
    2,367
    After restarting the instance. db2stop will deactivate it.

  11. #11
    Join Date
    Jan 2009
    Location
    Zoetermeer, Holland
    Posts
    746
    Quote Originally Posted by Drakosha View Post
    As I understand problem is only when users try to connect from Toad, seems it uses CLI drivers.
    Toad uses CLI, yes. But it installes its OWN CLI!
    So when you have your regular CLI installed & configured all is fine. Then you install toad on top of that and toad will install & use its own cli and import your settings. You will notice nothing strange.
    Time moves on and you change things (uncatalog/catalog/update CLI CFG) and those changes will not be seen by toad.
    Toad is a very nice tool but when you are not aware of the above it will cause some headache.
    Somewhere between " too small" and " too large" lies the size that is just right.
    - Scott Hayes

  12. #12
    Join Date
    Sep 2013
    Location
    India
    Posts
    246

    not able to connect

    Hello..

    I can help You in this issue

    Please provide me the following inputs:

    Toad client and db server plateform (DB + OS)
    TCP/IP and service PORTs details
    Its physical machine or some visualization (VM) are involved.


    cheers
    ssumit

Posting Permissions

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