Skip to main content

Converting from DB2 to Oracle

  • June 10, 2013
  • 10 replies
  • 0 views
D

Hi

We have a MF COBOL system that is developed in Netexpress 5.1. It is currently released using Object COBOL V4.0. We are planning to move to distribution of Netexpress version in the future although as ever, customers requirements come first. We have a customer who are requesting that the existing system is delivered using Oracle and not the existing state of affairs whereby DB2 is used.

The system has pretty generic SQL statements, which only exist within a 'file handling' program(s). Most of this SQL is simply a conversion from the ISAM single record retrieval code written many years ago. More of a concern is the DB utility program that was written by a previous employee in my position. This is a class based program that allows us to bind, backup, drop tables/database, restore, reorganise, etc, etc. It is possible that this program was previously supplied by MF or IBM for use by application developers.

If there are any differences between the existing SQL code and the new required code by Oracle then I don't envisage any issues at the moment changing this as I can deal with them as I encounter them. Plus, its just code isn't it?

Does anybody have any suggestions or tips on what needs to be done to convert this system to run with Oracle DB? Apologies if I've not provided all the required information here, but I'm going through a learning curve myself.

Many thanks

Ian


#oracledb2

10 replies

Chris Glazier
Forum|alt.badge.img+3

Hi

We have a MF COBOL system that is developed in Netexpress 5.1. It is currently released using Object COBOL V4.0. We are planning to move to distribution of Netexpress version in the future although as ever, customers requirements come first. We have a customer who are requesting that the existing system is delivered using Oracle and not the existing state of affairs whereby DB2 is used.

The system has pretty generic SQL statements, which only exist within a 'file handling' program(s). Most of this SQL is simply a conversion from the ISAM single record retrieval code written many years ago. More of a concern is the DB utility program that was written by a previous employee in my position. This is a class based program that allows us to bind, backup, drop tables/database, restore, reorganise, etc, etc. It is possible that this program was previously supplied by MF or IBM for use by application developers.

If there are any differences between the existing SQL code and the new required code by Oracle then I don't envisage any issues at the moment changing this as I can deal with them as I encounter them. Plus, its just code isn't it?

Does anybody have any suggestions or tips on what needs to be done to convert this system to run with Oracle DB? Apologies if I've not provided all the required information here, but I'm going through a learning curve myself.

Many thanks

Ian


#oracledb2

I guess the first question to ask is how are you currently accessing DB2 and how do you plan on accessing Oracle?

For both DB2 and Oracle there are more than one way to use embedded SQL against these products.

For DB2 you can use the DB2 directive which then will compile the program using the DB2 External Compiler Module (ECM) which actually interfaces to the IBM precompiler API,

You could also access DB2 using OpenESQL which is the Micro Focus precompiler for use with ODBC drivers. This would be turned on using the directive SQL(DBMAN=ODBC)

Likewise for Oracle you can use the COBSQL directives that would look something like:

PREPROCESS(cobsql) CSQLT=ORACLE8

This provides an interface to the Oracle Pro*COBOL precompiler.

You can also access Oracle using OpenESQL using the ODBC driver for Oracle. Again this would be turned on using the directive SQL(DBMAN=ODBC).

If you used OpenESQL with DB2 then moving to Oracle should be a pretty straightforward process as you would mostly just need to change the ODBC driver that you are using.

If you are using the DB2 ECM then it can become more involved because certain language features are only compatible with DB2 and would have to be converted.

If you are sticking to ANSI SQL type statements then it shouldn't be too difficult.

If you are looking to perhaps move to using Visual COBOL and managed code one day then I would highly recommend that you use OpenESQL for your Oracle application as Pro*COBOL will tie you in to Oracle and native code whereas OpenESQL is supported for both native ODBC and managed code ADO Providers and your embedded SQL syntax can remain the same.

I am not sure about the DB maintenance utility that you talk about.

What is the name of this program that you are using?

Thanks

D

Hi

We have a MF COBOL system that is developed in Netexpress 5.1. It is currently released using Object COBOL V4.0. We are planning to move to distribution of Netexpress version in the future although as ever, customers requirements come first. We have a customer who are requesting that the existing system is delivered using Oracle and not the existing state of affairs whereby DB2 is used.

The system has pretty generic SQL statements, which only exist within a 'file handling' program(s). Most of this SQL is simply a conversion from the ISAM single record retrieval code written many years ago. More of a concern is the DB utility program that was written by a previous employee in my position. This is a class based program that allows us to bind, backup, drop tables/database, restore, reorganise, etc, etc. It is possible that this program was previously supplied by MF or IBM for use by application developers.

If there are any differences between the existing SQL code and the new required code by Oracle then I don't envisage any issues at the moment changing this as I can deal with them as I encounter them. Plus, its just code isn't it?

Does anybody have any suggestions or tips on what needs to be done to convert this system to run with Oracle DB? Apologies if I've not provided all the required information here, but I'm going through a learning curve myself.

Many thanks

Ian


#oracledb2

Hi Chris

Thanks for your speedy response.

We are using the DB2 directives:DB2(DB=<dbname>) DB2(DBMAN=DB2) DB2(BIND=<progname>.BND). This is the Netexpress setting. When we release using the Object COBOL v4.0 we run a DB2 pre-processor before compilation:

DB2CMD -c -w -i RUNDB2 <dbname> <outname> <progname>

MFENV 32 COBOL.EXE <outname>, <progname>.obj, mylist.lst, REF STICKY-LINKAGE MFOO OOCTRL(-G N-P Q-W) LINKCOUNT "512" LOCALCOUNT "512" /CONSTANT VN (1700)

As I'm sure you are aware, the DB2CMD process produces a .BND file, although I'm not 100% sure what the purpose of this is and whether it could be omitted if we go through the ODBC route (whether to DB2, Oracle or any other db flavour).

I agree with you that it is not desirable to connect to Oracle using the precompiler. We'd much rather do OpenESQL, although as I stated, we recompile the system for release purposes at the moment using OCV4.0. I assume I'd have to do some calls to ODBC32.DLL to achieve this instead of OpenESQL?

I am pretty sure that the SQL statements in place are very basic and will probably be ANSI SQL. If not, we will make them so.

The DB maintenance utility is simply a COBOL program that allows us to manage the databases. It allows us to create, backup, import, bind, etc. It makes calls to "sqlbndx" (bind), "sqlgreot" (reorganise), "sqlgstat" (create stats), etc. I'm pretty sure these are DB2 specific calls.

The main product does not perform any of these type of calls and uses EXEC SQl statements to connect, read, update, etc.

Regards

Ian

D

Hi

We have a MF COBOL system that is developed in Netexpress 5.1. It is currently released using Object COBOL V4.0. We are planning to move to distribution of Netexpress version in the future although as ever, customers requirements come first. We have a customer who are requesting that the existing system is delivered using Oracle and not the existing state of affairs whereby DB2 is used.

The system has pretty generic SQL statements, which only exist within a 'file handling' program(s). Most of this SQL is simply a conversion from the ISAM single record retrieval code written many years ago. More of a concern is the DB utility program that was written by a previous employee in my position. This is a class based program that allows us to bind, backup, drop tables/database, restore, reorganise, etc, etc. It is possible that this program was previously supplied by MF or IBM for use by application developers.

If there are any differences between the existing SQL code and the new required code by Oracle then I don't envisage any issues at the moment changing this as I can deal with them as I encounter them. Plus, its just code isn't it?

Does anybody have any suggestions or tips on what needs to be done to convert this system to run with Oracle DB? Apologies if I've not provided all the required information here, but I'm going through a learning curve myself.

Many thanks

Ian


#oracledb2

Hi Chris

Thanks for your speedy response.

We are using the DB2 directives:DB2(DB=<dbname>) DB2(DBMAN=DB2) DB2(BIND=<progname>.BND). This is the Netexpress setting. When we release using the Object COBOL v4.0 we run a DB2 pre-processor before compilation:

