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

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

    Re: SQL1042C SQLSTATE=58004 when opening a Database

    ‏2013-03-26T20:32:08Z  
    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

    Re: SQL1042C SQLSTATE=58004 when opening a Database

    ‏2013-03-27T13:24:54Z  
    • nivanov1
    • ‏2013-03-26T20:32:08Z
    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.
    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

    Re: SQL1042C SQLSTATE=58004 when opening a Database

    ‏2013-03-27T14:53:09Z  
    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.
    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

    Re: SQL1042C SQLSTATE=58004 when opening a Database

    ‏2013-03-27T15:29:59Z  
    • mwandishi
    • ‏2013-03-27T14:53:09Z
    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.
    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
  • smyrnafl
    smyrnafl
    6 Posts

    Re: SQL1042C SQLSTATE=58004 when opening a Database

    ‏2013-03-27T15:34:39Z  
    Give this one a try:
    1. turn off stmm
    2. increase value in DATABASE_MEMORY
  • nivanov1
    nivanov1
    231 Posts

    Re: SQL1042C SQLSTATE=58004 when opening a Database

    ‏2013-03-27T17:20:50Z  
    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
    > (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

    Re: SQL1042C SQLSTATE=58004 when opening a Database

    ‏2013-03-27T17:29:49Z  
    • smyrnafl
    • ‏2013-03-27T15:34:39Z
    Give this one a try:
    1. turn off stmm
    2. increase value in DATABASE_MEMORY
    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 ?
  • SystemAdmin
    SystemAdmin
    17917 Posts

    Re: SQL1042C SQLSTATE=58004 when opening a Database

    ‏2013-03-27T17:45:00Z  
    • nivanov1
    • ‏2013-03-27T17:20:50Z
    > (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.
    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
  • DB2MUS
    DB2MUS
    3 Posts

    Re: SQL1042C SQLSTATE=58004 when opening a Database

    ‏2013-04-22T20:13:02Z  
    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 ?

    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