Prev: WBI Monitor
Next: The column name as com.ibm.db2.jcc.b.dc@1d5b3a
From: Frank Swarbrick on 19 Dec 2007 18:52
>>> On 12/19/2007 at 5:08 AM, in message
<5ssfu0F1ar1stU1(a)mid.individual.net>,
Serge Rielau<srielau(a)ca.ibm.com> wrote:
> Mark A wrote:
>> I will be upgrading to V9 in about 14 months (after the 2008 shopping
> season
>> has concluded), so it looks like I will have to choose between V9.1 and
> 9.5
>> if they stay on a dual track.
>>
>> My guess is that some in IBM were pushing for 9.5 to be released as
> 10.1,
>> but a decision was made to offer it for free to those with a V9
> maintenance
>> contract, hence the 9.5 designation.
> Mark,
>
> With DB2 9 the team has switched to a "no function in fixpack" policy.
> That is the reason that even a dot-release receives its own code-stream.
> Further you will see that that more releases will be available in
> parallel (right now: 8.2, 9.1, 9.5) to support conservative companies
> like yours.
>
> As of the naming of releases: It's a black art I don't even pretend to
> fathom.

Shouldn't they have bumped it up to "version 11" in order to keep pace with
Oracle?

:-)

Frank

From: 2803stan on 19 Dec 2007 19:04
On Dec 19, 1:08 pm, Serge Rielau <srie...(a)ca.ibm.com> wrote:
> 2803s...(a)gmail.com wrote:
> > Dear Folk,
>
> > Well, this has been a trip!
>
> > Discovered the problem. The stored procedure call was not fully
> > qualified! That is, instead of calling "GEORGE"."MYPROC" I was only
> > calling "MYPROC"
>
> > How did this work for so long? Because the user was always signing on
> > as "GEORGE," so db2 automatically attached that to the name of the SP.
>
> > (There still is a residual problem -- why was the error message not
> > more specific?)
>
> You are talking about the -444 rc='4' error?
> TYour explanation does not compute, at least not entirely.
> If a function is not found you get a -440.
> To make this Jibe with your observation there must have been ANOTHER
> function (namely the one with specificname: SQL071201165900590) that was
> declared, but was not (properly) implemented.
>
> The -440 situtation woudl have pointed you teh right way:
> * The user's SQL path does not contain the schema to which the desired
> function or method belongs, and an unqualified reference was used.
>
> To reach the -444 DB2 has successfully resolved and is now concerned to
> find the implementation. So the message is geared towards that.
>
> Cheers
> Serge
>
> --
> Serge Rielau
> DB2 Solutions Development
> IBM Toronto Lab

(The topic seems now to be versioneering!)

"There is more in heaven and earth than is dreamt of in your
philosophy . . ." (Hamlet)

There has to be an explanation for what you say vs what happened; the
app now works as expected since I went back and added the shema name
to all stored procedure names.

There must be a database table, available to all who can log on, which
lists the unqualified names of SPs and their IN and OUT parameters.
Or maybe the list is ordered by schema, but still searchable by SP
name.

Why? Because when my app was failing and I was trying to debug it, it
became obvious that even when a call was made to an unqualified SP
name, the first thing that happened was the return to the App of all
the parameters of the SP. Both IN and OUT. Only after that, when the
SQL of the SP had to be executed and a result set returned, did the
process fail.

SS
From: Serge Rielau on 20 Dec 2007 06:41
2803stan(a)gmail.com wrote:
> There has to be an explanation for what you say vs what happened; the
> app now works as expected since I went back and added the shema name
> to all stored procedure names.
>
> There must be a database table, available to all who can log on, which
> lists the unqualified names of SPs and their IN and OUT parameters.
> Or maybe the list is ordered by schema, but still searchable by SP
> name.
>
> Why? Because when my app was failing and I was trying to debug it, it
> became obvious that even when a call was made to an unqualified SP
> name, the first thing that happened was the return to the App of all
> the parameters of the SP. Both IN and OUT. Only after that, when the
> SQL of the SP had to be executed and a result set returned, did the
> process fail.
I presume you are not aware of DB2 procedure resolution rules.
When a procedure is not qualified it is resolved using the PATH (just
like in your OS). DB2 will consider all procedures with matching number
of parameters to which the invoker has EXECUTE PRIVILEGE in order of
appearance in the path.

