469,609 Members | 1,666 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 469,609 developers. It's quick & easy.

SQL Stored Procs

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
Nov 12 '05 #1
6 2828
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

Nov 12 '05 #2
Serge Rielau wrote:
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


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

--
Daniel Morgan
http://www.outreach.washington.edu/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #3
> Serge Rielau wrote:
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


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


Daniel, this just proves that you are nothing but troll. A rather sad and
pathetic example for a teacher to his students.
Nov 12 '05 #4
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:bH******************@news.uswest.net...
Serge Rielau wrote:
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


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


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

Nov 12 '05 #5
Mark A wrote:
Serge Rielau wrote:
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


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

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


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/e...ad/oad_crs.asp
http://www.outreach.washington.edu/e...oa/aoa_crs.asp
da******@x.washington.edu
(replace 'x' with a 'u' to reply)

Nov 12 '05 #6
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

Nov 12 '05 #7

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

1 post views Thread by LineVoltageHalogen | last post: by
3 posts views Thread by hrishy | last post: by
45 posts views Thread by John | last post: by
8 posts views Thread by Frank Calahan | last post: by
reply views Thread by Solution2021 | last post: by
reply views Thread by devrayhaan | last post: by
reply views Thread by gheharukoh7 | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.