Index Rebuild Script for IPE database!!

For iProcess engine DB maintenance , you can schedule the Index rebuiding script on periodic basis.

Check the Index space before rebuilding
select sum(bytes)/1024/1024,segment_name from dba_segments
where tablespace_name='Data_Tablespace_Name'
and segment_type='INDEX' group by segment_name;

Rebuild the IndexDECLARE
cursor c_index IS
SELECT index_name
FROM all_indexes"
WHERE index_name not like '%AQ%'"
and tablespace_name = 'Data_Tablespace_Name';

BEGIN
FOR idx_Rec in c_index LOOP
EXECUTE IMMEDIATE 'alter index '||idx_rec.index_name||' rebuild online';
END LOOP;
END;
/

Check the status of Index Rebuild
select object_name,last_ddl_time from user_objects where OBJECT_TYPE='INDEX' order by last_ddl_time;

Check the Index space after rebuilding
select sum(bytes)/1024/1024,segment_name from dba_segments
where tablespace_name='Data_Tablespace_Name'
and segment_type='INDEX' group by segment_name;

Note : Ensure that index rebuild is not happening on AQ tables and AQ indexes.

Extra MBox setup for purge!!

While purging the huge number of case ,Please be aware that Archive logs will be Huge - Ask your DBA to compress archive logs during the purge activity.

and Going forward you can schedule the purge activity regularly.

To Add Extra MBox - Run the below query on the database side to create extra AQ Queue/table:

BEGIN
DBMS_AQADM.CREATE_QUEUE_TABLE
(
QUEUE_TABLE => 'extramboxtable'
,QUEUE_PAYLOAD_TYPE => 'swlocalmsg'
,COMPATIBLE => '8.1.3'
,STORAGE_CLAUSE => 'TABLESPACE Data_Tablespace'
,COMMENT => 'default background mbox'
);
END;
/

BEGIN
SYS.DBMS_AQADM.CREATE_QUEUE
(
QUEUE_NAME => 'extramboxqueue'
,QUEUE_TABLE => 'extramboxtable'
,QUEUE_TYPE => SYS.DBMS_AQADM.NORMAL_QUEUE
,MAX_RETRIES => 12
,RETRY_DELAY => 300
,RETENTION_TIME => 0
);
END;
/

BEGIN
SYS.DBMS_AQADM.START_QUEUE
(
QUEUE_NAME => 'extramboxqueue'
,ENQUEUE => TRUE
,DEQUEUE => TRUE
);
END;
/

BEGIN
DBMS_AQADM.GRANT_QUEUE_PRIVILEGE ( PRIVILEGE=> 'all',
queue_name=> 'extramboxqueue',
grantee=> 'IPE_DBSchema_Owner',
grant_option=> FALSE
);
END;
/

BEGIN
DBMS_AQADM.GRANT_QUEUE_PRIVILEGE ( PRIVILEGE=> 'all',
queue_name=> 'extramboxqueue',
grantee=> 'IPE_DBUser',
grant_option=> FALSE
);
END;
/


Login as IPE BG User and check how many Mboxes and queues are there :

cd $SWDIR\util

./swadm show_mboxsets v

--------------------------------------------------------------------------------

Mboxset ID Mboxset Name Queue Type Queues in MBOX Set

--------------------------------------------------------------------------------

1 BGMBSET Local 1,2

2 WMDMBSET Local 3,4

3 WISBGMBSET1 Local 1

4 WISBGMBSET2 Local 2

5 PREDICTMBSET Local 6


./swadm show_queues

--------------------------------------------------------------------------------

Queue ID Queue Name Queue Type Queue Desc

--------------------------------------------------------------------------------

1 BGMBOX1 Local 0001::bgmboxtable1:bgmboxqueue1

2 BGMBOX2 Local 0001::bgmboxtable2:bgmboxqueue2

3 WISMBOX1 Local 0001::wismboxtable1:wismboxqueue1

4 WISMBOX2 Local 0001::wismboxtable2:wismboxqueue2

5 PREDICTMBOX1 Local 0001::predictmboxtable1:predictmboxqueue1

6 PREDICTMBOX2 Local 0001::predictmboxtable2:predictmboxqueue2


Now add Extra queue and Mbox for purging :

./swadm add_queue extraMBOXQueue Local 0001::extramboxtable:extramboxqueue


Create new MBOX set

./swadm add_mboxset extraMBOX Local

Show MBoxset - note extraMBOX has no queues and it is number '6'

./swadm show_mboxsets v
--------------------------------------------------------------------------------
Mboxset ID Mboxset Name Queue Type Queues in MBOX Set
--------------------------------------------------------------------------------
1 BGMBSET Local 1,2
2 WMDMBSET Local 3,4
3 WISBGMBSET1 Local 1
4 WISBGMBSET2 Local 2
5 PREDICTMBSET Local 6
6 extraMBOX Local


Show Queues - note the new queue is number '7'

