• The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
Menu
  • The Four Hundred
  • Subscribe
  • Media Kit
  • Contributors
  • About Us
  • Contact
  • FAQs for MySQL and the DB2 Storage Engine

    April 21, 2010 Susan Gantner

    In a couple of past tips, I’ve been exploring aspects about using MySQL on i and especially the ability to use the DB2 for i Storage Engine to make the MySQL data accessible from “native” DB2-based applications and tools. In this tip, I’ll cover a couple of questions that often arise related to this topic.

    How does database object security work with MySQL and the DB2 for i storage engine?

    MySQL has its own security implementation that governs how users may access data. Users and their authorities to databases and tables are set up in MySQL and are unrelated to IBM i system user profiles and authorities. When the DB2 for i storage engine is used, the same data is accessible from DB2 as well as MySQL. So when accessing the data via DB2, the normal IBM i system security model is used to govern access to the data.

    What this means ultimately is that in order for the same person to be able to access the data using both MySQL and DB2 (by way of RPG programs or native query tools, for example) that person will need to be authorized in two different ways. They need an IBM i user profile with appropriate authority to access the DB2 data, and they need to be set up as a MySQL user with the necessary authority to access the MySQL table.

    This duplication of user and authorization management can be a bit cumbersome. If you often have the same users who need access the same data from both interfaces, it may be worth writing your own interface to automate the process of setting up the authority in both environments. The user and authority information to the MySQL data is stored in database tables within MySQL, so one possible approach might be to have a trigger program react to changes to the security related tables in MySQL and apply those same changes to the native objects. I’m very hopeful that someone will create a nice set of tools to simplify authority management in the MySQL DB2 storage engine environment.

    By the way, you may be wondering if there is a similar user to QSECOFR in MySQL. The answer is yes–its name is root. By default when first installed, root has no password associated with it. Therefore, a critical step in the MySQL installation and setup process should always include assigning a password to the root user. Since MySQL users are qualified by the system name and since on IBM i, the system name and the special name ‘localhost” are usually synonymous, assigning the password to root may involve 2 MySQL statements, similar to the ones shown below.

    SET PASSWORD FOR 'root'@'localhost' = PASSWORD('newpwd');
    SET PASSWORD FOR 'root'@'YourHostName' = PASSWORD('newpwd');
    

    How do I access my existing DB2 tables/files via MySQL?

    The short answer is “you don’t.” The storage engine currently only works in one direction; i.e., the data for tables created via MySQL may be stored in DB2 for i. There is no support via the storage engine to allow MySQL to access pre-existing DB2 data.

    The slightly longer answer is “with a bit of effort.” You could re-create the table via MySQL using the storage engine and then copy the data (using CPYF, SQL Insert, or any number of other simple options) from the original DB2 table/file into the new object created by the storage engine. Sounds simple enough, right?

    Yes, but. . . what about level checks for all the programs already using the original table/file? What about all the logical files built on that table? They can be recreated, most likely via combinations of Create Index and/or Create View, but that takes time and planning, especially if they were all created using DDS originally. Now that SQL Views can have keys on them in the more recent releases of IBM i, at least most of the logical file features can be re-created using SQL. What about the fact that the new table in DB2 will have a record format name the same as the table? There are also currently some differences in data type support. The one most likely to be an issue in this scenario is a lack of zoned numeric data type with the storage engine.

    A Few Rough Edges

    The storage engine certainly makes it simpler to share data between applications using MySQL and applications using DB2 for i. It’s very powerful support and I plan to make use of it in many ways in my future applications. But it’s not a panacea. There are a few rough spots. I hope that over time the storage engine support from IBM may improve to help smooth some of these rough spots. Still others may be smoothed via tool vendors that make the scenario easier to manage and to exploit. I predict a bright future of more flexible application interfaces to IBM i data using a combination of MySQL and DB2.

    Susan Gantner is one of the most respected System i gurus in the world and is one of the co-founders of System i Developer, an organization dedicated to RPG, DB2, and other relevant software technologies for the IBM i platform that hosts the RPG & DB2 Summit conferences. Gantner, who has worked in IBM’s Rochester and Toronto labs, left IBM to focus on training development shops on the latest programming technologies. She is also a regular speaker at user groups and teaches customized on-site classes for i shops. Send your questions or comments for Susan to Ted Holt via the IT Jungle Contact page.

    RELATED STORIES

    Naming Idiosyncrasies with the DB2 Storage Engine for MySQL

    What a Lifelong DB2 Fanatic Sees in MySQL



                         Post this story to del.icio.us
                   Post this story to Digg
        Post this story to Slashdot

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Tags:

    Sponsored by
    Raz-Lee Security

    With COVID-19 wreaking havoc, cybercriminals are taking advantage of the global impact that it has had on our families, our businesses and our societies. It is more important now than ever to ensure that IT systems are protected, so that when all of this is behind us, we can get back to business as usual as quickly as possible.

    iSecurity Anti-Ransomware protects organizations against ransomware attacks and other kinds of malware that may access and change business-critical data on your IBM i. It even protects against zero-day attacks. Anti-Viruses can only report on the damage an attack has caused, but not stop it.

    iSecurity Anti-Ransomware has been recently enhanced with a Self-Test feature that allows you to simulate a ransomware attack on your IBM i. The simulated attack is limited to the test folder and cannot harm any other folders or files. This new feature lets organizations see how they are protected against known or unknown ransomware.

    Key Features:

    • Real-time scanning for known and unknown ransomware threats.
    • Blocks and disconnects the intruder.
    • Instantaneously sends alerts to SIEM as well as the offending computer.
    • Self-Test for attack simulation
    • Classification of the attack based on log.
    • Automatic updates with the most current ransomware definitions.

    Contact us at https://www.razlee.com/anti-ransomware

    Share this:

    • Reddit
    • Facebook
    • LinkedIn
    • Twitter
    • Email

    Sponsored Links

    PowerTech:  Download your free copy of the updated 2010 The State of IBM i Security today!
    Shield Advanced Solutions:  JobQGenie ~ the perfect companion for your high availability solution
    COMMON:  Join us at the annual 2010 conference, May 3 - 6, in Orlando, Florida

    IT Jungle Store Top Book Picks

    Easy Steps to Internet Programming for AS/400, iSeries, and System i: List Price, $49.95
    The iSeries Express Web Implementer's Guide: List Price, $49.95
    The System i RPG & RPG IV Tutorial and Lab Exercises: List Price, $59.95
    The System i Pocket RPG & RPG IV Guide: List Price, $69.95
    The iSeries Pocket Database Guide: List Price, $59.00
    The iSeries Pocket SQL Guide: List Price, $59.00
    The iSeries Pocket Query Guide: List Price, $49.00
    The iSeries Pocket WebFacing Primer: List Price, $39.00
    Migrating to WebSphere Express for iSeries: List Price, $49.00
    Getting Started With WebSphere Development Studio Client for iSeries: List Price, $89.00
    Getting Started with WebSphere Express for iSeries: List Price, $49.00
    Can the AS/400 Survive IBM?: List Price, $49.00
    Chip Wars: List Price, $29.95

    Talend Updates Data Management Software Stacking Up New Power7 Against Power6/6+ Blades

    Leave a Reply Cancel reply