Try this:
CREATE PROCEDURE Y.P() SPECIFIC P_LALALA LANGUAGE C EXTERNAL NAME
'LALALA' PARAMETER STYLE SQL;
CREATE PROCEDURE X.P() BEGIN END;

SET PATH = SYSTEM PATH;
VALUES VARCHAR(CURRENT PATH, 70);
CALL P();
SET PATH = SYSTEM PATH, Y;
CALL P();
SET PATH = SYSTEM PATH, X, Y;
CALL P();
SET PATH = SYSTEM PATH, Y, X;
CALL P();

Here is what I get:
db2 => SET PATH = SYSTEM PATH;
DB20000I The SQL command completed successfully.
db2 => VALUES VARCHAR(CURRENT PATH, 70);

1
----------------------------------------------------------------------
"SYSIBM","SYSFUN","SYSPROC","SYSIBMADM"

1 record(s) selected.

db2 => CALL P();
SQL0440N No authorized routine named "P" of type "PROCEDURE" having
compatible arguments was found. SQLSTATE=42884
db2 => SET PATH = SYSTEM PATH, Y;
DB20000I The SQL command completed successfully.
db2 => CALL P();
SQL0444N Routine "Y.P" (specific name "P_LALALA") is implemented with
code in
library or path "\LALALA", function "" which cannot be accessed. Reason
code:
"4". SQLSTATE=42724
db2 => SET PATH = SYSTEM PATH, X, Y;
DB20000I The SQL command completed successfully.
db2 => CALL P();

Return Status = 0
db2 => SET PATH = SYSTEM PATH, Y, X;
DB20000I The SQL command completed successfully.
db2 => CALL P();
SQL0444N Routine "Y.P" (specific name "P_LALALA") is implemented with
code in
library or path "\LALALA", function "" which cannot be accessed. Reason
code:
"4". SQLSTATE=42724

Note how twiddling with the path can flip you between success, -440 and
-444 rc=4

The perhaps most common way to set the default path from an app during
connection is the db2cli.ini file.

Cheers
Serge
--
Serge Rielau
DB2 Solutions Development
IBM Toronto Lab
From: Will Honea on 19 Dec 2007 02:48
Mark A wrote:

> "Mark A" <nobody(a)nowhere.com> wrote in message news:...
>> Sorry, my DB2 databases process real-time credit card authorizations. I
>> don't keep up with brand new releases.
>
> I see the problem now. 9.5 was not issued as a fixpack to 9.1, as was the
> case for 7.2 (fixpack 3 for 7.1) and 8.2 (fixpack 7 for 8.1). I was
> expecting to see 9.5 as a V9.1 fixpack, so I assumed it was still beta.
>
> I will be upgrading to V9 in about 14 months (after the 2008 shopping
> season has concluded), so it looks like I will have to choose between V9.1
> and 9.5 if they stay on a dual track.
>
> My guess is that some in IBM were pushing for 9.5 to be released as 10.1,
> but a decision was made to offer it for free to those with a V9
> maintenance contract, hence the 9.5 designation.

That became apparent when I tried to restore a 9.1 backup into a 9.5
system - same way I did with V7 and 8 across fixpak levels. Oops!
Incompatible version....

--
Will Honea

--
Posted via a free Usenet account from http://www.teranews.com

From: 2803stan on 21 Dec 2007 17:57
<<I presume you are not aware of DB2 procedure resolution rules. >>

Thanks for the reminder -- I haven't had to think of this for some
time. But, with all the advertising of "autonomous" functioning, and
all the automatic resizing of tablespaces, etc, etc, wouldn't you
think that db2's dbm, having placed the stored procedure (or the UDF
or whatever) where it wants it (I have no rôle in that placement),
that db2 could keep track of where it is? It could make its own entry
in a .ini file, or somewhere else more private, like a table. This
path stuff is one of the last things that one wants to keep track of.

I ended the last sentence with a preposition. I must be upset. So
I'll sign off before I do something worse, like USE ALL CAPS.

SS

PS For the purists {Americans wouldn't understand this}, I wrote
"in a .ini file" rather than "in an .ini file" because I pronounce it
"dot ini."
First  |  Prev  |  Next  |  Last
Pages: 1 2 3 4 5
Prev: WBI Monitor
Next: The column name as com.ibm.db2.jcc.b.dc@1d5b3a