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.



For training on TIBCO IProcess mail us at [email protected]