In IBM Sterling B2B Integrator 5263 and higher, a new column called CREATION_DATE was added to TRANS_DATA. If you upgrade to 5263, all the old records in TRANS_DATA will have the CREATION_DATE set to the date of the upgrade.
The following SQL query (oracle and db2) will give you TRANS_DATA monthly distribution based on the CREATION_DATE column:
select to_char(CREATION_DATE, 'MM-YYYY'), count(*) from trans_data group by to_char(CREATION_DATE, 'MM-YYYY') order by 1
The Oracle PL/SQL procedure below can be used to delete the old records in trans_data that are older than a given number of days in small batches to minimize the overhead.
WARNING: THIS PROCEDURE WILL DELETE ALL YOUR PAYLOAD, MESSAGES, TRANSACTION DETAILS OLDER THAN DaysOld. TAKE A BACKUP OF TRANS_DATA.
IF YOU ARE NOT NOT SURE, PLEASE POST A COMMENT IN THIS PAGE!
-- STORED PROCEDURE TO DELETE OLD RECORDS FROM TRANS_DATA
-- DO NOT RUN WITHOUT DBA ACCESS TO MONITOR THE INSTANCE
-- DDL TO CREATE DEBUGGING TABLE -----> PLEASE RUN FOR THE FIRST TIME:
-- create TABLE CLEANUP_DEBUG(ROWS_DELETED NUMBER,TOTAL NUMBER,START_TIME TIMESTAMP,END_TIME TIMESTAMP,DESCRIPTION VARCHAR2(60))
-- to clean the debug table add
-- truncate table CLEANUP_DEBUG;
--Index to help with the procedure performance:
-- create index TD_PERF_1 on TRANS_DATA( CREATION_DATE)
DECLARE
nCount NUMBER:=0;
iCount NUMBER:=0;
batchSize NUMBER:=5000;
daysOld NUMBER:=30;
sql1 VARCHAR2(2000);
starttime timestamp;
BEGIN
sql1 := 'DELETE FROM TRANS_DATA WHERE CREATION_DATE < sysdate - ' || daysOld ||' and rownum <= ' || batchSize;
LOOP
starttime:=sysdate;
EXECUTE IMMEDIATE sql1;
nCount := sql%rowcount;
iCount:=iCount+nCount;
insert into CLEANUP_DEBUG values (batchSize,iCount,starttime, sysdate,'DELETE old TRAN_DATA records ');
commit;
EXIT WHEN nCount = 0;
END LOOP;
end;You can run this PL/SQL procedure in SQL Developer or TOAD. To monitor its progress, query the CLEANUP_DEBUG table.
Remember to run SQL queries returning large result-sets OUTSIDE of the B2B Integrator application SQL manager. Use external tools like Oracle SQL developer.
Check more useful SQL queries:
- Useful SQL queries for Sterling B2B Integrator.
- TRANS_DATA related SQL queries.
- Workflow statuses explained with SQL queries: Active, Halted, Halting BPs ...
- Filegateway related SQL queries.
- Communication and adapter stats SQL.
- Slow services and steps SQL.
If you are struggling to find some information in the B2B Integrator Database, please post a comment below. You can also share your useful SQL for Integrator!
2 Pingbacks