DB2CMD -c -w -i RUNDB2 <dbname> <outname> <progname>

MFENV 32 COBOL.EXE <outname>, <progname>.obj, mylist.lst, REF STICKY-LINKAGE MFOO OOCTRL(-G N-P Q-W) LINKCOUNT "512" LOCALCOUNT "512" /CONSTANT VN (1700)

As I'm sure you are aware, the DB2CMD process produces a .BND file, although I'm not 100% sure what the purpose of this is and whether it could be omitted if we go through the ODBC route (whether to DB2, Oracle or any other db flavour).

I agree with you that it is not desirable to connect to Oracle using the precompiler. We'd much rather do OpenESQL, although as I stated, we recompile the system for release purposes at the moment using OCV4.0. I assume I'd have to do some calls to ODBC32.DLL to achieve this instead of OpenESQL?

I am pretty sure that the SQL statements in place are very basic and will probably be ANSI SQL. If not, we will make them so.

The DB maintenance utility is simply a COBOL program that allows us to manage the databases. It allows us to create, backup, import, bind, etc. It makes calls to "sqlbndx" (bind), "sqlgreot" (reorganise), "sqlgstat" (create stats), etc. I'm pretty sure these are DB2 specific calls.

The main product does not perform any of these type of calls and uses EXEC SQl statements to connect, read, update, etc.

Regards

Ian

D

Hi

We have a MF COBOL system that is developed in Netexpress 5.1. It is currently released using Object COBOL V4.0. We are planning to move to distribution of Netexpress version in the future although as ever, customers requirements come first. We have a customer who are requesting that the existing system is delivered using Oracle and not the existing state of affairs whereby DB2 is used.

The system has pretty generic SQL statements, which only exist within a 'file handling' program(s). Most of this SQL is simply a conversion from the ISAM single record retrieval code written many years ago. More of a concern is the DB utility program that was written by a previous employee in my position. This is a class based program that allows us to bind, backup, drop tables/database, restore, reorganise, etc, etc. It is possible that this program was previously supplied by MF or IBM for use by application developers.

If there are any differences between the existing SQL code and the new required code by Oracle then I don't envisage any issues at the moment changing this as I can deal with them as I encounter them. Plus, its just code isn't it?

Does anybody have any suggestions or tips on what needs to be done to convert this system to run with Oracle DB? Apologies if I've not provided all the required information here, but I'm going through a learning curve myself.

Many thanks

Ian


#oracledb2

Hi Chris

I have managed to get a test program reading information from an ODBC source in two ways. I have used calls to the ODBC32.DLL. This is cumbersome and doesn't really allow me to use embedded SQL statements other than defining in working storge and supplying this to the DLL when I make the call.

I have also been able to (easily) read a ODBC source using the embedded SQL method and ODBC compiler directives. This is obviously much easier to use and means we can use our embedded SQL.

I don't have a solution for our utility program that is used to backup and restore databases. This currently uses DB2 calls. Can I do similar things with the ODBC32.DLL called from COBOL? Can I do them with embedded SQL/ODBC compiler directives?

Ian

D

Hi

We have a MF COBOL system that is developed in Netexpress 5.1. It is currently released using Object COBOL V4.0. We are planning to move to distribution of Netexpress version in the future although as ever, customers requirements come first. We have a customer who are requesting that the existing system is delivered using Oracle and not the existing state of affairs whereby DB2 is used.

The system has pretty generic SQL statements, which only exist within a 'file handling' program(s). Most of this SQL is simply a conversion from the ISAM single record retrieval code written many years ago. More of a concern is the DB utility program that was written by a previous employee in my position. This is a class based program that allows us to bind, backup, drop tables/database, restore, reorganise, etc, etc. It is possible that this program was previously supplied by MF or IBM for use by application developers.

If there are any differences between the existing SQL code and the new required code by Oracle then I don't envisage any issues at the moment changing this as I can deal with them as I encounter them. Plus, its just code isn't it?