Volume 10, Number 14 -- April 21, 2010
THIS ISSUE SPONSORED BY:

Infor
Vision Solutions
WorksRight Software

Table of Contents

  • Files in Subprocedures
  • Let One Row Represent a Group
  • Admin Alert: Diary of a Production System Upgrade, Part 1
  • FAQs for MySQL and the DB2 Storage Engine
  • Yet Another Super-Easy Way to Copy a Spooled File from One Partition to Another
  • More on Whether IPLs Help System Performance

Content archive

  • The Four Hundred
  • Four Hundred Stuff
  • Four Hundred Guru

Recent Posts

  • IBM Mulls Using DataMigrator as Cloud Warehouse Pipeline
  • PowerTech AV Automatically Detects Ransomware Activity
  • Infor Puts CM3 Project On Hold
  • Four Hundred Monitor, June 29
  • IBM i PTF Guide, Volume 24, Number 26
  • Guild Mortgage Takes The 20-Year Option For Modernization
  • IBM i Licensing, Part 3: Can The Hardware Bundle Be Cheaper Than A Smartphone?
  • Guru: The Finer Points of Exit Points
  • Big Blue Tweaks IBM i Pricing Ahead Of Subscription Model
  • We Still Want IBM i On The Impending Power E1050

Subscribe

To get news from IT Jungle sent to your inbox every week, subscribe to our newsletter.

Pages

  • About Us
  • Contact
  • Contributors
  • Four Hundred Monitor
  • IBM i PTF Guide
  • Media Kit
  • Subscribe

Search

Copyright © 2022 IT Jungle

loading Cancel
Post was not sent - check your email addresses!
Email check failed, please try again
Sorry, your blog cannot share posts by email.