Sinking Feeling

[2803stan]

I have a successful app which is used in a variety of client/server
situations. (DB2 8.x.)

I need to update it to have XML capability to interface with several
other external programs. Therefore, I was happy to see that DB2 is
doing what I need with V9.x

I upgraded an experimental server to 9.5 and a client to same. I'm
using UDB for LUW and the server is Windows Server 2003 and the client
is XP. The programing language has been (to date) VisualBasic 6.

Now, suddenly, the Connection object takes about 100x longer to
connect, the Command object takes about 100x longer to collect the
parameters from a stored procedure, and the actual Execution takes
about 100x longer to get a recordset with return of an SQLSTATE to the
Command object!

In addition, there are many unpredictible cases when the application's
call to a SP cannot find it at all and returns a fatal error!

I have a SINKING FEELING that IBM has mandated more Java -- More slow,
clunky, error-prone Java. Please, someone, tell me that this is not
so.

Or tell me that it IS so, so that I may do what I have to do . . .

[Serge Rielau]

28******@gmail.com wrote:

I have a SINKING FEELING that IBM has mandated more Java -- More slow,
clunky, error-prone Java. Please, someone, tell me that this is not
so.

Or tell me that it IS so, so that I may do what I have to do . . .

There is no Java on the server. So whatever it is it isn't that.
You symptoms are to generic (more like an aching belly ;-) to give sound
advice. Can you open a PMR?

Alternatively I propose to divide and conquer.
Connect from the CLP. Run some tests.
What happens if the connection is local? Etc, etc...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[2803stan]

Serge,

I wasn't thinking of Java on the server, but on the new client. There
seems to be some disk-thrashing during all this (as I said, it takes
much, much longer than before for a connection.

1. Local connection is fine, ie if client and server are on same box,
the Stored Procedure is executed and returns correctly.
2. Remote CLP connections are fine.
3. Direct SQL statements executed from the remote client are fine.
THEN,
4. Calls to stored procedures from the new client (9.5 LUW) to the
new 9.5 server fail. Here's what happens: (Each of these is a step
in the code on the client:)
a) The client Connection object (via the new ODBC, I think it's in
MDAC 2.8) makes a very slow connection with the server, with disk
thrashing.
b) Uses Command object to contact the SP and pick up the number and
type of parameters (IN, OUT) via "Prepare Command" in code.
(Obviously can read the SP at this point.)
c) Back at the Client, The Command object is loaded with the values
of the IN Parameters.
d) This returns to the server for the SQL in the SP to run.
e) At this point a "About to Execute (Succes)" Event occurs in the
Connection object which has a Satus of 1 (succes).

Finis. Next message is the ODBC/system/SQL message that the SP code
is unavailable and the procedure has failed. The Connection Object
has Status 2 (fail). The Command remains with whatever parameters it
had; the Recordset is never opened. (Follows an eg of return with
"VERIFY_USER," but same for any SP):

[IBM][CLI driver]SQL0444N Routine "VERIFY_USER" (specific name
"SQL071201165900590") is implemented with code in library or path
"\VERIFY_USER", function "VERIFY_USER" which cannot be accessed.
Reason code "4". SQLSTATE = 42724

[Serge Rielau]

28******@gmail.com wrote:

[IBM][CLI driver]SQL0444N Routine "VERIFY_USER" (specific name
"SQL071201165900590") is implemented with code in library or path
"\VERIFY_USER", function "VERIFY_USER" which cannot be accessed.
Reason code "4". SQLSTATE = 42724

Is this UDF defined by you? Which language? Does the path make any
sense? (It looks dubious)
I'm wondering whether this has something to do with the search path...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[2803stan]

On Dec 16, 6:52 pm, Serge Rielau <[e-mail]:

[e-mail] wrote:

[IBM][CLI driver]SQL0444N Routine "VERIFY_USER" (specific name
"SQL071201165900590") is implemented with code in library or path
"\VERIFY_USER", function "VERIFY_USER" which cannot be accessed.
Reason code "4". SQLSTATE = 42724

