DB2 8.1.5 SQL1224N Error

[Udy]

Hi,

We have a custom application used by a customer on DB2 8.1.5 AIX 5.1,
we have tested it with 8.1.0 AIX 5.1 and everything works fine. But
with 8.1.5 we get the above error. The other applications connecting
to other instances of the database works fine.

lsattr -E -l sys0 | grep maxuproc gave the output
maxuproc 4096

ps -ef | grep <instance Name> | wc -l
returned 665

Also have checked on the extended shared memory to be used, in fact
the customer is using the TCP/IP, and this works, if the application
connects from a Windows environment. It seems to be a problem from
the AIX configuration.

The server side code of the application is written in C++ and the UI
is Servlet pages powered by IBM WebSphere 5.2. Any inputs on this
will be greatly appreciated.

Thanks and regards, Udaya.

FYI:
the db2 get db config output is

Database Configuration for Database

Database configuration release level = 0x0a00
Database release level = 0x0a00

Database territory = US
Database code page = 819
Database code set = ISO8859-1
Database country/region code = 1

Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

Discovery support for this database (DISCOVER_DB) = ENABLE

Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = ANY
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) =
99999999999999
Number of frequent values retained (NUM_FREQVALUES) = 20
Number of quantiles retained (NUM_QUANTILES) = 40

Backup pending = NO

Database is consistent = NO
Rollforward pending = NO
Restore pending = NO

Multi-page file allocation enabled = YES

Log retain for recovery status = NO
User exit for logging status = NO

Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0

