We have an application that uses CF 5.0 to collect parameters and pass them
to stored procedures in DB2 on the mainframe. It works well except that the
stored procedures are abending on a random but regular basis(!). We have not
been able to pinpoint the cause of the abends but we think it may have
something to do with when a user triggers a stored procedure then does not
wait for the result set but tries instead to send another request, thus
breaking the thread for the stored procedure so it has nowhere to return its
answer set (I'm not a database person and I probably showed my ignorance
with that statement . . .).
We are using mainframe DB2 version 5.2. Our stored procedures only read from
the database, they do not update, write, or delete. Our CF variables are all
local scope. We are running CF 5.0 on a quad processor server with 4GB RAM.
We are averaging about 540 calls a day to the stored procedures. Average
total elapsed time for the DB2 stored procedures is 1.7 seconds, but we have
one stored procedure that typically takes 5 - 20 seconds. We have received
abends on all three of the most requested stored procedures.
Here is an example of code we use to call a stored procedure:
=============================================
<cftry>
<cftransaction>
<cfstoredproc datasource="#datasource#"
procedure="#rcl_listing#">
<cfprocparam type="IN"
cfsqltype="CF_SQL_VARCHAR"
value="#listingQueryString#"
dbvarname="@PARM-RZSTR-INPUT">
<CFPROCPARAM TYPE="OUT"
cfsqltype="CF_SQL_VARCHAR"
VARIABLE="code"
DBVARNAME="@PARM-RETURN-CODE">
<CFPROCPARAM TYPE="OUT"
cfsqltype="CF_SQL_VARCHAR"
VARIABLE="message"
DBVARNAME="@PARM-RETURN-MSG">
<CFPROCRESULT NAME = "RS1">
<CFPROCRESULT NAME = "RS2" resultset="2">
</cfstoredproc>
</cftransaction>
<cfcatch>
<script language="javascript" type="text/javascript">
top.frames["XXXXX"].transferDisplay.close();
alert ("The application was unable to connect to the
Database.\nPlease try again later.");
</script>
<!--- <cfset errorMessage = cfcatch.type & " - " &
cfcatch.message & " - " & cfcatch.detail>
<cffile action="write"
file="d:\xxxx\xxxx\xxxx\xxxx\xxxx\error.txt"
output="#errorMessage#">--->
<cfabort>
</cfcatch>
</cftry>
=========================================
Is there anything obviously missing here that we should be doing to protect
the integrity of the call to the stored procedure? Thanks!
George
[EMAIL PROTECTED]
______________________________________________________________________
This list and all House of Fusion resources hosted by CFHosting.com. The place for
dependable ColdFusion Hosting.
FAQ: http://www.thenetprofits.co.uk/coldfusion/faq
Archives: http://www.mail-archive.com/cf-talk@houseoffusion.com/
Unsubscribe: http://www.houseoffusion.com/index.cfm?sidebar=lists
