We want to migrate ISAM data files created by an RM/Cobol application easily to a database format. We don't find a tool to facilitate this process and would prefer not to write for every data file a dump program to export the data into txt files to load this files into a database in a second step. Are there people having done this before, with a certain tool?
Tek-Tips is the largest IT community on the Internet today!
Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!
-
Congratulations strongm on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!
Migrating ISAM files from RM/Cobol 2
- Thread starter Patten
- Start date
Here is the tool:
So the questions:
Is this a one-off or will this be a continuing requirement?
Do you have the source available (at least the FDs)?
What hardware platform?
Tom Morrison
So the questions:
Is this a one-off or will this be a continuing requirement?
Do you have the source available (at least the FDs)?
What hardware platform?
Tom Morrison
fredericofonseca
IS-IT--Management
You won't be able to find an easy way.
Relativity will allow you to access the COBOL files directly from your DB (or other ODBC compliant application), but you still need to configure the files, and the destination DB if you are really dropping RM. If not it may be the best/only option.
Depending on how you have your files defined it may be possible to automate the conversion.
E.g. I (and others) have created small programs that will read a FD and create a small program that reads all records from the corresponding COBOL file and will update a Database table.
The problems that normally prevent this type of conversion are when you have different record types within a file (a "tables" file), or when you have different subsets within the same record type (a invoice line record could eventually have either a "article code" or a description).
There is no tool out there that will automate this for you. You will always have to define this info somewhere, and this is where you have most of the work on conversions.
Regards
Frederico Fonseca
SysSoft Integrated Ltd
Relativity will allow you to access the COBOL files directly from your DB (or other ODBC compliant application), but you still need to configure the files, and the destination DB if you are really dropping RM. If not it may be the best/only option.
Depending on how you have your files defined it may be possible to automate the conversion.
E.g. I (and others) have created small programs that will read a FD and create a small program that reads all records from the corresponding COBOL file and will update a Database table.
The problems that normally prevent this type of conversion are when you have different record types within a file (a "tables" file), or when you have different subsets within the same record type (a invoice line record could eventually have either a "article code" or a description).
There is no tool out there that will automate this for you. You will always have to define this info somewhere, and this is where you have most of the work on conversions.
Regards
Frederico Fonseca
SysSoft Integrated Ltd
-
1
- #4
Hi
I made a ulitilty that do the job (up to 3 alt.keys).
if you have interest in that tell me.
Barry
C O B O L I N D E X F I L E T O S E Q U E N T I A L F I L E
================================================================
(ESC-EXIT)
1. INPUT INDEX
FILE PATH+NAME: '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
NUBER OF RECORDS:~~~~~~~~~~~~~~~~~~~
FIX/VAR: ~ (F-FIX , V-VAR)
RECORD LENGTH: FROM: ~~~~ TO: ~~~~
KEY INFORMATION: ALTERNATE-KEYS:'~' (0-NO,1-YES)
! KEY ! STARTING ! KEY ! DUPLICATES !
! NUMBER ! POSITION ! LENGTH ! PERMITTED !
! ------ ! -------- ! ------ ! ---------- !
! XXXXX ! 'ZZZZ' ! 'ZZZZ' ! 'X' !
2. OUTPUT SEQUENTIAL
FILE PATH+NAME: '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
OUTPUT FILE EXIST OVERIDE?:'~' (Y-YES ' N-NO)
FUNCTION: '~' (1-2 , Y-OK , @,ESC-EXIT)
I made a ulitilty that do the job (up to 3 alt.keys).
if you have interest in that tell me.
Barry
C O B O L I N D E X F I L E T O S E Q U E N T I A L F I L E
================================================================
(ESC-EXIT)
1. INPUT INDEX
FILE PATH+NAME: '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
NUBER OF RECORDS:~~~~~~~~~~~~~~~~~~~
FIX/VAR: ~ (F-FIX , V-VAR)
RECORD LENGTH: FROM: ~~~~ TO: ~~~~
KEY INFORMATION: ALTERNATE-KEYS:'~' (0-NO,1-YES)
! KEY ! STARTING ! KEY ! DUPLICATES !
! NUMBER ! POSITION ! LENGTH ! PERMITTED !
! ------ ! -------- ! ------ ! ---------- !
! XXXXX ! 'ZZZZ' ! 'ZZZZ' ! 'X' !
2. OUTPUT SEQUENTIAL
FILE PATH+NAME: '~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~~'
OUTPUT FILE EXIST OVERIDE?:'~' (Y-YES ' N-NO)
FUNCTION: '~' (1-2 , Y-OK , @,ESC-EXIT)
- Thread starter
- #5
fredericofonseca
IS-IT--Management
Patten,
If you are interested on having someone doing this for you please contact me and we can discuss the situation.
Depending on how you have your fields defined you may not even need any utility at all (you already have one supplied with the runtime).
em ail is:
frederico_fonseca at the domain below
Baruch,
What do you mean up to 3 alternate keys?
As far as I can see this is only important if you are creating the SQL table + indexes your self, otherwise don't see how it can be of use!
Regards
Frederico Fonseca
SysSoft Integrated Ltd
If you are interested on having someone doing this for you please contact me and we can discuss the situation.
Depending on how you have your fields defined you may not even need any utility at all (you already have one supplied with the runtime).
em ail is:
frederico_fonseca at the domain below
Baruch,
What do you mean up to 3 alternate keys?
As far as I can see this is only important if you are creating the SQL table + indexes your self, otherwise don't see how it can be of use!
Regards
Frederico Fonseca
SysSoft Integrated Ltd
Patten,
I was a bit frustrated that you responded in this thread, yet you managed to ignore my questions. So, I had a look back through your previous postings and saw that you have been on this track of migration to a SQL database for months.
Other than simply repeating the word migration you might take a few minutes to explain what you are trying to accomplish and why you think that migration to a SQL database is the means to accomplish your goal. You may think that my suggestions are biased, but I can assure you that quality responses typically only result from quality questions, those that provide enough information for the experts in this forum (and there really are some experts in this forum).
Please accept this advice in the spirit in which it is given; I really want to make sure you have a solution which will advance you toward your goal.
Tom Morrison
I was a bit frustrated that you responded in this thread, yet you managed to ignore my questions. So, I had a look back through your previous postings and saw that you have been on this track of migration to a SQL database for months.
Other than simply repeating the word migration you might take a few minutes to explain what you are trying to accomplish and why you think that migration to a SQL database is the means to accomplish your goal. You may think that my suggestions are biased, but I can assure you that quality responses typically only result from quality questions, those that provide enough information for the experts in this forum (and there really are some experts in this forum).
Please accept this advice in the spirit in which it is given; I really want to make sure you have a solution which will advance you toward your goal.
Tom Morrison
- Thread starter
- #8
Tom,
don't wanted to frustate you.
Answer to your questions.
*Platforms: Linux, Windows, different flavours of Unix.
*FDs: yes we do have the FDs available.
*It would be a one shot operation: dump the data into a database and from that moment on working further with a database.
In fact what I am looking for is a kind of ETL tool (type Genio, djCosmos, Ascential, etc.) which can generate automatically from an FD the 'create table statement' and loads automatically the data from the RM/Cobol ISAM data file into an ODBC database. I know certain ETL tools like Genio have this opportunity. The frustrating thing for me is that they seem not to support RM/Cobol ISAM data files, which is for a lot of other applications true aswell. That's exactly why we wanna to get rid of them to be more open and compatible with other applications. So Relativity is no option for us: it is just adding another layer and tool, just because of the fact that RM/Cobol ISAM files are so closed. And we don't want to maintain just another layer for that purpose. Because this means extra effort for our development team, especially with updates of our software, it means extra fees to pay for maintenance contracts for this Relativity layer, extra risks of bugs, extra risks of upgrade problems, etc.
You read right that I posted couple of months ago already some questions on this forum: in the mean time we decided to which database we are going to migrate, now we are just considering what to do with our former installations where all the data is in RM/Cobol ISAM format: seems this is really a though format...
don't wanted to frustate you.
Answer to your questions.
*Platforms: Linux, Windows, different flavours of Unix.
*FDs: yes we do have the FDs available.
*It would be a one shot operation: dump the data into a database and from that moment on working further with a database.
In fact what I am looking for is a kind of ETL tool (type Genio, djCosmos, Ascential, etc.) which can generate automatically from an FD the 'create table statement' and loads automatically the data from the RM/Cobol ISAM data file into an ODBC database. I know certain ETL tools like Genio have this opportunity. The frustrating thing for me is that they seem not to support RM/Cobol ISAM data files, which is for a lot of other applications true aswell. That's exactly why we wanna to get rid of them to be more open and compatible with other applications. So Relativity is no option for us: it is just adding another layer and tool, just because of the fact that RM/Cobol ISAM files are so closed. And we don't want to maintain just another layer for that purpose. Because this means extra effort for our development team, especially with updates of our software, it means extra fees to pay for maintenance contracts for this Relativity layer, extra risks of bugs, extra risks of upgrade problems, etc.
You read right that I posted couple of months ago already some questions on this forum: in the mean time we decided to which database we are going to migrate, now we are just considering what to do with our former installations where all the data is in RM/Cobol ISAM format: seems this is really a though format...
Hi patern,
Hi Tom (we met at chicago conference)...
My utility r written in rm/cobol itselef.
Its target to SHIFT FROM INDEXED FILE TO FLAT BINARY FILES
The length is the same as in the index fike, but without keys.
In that tools i read the header of the file, & from that i can know its length, keys & number of recordes
So if you still intersting, i will be ggald to help,
(dont know who to attach a file here).
Baruch
Hi Tom (we met at chicago conference)...
My utility r written in rm/cobol itselef.
Its target to SHIFT FROM INDEXED FILE TO FLAT BINARY FILES
The length is the same as in the index fike, but without keys.
In that tools i read the header of the file, & from that i can know its length, keys & number of recordes
So if you still intersting, i will be ggald to help,
(dont know who to attach a file here).
Baruch
Patten,
Please do not interpret this the wrong way, but I would really like to understand what you think Relativity does. From your reply (for which I offer thanks) I can only conclude there is some misunderstanding.
I would not propose Relativity for loading RM/COBOL indexed data files into an ODBC database because, by using Relativity, your existing RM/COBOL indexed files become an ODBC database without moving (i.e. loading) the data anywhere. RM/COBOL indexed files are not closed due to the existence of Relativity. (BTW, the same can be said for Micro Focus indexed files which are also supported by Relativity.)
Finally, you list a large number of risks you would be incurring merely from the introduction of Relativity into the mix, but you seem totally to discount the enormous risks involved in changing your persistant storage mechanism. That is a risk you should not fail to consider.
So, if the problem you are trying to solve is data availability in the enterprise, I would suggest that Relativity is your most cost effective and lowest risk solution.
Tom Morrison
Please do not interpret this the wrong way, but I would really like to understand what you think Relativity does. From your reply (for which I offer thanks) I can only conclude there is some misunderstanding.
I would not propose Relativity for loading RM/COBOL indexed data files into an ODBC database because, by using Relativity, your existing RM/COBOL indexed files become an ODBC database without moving (i.e. loading) the data anywhere. RM/COBOL indexed files are not closed due to the existence of Relativity. (BTW, the same can be said for Micro Focus indexed files which are also supported by Relativity.)
Finally, you list a large number of risks you would be incurring merely from the introduction of Relativity into the mix, but you seem totally to discount the enormous risks involved in changing your persistant storage mechanism. That is a risk you should not fail to consider.
So, if the problem you are trying to solve is data availability in the enterprise, I would suggest that Relativity is your most cost effective and lowest risk solution.
Tom Morrison
-
1
- #11
fredericofonseca
IS-IT--Management
Dimandja,
You can even use them at the same time. No need to have the COBOL application stopped for the other APPS to use Relativity to access the files.
As I mentioned before the product is VERY good.
Patten,
If this is a one off then you really need to go to the conversion program area.
Relativity won't be of much use for you here, especially if your database is on the Linux/Unix side.
Is your conversion always to the same database type, or will this be dependent on the customer? And what is the DB for that matter? This is going to affect how the SQL code is created, and also how you can insert the records onto the table.
And have you decided how you are going to translate your RM fields into DB fields? How are you going to deal with Null values, or even HIGH-VALUES (if you have them) on some fields?
Regards
Frederico Fonseca
SysSoft Integrated Ltd
You can even use them at the same time. No need to have the COBOL application stopped for the other APPS to use Relativity to access the files.
As I mentioned before the product is VERY good.
Patten,
If this is a one off then you really need to go to the conversion program area.
Relativity won't be of much use for you here, especially if your database is on the Linux/Unix side.
Is your conversion always to the same database type, or will this be dependent on the customer? And what is the DB for that matter? This is going to affect how the SQL code is created, and also how you can insert the records onto the table.
And have you decided how you are going to translate your RM fields into DB fields? How are you going to deal with Null values, or even HIGH-VALUES (if you have them) on some fields?
Regards
Frederico Fonseca
SysSoft Integrated Ltd
Frederico,
You said, "Relativity won't be of much use for you here, especially if your database is on the Linux/Unix side."
First, the Relativity data server has been available on Linux/Unix platforms for years. The Relativity data client for Unix/Linux will be available in the next release (which is about to emerge from QA), and can be made available on a limited basis right now. The Unix/Linux client is becoming more popular as the IT community adopts ODBC on Unix (through the open source ODBC driver managers: iodbc and unixodbc), and we have chosen to offer the Unix/Linux client for this reason. We have customers using Relativity via ODBC on Unix with such tools as Perl and php. One customer even uses it with our InstantSQL tool from COBOL; they prefer using relational result sets for doing reports to the traditional reading of multiple files.
Second, I inferred from the fact that Patten's interest in targeting an ODBC database that an ODBC database was reasonable alternative!![[smarty]](/data/assets/smilies/smarty.gif "[smarty] [smarty]")
Tom Morrison
You said, "Relativity won't be of much use for you here, especially if your database is on the Linux/Unix side."
First, the Relativity data server has been available on Linux/Unix platforms for years. The Relativity data client for Unix/Linux will be available in the next release (which is about to emerge from QA), and can be made available on a limited basis right now. The Unix/Linux client is becoming more popular as the IT community adopts ODBC on Unix (through the open source ODBC driver managers: iodbc and unixodbc), and we have chosen to offer the Unix/Linux client for this reason. We have customers using Relativity via ODBC on Unix with such tools as Perl and php. One customer even uses it with our InstantSQL tool from COBOL; they prefer using relational result sets for doing reports to the traditional reading of multiple files.
Second, I inferred from the fact that Patten's interest in targeting an ODBC database that an ODBC database was reasonable alternative!
Tom Morrison
fredericofonseca
IS-IT--Management
Tom,
I didn't know about the client bit. Good news.
My idea when I said that is that FOR A ONE OFF conversion having to buy relativity, + Relativity unix server (or map a Samba/NFS drive), and then having all the info going up and down the network would not be a very good option.
From -
In fact what I am looking for is a kind of ETL tool (type Genio, djCosmos, Ascential, etc.) which can generate automatically from an FD the 'create table statement' and loads automatically the data from the RM/Cobol ISAM data file into an ODBC database
-
I take it that it is that the tool mentioned (Genio or other) that would normally use ODBC. This does not mean that ODBC is the preferred method, nor a requirement, as there are tools that can access some Databases directly without using ODBC, although I agree with you that when Patten says "...be more open and compatible with other applications." he/she is probably thinking on ODBC possibilities.
Regards
Frederico Fonseca
SysSoft Integrated Ltd
I didn't know about the client bit. Good news.
My idea when I said that is that FOR A ONE OFF conversion having to buy relativity, + Relativity unix server (or map a Samba/NFS drive), and then having all the info going up and down the network would not be a very good option.
From -
In fact what I am looking for is a kind of ETL tool (type Genio, djCosmos, Ascential, etc.) which can generate automatically from an FD the 'create table statement' and loads automatically the data from the RM/Cobol ISAM data file into an ODBC database
-
I take it that it is that the tool mentioned (Genio or other) that would normally use ODBC. This does not mean that ODBC is the preferred method, nor a requirement, as there are tools that can access some Databases directly without using ODBC, although I agree with you that when Patten says "...be more open and compatible with other applications." he/she is probably thinking on ODBC possibilities.
Regards
Frederico Fonseca
SysSoft Integrated Ltd
Frederico,
For one-off, I would agree. That is why I asked the question -- unless one is going to reuse Relativity in a consulting situation to do one-time data migrations, it would not seem economical. (But read the original post, which definitely states the most economical method is not preferred.) But another part of the thread Patten states, "...just considering what to do with our former installations..." so it is possible that Patten is in a company that sells (an) application(s) which is more in line with the normal Relativity pricing and business model (develop once, deploy many times).
And I agree that Patten still has not told us what his/her goal is, just the means by which the goal might be achieved. But I am on deadline so...![[bigsmile]](/data/assets/smilies/bigsmile.gif "[bigsmile] [bigsmile]")
Tom Morrison
For one-off, I would agree. That is why I asked the question -- unless one is going to reuse Relativity in a consulting situation to do one-time data migrations, it would not seem economical. (But read the original post, which definitely states the most economical method is not preferred.) But another part of the thread Patten states, "...just considering what to do with our former installations..." so it is possible that Patten is in a company that sells (an) application(s) which is more in line with the normal Relativity pricing and business model (develop once, deploy many times).
And I agree that Patten still has not told us what his/her goal is, just the means by which the goal might be achieved. But I am on deadline so...
Tom Morrison
Frederico said:
Add to that: date conversions, LOW-VALUES (not the same as NULL), automated normalization of OCCURS and REDEFINES (explicit and implicit)....
It was/is a lot of fun to develop.
Tom Morrison
- Thread starter
- #19
We are thinking, like mentioned above, to add ODBC possibilites on our data layer: nowadays our application uses C-ISAM files as data layer, which is a closed structure. More and more of our customers ask to be able to add BI tools on top of our data layer for reporting reasons etc. If we move to a database (whatever one), we are able to add immediately 3rd party BI tools on top of this data layer without having to develop a lot. Moreover, we don't push our customers to buy another product like Relativity to make this possible, most of those customers already have such BI tools in house, and can use them for our application data layer, if it is an ODBC compliant layer.
Secondly, for support reasons, it would be a lot easier for us to have a ODBC compliant data layer, so we don't have to create read programs or write programs for every possible problem which arises, especially since our application releases follow each other closely.
Like mentioned, for the one shot operation to move the data, it is not necessary that the move happens via an ODBC way: it is just the result which has to be ODBC compliant: it could be that the one shot extract-loading happens with a native connection to the database, if available.
Secondly, for support reasons, it would be a lot easier for us to have a ODBC compliant data layer, so we don't have to create read programs or write programs for every possible problem which arises, especially since our application releases follow each other closely.
Like mentioned, for the one shot operation to move the data, it is not necessary that the move happens via an ODBC way: it is just the result which has to be ODBC compliant: it could be that the one shot extract-loading happens with a native connection to the database, if available.
fredericofonseca
IS-IT--Management
Patten,
You still haven't told us what database you are going to use, neither have you mentioned how are you going to deal with the problems mentioned (which you may not have!!).
Regarding your move it is purelly a business decision of yours to move to a database.
For most purposes having ODBC access to COBOL files trough Relativity or to another Database is basically the same.
If this is the only reason it may be cheaper to use Relativity than to use one of the big names (e.g. Oracle or DB2).
If the intention is to move away from COBOL then there's little point in keeping the COBOL files, so the price difference would be irrelevant here.
Regards
Frederico Fonseca
SysSoft Integrated Ltd
You still haven't told us what database you are going to use, neither have you mentioned how are you going to deal with the problems mentioned (which you may not have!!).
Regarding your move it is purelly a business decision of yours to move to a database.
For most purposes having ODBC access to COBOL files trough Relativity or to another Database is basically the same.
If this is the only reason it may be cheaper to use Relativity than to use one of the big names (e.g. Oracle or DB2).
If the intention is to move away from COBOL then there's little point in keeping the COBOL files, so the price difference would be irrelevant here.
Regards
Frederico Fonseca
SysSoft Integrated Ltd
- Status