Does anybody have any suggestions or tips on what needs to be done to convert this system to run with Oracle DB? Apologies if I've not provided all the required information here, but I'm going through a learning curve myself.

Many thanks

Ian


#oracledb2

Hi Chris

I have managed to get a test program reading information from an ODBC source in two ways. I have used calls to the ODBC32.DLL. This is cumbersome and doesn't really allow me to use embedded SQL statements other than defining in working storge and supplying this to the DLL when I make the call.

I have also been able to (easily) read a ODBC source using the embedded SQL method and ODBC compiler directives. This is obviously much easier to use and means we can use our embedded SQL.

I don't have a solution for our utility program that is used to backup and restore databases. This currently uses DB2 calls. Can I do similar things with the ODBC32.DLL called from COBOL? Can I do them with embedded SQL/ODBC compiler directives?

Ian

Chris Glazier
Forum|alt.badge.img+3

Hi

We have a MF COBOL system that is developed in Netexpress 5.1. It is currently released using Object COBOL V4.0. We are planning to move to distribution of Netexpress version in the future although as ever, customers requirements come first. We have a customer who are requesting that the existing system is delivered using Oracle and not the existing state of affairs whereby DB2 is used.

The system has pretty generic SQL statements, which only exist within a 'file handling' program(s). Most of this SQL is simply a conversion from the ISAM single record retrieval code written many years ago. More of a concern is the DB utility program that was written by a previous employee in my position. This is a class based program that allows us to bind, backup, drop tables/database, restore, reorganise, etc, etc. It is possible that this program was previously supplied by MF or IBM for use by application developers.

If there are any differences between the existing SQL code and the new required code by Oracle then I don't envisage any issues at the moment changing this as I can deal with them as I encounter them. Plus, its just code isn't it?

Does anybody have any suggestions or tips on what needs to be done to convert this system to run with Oracle DB? Apologies if I've not provided all the required information here, but I'm going through a learning curve myself.

Many thanks

Ian


#oracledb2

Backup and Restore functionality is not part of the ODBC API but is normally handled through proprietary commands of each particular database vendor.

Oracle does have both Backup and Restore commands available.

You may want to look at writing an Oracle stored procedure that handles this and then execute the stored procedure through an ODBC call from COBOL.

Perhaps another customer who has already dealt with such an issue can chime in here with some advice?

Thanks.

D

Hi

We have a MF COBOL system that is developed in Netexpress 5.1. It is currently released using Object COBOL V4.0. We are planning to move to distribution of Netexpress version in the future although as ever, customers requirements come first. We have a customer who are requesting that the existing system is delivered using Oracle and not the existing state of affairs whereby DB2 is used.

The system has pretty generic SQL statements, which only exist within a 'file handling' program(s). Most of this SQL is simply a conversion from the ISAM single record retrieval code written many years ago. More of a concern is the DB utility program that was written by a previous employee in my position. This is a class based program that allows us to bind, backup, drop tables/database, restore, reorganise, etc, etc. It is possible that this program was previously supplied by MF or IBM for use by application developers.

If there are any differences between the existing SQL code and the new required code by Oracle then I don't envisage any issues at the moment changing this as I can deal with them as I encounter them. Plus, its just code isn't it?

Does anybody have any suggestions or tips on what needs to be done to convert this system to run with Oracle DB? Apologies if I've not provided all the required information here, but I'm going through a learning curve myself.

Many thanks

Ian


#oracledb2

Chris

As you can see, I didn't get any further assistance.

We have decided we will deal with the DB 'utility' program separately. We are now considering the Pro*COBOL pre-compiler as an option as hopefully our SQL statements will already be suitable. Do you know if the is a 'how to' or Wiki article on how we can integrate with it?

Regards

Ian

D

Hi

We have a MF COBOL system that is developed in Netexpress 5.1. It is currently released using Object COBOL V4.0. We are planning to move to distribution of Netexpress version in the future although as ever, customers requirements come first. We have a customer who are requesting that the existing system is delivered using Oracle and not the existing state of affairs whereby DB2 is used.

