May 6, 2016 at 1:04 am
I think almost regardless of the initial direction from your managers, you need to present business cases for a) migrating the existing app to new technology but with just its current functionality, b) a new app that takes into account current business needs, and freezing the existing app and data for legacy access, c) a new app that takes into account current business needs, plus migrating the data from the old app, d) keep using IMS with the old app. These options are there to show you have done due diligence on the project design.
You will need to do investigation for all 4 options, and produce both tangible costs (buying new stuff) and intangible costs (cost of change, cost of maintaining existing platform, cost of not meeting current business needs), along with risks, etc. Certainly you will show the the 'do nothing' approach of keeping IMS does have its own costs and risks. As you work through this, one option will hopefully stand out as having the best combination of lowest risk, lowest cost, and most benefits to the business. This preferred option may not be what you expect at this stage, and may not be what the business chooses, but they will have made an informed decision.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 6, 2016 at 1:27 am
Jeff, certainly doing a 1 to 1 mapping of what IMS calls Segments into relational tables will give a reasonable first-cut design.
Most of the mapped tables will have an obvious PK, but some will not. Because IMS deals with parentage via pointers, it is easily possible to have segments that show no trace of parentage from looking at the columns they contain. For what IMS calls 'physical parents' these relationships are easy to work out from the IMS schema, and additional columns can be added to the relational tables where needed to allow relational parent/child FKs. Just about any database designer who sees a copy of the IMS physical schema can work this bit out even if they do not know IMS.
What IMS calls 'logical parents' can be harder to discern from the IMS schema, they are definitely there but it needs someone who understands IMS to look for these details. One outcome may be the need to create new relational tables to show many-to-many relationships that are captured in the IMS schema but which do not need a physical object within IMS to materialise them.
D'oh! I last used IMS many years ago and thought I could safely forget about it. It just shows that everything you learn might become useful again...
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 6, 2016 at 2:50 am
Ah.. if it was that easy.. 1 to 1 mapping.. (sigh).
On the migration I did 1 segment was converted to 200+ Tables, each with their own set of primary key columns
Jeff,
As Ed pointed out there are several types of segments and several types of records. This on its own needs to be taken in consideration when doing the mapping.
But we are also talking COBOL which adds another level of complexity unless the application was designed to be normalized from the start (unlikely but possible).
The following is not necessarily how the application the OP has to deal with is structured hence the need to talk with the COBOL developers as they alone will be able to supply the required information. But it is a very common practice within old COBOL applications.
One way to understand what may be involved here ...
Consider all the sys.* tables. sys.objects, sys.columns, sys.foreignkeys, sys.indexes, sys.types, sys.schemas etc
These are considered metadata tables.
On a COBOL/IMS application all the above tables would be defined in a single physical segment - metadata - with the following IMS layout (example)
segmentname - metadata
Datarecord
key field - char(50)
data field - char(4000)
So anyone looking at the IMS structure doing a direct convert to Sql server would just create a single table with 2 columns.
But the COBOL application internaly redefines the above as follows on a higher level
Schemasrecord redefines datarecord
Key field -- cobol group record
recordtype - char(2) -- value SC on data record
schemaid - int (cobol PIC 9(4))
filler - char(44) -- empty data
data field -- cobol group record
name - char(30)
principal_id - int (cobol PIC 9(4))
filler - char(xx)
objectsrecord redefines datarecord
Key field -- cobol group record
recordtype - char(2) -- value OB on data record
objectid - int (cobol PIC 9(4))
filler - char(44) -- empty data
data field -- cobol group record
name - char(30)
schema_id - int (cobol PIC 9(4))
type - char(2)
type_desc - char(60)
principal_id - int (cobol PIC 9(4))
filler - char(xx)
indexesrecord redefines datarecord
Key field -- cobol group record
recordtype - char(2) -- value IX on data record
objectid - int (cobol PIC 9(4))
indexid - int (cobol PIC 9(4))
filler - char(40) -- empty data
data field -- cobol group record
name - char(30)
schema_id - int (cobol PIC 9(4))
type - char(2)
type_desc - char(60)
is_unique - int (cobol PIC 9(1))
data_space_id - int (cobol PIC 9(1))
ignore_dup_key - int (cobol PIC 9(1))
filler - char(xx)
indexescolumnsrecord redefines datarecord
Key field -- cobol group record
recordtype - char(2) -- value IC on data record
objectid - int (cobol PIC 9(4))
indexid - int (cobol PIC 9(4))
indexcolumnid - int (cobol PIC 9(4))
filler - char(40) -- empty data
data field -- cobol group record
name - char(30)
column_id - int (cobol PIC 9(4))
key_ordinal - int (cobol PIC 9(4))
partition_ordinal - int (cobol PIC 9(4))
is_descending_key - int (cobol PIC 9(1))
is_included_column - int (cobol PIC 9(1))
filler - char(xx)
The above is what common on the Cobol world. and it is also what makes it hard to convert to a relational database.
May 6, 2016 at 4:36 am
EdVassie (5/6/2016)
Jeff, certainly doing a 1 to 1 mapping of what IMS calls Segments into relational tables will give a reasonable first-cut design.Most of the mapped tables will have an obvious PK, but some will not. Because IMS deals with parentage via pointers, it is easily possible to have segments that show no trace of parentage from looking at the columns they contain. For what IMS calls 'physical parents' these relationships are easy to work out from the IMS schema, and additional columns can be added to the relational tables where needed to allow relational parent/child FKs. Just about any database designer who sees a copy of the IMS physical schema can work this bit out even if they do not know IMS.
What IMS calls 'logical parents' can be harder to discern from the IMS schema, they are definitely there but it needs someone who understands IMS to look for these details. One outcome may be the need to create new relational tables to show many-to-many relationships that are captured in the IMS schema but which do not need a physical object within IMS to materialise them.
Awesome. I always wondered how IMS worked behind the scenes. It does explain why folks had to bust a bit of a hump to provide some "flat" data from so I could download it into some new fangled things called PCs back in the 80's. It's also fine testament to the product. It's been around for longer than most programmers I know have been alive.
Thanks for taking the time to explain a bit, Ed. Sounds like you've been around the block well more than once with IMS.
D'oh! I last used IMS many years ago and thought I could safely forget about it. It just shows that everything you learn might become useful again...
Heh... yes... many, many times around the block. And I definitely agree with the idea that everything learned has a pretty good potential for serving someone well in the future. Thanks, again.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 6, 2016 at 9:36 am
Thank you All, appreciate your thoughts and time. It is kind of overwhelming to digest especially cobol code of META DATA conversion.
Now, as far presenting business cases is concerned:
a) migrating the existing app to new technology but with just its current functionality - This is what I recommended for PHASE 1, (headache of understanding COBOL and IMS) b) a new app that takes into account current business needs, and freezing the existing app and data for legacy access - Planning to PROPOSE this but what about the legacy data ? (Business wants RETENTION PERIOD of Data is like 100 years) c) a new app that takes into account current business needs, plus migrating the data from the old app, - second part is what I proposed as PHASE 2 d) keep using IMS with the old app. - :w00t: RULED OUT because I have to go home 🙂 They hired me to build NEW App 😎
May 9, 2016 at 2:41 am
If the business want data retention for 100 years, you need to ask the business a difficult question...
Do they just want the data retained, but with no means to present it in a meaningful way. In other words, do they just want a collection of strings and numbers but without anything that will tell them what the numbers mean or what the strings relate to. If they want this option then costs are really low. You just make sure you have a backup of the data from IMS, this is called an 'image copy' (plus maybe a copy of the application programs but you have zero chance you will get them working), and that you try to keep the media for 100 years.
Alternatively, do they want to retain the data and be able to present it in a meaningful format. In other words, do they need to know what the numbers mean and what the strings describe. If they want this option the costs will be high. The previous discussions cover some of the complexities of exporting data from IMS to a different database format (relational is no harder or easier than anything else), plus the cost of developing a new app to access it.
This might sound a stupid question, but I have seen businesses take the decision to keep only the data. They have taken the view that any legislation that requires the data to be kept only requires that it be readable, not that it must be understandable.
If they do require the data to be understandable, then they must accept the cost implications of that decision. In this situation you are stuck with items a) headache of understanding, c) new app with data migration.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 9, 2016 at 2:50 am
Jeff, I had totally forgotten frederico's point about copybooks. This turns what looks like a single parent single child relationship into a generalised parent/children relationship, with as many parent and child objects as you have copybooks.
As far as logical relationships and what is called 'secondary indexes' are concerned, my memory is that these are described in their own dedicated definition files, and do not form part of the physical schema files. It could even be that App B has a logical relationship on to App A, and that this relationship is described with the files kept for App B and is not present with any files that describe App A, unless someone has thought to include a 'readme' type file to warn about this. That is why understanding an IMS schema needs someone who knows IMS so they can identify what the schema actualy contains.
In spite of all this complexity, IMS is the database that got man to the moon. Nothing else has come close since then. And just be grateful you are not having to convert a TPF database and app to a new system, that can be really complicated!
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 9, 2016 at 5:12 am
If you have not found them already, there are some IMS to relational conversion products available. BMC Software now owns the NEON product suite of IMS conversion stuff that was highly regarded back around 2000 when I last worked with these things. CA also seem to have a conversion toolset, and there may be others.
None of these things will be free, but neither is staff time in putting together a home-grown solution. A toolset should give you a standardised approach and audit information about what has been done, the type of stuff that is often de-prioritised in a homegrown solution. You still may have unknown unknowns, but this can happen with any conversion method.
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 9, 2016 at 9:57 am
Thanks Ed, couldn't follow your message to Jeff though...
So, here is the scene, It is kinda small to medium app I should say with 3 functions / screens need to be converted to .NET app. My approach is forget IMS Hierarchial DB model, Just understand the COBOL UI Screens and build a relational data model like I do for a new app.
Thanks for suggesting Migration Software, not sure that is a good idea :w00t: as it will reduce my billable hours... 😉
May 9, 2016 at 10:08 am
Hi Rankberg, I think for a small app like you are describing then your approach of understanding the Cobol screen layouts may give the fastest results. Best of luck
Original author: https://github.com/SQL-FineBuild/Common/wiki/ 1-click install and best practice configuration of SQL Server 2019, 2017 2016, 2014, 2012, 2008 R2, 2008 and 2005.
When I give food to the poor they call me a saint. When I ask why they are poor they call me a communist - Archbishop Hélder Câmara
May 9, 2016 at 10:29 am
Layout means UI segment layout or COBOL code? I checked BMC Website and couldn't find migration software?
I need help on Data modeling in general? Normalization Testing. ..
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply