Table Comversion on Mainframe database

[forjoydeep1]

Hi -
I have never done table conversion against mainframe databases. I have used AS400 as my source data and treated the same as foreign tables. Can we do the same thing with mainframe files too. That is create a datasource and pull the mainframe files as foreign tables and map them to jde e1 ?
OR do we need to convert the mainframe tables into flat files and then read from the flat files ?

Thanks,
Joydeep

 

[JEMILLER]

JDE E1 does not have a working OS/390 DB2 connection. You will see a datasource platform type in the UDC's for OS/390 DB2 (type "D") but I don't think it is useable. I believe in the early days of OneWorld someone thought they might support the mainframe as a platform but never did.

Are you running this table conversion on Windows? If so I imagine that an ODBC connection to the Mainframe DB2 could be created assuming that the Windows DB2 Connect client is installed. The foreign tables could then be referenced through this ODBC connections.


There are some other approaches that I can think of depending on your platform such as DB2 Federation, SQL Server ODBC linked server to z/OS DB2, iSeries remote connection to z/OS DB2.

What platform will this table conversion run on?

 

[forjoydeep1]

Thanks Justin for your inputs. At this point the project is at a very early phase to decide whether we the platform will be Windows/Unix/AS400. But I think I understood your points. So using Windows we should be able to have datasources pointing to z/OS. Also using AS400 we could do it in the similar way you think ? Is the I-series remote connection able to pull the foreign tables. Can you please provide any document that explains creation of I-series remote connection...because in the past I had issues I remember where I was using data sources on my fat client (windows) running table conversions pulling foreign tables from World to JDE E1 but when we submitted the same jobs on server it did not work (some LPAR issue as it was not allowing one LPAR to access tables on the other LPAR)....now i think we should have gone for I-series remote connection......any inputs please ?

Thanks,
Joydeep

 

[JEMILLER]

Hi Joydeep,

I have never tested an ODBC connection to the mainframe as a third-party table conversion source but I would expect it to work since that is the purpose of the third-party ODBC connection. It should work with any ODBC compliant connection that respects a SQL-89 syntax.

For iSeries to iSeries connections you'll need to read up on relational database entries (WRKRDBDIRE) and EDRSQL. Some key points are:

The remote database must be registed in the local machines Relational Database Entries

There must be a matching user id on both local and remote machine with the same password. In the case of a Table Conversion this would be the system account used to run the TC UBE. This will usually be iSeries profile JDE.

The QXDAEDRSQL service must be started on the remote machine.

There are a variety of issues you can run into related to PTF's being out of synch between the two hosts, System SQL package issues on the target. In the CNC space we run into these issue when trying to connect a Dev AS/400 to System or DD tables hosted on a Production AS/400.

Have a look at these Oracle support documents for more info and possible issues you might encounter with remote connections:

E1: TC: Foreign Table Connection via AS/400 EDRSQL Fails (Doc ID 644598.1)

E1: OS: INSTALLSP, STRNET and SQL0950 Relational Database Directory Errors (Doc ID 642400.1)

For iSeries to mainframe DB2 or other DB2 links see this article. I think it sums up all the combinations of cross-platform DB2 connectivity nicely.

http://www.ibm.com/developerworks/data/library/techarticle/dm-0312chong/index.html