./swadm show_queues
--------------------------------------------------------------------------------
Queue ID Queue Name Queue Type Queue Desc
--------------------------------------------------------------------------------
1 BGMBOX1 Local 0001::bgmboxtable1:bgmboxqueue1
2 BGMBOX2 Local 0001::bgmboxtable2:bgmboxqueue2
3 WISMBOX1 Local 0001::wismboxtable1:wismboxqueue1
4 WISMBOX2 Local 0001::wismboxtable2:wismboxqueue2
5 PREDICTMBOX1 Local 0001::predictmboxtable1:predictmboxqueue1
6 PREDICTMBOX2 Local 0001::predictmboxtable2:predictmboxqueue2
7 extraMBOXQueue Local 0001::extramboxtable:extramboxqueue


Add queue 7 to mboxset 6 ./swadm add_queue_to_mboxset 6 7

Check that the queue is now part of MBoxset 7

./swadm show_mboxsets v
--------------------------------------------------------------------------------
Mboxset ID Mboxset Name Queue Type Queues in MBOX Set
--------------------------------------------------------------------------------
1 BGMBSET Local 1,2
2 WMDMBSET Local 3,4
3 WISBGMBSET1 Local 1
4 WISBGMBSET2 Local 2
5 PREDICTMBSET Local 6
6 extraMBOX Local 7



Now you can assign the dedicated BG process to this extra MBox so that it won't disturb the ongoing transactions and won't slow down the speed.
SHow the current attribute list for the BG attributes (Read/Write)


cd $SWDIR\util

./swadm show_all_attributes | grep _BG


0 ALL 0 MBSET_READ_BG I 1

0 ALL 0 MBSET_WRITE_BG I 1

0 BG 1 MBSET_READ_BG I 3

0 BG 2 MBSET_READ_BG I 3

0 BG 3 MBSET_READ_BG I 4

0 BG 4 MBSET_READ_BG I 4

0 BG 5 MBSET_READ_BG I 3

0 BG 6 MBSET_READ_BG I 3

0 BG 7 MBSET_READ_BG I 4

0 BG 8 MBSET_READ_BG I 4

0 WIS 1 MBSET_WRITE_BG I 3

0 WIS 2 MBSET_WRITE_BG I 4

0 WIS 3 MBSET_WRITE_BG I 3

0 WIS 4 MBSET_WRITE_BG I 4


The above attributes shows that - there are total 8 BG(s), so I am picking up the last 3 processes ( BG 6 , BG 7 , BG 8) to dedicate to the extra mboix set. If you have more than 8 BG(s) then you can also pick up the last three processes to dedicate to the extra mbox. ( for example if you have 20 BG(s) then you can pick up BG 18, BG 19 and BG 20 ). This is just a convention.


Now stop the engine:

$SWDIR/bin/swstop -f

$SWDIR/bin/swstop -p


Reset the BG attributes for BG 6, BG 7 and BG 8

cd $SWDIR/util

./swadm DELETE_ATTRIBUTE 0 BG 6 MBSET_READ_BG

./swadm DELETE_ATTRIBUTE 0 BG 7 MBSET_READ_BG

./swadm DELETE_ATTRIBUTE 0 BG 8 MBSET_READ_BG


Note : I have deleted the MBSET_READ_BG attribute for BG 6, BG 7 and BG 8.


Now set BG 6 as normal to read the WISMBOX2 (Mbox Number is 4)


Note :Your Logical Machine ID can be different May be 0 or 1 so use accordingly.


./swadm set_attribute 0 BG 6 MBSET_READ_BG 4


Now set BG7 and BG8 to read from MBox Number 6 , for Purging (Which we have added recently)


./swadm set_attribute 1 BG 7 MBSET_READ_BG 6

./swadm set_attribute 1 BG 8 MBSET_READ_BG 6


Configure the command "SWBATCH" to write to MBox 6 (Purging one)


./swadm set_attribute 0 SWBATCH 0 MBSET_WRITE_BG 6


Now you can start the engine. If anything goes wrong then you can revert all commands.

Assigning WIS to Workqueues

While deploying Queues.txt - you can add one additional attribute "SW_WISINST" in the file and this is TEXT variable and default value should be blank. If you will add "1" to the value then WIS 1 will be the dedicated to this particular Queue.


In below example - I have added the SW_WISINST to queue "MY_BUSY_WORKQUEUE" and value is 1. So WIS 1 is dedicated to "MY_BUSY_WORKQUEUE".


After delpoying the below code - you can verify the assigned WIS queue details by running the command "$SWDIR/util/plist -w"(i believe this is the right command).

G+MY_BUSY_WORKQUEUE

V+MY_BUSY_WORKQUEUE,DESCRIPTION,BUSYQUEUE

V+MY_BUSY_WORKQUEUE,FUNCTION_ID,TESTID

V+MY_BUSY_WORKQUEUE,LANGUAGE,english

V+MY_BUSY_WORKQUEUE,MENUNAME,SW_GROUP

V+MY_BUSY_WORKQUEUE,QSUPERVISORS,

V+MY_BUSY_WORKQUEUE,SORTMAIL,PROCEDURE

V+MY_BUSY_WORKQUEUE,SW_DOMAIN,

