Sometimes we get Abnormal End of unit in coldfusion when calling some db2 procedures. we are running DB2 9.7. when we restart server it works fine for few days. there seems to lot of file locks.Thanks for your suggestion in advance.

Is there a specific DB2 error you are getting?

Andy

No specific error, we just have frequent locks that effect the entire host application.

This is our current configuration, do you have any recomendations?

We are on LUW 9.7 Workgroup version 64bit Quad core with 32gb ram.

UPDATE DATABASE CONFIGURATION FOR KADEVCP USING APPLHEAPSZ 16384 ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING CATALOGCACHE_SZ 1024 AUTOMATIC;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING CHNGPGS_THRESH 80 ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING DBHEAP 7648 ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING DFT_DEGREE 1 ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING DFT_EXTENT_SZ 32 ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING DFT_PREFETCH_SZ AUTOMATIC;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING LOCKLIST 235744 AUTOMATIC;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING LOGBUFSZ 256 ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING LOGFILSIZ 0 ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING LOGSECOND 6 ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING MAXAPPLS 267 AUTOMATIC MAXLOCKS 98 AUTOMATIC;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING MINCOMMIT 1 ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING NUM_IOCLEANERS 3 AUTOMATIC;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING NUM_IOSERVERS 3 AUTOMATIC;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING PCKCACHESZ 8192 AUTOMATIC;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING SOFTMAX 960 ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING LOGPRIMARY 250 ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING SORTHEAP 269065 AUTOMATIC;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING STMTHEAP 4096 AUTOMATIC;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING STAT_HEAP_SZ 4384 ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING UTIL_HEAP_SZ 343740 ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING SELF_TUNING_MEM ON ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING AUTO_MAINT ON ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING AUTO_DB_BACKUP OFF ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING AUTO_TBL_MAINT ON ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING AUTO_RUNSTATS ON ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING AUTO_STATS_PROF OFF ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING AUTO_REORG ON ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING AUTO_PROF_UPD OFF ;
#UPDATE DATABASE CONFIGURATION FOR KADEVCP USING SHEAPTHRES_SHR 1345329 AUTOMATIC;
#UPDATE DATABASE MANAGER CONFIGURATION USING AGENT_STACK_SZ 16 ;
#UPDATE DATABASE MANAGER CONFIGURATION USING ASLHEAPSZ 15 ;
#UPDATE DATABASE MANAGER CONFIGURATION USING FCM_NUM_BUFFERS 1024 AUTOMATIC;
#UPDATE DATABASE MANAGER CONFIGURATION USING INTRA_PARALLEL OFF ;
#UPDATE DATABASE MANAGER CONFIGURATION USING MAX_QUERYDEGREE 2 ;
#UPDATE DATABASE MANAGER CONFIGURATION USING NUM_POOLAGENTS 100 AUTOMATIC;
#UPDATE DATABASE MANAGER CONFIGURATION USING NUM_INITAGENTS 0 ;
#UPDATE DATABASE MANAGER CONFIGURATION USING RQRIOBLK 32767 ;
#UPDATE DATABASE MANAGER CONFIGURATION USING SHEAPTHRES 0 ;
#CONNECT TO KADEVCP;
ALTER BUFFERPOOL BP32K0001 SIZE 18541 ;
#ALTER BUFFERPOOL IBMDEFAULTBP SIZE 148569 ;
#ALTER BUFFERPOOL QP SIZE 292874 ;
#ALTER BUFFERPOOL TASK0000 SIZE 148569 ;
#ALTER BUFFERPOOL TEMP0000 SIZE 292874 ;
#SET CURRENT QUERY OPTIMIZATION = 5;

Locks are usually an application issue. If the locks are being held too long then the application needs to commit more often to release the locks.

Andy

P1: BEGIN
DECLARE SQLSTATE CHAR(5) default '???';

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET P_MESSAGE = 'ERR:' || SQLSTATE || '-' || P_MESSAGE ;
ROLLBACK TO SAVEPOINT SAVEPOINT1;
END ;
SAVEPOINT SAVEPOINT1 ON ROLLBACK RETAIN CURSORS;

insert into admin.table1 ()...
insert into admin.table2 () ..
Update table3 ....
-- many statements like this

commit;
END P1

most of our procedures are like above , does creating SAVEPOINT create locks . I am commiting at end

or should i change procedures to (no save point)

P1: BEGIN
DECLARE SQLSTATE CHAR(5) default '???';

DECLARE EXIT HANDLER FOR SQLEXCEPTION
BEGIN
SET P_MESSAGE = 'ERR:' || SQLSTATE || '-' || P_MESSAGE ;
ROLLBACK ;
END ;

insert into admin.table1 ()...
insert into admin.table2 () ..
Update table3 ....
-- many statements like this

commit;
END P1

The locks are created during the INSERT and UPDATE statements. What is the "problem" you are having with the locks?

Andy

you are saying after a server start, everything runs fine, then after a few days you are receiving 'a lot of locks'.
does a servier start also mean reorgs have been accomplished?
what kind of reorg frequency do you have.

repeating other's question: Are you having Timeouts?, deadlocks, or just slow response?

what kind of volume is involved with the INSERTs and UPDATEs?
you indicated 'many statements like this - what does that mean? again, volume.

also, repeating an oft-mentioned question: are the order of INSERT's/UPDATE's to the same tables in the same order in different procedures?

We have automatic maintaince set on and reorgs is done every night. We dont reorgs manually after restart.

We had deadlock this morning, in general response is slow and sometimes we get timeouts too.

The amount of data is not very large may be total 5000 inserts and 1000 updates at max in single procedure for around 10 tables . and there may be around 30 users atmost in application at sametime working on different sets of data.
records counts in our largest table is 6463721, next one 1793494 . We have created indexes on these tables

The order of insert and updates is different in different procedures and there are only few common tables in these procedures.

One of my concern was if adding savepoint effect the performance.

I would spend time insuring that when procedure-1 inserts to tables 1, 2, 3, 4, and updates 5,6,7
and procedure -2 inserts to tables 1,2,3,4 and updates 5,6,7
both procedures update the tables in the same order- deadlocks are a result of poor application design.

can not advise on the savepoint issue.