TM1Top does not cancel process while waiting Database - Batch Process

Post Reply
My24
Posts: 11
Joined: Tue Dec 12, 2017 2:57 pm
OLAP Product: TM1
Version: PA 2.0
Excel Version: Excel 2016

TM1Top does not cancel process while waiting Database - Batch Process

Post by My24 » Thu Nov 08, 2018 12:36 pm

Hi all!

In a project, we make parallel processes to take data from a database.
And sometimes we want to cancel the process while TI is trying to wait database response. (in case of when we realize anything wrong in code).
When we hit 'c' and try to cancel parallel processes while waiting database, we cannot cancel processes and nothing happens.

We cannot kill from Services and Task Manager due to some security conventions.

Is there any way to solve this issue?

Regards.

User avatar
gtonkin
MVP
Posts: 638
Joined: Thu May 06, 2010 3:03 pm
OLAP Product: TM1
Version: PAL 2.0.3
Excel Version: 2016 64-bit
Location: JHB, South Africa
Contact:

Re: TM1Top does not cancel process while waiting Database - Batch Process

Post by gtonkin » Thu Nov 08, 2018 12:41 pm

The only way I have found to do this is to kill the process via TM1Top then kill the associated process running on the database server i.e. the SQL Query/Stored Proc. Until the database releases the query, your TM1 will thread will be locked.

bgregs
Posts: 43
Joined: Wed Sep 12, 2018 11:19 am
OLAP Product: TM1 / Planning Analytics
Version: 2.0
Excel Version: 2016

Re: TM1Top does not cancel process while waiting Database - Batch Process

Post by bgregs » Thu Nov 08, 2018 3:45 pm

My preferred way of working around this during the development phase is the following:
  • Write your SQL query outside of the ODBC connection (i.e. SQL Developer for Oracle, pgAdmin III for PostgreSQL, etc.)
  • When you're positive the data coming out of your DB is correct, export it from the database to a csv or other flat file format
  • Use the flat file extract as the datasource in your TI until you are positive your TI code is correct
  • Merge your SQL query into your TI, hold your breath, think happy thoughts, and pray that you don't lock up the system for 3 hours :D

User avatar
paulsimon
MVP
Posts: 651
Joined: Sat Sep 03, 2011 11:10 pm
OLAP Product: TM1
Version: PA 2.0.5
Excel Version: 2016
Contact:

Re: TM1Top does not cancel process while waiting Database - Batch Process

Post by paulsimon » Mon Nov 12, 2018 10:32 pm

Hi

For info, TM1Top (or for that matter Ops Console) can only cancel a process after the current API call has been completed. Check the TM1 API documentation for a list of API functions. In this case it is probably TM1ProcessExecuteSQLQuery. Since the SQL that you send is all getting executed within a single API call, if that SQL never completes then the TM1 side will hang waiting for it to finish. As others have said, the solution is to stop TM1 waiting by killing the process on the SQL side.

Regards

Paul Simon

Post Reply