ibmi-brunch-learn

Announcement

Collapse
No announcement yet.

Synchronise DB2 With MySQL Database

Collapse
This topic is closed.
X
X
 
  • Filter
  • Time
  • Show
Clear All
new posts

  • Synchronise DB2 With MySQL Database

    Something that I thought would be fairly easy to accomplish has taken me a week and I just keep hitting roadblocks every step of the way. I am hoping that someone has been able to achieve this and can maybe offer some tips.

    The scenario is that our corporate web site (PHP using the DB2 database on our iSeries) is running really slowly, plus with recent power and internet problems, it has been decided to move it to a hosted site, which requires us to convert everything to MySQL (preferred database).

    Since the data source will remain on the iSeries, so we need to be able to synchronise the DB2 content onto a MySQL database.

    I looked at various solutions, which sounded easy, but proved to be far from it.

    DBConvert.com has a cheap solution (with a GUI interface) that will synchronise a DB2 database with a MySQL one. I installed the software on a Windows 2008 Server, then tried to connect to the iSeries, and it requires "IBM Data Server Client" to be installed on the server. This unfortunately seems to require a paid license from IBM, and with the company paying out fee after fee for software and licenses, I am sure there must be a free way to be able to do this.

    So I am stuck on the DBConvert solution, but I wondered if there was a way to synchronise DB2 with MySQL on the iSeries, and then I could synchronise that MySQL to the Windows Server, making this a 2-stage process, but at the same time giving us a backup of the MySQL database on the iSeries, which might come in handy later if we incorporate ordering facilities on the web site.

    So "finally" to my question, which is: Has anyone managed to successfully synchronise DB2 on the iSeries with a MySQL database, and if so what solutions would you recommend?

    I know I could write a solution in RPG, but it would be slow and tedious, and frankly why re-invent the wheel...

    Thanks in advance for any help.
    Poddys Rambles On

  • #2
    Re: Synchronise DB2 With MySQL Database

    Why do you want to sync the DB? If the data is managed on the IBM i and the link fails (which appears to be the reason you need to move to MySQL??) the data the users see will be old data anyhow? I have a Linux Server which runs the web services and uses i5_toolkit to talk to the IBM i via the Easycom server, its much faster than using the IBM i server directly. I keep my data on the IBM i and can replicate to the HA server, if the prod server fails, I ca still get the data from the HA server. Would that not be a better option?

    Chris...

    Comment


    • #3
      Re: Synchronise DB2 With MySQL Database

      In recent months we have had (a) power problems in the local area/building, (b) internet problems, and we don't have a backup iSeries so like today all the web sites will be down for most of the day, which is unacceptable (IMHO).

      For this reason we are looking to replicate the main files used on the web site and to run everything on a server operated by a hosting company, so "in theory" guaranteeing 100% up time.

      It seems easier to use MySQL rather than DB2, but regardless we do want to replicate the data onto the hosting server.

      As regards data freshness, the files are only updated on the iSeries twice daily. It's product information (prices/descriptions) and we don't operate a shopping cart (currently). So a lag in updates of even hours is acceptable.

      The problem is just trying to get the data synchronized.

      We do use the i5_toolkit but I am not very familiar with all the functionality. I come from the "old school" Midrange use, but will read up more to see if I can make sense of it.

      Appreciate the feedback.
      Poddys Rambles On

      Comment


      • #4
        Re: Synchronise DB2 With MySQL Database

        Here is an alternative as you already have the toolkit.

        Create a PHP CLI that can be scheduled in the CRON scheduler to retrieve the records using the DB2 connection, then create a MySQL insert for each record read from the DB. You would have to clear the records each time but it will work in much the same way you would do a SQL_DUMP on MySQL. Or you can use the SQL JOURNAL_TABLE() request to read updates from the journal and use the same kind of method to update / insert the data read from the journal and apply to the file. Personally and depending on the size of the files which need to be managed I would think the clear and insert method will be the simplest. However if you want to take the bigger leap and use the SQL JOURNAL_TABLE() request here is a link that may put you on the right path. I use this method for displaying data on a remote system that has been applied by a job, it can be quite complex but it works and its definitely fast. There are other posts about the same process in my blog so just search for them.



        Dawn May described the functionality in her iCan blog, but I cannot find the posts anymore. Hope that helps.

        Chris...

        Comment


        • #5
          Re: Synchronise DB2 With MySQL Database

          Thanks Chris. I did think of writing a PHP or even RPG program to update the data from DB2 to MySQL. It's just a lot of coding. I would prefer to use journalling or even triggers (which I haven't used before) to update the MySQL database so that only affected rows are updated. I did think to replicate the DB2 data into MySQL on the iSeries and then using synch software to keep it up to date on the server where the web site is hosted. Bit long winded but it might be simpler in a way.
          Poddys Rambles On

          Comment


          • #6
            Re: Synchronise DB2 With MySQL Database

            I don't think it will be too much code to do the fetch and store, the journalling may be a bit more effort just because you need to store the last applied entry somewhere etc. Plus the data that is returned is a simple character array so you will need to format that as part of the apply.

            Still it gives you a few more options to consider, I personally would rather keep the DB2 access and not try doing copies to MySQL, even if that means having another copy of the DB located on a separate system. Its not too expensive these days either with the cost of HA replication tools and hosting on a MSP system so maybe it will kill two birds with one stone (better uptime plus access to the webserver 24x7??).

            Chris...

            Comment


            • #7
              Re: Synchronise DB2 With MySQL Database

              A quick update...

              I managed to create an sql script that deleted all the data in the MySQL tables and insterted the DB2 data (I built the tables manually first), but the full script would not run because it was too large (or that is what I think the error meant, the script was 22mb).

              So I changed things to read/insert each row, which I know is slower, but that at least worked ok. I didn't have unlimited time to fix the script size problem.

              However, in doing this, when I come to triggering the DB2 files and just processing the changes to them, this approach will I think work out better.

              Thanks for the help...
              Poddys Rambles On

              Comment


              • #8
                Re: Synchronise DB2 With MySQL Database

                Could be a problem with the maximum upload size in the php.ini. I think the default is 20MB. Just change that and you would be OK.

                Chris...

                Comment


                • #9
                  Re: Synchronise DB2 With MySQL Database

                  Just a quick update on the status of this, in case it's of use to anyone.

                  I ended up with a single PHP script that reads rows from each DB2 table and writes them out to it's MySQL equivalent.

                  This is a bit slow I know, but it works, and the beauty is that I have a single function to process a table, which uses db2_fetch_array() so that I don't need to reference any column names in any table.

                  The main section of the PHP script calls this function for each table that is to be copied to MySQL. Simples...

                  I can provide a copy of the script if it's of use to anyone.
                  Poddys Rambles On

                  Comment

                  Working...
                  X