V+MY_BUSY_WORKQUEUE,SW_WISINST,1

(Note – This Property can be used to dedicate the WIS queue. In above example I have dedicated WIS 1 to MY_BUSY_WORKQUEUE. If this paramenter is empty then MY_BUSY_WORKQUEUE will pick up the random WIS accordingly.

V+MY_BUSY_WORKQUEUE,USERFLAGS,

V+MY_BUSY_WORKQUEUE,WISCACHE,

Query for Closed Cases!!

To query the closed cases details in iProcess Engine database - You can run the following sql statement :

Note : If Is_Dead=0 then case is active and if Is_Dead=1 then case is closed.

To Reterive the closed cases:

set pages 1000 lines 100 term off feed off trims on
select pi.proc_name "Procedure Name"
, trunc(ci.next_deadline) "Closed Date"
, count(1) "Count"
from case_information ci
left join proc_index pi
on ( ci.proc_id = pi.proc_id )
where ci.is_dead = 1
and ci.is_subcase = 0
group by pi.proc_name, trunc(ci.next_deadline)
order by trunc(ci.next_deadline);

To Reterive the active cases:

set pages 1000 lines 100 term off feed off trims on
select pi.proc_name "Procedure Name"
, trunc(ci.next_deadline) "Closed Date"
, count(1) "Count"
from case_information ci
left join proc_index pi
on ( ci.proc_id = pi.proc_id )
where ci.is_dead = 0
and ci.is_subcase = 0
group by pi.proc_name, trunc(ci.next_deadline)
order by trunc(ci.next_deadline);

Maintenance Jobs for IPEngine

To make iProcess Engine stable - We should regularly perform the Maintenance jobs:

1. Regularly Deleteing the Old procedure versions (Recommended to keep last 4 versions of procedures)
2. Purging the Closed cases on Periodically basis
3. Regular Index Rebuilding for iProcess Engine database
4. DBMS STATS Gather on database Schemas
5. Archiving the logs from $SWDIR/logs
6. Taking the Regular backups of IPE Engines
7. Weekly Restart of IPEngine

Many more....

Message Jump!!

To Make any application/case jump in iProcess Engine, Please follow the below steps:

Close the Procedure Name:
$SWDIR/bin/swutil CLOSE {Procedure Name} {Case Number}-y

Move the Case to StepName:
$SWDIR/bin/swutil EVENT {Procedure Name} R{Case Nummbert} {Step Name}

Messages jammed in BGQueue!!

There is a typical scenario that when messages are not getting moved from the Background queue and you can see that message count is getting increased.

Queue ID : 1
$SWSIR/util/swadm count_messages 1
Queue ID : 2
$SWSIR/util/swadm count_messages 2

Please follow the steps given below :

1. Stop the iProcess Engine
2. Run the following query on IPE Database - Once this is completed.
3. Start iProcess Engine.

SQL Query:

DECLARE

dequeue_options DBMS_AQ.dequeue_options_t;
message_properties DBMS_AQ.message_properties_t;
message_handle RAW (16);
MESSAGE swlocalmsg;
STATUS VARCHAR2 (10);
enqueue_options DBMS_AQ.enqueue_options_t;

-- Build cursor of exception messages, from both queues
CURSOR c1 IS
SELECT msgid, user_data, enq_time, q_name FROM BGMBOXTABLE1 WHERE state = 0
UNION ALL
SELECT msgid, user_data, enq_time, q_name FROM BGMBOXTABLE2 WHERE state = 0;

xt swlocalmsg;
v_head VARCHAR2(20);
v_user VARCHAR2(20);
v_inst VARCHAR2(20);
v_in INTEGER;
v_io INTEGER;

BEGIN
-- enable dequeue for exception queues
--DBMS_AQADM.START_QUEUE ('aq$_bgmboxtable1_e', FALSE, TRUE);
--DBMS_AQADM.START_QUEUE ('aq$_bgmboxtable2_e', FALSE, TRUE);

-- Open exception cursor
FOR x_c1 IN c1 LOOP

xt := x_c1.user_data;

-- ################
-- Breakdown the message
-- ################
v_in := INSTR(xt.msginfo,'^');
v_io := v_in;

v_head := SUBSTR(xt.msginfo,1,(v_in - 1));
v_in := INSTR(xt.msginfo,'^',(v_in + 1));
v_user := SUBSTR(xt.msginfo, (v_io + 1), ((v_in - v_io) - 1));

v_io := v_in;
v_in := INSTR(xt.msginfo,'^',(v_in + 1));
v_inst := SUBSTR(xt.msginfo, (v_io + 1), ((v_in - v_io) - 1));


IF v_inst = 'PROCCASE' THEN
dequeue_options.msgid := x_c1.msgid;
dbms_output.put_line( v_inst||' '||dequeue_options.msgid);
dBMS_AQ.dequeue (queue_name => x_c1.q_name,
dequeue_options => dequeue_options,
message_properties => message_properties,
payload => MESSAGE,
msgid => message_handle
);
END IF;
END LOOP;
COMMIT;
END;
/