Finding gaps in history table (slowly changing dimension 2)



IBM's flagship relational database management system

Finding gaps in history table (slowly changing dimension 2)

Postby Ron Klop » Wed Jan 27, 2021 2:15 pm

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
You do not have the required permissions to view the files attached to this post.
Ron Klop
 
Posts: 2
Joined: Wed Jan 27, 2021 1:57 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Finding gaps in history table (slowly changing dimension

Postby chaat » Wed Jan 27, 2021 11:54 pm

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.
chaat
 
Posts: 26
Joined: Sun Aug 16, 2009 11:07 pm
Location: St. Cloud, Minnesota
Has thanked: 0 time
Been thanked: 1 time

Re: Finding gaps in history table (slowly changing dimension

Postby Ron Klop » Thu Jan 28, 2021 1:30 pm

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

Regards
Ron Klop
 
Posts: 2
Joined: Wed Jan 27, 2021 1:57 pm
Has thanked: 0 time
Been thanked: 0 time

Re: Finding gaps in history table (slowly changing dimension

Postby sergeyken » Thu Jan 28, 2021 10:15 pm

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!
Javas come and go, but JCL stays forever.
User avatar
sergeyken
 
Posts: 155
Joined: Wed Jul 24, 2019 10:12 pm
Has thanked: 3 times
Been thanked: 13 times

Re: Finding gaps in history table (slowly changing dimension

Postby enrico-sorichetti » Fri Jan 29, 2021 1:35 am

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 )
cheers
enrico
When I tell somebody to RTFM or STFW I usually have the page open in another tab/window of my browser,
so that I am sure that the information requested can be reached with a very small effort
enrico-sorichetti
Global moderator
 
Posts: 2958
Joined: Fri Apr 18, 2008 11:25 pm
Has thanked: 0 time
Been thanked: 159 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post