Is this UDF defined by you? Which language? Does the path make any
sense? (It looks dubious)
I'm wondering whether this has something to do with the search path...

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Serge,

<<Is this UDF defined by you? Which language?>>

The stored procedure is defined by me and has been working fine for
many months. Written in SQL. Also tried other SPs that have been
working well and same thing.

<<Does the path make any sense? (It looks dubious) >>

I'm not sure what you mean by this. All procs are written on the
client and sent to the server by using the CLP: DB2 CONNECT TO
MYDATABASE USER ME USING MYPASSWORD <ENTER (get connection message)
then
db2 -td@ -vf "C:\ADDRESS\SP_MY_STORED_PROC.SQL" where
SP_MY_STORED_PROC.SQL is the name of the local file containing the SQL
code for the SP. (Returns success message).

I do not declare the address on the server. All these procs have
worked fine for a long time.

Is there a way for me to check the search path, change a PATH
environmental variable, or do anything else to make the SP more
findable? Remember that the Command successfully gets the number and
type of parameters before trying to execute the SQL query (which is
what fails).

SS

PS The total SP looks like (Extra white space removed):
DROP SPECIFIC PROCEDURE "DATASHIELD"."VERIFY_USER"@
CREATE PROCEDURE "DATASHIELD"."VERIFY_USER" (
IN p_USER_ID VARCHAR(18),
IN p_PASSWORD VARCHAR(250),
OUT p_SQLSTATE CHAR(5) )
SPECIFIC "DATASHIELD"."VERIFY_USER"
DYNAMIC RESULT SETS 1
READS SQL DATA
LANGUAGE SQL
P1: BEGIN
DECLARE SQLSTATE CHAR(5) DEFAULT '00000';
DECLARE C1 CURSOR WITH RETURN FOR
SELECT
UNIQUE_ID,
GROUP_ID,
SIGNATURE,
NAME_TO_SIGN
FROM DATASHIELD.USERS U
WHERE UCASE(U.USER_ID) = UCASE(p_USER_ID) AND U.PASSWORD =
p_PASSWORD; DECLARE EXIT HANDLER FOR SQLEXCEPTION, SQLWARNING, NOT
FOUND
SET p_SQLSTATE = SQLSTATE;
VALUES (SQLSTATE) INTO p_SQLSTATE;
OPEN C1;
END P1@

[Serge Rielau]

OK you should NEVER get a 444 from an SQL Procedure invocation.
Please open a PMR.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[2803stan]

On Dec 17, 11:10 am, Serge Rielau <[e-mail]:

OK you should NEVER get a 444 from an SQL Procedure invocation.
Please open a PMR.

Cheers
Serge

--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

My thoughts go like this:

I got the free version 9.5 as Express-C. I could buy a license and
then submit a PMR. Why should I pay $2300 (the cost of a license) for
the privilege of submitting a PMR? I have already spent many hours,
which means many $.

I paid full fare for V8 WSE. Why not revert to that version (with
highest fixpack) and wait until IBM figures out what's wrong and fixes
it? Since I can't be the only one with this problem, why should I
alone pay IBM to fix its own problem?

I'm going to try one more thing: a third-party ODBC driver. If that
doesn't work then I'm back to V8.max and wait until I hear that IBM
has solved the problem. I want the features advertised for V9.5, but
I can't afford to personally cause them to be available.

SS

[Mark A]

<28******@gmail.comwrote in message
news:96**********************************@l1g2000h sa.googlegroups.com...

My thoughts go like this:

I got the free version 9.5 as Express-C. I could buy a license and
then submit a PMR. Why should I pay $2300 (the cost of a license) for
the privilege of submitting a PMR? I have already spent many hours,
which means many $.

I paid full fare for V8 WSE. Why not revert to that version (with
highest fixpack) and wait until IBM figures out what's wrong and fixes
it? Since I can't be the only one with this problem, why should I
alone pay IBM to fix its own problem?

