Pentaho

Joao Figueiredo posted 09-28-2018 10:14

Hi,

what is the pan command that you are using?

Can you provide the pan log (send private message if sensitive data, or mask it)?

Thanks

Best regards

saket Maheshwary posted 09-28-2018 13:20

Thanks for the reply Joao Figueiredo. I really appreciate it.

From my JSP I call following command:

<<AbsolutePath on Windows Server>>\ExecuteETLTransformation.bat "<<AbsolutePath on Windows Server>>\data-integration-8.1.0.0" PDI_RepoName PDI_TransformationName PDI_username PDI_Pwd <<AbsolutePath on Windows Server>>\LogFileaName.log "-param:v_month=JAN" "-param:v_year=2018"

Contents of Execute ETLTransformation.bat

d:

cd %1

pan.bat /rep:"%2" /trans:"%3" /dir:/Transformations /user:%4 /pass:%5 /level:Detailed >> %6 %7 %8 %9

I added some debug messages in Pan.bat and Spoon.bat and found that it gets stuck in spoon.bat at command:

%SPOON_START_OPTION% "%_PENTAHO_JAVA%" %OPT% -jar launcher\launcher.jar -lib ..\%LIBSPATH% %_cmdline%

This command basically translates into

"java.exe"  "-Xms1024m" "-Xmx2048m" "-XX:MaxPermSize=256m" "-Dhttps.protocols=TLSv1,TLSv1.1,TLSv1.2" "-Djava.library.path=libswt\win64" "-DKETTLE_HOME=" "-DKETTLE_REPOSITORY=" "-DKETTLE_USER=" "-DKETTLE_PASSWORD=" "-DKETTLE_PLUGIN_PACKAGES=" "-DKETTLE_LOG_SIZE_LIMIT=" "-DKETTLE_JNDI_ROOT=" -jar launcher\launcher.jar -lib ..\libswt\win64  -main org.pentaho.di.pan.Pan -initialDir "<<Absolute path on Windows server>>\data-integration-8.1.0.0"\ /rep:"PDI_RepoName" /trans:"PDI_TransformationName" /dir:/Transformations /user:PDI_UserName /pass:PDI_Password /level:Detailed "-param:v_month=JAN" "-param:v_year=2018"

Above command executes and creates an output file as expected, PDI log file also indicates that process is successfully completed, but control does not return back. Subsequent debug messages in spoon.bat are not printed. But once I shutdown tomcat server, it prints the subsequent messages from spoon.bat

I also tried to use an older version of jar file pentaho-application-launcher-6.1.0.1-196 to investigate, but it seems that is also behaving the same way. So basically I just copied pentaho-application-launcher-6.1.0.1-196.jar file into launcher directory and modified spoon to use pentaho-application-launcher-6.1.0.1-196 instead of launcher.jar

Thanks for all your help.

Joao Figueiredo posted 09-28-2018 14:02

Glad to help...

You should invoke the pan.bat inside your ExecuteETLTransformation.bat using the command

"call pan.bat ....".

The call of other batch files inside Windows batch files should be done using the call otherwise the control won't be passed back into the calling batch file, because the calling batch file is aborted. See below:

call | Microsoft Docs

"Calls one batch program from another without stopping the parent batch program. The call command accepts labels as the target of the call."

Let me know if it worked...

Best regards

João Figueiredo

saket Maheshwary posted 09-28-2018 15:23

I tried with call but same behavior. I have been using these same batch files on older servers without any issue for quite sometime.

I believe problem is that launcher command does not return the control back after successful execution. Not sure why though.

Joao Figueiredo posted 09-28-2018 16:57

Let's do then a back trace approach to try to pinpoint where the problem is, let me know on which one the test failed or made things work, and the results (did it end, did things worked as expected, etc..):

1) Try to execute one of those transformations directly by calling the pan.bat from the command line. Check if it ends correctly.

2) Call the pan.bat in your bat file without redirecting the output into a file.

3) Call your bat file from command line directly.

saket Maheshwary posted 09-28-2018 18:42

Didn't realize I can not attach files on this forum.

I believe first and third worked fine one worked.

Not sure in second point what to you mean by "without redirecting the output into a file."? Please elaborate.

Again appreciate you taking time out of your busy schedule.

Joao Figueiredo posted 09-28-2018 19:20

You are redirecting the out and err using the >> command in front of the pan.bat call...

Try to remove the output redirect...

And I think you should be calling pan.bat using the call command IMO.

saket Maheshwary posted 10-01-2018 14:12

even after commenting the call to redirect output and invoking pan using call results are still the same.

I commented all the echo statements.

During all three tests behavior remains the same.

Joao Figueiredo posted 10-01-2018 14:41

If running "pan.bat" or "ExecuteETLTransformation.bat" manually in the command line works ok... I would say that you may have some issue with the code that is running on the jsp to execute the batch command.

Can you place the code you are using here?

saket Maheshwary posted 10-01-2018 20:13

This content was either too long or contained formatting that did not work with our migration. A PDF document is attached that contains the original representation

 

//***********I am setting up the variables for logfile and output file and job specific **************//  else if (job.equalsIgnoreCase("UPFIT_ETA_LIST")) {      outFile = outputDirectory + job.toLowerCase() + ".xls";             jobLogFileName = job.toLowerCase()+".log";      }     // HD-113 new job    else if (job.equalsIgnoreCase("TAL_METRICS_REPORT")) {      outFile = outputDirectory + job.toLowerCase() + ".xls";             jobLogFileName = job.toLowerCase()+".log";      }      // HD-226 new job    else if (job.equalsIgnoreCase("EXPIRING_TEMP_TAGS")) {      outFile = outputDirectory + job.toLowerCase() + ".xls";             jobLogFileName = job.toLowerCase()+".log";      }    //hd-376 added new job MAINTENANCE_ACCRUAL_REPORT   else if (job.equalsIgnoreCase("MAINTENANCE_ACCRUAL_REPORT")) {      System.out.println(job);      outFile = outputDirectory + job.toLowerCase() + ".xls";      System.out.println(outFile);      v1_rn_clientAC = request.getParameter("clientaccount");      v1_rn_actionDate = request.getParameter("accountEndDate");      v1_recipient_address =  request.getParameter("emailadd");      System.out.println("v1_rn_clientAC "+v1_rn_clientAC+" v1_rn_actionDate "+v1_rn_actionDate);      param7  = "\"-param:v_rn_clientAC="+v1_rn_clientAC+"\"";      param8 = "\"-param:v_actionDate="+v1_rn_actionDate+"\"";       param9 = "\"-param:v_email="+v1_recipient_address+"\"";      System.out.println("param7 "+param7+" param8 "+param8+" param9 "+param9);           jobLogFileName = job.toLowerCase()+".log";      }System.out.println("outFile:"+ outFile);   if(outFile != null) {      finalFile = new File(outFile);      if(finalFile.exists())         if (!finalFile.delete()){            error = "Y";            out.print("Problem with processing.  Could not delete file.  Contact ITS");         }      }   if (!error.equals("Y")) {   if (job.equalsIgnoreCase("REFRESH_REPORT_STATS_DATA_MONTHLY_NOW") || job.equalsIgnoreCase("IRS_EXTRACT_REPORT") ||       job.equalsIgnoreCase("PRODUCT_INFORMATION_EXTRACT")||       job.equalsIgnoreCase("MAINT_COMPLIANCE_REPORT") || // added for Bug 16620       job.equalsIgnoreCase("MAINT_COMPLIANCE_REPORT_LANDCARE") || // added for HPS 2379       job.equalsIgnoreCase("TRANSFER_CLEARANCE_FILE_TO_BANK")|| //added for HPS 3231       job.equalsIgnoreCase("DEALER_RECEIVED_LIST")||// added for Bug 16862       job.equalsIgnoreCase("IN_SERVICE_LIST")|| // added for Bug 16862       job.equalsIgnoreCase("UPFIT_ETA_LIST")||  //added for Bug 16862       job.equalsIgnoreCase("TAL_METRICS_REPORT")||//HD-113       job.equalsIgnoreCase("EXPIRING_TEMP_TAGS")||//HD-226       job.equalsIgnoreCase("MAINTENANCE_ACCRUAL_REPORT"))//HD-376//***********Here I am calling a Job specific bat file **************//     //Calling Job Batch file       etlCommand = batchFileDir + "ExecuteETLJob.bat " + "\"" +            pentahoLocation + "\" " + etlRepositoryName + " " +            job + " " + etlRepoUserName + " " +            etlRepoPassword + " " + logDirectory+jobLogFileName + " " +            param7 + " " + param8 +  " " + param9;     else//***********Here I am calling transformatoin specific file **************//     //Calling Transformation Batch file       etlCommand = batchFileDir + "ExecuteETLTransformation.bat " + "\"" +            pentahoLocation + "\" " + etlRepositoryName + " " +            job + " " + etlRepoUserName + " " +            etlRepoPassword + " " + logDirectory+jobLogFileName + " " +            param7 + " " + param8 +  " " + param9;     System.out.println(etlCommand);//***********Executing the command just created **************//     Process proc = Runtime.getRuntime().exec(etlCommand);     if(job.equalsIgnoreCase("REFRESH_REPORT_STATS_DATA_MONTHLY_NOW") ||        job.equalsIgnoreCase("REFRESH_GL_FA_SEQ_DTL") ||        job.equalsIgnoreCase("REFRESH_LOAD_VEH_SALES_STATS") ||        job.equalsIgnoreCase("REFRESH_AR_WILLOW_CLOSE") ||        job.equalsIgnoreCase("REFRESH_GL_AFTER_CLOSE") ||        job.equalsIgnoreCase("REFRESH_AR_ADP_CLOSE") ||        job.equalsIgnoreCase("MAINT_COMPLIANCE_REPORT_LANDCARE") || //Added for HPS 2379        job.equalsIgnoreCase("MAINT_COMPLIANCE_REPORT") ||        job.equalsIgnoreCase("TRANSFER_CLEARANCE_FILE_TO_BANK")|| // added for Bug 16620        job.equalsIgnoreCase("DEALER_RECEIVED_LIST")||// added for Bug 16862        job.equalsIgnoreCase("IN_SERVICE_LIST")|| // added for Bug 16862        job.equalsIgnoreCase("UPFIT_ETA_LIST")|| // added for Bug 16862        job.equalsIgnoreCase("TAL_METRICS_REPORT")|| //HD-113            job.equalsIgnoreCase("EXPIRING_TEMP_TAGS")|| //HD-226        job.equalsIgnoreCase("MAINTENANCE_ACCRUAL_REPORT")) //HD-376         returncode = -12345; //This is done because we don't want JSP to wait in case of REFRESHES. They take a long time to complete. User is sent an email from the Oracle Procedure about the completion status of the Refresh.     else//***********Waiting for ETL to finish up and generate the output file. This is where we get stuck because bat file never returns. **************//        returncode = proc.waitFor(); //In other cases we want JSP to wait for the process to complete.     System.out.println("returncode:" + returncode);     //This means batch file returned successfully     if(returncode == 0) {       out.print("The process has completed successfully.<br><br>");       if(outFile != null){         //Display Link to save output file         //Though faremeDirectory and outputDirectory point to same location. but for some reason if we use outputDirectory  it does not works and we have to set faremeDirectory in such a way as we we are picking file from a server folder.         finalFile = new File(faremeDirectory + job.toLowerCase() + ".csv");         if (job.equalsIgnoreCase("IRS_EXTRACT_REPORT") || job.equalsIgnoreCase("TRANSFER_CLEARANCE_FILE_TO_BANK")) { // added for HPS-3231            finalFile = new File(faremeDirectory + job.toLowerCase() + ".txt");         }         //Added for Bug 16469/Bug 16658/Bug 16788/Bug 16862         if (job.equalsIgnoreCase("AUTOTAG_TRANSACTION_REPORT") || job.equalsIgnoreCase("MAINTENANCE_TRANSACTION_REPORT") ||job.equalsIgnoreCase("MINIMUM_IRR_AUDIT_REPORT")||             job.equalsIgnoreCase("TAL_TRANSACTION_REPORT")||job.equalsIgnoreCase("LESSEE_ODOMETER_QUERY")){            System.out.println("final file creation");            finalFile = new File(faremeDirectory + job.toLowerCase() + ".xls");            System.out.println("finalFile "+finalFile);         }         if(finalFile.exists()){           out.print("Right click on the file below and choose '<b>Save Target As..</b>'<br>");            out.print("<a href=file:" + finalFile + ">" + job.toLowerCase() + " </a><br><br>");         }         else {           out.print("Though ETL process has reported success but we are not able to find the generated file. Please contact ITS.<br><br>");         }       }     }

