May 2, 2016 at 5:54 pm
Hi
COBOL / IMS Db migration to SQL Server / .NET, Need help on how to DATA Model from segments to tables, that too relational data model ?
It seems the referential integrity and other relations are maintained via code in Cobol app so how should I go about understanding the current Cobol IMS DB and design RDBMS ?
I was thinking of SIMPLY extract the Data in flat file/ excel and import it to SQL Server and design the relations from scratch, in order to do that, I need to understand the logic and queries of the IMS/DB I guess, I do NOT know COBOL so
step 1: have to rely on IN-HOUSE Cobol programmers
step 2: Understand the UI of the cobol app and with the extracted Data, build the design from scratch.
May 2, 2016 at 7:06 pm
I've done such a migration 4 years ago - migrate a IMS db to Sql server and mainframe cobol to Microfocus COBOL. we kept the core code in COBOL but the db interface was converted to C# using sql client calls.
http://www.tutorialspoint.com/ims_db/ims_db_quick_guide.htm contains information about IMS but you better off asking the developers as implementation can change depending on the application.
In order to understand how the records link to each other you should ask the client developers to see if they have a common ims access cobol program where all calls are executed through.
And ask them how do you call it when they wish to do the following operations
1 - read single record directly
2 - position a record pointer to a record on the chain
3 - read first or last record on a chain
4 - read next or prior record on the chain
In most cases the cobol program will setup the "key" to access the dataset and then call one of the IMS functions to access the data as per above.
Possible examples
MOVE 123 to invoice-number
call IMS using get-first
Invoice-record-line
...process record
then on a loop until eof or invoice-number not equal required number
call IMS using get-next
Invoice-record-line
process record
It is likely that your client has cobol copybooks that define each individual IMS record they deal with - those copybooks may have indication of which are the key fields for each record.
You will also need to consider that it is highly likely that a IMS dataset contains several type of records and that you will need to create a sql table for each type of record.
Adding to this it will also be highly likely that a IMS record will contain COBOL packed/binary data - you can't just extract the record as is and load to SQL - you will need to convert it to a edited version of of the fields prior to loading to SQL.
And... you said you are migration from cobol to sqlserver/.net - has your client considered the possibility of keeping part of the COBOL code?
May 2, 2016 at 8:08 pm
Thank you so much
Please see my INLINE response below
frederico_fonseca (5/2/2016)
I've done such a migration 4 years ago - migrate a IMS db to Sql server and mainframe cobol to Microfocus COBOL. we kept the core code in COBOL but the db interface was converted to C# using sql client calls.-- [highlight="#ffff11"]which db interface ? for IMS DB you created C# CODE ? How using client calls ? please explain and Do we have to migrate to MICROFOCUS Cobol first ?[/highlight]
http://www.tutorialspoint.com/ims_db/ims_db_quick_guide.htm contains information about IMS but you better off asking the developers as implementation can change depending on the application.
In order to understand how the records link to each other you should ask the client developers to see if they have a common ims access cobol program where all calls are executed through.
And ask them how do you call it when they wish to do the following operations
1 - read single record directly
2 - position a record pointer to a record on the chain
3 - read first or last record on a chain
4 - read next or prior record on the chain
In most cases the cobol program will setup the "key" to access the dataset and then call one of the IMS functions to access the data as per above.
Possible examples
MOVE 123 to invoice-number
call IMS using get-first
Invoice-record-line
...process record
then on a loop until eof or invoice-number not equal required number
call IMS using get-next
Invoice-record-line
process record
It is likely that your client has cobol copybooks that define each individual IMS record they deal with - those copybooks may have indication of which are the key fields for each record.
You will also need to consider that it is highly likely that a IMS dataset contains several type of records and that you will need to create a sql table for each type of record.
[highlight="#ffff11"]-- finding it difficult to comprehend above :-)[/highlight]
Adding to this it will also be highly likely that a IMS record will contain COBOL packed/binary data - you can't just extract the record as is and load to SQL - you will need to convert it to a edited version of of the fields prior to loading to SQL.
[highlight="#ffff11"]- - Well, the cobol developer already extracted some segment data into flat file first and then to excel.[/highlight]
And... you said you are migration from cobol to sqlserver/.net - has your client considered the possibility of keeping part of the COBOL code?
[highlight="#ffff11"] -- why keep COBOL Code ? [/highlight]
May 3, 2016 at 2:54 am
going to reply to your 4 comments.
1 - you do not need to migrate to Microfocus - I was just stating what we did on our project.
regarding the using client calls this goes to application design.
. application layer
. data layer
the above apply to any language being it Cobol or C#
In our case we had
. cobol application -> core IO Module (Cobol) -> IMS DB calls
and we converted to
. cobol application -> core IO Module (C#) -> Sql statements using SQL Client
2 - Show this to your cobol developers - they will most likely understand it and explain to you what I mean
3 - Any cobol developer will know what to do and the one that supplied the file has probably done it already, but depending on volumes of data it may be better to do it differently - but this is outside the help I can give here.
4 - to that I ask "Why not?"
possible reasons why
. keep extensive business logic intact
. avoid making same mistakes on code that have already been fixed on cobol
. reuse cobol developers that know the application without the need for a full retraining in another language
Obviously all this is going to be highly dependent on what the application does, how extensive it is in terms of both business logic and volumes of data and how it is processed.
But for me as an Architect it would require that all possibilities are put on the table and then pros and cons analyzed and all alternatives are given so a decision can be taken on the way to go.
And my question here was not to suggest in any way that the decision is wrong - was only to confirm that the client has indeed taken this option in consideration - seen many that were not even aware they could do it.
May 3, 2016 at 3:39 pm
Thanks Again and see my inline response
frederico_fonseca (5/3/2016)
going to reply to your 4 comments.1 - you do not need to migrate to Microfocus - I was just stating what we did on our project.
regarding the using client calls this goes to application design.
. application layer
. data layer
the above apply to any language being it Cobol or C#
In our case we had
. cobol application -> core IO Module (Cobol) -> IMS DB calls
and we converted to
. cobol application -> core IO Module (C#) -> Sql statements using SQL Client
[highlight="#ffff11"]So you converted CORE IO MODULE to C# and IMS DB calls to SQL Statements in the SQL Server, IF yes, then I did not know that IMS DB calls are segregated / Separate like a Client - Server app.
Now, the IMS DB calls are written in COBOL or any other language like SQL ? because if I ask the COBOL Developer to separate IMS DB calls and show it to me, can he ?[/highlight]
2 - Show this to your cobol developers - they will most likely understand it and explain to you what I mean
3 - Any cobol developer will know what to do and the one that supplied the file has probably done it already, but depending on volumes of data it may be better to do it differently - but this is outside the help I can give here. -[highlight="#ffff11"]How else can we meet ?[/highlight]
4 - to that I ask "Why not?"
possible reasons why
. keep extensive business logic intact
. avoid making same mistakes on code that have already been fixed on cobol
. reuse cobol developers that know the application without the need for a full retraining in another language
Obviously all this is going to be highly dependent on what the application does, how extensive it is in terms of both business logic and volumes of data and how it is processed.
But for me as an Architect it would require that all possibilities are put on the table and then pros and cons analyzed and all alternatives are given so a decision can be taken on the way to go.
And my question here was not to suggest in any way that the decision is wrong - was only to confirm that the client has indeed taken this option in consideration - seen many that were not even aware they could do it.
[highlight="#ffff11"]Yes at an Architect Level, I am contemplating what MIGRATION route to go, as I only have 10 months left
1. Since not much data 500k records, I wask thinking MS Access
2. PHP and any DB
3. OO Java / .NET and any DB[/highlight]
May 3, 2016 at 4:28 pm
Rankerg (5/3/2016)
Thanks Again and see my inline responsefrederico_fonseca (5/3/2016)
going to reply to your 4 comments.1 - you do not need to migrate to Microfocus - I was just stating what we did on our project.
regarding the using client calls this goes to application design.
. application layer
. data layer
the above apply to any language being it Cobol or C#
In our case we had
. cobol application -> core IO Module (Cobol) -> IMS DB calls
and we converted to
. cobol application -> core IO Module (C#) -> Sql statements using SQL Client
[highlight="#ffff11"]So you converted CORE IO MODULE to C# and IMS DB calls to SQL Statements in the SQL Server, IF yes, then I did not know that IMS DB calls are segregated / Separate like a Client - Server app.
Now, the IMS DB calls are written in COBOL or any other language like SQL ? because if I ask the COBOL Developer to separate IMS DB calls and show it to me, can he ?[/highlight]
2 - Show this to your cobol developers - they will most likely understand it and explain to you what I mean
3 - Any cobol developer will know what to do and the one that supplied the file has probably done it already, but depending on volumes of data it may be better to do it differently - but this is outside the help I can give here. -[highlight="#ffff11"]How else can we meet ?[/highlight]
4 - to that I ask "Why not?"
possible reasons why
. keep extensive business logic intact
. avoid making same mistakes on code that have already been fixed on cobol
. reuse cobol developers that know the application without the need for a full retraining in another language
Obviously all this is going to be highly dependent on what the application does, how extensive it is in terms of both business logic and volumes of data and how it is processed.
But for me as an Architect it would require that all possibilities are put on the table and then pros and cons analyzed and all alternatives are given so a decision can be taken on the way to go.
And my question here was not to suggest in any way that the decision is wrong - was only to confirm that the client has indeed taken this option in consideration - seen many that were not even aware they could do it.
[highlight="#ffff11"]Yes at an Architect Level, I am contemplating what MIGRATION route to go, as I only have 10 months left
1. Since not much data 500k records, I wask thinking MS Access
2. PHP and any DB
3. OO Java / .NET and any DB[/highlight]
yes the Cobol developer should know what to show you once you give him all my replies to this thread.
IMS calls can be segregated or not - it all depends on what the developer of the application did.
Regarding db to store those 500k record.. DO NOT use MsAccess for data storage - for that volume you can go with Sql Server Express (free) and it will be better than Access.
With such small volume you may also choose one of the other free database vendors - but I can't advise in any as I haven't used any other recently to know they capabilities.
As for interface anything will do so not giving any advice here.
May 3, 2016 at 6:00 pm
Ok, thanks
1. I will ask Cobol Developer if DB calls are segregated like Client Server app ? But in the Cobol source code, how the DB calls are separated?, perhaps sample code will help 🙂
2. My Bad it is 12,000,000 12 million records, Perhaps Oracle is better suited.
3. As far as understanding how the records link to each other, how will it help me ? because want to be careful on bugging Cobol developer ? 🙂
May 3, 2016 at 6:08 pm
no Oracle isn't better suitable - and SQL Server Express will still hold with that volume. what you need to find out is exactly how many records each table that you need to create will have - and if any single table after you load all records will have more than 10GB.
For the other 2 questions you do need to ask the other developer as there is nothing I can do there as it is application dependent.
May 3, 2016 at 6:11 pm
Oops I missed your quote "You will also need to consider that it is highly likely that a IMS dataset contains several type of records and that you will need to create a sql table for each type of record."
Yes, I am in verge of DATA Modeling and Cobol guy sent me for 3 MENU screens and the segments each Menu screen is used, so based on segments used I was planning to Datamodel by
1. create SUPERTYPE based on common data and SUBTYPE based on uncommon data
2. Based on your comment, I have to create a SQL TABLE for each type of record in a IMS Dataset ?
However, I am considering this project as a project, where client has given me EXCEL SHEETS and UI Mock screens, build everything from scratch - your comments ? because I cant spend my time learning COBOL or HIERARCHIAL Data model of IMS.
So, any guidance on HOW TO Datamodel an IMS DB to RDBMS will be immense help ?
May 3, 2016 at 9:15 pm
frederico_fonseca (5/3/2016)
no Oracle isn't better suitable - and SQL Server Express will still hold with that volume. what you need to find out is exactly how many records each table that you need to create will have - and if any single table after you load all records will have more than 10GB.For the other 2 questions you do need to ask the other developer as there is nothing I can do there as it is application dependent.
You can even beat the 10GB limit of Express by using multiple databases and partitioned views.
--Jeff Moden
Change is inevitable... Change for the better is not.
May 4, 2016 at 10:20 am
Please see the INLINE response I got, for the question - how the records link to each other - I could not quite comprehend again 🙂
Rankerg (5/2/2016)
Thank you so muchPlease see my INLINE response below
frederico_fonseca (5/2/2016)
I've done such a migration 4 years ago - migrate a IMS db to Sql server and mainframe cobol to Microfocus COBOL. we kept the core code in COBOL but the db interface was converted to C# using sql client calls.-- [highlight="#ffff11"]which db interface ? for IMS DB you created C# CODE ? How using client calls ? please explain and Do we have to migrate to MICROFOCUS Cobol first ?[/highlight]
http://www.tutorialspoint.com/ims_db/ims_db_quick_guide.htm contains information about IMS but you better off asking the developers as implementation can change depending on the application.
In order to understand how the records link to each other you should ask the client developers to see if they have a common ims access cobol program where all calls are executed through.
And ask them how do you call it when they wish to do the following operations
1 - read single record directly
2 - position a record pointer to a record on the chain
3 - read first or last record on a chain
4 - read next or prior record on the chain
In most cases the cobol program will setup the "key" to access the dataset and then call one of the IMS functions to access the data as per above.
Possible examples
MOVE 123 to invoice-number
call IMS using get-first
Invoice-record-line
...process record
then on a loop until eof or invoice-number not equal required number
call IMS using get-next
Invoice-record-line
process record
It is likely that your client has cobol copybooks that define each individual IMS record they deal with - those copybooks may have indication of which are the key fields for each record.
You will also need to consider that it is highly likely that a IMS dataset contains several type of records and that you will need to create a sql table for each type of record.
[highlight="#ffff11"]-- finding it difficult to comprehend above :-)[/highlight]
Here is the RESPONSE I got :
[highlight="#ffff11"]As you may know, JAI IMS database is a hierarchy database, although what you have on the diagram is only a view of it ( in reality is composed of four separate data bases) but we only work through the logical view as your diagram shows. Therefore there is no get first or get last like you would find in SQL.
The diagram shows the hierarchy sequence as to how to access the different segments in the database.
All records that were dumped and loaded to the EXCEL files have their corresponding hierarchy key and where they belong in the database.[/highlight]
Adding to this it will also be highly likely that a IMS record will contain COBOL packed/binary data - you can't just extract the record as is and load to SQL - you will need to convert it to a edited version of of the fields prior to loading to SQL.
[highlight="#ffff11"]- - Well, the cobol developer already extracted some segment data into flat file first and then to excel.[/highlight]
And... you said you are migration from cobol to sqlserver/.net - has your client considered the possibility of keeping part of the COBOL code?
[highlight="#ffff11"] -- why keep COBOL Code ? [/highlight]
May 5, 2016 at 10:39 am
You are talking about re-authoring the DB design and re-authoring the application program. From my viewpoint that is the time to stop saying this is a migration and start saying it is a new application.
All of the normal stuff that is done for a new application is needed for this. Part of the work will be looking at the data in the old application and deciding if you want to import it. An alternative to importing the data would be to freeze the old application with its data, and use it whenever any legacy information is needed.
If you do decide to import, then it becomes a standard piece of work to map old data to new data. The need to be understand the impact of logical parents and other non-hierarchical relationships that can exist within IMS has already been pointed out. IMS is classed as hierarchical, but it is better to think of it as a Network database that allows the Codasyl structures but without complying to Codasyl standards of working.
I feel that if you keep tackling this as a migration you will restrict yourself for no good reason (business, cost or whatever) in the DB design and in the app design. Every day is new, and the business problem the old application tackled when it was written has changed. If you restrict yourself to just migrating it, then you will spend about as much as a total new build. The project may be a success from the technical viewpoint, but you will give the business very poor value for money because it will not meet their current needs.
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 5, 2016 at 11:20 am
It would be hard to infer what would be the equivalent of referential constraints simply by looking at the code. I'd start all this effort by collaborating with a subject matter expert and what's left of the original database team to sketch out some logical ER diagrams and data flow diagrams.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
May 5, 2016 at 7:15 pm
Wow! W O W What a Nice Approach. Thank you so much Sir, I will make an attempt to approach this as NEW application, that way I do not have to MAP the DATA and like you said I will be giving BUSINESS more value by utilizing current MODERN technology.
However, Are they any CAVEAT's that I need to give HEADS UP to BUSINESS ?
Thank you so much.
May 5, 2016 at 8:45 pm
With the disclaimer that I've never had to work directly with IMS nor am I a Cobol programmer, it seems to me that what people are calling a "hierarchical" database is really a "Star Schema" on steroids. With that, why wouldn't someone just export each "node" (a collection or rows, in this case) into a separate table and create the FKs based on the "edges" formed in the IMS hierarchical structure?
--Jeff Moden
Change is inevitable... Change for the better is not.
Viewing 15 posts - 1 through 15 (of 25 total)
You must be logged in to reply to this topic. Login to reply