SQL Procedure Profiler for Stinger

[Serge Rielau]

For those of you playing with SQL Procedures, here's useful toy - I hope.
http://www-106.ibm.com/developerwork...dm-0406rielau/

Enjoy
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[aka]

Hi Serge,

what am I doing wrong?
Connect string:jdbc:db2 ://hector:6789/dbhec
Error:dbAccess: setConnection() ::
Description=>co m.ibm.db2.jcc.a .DisconnectExce ption: A communication error
has been detected. Comm
unication protocol being used: Reply.fill(). Communication API being used:
InputStream.rea d(). Location where the error was detect
ed: Connection reset. Communication function detecting the error: *.
Protocol specific error codes(s) TCP/IP SOCKETS
com.ibm.db2.jcc .a.DisconnectEx ception: A communication error has been
detected. Communication protocol being used: Reply.fill(). C
ommunication API being used: InputStream.rea d(). Location where the error
was detected: Connection reset. Communication function d
etecting the error: *. Protocol specific error codes(s) TCP/IP SOCKETS
at com.ibm.db2.jcc .c.a.a(a.java:4 17)
at com.ibm.db2.jcc .c.ab.b(ab.java :173)
at com.ibm.db2.jcc .c.ab.c(ab.java :219)
at com.ibm.db2.jcc .c.ab.c(ab.java :333)
at com.ibm.db2.jcc .c.ab.v(ab.java :1434)
at com.ibm.db2.jcc .c.bb.a(bb.java :37)
at com.ibm.db2.jcc .c.b.d(b.java:1 096)
at com.ibm.db2.jcc .c.b.b(b.java:1 011)
at com.ibm.db2.jcc .c.b.m(b.java:7 95)
at com.ibm.db2.jcc .c.b.a(b.java:5 65)
at com.ibm.db2.jcc .c.b.<init>(b.j ava:288)
at com.ibm.db2.jcc .DB2Driver.conn ect(DB2Driver.j ava:162)
at java.sql.Driver Manager.getConn ection(Unknown Source)
at java.sql.Driver Manager.getConn ection(Unknown Source)
at db.DbConnection .setConnection( DbConnection.ja va:87)
at gui.Script.conn ectActionPerfor med(Script.java :1208)
at gui.Script$1.ac tionPerformed(S cript.java:166)
at javax.swing.Abs tractButton.fir eActionPerforme d(Unknown Source)
at javax.swing.Abs tractButton$Han dler.actionPerf ormed(Unknown
Source)
at javax.swing.Def aultButtonModel .fireActionPerf ormed(Unknown
Source)
at javax.swing.Def aultButtonModel .setPressed(Unk nown Source)
at javax.swing.pla f.basic.BasicBu ttonListener.mo useReleased(Unk nown
Source)
at java.awt.Compon ent.processMous eEvent(Unknown Source)
at javax.swing.JCo mponent.process MouseEvent(Unkn own Source)
at java.awt.Compon ent.processEven t(Unknown Source)
at java.awt.Contai ner.processEven t(Unknown Source)
at java.awt.Compon ent.dispatchEve ntImpl(Unknown Source)
at java.awt.Contai ner.dispatchEve ntImpl(Unknown Source)
at java.awt.Compon ent.dispatchEve nt(Unknown Source)
at java.awt.Lightw eightDispatcher .retargetMouseE vent(Unknown Source)
at java.awt.Lightw eightDispatcher .processMouseEv ent(Unknown Source)
at java.awt.Lightw eightDispatcher .dispatchEvent( Unknown Source)
at java.awt.Contai ner.dispatchEve ntImpl(Unknown Source)
at java.awt.Window .dispatchEventI mpl(Unknown Source)
at java.awt.Compon ent.dispatchEve nt(Unknown Source)
at java.awt.EventQ ueue.dispatchEv ent(Unknown Source)
at java.awt.EventD ispatchThread.p umpOneEventForH ierarchy(Unknow n
Source)
at java.awt.EventD ispatchThread.p umpEventsForHie rarchy(Unknown
Source)
at java.awt.EventD ispatchThread.p umpEvents(Unkno wn Source)
at java.awt.EventD ispatchThread.p umpEvents(Unkno wn Source)
at java.awt.EventD ispatchThread.r un(Unknown Source)
Error:dbAccess: setConnection() ::
Description=>co m.ibm.db2.jcc.a .DisconnectExce ption: A communication error
has been detected. Comm
unication protocol being used: Reply.fill(). Communication API being used:
InputStream.rea d(). Location where the error was detect
ed: Connection reset. Communication function detecting the error: *.
Protocol specific error codes(s) TCP/IP SOCKETS
com.ibm.db2.jcc .a.DisconnectEx ception: A communication error has been
detected. Communication protocol being used: Reply.fill(). C
ommunication API being used: InputStream.rea d(). Location where the error
was detected: Connection reset. Communication function d
etecting the error: *. Protocol specific error codes(s) TCP/IP SOCKETS
at com.ibm.db2.jcc .c.a.a(a.java:4 17)
at com.ibm.db2.jcc .c.ab.b(ab.java :173)
at com.ibm.db2.jcc .c.ab.c(ab.java :219)
at com.ibm.db2.jcc .c.ab.c(ab.java :333)
at com.ibm.db2.jcc .c.ab.v(ab.java :1434)
at com.ibm.db2.jcc .c.bb.a(bb.java :37)
at com.ibm.db2.jcc .c.b.d(b.java:1 096)
at com.ibm.db2.jcc .c.b.b(b.java:1 011)
at com.ibm.db2.jcc .c.b.m(b.java:7 95)
at com.ibm.db2.jcc .c.b.a(b.java:5 65)
at com.ibm.db2.jcc .c.b.<init>(b.j ava:288)
at com.ibm.db2.jcc .DB2Driver.conn ect(DB2Driver.j ava:162)
at java.sql.Driver Manager.getConn ection(Unknown Source)
at java.sql.Driver Manager.getConn ection(Unknown Source)
at db.DbConnection .setConnection( DbConnection.ja va:87)
at gui.Script.conn ectActionPerfor med(Script.java :1210)
at gui.Script$1.ac tionPerformed(S cript.java:166)
at javax.swing.Abs tractButton.fir eActionPerforme d(Unknown Source)
at javax.swing.Abs tractButton$Han dler.actionPerf ormed(Unknown
Source)
at javax.swing.Def aultButtonModel .fireActionPerf ormed(Unknown
Source)
at javax.swing.Def aultButtonModel .setPressed(Unk nown Source)
at javax.swing.pla f.basic.BasicBu ttonListener.mo useReleased(Unk nown
Source)
at java.awt.Compon ent.processMous eEvent(Unknown Source)
at javax.swing.JCo mponent.process MouseEvent(Unkn own Source)
at java.awt.Compon ent.processEven t(Unknown Source)
at java.awt.Contai ner.processEven t(Unknown Source)
at java.awt.Compon ent.dispatchEve ntImpl(Unknown Source)
at java.awt.Contai ner.dispatchEve ntImpl(Unknown Source)
at java.awt.Compon ent.dispatchEve nt(Unknown Source)
at java.awt.Lightw eightDispatcher .retargetMouseE vent(Unknown Source)
at java.awt.Lightw eightDispatcher .processMouseEv ent(Unknown Source)
at java.awt.Lightw eightDispatcher .dispatchEvent( Unknown Source)
at java.awt.Contai ner.dispatchEve ntImpl(Unknown Source)
at java.awt.Window .dispatchEventI mpl(Unknown Source)
at java.awt.Compon ent.dispatchEve nt(Unknown Source)
at java.awt.EventQ ueue.dispatchEv ent(Unknown Source)
at java.awt.EventD ispatchThread.p umpOneEventForH ierarchy(Unknow n
Source)
at java.awt.EventD ispatchThread.p umpEventsForHie rarchy(Unknown
Source)
at java.awt.EventD ispatchThread.p umpEvents(Unkno wn Source)
at java.awt.EventD ispatchThread.p umpEvents(Unkno wn Source)
at java.awt.EventD ispatchThread.r un(Unknown Source)