Database heap (4KB) (DBHEAP) = 6400
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 288
Log buffer size (4KB) (LOGBUFSZ) = 256
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 38400
Buffer pool size (pages) (BUFFPAGE) = 1000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 250

Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 40000
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 2560

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) =
(SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 13333
SQL statement heap (4KB) (STMTHEAP) = 16382
Default application heap (4KB) (APPLHEAPSZ) = 5120
Package cache size (4KB) (PCKCACHESZ) =
(MAXAPPLS*8)
Statistics heap size (4KB) (STAT_HEAP_SZ) = 19973

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = 30

Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 40
Number of I/O servers (NUM_IOSERVERS) = 70
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32

Track modified pages (TRACKMOD) = OFF

Default number of containers = 4
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

Max number of active applications (MAXAPPLS) = AUTOMATIC
Average number of active applications (AVG_APPLS) = 5
Max DB files open per application (MAXFILOP) = 128

Log file size (4KB) (LOGFILSIZ) = 3000
Number of primary log files (LOGPRIMARY) = 10
Number of secondary log files (LOGSECOND) = 20
Changed path to log files (NEWLOGPATH) =
Path to log files =
/adw/tbs06/adwd3/database/dblog0/NODE0000/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file =
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent of max active log space by transaction(MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF

Auto restart enabled (AUTORESTART) = ON
Index re-creation time (INDEXREC) = SYSTEM
(RESTART)
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366

TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =

[Mark Yudkin]

What happens when you try all of the suggestions, including the AIX specific
ones such as setting EXTSHM, given in the Messages Reference for SQL1224N?

"Udy" <ud***********@gmail.com> wrote in message
news:cf**************************@posting.google.c om...

Hi,

We have a custom application used by a customer on DB2 8.1.5 AIX 5.1,
we have tested it with 8.1.0 AIX 5.1 and everything works fine. But
with 8.1.5 we get the above error. The other applications connecting
to other instances of the database works fine.

lsattr -E -l sys0 | grep maxuproc gave the output
maxuproc 4096

ps -ef | grep <instance Name> | wc -l
returned 665

Also have checked on the extended shared memory to be used, in fact
the customer is using the TCP/IP, and this works, if the application
connects from a Windows environment. It seems to be a problem from
the AIX configuration.

The server side code of the application is written in C++ and the UI
is Servlet pages powered by IBM WebSphere 5.2. Any inputs on this
will be greatly appreciated.

Thanks and regards, Udaya.

FYI:
the db2 get db config output is

Database Configuration for Database

Database configuration release level = 0x0a00
Database release level = 0x0a00

Database territory = US
Database code page = 819
Database code set = ISO8859-1
Database country/region code = 1

Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE

Discovery support for this database (DISCOVER_DB) = ENABLE

Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = ANY
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) =
99999999999999
Number of frequent values retained (NUM_FREQVALUES) = 20
Number of quantiles retained (NUM_QUANTILES) = 40

Backup pending = NO

Database is consistent = NO
Rollforward pending = NO
Restore pending = NO

Multi-page file allocation enabled = YES

Log retain for recovery status = NO
User exit for logging status = NO

Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Write Token Init Expiry Intvl(DL_WT_IEXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0

Database heap (4KB) (DBHEAP) = 6400
Size of database shared memory (4KB) (DATABASE_MEMORY) = AUTOMATIC
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 288
Log buffer size (4KB) (LOGBUFSZ) = 256
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 38400
Buffer pool size (pages) (BUFFPAGE) = 1000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 250

Max size of appl. group mem set (4KB) (APPGROUP_MEM_SZ) = 40000
Percent of mem for appl. group heap (GROUPHEAP_RATIO) = 70
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 2560

Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) =
(SHEAPTHRES)
Sort list heap (4KB) (SORTHEAP) = 13333
SQL statement heap (4KB) (STMTHEAP) = 16382
Default application heap (4KB) (APPLHEAPSZ) = 5120
Package cache size (4KB) (PCKCACHESZ) =
(MAXAPPLS*8)
Statistics heap size (4KB) (STAT_HEAP_SZ) = 19973

Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = 30

Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 40
Number of I/O servers (NUM_IOSERVERS) = 70
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32

Track modified pages (TRACKMOD) = OFF

Default number of containers = 4
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32

Max number of active applications (MAXAPPLS) = AUTOMATIC
Average number of active applications (AVG_APPLS) = 5
Max DB files open per application (MAXFILOP) = 128

Log file size (4KB) (LOGFILSIZ) = 3000
Number of primary log files (LOGPRIMARY) = 10
Number of secondary log files (LOGSECOND) = 20
Changed path to log files (NEWLOGPATH) =
Path to log files =
/adw/tbs06/adwd3/database/dblog0/NODE0000/
Overflow log path (OVERFLOWLOGPATH) =
Mirror log path (MIRRORLOGPATH) =
First active log file =
Block log on disk full (BLK_LOG_DSK_FUL) = NO
Percent of max active log space by transaction(MAX_LOG) = 0
Num. of active log files for 1 active UOW(NUM_LOG_SPAN) = 0

Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF

Auto restart enabled (AUTORESTART) = ON
Index re-creation time (INDEXREC) = SYSTEM
(RESTART)
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366

TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =

[Udy]

We've tried all the options mentioned for the SQL1224N threads.
Finally one observtation we have come across is that when the
application connects from remote system to the DB2 it works, But when
the application running on the same machine tries to access the DB2
locally, it fails, with the fact that EXTSHM settings are enabled.

Could there be something that I missed?

Can anyone elaborate on these ...

The setting EXTSHM should be used with careful consideration.
Do not use EXTSHM=ON at system level.
Do not use EXTSHM=ON at user level.
Use EXTSHM=ON on process level, for only those who need it.
Documentation reveals that EXTSHM=ON uses more CPU time and therefore
could cause damage to performance. EXTSHM=ON uses shared memory
differently than in the conventional normal case.

What does this mean? and how can I achieve it?

-Thanks you all and regards, Udaya.

"Mark Yudkin" <my***********************@nospam.org> wrote in message news:<ci**********@ngspool-d02.news.aol.com>...

What happens when you try all of the suggestions, including the AIX specific
ones such as setting EXTSHM, given in the Messages Reference for SQL1224N?

[Richard]

Check this link, I once have that problem and fixed with this
http://www-1.ibm.com/support/docview...&cc=us&lang=en


ud***********@gmail.com (Udy) wrote in message news:<cf**************************@posting.google. com>...

We've tried all the options mentioned for the SQL1224N threads.
Finally one observtation we have come across is that when the
application connects from remote system to the DB2 it works, But when
the application running on the same machine tries to access the DB2
locally, it fails, with the fact that EXTSHM settings are enabled.

Could there be something that I missed?

Can anyone elaborate on these ...

The setting EXTSHM should be used with careful consideration.
Do not use EXTSHM=ON at system level.
Do not use EXTSHM=ON at user level.
Use EXTSHM=ON on process level, for only those who need it.
Documentation reveals that EXTSHM=ON uses more CPU time and therefore
could cause damage to performance. EXTSHM=ON uses shared memory
differently than in the conventional normal case.

What does this mean? and how can I achieve it?

-Thanks you all and regards, Udaya.

"Mark Yudkin" <my***********************@nospam.org> wrote in message news:<ci**********@ngspool-d02.news.aol.com>...

What happens when you try all of the suggestions, including the AIX specific
ones such as setting EXTSHM, given in the Messages Reference for SQL1224N?

[Udy]

ri********@hotmail.com (Richard) wrote in message news:<4c**************************@posting.google. com>...

Check this link, I once have that problem and fixed with this
http://www-1.ibm.com/support/docview...&cc=us&lang=en

Thank you Ricardo, this solution helped us a lot. Cheers to you.
Have a good time. Udy.

[ÕÔÔÀ·å]

You can use follow command:

export EXTSHM=ON
db2set DB2ENVLIST=EXTSHM

then restart db2 instance.

$)ATZ 22 Sep 2004 10:57:55 -0700 J1#, ud***********@gmail.com (Udy) P4AK:

ri********@hotmail.com (Richard) wrote in message news:<4c**************************@posting.google. com>...

Check this link, I once have that problem and fixed with this
http://www-1.ibm.com/support/docview...&cc=us&lang=en

Thank you Ricardo, this solution helped us a lot. Cheers to you.
Have a good time. Udy.