Sterling GIS/SI: DB Structure: Maps

Lately, there was a discussion about “Bulk download of SI Maps” on LinkedIn. I shared my experience – working on a migration project from GIS to SI we needed to download about 500+ maps, compile them with new MapEditor and upload them back. It would’ve taken a lot of time doing it manually, so I decided to get the maps from DB, compile them with MapEditor command line and then upload them into the new system.

Of course, maps should be stored in some kind of CVS – SVN, Git, etc. But we didn’t have this option – all the maps were on the GIS server.

I wrote a simple application on Java – it connected to Oracle DB, downloaded maps (see my SQL below), unzip them and then another application compiled the maps and ran unit tests (it’s a different story). Finally, another application uploaded compiled maps into the new system (now I know we could’ve used native or script, but back then I didn’t know about it).

So, here is an SQL to get the map in as a blob (Oracle)

select m.*, dt.DATA_OBJECT from map_versions mv, map m, DATA_TABLE dt 
  mv.map_name like '{here your map name, without extension}' 
  and m.map_name = mv.map_name 
  and m.map_version = mv.default_version 
  and m.UNCOMPILED_MAP = dt.data_id

so, there are two main tables – Map and Map_Versions. Maps and Txos are stored in DATA_TABLE.

Gennady Kim

GIS/SI Map Editor: short overview

Map Editor looks like all the other mapping tools – source structure on the left side and target structure on the right. There are 3 ways for mapping the data from the source to target structure:

Simple Links – when you simply connect field from the input to the output. You can drag&drop a field from a left side to the right. If you are trying to connect fields with the different types (String to Number or Date), or from different levels (Header to Line Item) or from repeatable element to non-repeatable (and vice versa) it will show you an alert message:

Continue reading

Sterling GIS/SI: DB: Correlation Set

And today let me show you how the Correlation Set is organized in the DB. Correlation data might be very helpful if you need to gather some stats about i/o documents, find test data and so on. But don’t forget that GIS/SI deletes record from this table from time to time to prevend the DB from growing.


select cs.NAME, cs.VALUE, cs.WF_ID, cs.* from CORRELATION_SET cs
        cs.OBJECT_ID = '1234567890'

A screenshot:

Gennady Kim

Sterling GIS/SI: DB: Workflow

Now let me show how to find WorkFlow instances. For example, in my previous post about the DB structure I described how to find a BP in the DB. When GIS/SI executes a BP, it creates a set of records in the DB – you can find them through “BP/Monitor/Current Processes” or “BP/Monitor/Central Search” or “Advanced Search”. The main table for these records is WF_INST_S. Steps are stored in WORKFLOW_CONTEXT.

For example (see the screenshot below), your BP instance ID is 12345. To find the appropriate DB records run the following DB queries:

select * from WF_INST_S wis
        wis.WORKFLOW_ID = 12345;
select wc.STEP_ID, wc.SERVICE_NAME, wc.* from WORKFLOW_CONTEXT wc
        wc.WORKFLOW_ID = 12345;

A screenshot:

Gennady Kim

Sterling GIS/SI: the DB growth, part 2

Just an update about an old post about the DB growth. We got the same locks again in June, in July and finally in August. One time – incident, two times – co-incident, and 3 times – a rule. So, I decided to dive into the problem. The DB is on MS SQL Server so I opened the System Log and tried to find all the reboots of this machine. I used Event ID #6006 – Event Log has stopped. And it turned out that the locks occurred exactly on the same days as the server reboots. I didn’t know about these reboots – I believe we’ll have to discuss them with the SQL guys who are responsible for the maintenance.

How to prevent this from happening in the future ? Of course, we can unlock the DB manually every time the SQL server is rebooted. But maybe it would be easier to create a SQL Job which will check the LOCKS table for the old locks from the Purge service… I haven’t decided yet.

Gennady Kim

Sterling GIS/SI: DB: Business Processes

In my previous post I described how the Envelopes’ records are organized in the DB. Now I’m going to describe the Business Processes. They have similar structure – main table (WFD) and versions (WFD_VERSIONS). But also there are more tables – for example WFD_XML and DATA_TABLE. WFD_XML is a “link” between BPs and XML content/source of the BPs. DATA_TABLE contains data in binary format – maps, BPs and so on. Not sure for 100% about 5.2, but in 4.3 these binary fields contain serialized Java objects.

