Hi all,
I have a problem with running stored procedures. Whenever a java stored
procedure is called it consumes cpu after it has finished. The thread
still appears to be running.
It is using 25% of the machine (ie all of 1 cpu). See threads from ps
output below and prstat output. This is after running the stored procedure
and getting a result. The only way to terminate it is to bounce the
database.
DB2 8.1.5 on Solaris 9.
Below is the example stored procedure definition and java code as per DB2
sample.
Thanks for your time.
Regards
Gerry
Stored Proc Definition
DROP PROCEDURE PHLEE0.[Email Address Removed] PROCEDURE PHLEE0.OUT_LANGUAGE (OUT LANGUAGE CHAR(8))
DYNAMIC RESULT SETS 0
LANGUAGE JAVA
PARAMETER STYLE JAVA
NO DBINFO
FENCED
THREADSAFE
MODIFIES SQL DATA
PROGRAM TYPE SUB
EXTERNAL NAME 'SpServer1.outLanguage'@
GRANT EXECUTE ON PROCEDURE PHLEE0.OUT_LANGUAGE(CHAR()) TO [Email Address Removed] Code
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
public class SpServer1 {
public static void outLanguage(String[] outLang) throws Exception {
try {
String procName;
// Initialize variables
procName = "OUT_LANGUAGE";
// Get caller's connection to the database
Connection con =
DriverManager.getConnection("jdbc:default:connection");
String query = "SELECT language FROM syscat.procedures "
+ "WHERE procname = ? ";
PreparedStatement stmt = con.prepareStatement(query);
stmt.setString(1, procName);
ResultSet rs = stmt.executeQuery();
// move to first row of result set
rs.next();
// set value for the output parameter
outLang[0] = rs.getString(1);
// clean up resources
rs.close();
stmt.close();
con.close();
} catch (Exception e) {
throw e;
}
}
}
PS Output
jeeves db2inst1:~/gfs.241106$ ps -LfU db2fenc1
UID PID PPID LWP NLWP C STIME TTY LTIME CMD
db2fenc1 7726 28817 1 10 0 Nov 22 ? 0:01 db2fmp
0,1,1,0,0,0,0,1e010,2,0,1,8080,210000000,210000000,15fc000,8004,2,673004
db2fenc1 7726 28817 2 10 0 Nov 22 ? 0:01 db2fmp
0,1,1,0,0,0,0,1e010,2,0,1,8080,210000000,210000000,15fc000,8004,2,673004
db2fenc1 7726 28817 3 10 0 Nov 22 ? 0:00 db2fmp
0,1,1,0,0,0,0,1e010,2,0,1,8080,210000000,210000000,15fc000,8004,2,673004
db2fenc1 7726 28817 4 10 0 Nov 22 ? 0:00 db2fmp
0,1,1,0,0,0,0,1e010,2,0,1,8080,210000000,210000000,15fc000,8004,2,673004
db2fenc1 7726 28817 5 10 0 Nov 22 ? 0:02 db2fmp
0,1,1,0,0,0,0,1e010,2,0,1,8080,210000000,210000000,15fc000,8004,2,673004
db2fenc1 7726 28817 6 10 0 Nov 22 ? 0:00 db2fmp
0,1,1,0,0,0,0,1e010,2,0,1,8080,210000000,210000000,15fc000,8004,2,673004
db2fenc1 7726 28817 7 10 25 Nov 22 ? 3045:07 db2fmp
0,1,1,0,0,0,0,1e010,2,0,1,8080,210000000,210000000,15fc000,8004,2,673004
db2fenc1 7726 28817 8 10 0 Nov 22 ? 0:00 db2fmp
0,1,1,0,0,0,0,1e010,2,0,1,8080,210000000,210000000,15fc000,8004,2,673004
db2fenc1 7726 28817 9 10 0 Nov 22 ? 0:00 db2fmp
0,1,1,0,0,0,0,1e010,2,0,1,8080,210000000,210000000,15fc000,8004,2,673004
db2fenc1 7726 28817 10 10 0 Nov 22 ? 0:00 db2fmp
0,1,1,0,0,0,0,1e010,2,0,1,8080,210000000,210000000,15fc000,8004,2,673004
PRSTAT Output
jeeves db2inst1:~/gfs.241106$ prstat -a
PID USERNAME SIZE RSS STATE PRI NICE TIME CPU PROCESS/NLWP
7726 db2fenc1 137M 36M cpu3 0 0 52:42:30 25% db2fmp/10
7551 db2inst1 4672M 4621M sleep 44 0 1:27:50 2.1% db2sysc/1
28845 db2inst1 131M 79M sleep 50 0 0:25:11 0.3% db2sysc/1
11896 db2inst1 4688M 4636M sleep 59 0 0:11:51 0.1% db2sysc/1
11172 db2inst1 4672M 4621M sleep 59 0 0:23:46 0.1% db2sysc/1
3098 db2inst1 4904K 4712K cpu1 59 0 0:00:00 0.1% prstat/1
20729 db2inst1 4672M 4619M sleep 59 0 0:01:52 0.1% db2sysc/1
1883 db2inst1 4667M 4613M sleep 59 0 0:10:42 0.0% db2sysc/1
22560 db2inst1 4674M 4623M sleep 59 0 0:07:17 0.0% db2sysc/1
1889 db2inst1 4669M 4615M sleep 59 0 1:01:51 0.0% db2sysc/1
9618 oracle 747M 726M sleep 53 2 0:08:07 0.0% oracle/1
8723 root 5184K 4560K sleep 59 0 0:17:05 0.0% nscd/22
743 root 2904K 1672K sleep 59 0 0:57:03 0.0% nmbd/1
9608 oracle 744M 718M sleep 53 2 0:02:38 0.0% oracle/11
13964 db2inst1 5656K 2320K sleep 59 0 0:00:39 0.0% sshd/1
11545 db2inst1 4691M 4648M sleep 59 0 1:29:59 0.0% db2sysc/1
1389 db2inst1 4672M 4628M sleep 59 0 0:01:39 0.0% db2sysc/1
489 root 3128K 1688K sleep 59 0 0:00:00 0.0% mdmonitord/1
484 root 2976K 1552K sleep 59 0 0:00:00 0.0% rpc.metad/1
505 root 5400K 3704K sleep 59 0 0:00:03 0.0% cupsd/1
491 root 2896K 1608K sleep 59 0 0:00:00 0.0% syncsortd/1
373 root 4536K 1744K sleep 59 0 0:00:04 0.0% sendmail/1
453 root 1760K 1272K sleep 59 0 0:00:00 0.0% smcboot/1
371 smmsp 4504K 1096K sleep 59 0 0:00:00 0.0% sendmail/1
455 root 1760K 824K sleep 59 0 0:00:00 0.0% smcboot/1
9620 oracle 741M 715M sleep 53 2 0:00:00 0.0% oracle/1
351 root 1080K 752K sleep 59 0 0:00:15 0.0% utmpd/1
236 root 2400K 1504K sleep 59 0 0:00:01 0.0% rpcbind/1
356 nobody 2376K 1584K sleep 59 0 0:00:00 0.0% suntechd/1
319 root 1072K 560K sleep 59 0 0:00:00 0.0% sf880drd/1
469 root 1008K 784K sleep 59 0 0:00:00 0.0% htt/1
471 root 3256K 1608K sleep 59 0 0:00:00 0.0% htt_server/2
282 daemon 3128K 2000K sleep 59 0 0:00:00 0.0% statd/1
279 root 2216K 1408K sleep 59 0 0:00:00 0.0% lockd/2
388 root 2104K 1240K sleep 100 - 0:03:53 0.0% xntpd/1
719 root 2208K 1456K sleep 59 0 0:00:00 0.0% snmpdx/1
NPROC USERNAME SIZE RSS MEMORY TIME CPU
1 db2fenc1 137M 36M 0.0% 52:42:30 25%
98 db2inst1 247G 243G 96% 50:30:06 2.8%
57 root 821M 493M 0.2% 3:32:46 0.0%
11 oracle 7436M 7197M 2.8% 0:13:41 0.0%
4 gesch0 76M 55M 0.0% 0:00:00 0.0%
19 informix 3874M 3095M 1.2% 0:00:06 0.0%
33 applmgr 652M 201M 0.1% 0:06:20 0.0%
2 dasusr1 16M 6776K 0.0% 0:00:03 0.0%
11 applxs 69M 29M 0.0% 0:00:07 0.0%
1 smmsp 4504K 1096K 0.0% 0:00:00 0.0%
1 daemon 3128K 2000K 0.0% 0:00:00 0.0%
2 nobody 4560K 3032K 0.0% 0:00:00 0.0%
DB2 & UDB email list listserv db2-l LazyDBA home page