SQL Stored Procs

[Dave]

1) I know that we can define an external proc to be Fenced or NotFenced on
"CREATE PROCEDURE" command.
I don't see the FENCED / NOT FENCED option on "Create Procedure" for SQL
stored procs.
Is it always "NotFenced" for SQL stored procs by any chance ?
We are in the process of migrating a SQL server app to DB2 with 2000 stored
procs and the DB2 server keeps
crashing too often during stored procs execution.

2) I have read somewhere that we can't use COMMIT statement in the body of
the stored proc. Is it true ?

Ver : Linux DB2 8.1 FP 2

[Serge Rielau]

Re: SQL Stored Procs

In DB2 V8 SQL Procedures run always UNFENCED because they are not
supposed to crash. Did you open an PMR to get teh crash investigated?

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab

[Daniel Morgan]

Re: SQL Stored Procs

Serge Rielau wrote:[color=blue]
> In DB2 V8 SQL Procedures run always UNFENCED because they are not
> supposed to crash. Did you open an PMR to get teh crash investigated?
>
> Cheers
> Serge
>[/color]

You mean there are things in DB2 that ARE supposed to crash? ;-)

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Mark A]

Re: SQL Stored Procs

> Serge Rielau wrote:[color=blue][color=green]
> > In DB2 V8 SQL Procedures run always UNFENCED because they are not
> > supposed to crash. Did you open an PMR to get teh crash investigated?
> >
> > Cheers
> > Serge[/color]
>
> You mean there are things in DB2 that ARE supposed to crash? ;-)
> --
> Daniel Morgan[/color]

Daniel, this just proves that you are nothing but troll. A rather sad and
pathetic example for a teacher to his students.

[Dave]

Re: SQL Stored Procs

We did open PMRs, IBM has admitted there are issues and are working on
them.
As I said, our entire app has been coded in stored procs (1800 SQL procs &
200 External proc) & UDFs.
We have used IBM Migration toolkit for most part.
We have actually crashed it while just compiling a SQL stored proc....IBM
told us that we have put 'DECLARE'
statements at wrong places and advised to reorder the statments in the proc
and it solved the crash problem.
I guess it is definitely a bug, as I expect a syntax error if I put a
DECLARE statement at a wrong place
and not a server crash.
Some times, the server crashes when we try to debug procs using
Developement Center which is very easy
to reproduce. We opened another PMR and IBM is working on it.
As multiple developers keep working sometimes it is difficult to figure out
who and what crashed the server.



"Mark A" <ma@switchboard .net> wrote in message
news:bHgAb.341$ KB5.191163@news .uswest.net...[color=blue][color=green]
> > Serge Rielau wrote:[color=darkred]
> > > In DB2 V8 SQL Procedures run always UNFENCED because they are not
> > > supposed to crash. Did you open an PMR to get teh crash investigated?
> > >
> > > Cheers
> > > Serge[/color]
> >
> > You mean there are things in DB2 that ARE supposed to crash? ;-)
> > --
> > Daniel Morgan[/color]
>
> Daniel, this just proves that you are nothing but troll. A rather sad and
> pathetic example for a teacher to his students.
>
>[/color]

[Daniel Morgan]

Re: SQL Stored Procs

Mark A wrote:
[color=blue][color=green]
>>Serge Rielau wrote:
>>[color=darkred]
>>>In DB2 V8 SQL Procedures run always UNFENCED because they are not
>>>supposed to crash. Did you open an PMR to get teh crash investigated?
>>>
>>>Cheers
>>>Serge[/color]
>>
>>You mean there are things in DB2 that ARE supposed to crash? ;-)
>>--
>>Daniel Morgan[/color]
>
>
> Daniel, this just proves that you are nothing but troll. A rather sad and
> pathetic example for a teacher to his students.
>
>[/color]

And this just proves that at least one regular at this usenet group
doesn't have a sense of humor: Lighten up!

--
Daniel Morgan

http://www.outreach.washington.edu/ext/certificates/oad/oad_crs.asp

http://www.outreach.washington.edu/ext/certificates/aoa/aoa_crs.asp

damorgan@x.wash ington.edu
(replace 'x' with a 'u' to reply)

[Serge Rielau]

Re: SQL Stored Procs

external routines are out of the control of DB2.
So you can make them crash or corrupt DB2 anyway you please.

To comment on Dave's follow up:
Good. When IBM Support told you to re-order the DECLARE's that was not
an intent to get away with it. It's what we call a "workaround " to take
the pressure from a problem and get you up and running.
Seems like this particular crash was related to an error path which may
explain why it was not discovered earlier.
You are absulutely correct to expect error-paths to play nice.

Cheers
Serge

--
Serge Rielau
DB2 SQL Compiler Development
IBM Toronto Lab