Topic
9 replies Latest Post - ‏2013-04-22T20:13:02Z by DB2MUS
SystemAdmin
SystemAdmin
17917 Posts
ACCEPTED ANSWER

Pinned topic SQL1042C SQLSTATE=58004 when opening a Database

‏2013-03-26T19:59:58Z |
I upgrade DB2 V9.5 ESE(windows w2003) to DB2 v9.7.301.326" and Fix Pack "3a".
Through db2cc, i can connect to all the Databses, but i can not with only one :
The error displayed is :

IBMCLI Driver SQL1042C An unexpected system error occurred.
SQLSTATE=58004

In the diaglog, it's big and i cant open it now

In the STMM log:

2013-03-26-15.53.25.765000-240 I8167041H936 LEVEL: Event
PID : 1976 TID : 900 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : FILES
APPHDL : 0-1885 APPID: *LOCAL.DB2.130321233805
AUTHID : LCUSER
EDUID : 900 EDUNAME: db2stmm (FILES) 0
FUNCTION: DB2 UDB, Self tuning memory manager, stmmGetDBMemDataAutomatic, probe:3832
DATA #1 : String, 480 bytes
Configured memory on the system: 1048576
Memory currently available on the system: 9984
Configured memory on the partition: 838861
Memory currently available on the partition: 62189
Set's configured size: 39680
Overflow left: 7680
Uncommited size: 13856
Target consumer size: 699735
Current consumer size: 32000
Max growth: 0
Current overflow percent: 0.200000
Target overflow percent: 0.200000
Average benefit: 0
Global benefit: Instance: DB2 DB: HOMEPAGE Benefit: 8.81266E-009
2013-03-26-15.53.25.780000-240 I8167979H644 LEVEL: Warning
PID : 1976 TID : 900 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : FILES
APPHDL : 0-1885 APPID: *LOCAL.DB2.130321233805
AUTHID : LCUSER
EDUID : 900 EDUNAME: db2stmm (FILES) 0
FUNCTION: DB2 UDB, Self tuning memory manager, stmmCheckIfFreeMemoryIsEnoughForSizeIncr, probe:667
MESSAGE : ZRC=0xFFFFEC49=-5047
DATA #1 : String, 138 bytes
There is not enough free memory for size increase. Free memory in pages: Physical memory = 0, Instance memory = 0, Database memory = 62189

2013-03-26-15.53.25.780000-240 I8168625H645 LEVEL: Error
PID : 1976 TID : 900 PROC : db2syscs.exe
INSTANCE: DB2 NODE : 000 DB : FILES
APPHDL : 0-1885 APPID: *LOCAL.DB2.130321233805
AUTHID : LCUSER
EDUID : 900 EDUNAME: db2stmm (FILES) 0
FUNCTION: DB2 UDB, Self tuning memory manager, stmmUpdateDBConfig, probe:275
MESSAGE : ZRC=0x82AE00A7=-2102525785=STMM_CONFIG_UPDATE_FAILED
"An attempted configuration update failed"
DATA #1 : String, 78 bytes
Error updating parameter Database_memory, updateValue = 40000, sqlcode = -5047
The following is an output of the "GET of the Database" in a problem:

Self tuning memory (SELF_TUNING_MEM) = ON
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC(79056)
Database memory threshold (DB_MEM_THRESH) = 10
Max storage for lock list (4KB) (LOCKLIST) = AUTOMATIC(4096)
Percent. of lock lists per application (MAXLOCKS) = AUTOMATIC(98)
Package cache size (4KB) (PCKCACHESZ) = AUTOMATIC(320)
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(257)
Sort list heap (4KB) (SORTHEAP) = AUTOMATIC(51)

Database heap (4KB) (DBHEAP) = AUTOMATIC(1335)
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 300
Log buffer size (4KB) (LOGBUFSZ) = 98
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 50747
Buffer pool size (pages) (BUFFPAGE) = 250
SQL statement heap (4KB) (STMTHEAP) = AUTOMATIC(2048)
Default application heap (4KB) (APPLHEAPSZ) = AUTOMATIC(1024)
Application Memory Size (4KB) (APPL_MEMORY) = AUTOMATIC(10000)
Statistics heap size (4KB) (STAT_HEAP_SZ) = AUTOMATIC(4384)

