Prev: What is the fastest way...
Next: DB2 load with error SQL2044N Reason Code "3"
From: Sam Durai on 26 Feb 2007 23:14
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.

From: Liam Finnie on 27 Feb 2007 10:10
On Feb 26, 11:14 pm, "Sam Durai" <reachsamdu...(a)gmail.com> wrote:
> 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.wss?rs=71&context=SSEPGG&q1=EXTSHM+DB2&uid=swg21009742&loc=en_US&cs=utf-8&lang=en

Cheers,
Liam.

From: Sam Durai on 27 Feb 2007 12:50
On Feb 27, 10:10 am, "Liam Finnie" <lfin...(a)ca.ibm.com> wrote:
> On Feb 26, 11:14 pm, "Sam Durai" <reachsamdu...(a)gmail.com> wrote:
>
>
>
> > 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.wss?rs=71&context=SSEPGG&q1=EXTS...
>
> Cheers,
> Liam.

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

 | 
Pages: 1
Prev: What is the fastest way...
Next: DB2 load with error SQL2044N Reason Code "3"