• Introducing Dark Mode! Switch by clicking on the lightbulb icon next to Search or by clicking on Default style at the bottom left of the page!

Read/Write/Update to MS SQL database from E1

nkuebelbeck

VIP Member
We have an external MS MVC app that our customer use to view order/invoice etc etc info. It uses data from ms sql that gets updated from multiple systems. We have some RPG pushing stuff from E1 DB2 stuff (invoicing) to this SQL. We are converting a section of the business from a legacy app to jde sales order entry. Rather than learn some new fangled RPG,or maintain the RPG stuff, I'm wondering if it's possible to create a UBE that could read/write/update this external sql. In my mind I know that E1 can use different RDMS(s).

Please advise. I've done some searching through the KG but it's just not clear cut.
 

johndanter

Legendary Poster
Do you mean a dropped dummy view in E1?

So you create a table that matches the column structure of your external table, create it in E1, create a view over it too.
Then drop the new E1 table in SQL on the DB and create a view to your external table with the same name as the E1 table.
Grant the view the correct update/insert/delete rights and you're good to go.

You can then do Table I/O in E1 or even use a BSVW to see/read the data
 

nkuebelbeck

VIP Member
Do you mean a dropped dummy view in E1?

So you create a table that matches the column structure of your external table, create it in E1, create a view over it too.
Then drop the new E1 table in SQL on the DB and create a view to your external table with the same name as the E1 table.
Grant the view the correct update/insert/delete rights and you're good to go.

You can then do Table I/O in E1 or even use a BSVW to see/read the data
I would think it's slightly more native than what I believe you are talking about.
 

JMast

Reputable Poster
I would think it's slightly more native than what I believe you are talking about.
If I understand your response, you are thinking of creating a new database data source in JDE that references your SQL Server and would have the MVC table(s) in the OCMs. John's approach is the typical way to handle your situation precisely because it avoids having to do the detail (and future maintenance) of a datasource for just a few specific tables that could change in the future.

Having the table(s) tightly defined in JDE is useful if they are involved in transactional processing or frequent use by JDE users. The only other benefit would be if the MVC system had to be updated in real time/near real time.
Another option is the good old Extract,Transform,Load. Having a straightforward JDE UBE write out to a csv which is then processed into SQL through Integration Services might be a faster and, certainly, more flexible option.

While it seems efficient to have everything built into native JDE, it may be more expensive down the road. If your MVC system architecture changes, you will have to do rework and testing in JDE to handle it. Using ETL, JDE will most likely stay the same (unless a new piece of data is needed). Integration Services is very flexible, so it would be the best place to handle changes in table layout or data transformations. Also, performance using ETL may actually be faster depending on your architecture.

Jer
 

nkuebelbeck

VIP Member
If I understand your response, you are thinking of creating a new database data source in JDE that references your SQL Server and would have the MVC table(s) in the OCMs. John's approach is the typical way to handle your situation precisely because it avoids having to do the detail (and future maintenance) of a datasource for just a few specific tables that could change in the future.

Having the table(s) tightly defined in JDE is useful if they are involved in transactional processing or frequent use by JDE users. The only other benefit would be if the MVC system had to be updated in real time/near real time.
Another option is the good old Extract,Transform,Load. Having a straightforward JDE UBE write out to a csv which is then processed into SQL through Integration Services might be a faster and, certainly, more flexible option.

While it seems efficient to have everything built into native JDE, it may be more expensive down the road. If your MVC system architecture changes, you will have to do rework and testing in JDE to handle it. Using ETL, JDE will most likely stay the same (unless a new piece of data is needed). Integration Services is very flexible, so it would be the best place to handle changes in table layout or data transformations. Also, performance using ETL may actually be faster depending on your architecture.

Jer
Thank you for the detailed response.

Sounds like I have options for the business to consider.
 

jdelisths

Reputable Poster
I agree with JMast on using an ETL tool to accomplish this. SQL Integration Services is probably the best option for this.

On the virtual view and table i/o option: (To my knowledge and please correct me if this is incorrect), you cannot write directly to a SQL database from a UBE running on iSeries. To do that, the data source must use JDENET and you must have services running on a Windows based server to handle the SQL queries. You might as well just submit the UBE to the Windows based sever.
 

johndanter

Legendary Poster
On the virtual view and table i/o option: (To my knowledge and please correct me if this is incorrect), you cannot write directly to a SQL database from a UBE running on iSeries.
I'm not on a 400, I'm on Unix but I made sure my view was granted the correct privileges based on the JDE User account and it's fine. I can delete, update, insert and read fine from a UBE.
 
Top