Too many open files error on db2diag.log

[Sam Durai]

Whenever datastage (ETL Tool) tries to establish more than 2
connection to database it fails with the error SQL1224N A database
agent could not be started to service a request, or was terminated as
a result of a database system shutdown or a force command.
SQLSTATE=55032

Environment:
AIX 5.3
DB2 UDB V8.1 FP 14
It has been less than a week since a fresh DB2 Installation
Instance type : Server, 64 Bit

Following error appears on db2diag.log
2007-02-27-03.06.39.415586+300 E11838A428 LEVEL: Warning (OS)
PID : 331976 TID : 1 PROC : db2spmrsy 0
INSTANCE: cardinst NODE : 000
APPHDL : 0-6
FUNCTION: DB2 UDB, oper system services, sqloopenp, probe:80
CALLED : OS, -, unspecified_system_function
OSERR : EEXIST (17) "Do not specify an existing file."
DATA #1 : File name, 39 bytes
/home/cardinst/sqllib/spmlog/SPMLOG.LCF

2007-02-27-03.06.39.449626+300 E12267A1022 LEVEL: Event
PID : 405722 TID : 1 PROC : db2star2
INSTANCE: cardinst NODE : 000
FUNCTION: DB2 UDB, base sys utilities, DB2StartMain, probe:911
MESSAGE : ADM7513W Database manager has started.
START : DB2 DBM
DATA #1 : Build Level, 144 bytes
Instance "cardinst" uses "64" bits and DB2 code release "SQL08027"
with level identifier "03080106".
Informational tokens are "DB2 v8.1.1.128", "s061108", "U810098",
FixPak "14".
DATA #2 : System Info, 224 bytes
System: AIX grf2 3 5 00CB874E4C00
CPU: total:4 online:4 Threading degree per core:2
Physical Memory(MB): total:3824 free:254
Virtual Memory(MB): total:8304 free:4725
Swap Memory(MB): total:4480 free:4471
Kernel Params: msgMaxMessageSize:4194304 msgMaxQueueSize:4194304
shmMax:68719476736 shmMin:1 shmIDs:131072
shmSegments:68719476736 semIDs:131072 semNumPerID:
65535
semOps:1024 semMaxVal:32767 semAdjustOnExit:16384
2007-02-27-03.07.31.061729+300 I13290C499 LEVEL: Severe (OS)
PID : 463092 TID : 1 PROC : osh
INSTANCE: cardinst NODE : 000
FUNCTION: DB2 UDB, SQO Memory Management, sqlocshr2, probe:200
CALLED : OS, -, shmat
OSERR : EMFILE (24) "Too many open files"
DATA #1 : Memory set handle, PD_TYPE_OSS_MEM_SET_HDL, 20 bytes
0x2FF1BAE0 : FFFF FFFF FFFF FFFF 0000 0000 0080
0017 ................
0x2FF1BAF0 : 0004 0000 ....
I've set DB2ENVLIST to EXTSHM but still the problem persists.
Any suggestions ?
Sam.

[Liam Finnie]

On Feb 26, 11:14 pm, "Sam Durai" <[e-mail]:

Whenever datastage (ETL Tool) tries to establish more than 2
connection to database it fails with the error SQL1224N A database
agent could not be started to service a request, or was terminated as
a result of a database system shutdown or a force command.
SQLSTATE=55032

Environment:
AIX 5.3
DB2 UDB V8.1 FP 14
It has been less than a week since a fresh DB2 Installation
Instance type : Server, 64 Bit

Following error appears on db2diag.log
2007-02-27-03.06.39.415586+300 E11838A428 LEVEL: Warning (OS)
PID : 331976 TID : 1 PROC : db2spmrsy 0
INSTANCE: cardinst NODE : 000
APPHDL : 0-6
FUNCTION: DB2 UDB, oper system services, sqloopenp, probe:80
CALLED : OS, -, unspecified_system_function
OSERR : EEXIST (17) "Do not specify an existing file."
DATA #1 : File name, 39 bytes
/home/cardinst/sqllib/spmlog/SPMLOG.LCF

2007-02-27-03.06.39.449626+300 E12267A1022 LEVEL: Event
PID : 405722 TID : 1 PROC : db2star2
INSTANCE: cardinst NODE : 000
FUNCTION: DB2 UDB, base sys utilities, DB2StartMain, probe:911
MESSAGE : ADM7513W Database manager has started.
START : DB2 DBM
DATA #1 : Build Level, 144 bytes
Instance "cardinst" uses "64" bits and DB2 code release "SQL08027"
with level identifier "03080106".
Informational tokens are "DB2 v8.1.1.128", "s061108", "U810098",
FixPak "14".
DATA #2 : System Info, 224 bytes
System: AIX grf2 3 5 00CB874E4C00
CPU: total:4 online:4 Threading degree per core:2
Physical Memory(MB): total:3824 free:254
Virtual Memory(MB): total:8304 free:4725
Swap Memory(MB): total:4480 free:4471
Kernel Params: msgMaxMessageSize:4194304 msgMaxQueueSize:4194304
shmMax:68719476736 shmMin:1 shmIDs:131072
shmSegments:68719476736 semIDs:131072 semNumPerID:
65535
semOps:1024 semMaxVal:32767 semAdjustOnExit:16384
2007-02-27-03.07.31.061729+300 I13290C499 LEVEL: Severe (OS)
PID : 463092 TID : 1 PROC : osh
INSTANCE: cardinst NODE : 000
FUNCTION: DB2 UDB, SQO Memory Management, sqlocshr2, probe:200
CALLED : OS, -, shmat
OSERR : EMFILE (24) "Too many open files"
DATA #1 : Memory set handle, PD_TYPE_OSS_MEM_SET_HDL, 20 bytes
0x2FF1BAE0 : FFFF FFFF FFFF FFFF 0000 0000 0080
0017 ................
0x2FF1BAF0 : 0004 0000 ....

