Finding gaps in history table (slowly changing dimension 2)

[Ron Klop]

Hi

I have a table, example in attachment.
A slowly changing 2 dimension according to Kimball.
Key is just a surrogate key, a key to make rows unique

As you can see there are three rows for product A.
Timelines for this product are ok. During time the description of the product changes.
From 1-1-2020 up until 4-1-2020 the description of this product was ProdA1.
From 5-1-2020 up until 12-2-2020 the description of this product was ProdA2 etc.

If you look at product B, you see there are gaps in the timeline.

We use DB2 V12 z/Os. How can I check if there are gaps in the timelines for each and every product?

regards
Ron

[chaat]

This should be pretty simple, unload the table to a sequential file, sort it by product / start date. (for this I would recommend casting the dates to ISO format CCYYMMDD as it makes sorting much easier). The write a simple COBOL program to check for gaps in the timelines. This should be a pretty simple program to write.

[Ron Klop]

I what if you don't have Cobol experience?
Isn't this just possible by writing a query in DB2?

Regards

[sergeyken]

Prior to writing a code in any language, either SQL, or COBOL, or REXX, or Java, or SORT, or 100 other options you need first of all to find out (to develop, to search, to learn, to ask, etc.) what the acceptable algorithm should be to reach your goal?

The algorithms do not depend at all on the used language, or whatever. It only demonstrates: either the expert is able to think with his own mind, or he is able only to beg any free of charge ready to use solution from others...

It doesn’t matter did you have or not experience with COBOL, or whatever else. The way of thinking with ones own mind does not depend on any programming language, and even on any language at all!

[enrico-sorichetti]

The write a simple COBOL program to check for gaps in the timelines. This should be a pretty simple program to write.

can be done with sort, but at the end a simple COBOL program might be faster to develop and test
( or any other language the TS is comfortable with )