Sterling SI/GIS: how to find a “quiet period”

If you are working for and international company which operates 24×7, you might need to find a quiet period of time when SI/GIS server is not processing a lot of documents (for example, for running heavy BackupService or do any kind of maintenance and so on). I use the following SQL queries to gather some usage stats:

Per day hours:

select count(*) as CNT, TO_CHAR(start_time, 'HH24') as DT from WF_INST_S
--where TO_CHAR(start_time, 'yyyy') = '2014'
group by TO_CHAR(start_time, 'HH24')
order by TO_CHAR(start_time, 'HH24')

Per week days:

select count(*) as CNT, TO_CHAR(start_time, 'D') as WeekDay from WF_INST_S
--where TO_CHAR(start_time, 'yyyy') = '2014'
group by TO_CHAR(start_time, 'D')
order by TO_CHAR(start_time, 'D')

Gennady Kim

Sterling SI/GIS: number of inbound and outbound documents grouped by customer

Just an SQL query, not nice and clear, but it works… It returns number of inbound/outbound documents for a period of time, grouped by customers and document types. Don’t forget that GIS/SI purges the old data so you cannot use it for “retrospective” reports…

SELECT * FROM (

SELECT
    TRUNC(TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS') - 1, 'HH24') as START_DATE,
    TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS') as END_DATE,
    SUM(cs5.VALUE) as CNT,
    cs1.VALUE as Partner,
    'Inbound' as Direction,
    cs2.VALUE as Status,
    cs4.VALUE as Transaction
FROM
correlation_set cs1,  -- TP
correlation_set cs2,  
correlation_set cs3, -- Direction
correlation_set cs4, -- Transaction
correlation_set cs5 -- TransactionCount
WHERE
    cs1.REC_TIME <= TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS')
    AND cs1.REC_TIME >= TRUNC(TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS') - 1, 'HH24')
AND cs1.NAME = 'InterchangeSenderID'
AND cs2.NAME = 'Status'
AND cs3.NAME = 'Direction'
AND cs3.VALUE = 'Inbound'
AND cs4.NAME = 'TransactionSetID'
AND cs4.VALUE != '997'
AND cs5.NAME = 'TransactionCount'
AND cs1.OBJECT_ID = cs2.OBJECT_ID
AND cs1.OBJECT_ID = cs3.OBJECT_ID
AND cs1.WF_ID = cs4.WF_ID
AND cs1.OBJECT_ID != cs4.OBJECT_ID -- to exclude duplicate EDIFACTs
AND cs4.OBJECT_ID = cs5.OBJECT_ID -- important!
GROUP BY cs1.VALUE, cs2.VALUE, cs4.VALUE

UNION ALL

SELECT
    TRUNC(TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS') - 1, 'HH24') as START_DATE,
    TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS') as END_DATE,
    SUM(cs5.VALUE) as Cnt,
    cs1.VALUE as Partner,
    cs2.VALUE as Direction,
    cs3.VALUE as Status,
    cs4.VALUE as Transaction
FROM
    correlation_set cs1,  -- TP
    correlation_set cs2, -- direction
    correlation_set cs3, -- status
    correlation_set cs4, --transaction id
    correlation_set cs5 -- # of transactions
WHERE
    cs1.REC_TIME <= TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS')
    AND cs1.REC_TIME >= TRUNC(TO_DATE('2012-08-02 07:00:00', 'yyyy-mm-dd HH24:MI:SS') - 1, 'HH24')
AND cs1.NAME = 'InterchangeReceiverID'
    AND cs2.NAME = 'Direction'
    AND cs2.VALUE = 'Outbound'
    AND cs3.NAME = 'Status' 
    AND cs4.NAME = 'TransactionSetID' 
    AND cs5.NAME = 'TransactionCount' 
    AND cs4.VALUE != '997'
    AND cs1.object_id = cs2.object_id
    AND cs1.object_id = cs3.object_id
    AND cs1.object_id = cs4.object_id
    AND cs1.object_id = cs5.object_id
GROUP BY cs1.VALUE, cs2.VALUE, cs3.VALUE, cs4.VALUE

)

ORDER BY Partner, Transaction, Status

Gennady Kim

Sterling GIS/SI maps and Windows locale

If your OS is Windows and you’re using some “exotic” system locale (for example, it’s Russian on my laptop) you might have problems uploading your maps to GIS/SI. “Your compiled map file is invalid. Please try again.”:

It’s a known issue, I had it with GIS 4.0 and I have it with SI 5.4.2. On IBM Support site they call it “The Regional and Language Settings” but actually it’s “System locale”. To fix this problem you’ll need to change your system locale to “English (United States)” for example and then re-compile your map(s).

Gennady Kim

Problems with GIS/SI index/backup/purge services: consequences

As I wrote in my previous post, there were some problems with the BackupService and it was turned off for about 2 months. The DB has grown up dramatically and we’ve started having problems with some BPs. So, we had to re-build a couple of indexes in our GIS DB (Oracle) as it described here SQL queries taking 100% CPU in the database. It solved our problems perfectly.

01 May 2014: There have been some changes on the IBM portal and it looks it doesn’t work anymore – all the links are broken

02 Jul 2014: The blog with this article has been deleted. As I remember, one of 2 options were supposed to do:

1. Rebuild indexes on CORRELATION_SET
2. On CORRELATION_SET table – drop index SCI_IDX_57 and create new one with column (OBJECT_ID, KEY_ID, VALUE_UPPER)

Gennady Kim