473,886 Members | 2,366 Online
Bytes | Software Development & Data Engineering Community
+ Post

Home Posts Topics Members FAQ

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 3109
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.wash ington.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.wash ington.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 thread has been closed and replies have been disabled. Please start a new discussion.

Similar topics

1
460
by: LineVoltageHalogen | last post by:
Greetings All, currentley there is a heated discussion in my place of work over which method is better/more efficient for simple selects. Background: 1.) Simple Application that uses sql server for backened db. 2.) The application is only inserting and selecting data from the db. 3.) The developers want to use sp_executesql for simple selects and the dba's want to use a stored proc. >From my reading it seems that sp_executesql has a...
3
1984
by: hrishy | last post by:
Hi Db2ers I am a newbie starting out in the fascinating world of IBM.Could anybody let me know in what version the sql /PL language was introduced..Can you also let me know what is the language that most stored procs are written using.. I am asking this question as i want to develop the ability to write stored procs..so just wunderin should i pick up fortran c c++ or sql pl.
1
1559
by: VM | last post by:
I'm working on a win appication that is constantly querying a small-sized DB. Until now, I've been using Selects from within my app but, all of a sudden I remembered of an application I was working on a year ago (with PowerBuilder) that queried the DB by invoking stored procedures that return the data to the application. One of the main problems was that there were over 100 SPs so it was pretty difficult to maintain. So my question: Is...
7
1728
by: trint | last post by:
My boss wants me to use stored procedures, but I thought somehow that using c# that ADO.Net was better. Any help is appreciated. Thanks, Trint
45
3454
by: John | last post by:
Hi When developing vb.bet winform apps bound to sql server datasource, is it preferable to use SELECTs or stored procedure to read and write data from/to SQL Server? Why? Thanks Regards
1
1720
by: kentk | last post by:
Is there a difference in how SQL Server 7 and SQL 2000 processes SQL passed from a program by an ADO command object. Reason I ask is I rewrote a couple applications a couple years ago were the SQL statements were inline. I basically took the SQL statements and put them into stored procs, were there were variables in the code I used SQL parameters in the stored procs. I got some amazing performance results by switching to stored procs. ...
15
2295
by: Burt | last post by:
I'm a stored proc guy, but a lot of people at my company use inline sql in their apps, sometimes putting the sql in a text file, sometimes hardcoding it. They don't see much benefit from procs, and say anyway they're are db specific- what if we change from SQL Server to Oracle one day? What say you experts? Is this one of those "no right answer" questions? Thanks,
8
2638
by: Frank Calahan | last post by:
I've been looking at LINQ and it seems very nice to be able to make queries in code, but I use stored procs for efficiency. If LINQ to SQL only works with SQL Server and stored procs are more efficient, what use is LINQ to SQL, other than to have a simpler way to call my stored proc?
5
4087
by: Bogdan | last post by:
Hi, I have a stored procedure that uses JOINs to return columns from multiple tables. I also have another stored proc that that takes a series of params and updates multiple tables. I used the framework to auto-generate a table adapter specifying both stored procs as Get/Fill and Update. The problem is that columns from the JOINed table seem to marked as 'read-only' so trying to update a row results in an exception. BTW, by default a...
0
9956
marktang
by: marktang | last post by:
ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, weíll explore What is ONU, What Is Router, ONU & Routerís main usage, and What is the difference between ONU and Router. Letís take a closer look ! Part I. Meaning of...
0
11172
Oralloy
by: Oralloy | last post by:
Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...
0
10770
jinu1996
by: jinu1996 | last post by:
In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...
1
10872
by: Hystou | last post by:
Overview: Windows 11 and 10 have less user interface control over operating system update behaviour than previous versions of Windows. In Windows 11 and 10, there is no way to turn off the Windows Update option using the Control Panel or Settings app; it automatically checks for updates and installs any it finds, whether you like it or not. For most users, this new feature is actually very convenient. If you want to control the update process,...
0
10429
tracyyun
by: tracyyun | last post by:
Dear forum friends, With the development of smart home technology, a variety of wireless communication protocols have appeared on the market, such as Zigbee, Z-Wave, Wi-Fi, Bluetooth, etc. Each protocol has its own unique characteristics and advantages, but as a user who is planning to build a smart home system, I am a bit confused by the choice of these technologies. I'm particularly interested in Zigbee because I've heard it does some...
0
7139
by: conductexam | last post by:
I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...
0
5808
by: TSSRALBI | last post by:
Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...
0
6010
by: adsilva | last post by:
A Windows Forms form does not have the event Unload, like VB6. What one acts like?
1
4627
by: 6302768590 | last post by:
Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

By using Bytes.com and it's services, you agree to our Privacy Policy and Terms of Use.

To disable or enable advertisements and analytics tracking please visit the manage ads & tracking page.