For those of you playing with SQL Procedures, here's useful toy - I hope.
http://www-106.ibm.com/developerwork...dm-0406rielau/
Enjoy
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Do you get that on CONNECT?
I installed Stinger squeaky clean on my laptop, downloaded teh
application to verify and used localhost and port 50000
(which is what is in etc\services).

Hope that helps.
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Ian]

aka wrote:

Hi Serge,

what am I doing wrong?
Connect string:jdbc:db2 ://hector:6789/dbhec

6789 is the port for Type-3 java connections. This tool uses the new Type
4 ("jcc") driver. You need to use the port that your instance listens on
(50000 is the default, but check the SVCENAME dbm config parameter).


-----= Posted via Newsfeeds.Com, Uncensored Usenet News =-----
http://www.newsfeeds.com - The #1 Newsgroup Service in the World!
-----== Over 100,000 Newsgroups - 19 Different Servers! =-----

[aka]

Yes Serge, when I hit the connect button....I tried all my jdk's, local,
remote...I can't get a connect with SQLProfiler...

Ian
hmpf...yes I missed that it, even I looked thru the source...would be easy
to get if the sqlprofiler would give back the sqlcode
here's my output:

D:\aka\sqlprofi ler>db2admin start
SQL4406W The DB2 Administration Server was started successfully.