OK, SQL queries – just to give you an idea:

select w.* from WFD w, WFD_VERSIONS wv
        w.WFD_ID = wv.WFD_ID
        and w.WFD_VERSION = wv.WFD_VERSION
        and w.NAME = 'Alert';
select dt.* from WFD_XML wx, DATA_TABLE dt
        wx.WFD_ID = {ID here}
        and wx.WFD_VERSION = 1
        and wx.XML = dt.DATA_ID;

… and a screenshot:

Why might you need to have access to the DB/BPs? Usually it’s enough to have access to BPs via the Web Dashboard only. But sometimes you need to know how BPs are organized in the DB. For example, recently I described some problems with 10 years Life Span and I used the tables described above.

Gennady Kim

Sterling GIS/SI: DB Structure: Envelopes

I’m going to explain the Sterling GIS/SI DB structure from the entities prospective. Lets start with Envelopes. There are 3 tables:

  • ENVELOPE – envelope definitions. As you know, every time you change the envelope (map, BP, etc) GIS/SI stores it as a new version, so you can easily rollback if needed. So, this table stores all the versions of envelopes.
  • ENVELOPE_VERSIONS – versions. DEFAULT_VERSION field is used to indicate the selected version of the envelope.
  • ENVELOPE_PARMS – it’s a key/value table, i.e. every pair of data (name and value) creates a record in this table. These params are envelope-type specific, i.e. an Inbound X12 ISA envelope has it’s own params and an outbound X12 GS or UN/EDIFACT UNH have their own.

I won’t describe the structures of these tables in details – you should use ERD instead. But I’m going to give you a couple of SQL queries and a screenshot which would help you to understand it:

        and NAME = '{Envelope Name}'
        and e.ENVELOPE_ID = ep.ENVELOPE_ID
        and e.NAME = '{Envelope Name}'

The first query returns you the default (selected) record of the envelope by name. The second returns you all the params:

Why might you need to have access to the DB/Envelopes? For example, if you need to get the list of all the envelopes with Sender/Receiver ID and so on. You can do it manually with using of the Web Dashboard, but there is an easier and more elegant way to do it.

Gennady Kim

Sterling GIS/SI: Entity Relationship Diagram

It’s not recommended to work directly with the GIS/SI database, but sometimes I use my Oracle SQL Developer or MS SQL Management Studio to access it and run some SQL queries. And I also use the documentation coming with GIS/SI – Entity Relationship Diagram or ERD. It’s a set of HTML files and MS Visio diagrams which are helpful for the understanding of the DB structure and relationships between tables. You can found it in the GIS/SI installation directory

Gennady Kim

Sterling GIS/SI: a “strange” 10 years lifespan

Working with our client’s SI 5.2.4 I saw that some BPs store data for 10 years in the database, in spite of the LifeSpan of 15 days. I tried to find all the BPs with this strange behavior:

select * from WFD where WFD_ID in
  (select WFD_ID from WF_INST_S where WORKFLOW_ID in
    (select WF_ID from ARCHIVE_INFO where
        CONVERT(varchar(10), archive_date, 11) like '24/%')
    group by WFD_ID, WFD_VERSION)

And found, that all these BPs call for Mailbox. On the IBM Support portal I found the reason of it: FAQ: Who is responsible for deleting the files on file system if BP storage is set to file system:
– mailbox actions will lead to a life span of 10 years, so that a download is possible.

Gennady Kim

GIS/SI: the DB growth

Last week another client told me that their Sterling B2B Integrator’s DB had been growing uncontrollably. I checked the DataBase Usage section and found that there are too many records to be purged – it meant that something was wrong with the Purge service. When it comes to the purge service, there are not too many options – I immediately checked the Locks and found that there had been 2 locks for about a month. Something went wrong, the Purge service setup these locks but was not able to release them. We unchecked them and 24 hours later it got back to the normal routine. Now it’s time to rebuild the indexes and shrink tables…

Gennady Kim