The system has pretty generic SQL statements, which only exist within a 'file handling' program(s). Most of this SQL is simply a conversion from the ISAM single record retrieval code written many years ago. More of a concern is the DB utility program that was written by a previous employee in my position. This is a class based program that allows us to bind, backup, drop tables/database, restore, reorganise, etc, etc. It is possible that this program was previously supplied by MF or IBM for use by application developers.

If there are any differences between the existing SQL code and the new required code by Oracle then I don't envisage any issues at the moment changing this as I can deal with them as I encounter them. Plus, its just code isn't it?

Does anybody have any suggestions or tips on what needs to be done to convert this system to run with Oracle DB? Apologies if I've not provided all the required information here, but I'm going through a learning curve myself.

Many thanks

Ian


#oracledb2

Chris

As you can see, I didn't get any further assistance.

We have decided we will deal with the DB 'utility' program separately. We are now considering the Pro*COBOL pre-compiler as an option as hopefully our SQL statements will already be suitable. Do you know if the is a 'how to' or Wiki article on how we can integrate with it?

Regards

Ian

D

Hi

We have a MF COBOL system that is developed in Netexpress 5.1. It is currently released using Object COBOL V4.0. We are planning to move to distribution of Netexpress version in the future although as ever, customers requirements come first. We have a customer who are requesting that the existing system is delivered using Oracle and not the existing state of affairs whereby DB2 is used.

The system has pretty generic SQL statements, which only exist within a 'file handling' program(s). Most of this SQL is simply a conversion from the ISAM single record retrieval code written many years ago. More of a concern is the DB utility program that was written by a previous employee in my position. This is a class based program that allows us to bind, backup, drop tables/database, restore, reorganise, etc, etc. It is possible that this program was previously supplied by MF or IBM for use by application developers.

If there are any differences between the existing SQL code and the new required code by Oracle then I don't envisage any issues at the moment changing this as I can deal with them as I encounter them. Plus, its just code isn't it?

Does anybody have any suggestions or tips on what needs to be done to convert this system to run with Oracle DB? Apologies if I've not provided all the required information here, but I'm going through a learning curve myself.

Many thanks

Ian


#oracledb2

Chris

As you can see, I didn't get any further assistance.

We have decided we will deal with the DB 'utility' program separately. We are now considering the Pro*COBOL pre-compiler as an option as hopefully our SQL statements will already be suitable. Do you know if the is a 'how to' or Wiki article on how we can integrate with it?

Regards

Ian

Chris Glazier
Forum|alt.badge.img+3

Hi

We have a MF COBOL system that is developed in Netexpress 5.1. It is currently released using Object COBOL V4.0. We are planning to move to distribution of Netexpress version in the future although as ever, customers requirements come first. We have a customer who are requesting that the existing system is delivered using Oracle and not the existing state of affairs whereby DB2 is used.

The system has pretty generic SQL statements, which only exist within a 'file handling' program(s). Most of this SQL is simply a conversion from the ISAM single record retrieval code written many years ago. More of a concern is the DB utility program that was written by a previous employee in my position. This is a class based program that allows us to bind, backup, drop tables/database, restore, reorganise, etc, etc. It is possible that this program was previously supplied by MF or IBM for use by application developers.

If there are any differences between the existing SQL code and the new required code by Oracle then I don't envisage any issues at the moment changing this as I can deal with them as I encounter them. Plus, its just code isn't it?

Does anybody have any suggestions or tips on what needs to be done to convert this system to run with Oracle DB? Apologies if I've not provided all the required information here, but I'm going through a learning curve myself.

Many thanks

Ian


#oracledb2

The integration with Pro*COBOL is covered in the Net Express documentation under Programming-->Database Access-->COBSQL.

If you are trying to use this with an older Micro Focus product that does not support cobsql then you will have to run the code through the Oracle procob command prior to compiling it with MF COBOL.

 

You will have to check the Oracle documentation for the use of procob.

Recent badge winners

Explore all badges
Terms & ConditionsAccessibility statement