Monthly Archives: March 2017

Protected: IBM i PASE

This content is password protected. To view it please enter your password below:

Install Apigility on minimal CentOS. Connect Apigility on CentOS to IBM i DB2.

Step by step guide on how to install Apigility on latest CentOS with PHP 7, Redis, and MySQL.

  • Log to minimal CentOS server as root. This assumes that the server has all required network components installed and configured and has an access to the internet.
  • Install EPEL and its repositories.
$ yum install epel-release
$ rpm -Uvh http://rpms.famillecollet.com/enterprise/remi-release-7.rpm
$ rpm -Uvh https://mirror.webtatic.com/yum/el7/webtatic-release.rpm
$ rpm -Uvh http://repo.mysql.com/mysql-community-release-el7-5.noarch.rpm
  • Install PHP 7
$ yum install php70w
$ yum install php70w-mysql php70w-xml php70w-soap php70w-xmlrpc php70w-mbstring php70w-json php70w-gd php70w-mcrypt php70w-odbc.x86_64 php70w-pecl-redis
  • Install Apache 2.4
$ yum --enablerepo=epel,remi install httpd
$ systemctl start httpd
$ systemctl enable httpd
  • Install MySQL 5.6
$ yum install mysql-server
$ systemctl start mysqld
$ mysql_secure_installation
$ systemctl restart mysqld
$ systemctl enable mysqld
  • Check LAMP versions
$ cat /etc/centos-release
$ php –v
$ httpd –v
$ mysql –V

Command should return something like this:

CentOS Linux 7.2.1511 (Core)
PHP 7.02.12
Apache 2.4.6
MySQL 5.6.34
  • Install IBM i Access Driver

Upload package (ibm-iaccess-1.1.0.6-1.0.x86_64.rpm) to /opt/ibm directory

$ cd /opt/ibm
$ rpm -ivh ibm-iaccess-1.1.0.6-1.0.x86_64.rpm
  • Create ODBC.ini file
$ vi /etc/odbc.ini

Change sample values as needed:

[MYIBMISYSTEM]
Description             	= Production IBM i DB2 Database
Driver                  	= IBM i Access ODBC Driver 64-bit
System                  	= MYIBMI.MYDOMAIN.COM
UserID                  	= myUserID
Password                	= myUserPwd  
UNICODESQL              	= 1
DB2SQLSTATES			= 1
ALLOWPROCCALLS			= 1
Naming				= 0
DefaultLibraries		= IFNEEDED, *USRLIBL
Database			= MYDB2NAME
ConnectionType			= 0
CommitMode			= 2
ExtendedDynamic			= 1
DefaultPkgLibrary		= QGPL	
DefaultPackage			= A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression		= 1
LibraryView			= 0
AllowUnsupportedChar		= 0
ForceTranslation		= 0
Trace				= 0

We assume that you already have your networking configured. If not, here are some steps that can be used to do so:

  • Set Static IP Address

Locate the name of your adapter in /etc/sysconfig/network-scripts directory. It’s name will begin with if ‘cfg-eno’. Copy the full name and use it is following command:

$ vi /etc/sysconfig/network-scripts/ifcfg-enoXXXXXXX

Paste following text and change it as needed:

TYPE=Ethernet
BOOTPROTO="static"
IPADDR=10.10.10.126
NETMASK=255.255.254.0
GATEWAY=10.10.10.254
DNS1=10.10.10.1
DNS2=8.8.8.8
NM_CONTROLLED=no
DEFROUTE=yes
IPV4_FAILURE_FATAL=no
IPV6INIT=yes
IPV6_AUTOCONF=yes
IPV6_DEFROUTE=yes
IPV6_FAILURE_FATAL=no
NAME=ens33
DEVICE=ens33
ONBOOT=yes
PEERDNS=yes
PEERROUTES=yes
IPV6_PEERDNS=yes
IPV6_PEERROUTES=yes
  • Set Hostname
$ vi /etc/hostname

Add your hostname on first line (example MYCENTOS7). Update also the network configuration file with the proper value for the host name:

$ vi /etc/sysconfig/network

Set proper values:

NETWORKING=yes
HOSTNAME=MYCENTOS7
  • Add other hosts to Host Configuration File
$ vi /etc/hosts

Sample entires:

127.0.0.1       MYCENTOS7 localhost.localdomain localhost4 localhost4.localdomain4
::1             MYCENTOS7 localhost.localdomain localhost6 localhost6.localdomain6
10.10.10.55	MYIBMI.MYDOMAIN.COM	MYIBMI
  • Verify DB connection to IBM i server (via ODBC IBM i Access)
