Highlighted

Cold Fusion process Stuck Executing

New Here ,
Nov 07, 2016

Copy link to clipboard

Copied

I have a question that I am hoping someone might help with, an issue I come across with a daily process we have.

We have a process that executes a cold fusion script on a daily basis (business days). It pulls in daily production from a file given to us with the previous day's data. On Tuesdays, we get 3 files (Saturday-Monday). We concatenate the files into one before the cold fusion script executes.

The way our process works is our cold fusion script kicks off and runs a few things, and eventually ends with the following code:

<cfif #qry_stgDate.stg_date# lt #qry_nextloadDate.next_date#>

    <cfoutput>Got nothing to load, inform the development team</cfoutput>

    <cfquery name="qry_dlDate" datasource="#request.DS#">

        select max(to_date(trandate,'yyyymmdd')) as dl_date from

        dl_prod_dly

    </cfquery>

    <cfmail to="xxx@server.com" subject="Daily Load ISSUE" server="#request.mailServer#" from="#request.mailFrom#">

        Daily ETL process did not run. The date to load is #qry_nextloadDate.next_date# and staging table has data for #qry_stgDate.stg_date#.

        DL table has data for #qry_dlDate.dl_date#

    </cfmail>

<cfelse>

    <cfoutput>Fire off the ETl procedure</cfoutput>

    <cfstoredproc procedure="P_ETL__DLY" datasource="#request.DS#">

       

    </cfstoredproc>

    <cfstoredproc procedure="P_ETL__DLY_2" datasource="#request.DS#">

    </cfstoredproc>

    <cfstoredproc procedure="P_ETL__DLY_3" datasource="#request.DS#">

    </cfstoredproc>

    <cfmail to="xxx@server.com" subject="Daily Load completed successfully" server="#request.mailServer#" from="#request.mailFrom#">

        Daily ETL process completed. Data was loaded for #qry_nextloadDate.next_date#

        Run the validation checks.

   </cfmail>

</cfif>

When the procedures kick off, each one will run for each day available in the concatenated file with a loop, i.e. P_ETL__DLY will loop 3 times on Tuesdays but only once on Monday, Wednesday, Thursday, Friday.

Our coldfusion script runs fine all days except Tuesdays. Every day it emails us the success email and finishes with no issues. On Tuesdays, the procedures execute and finish with no issues, we receive the email, but the script itself gets hung up and never finishes. We verified the issue isn't on the database side and were wondering if maybe there was an issue with the actual cold fusion script? Any help or advice would be appreciated!

Views

174

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more

Cold Fusion process Stuck Executing

New Here ,
Nov 07, 2016

Copy link to clipboard

Copied

I have a question that I am hoping someone might help with, an issue I come across with a daily process we have.

We have a process that executes a cold fusion script on a daily basis (business days). It pulls in daily production from a file given to us with the previous day's data. On Tuesdays, we get 3 files (Saturday-Monday). We concatenate the files into one before the cold fusion script executes.

The way our process works is our cold fusion script kicks off and runs a few things, and eventually ends with the following code:

<cfif #qry_stgDate.stg_date# lt #qry_nextloadDate.next_date#>

    <cfoutput>Got nothing to load, inform the development team</cfoutput>

    <cfquery name="qry_dlDate" datasource="#request.DS#">

        select max(to_date(trandate,'yyyymmdd')) as dl_date from

        dl_prod_dly

    </cfquery>

    <cfmail to="xxx@server.com" subject="Daily Load ISSUE" server="#request.mailServer#" from="#request.mailFrom#">

        Daily ETL process did not run. The date to load is #qry_nextloadDate.next_date# and staging table has data for #qry_stgDate.stg_date#.

        DL table has data for #qry_dlDate.dl_date#

    </cfmail>

<cfelse>

    <cfoutput>Fire off the ETl procedure</cfoutput>

    <cfstoredproc procedure="P_ETL__DLY" datasource="#request.DS#">

       

    </cfstoredproc>

    <cfstoredproc procedure="P_ETL__DLY_2" datasource="#request.DS#">

    </cfstoredproc>

    <cfstoredproc procedure="P_ETL__DLY_3" datasource="#request.DS#">

    </cfstoredproc>

    <cfmail to="xxx@server.com" subject="Daily Load completed successfully" server="#request.mailServer#" from="#request.mailFrom#">

        Daily ETL process completed. Data was loaded for #qry_nextloadDate.next_date#

        Run the validation checks.

   </cfmail>

</cfif>

When the procedures kick off, each one will run for each day available in the concatenated file with a loop, i.e. P_ETL__DLY will loop 3 times on Tuesdays but only once on Monday, Wednesday, Thursday, Friday.

Our coldfusion script runs fine all days except Tuesdays. Every day it emails us the success email and finishes with no issues. On Tuesdays, the procedures execute and finish with no issues, we receive the email, but the script itself gets hung up and never finishes. We verified the issue isn't on the database side and were wondering if maybe there was an issue with the actual cold fusion script? Any help or advice would be appreciated!

Views

175

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Nov 07, 2016 0
BKBK LATEST
Adobe Community Professional ,
Nov 07, 2016

Copy link to clipboard

Copied

chrisr82907079 wrote:

Our coldfusion script runs fine all days except Tuesdays. Every day it emails us the success email and finishes with no issues. On Tuesdays, the procedures execute and finish with no issues, we receive the email, but the script itself gets hung up and never finishes.

It might help to run the procedures sequentially, using a named lock. Something like

<cflock name="doOneAtATime" type="exclusive" timeout="60">

<cfstoredproc procedure="P_ETL__DLY" datasource="#request.DS#">

</cfstoredproc>

</cflock>

<cflock name="doOneAtATime" type="exclusive" timeout="60">

<cfstoredproc procedure="P_ETL__DLY_2" datasource="#request.DS#">

</cfstoredproc>

</cflock>

<cflock name="doOneAtATime" type="exclusive" timeout="60">

<cfstoredproc procedure="P_ETL__DLY_3" datasource="#request.DS#">

</cfstoredproc>

</cflock>

<cflock name="doOneAtATime" type="exclusive" timeout="60">

<cfmail to="xxx@server.com" subject="Daily Load completed successfully" server="#request.mailServer#" from="#request.mailFrom#">

Daily ETL process completed. Data was loaded for #qry_nextloadDate.next_date#

Run the validation checks.

</cfmail>

</cflock>

Likes

Translate

Translate

Report

Report
Community Guidelines
Be kind and respectful, give credit to the original source of content, and search for duplicates before posting. Learn more
Reply
Loading...
Nov 07, 2016 0