Mike Pavlak
Solutions Consultant
(815) 722 3454
Function Junction
MySQL for IBM i
Or
Agenda
• What is MySQL • Install • Access • GUI’s • DB2 Storage Engine • Q&AWhat is MySQL?
•
Most Popular and widely used Open Source Database
•
Relational Database management System (RDBMS)
•
Like DB2, but not, really
•
Command line interface
Who uses MySQL & why?
•
Millions of downloads
•
Big companies
•
Growing in IBM i
•
Nearly Everyone using open source PHP applications
•
Free
•
Open Source
How does it work?
•
Essentially flat files in the IFS
•
MySQL has two major parts:
•
UI Layer
•
Storage Engine
•
MyISAM
•
INNODB
In the beginning
Founded byDavid Axmark Allan Larsson
Michael “Monty” Widenius
Ownership
Remember MySQL is Open Source: Anyone can compile the source code and use the binaries as long as they follow the rules of the license.
Jan 2008:Sun purchased MySQL for $1B
Oracle purchased sun about year later for $7.4B FUD ensues…
What about IBM i?
Zend DBI == MySQL
A few years ago Oracle dropped support for MySQL on Power and discontinued compiling the binaries.
Old binaries still available on Oracle archive site IBM began looking for a new suitor
Zend stepped up and took over the binary distribution for
MySQL for IBM i and the new product is called Zend DBi
Same wonderful MySQL, just compiled for POWER IBM i
MySQL Options
• If you have never installed MySQL or Zend DBi
• Perform a clean installation of Zend DBi
• Otherwise, if you have MySQL & no real data
• Delete MySQL (Steps on next slide)
• If you have data and are at Version 5.1.x of MySQL
• Update to Zend DBI via the install process
• Lastly, if you have data at a version 5.0.x or lower
Details on deleting MySQL
• Steps for a clean removal…• ENDSBS ZMYSQL OPTION(*IMMED)
• DLTLIB ZMYSQL • CALL QP2TERM • rm -f /usr/local/mysql • rm -r -f /usr/local/mysql-5.1.50-i5os-power-64bit • rm -r -f /usr/local/mysqldata • rm -f /etc/my.cnf • rm -f /tmp/mysql.sock
Zend DBi Installation process
• Documented at Zend.com• Option to install or update within point release
• Can update 5.1.x to 5.1.y (Zend Server)
• Cannot update 5.0.x to 5.1.y (Zend Core)
• Download .zip from Zend.com, contains SAVF & README
• Follow readme.txt in SAVF, pretty straight forward
• Required LP’s are typically installed before Zend Server
Update anyone?
• Here we have 5.1.50 (install with ZS 5.1)
Start the installation process
• Have ROOT password before updating• Just stop ZMYSQL
• GO ZMYSQL/ZCMYSQL
Get SAVF to IBM i
• FTP and check it…Start the update process
• Steps to fire up process• RSTLIB SAVLIB(ZMYSQL)
• CHGCURLIB ZMYSQL
Set update options
• Have ROOT password before you get here
New subsystem and daemon
• ZENDDBIMySQL Monitor
• MySQL has no native GUI interface (sound familiar?)
• Command line accessible from QSHELL, QP2TERM, etc.
• This is the natural method for accessing.
• Will show you access, but not a big fan.
• Fully documented at MySQL website
Starting MySQL Monitor
• I typically use CALL QP2TERM, can use QSH, etc.
• The mysql> is the prompter for the monitor
• Mysql –u root
Difference between QP2TERM and Qshell
• QP2TERM is window to PASE• Cannot run IBM native programs like DB2, etc.
• Qshell is a Unix like shell
• Runs many things you find on Unix and IBM binaries
List all the databases
•
Show databases;
•
List all databases in MySQL
•
Database is like a library
•
Usually sorted by application
Don’t forget the semi-colon!
Set database
•
Use <DBNAME>
•
Selects the database you will use for processing
•
Database is like a library, where you store
tables, etc.
Display the user table
• Use SQL: select host, user, password from user;
Many GUI solutions
AdminerSingle PHP script
Lightweight, powerful and easy to use phpMyAdmin
Widely used by community Installed with Zend Server
phpMyAdmin
• Open Source PHP Project
• Provides nearly every access to MySQL you might need
• Installed automatically with Zend Server.
• Access from Zend Server Dashboard…
• Documentation at
phpMyAdmin
• Login with “root” profile
• Create your profile
• Test your profile
• Change root password
phpMyAdmin
• Login with “root” profile
• Create your profile
• Test your profile
• Change root password
Privileges
• Click Add new user
Look at databases & tables
• Left hand navigation• Right hand workspace
• FULL CRUD capabilities
IBM DB2 Storage
Engine
Why use MySQL to store in DB2?
Many PHP applications in the open source arena Can be easily installed
Modifying to access DB2 can be cumbersome & then updates? Zend DBi includes IBM DB2 Storage Engine
How to install
From the MySQL Monitor in QSH or QP2TERM Now go to phpMyAdmin and select the engine
IBM DB2 Storage Engine:phpMyAdmin
Create Table, set number of fieldsWhat happened on the i?
Library test was createdTable was created Records populated
Records, please?
STRSQLIBM Redbook and Redpiece
Events where you’ll find Zend…
COMMON Fall Conference (Columbus, OH) Sep 24-26
http://www.common.org/index.php/fall-conference-and-expo.html
Join us at ZendCon
The premier PHP conference!
October 22-25, 2012 – Santa Clara, CA
Conference Themes
PHP in 2012 - The latest PHP technologies and tools
Learn how to leverage the latest mobile, HTML 5, testing and PHP best practices
Zend Framework 2 - Hit the ground running
Learn how to build faster, more modular and more expandable applications
Conference Highlights
•Sessions focused on how to best develop and deploy PHP • Sessions designed for all knowledge levels
• Intensive tutorials for accelerated learning • PHP Certification crash courses and testing • Exhibit hall showcasing the latest products
Q&A
www.zend.com
