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