How do I identify if DB is Down and QUIT TI Process

[Gollapudi]

Hi,

I have couple of TI process involved in updating a Dimension on daily basis..
one of them unwinds the hierarchies of dimension and then it fires another TI Process which fires an SQL Query to Oracle Database for the updated values.
Another will Construct the Hierarchies, it works fine if Database responds well.

But, in-case if DB is down...

1st TI Process will unwind the Hierarchies
2nd TI Process Will fail
3rd TI Process will construct Hierarchies and all goes under "Unknown"/"Orphans" level.

So now I am looking for some TI function or Technic where I can check the status of DB if its Up and running, if not wanted to Stop TI process (PROCESSQUIT) before its gonna unwind the dimension.

Any ideas pls...

[Andy Key]

Create a process with an ODBC data source, give it a piece of simple SQL that you know works and will always return a small recordset. On the MetaData or Data tab just increment a counter to force the process to actually do something and complete successfully - if the database is available.

In your controlling process, add a call to this new process before your existing processes and check the process return code.

Code: Select all

ExecuteProcess( '<name of your new process>');
NumericGlobalVariable( 'ProcessReturnCode');
If( ProcessReturnCode <> ProcessExitNormal());
   ProcessQuit;
EndIf;

<Call existing processes...>

[rmackenzie]

Create a process with an ODBC data source, give it a piece of simple SQL that you know works and will always return a small recordset.

I totally agree with the gist of Andy's response, but I would implement it a little differently:

Master process:

Code: Select all

# Prolog
nReturnCode = ExecuteProcess ( '<name of your new process>' );
If ( nReturnCode <> ProcessExitNormal());
   ProcessQuit;
EndIf;

ODBC-checking process:

Code: Select all

# Prolog
ODBCOpen ( 'DSN', 'User', 'Password' );

First, I don't think there's any need to use a global variable. However my main point is that based on my experience with different ODBC drivers, there is a chance that TM1 simply hangs when it tries to run a TI where the DSN in the datasource can't make the connection, thus forcing a service restart. Sometimes you get the 'failed to initialise the process' and sometimes the hang. I've never found this to happen using the ODBCOpen command but it will throw an error if it can't make the connection. Therefore you get the same outcome but with less risk.

[lotsaram]

For a long time I used exactly the same method as Robin (in fact that's who I got the method from) i.e. connect to the database and if the connection is OK then assume everything is OK. However recently in my shop we have switched to what I think is a better and more foolproof method as we came unstuck a few times when the DBAs controlling the data sources we connect to changed things on their end and forgot to reset security, consequently the TM1 system account on the SQL DB had no access to some of the tables we needed and although the DB connection check passed, the query we actually needed subsequently failed.

Therefore our new approach is a modified version that combines Robin's method as step 1 then follows with (I think a slightly improved version of..) Andy's method. The check of a table/query is basically the same as Andy's method but we pass the same query as what the subsequent process needs to the "DB Check" process as opposed to some small fast query that we know should always work and not have many records due to the reasons above (that is we are making sure that not only can we connect to the DB but also that we can access the tables we need). All we are concerned with is that the query result actually returns a record count greater than 0. There are 2 ways to do this:
1/ "the all in TM1" approach. There is no code as such in the checking process other than to initialize a counter on the prolog and increment on the data tab with a ProcessBreak once the counter passes 1 record. Therefore it doesn't matter if the query might return 1 million rows since we exit after the first one anyway. (Well the query may take longer to generate.) On the Epilog if the counter is still 0 then call ProcessError else set ProcessExitNormal.
2/ the "do it in SQL" approach where you do a Select Count(*) as RowsNumber From <your query> so that you just get one record back with one value being the number or records in your query. then on the data tab you just check this variable and if it is non zero then ProcessExitNormal else ProcessError or ItemReject "the query has no records".
The logic and result is the same but the 2nd approach is much more elegant (thanks to Mr Strygner for the 2nd method who is approx 100 times better than I am at SQL).

Since you can pass the entire string of DatasourceQuery as a parameter you still just need one generic "DB check" routine even though the query might be different each time. For that matter you can also pass the ODBC name, user and password as parameters also.

---
edited for clarity

[rmackenzie]

Since you can pass the entire string of DatasourceQuery as a parameter you still just need one generic "DB check" routine even though the query might be different each time. For that matter you can also pass the ODBC name, user and password as parameters also.

Yes, good idea. When you are in a big shop where IT operations guarantee 100% up-time of the ERP/ DW sources, there is no point in my risk aversion and lots more value in doing it this way. Good range of options here for the OP.

[Gollapudi]

Hi,

Putting,

ODBCOpen ( 'DSN', 'User', 'Password' )

; in prolog in a process (which calls both Unwind & Dim Populate TI Processes) worked for me, incase if DB is down its not going further but gives an error.

Thanks to Andy, rmackenzie & lotsaram..
Venu.