Oracle's Application Development Framework ("ADF") contains a component called Business Components for Java ("BC4J"). BC4J simplifies the development of complex, database-centric applications by providing a set of tools and runtime infrastructure for building, deploying, and running business components. It is a core part of the Oracle ADF and leverages the MVC architecture to separate concerns and promote reusability and maintainability in enterprise application development. As mentioned in this blog post:
There is a lot of detail behind the AM [Application Module] State Management Facility, but for our purposes we need only know that the module allows an application to store ("passivate") pending transaction state as XML data (also known as a snapshot). As you might expect, an application can also restore ("activate") pending transaction state from a previously-stored snapshot.
What does this mean? In short, the BC4J component of ADF provides for session state management, which may be stored in the file system (on earlier versions of ODEE) or in a database (in later versions of ODEE). Wondering which is active in your environment? If you've seen BC* files sitting in your WebLogic domain directory, then you are configured for filesystem state management. Otherwise, you are configured to store session state in a database. If you want to know more about passivation and activation in ADF applications, read further here.
Cleanup
Under typical conditions, the session state data is managed automatically, and the rows in the temporary storage tables (PS_TXN and PCOLL_CONTROL) are cleaned up automatically. However, there are cases in which these persistent state snapshots may accumulate over time: if the managed server was shut down in an abnormal way, there was a failure, or if there has been some improper logout. This problem may be further compounded in performance testing environments. Ultimately there will be orphaned records in this table that will need to be cleaned up.
Oracle provides a SQL script to help with periodic cleanup of the state management table. The script is provided with the Fusion Middleware Infrastructure ("FMW") installer, and can be located in $FMW_HOME/oracle_common/common/sql directory. Herein you'll find a number of SQL scripts that you can investigate at your leisure; the file of interest is adfbc_purge_statesnapshots.sql. Open the file in a text editor and you'll find some information about the usage of this script:
Rem NAME Rem bc4jcleanup.sql - Utilities to clean temporary BC4J storage Rem Rem DESCRIPTION Rem Rem This package contains procedures to clean out rows Rem in the database used by BC4J to store user session state Rem and storage used by temporary persistent collections. Rem NOTES Rem Rem You can schedule periodic cleanup of your BC4J temporary Rem persistence storage by submitting an invocation of the Rem appropriate procedure in this package as a database job. Rem Rem You can use an anonymous PL/SQL block like the following Rem to schedule the execution of bc4j_cleanup.session_state() Rem to run starting tomorrow at 2:00am and each day thereafter Rem to cleanup sessions whose state is over 1 day (1440 minutes) old.
From this we can deduce a few things:
- The script is specific to Oracle databases.
- The script creates a package of procedures that can be called to perform cleanup, using one of two parameters: a number of minutes or a date. The procedure will remove snapshot data that is older than the given parameter.
- The script shows an example of creating an anonymous PL/SQL block to schedule a job that runs the package daily to delete snapshots that are over one day old.
- The script does not provide coverage for other database types.
If your ODEE environment uses Oracle Database, then you need only follow the instructions in the script to create a job, or provide the script to your DBA and have them create the job for you in a manner that follows your organization's policies. However, If your ODEE environment uses Microsoft SQL Server, you'll need to roll your own procedure to clean up the snapshot table.
Below you'll find SQL statements to create procedures for SQL Server that are equivalent to the packages provided by Oracle. Replace DMAKER with the name of your database where you have installed the Documaker Administrative schema; and replace DMKR_ADMIN with the name of the Administrative schema if it is not using the default. Execute with your favorite SQL tool, and you should see four new stored procedures created in your database.
-- Use the appropriate database
USE DMAKER;
GO
-- Procedure to delete session state older than a given date
IF OBJECT_ID('Delete_Old_Session_State', 'P') IS NOT NULL
DROP PROCEDURE Delete_Old_Session_State;
GO
CREATE PROCEDURE Delete_Old_Session_State
@olderThan DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @cur_Id DECIMAL(20,0);
DECLARE @tmpval DECIMAL(20,0);
DECLARE cur_Session CURSOR FOR
SELECT id
FROM DMKR_ADMIN.PS_TXN
WHERE creation_date < @olderThan;
OPEN cur_Session;
FETCH NEXT FROM cur_Session INTO @cur_Id;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
-- Lock the row
SET @tmpval = NULL;
EXEC sp_executesql
N'SELECT @tmpval = 1
FROM DMKR_ADMIN.PS_TXN WITH (ROWLOCK, UPDLOCK, NOWAIT)
WHERE id = @cur_Id',
N'@cur_Id DECIMAL(20,0), @tmpval DECIMAL(20,0) OUTPUT',
@cur_Id = @cur_Id, @tmpval = @tmpval OUTPUT;
-- Check if the row was successfully locked
IF @tmpval IS NOT NULL
BEGIN
-- Delete the row
DELETE FROM DMKR_ADMIN.PS_TXN WHERE id = @cur_Id;
COMMIT;
END
END TRY
BEGIN CATCH
-- Handle errors (ignore rows that cannot be locked)
IF ERROR_NUMBER() != 1205 -- Not a lock timeout
BEGIN
-- Handle other errors
PRINT 'An error occurred: ' + ERROR_MESSAGE();
END;
END CATCH;
FETCH NEXT FROM cur_Session INTO @cur_Id;
END
CLOSE cur_Session;
DEALLOCATE cur_Session;
END;
GO
-- Procedure to delete session state older than a given number of minutes
IF OBJECT_ID('Delete_Old_Session_State_Minutes', 'P') IS NOT NULL
DROP PROCEDURE Delete_Old_Session_State_Minutes;
GO
CREATE PROCEDURE Delete_Old_Session_State_Minutes
@olderThan_minutes INT
AS
BEGIN
SET NOCOUNT ON;
-- Ignore negative values for olderThan_minutes
IF @olderThan_minutes < 0
BEGIN
RETURN;
END;
DECLARE @olderThan DATETIME;
SET @olderThan = DATEADD(MINUTE, -@olderThan_minutes, GETDATE());
EXEC Delete_Old_Session_State @olderThan;
END;
GO
-- Procedure to cleanup persistent collections older than a given date
IF OBJECT_ID('Cleanup_Persistent_Collections', 'P') IS NOT NULL
DROP PROCEDURE Cleanup_Persistent_Collections;
GO
CREATE PROCEDURE Cleanup_Persistent_Collections
@olderThan DATETIME
AS
BEGIN
SET NOCOUNT ON;
DECLARE @cur_Tabname NVARCHAR(80);
DECLARE @spillTableName NVARCHAR(80);
DECLARE @sql NVARCHAR(MAX);
DECLARE cur_PCollControl CURSOR FOR
SELECT tabname
FROM DMKR_ADMIN.PCOLL_CONTROL
WHERE updatedate < @olderThan
AND tabname <> 'PS_TXN';
OPEN cur_PCollControl;
FETCH NEXT FROM cur_PCollControl INTO @cur_Tabname;
WHILE @@FETCH_STATUS = 0
BEGIN
BEGIN TRY
-- Lock the "old" PColl Control Row, selecting spill-over table name
SET @spillTableName = NULL;
EXEC sp_executesql
N'SELECT @spillTableName = tabname
FROM DMKR_ADMIN.PCOLL_CONTROL WITH (ROWLOCK, UPDLOCK, NOWAIT)
WHERE tabname = @cur_Tabname',
N'@cur_Tabname NVARCHAR(80), @spillTableName NVARCHAR(80) OUTPUT',
@cur_Tabname = @cur_Tabname, @spillTableName = @spillTableName OUTPUT;
-- Check if the row was successfully locked
IF @spillTableName IS NOT NULL
BEGIN
-- Delete the row keeping track of temporary spill-over table
DELETE FROM DMKR_ADMIN.PCOLL_CONTROL WHERE tabname = @cur_Tabname;
-- Drop the temporary spill-over table
SET @sql = N'DROP TABLE ' + QUOTENAME(@spillTableName);
EXEC sp_executesql @sql;
-- Drop the temporary spill-over table's key table
SET @sql = N'DROP TABLE ' + QUOTENAME(@spillTableName + '_ky');
EXEC sp_executesql @sql;
COMMIT;
END
END TRY
BEGIN CATCH
-- Handle errors (ignore rows that cannot be locked)
IF ERROR_NUMBER() != 1205 -- Not a lock timeout
BEGIN
-- Handle other errors
PRINT 'An error occurred: ' + ERROR_MESSAGE();
END;
END CATCH;
FETCH NEXT FROM cur_PCollControl INTO @cur_Tabname;
END
CLOSE cur_PCollControl;
DEALLOCATE cur_PCollControl;
END;
GO
-- Procedure to cleanup persistent collections older than a given number of days
IF OBJECT_ID('Cleanup_Persistent_Collections_Days', 'P') IS NOT NULL
DROP PROCEDURE Cleanup_Persistent_Collections_Days;
GO
CREATE PROCEDURE Cleanup_Persistent_Collections_Days
@olderThan_days INT
AS
BEGIN
SET NOCOUNT ON;
-- Ignore negative values for olderThan_days
IF @olderThan_days < 0
BEGIN
RETURN;
END;
DECLARE @olderThan DATETIME;
SET @olderThan = DATEADD(DAY, -@olderThan_days, GETDATE());
EXEC Cleanup_Persistent_Collections @olderThan;
END;
GO
Let's try a quick test. First let's see how many rows are in the PS_TXN table, then we'll run the stored procedure, and then count again.
select count(*) as [rowcoount] from DMKR_ADMIN.PS_TXN; rowcount ----------- 333 -- Deletes rows older than 24 hours EXEC Delete_Old_Session_State_Minutes 1440; select count(*) as [rowcount] from DMKR_ADMIN.PS_TXN; rowcount ----------- 0
Success! Now, let's try the collections table stored procedure. I know that there are some more current records here, so not all of them should be cleaned up.
select count(*) as [rowcount] from DMKR_ADMIN.PCOLL_CONTROL; rowcount ----------- 2 -- Deletes rows older than seven days. EXEC Cleanup_Persistent_Collections_Days 7; select count(*) as [rowcount] from DMKR_ADMIN.PCOLL_CONTROL; rowcount ----------- 1
Again, success! Now that we know this works, we should set up a scheduled job to periodically maintain these tables. Using SQL Server Management Studio or your favorite SQL tool, run the following script, adjusting as needed for your environment as indicated in bold:
USE msdb;
GO
-- Delete the job if it already exists
IF EXISTS (SELECT 1 FROM msdb.dbo.sysjobs WHERE name = 'BC4J Cleanup Procedures')
BEGIN
EXEC msdb.dbo.sp_delete_job @job_name = 'BC4J Cleanup Procedures';
END
GO
-- Create the job with appropriate parameters. Adjust to your needs.
EXEC msdb.dbo.sp_add_job
@job_name = N'BC4J Cleanup Procedures',
@enabled = 1,
@notify_level_eventlog = 2,
@notify_level_email = 2,
@notify_level_netsend = 2,
@notify_level_page = 2,
@delete_level = 0;
GO
-- Add a step to the job
EXEC msdb.dbo.sp_add_jobstep
@job_name = N'BC4J Cleanup Procedures',
@step_name = N'Execute Cleanup Procedures',
@subsystem = N'TSQL',
@command = N'
EXEC DMAKER.dbo.Delete_Old_Session_State_Minutes 1440;
EXEC DMAKER.dbo.Cleanup_Persistent_Collections_Days 7;',
@database_name = N'DMAKER',
@on_success_action = 1,
@on_fail_action = 2;
GO
-- Add a schedule to the job
EXEC msdb.dbo.sp_add_jobschedule
@job_name = N'BC4J Cleanup Procedures',
@name = N'Daily Schedule',
@enabled = 1,
@freq_type = 4, -- Daily
@freq_interval = 1,
@active_start_time = 000000; -- Midnight
GO
-- Add the job to the SQL Server Agent
EXEC msdb.dbo.sp_add_jobserver
@job_name = N'BC4J Cleanup Procedures';
GO
With this completed, you know no longer have to worry about filling up table space with old data. Be sure to observe the operation and performance of these procedures and jobs to ensure it is to functioning to your requirements. These are provided here without support. If you have questions or comments, feel free to comment below or ask a question over at the Documaker community.