D:\aka\sqlprofi ler>db2start
2004-06-09 00.16.14 0 0 SQL1063N DB2START processing was
successful.
SQL1063N DB2START processing was successful.

D:\aka\sqlprofi ler>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08020" with
level identifier "03010106".
Informational tokens are "DB2 v8.1.7.328", "s040415", "WR21326", and FixPak
"7".
Product is installed at "d:\sqllib" .
D:\aka\sqlprofi ler>db2 get dbm cfg|d:\bin\cygw in\bin\grep -i svcename
TCP/IP Service name (SVCENAME) = db2c_DB2

D:\aka\sqlprofi ler>type
c:\windows\syst em32\drivers\et c\services|d:\b in\cygwin\bin\g rep -i db2c_db2
db2c_DB2 50000/tcp
db2c_DB2i 50001/tcp
db2c_DB2CTLSV 50003/tcp

D:\aka\sqlprofi ler>db2 connect to dbodi

Database Connection Information

Database server = DB2/NT 8.2.0
SQL authorization ID = AKA
Local database alias = DBODI
D:\aka\sqlprofi ler>db2 list applications

Auth Id Application Appl. Application Id DB #
of
Name Handle Name
Agents
-------- -------------- ---------- ------------------------------ -------- -
----
AKA db2bp.exe 11 *LOCAL.DB2.002B 48222521 DBODI 1
D:\aka\sqlprofi ler>
D:\aka\sqlprofi ler>set CLASSPATH
CLASSPATH=.;d:\ sqllib\java\db2 java.zip;d:\sql lib\java\db2jcc .jar;d:\sqllib\ j
ava\sqlj.zip;d: \sqllib\java\db 2jcc_license_ci suz.jar;d
:\sqllib\java\d b2jcc_license_c u.jar;d:\sqllib \bin;d:\sqllib\ tools\db2XTrigg e
r.jar;d:\sqllib \java\common.ja r

D:\aka\sqlprofi ler>java -version
java version "1.5.0-beta2"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.5.0-beta2-b51)
Java HotSpot(TM) Client VM (build 1.5.0-beta2-b51, mixed mode, sharing)

D:\aka\sqlprofi ler>java gui.SysMain
return of emptyField false
Connect string:jdbc:db2 ://localhost:50000/dbodi
Error:dbAccess: setConnection() ::
Description=>co m.ibm.db2.jcc.a .SqlException: Connection authorization
failure occurred. Reason:
security mechanism not supported
com.ibm.db2.jcc .a.SqlException : Connection authorization failure occurred.
Reason: security mechanism not supported
at com.ibm.db2.jcc .c.b.f(b.java:1 581)
at com.ibm.db2.jcc .c.b.a(b.java:1 226)
at com.ibm.db2.jcc .c.bb.b(bb.java :3307)
at com.ibm.db2.jcc .c.bb.a(bb.java :258)
at com.ibm.db2.jcc .c.bb.a(bb.java :66)
at com.ibm.db2.jcc .c.b.d(b.java:1 097)
at com.ibm.db2.jcc .c.b.b(b.java:1 011)
at com.ibm.db2.jcc .c.b.m(b.java:7 95)
at com.ibm.db2.jcc .c.b.a(b.java:5 65)
at com.ibm.db2.jcc .c.b.<init>(b.j ava:288)
at com.ibm.db2.jcc .DB2Driver.conn ect(DB2Driver.j ava:162)
at java.sql.Driver Manager.getConn ection(Unknown Source)
at java.sql.Driver Manager.getConn ection(Unknown Source)
at db.DbConnection .setConnection( DbConnection.ja va:87)
at gui.Script.conn ectActionPerfor med(Script.java :1208)
<snip>

