1 Reply Latest reply on Nov 7, 2016 3:48 PM by BKBK

    Cold Fusion process Stuck Executing

    chrisr82907079 Level 1

      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!

        • 1. Re: Cold Fusion process Stuck Executing
          BKBK Adobe Community Professional & MVP

          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>