I was recently sat in the “What’s New in Essbase 11.1.2.4” session at Kscope15 where one of the questions from the audience lead on to a discussion on terminating applications, Steve Liebermensch from Oracle spoke of an upcoming update in patch 002 for Essbase 11.1.2.4 which will change the behaviour of unloading applications using Maxl.
Fast forward a few days and the Essbase 002 PSUs are released.
In the patch readme it contains the following:
Changes in Server Shutdown (21137674)
With this release, issuing a MaxL statement to unload an application cancels all active requests and database connections, and stops the application, unless you explicitly specify otherwise using the no_force option.
Updated MaxL Syntax
alter system unload application {all|APP-NAME} [no_force];
no_force Optional keyword. The no_force option causes Essbase to return an error if active requests are running on the application.
In the past it has sometimes been a bit of an issue shutting down applications when there are active requests, there are ways around it using a few different MaxL statements like kill requests and forcibly log out sessions but these can have varying success depending on the active requests and the Essbase version, I must admit from 11.1.2.3 it has been much easily to terminate requests like exports.
I will just go through the behaviour of using the unload application MaxL statement when there are active requests using the base install of 11.1.2.4
First of all with an active data load in progress then issuing the MaxL unload application statement.
MAXL> alter system unload application MORE2LIFE;
OK/INFO - 1054005 - Shutting down application MORE2LIFE.
ERROR - 1013018 - Cannot unload database [BLOG] while user [admin@Native Directory] is performing database operation. Wait for the user to complete the operation, or ask the user to abort it. Log out all users and then unload the database..
Ok so I added the following line to the same unload script:
alter system kill request on application MORE2LIFE;
Even though the request was killed the application was not unloaded
MAXL> alter system kill request on application MORE2LIFE;
OK/INFO - 1056090 - System altered.
MAXL> alter system unload application MORE2LIFE;
OK/INFO - 1054005 - Shutting down application MORE2LIFE.
ERROR - 1013018 - Cannot unload database [BLOG] while user [admin@Native Directory] is performing database operation. Wait for the user to complete the operation, or ask the user to abort it. Log out all users and then unload the database..
To get around this I added the logout session with force statement though I am sure it can be done with other statements.
MAXL> alter system logout session on application MORE2LIFE force;
OK/INFO - 1013220 - Supervisor [admin@Native Directory] has forced user [admin@Native Directory] to logout.
OK/INFO - 1051037 - Logging out user [admin@Native Directory], active for 11 minutes.
OK/INFO - 1056092 - Sessions logged out [1].
OK/INFO - 1056090 - System altered.
MAXL> alter system unload application MORE2LIFE;
OK/INFO - 1054005 - Shutting down application MORE2LIFE.
OK/INFO - 1056090 - System altered.
It was exactly the same behaviour when running a calculation, unload was no use without killing the request and the user, running a data export only required the request killing first before successfully unloading like the following:
MAXL> alter system kill request on application MORE2LIFE;
OK/INFO - 1056090 - System altered.
MAXL> alter system unload application MORE2LIFE;
OK/INFO - 1054005 - Shutting down application MORE2LIFE.
OK/INFO - 1056090 - System altered.
The essbase application log gives more of an insight to what is going on.
Received Command [ParExport] from user [admin@Native Directory]
Request [ParExport] from user [admin@Native Directory] was terminated by an admin user
Transaction [ 0x4000f( 0x55913c7a.0x6aef0 ) ] aborted due to status [1014030].
Parallel export failed due to status [1014030]
Transaction [ 0x4000e( 0x55913c7a.0x6aef0 ) ] aborted due to status [1005032].
Received Command [Unload Database]
RECEIVED SHUTDOWN COMMAND - SERVER TERMINATING
Right so moving on to the difference with the 002 patch applied.
It is worth taking note of the information in the essbase patch readme:
Oracle recommends using the same version of all Essbase portfolio products (Essbase, Essbase Administration Services, Hyperion Provider Services, and Essbase Studio) and components (server, client, runtime client, API, and JAPI). When only some Essbase portfolio products are included in a patch release, the last released versions of the products that are not included in the patch are supported.
What is also interesting is that Oracle have now started to include the following recommendation after applying essabse server patches.
After you apply a patch within the same release codeline, Oracle recommends as a best practice that you export the data from your databases, clear the data from the databases, and then reload the data.
I tested the same script with only an unload application statement and this time the application was shutdown successfully while running active requests like data loads, calculations and exports.
Here is an example of unloading when an export was running
MAXL> alter system unload application MORE2LIFE;
OK/INFO - 1054005 - Shutting down application MORE2LIFE.
OK/INFO - 1013432 - Unloading application [MORE2LIFE]: waiting for active requests to finish..
OK/INFO - 1056090 - System altered.
As you can see now the application is unloaded without the need for any additional MaxL statements
If you look in the application log it looks similar to running a MaxL kill request and then an unload.
Received Command [ParExport] from user [admin@Native Directory]
Canceling current requests.
Unloading application [MORE2LIFE]: waiting for active requests to finish.
Request [ParExport] from user [admin@Native Directory] was terminated by an admin user
Transaction [ 0x20002( 0x55914ccf.0xe2ce8 ) ] aborted due to status [1014030].
Parallel export failed due to status [1014030]
Transaction [ 0x30001( 0x55914ccf.0xe2ce8 ) ] aborted due to status [1005032].
RECEIVED SHUTDOWN COMMAND - SERVER TERMINATING
If you don’t want to use force which is the default to unload the application then you can add no_force to the statement and the the unload should behave in the way it used to.
MAXL> alter system unload application MORE2LIFE no_force;
OK/INFO - 1054005 - Shutting down application MORE2LIFE.
ERROR - 1013018 - Cannot unload database [BLOG] while user [admin@Native Directory] is performing database operation. Wait for the user to complete the operation, or ask the user to abort it. Log out all users and then unload the database..
So finally it is much simpler to unload applications which will certainly should help eliminate orphaned ESSSVR processes when shutting down Essbase.
Fast forward a few days and the Essbase 002 PSUs are released.
In the patch readme it contains the following:
Changes in Server Shutdown (21137674)
With this release, issuing a MaxL statement to unload an application cancels all active requests and database connections, and stops the application, unless you explicitly specify otherwise using the no_force option.
Updated MaxL Syntax
alter system unload application {all|APP-NAME} [no_force];
no_force Optional keyword. The no_force option causes Essbase to return an error if active requests are running on the application.
In the past it has sometimes been a bit of an issue shutting down applications when there are active requests, there are ways around it using a few different MaxL statements like kill requests and forcibly log out sessions but these can have varying success depending on the active requests and the Essbase version, I must admit from 11.1.2.3 it has been much easily to terminate requests like exports.
I will just go through the behaviour of using the unload application MaxL statement when there are active requests using the base install of 11.1.2.4
First of all with an active data load in progress then issuing the MaxL unload application statement.
MAXL> alter system unload application MORE2LIFE;
OK/INFO - 1054005 - Shutting down application MORE2LIFE.
ERROR - 1013018 - Cannot unload database [BLOG] while user [admin@Native Directory] is performing database operation. Wait for the user to complete the operation, or ask the user to abort it. Log out all users and then unload the database..
Ok so I added the following line to the same unload script:
alter system kill request on application MORE2LIFE;
Even though the request was killed the application was not unloaded
MAXL> alter system kill request on application MORE2LIFE;
OK/INFO - 1056090 - System altered.
MAXL> alter system unload application MORE2LIFE;
OK/INFO - 1054005 - Shutting down application MORE2LIFE.
ERROR - 1013018 - Cannot unload database [BLOG] while user [admin@Native Directory] is performing database operation. Wait for the user to complete the operation, or ask the user to abort it. Log out all users and then unload the database..
To get around this I added the logout session with force statement though I am sure it can be done with other statements.
MAXL> alter system logout session on application MORE2LIFE force;
OK/INFO - 1013220 - Supervisor [admin@Native Directory] has forced user [admin@Native Directory] to logout.
OK/INFO - 1051037 - Logging out user [admin@Native Directory], active for 11 minutes.
OK/INFO - 1056092 - Sessions logged out [1].
OK/INFO - 1056090 - System altered.
MAXL> alter system unload application MORE2LIFE;
OK/INFO - 1054005 - Shutting down application MORE2LIFE.
OK/INFO - 1056090 - System altered.
It was exactly the same behaviour when running a calculation, unload was no use without killing the request and the user, running a data export only required the request killing first before successfully unloading like the following:
MAXL> alter system kill request on application MORE2LIFE;
OK/INFO - 1056090 - System altered.
MAXL> alter system unload application MORE2LIFE;
OK/INFO - 1054005 - Shutting down application MORE2LIFE.
OK/INFO - 1056090 - System altered.
The essbase application log gives more of an insight to what is going on.
Received Command [ParExport] from user [admin@Native Directory]
Request [ParExport] from user [admin@Native Directory] was terminated by an admin user
Transaction [ 0x4000f( 0x55913c7a.0x6aef0 ) ] aborted due to status [1014030].
Parallel export failed due to status [1014030]
Transaction [ 0x4000e( 0x55913c7a.0x6aef0 ) ] aborted due to status [1005032].
Received Command [Unload Database]
RECEIVED SHUTDOWN COMMAND - SERVER TERMINATING
Right so moving on to the difference with the 002 patch applied.
It is worth taking note of the information in the essbase patch readme:
Oracle recommends using the same version of all Essbase portfolio products (Essbase, Essbase Administration Services, Hyperion Provider Services, and Essbase Studio) and components (server, client, runtime client, API, and JAPI). When only some Essbase portfolio products are included in a patch release, the last released versions of the products that are not included in the patch are supported.
What is also interesting is that Oracle have now started to include the following recommendation after applying essabse server patches.
After you apply a patch within the same release codeline, Oracle recommends as a best practice that you export the data from your databases, clear the data from the databases, and then reload the data.
I tested the same script with only an unload application statement and this time the application was shutdown successfully while running active requests like data loads, calculations and exports.
Here is an example of unloading when an export was running
MAXL> alter system unload application MORE2LIFE;
OK/INFO - 1054005 - Shutting down application MORE2LIFE.
OK/INFO - 1013432 - Unloading application [MORE2LIFE]: waiting for active requests to finish..
OK/INFO - 1056090 - System altered.
As you can see now the application is unloaded without the need for any additional MaxL statements
If you look in the application log it looks similar to running a MaxL kill request and then an unload.
Received Command [ParExport] from user [admin@Native Directory]
Canceling current requests.
Unloading application [MORE2LIFE]: waiting for active requests to finish.
Request [ParExport] from user [admin@Native Directory] was terminated by an admin user
Transaction [ 0x20002( 0x55914ccf.0xe2ce8 ) ] aborted due to status [1014030].
Parallel export failed due to status [1014030]
Transaction [ 0x30001( 0x55914ccf.0xe2ce8 ) ] aborted due to status [1005032].
RECEIVED SHUTDOWN COMMAND - SERVER TERMINATING
If you don’t want to use force which is the default to unload the application then you can add no_force to the statement and the the unload should behave in the way it used to.
MAXL> alter system unload application MORE2LIFE no_force;
OK/INFO - 1054005 - Shutting down application MORE2LIFE.
ERROR - 1013018 - Cannot unload database [BLOG] while user [admin@Native Directory] is performing database operation. Wait for the user to complete the operation, or ask the user to abort it. Log out all users and then unload the database..
So finally it is much simpler to unload applications which will certainly should help eliminate orphaned ESSSVR processes when shutting down Essbase.
Thanks for the post John! I need to write a stop script for the Essbase cluster I'm working on and the new functionality simplifies things quite a bit.
ReplyDeleteI like this feature, but I'm surprised the DEV team chose to change the default behavior and add a new keyword to get the old behavior. I would guess there are lots of scripts written that test for errors after the 'regular' unload, which may not now do what was intended.
ReplyDelete