Joao Figueiredo posted 10-02-2018 18:49

Hi,

The problem is that the batch file that you are executing is waiting for you to read from the input/error streams. I've replicated your issue, but once I read from those buffers the process ended normally.

Notice that I've also call the pan.bat from my test batch file using the command "call pan.bat %DEMO_PAN_PARAMS%"

The code I've used is below... notice that you must read the streams from a separate thread (not good approach to span new threads from inside a JSP container... so please adapt to better suite and adhere to best practices).

Also consider, that having a JSP thread that is executing the browser request hanged waiting for an external process to run (in this case, run the pan.bat), may lead to a thread starvation if the service has high loads and/or the transformations being executed take some time to finnish. This should be an asynchronous process IMO.

final java.lang.Process proc = java.lang.Runtime.getRuntime().exec("testPan.bat");

new Thread(() -> {

  try{

    java.io.BufferedReader reader = new java.io.BufferedReader(new java.io.InputStreamReader(proc.getInputStream()));

    String line;

    while ((line = reader.readLine()) != null){

      System.out.println("Output: " + line);

    }

  } catch (Exception e) {

    System.err.println(e.getMessage());

  }

}).start();

new Thread(() -> {

  try{

    java.io.BufferedReader reader = new java.io.BufferedReader(new java.io.InputStreamReader(proc.getErrorStream()));

    String line;

    while ((line = reader.readLine()) != null){

      System.out.println("Error: " + line);

    }

  } catch (Exception e) {

    System.err.println(e.getMessage());

  }

}).start();

System.out.println("returncode:" + proc.waitFor());

Data Conversion posted 08-15-2019 00:16