Thanks for help
Updated on 2013-03-27T17:45:00Z at 2013-03-27T17:45:00Z by SystemAdmin
  • nivanov1
    nivanov1
    231 Posts
    ACCEPTED ANSWER

    Re: SQL1042C SQLSTATE=58004 when opening a Database

    ‏2013-03-26T20:32:08Z  in response to SystemAdmin
    I think you can start with running "db2diag -A" to archive and truncate db2diag.log (or just delete the file), then try connecting to the database and see what errors appear in db2diag.log.
    • SystemAdmin
      SystemAdmin
      17917 Posts
      ACCEPTED ANSWER

      Re: SQL1042C SQLSTATE=58004 when opening a Database

      ‏2013-03-27T13:24:54Z  in response to nivanov1
      Thanks for responding:
      I took the msg in the db2diag as you said:

      2013-03-27-09.21.49.849000-240 E1370H1261 LEVEL: Error (OS)
      PID : 1976 TID : 3480 PROC : db2syscs.exe
      INSTANCE: DB2 NODE : 000 DB : TOOLSDB
      APPHDL : 0-28348 APPID: *LOCAL.DB2.130327132149
      AUTHID : DB2SERV0
      EDUID : 3480 EDUNAME: db2agent (TOOLSDB) 0
      FUNCTION: DB2 UDB, SQO Memory Management, sqloLogMemoryCondition, probe:100
      CALLED : OS, -, VirtualAlloc
      OSERR : 8 "Not enough storage is available to process this command."
      MESSAGE : Private memory and/or virtual address space exhausted
      DATA #1 : Requested size, PD_TYPE_MEM_REQUESTED_SIZE, 4 bytes
      1048576
      DATA #2 : Current set size, PD_TYPE_SET_SIZE, 4 bytes
      65536
      CALLSTCK:
      [0] 0x6CE39BD4 _VInfreq__pdLogSysRC + 0x54
      [1] 0x6CC4F0A0 sqloLogMemoryCondition. + 0x166
      [2] 0x6CE1F60E _VInfreq__sqlogmblkEx + 0x228
      [3] 0x6D998BB2 sqlmoAllocApplsSharedHeap + 0x6E
      [4] 0x6D730747 sqeLocalDatabase::FirstConnect + 0x1597
      [5] 0x6D72F5F7 sqeLocalDatabase::FirstConnect + 0x447
      [6] 0x6D74BB0C sqeDBMgr::StartUsingLocalDatabase + 0xAF0
      [7] 0x6D752662 sqeApplication::AppStartUsing + 0x194
      [8] 0x6D74F3E1 sqeApplication::AppLocalStart + 0x42B
      [9] 0x6D7E874A sqlelostWrp + 0x18

      2013-03-27-09.21.49.849000-240 E2633H759 LEVEL: Warning
      PID : 1976 TID : 3480 PROC : db2syscs.exe
      INSTANCE: DB2 NODE : 000 DB : TOOLSDB
      APPHDL : 0-28348 APPID: *LOCAL.DB2.130327132149
      AUTHID : DB2SERV0
      EDUID : 3480 EDUNAME: db2agent (TOOLSDB) 0
      FUNCTION: DB2 UDB, SQO Memory Management, sqloMemLogPoolConditions, probe:30
      DATA #1 : <preformatted>
      Out of memory failure for Applications Shared Heap (application shared) on node 0.
      Requested block size : 32 bytes.
      Physical heap size : 0 bytes.
      Configured heap size : 20512768 bytes.
      Unreserved memory used by heap : 0 bytes.
      Unreserved memory left in set : 0 bytes.
      • mwandishi
        mwandishi
        47 Posts
        ACCEPTED ANSWER

        Re: SQL1042C SQLSTATE=58004 when opening a Database

        ‏2013-03-27T14:53:09Z  in response to SystemAdmin
        Hi,

        Firstly, I would advise you open a PMR.

        Secondly, in short, DB2 is trying to implicitly activate the TOOLSDB database ( 1st connection ), as part of that activation is is attempting to allocate private memory by making an OS API call ( VirtualAlloc() ) which is failing with:

        OSERR : 8 "Not enough storage is available to process this command."

        DB2 is requesting: 1048576 private mem but there is only: 65536 available.

        So at first glance it would appear there is insufficient system memory to activate this database.

        The reasons are numerous and potentially complex. It could be the OS, it could be you have over-committed the OS due to a poor configuration of the database(s), it could be that another app that coexists with DB2 is holding lots of memory open?

        The error raises more questions than it answers frankly. However, key questions and areas to look at include:

        1) What is the db2level?
        2) What is the 'bitness' of the OS & DB2 ( 32 or 64 )?
        3) How many DB2 instances are configured on this box?
        4) How many local databases under each instance?
        5) Is STMM enabled?
        6) Is INSTANCE_MEMORY & DATABASE_MEMORY set to automatic on all instances and databases?
        7) Are you using large & fixed bufferpools ( probably not for TOOLSDB but what about other DBs )?
        8) How much system memory does the box have?
        9) How much: Total / Cached / Available & Free memory is available ( Task manager - performance tab )?
        10) Any settings in boot.ini that might be reducing application memory?

        Generally speaking and all things being equal, these sorts of errors can be avoided by ensuring:

        1) DB2 is at the latest level.
        2) STMM is enabled and most if not all memory related params are AUTOMATIC
        3) 64 bit DB2 & WIn is used.

        Best regards,
        Stephen Levett.
        • SystemAdmin
          SystemAdmin
          17917 Posts
          ACCEPTED ANSWER

          Re: SQL1042C SQLSTATE=58004 when opening a Database

          ‏2013-03-27T15:29:59Z  in response to mwandishi
          Thanks Stephen,

          The response of your requests are:

          1) What is the db2level?
          "DB2 v9.7.301.326" Fix pack a3
          2) What is the 'bitness' of the OS & DB2 ( 32 or 64 )?
          32
          3) How many DB2 instances are configured on this box?
          1
          4) How many local databases under each instance?
          12
          5) Is STMM enabled?

          Yes, here is some data of its log:

          2013-03-27-10.21.46.151000-240 I9096H644 LEVEL: Warning
          PID : 1976 TID : 5920 PROC : db2syscs.exe
          INSTANCE: DB2 NODE : 000 DB : FORUM
          APPHDL : 0-1820 APPID: *LOCAL.DB2.130321233700
          AUTHID : LCUSER
          EDUID : 5920 EDUNAME: db2stmm (FORUM) 0
          FUNCTION: DB2 UDB, Self tuning memory manager, stmmCheckIfFreeMemoryIsEnoughForSizeIncr, probe:667
          MESSAGE : ZRC=0xFFFFEC49=-5047
          DATA #1 : String, 138 bytes
          There is not enough free memory for size increase. Free memory in pages: Physical memory = 0, Instance memory = 0, Database memory = 66032

          2013-03-27-10.21.46.151000-240 I9742H645 LEVEL: Error
          PID : 1976 TID : 5920 PROC : db2syscs.exe
          INSTANCE: DB2 NODE : 000 DB : FORUM
          APPHDL : 0-1820 APPID: *LOCAL.DB2.130321233700
          AUTHID : LCUSER
          EDUID : 5920 EDUNAME: db2stmm (FORUM) 0
          FUNCTION: DB2 UDB, Self tuning memory manager, stmmUpdateDBConfig, probe:275
          MESSAGE : ZRC=0x82AE00A7=-2102525785=STMM_CONFIG_UPDATE_FAILED
          "An attempted configuration update failed"
          DATA #1 : String, 78 bytes
          Error updating parameter Database_memory, updateValue = 74500, sqlcode = -5047

          6) Is INSTANCE_MEMORY & DATABASE_MEMORY set to automatic on all instances and databases?
          Yes:
          (INSTANCE_MEMORY) = AUTOMATIC(838861)
          (DATABASE_MEMORY) = AUTOMATIC(79056) of TOOLSDB Database and Automatic of the others.

          7) Are you using large & fixed bufferpools ( probably not for TOOLSDB but what about other DBs )?
          I don't know where to find the right information

          8) How much system memory does the box have?
          4 GB

          9) How much: Total / Cached / Available & Free memory is available ( Task manager - performance tab )?
          Total: 4 193 956
          Available: 1 699 788
          System Cache: 1 617 728

          10) Any settings in boot.ini that might be reducing application memory?
          Nothing added on the boot.ini

          Thanks for your help
          • nivanov1
            nivanov1
            231 Posts
            ACCEPTED ANSWER

            Re: SQL1042C SQLSTATE=58004 when opening a Database

            ‏2013-03-27T17:20:50Z  in response to SystemAdmin
            > (INSTANCE_MEMORY) = AUTOMATIC(838861)
            > ...
            > Nothing added on the boot.ini

            This means you're telling DB2 to use over 3 GiB of memory (838861 x 4KiB) on a system with only 2 GiB available (no /3GB switch in boot.ini). In this situation I would try setting INSTANCE_MEMORY to a fixed value that is less than 2 GiB minus whatever may be needed for other applications running on the server. STMM will then balance the available memory between the databases.

            It may not solve the memory problems though - running 12 databases on a 32-bit Windows system will push the memory limits in any case.
            • SystemAdmin
              SystemAdmin
              17917 Posts
              ACCEPTED ANSWER

              Re: SQL1042C SQLSTATE=58004 when opening a Database

              ‏2013-03-27T17:45:00Z  in response to nivanov1
              Thanks nivanov1,
              I find correct your suggesting, i did it; i update the INSTANCE_MEMORY to 400 000 : less than 2go because ithe available memory is 1667 668
              But it still give the same ERROR MSG
  • smyrnafl
    smyrnafl
    6 Posts
    ACCEPTED ANSWER

    Re: SQL1042C SQLSTATE=58004 when opening a Database

    ‏2013-03-27T15:34:39Z  in response to SystemAdmin
    Give this one a try:
    1. turn off stmm
    2. increase value in DATABASE_MEMORY
    • SystemAdmin
      SystemAdmin
      17917 Posts
      ACCEPTED ANSWER

      Re: SQL1042C SQLSTATE=58004 when opening a Database

      ‏2013-03-27T17:29:49Z  in response to smyrnafl
      Thanks ,
      I increase the INSTANCE_MEMORY to 1000 000 : the max i think: i can not access to TOOLSDB Database.
      I did not change the Database memory yes because of the msg in the STMM log notifing only some databases but not the TOOLSDB
      I do not know why ?
      What do you suggest ?
      • DB2MUS
        DB2MUS
        1 Post
        ACCEPTED ANSWER

        Re: SQL1042C SQLSTATE=58004 when opening a Database

        ‏2013-04-22T20:13:02Z  in response to SystemAdmin

        Hi,

        I found something to my problem, i have two choices:

        1-create many instance as a database

        2-upgrade the OS to 64 bits so as it can manage up to 1GO memory of DB2 (max of 32 bits= 1GO)

        thanks for your searches and your efforts