I'm going to try one more thing: a third-party ODBC driver. If that
doesn't work then I'm back to V8.max and wait until I hear that IBM
has solved the problem. I want the features advertised for V9.5, but
I can't afford to personally cause them to be available.

SS

Is 9.5 GA yet? If not, the DB2 Express-C version should probably be
considered beta code. Even if it is GA, any problems with the free version
of DB2 Express-C version should be reported on the DB2 Express-C forum (on
IBM website) and I suspect that the IBM people who run the forum will
forward that to development. It probably will not hurt to do some name
dropping when you post on that forum (Serge).

[Serge Rielau]

Mark A wrote:

Is 9.5 GA yet?

Hmpf!

If not, the DB2 Express-C version should probably be
considered beta code. Even if it is GA, any problems with the free version
of DB2 Express-C version should be reported on the DB2 Express-C forum (on
IBM website) and I suspect that the IBM people who run the forum will
forward that to development.

Yes. Express C has forum support.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

[2803stan]

On Dec 17, 9:13 pm, Serge Rielau <[e-mail]:

Mark A wrote:

Is 9.5 GA yet?

Hmpf!

If not, the DB2 Express-C version should probably be
considered beta code. Even if it is GA, any problems with the free version
of DB2 Express-C version should be reported on the DB2 Express-C forum (on
IBM website) and I suspect that the IBM people who run the forum will
forward that to development.

Yes. Express C has forum support.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to Serge and Mark:

<<From Mark A. -- should be reported on the DB2 Express-C forum (on
IBM website) and I suspect that the IBM people who run the forum will
forward that to development. It probably will not hurt to do some
name
dropping when you post on that forum (Serge). >>
<<and answered from Serge: Yes. Express C has forum support.>>

So who forwards this, and how does one get results?

C'mon guys, I'm nobody. You're somebody. Why are the somebodies not
doing something for the nobody?!?

SS

[Mark A]

"Serge Rielau" <sr*****@ca.ibm.comwrote in message
news:5s*************@mid.individual.net...

Hmpf!

Sorry, my DB2 databases process real-time credit card authorizations. I
don't keep up with brand new releases.

[Mark A]

<28******@gmail.comwrote in message
news:91**********************************@s12g2000 prg.googlegroups.com...

So who forwards this, and how does one get results?

C'mon guys, I'm nobody. You're somebody. Why are the somebodies not
doing something for the nobody?!?

SS

I am nobody (customer) also, plus we don't own any V9 licenses.

Register on this forum and post your problem. Mention that Serge read your
original post on the comp.databases.ibm-db2 newsgroup and he told you to
open a PMR.
http://www.ibm.com/developerworks/fo...pa?forumID=805

[Serge Rielau]

28******@gmail.com wrote:

On Dec 17, 9:13 pm, Serge Rielau <[e-mail]:

>Mark A wrote:

>>Is 9.5 GA yet?

Hmpf!

>>If not, the DB2 Express-C version should probably be
considered beta code. Even if it is GA, any problems with the free version
of DB2 Express-C version should be reported on the DB2 Express-C forum (on
IBM website) and I suspect that the IBM people who run the forum will
forward that to development.

Yes. Express C has forum support.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab

Reply to Serge and Mark:

<<From Mark A. -- should be reported on the DB2 Express-C forum (on
IBM website) and I suspect that the IBM people who run the forum will
forward that to development. It probably will not hurt to do some
name
dropping when you post on that forum (Serge). >>
<<and answered from Serge: Yes. Express C has forum support.>>

So who forwards this, and how does one get results?

The forum is moderated by three guys just as (in) competent as I am:
Ryan, Ian and Raul.

C'mon guys, I'm nobody. You're somebody. Why are the somebodies not
doing something for the nobody?!?

Ryan, Ian, and Raul are the paid somebodies to help your nobodyness.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab