Hi everyone,

Today I noticed a bunch of db2sysc processing hanging around on our db2 server.. not sure what has caused this but recently I've been running a sproc with various date ranges to update some data in our database -- it commits the data every 1000 records.. do you think this sproc could be causing the problem? Is there any way I can see what program, etc is mapped to the db2sys processes that are hanging? Any help will be appreciated..thanks!


CREATE PROCEDURE NRG1.UpdateReadingsMeterID ( IN fromMeterID INTEGER, IN toMeterID INTEGER, IN fromTs TIMESTAMP, IN toTs TIMESTAMP)
RESULT SETS 0
MODIFIES SQL DATA
NOT DETERMINISTIC
LANGUAGE SQL

BEGIN
DECLARE errorCode INTEGER DEFAULT 1;
DECLARE SQLCODE INTEGER DEFAULT 0;
DECLARE counter INTEGER;
DECLARE currentTs TIMESTAMP;

DECLARE CONTINUE HANDLER FOR SQLEXCEPTION
SET errorCode = SQLCODE;

SET counter = 0;
SET currentTs = fromTs;

WHILE (currentTs <= toTs) DO

UPDATE readings SET meterID = toMeterID WHERE meterid = fromMeterID AND scenarioid = 0 AND readingdatetime = currentTs;
SET currentTs = currentTs + 5 MINUTES;
SET counter = counter + 1;
IF counter = 1000 THEN
COMMIT WORK;
SET counter = 0;
END IF;

END WHILE;
END
@

db2sysc is the database manager process. You will have one such process per instance per partition.

thanks for the reply...am just trying to understand what is causing this...in the past it was because there were a few attempts to connect to a sproc that was hanging and that left the processes hanging around...am pretty sure that all of the sprocs are working just fine at the moment..

Like I said, these are the database manager processes. They have nothing to do stored procedures, whether you "connect to them" or not.

Do they show up as defunct/zombie in the ps output? What is your DB2 version and OS? Can you please include ps and ipcs output for the instance id?

Thanks for the response bella...we have DB2 v8.1.2.136", "s070720", "MI00189", and FixPak "15" on a Debian Linux OS.

Here are some entries I get when I run ps ax --


26548 ? Sl 0:09 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,1089fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,34838143
26579 ? Sl 0:09 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,10a9fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,34848144
26599 ? Sl 0:10 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,10c9fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,34858146
26612 ? Sl 0:10 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,10e9fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,34870149
26617 ? Sl 0:08 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,1109fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,3487814a
26631 ? Sl 0:06 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,1129fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,3489014d
26633 ? Sl 0:05 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,1149fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,3489814e
26636 ? Sl 0:05 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,1169fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,348a8150
26643 ? Sl 0:06 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,1189fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,348b8152
26657 ? Sl 0:08 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,11a9fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,348d8156
26659 ? Sl 0:11 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,11c9fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,348e0157
26661 ? Sl 0:16 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,11e9fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,348e8158
26664 ? Sl 0:22 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,1209fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,348f815a
26667 ? Sl 0:29 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,1229fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,3490815c
26754 ? Sl 0:40 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,1249fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,3492015f
26756 ? Sl 1:30 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,1269fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,34928160
26759 ? Sl 1:41 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,1289fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,34938162
26762 ? Sl 2:16 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,12a9fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,34948164
30428 ? Sl 0:03 db2fmp ( ,1,0,0,0,0,0,0,1e014,2,0,1,12c9fe0,0x12938000,0x12 938000,eaa4000,4f140006,2,34a58173
10157 ? S 20:55 db2agent (idle)
3432 ? S 11:54 db2agent (dbase)
29333 ? S 16:38 db2agent (idle)
16836 ? S 13:04 db2agent (dbase)
12189 ? S 12:51 db2agent (idle)
12204 ? S 10:19 db2agent (dbase)
2966 ? S 11:00 db2agent (dbase)
17122 ? S 10:27 db2agent (dbase)
17125 ? S 9:47 db2agent (dbase)
26740 ? S 10:21 db2agent (idle)
27375 ? S 3:37 db2agent (dbase)
27377 ? S 7:11 db2agent (idle)
12039 ? S 10:45 db2agent (idle)
9526 ? S 5:47 db2agent (dbase)
7889 ? S 7:16 db2agent (dbase)
17632 ? S 0:14 db2agent (dbase)
28100 ? S 6:48 db2agent (dbase)
3166 ? S 0:20 db2agent (dbase)
28545 ? S 4:42 db2agent (dbase)
12070 ? S 5:01 db2agent (idle)
12078 ? S 7:04 db2agent (idle)
12086 ? S 4:57 db2agent (dbase)
13668 ? S 6:31 db2agent (dbase)
13671 ? S 5:16 db2agent (dbase)
13672 ? S 2:33 db2agent (dbase)
13673 ? S 9:09 db2agent (dbase)
13674 ? S 4:10 db2agent (dbase)
1845 ? S 4:48 db2agent (idle)
20429 ? S 1:32 db2agent (dbase)
28285 ? S 3:36 db2agent (idle)
2854 ? S 3:23 db2agent (dbase)
20813 ? S 5:13 db2agent (idle)
20814 ? S 3:14 db2agent (dbase)
20815 ? S 1:34 db2agent (idle)
20820 ? S 2:32 db2agent (dbase)
20822 ? S 7:15 db2agent (dbase)
20823 ? S 3:08 db2agent (dbase)
20825 ? S 0:46 db2agent (dbase)
20828 ? S 3:17 db2agent (dbase)
20831 ? S 2:11 db2agent (dbase)
20832 ? S 3:59 db2agent (idle)
20833 ? S 1:19 db2agent (dbase)
20844 ? S 3:37 db2agent (idle)
20847 ? S 2:56 db2agent (dbase)
20850 ? S 3:06 db2agent (dbase)
20853 ? S 0:43 db2agent (dbase)
20858 ? S 5:13 db2agent (dbase)
20860 ? S 3:53 db2agent (dbase)
20865 ? S 2:47 db2agent (dbase)
28293 ? S 1:50 db2agent (dbase)
28294 ? S 1:49 db2agent (idle)
28297 ? S 1:25 db2agent (idle)
28298 ? S 2:32 db2agent (idle)
26581 ? S 2:02 db2agent (dbase)
26582 ? S 2:02 db2agent (idle)
26583 ? S 3:33 db2agent (dbase)
17120 ? S 2:23 db2agent (dbase)
17121 ? S 1:46 db2agent (idle)
17124 ? S 1:42 db2agent (idle)
2148 ? S 0:00 /usr/sbin/apache
2149 ? S 0:00 /usr/sbin/apache
2150 ? S 0:00 /usr/sbin/apache
2151 ? S 0:00 /usr/sbin/apache
2152 ? S 0:00 /usr/sbin/apache
15168 ? S 1:48 db2agent (idle)
15169 ? S 1:30 db2agent (dbase)
15170 ? S 1:36 db2agent (dbase)
11865 ? S 0:49 db2agent (idle)
11866 ? S 0:44 db2agent (dbase)
11867 ? S 0:41 db2agent (dbase)
11868 ? S 0:43 db2agent (idle)
11869 ? S 0:40 db2agent (dbase)
11870 ? S 0:45 db2agent (dbase)
11871 ? S 0:48 db2agent (idle)
11872 ? S 0:48 db2agent (dbase)
10193 ? S 0:28 db2agent (dbase)
1406 ? S 0:15 db2agent (dbase)
1407 ? S 0:20 db2agent (dbase)
4321 ? S 0:11 db2agent (dbase)
4414 ? S 0:15 db2agent (dbase)
17033 ? S 0:07 db2agent (dbase)
19154 ? S 0:15 db2agent (idle)
19557 ? S 0:09 db2agent (dbase)
21746 ? S 0:08 db2agent (idle)
21747 ? S 0:10 db2agent (dbase)
21748 ? S 0:11 db2agent (dbase)
21749 ? S 0:08 db2agent (idle)

Some more output --

23982 ? S 0:00 db2sysc
23983 ? S 7:31 db2ckpwd
23984 ? S 7:33 db2ckpwd
23985 ? S 7:33 db2ckpwd
23986 ? S 0:00 db2pmd
23987 ? S 0:00 db2gds
23988 ? S 0:07 db2ipccm
23989 ? S 2:45 db2tcpcm
23990 ? S 2:44 db2tcpcm
23992 ? S 5:46 db2logts (dbase)
23993 ? S 0:34 db2logmgr (dbase)
23994 ? S 0:00 db2loggr (dbase)
23995 ? S 78:44 db2loggw (dbase)
23996 ? S 0:01 db2lfr (dbase)
23997 ? S 0:00 db2dlock (dbase)
23998 ? S 27:24 db2pfchr
23999 ? S 24:27 db2pfchr
24000 ? S 21:00 db2pfchr
24001 ? S 17:35 db2pfchr
24002 ? S 16:09 db2pfchr
24003 ? S 13:48 db2pfchr
24004 ? S 3:03 db2pfchr
24005 ? S 1:28 db2pfchr
24006 ? S 1:03 db2pfchr
24007 ? S 0:32 db2pfchr
24008 ? S 0:31 db2pclnr
24009 ? S 0:28 db2pclnr
24010 ? S 0:29 db2pclnr
24011 ? S 0:29 db2pclnr
24012 ? S 0:28 db2pclnr
24020 ? S 0:00 db2event (DB2DETAILDEADLOCK)
24030 ? S 0:00 db2resync
24031 ? S 0:00 db2srvlst
24033 ? Sl 41:04 db2hmon ,0,0,0,1,0,0,0,1e014,2,0,1,9fe0,0x12938000,0x12938 000,eaa4000,4f140006,2,33f50039
24040 ? Sl 0:00 db2fmp ( ,0,0,0,0,0,0,0,1e014,2,0,1,29fe0,0x12938000,0x1293 8000,eaa4000,4f140006,2,33fa0040

I only see one db2sysc in the output ( 23982 ? S 0:00 db2sysc ). Where are the others?

If i run top, then I get these --

20847 db2inst1 15 0 812m 122m 112m S 4 1.5 3:03.12 db2sysc
21746 db2inst1 15 0 811m 93m 84m S 4 1.2 0:12.40 db2sysc
11868 db2inst1 15 0 810m 109m 101m S 1 1.4 0:49.64 db2sysc
20833 db2inst1 15 0 809m 96m 87m S 1 1.2 1:24.20 db2sysc
15169 db2inst1 15 0 812m 110m 97m S 1 1.4 1:34.31 db2sysc
23995 db2inst1 15 0 803m 18m 11m S 0 0.2 79:01.40 db2sysc
26417 db2inst1 15 0 273m 7232 4200 S 0 0.1 0:37.01 db2fmp
17122 db2inst1 15 0 827m 90m 73m S 0 1.1 10:32.57 db2sysc

Some of these have been hanging around for a few days now..

I'm not familiar with top, but it looks like this command is displaying all DB2 processes (possibly with the exception of db2fmp) as db2sysc instead of their real names. I usually see this on Solaris when using the ps command.

So, there is nothing wrong - you only have one db2sysc.