Using OFBiz with DB2 for i (AS400) database
Locked
Using OFBiz with DB2 for i (AS400) database
 
|
Hi, I was trying to connect OFBiz to the database which is included in IBMi operating system.
This database is "DB2 for i", and as DERBY (the db OFBiz includes and uses by default) was bought by IBM and certified with its own db, I thought it shouldn't be so hard to use DB2 for i instead. I had to do the following: - copy the file framework\entity\fieldtype\fieldtypederby.xml into the new framework\entity\fieldtype\fieldtypedb2fori.xml file - replace sql-type="DOUBLE" with sql-type="FLOAT" (DB2 for i specification names FLOAT to DOUBLES) - create the db2fori entry in framework\entity\config\entityengine.xml (copying the entries of derby) and pointing to the new file, as with the other databases - include lib for db2fori connection (I used jt400-6.7.jar): copied into framework\entity\lib\jdbc - also, schemas have to be manually created (I don't know if there is a command to create schemas automatically if they don't exist as ofbiz does with derby db) (create schema OFBIZ, create schema OFBIZOLAP, create schema OFBIZTENAN) doing this, and configuring entityengine.xml accordingly, I could use OFBiz with DB2 for i, the database that comes with IBMi systems. Just informing this as some other could find this useful. Also, is this worth to consider it as a modification to ofbiz to make it more generic? Cheers! |
Re: Using OFBiz with DB2 for i (AS400) database
|
|
Gabriel,
There have been questions posted in the past about db2 support. If you would file an improvement/new feature JIRA and provide patches, I am sure these enhancements would be regarded as welcome. Regards, Pierre Smits *ORRTIZ.COM <http://www.orrtiz.com>* Services & Solutions for Cloud- Based Manufacturing, Professional Services and Retail & Trade http://www.orrtiz.com On Wed, Apr 23, 2014 at 2:55 PM, Gabriel Oberreuter <[hidden email]>wrote: > Hi, I was trying to connect OFBiz to the database which is included in IBMi > operating system. > This database is "DB2 for i", and as DERBY (the db OFBiz includes and uses > by default) was bought by IBM and certified with its own db, I thought it > shouldn't be so hard to use DB2 for i instead. > I had to do the following: > > - copy the file framework\entity\fieldtype\fieldtypederby.xml into the new > framework\entity\fieldtype\fieldtypedb2fori.xml file > - replace sql-type="DOUBLE" with sql-type="FLOAT" (DB2 for i specification > names FLOAT to DOUBLES) > - create the db2fori entry in framework\entity\config\entityengine.xml > (copying the entries of derby) and pointing to the new file, as with the > other databases > - include lib for db2fori connection (I used jt400-6.7.jar): copied into > framework\entity\lib\jdbc > - also, schemas have to be manually created (I don't know if there is a > command to create schemas automatically if they don't exist as ofbiz does > with derby db) > (create schema OFBIZ, create schema OFBIZOLAP, create schema > OFBIZTENAN) > > doing this, and configuring entityengine.xml accordingly, I could use OFBiz > with DB2 for i, the database that comes with IBMi systems. > > Just informing this as some other could find this useful. Also, is this > worth to consider it as a modification to ofbiz to make it more generic? > > Cheers! > > > > -- > View this message in context: > http://ofbiz.135035.n4.nabble.com/Using-OFBiz-with-DB2-for-i-AS400-database-tp4650633.html > Sent from the OFBiz - User mailing list archive at Nabble.com. > |
Re: Using OFBiz with DB2 for i (AS400) database
|
|
In reply to this post by Gabriel Oberreuter
For the most part, that is the normal configuration process. I am
surprised we don't have a fieldtype.xml file for DB2. Please feel free to create a Jira ticket and include your DB2 fieldtype.xml file. Adrian Crum Sandglass Software www.sandglass-software.com On 4/23/2014 5:55 AM, Gabriel Oberreuter wrote: > Hi, I was trying to connect OFBiz to the database which is included in IBMi > operating system. > This database is "DB2 for i", and as DERBY (the db OFBiz includes and uses > by default) was bought by IBM and certified with its own db, I thought it > shouldn't be so hard to use DB2 for i instead. > I had to do the following: > > - copy the file framework\entity\fieldtype\fieldtypederby.xml into the new > framework\entity\fieldtype\fieldtypedb2fori.xml file > - replace sql-type="DOUBLE" with sql-type="FLOAT" (DB2 for i specification > names FLOAT to DOUBLES) > - create the db2fori entry in framework\entity\config\entityengine.xml > (copying the entries of derby) and pointing to the new file, as with the > other databases > - include lib for db2fori connection (I used jt400-6.7.jar): copied into > framework\entity\lib\jdbc > - also, schemas have to be manually created (I don't know if there is a > command to create schemas automatically if they don't exist as ofbiz does > with derby db) > (create schema OFBIZ, create schema OFBIZOLAP, create schema OFBIZTENAN) > > doing this, and configuring entityengine.xml accordingly, I could use OFBiz > with DB2 for i, the database that comes with IBMi systems. > > Just informing this as some other could find this useful. Also, is this > worth to consider it as a modification to ofbiz to make it more generic? > > Cheers! > > > > -- > View this message in context: http://ofbiz.135035.n4.nabble.com/Using-OFBiz-with-DB2-for-i-AS400-database-tp4650633.html > Sent from the OFBiz - User mailing list archive at Nabble.com. > |
Re: Using OFBiz with DB2 for i (AS400) database
|
|
Thanks. Then I will file a Jira ticket (hopefully doing it right, never done that before). On Wed, Apr 23, 2014 at 10:27 AM, adrian.crum [via OFBiz] <[hidden email]> wrote: For the most part, that is the normal configuration process. I am -- Gabriel Oberreuter
Mobile: +56 9 85299198 Innovandio S.A. |
Re: Using OFBiz with DB2 for i (AS400) database
Administrator
|
https://cwiki.apache.org/confluence/display/OFBADMIN/OFBiz+Contributors+Best+Practices
Jacques Le 23/04/2014 20:03, Gabriel Oberreuter a écrit : > Thanks. Then I will file a Jira ticket (hopefully doing it right, never > done that before). > > > On Wed, Apr 23, 2014 at 10:27 AM, adrian.crum [via OFBiz] < > [hidden email]> wrote: > >> For the most part, that is the normal configuration process. I am >> surprised we don't have a fieldtype.xml file for DB2. Please feel free >> to create a Jira ticket and include your DB2 fieldtype.xml file. >> >> Adrian Crum >> Sandglass Software >> www.sandglass-software.com >> >> On 4/23/2014 5:55 AM, Gabriel Oberreuter wrote: >> >>> Hi, I was trying to connect OFBiz to the database which is included in >> IBMi >>> operating system. >>> This database is "DB2 for i", and as DERBY (the db OFBiz includes and >> uses >>> by default) was bought by IBM and certified with its own db, I thought >> it >>> shouldn't be so hard to use DB2 for i instead. >>> I had to do the following: >>> >>> - copy the file framework\entity\fieldtype\fieldtypederby.xml into the >> new >>> framework\entity\fieldtype\fieldtypedb2fori.xml file >>> - replace sql-type="DOUBLE" with sql-type="FLOAT" (DB2 for i >> specification >>> names FLOAT to DOUBLES) >>> - create the db2fori entry in framework\entity\config\entityengine.xml >>> (copying the entries of derby) and pointing to the new file, as with the >>> other databases >>> - include lib for db2fori connection (I used jt400-6.7.jar): copied into >>> framework\entity\lib\jdbc >>> - also, schemas have to be manually created (I don't know if there is a >>> command to create schemas automatically if they don't exist as ofbiz >> does >>> with derby db) >>> (create schema OFBIZ, create schema OFBIZOLAP, create schema OFBIZTENAN) >>> >>> doing this, and configuring entityengine.xml accordingly, I could use >> OFBiz >>> with DB2 for i, the database that comes with IBMi systems. >>> >>> Just informing this as some other could find this useful. Also, is this >>> worth to consider it as a modification to ofbiz to make it more generic? >>> >>> Cheers! >>> >>> >>> >>> -- >>> View this message in context: >> http://ofbiz.135035.n4.nabble.com/Using-OFBiz-with-DB2-for-i-AS400-database-tp4650633.html >>> Sent from the OFBiz - User mailing list archive at Nabble.com. >>> >> >> ------------------------------ >> If you reply to this email, your message will be added to the discussion >> below: >> >> http://ofbiz.135035.n4.nabble.com/Using-OFBiz-with-DB2-for-i-AS400-database-tp4650633p4650637.html >> To unsubscribe from Using OFBiz with DB2 for i (AS400) database, click >> here< >> . >> NAML<http://ofbiz.135035.n4.nabble.com/template/NamlServlet.jtp?macro=macro_viewer&id=instant_html%21nabble%3Aemail.naml&base=nabble.naml.namespaces.BasicNamespace-nabble.view.web.template.NabbleNamespace-nabble.view.web.template.NodeNamespace&breadcrumbs=notify_subscribers%21nabble%3Aemail.naml-instant_emails%21nabble%3Aemail.naml-send_instant_email%21nabble%3Aemail.naml> >> > > -- |
|
|
FYI
We have installed Apache Ofbiz on Debian 7 ("Wheezy") and use DB2 for i AS/400 as database. Since version V7R1 (AS/400, db2) there is no need for an own fieldtype.xml - just use the derby-fieldtypes like this: <datasource name="devdb2" helper-class="org.ofbiz.entity.datasource.GenericHelperDAO" schema-name="OFBIZ" field-type-name="derby" check-on-start="true" add-missing-on-start="true" use-pk-constraint-names="false" use-indices-unique="false" alias-view-columns="false" use-order-by-nulls="true" offset-style="fetch"> <read-data reader-name="tenant"/> <read-data reader-name="seed"/> <read-data reader-name="seed-initial"/> <read-data reader-name="demo"/> <read-data reader-name="ext"/> <read-data reader-name="ext-test"/> <read-data reader-name="ext-demo"/> <inline-jdbc jdbc-driver="com.ibm.as400.access.AS400JDBCDriver" jdbc-uri="jdbc:as400:192.168.1.10;prompt=false;libraries=ofbiz;lazy close=true;translate binary=true" jdbc-username="OFBIZUSR" jdbc-password="***" isolation-level="ReadCommitted" pool-minsize="2" pool-maxsize="250" time-between-eviction-runs-millis="600000"/> </datasource> I've copied the jdbc parms (lazy close...) from my hibernate projects. I'll check this later. The demo and our first steps running without problems. Important: - You have the create the schemas manually. (Gabriel wrote this already) - the codepage of the user (in my case OFBIZUSR) is used for the create table (CCSID) --> actually we use an ebcdic codepage, this allow green screen programs to access the data with native database commands |
Re: Using OFBiz with DB2 for i (AS400) database
|
|
Hi Gabriel, Robert,
A JIRA issue already exists. See https://issues.apache.org/jira/browse/OFBIZ-1607 Please feel free to share your solution as a patch to that issue and I am sure it will get reviewed and committed. Best regards. Pierre Smits *ORRTIZ.COM <http://www.orrtiz.com>* Services & Solutions for Cloud- Based Manufacturing, Professional Services and Retail & Trade http://www.orrtiz.com On Tue, Apr 14, 2015 at 1:37 PM, robertpic <[hidden email]> wrote: > FYI > > We have installed Apache Ofbiz on Debian 7 ("Wheezy") and use DB2 for i > AS/400 as database. > > Since version V7R1 (AS/400, db2) there is no need for an own fieldtype.xml > - > just use the derby-fieldtypes like this: > > <datasource name="devdb2" > helper-class="org.ofbiz.entity.datasource.GenericHelperDAO" > schema-name="OFBIZ" > field-type-name="derby" > check-on-start="true" > add-missing-on-start="true" > use-pk-constraint-names="false" > use-indices-unique="false" > alias-view-columns="false" > use-order-by-nulls="true" > offset-style="fetch"> > <read-data reader-name="tenant"/> > <read-data reader-name="seed"/> > <read-data reader-name="seed-initial"/> > <read-data reader-name="demo"/> > <read-data reader-name="ext"/> > <read-data reader-name="ext-test"/> > <read-data reader-name="ext-demo"/> > > <inline-jdbc > jdbc-driver="com.ibm.as400.access.AS400JDBCDriver" > > jdbc-uri="jdbc:as400:192.168.1.10;prompt=false;libraries=ofbiz;lazy > close=true;translate binary=true" > jdbc-username="OFBIZUSR" > jdbc-password="***" > isolation-level="ReadCommitted" > pool-minsize="2" > pool-maxsize="250" > time-between-eviction-runs-millis="600000"/> > > </datasource> > > I've copied the jdbc parms (lazy close...) from my hibernate projects. I'll > check this later. The demo and our first steps running without problems. > > Important: > - You have the create the schemas manually. (Gabriel wrote this already) > - the codepage of the user (in my case OFBIZUSR) is used for the create > table (CCSID) > --> actually we use an ebcdic codepage, this allow green screen programs to > access the data with native database commands > > > > > > > -- > View this message in context: > http://ofbiz.135035.n4.nabble.com/Using-OFBiz-with-DB2-for-i-AS400-database-tp4650633p4666589.html > Sent from the OFBiz - User mailing list archive at Nabble.com. > |
Re: Using OFBiz with DB2 for i (AS400) database
|
Administrator
|
In reply to this post by robertpic
I propose we copy those params into EntityEngine.xml (with a comment about "Since version V7R1 (AS/400, db2)")
Could you please confirm you did not cross issues later Robert? We would need also to download the JDBC driver with Ant+Ivy... Jacques Le 14/04/2015 13:37, robertpic a écrit : > FYI > > We have installed Apache Ofbiz on Debian 7 ("Wheezy") and use DB2 for i > AS/400 as database. > > Since version V7R1 (AS/400, db2) there is no need for an own fieldtype.xml - > just use the derby-fieldtypes like this: > > <datasource name="devdb2" > helper-class="org.ofbiz.entity.datasource.GenericHelperDAO" > schema-name="OFBIZ" > field-type-name="derby" > check-on-start="true" > add-missing-on-start="true" > use-pk-constraint-names="false" > use-indices-unique="false" > alias-view-columns="false" > use-order-by-nulls="true" > offset-style="fetch"> > <read-data reader-name="tenant"/> > <read-data reader-name="seed"/> > <read-data reader-name="seed-initial"/> > <read-data reader-name="demo"/> > <read-data reader-name="ext"/> > <read-data reader-name="ext-test"/> > <read-data reader-name="ext-demo"/> > > <inline-jdbc > jdbc-driver="com.ibm.as400.access.AS400JDBCDriver" > > jdbc-uri="jdbc:as400:192.168.1.10;prompt=false;libraries=ofbiz;lazy > close=true;translate binary=true" > jdbc-username="OFBIZUSR" > jdbc-password="***" > isolation-level="ReadCommitted" > pool-minsize="2" > pool-maxsize="250" > time-between-eviction-runs-millis="600000"/> > > </datasource> > > I've copied the jdbc parms (lazy close...) from my hibernate projects. I'll > check this later. The demo and our first steps running without problems. > > Important: > - You have the create the schemas manually. (Gabriel wrote this already) > - the codepage of the user (in my case OFBIZUSR) is used for the create > table (CCSID) > --> actually we use an ebcdic codepage, this allow green screen programs to > access the data with native database commands > > > > > > > -- > View this message in context: http://ofbiz.135035.n4.nabble.com/Using-OFBiz-with-DB2-for-i-AS400-database-tp4650633p4666589.html > Sent from the OFBiz - User mailing list archive at Nabble.com. > |
|
|
I wrote since V7R1, because our AS/400 has V7R1 installed. I've checked the docs and find out, that DOUBLE should work at least at version V6R1. I could not test it, but the docs say DOUBLE is ok. Note: Only the fieldtype is problem in early releases. All other stuff (JDBC parameters...) should be the same in all releases. We have not any problems so far. FLOAT and DOUBLE mean the same for DB2/AS400. I have to say: We don't use any ERP functions and the ecommerce-project is on an early stage (~ 15 products). But i have not any problems with float/double in may other projects. I'm not sure about Ant+Ivy. There is an open source version of the jdbc driver at sourceforge: http://sourceforge.net/projects/jt400/ About our project: Actually we try to migrate our existing 40.000 products (online-catalog) into OfBiz. But we have also to record the 30.000 ERP-only producs. So I expect a Go-Live between Q4/2015 - Q1/2016. |
Re: Using OFBiz with DB2 for i (AS400) database
|
Administrator
|
Thanks Robert,
I will document on this basis Jacques Le 21/05/2015 14:35, robertpic a écrit : > Jacques Le Roux wrote >> I propose we copy those params into EntityEngine.xml (with a comment about >> "Since version V7R1 (AS/400, db2)") > I wrote since V7R1, because our AS/400 has V7R1 installed. I've checked the > docs and find out, that DOUBLE should work at least at version V6R1. I could > not test it, but the docs say DOUBLE is ok. > Note: Only the fieldtype is problem in early releases. All other stuff (JDBC > parameters...) should be the same in all releases. > > > Jacques Le Roux wrote >> Could you please confirm you did not cross issues later Robert? > We have not any problems so far. FLOAT and DOUBLE mean the same for > DB2/AS400. > I have to say: We don't use any ERP functions and the ecommerce-project is > on an early stage (~ 15 products). > But i have not any problems with float/double in may other projects. > > > Jacques Le Roux wrote >> We would need also to download the JDBC driver with Ant+Ivy... > I'm not sure about Ant+Ivy. There is an open source version of the jdbc > driver at sourceforge: > http://sourceforge.net/projects/jt400/ > > About our project: > Actually we try to migrate our existing 40.000 products (online-catalog) > <http://online.odoerfer.com/OdKatalog/> into OfBiz. But we have also to > record the 30.000 ERP-only producs. So I expect a Go-Live between Q4/2015 - > Q1/2016. > > > > > > > -- > View this message in context: http://ofbiz.135035.n4.nabble.com/Using-OFBiz-with-DB2-for-i-AS400-database-tp4650633p4668827.html > Sent from the OFBiz - User mailing list archive at Nabble.com. > |
|
|
Our current project status: We've imported about 70.000 products and our cataloges (~ 40.000 variants/products) into ofbiz on db2/AS400. There are some performance issues - (i think) on the client side. I seems that the product/tree always loading the whole tree at once. |
Re: Using OFBiz with DB2 for i (AS400) database
|
Administrator
|
Hi Robert,
I haved copied your proposition for the DB2 data source at r1730616. Please check, thanks! |
|
|
@Jacques Le Roux
DB2 data source at r1730616 looks, fine there are some troubles with the native (AS/400) backup/restore tools (savobj/rstobj) and the contraints. --> actually we do an xml-export, 'll check this out later status update we will go online in about 2 weeks (B2B) ... and B2C in about 2 months  mobile 
|
Re: Using OFBiz with DB2 for i (AS400) database
|
Administrator
|
Thanks for the update Robert,
I wish you best luck for the production release Jacques Le 06/07/2017 à 00:37, robertpic a écrit : > @Jacques Le Roux > > DB2 data source at r1730616 > looks, fine > > there are some troubles with the native (AS/400) backup/restore tools > (savobj/rstobj) and the contraints. > --> actually we do an xml-export, 'll check this out later > > > status update > we will go online in about 2 weeks (B2B) ... and B2C in about 2 months > > <http://ofbiz.135035.n4.nabble.com/file/n4707470/Screenshot_Werkzeug.jpg> > > mobile > <http://ofbiz.135035.n4.nabble.com/file/n4707470/Screenshot_Mobile.jpg> > > > > -- > View this message in context: http://ofbiz.135035.n4.nabble.com/Using-OFBiz-with-DB2-for-i-AS400-database-tp4650633p4707470.html > Sent from the OFBiz - User mailing list archive at Nabble.com. > |
|
In reply to this post by robertpic
Hi Robert,
Just out of curiosity, are you folks actually running DB2 on an IBM AS/400 machine? I didn't know those still exist, any reason why you chose this architecture in the first place? Or is that a legacy system? On Thu, Jul 6, 2017 at 1:37 AM, robertpic <[hidden email]> wrote: > @Jacques Le Roux > > DB2 data source at r1730616 > looks, fine > > there are some troubles with the native (AS/400) backup/restore tools > (savobj/rstobj) and the contraints. > --> actually we do an xml-export, 'll check this out later > > > status update > we will go online in about 2 weeks (B2B) ... and B2C in about 2 months > > <http://ofbiz.135035.n4.nabble.com/file/n4707470/Screenshot_Werkzeug.jpg> > > mobile > <http://ofbiz.135035.n4.nabble.com/file/n4707470/Screenshot_Mobile.jpg> > > > > -- > View this message in context: http://ofbiz.135035.n4.nabble.com/Using-OFBiz-with-DB2-for-i-AS400-database-tp4650633p4707470.html > Sent from the OFBiz - User mailing list archive at Nabble.com. |
|
|
>>Or is that a legacy system?
Yes. We use an self-made ERP on this system for ~ 350 Users. Most parts of the ERP working with "green screen/telnet". Why we chosse ofbiz? - it can handle all our needs (multishop, variants....) - very clean overrides for i.e. own prices --> override event, extend Event - well known technology (J2EE) why we use db2/as400? - for seamless integration with our greenscreen ERP (Cobol/RPG could read/update ofbiz data) - our db2 is clustered |
|
|
Update
We are running Ofbiz since 16 months (B2B) and now B2C without any troubles on DB2 (on AS/400 aka iSeries aka Power7/8/9). https://shop.odoerfer.com/ <https://shop.odoerfer.com/> We had some performance issues because of the big product tree (> 70.000 products). We used the ofbiz caching to hold the tree one day inside the cache/memory. -- Sent from: http://ofbiz.135035.n4.nabble.com/OFBiz-User-f135036.html |
Re: Using OFBiz with DB2 for i (AS400) database
|
|
Thanks for update Robert, this is great to see evidence that OFBiz scales
for large set of data. The performance issue you had, could you please share its details. As far as I know OFBiz can handle millions of products without any issue. Like what you have setup in the cache and how it helps you to resolve your performance problem. Also do you need any external tool to resolve it or OFBiz was capable to do that after setting the cache etc. May be you like to share your experience with community on how easy or difficult it was to overcome from the problem you were having. Best Regards, -- Rishi Solanki Sr Manager, Enterprise Software Development HotWax Systems Pvt. Ltd. Direct: +91-9893287847 http://www.hotwaxsystems.com www.hotwax.co On Fri, Nov 23, 2018 at 7:26 PM robertpic <[hidden email]> wrote: > Update > > We are running Ofbiz since 16 months (B2B) and now B2C without any troubles > on DB2 (on AS/400 aka iSeries aka Power7/8/9). > > https://shop.odoerfer.com/ <https://shop.odoerfer.com/> > > We had some performance issues because of the big product tree (> 70.000 > products). We used the ofbiz caching to hold the tree one day inside the > cache/memory. > > > > -- > Sent from: http://ofbiz.135035.n4.nabble.com/OFBiz-User-f135036.html > |
«
Return to OFBiz - User
|
1 view|%1 views
| Free forum by Nabble | Edit this page |