Re: Assistance with Converting IMS DB to MySQL DB
One point which might seem obvious, and if it's been discussed I apologise for repeating, but IMS DB supports a hierarchical structure, so in a typical DB a root segment may have zero, one or many child segments. Exhoing others, you really will need an IMS expert. Roops On Fri, 17 May 2024, 03:42 Jason Cai, wrote: > Dear all, > > We are currently undertaking a project to convert some 15-year-old IMS > database, for which we have some full image copy on tape, to a MySQL > database on a Linux platform. > > To ensure the success of this migration, we would greatly appreciate your > expertise and guidance on the following matters: > > Is it feasible to recover the IMS full image copy without an IMS DB > system, or is the presence of an IMS DB system an absolute necessity for > this migration process? > > Once we have successfully restored the IMS database files to disk, we are > considering two potential approaches: > a) Utilizing the IMS unload utility to convert the database files into a > PS format, followed by developing a program to read the PS file and import > the data into MySQL. > > b) Directly reading the IMS database files and writing the data into DB2, > and then subsequently converting the data from DB2 to MySQL. > > Are there any specific constraints or limitations that we should be aware > of, such as the mandatory requirement of an IMS DB system for this > migration? > > Are there any relevant tools or utilities available that could assist us > in this process and help streamline the migration? > > We would be immensely grateful if you could provide your insights on > which of the two approaches mentioned above would be more feasible, given > our current situation. Additionally, if there are any alternative methods > or strategies that you believe might be suitable for our specific case, we > would greatly appreciate your suggestions. > > Your expertise and knowledge in this area are highly valued, and any > advice or recommendations you can offer to help us navigate this complex > migration process effectively would be sincerely appreciated. > > Thank you in advance for your time and consideration. We eagerly await > your response and look forward to benefiting from your guidance. > > Best regards, > > Jason Cai > > -- > For IBM-MAIN subscribe / signoff / archive access instructions, > send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN > -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Assistance with Converting IMS DB to MySQL DB
Jason, in addition to all the previous comment, keep attention to the fact that it's not so unusual that programmers redefine fields into a DLI segment (or the entire segment itself) so fields can or can not be what they appear. You should have ALL the old programs accessing DLI data and double check. Good luck. Max <https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail> Privo di virus.www.avast.com <https://www.avast.com/sig-email?utm_medium=email_source=link_campaign=sig-email_content=webmail> <#DAB4FAD8-2DD7-40BB-A1B8-4E2AA1F9FDF2> Il giorno ven 17 mag 2024 alle ore 20:46 ITschak Mugzach < 05a7ced721d8-dmarc-requ...@listserv.ua.edu> ha scritto: > If you have IMS installed, you can use rexx to read the database and create > sql statements. > > ITschak > > *| **Itschak Mugzach | Director | SecuriTeam Software **|** IronSphere > Platform* *|* *Information Security Continuous Monitoring for Z/OS, zLinux > and IBM I **| * > > *|* *Email**: i_mugz...@securiteam.co.il **|* *Mob**: +972 522 986404 **|* > *Skype**: ItschakMugzach **|* *Web**: www.Securiteam.co.il **|* > > > > > > בתאריך יום ו׳, 17 במאי 2024 ב-19:53 מאת Andrew Wilkinson < > 04f504c2b946-dmarc-requ...@listserv.ua.edu>: > > > Jason,Please excuse the terrible formatting from my phone.Does the DBD > > describe every byte of every segment?If so, you won't need the > > copybooks.Also you will need to reformat the IMS unload file before > > reloading into DB2, at the very least because of keys cascading from > parent > > segment(s).You do need an IMS expert to do this cleanly.Cheers,Andrew > > Original message From: Jason Cai > > Date: 17/05/2024 09:21 (GMT+00:00) To: IBM-MAIN@LISTSERV.UA.EDU, > Andrew > > Wilkinson Cc: Jason Cai < > > ibmm...@foxmail.com> Subject: Re: Assistance with Converting IMS DB to > > MySQL DB Dear Andrew,Thank you for your suggestion. I have some thoughts > on > > the IMS segment layout and defining SQL columns, and I’d like to confirm > > with you:If I have the DBD (Database Description), is it sufficient to > > correctly define the SQL columns? Or do I need the application copybook > in > > addition to the DBD?If the SQL columns are defined correctly, is it > > possible to do the following:a. Unload Data from IMS using PGM=DFSURGU0b. > > Define DB2 Tablesc. Load Data into DB2 using PGM=DSNUTILBThe above are > some > > of my ideas, and I’m not sure if they are correct. I hope to get your > > guidance. Thank you very much for your help!Best regards,Jason Cai > > > > -- > > For IBM-MAIN subscribe / signoff / archive access instructions, > > send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN > > > > -- > For IBM-MAIN subscribe / signoff / archive access instructions, > send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN > -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Assistance with Converting IMS DB to MySQL DB
If you have IMS installed, you can use rexx to read the database and create sql statements. ITschak *| **Itschak Mugzach | Director | SecuriTeam Software **|** IronSphere Platform* *|* *Information Security Continuous Monitoring for Z/OS, zLinux and IBM I **| * *|* *Email**: i_mugz...@securiteam.co.il **|* *Mob**: +972 522 986404 **|* *Skype**: ItschakMugzach **|* *Web**: www.Securiteam.co.il **|* בתאריך יום ו׳, 17 במאי 2024 ב-19:53 מאת Andrew Wilkinson < 04f504c2b946-dmarc-requ...@listserv.ua.edu>: > Jason,Please excuse the terrible formatting from my phone.Does the DBD > describe every byte of every segment?If so, you won't need the > copybooks.Also you will need to reformat the IMS unload file before > reloading into DB2, at the very least because of keys cascading from parent > segment(s).You do need an IMS expert to do this cleanly.Cheers,Andrew > Original message From: Jason Cai > Date: 17/05/2024 09:21 (GMT+00:00) To: IBM-MAIN@LISTSERV.UA.EDU, Andrew > Wilkinson Cc: Jason Cai < > ibmm...@foxmail.com> Subject: Re: Assistance with Converting IMS DB to > MySQL DB Dear Andrew,Thank you for your suggestion. I have some thoughts on > the IMS segment layout and defining SQL columns, and I’d like to confirm > with you:If I have the DBD (Database Description), is it sufficient to > correctly define the SQL columns? Or do I need the application copybook in > addition to the DBD?If the SQL columns are defined correctly, is it > possible to do the following:a. Unload Data from IMS using PGM=DFSURGU0b. > Define DB2 Tablesc. Load Data into DB2 using PGM=DSNUTILBThe above are some > of my ideas, and I’m not sure if they are correct. I hope to get your > guidance. Thank you very much for your help!Best regards,Jason Cai > > -- > For IBM-MAIN subscribe / signoff / archive access instructions, > send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN > -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Assistance with Converting IMS DB to MySQL DB
Jason,Please excuse the terrible formatting from my phone.Does the DBD describe every byte of every segment?If so, you won't need the copybooks.Also you will need to reformat the IMS unload file before reloading into DB2, at the very least because of keys cascading from parent segment(s).You do need an IMS expert to do this cleanly.Cheers,Andrew Original message From: Jason Cai Date: 17/05/2024 09:21 (GMT+00:00) To: IBM-MAIN@LISTSERV.UA.EDU, Andrew Wilkinson Cc: Jason Cai Subject: Re: Assistance with Converting IMS DB to MySQL DB Dear Andrew,Thank you for your suggestion. I have some thoughts on the IMS segment layout and defining SQL columns, and I’d like to confirm with you:If I have the DBD (Database Description), is it sufficient to correctly define the SQL columns? Or do I need the application copybook in addition to the DBD?If the SQL columns are defined correctly, is it possible to do the following:a. Unload Data from IMS using PGM=DFSURGU0b. Define DB2 Tablesc. Load Data into DB2 using PGM=DSNUTILBThe above are some of my ideas, and I’m not sure if they are correct. I hope to get your guidance. Thank you very much for your help!Best regards,Jason Cai -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Assistance with Converting IMS DB to MySQL DB
The only fields that are required to be defined in a DBD are the keys, indexed fields, and any other fields that the application wants to be able to reference in Segment Search Arguments (SSAs). In the IMS system I work on, the 99% of the segment fields are not defined in the DBD. And even if they are, IMS is limited to 8 character field names, with only a few field types. In our system we only define fields as binary or "X". X fields could be packed decimal, zoned decimal, or alphanumeric. They could hold dates. If they're numbers there could be an implied decimal point. There could be a sign, or it could be unsigned. None of that can be determined from the DBD. Do you have access to any IMS tools beyond what's included in the IMS base product? For example, if you have the Broadcom IMS tools... * You can unload a database from an image copy. That is, create the unload format file from an image copy input * The Database Organizer Extractor can create a flat file from a database, which /in some cases/ is a lot easier to process than an unload file. If you have File Manager/IMS, it can create an extract file that includes the concatenated keys to each segment. That also can be easier to process than an IMS unload. -Original Message- From: IBM Mainframe Discussion List On Behalf Of Jason Cai Sent: Friday, May 17, 2024 3:21 AM To: IBM-MAIN@LISTSERV.UA.EDU Subject: Re: Assistance with Converting IMS DB to MySQL DB Dear Andrew, Thank you for your suggestion. I have some thoughts on the IMS segment layout and defining SQL columns, and I’d like to confirm with you: If I have the DBD (Database Description), is it sufficient to correctly define the SQL columns? Or do I need the application copybook in addition to the DBD? If the SQL columns are defined correctly, is it possible to do the following: a. Unload Data from IMS using PGM=DFSURGU0 b. Define DB2 Tables c. Load Data into DB2 using PGM=DSNUTILB The above are some of my ideas, and I’m not sure if they are correct. I hope to get your guidance. Thank you very much for your help! Best regards, Jason Cai -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Assistance with Converting IMS DB to MySQL DB
Jason, See embedded notes. My suggestion is that you should engage a consultant who is knowledgeable in IMS and relational data bases to accomplish this. It is non-trivial and full of pitfalls. Tom Harper Phoenix Software International On 5/16/2024 7:42 PM, Jason Cai wrote: Dear all, We are currently undertaking a project to convert some 15-year-old IMS database, for which we have some full image copy on tape, to a MySQL database on a Linux platform. To ensure the success of this migration, we would greatly appreciate your expertise and guidance on the following matters: Is it feasible to recover the IMS full image copy without an IMS DB system, or is the presence of an IMS DB system an absolute necessity for this migration process? Yes, it is possible, but not likely to succeed unless you have detailed IMS knowledge. Image copies may be made many different ways with many different utilities, and their format is different for different types of IMS data bases. If the image copy you have was made with a BMC product, for example, having an IMS DB system won't help you unless you have the BMC product to restore the data base if the BMC image copy was commpressed. Once we have successfully restored the IMS database files to disk, we are considering two potential approaches: a) Utilizing the IMS unload utility to convert the database files into a PS format, followed by developing a program to read the PS file and import the data into MySQL. It is true that the IMS unload utility will unload to a PS format, but it is not a simple format to process. Also, if it is a DEDB data base, the IMS unload utility does not supprot that, and the same is true of a SHISAM data base. Also, if the data base contains compressed segments, you will need the compress/expand routines successfully unload it. Does this data base contain logical relationships? If it does, converting it to MySQL requires expert knowledge and experience. b) Directly reading the IMS database files and writing the data into DB2, and then subsequently converting the data from DB2 to MySQL. To do this you will of course need an IMS DB system plus any compress / expand products, and possible Partitioned Data Base Facility (PDF) from BMC if the data base was using that. Are there any specific constraints or limitations that we should be aware of, such as the mandatory requirement of an IMS DB system for this migration? Are there any relevant tools or utilities available that could assist us in this process and help streamline the migration? We would be immensely grateful if you could provide your insights on which of the two approaches mentioned above would be more feasible, given our current situation. Additionally, if there are any alternative methods or strategies that you believe might be suitable for our specific case, we would greatly appreciate your suggestions. Your expertise and knowledge in this area are highly valued, and any advice or recommendations you can offer to help us navigate this complex migration process effectively would be sincerely appreciated. Thank you in advance for your time and consideration. We eagerly await your response and look forward to benefiting from your guidance. Best regards, Jason Cai -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN This e-mail message, including any attachments, appended messages and the information contained therein, is for the sole use of the intended recipient(s). If you are not an intended recipient or have otherwise received this email message in error, any use, dissemination, distribution, review, storage or copying of this e-mail message and the information contained therein is strictly prohibited. If you are not an intended recipient, please contact the sender by reply e-mail and destroy all copies of this email message and do not otherwise utilize or retain this email message or any or all of the information contained therein. Although this email message and any attachments or appended messages are believed to be free of any virus or other defect that might affect any computer system into which it is received and opened, it is the responsibility of the recipient to ensure that it is virus free and no responsibility is accepted by the sender for any loss or damage arising in any way from its opening or use. -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Assistance with Converting IMS DB to MySQL DB
Dear Andrew, Thank you for your suggestion. I have some thoughts on the IMS segment layout and defining SQL columns, and I’d like to confirm with you: If I have the DBD (Database Description), is it sufficient to correctly define the SQL columns? Or do I need the application copybook in addition to the DBD? If the SQL columns are defined correctly, is it possible to do the following: a. Unload Data from IMS using PGM=DFSURGU0 b. Define DB2 Tables c. Load Data into DB2 using PGM=DSNUTILB The above are some of my ideas, and I’m not sure if they are correct. I hope to get your guidance. Thank you very much for your help! Best regards, Jason Cai -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Re: Assistance with Converting IMS DB to MySQL DB
Jason,There is an important step you did not list: understanding the IMS segment layout so that you can define the SQL columns correctly.For that you will need the application copybook.Cheers,Andrew -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN
Assistance with Converting IMS DB to MySQL DB
Dear all, We are currently undertaking a project to convert some 15-year-old IMS database, for which we have some full image copy on tape, to a MySQL database on a Linux platform. To ensure the success of this migration, we would greatly appreciate your expertise and guidance on the following matters: Is it feasible to recover the IMS full image copy without an IMS DB system, or is the presence of an IMS DB system an absolute necessity for this migration process? Once we have successfully restored the IMS database files to disk, we are considering two potential approaches: a) Utilizing the IMS unload utility to convert the database files into a PS format, followed by developing a program to read the PS file and import the data into MySQL. b) Directly reading the IMS database files and writing the data into DB2, and then subsequently converting the data from DB2 to MySQL. Are there any specific constraints or limitations that we should be aware of, such as the mandatory requirement of an IMS DB system for this migration? Are there any relevant tools or utilities available that could assist us in this process and help streamline the migration? We would be immensely grateful if you could provide your insights on which of the two approaches mentioned above would be more feasible, given our current situation. Additionally, if there are any alternative methods or strategies that you believe might be suitable for our specific case, we would greatly appreciate your suggestions. Your expertise and knowledge in this area are highly valued, and any advice or recommendations you can offer to help us navigate this complex migration process effectively would be sincerely appreciated. Thank you in advance for your time and consideration. We eagerly await your response and look forward to benefiting from your guidance. Best regards, Jason Cai -- For IBM-MAIN subscribe / signoff / archive access instructions, send email to lists...@listserv.ua.edu with the message: INFO IBM-MAIN