"Serge Rielau" schrieb im Newsbeitrag
news:ca******** **@hanover.toro lab.ibm.com...

Do you get that on CONNECT?
I installed Stinger squeaky clean on my laptop, downloaded teh
application to verify and used localhost and port 50000
(which is what is in etc\services).

Hope that helps.
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Security mechanism not supported...
I'll see what our JCC dudes think about that.
In the meantime:
It couldn't be something as silly as your firewall or so?

But decent error-handling would be a good idea for the tool, eh?
It also needs a DELETE button for reports and a way to hook in explain...

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Serge Rielau]

Excerpt from my DBM CFG:
Database manager authentication (AUTHENTICATION ) = SERVER
Trust all clients (TRUST_ALLCLNTS ) = YES
Trusted client authentication (TRUST_CLNTAUTH ) = CLIENT

Are you playing on a desktop or true client server?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[ChetWest]

Serge Rielau <sr*****@ca.e ye-be-em.com> wrote in message news:<c9******* ***@hanover.tor olab.ibm.com>.. .

For those of you playing with SQL Procedures, here's useful toy - I hope.
http://www-106.ibm.com/developerwork...dm-0406rielau/

Enjoy
Serge

Serge...Will it work on anything besides Stinger (i.e. Ver 8.1)???

Chet

[Serge Rielau]

More or less - I foresaw that this tool is needed badly and was able to
get a hook into FP5.

Use
db2set DB2_SQLROUTINE_ KEEP_FILES=yes
(restart DB2)
You will get files named:
sqllib/function/routine/<dbname>/<schema>/<packagename>.s cm
when creating SQL Procedures.
These scm files contain a mapping of the SQL statements in the procedure
to line-numbers. In Stinger that information is stored in SYSCAT.STATEMEN TS.
Now with that in mind one can massage the export script (ksh example) as
shown below and you can operate the tool in "disconnect mode":

#!/usr/bin/ksh
dbname=TEST
basedir=$HOME/sqllib/function/routine/sqlproc/$dbname
locdir=$HOME/exportData
mkdir $locdir
db2 connect to test;

for j in `ls $basedir`;
do
echo $j
cd $basedir/$j
for k in `ls $basedir/$j/*.scm`;
do
db2 import FROM $k of del messages msgs.txt INSERT INTO SCM;
echo $k
done
done

db2 export to $locdir/stmt.ixf of ixf messages msgs.txt "select * from
stmts";
db2 export to $locdir/routine.ixf of ixf messages msgs.txt select
R.ROUTINENAME, R.SPECIFICNAME, R.TEXT, R.ROUTINESCHEMA from
SYSCAT.ROUTINES R;
db2 export to $locdir/routinedep.ixf of ixf messages msgs.txt select
D.ROUTINENAME, D.ROUTINESCHEMA , D.BTYPE, D.BNAME from SYSCAT.ROUTINED EP D;
db2 export to $locdir/statements.ixf of ixf messages msgs.txt select
ST.PKGNAME, ST.SECTNO, A.STMTNO from SYSCAT.STATEMEN TS ST, SCM A WHERE
A.SECTNO= ST.SECTNO;
db2 export to $locdir/data.ixf of ixf messages datamsgs.msg "SELECT *
FROM REMOTE_STMTS EV, REMOTE_STATEMEN TS ST, REMOTE_routined ep d,
REMOTE_routines r WHERE EV.PACKAGE_NAME = ST.PKGNAME AND ST.PKGNAME =
d.bname AND ST.SECTNO = EV.SECTION_NUMB ER AND D.ROUTINENAME =
R.ROUTINENAME AND D.BTYPE = 'K'";

#db2 -tvf import.sql;
db2 connect reset;

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[aka]

Serge,

thanks for U'r reply...I will explain my environment:

I'm fully windows (not that I'm proud of it) in this test env. machine 1
called wotan is Win2003 Server Std Edition PrimaryDomainCt rl, Stinger
default install, 3 instances (1 partitioned), machine 2 called hector is
WinXP Prof, Stinger, 3 instances (1 partitioned), machine 3 called odin is
my laptop, WinXP Prof, Stinger, 2 instances.
So far I did quite a lot of testing since Stinger TP2, I got up DPF,
federated also running with wrappers and this stuff and also my own java
apps with mostly type 2 jdbc.app driver are running, but I get this error
with SQLProfiler as well as with my applets built with IMS Tools Table
Editor.

According to your tip I deactivated my winxp internet firewall running on
hector (reboot all 3 machines). But that changed nothing :(

I get the error on every box to every db that I tried, local and remote. I
posted the results from a local test on my laptop, because you described
that as beeing your own environment with localhost and port 50000, that is
my default also.

Next the output of my test where I try to connect from my laptop to remote
database dbhec located on machine hector, remote instance db2hec catalog'ed
via CC with discover as tcpip node (port is 50001). Connect from command
line works fine, then I set env to Stinger java directory start profiler and
hit connect button...
D:\aka\sqlprofi ler>db2 connect to dbhec user aka
Enter current password for aka:

Database Connection Information

Database server = DB2/NT 8.2.0
SQL authorization ID = AKA
Local database alias = DBHEC
D:\aka\sqlprofi ler>db2level
DB21085I Instance "DB2" uses "32" bits and DB2 code release "SQL08020" with
level identifier "03010106".
Informational tokens are "DB2 v8.1.7.328", "s040415", "WR21326", and FixPak
"7".
Product is installed at "d:\sqllib" .
D:\aka\sqlprofi ler>set path=d:\sqllib\ java\jdk\jre\bi n

D:\aka\sqlprofi ler>java -version
java version "1.4.1"
Java(TM) 2 Runtime Environment, Standard Edition (build 1.4.1)
Classic VM (build 1.4.1, J2RE 1.4.1 IBM Windows 32 build cn1411-20040301a
(JIT enabled: jitc))

D:\aka\sqlprofi ler>java gui.SysMain
return of emptyField false
Connect string:jdbc:db2 ://hector:50001/dbhec
Error:dbAccess: setConnection() ::
Description=>co m.ibm.db2.jcc.a .SqlException: Connection authorization
failure occurred. Reason:
security mechanism not supported
com.ibm.db2.jcc .a.SqlException : Connection authorization failure occurred.
Reason: security mechanism not supported
at com.ibm.db2.jcc .c.b.f(b.java:1 581)
at com.ibm.db2.jcc .c.b.a(b.java:1 226)
at com.ibm.db2.jcc .c.bb.b(bb.java :3307)
at com.ibm.db2.jcc .c.bb.a(bb.java :258)
at com.ibm.db2.jcc .c.bb.a(bb.java :66)
at com.ibm.db2.jcc .c.b.d(b.java:1 097)
at com.ibm.db2.jcc .c.b.b(b.java:1 011)
at com.ibm.db2.jcc .c.b.m(b.java:7 95)
at com.ibm.db2.jcc .c.b.a(b.java:5 65)
at com.ibm.db2.jcc .c.b.<init>(b.j ava:288)
at com.ibm.db2.jcc .DB2Driver.conn ect(DB2Driver.j ava:162)
at java.sql.Driver Manager.getConn ection(DriverMa nager.java:538)
at java.sql.Driver Manager.getConn ection(DriverMa nager.java:188)
at db.DbConnection .setConnection( DbConnection.ja va:87)
at gui.Script.conn ectActionPerfor med(Script.java :1208)
<snip>


"Serge Rielau" schrieb im Newsbeitrag
news:ca******** **@hanover.toro lab.ibm.com...

Excerpt from my DBM CFG:
Database manager authentication (AUTHENTICATION ) = SERVER
Trust all clients (TRUST_ALLCLNTS ) = YES
Trusted client authentication (TRUST_CLNTAUTH ) = CLIENT

Are you playing on a desktop or true client server?

Cheers
Serge
--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab