Hi,

I am using DB2 8.2 for Linux, in dbm cfg I have set KEEPFENCED = YES, but developers creating Stored procedure with not fenced (syscat.procedures column fenced has 'N' value) option.. Is this ok to have stored procedure with not fenced?

Please suggest me

Thanks,

Allian

Yes, it is OK to have Stored Procedures to be not fenced--as long as you can guarantee that they will not bring down the database. This excerpt from the documentation should give you more insight:

http://publib.boulder.ibm.com/infoce...n/c0005286.htm

Andy

When creating SQL stored procedure by using development centre 'not fenced' is default.. Is there any way to change all stored procedure to have 'fenced' option

Thanks,

Allian

You can only set them to "Fenced" ad create time. You can use the Development Center to change the create statement from "NOT FENCED" to "FENCED". Then rebuild them. This should drop them and create them anew.

Andy

You can use the ALTER PROCEDURE statement and switch them to FENCED.

http://publib.boulder.ibm.com/infoce...n/r0008168.htm

Note that fenced procedures and UDFs incurr a communication overhead. The parameters have to be passed to the fenced mode process (db2fmp) via shared memory, whereas non-fenced routines do this in-process. The separation of the processes gives you the safety you need in a production system. So using fenced routines is highly recommended in most cases.

In the development manual there is no keyword 'FENCED' or 'NOT FENCED' mentioned for CREATE PROCEDURE statement, when I try from development centre I am getting an error for the word 'FENCED'.

that means sql stored procedure always will be created as NOT FENCED ? ..any suggestion

Thanks,

Allian

You can only specify FENCED/NOT FENCED on non-sql (external) Stored Procedures.

Andy

Stored procedures with LANGUAGE SQL were translated to C code, which was compiled - but that's only in Version 7 (which is out of service). These days, all LANGUAGE SQL stored procedures are compiled into so-called "sections", a DB2 internal structure for SQL statements. Since DB2 knows what it is doing (contrary to developers of external procedures), there is no point to execute such sections in a fenced process.