$ /opt/ibm/iSeriesAccess/bin64/cwbping MYIIBM

The result should look like this:

IBM i Access Client Solutions
Version 1  Release 1  Level 0
Connection Verification Program
(C) Copyright IBM Corporation and Others 1984, 2010.  All rights reserved.
U.S. Government Users Restricted Rights - Use, duplication or disclosure
  restricted by GSA ADP Schedule Contract with IBM Corp.
Licensed Materials - Property of IBM

To cancel the CWBPING request, press CTRL-C or CTRL-BREAK
I - Verifying connection to system MYIBMI...
I - Successfully connected to server application: Central Client
I - Successfully connected to server application: Network File
I - Successfully connected to server application: Network Print
I - Successfully connected to server application: Data Access
I - Successfully connected to server application: Data Queues
I - Successfully connected to server application: Remote Command
I - Successfully connected to server application: Security
I - Successfully connected to server application: DDM
I - Successfully connected to server application: Telnet
I - Connection verified to system MYIBMI
  • Verify ODBC IBM i Access Driver configuration
$ isql -v MYIBMISYSTEM

The result should look like this:

+---------------------------------------+
| Connected!                            |
|                                       |
| sql-statement                         |
| help [tablename]                      |
| quit                                  |
|                                       |
+---------------------------------------+
  • Install OpenSSL
$ yum install mod_ssl openssl
  • Create Virtual Host

Edit /etc/httpd/conf.d/ssl.conf file and create virtual hosts for secure communication:

$ vi /etc/httpd/conf.d/ssl.conf

Change values as needed:

Listen 443 https

SSLPassPhraseDialog exec:/usr/libexec/httpd-ssl-pass-dialog
SSLSessionCache         shmcb:/run/httpd/sslcache(512000)
SSLSessionCacheTimeout  300
SSLRandomSeed startup file:/dev/urandom  256
SSLRandomSeed connect builtin
SSLCryptoDevice builtin

##
## SSL Virtual Host Context
##

<VirtualHost _default_:443>
DocumentRoot "/var/www/html/API7/public"
ServerName MYCENTOS7

ErrorLog logs/ssl_error_log
TransferLog logs/ssl_access_log
LogLevel warn

SSLEngine on
SSLProtocol all -SSLv2
SSLCipherSuite HIGH:MEDIUM:!aNULL:!MD5:!SEED:!IDEA
SSLCertificateFile /etc/pki/tls/certs/ca.crt
SSLCertificateKeyFile /etc/pki/tls/private/ca.key


<Files ~ "\.(cgi|shtml|phtml|php3?)$">
    		SSLOptions +StdEnvVars
</Files>
<Directory "/var/www/cgi-bin">
    		SSLOptions +StdEnvVars
</Directory>

BrowserMatch "MSIE [2-5]" \
         nokeepalive ssl-unclean-shutdown \
         downgrade-1.0 force-response-1.0

CustomLog logs/ssl_request_log \
          "%t %h %{SSL_PROTOCOL}x %{SSL_CIPHER}x \"%r\" %b"


SetEnv APPLICATION_ENV "development"     
AllowEncodedSlashes On       
RewriteEngine on     
	<Location />         
		RewriteEngine On         
		RewriteCond %{REQUEST_FILENAME} -s [OR]         
		RewriteCond %{REQUEST_FILENAME} -l [OR]         
		RewriteCond %{REQUEST_FILENAME} -d          
		RewriteRule ^.*$ - [NC,L]         
		RewriteCond %{REQUEST_URI}::$1 ^(/.+)(.+)::\2$         
		RewriteRule ^(.*) - [E=BASE:%1]         
		RewriteRule ^(.*)$ %{ENV:BASE}index.php [NC,L]     
	</Location>      
	<Directory "/var/www/html/API7/public">         
		Options Indexes MultiViews FollowSymLinks         
		DirectoryIndex index.php         
		AllowOverride None         
		Require all granted   
	</Directory> 
</VirtualHost>
  • Open ports in Firewall
$ firewall-cmd --permanent --add-port=443/tcp
$ firewall-cmd --add-port=3306/tcp 
$ firewall-cmd --permanent --add-port=3306/tcp
$ firewall-cmd --add-port=6379/tcp 
$ firewall-cmd --permanent --add-port=6379/tcp

Reload the firewall:

$ firewall-cmd --reload

Once done, restart the Apache server:

$ systemctl restart httpd
  • Set SELinux Permissions for Ports
$ yum -y install policycoreutils-python
$ semanage port -a -t http_port_t -p tcp 443
$ semanage port -a -t http_port_t -p tcp 3306
$ semanage port -a -t http_port_t -p tcp 6379

and restart Apache:

$ systemctl restart httpd

We can also disable SELinux entirely:

$ sudo setenforce 0
$ systemctl restart httpd
  • Set Time Zone
$ timedatectl list-timezones | grep America
$ timedatectl set-timezone America/New_York

Verify configuration:

$ date
$ ls -l /etc/localtime
  • Configure NTP
$ vi /etc/chrony.conf

Add your NTP server to the top of the list:

# Use public servers from the pool.ntp.org project.
# Please consider joining the pool (http://www.pool.ntp.org/join.html).
server 10.10.10.66 prefer
server 0.centos.pool.ntp.org iburst
server 1.centos.pool.ntp.org iburst
server 2.centos.pool.ntp.org iburst
server 3.centos.pool.ntp.org iburst

Restart Chrony:

$ systemctl restart chronyd

Check the synchronization sources:

$ chronyc sources –v
$ chronyc sourcestats
  • Install Redis
$ yum install redis
$ redis-cli ping
$ systemctl enable redis.service
$ ss -nlp|grep redis

Configure Redis on Master server:

$ vi /etc/redis.conf

Replace content with following:

tcp-keepalive 60
#bind 127.0.0.1
requirepass your_redis_master_password
appendonly yes
appendfilename "appendonly.aof"

Configure Slave server; replace redis.conf with following:

#bind 127.0.0.1
requirepass your_redis_slave_password
slaveof MYCENTOS7 6379
masterauth your_redis_master_password
slave-read-only no

Verify Cluster Connectivity:

$ redis-cli -h 127.0.0.1 -p 6379

or

$ redis-cli -h masterIP -p 6379
$ AUTH your_redis_master_password
$ INFO
  • Install Apigility

Install the latest distribution package from Zend in /var/www/html/API7.

$ mkdir /var/www/html/API7
$ cd /var/www/html/API7
$ curl -sS https://apigility.org/install | php
  • Set Files and Directories Permissions

This assumes that your Apigility installation is placed in /var/www/html/API7 directory.

$ chown apache:apache -R /var/www/html/
$ cd /var/www/html/
$ find . -type f -exec chmod 0644 {} \;
$ find . -type d -exec chmod 0755 {} \;
$ chcon -t httpd_sys_content_t /var/www/html/API7 –R
$ chcon -t httpd_sys_rw_content_t /var/www/html/API7/config –R
$ chcon -t httpd_sys_rw_content_t /var/www/html/API7/module –R
$ chcon -t httpd_sys_rw_content_t /var/www/html/API7/vendor –R
$ chcon -t httpd_sys_rw_content_t /var/www/html/API7/data –R
  • Apache Performance

Find out what MPM is in use:

$ apachectl –M | grep mpm

or

$ httpd –V | grep MPM

Decide which MPM will be used and uncomment it in /etc/httpd/conf.modules.d/00-mpm.conf:

$ vi /etc/httpd/conf.modules.d/00-mpm.conf
# Select the MPM module which should be used by uncommenting exactly
# one of the following LoadModule lines:

# prefork MPM: Implements a non-threaded, pre-forking web server
# See: http://httpd.apache.org/docs/2.4/mod/prefork.html
#LoadModule mpm_prefork_module modules/mod_mpm_prefork.so

# worker MPM: Multi-Processing Module implementing a hybrid
# multi-threaded multi-process web server
# See: http://httpd.apache.org/docs/2.4/mod/worker.html
#
LoadModule mpm_worker_module modules/mod_mpm_worker.so

# event MPM: A variant of the worker MPM with the goal of consuming
# threads only for connections with active processing
# See: http://httpd.apache.org/docs/2.4/mod/event.html
#
#LoadModule mpm_event_module modules/mod_mpm_event.so

Create file /etc/httpd/conf.modules.d/10-worker.conf with flowing content:

<IfModule mpm_worker_module>
	ServerLimit             30
        StartServers            8
        MaxRequestWorkers       2048
        MinSpareThreads         256
        MaxSpareThreads         2048
        ThreadsPerChild         256
        ThreadLimit             256
        MaxConnectionsPerChild  0 
</IfModule>
  • Troubleshooting:

UnixODBC error 08S01:

$ setsebool -P http_can_network_connect=1

 

  • Update:

We recommend to upgrade PHP, OpenSSL, and Apache.

yum install http://rpms.remirepo.net/enterprise/remi-release-7.rpm
rpm -Uvh https://dl.fedoraproject.org/pub/epel/epel-release-latest-7.noarch.rpm
rpm -Uvh  https://mirror.webtatic.com/yum/el7/webtatic-release.rpm
yum install yum-utils
yum-config-manager --enable remi-php72
yum remove php*
yum install php
yum install php-mysql php-odbc php-xml php-soap php-xmlrpc php-mbstring php-mcrypt php-pecl-zip php-pecl-redis
yum install php-pdo php-odbc php-mysql
yum upgrade openssl
yum upgrade httpd

# get location of php.ini first from phpinfo()
vi /etc/opt/remi/php72/php.ini timezone= America/New_York
service httpd restart

PATH=$PATH:/opt/remi/php72/root/usr/bin/

export PATH
php -v
  • References:

How To Configure a Redis Cluster on CentOS 7

No Comments Apigility, CentOS, Zend

Connecting to IBM i DB2 from Linux Using 64-bit IBM i Access ODBC Driver

This comprehensive guide covers multiple methods to connect to IBM i DB2 from Linux, including ODBC, JDBC, Python, Node.js, PHP, and direct SQL session connections.

Prerequisites

  • 64-bit Linux OS (x86_64)
  • Network access to IBM i system
  • Valid user credentials
  • Root/sudo access

Method 1: 64-bit ODBC Connection

Installation & Configuration

# Debian/Ubuntu
sudo apt-get update
sudo apt-get install -y unixodbc unixodbc-dev

# RHEL/CentOS
sudo yum install -y unixODBC unixODBC-devel

# Install 64-bit driver
sudo dpkg -i ibm-iaccess-*_x86_64.deb  # Debian/Ubuntu
sudo rpm -ivh ibm-iaccess-*.x86_64.rpm  # RHEL/CentOS

ODBC Configuration (/etc/odbc.ini)

[IBMI64]
Description = IBM i 64-bit DB2 Connection
Driver = IBM i Access ODBC Driver 64-bit
System = your.ibmi.server
UserID = youruser
Password = yourpassword
Database = *SYSBAS
Naming = 1
CommitMode = 2

Connection Examples

1. Python (pyodbc)

import pyodbc
conn = pyodbc.connect(
    "DRIVER={IBM i Access ODBC Driver 64-bit};"
    "SYSTEM=your.ibmi.server;"
    "UID=youruser;"
    "PWD=yourpassword;"
    "DATABASE=QGPL;"
)
cursor = conn.cursor()
cursor.execute("SELECT * FROM QIWS.QCUSTCDT")
print(cursor.fetchall())

2. Node.js (node-odbc)

const odbc = require('odbc');
const connection = await odbc.connect(
  "DRIVER={IBM i Access ODBC Driver 64-bit};" +
  "SYSTEM=your.ibmi.server;" +
  "UID=youruser;" +
  "PWD=yourpassword;" +
  "DATABASE=QGPL;"
);
const result = await connection.query("SELECT * FROM QIWS.QCUSTCDT");
console.log(result);

3. PHP (unixODBC)

<?php
$conn = odbc_connect("IBMI64", "youruser", "yourpassword");
$sql = "SELECT * FROM QIWS.QCUSTCDT";
$result = odbc_exec($conn, $sql);
while ($row = odbc_fetch_array($result)) {
    print_r($row);
}
odbc_close($conn);
?>

4. Direct SQL Session (Command Line)

# Start interactive SQL session
/opt/ibm/iaccess/bin64/db2cli execsql -connstring "DATABASE=*SYSBAS;HOSTNAME=your.ibmi.server;UID=youruser;PWD=yourpassword"

# Execute single command
/opt/ibm/iaccess/bin64/db2cli execsql -connstring "DATABASE=*SYSBAS;HOSTNAME=your.ibmi.server;UID=youruser;PWD=yourpassword" -sql "SELECT * FROM QIWS.QCUSTCDT"

JDBC Connection (Java)

// Using JTOpen (IBM Toolbox for Java)
String url = "jdbc:as400://your.ibmi.server;libraries=QGPL";
Connection conn = DriverManager.getConnection(url, "youruser", "yourpassword");
Statement stmt = conn.createStatement();
ResultSet rs = stmt.executeQuery("SELECT * FROM QIWS.QCUSTCDT");

Troubleshooting

IssueSolution
Driver not foundVerify path: /opt/ibm/iaccess/lib64
Connection timeoutCheck firewall and port 8471
Authentication failureVerify user profile on IBM i

Best Practices

  • Use TLS encryption for production
  • Implement connection pooling
  • Follow principle of least privilege
  • Consider using SSH tunneling for remote connections

This guide provides multiple approaches to connect to IBM i DB2 from Linux, allowing you to choose the best method for your specific application needs.

No Comments Database Connections, IBM i, Linux