I've set DB2ENVLIST to EXTSHM but still the problem persists.

Any suggestions ?
Sam.

Hi Sam,

Have you verified that EXTSHM=ON is exported in your environment at
the time that the osh process is started (I assume this is a tool
provided with ETL?). You don't need to add EXTSHM to DB2ENVLIST,
since it's the client that is hitting a problem, not the server (the
server is 64-bit, so is immune from this problem). If the osh tool is
available in a 64-bit mode, that should workaround this problem as
well.

If you're still having trouble with this, you might need to configure
a TCP/IP loopback - your client connections will then use TCP/IP for
communication with the server, instead of shared memory. Here's a
link to an (old) article that describes EXTSHM and TCP/IP loopbacks in
more detail:
http://www-1.ibm.com/support/docview...=utf-8&lang=en

Cheers,
Liam.

[Sam Durai]

On Feb 27, 10:10 am, "Liam Finnie" <[e-mail]:

On Feb 26, 11:14 pm, "Sam Durai" <[e-mail]:

Whenever datastage (ETL Tool) tries to establish more than 2
connection to database it fails with the error SQL1224N A database
agent could not be started to service a request, or was terminated as
a result of a database system shutdown or a force command.
SQLSTATE=55032

Environment:
AIX 5.3
DB2 UDB V8.1 FP 14
It has been less than a week since a fresh DB2 Installation
Instance type : Server, 64 Bit

Following error appears on db2diag.log
2007-02-27-03.06.39.415586+300 E11838A428 LEVEL: Warning (OS)
PID : 331976 TID : 1 PROC : db2spmrsy 0
INSTANCE: cardinst NODE : 000
APPHDL : 0-6
FUNCTION: DB2 UDB, oper system services, sqloopenp, probe:80
CALLED : OS, -, unspecified_system_function
OSERR : EEXIST (17) "Do not specify an existing file."
DATA #1 : File name, 39 bytes
/home/cardinst/sqllib/spmlog/SPMLOG.LCF

2007-02-27-03.06.39.449626+300 E12267A1022 LEVEL: Event
PID : 405722 TID : 1 PROC : db2star2
INSTANCE: cardinst NODE : 000
FUNCTION: DB2 UDB, base sys utilities, DB2StartMain, probe:911
MESSAGE : ADM7513W Database manager has started.
START : DB2 DBM
DATA #1 : Build Level, 144 bytes
Instance "cardinst" uses "64" bits and DB2 code release "SQL08027"
with level identifier "03080106".
Informational tokens are "DB2 v8.1.1.128", "s061108", "U810098",
FixPak "14".
DATA #2 : System Info, 224 bytes
System: AIX grf2 3 5 00CB874E4C00
CPU: total:4 online:4 Threading degree per core:2
Physical Memory(MB): total:3824 free:254
Virtual Memory(MB): total:8304 free:4725
Swap Memory(MB): total:4480 free:4471
Kernel Params: msgMaxMessageSize:4194304 msgMaxQueueSize:4194304
shmMax:68719476736 shmMin:1 shmIDs:131072
shmSegments:68719476736 semIDs:131072 semNumPerID:
65535
semOps:1024 semMaxVal:32767 semAdjustOnExit:16384
2007-02-27-03.07.31.061729+300 I13290C499 LEVEL: Severe (OS)
PID : 463092 TID : 1 PROC : osh
INSTANCE: cardinst NODE : 000
FUNCTION: DB2 UDB, SQO Memory Management, sqlocshr2, probe:200
CALLED : OS, -, shmat
OSERR : EMFILE (24) "Too many open files"
DATA #1 : Memory set handle, PD_TYPE_OSS_MEM_SET_HDL, 20 bytes
0x2FF1BAE0 : FFFF FFFF FFFF FFFF 0000 0000 0080
0017 ................
0x2FF1BAF0 : 0004 0000 ....

I've set DB2ENVLIST to EXTSHM but still the problem persists.

Any suggestions ?
Sam.

Hi Sam,

Have you verified that EXTSHM=ON is exported in your environment at
the time that the osh process is started (I assume this is a tool
provided with ETL?). You don't need to add EXTSHM to DB2ENVLIST,
since it's the client that is hitting a problem, not the server (the
server is 64-bit, so is immune from this problem). If the osh tool is
available in a 64-bit mode, that should workaround this problem as
well.

If you're still having trouble with this, you might need to configure
a TCP/IP loopback - your client connections will then use TCP/IP for
communication with the server, instead of shared memory. Here's a
link to an (old) article that describes EXTSHM and TCP/IP loopbacks in
more detail:http://www-1.ibm.com/support/docview...SSEPGG&q1=EXTS...

Cheers,
Liam.

Establishing a loopback connection solved the problem. Thanks for the
inputs.