ODBC from Linux to iseries (AS400) for php

Using a linux (Ubuntu) box to connect to a IBM iSeries (AS400) to use php queries and reporting

IBM ODBC driver (login required) http://www-03.ibm.com/systems/power/software/i/access/linux/guide.html

Install apache with php and odbc modules.

sudo apt-get install libapache2-mod-php5 apache2 php5-odbc

Install unixodbc

sudo apt-get install unixodbc

Copy the downloaded iseries odbc driver to the server and install

sudo dpkg -i ibm-iaccess-1.1.0.2-1.0.amd64.deb

symbolic link the libraries to the /usr/lib folder for use for the system

sudo ln -s /opt/ibm/iSeriesAccess/lib64/libcwb* /usr/lib

We can now create the odbc setup using the driver.
Find where the SYSTEM odbcinst.ini and odbc.ini files are:

odbcinst -j

unixODBC 2.2.14
DRIVERS............: /etc/odbcinst.ini
SYSTEM DATA SOURCES: /etc/odbc.ini
FILE DATA SOURCES..: /etc/ODBCDataSources

Edit the files, the odbcinst.ini file should have the IBM iseries driver setup already within it as part of the driver install, but the default file is missing the header [ODBC Drivers] which I found caused problems, so can be added to the top.

/etc/odbcinst.ini

[ODBC Drivers]
IBM i Access ODBC Driver
Description = IBM i Access for Linux ODBC Driver
Driver = /opt/ibm/iSeriesAccess/lib/libcwbodbc.so
Setup = /opt/ibm/iSeriesAccess/lib/libcwbodbcs.so
Driver64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup64 = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
Threading = 0
DontDLClose = 1
UsageCount = 2

[IBM i Access ODBC Driver 64-bit]
Description = IBM i Access for Linux 64-bit ODBC Driver
Driver = /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
Threading = 0
DontDLClose = 1
UsageCount = 2

Now create the odbc.ini for your specific system, its important to create a [ODBC Data Sources] and the [DSN]

Example odbc.ini

[ODBC Data Sources]
DEV = DEV

[DEV]
Description = iSeries Access ODBC Driver
Driver = IBM i Access ODBC Driver
System = FQDN or IP
UserID = USER
Password = PASSWORD
Naming = 1
DefaultLibraries = QGPL
Database =
ConnectionType = 2
CommitMode = 2
ExtendedDynamic = 1
DefaultPkgLibrary =
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 1
LibraryView = 0
AllowUnsupportedChar = 1
ForceTranslation = 1
Trace = 0

The bracketed name in the odbc is the DSN, in this case [DEV]
Driver in odbc.in needs to match the name specified in odbcinst.ini
System is the network name in fqdn or IP.
UserID and Password are as required to login to the iseries.
DefaultLibraries, Database, and DefaultPkgLibrary can be specified as required or left blank and specified higher in the php, I only use the DefaultLibraries.

Test the connection using the commandline isql and the DSN.

isql -v DEV
[unixODBC][Driver Manager]Can't open lib '/opt/ibm/iSeriesAccess/lib64/libcwbodbc.so' : file not found

Oooh ERROR, thats not good… but I have found a fix !

There is a discrepancy between the iseries driver and the unixodbc libraries for the libodbcinst which can result in the above meaningless error when using the driver.

The file is within the location but there is a problem with the actual library and the error message is not very clear.
To see the true error we need to use ldd to see the linked dependancies of libcwdodbc.so

ldd /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
linux-vdso.so.1 => (0x00007fff86dfe000)
libodbcinst.so.2 => not found
libcwbcore.so => /usr/lib/x86_64-linux-gnu/libcwbcore.so (0x00007f7f68545000)
libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007f7f68240000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007f7f67f3a000)
libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007f7f67d24000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007f7f6795d000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007f7f6773f000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007f7f6753b000)
librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007f7f67332000)
/lib64/ld-linux-x86-64.so.2 (0x00007f7f68b98000)

Until there is a fix packaged version of unixodbc within the ubuntu packages (current version 2.2.14p2-5ubuntu5) then we can symbolic link so.1 to so.2

sudo ln -s /usr/lib/x86_64-linux-gnu/libodbcinst.so.1 /usr/lib/x86_64-linux-gnu/libodbcinst.so.2

IBM article regarding this: http://www.ibm.com/developerworks/ibmi/library/i-ibmi-access-client-solutions-linux/

Running ldd now sees the required libraries.

ldd /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
linux-vdso.so.1 => (0x00007fff315fe000)
libodbcinst.so.2 => /usr/lib/x86_64-linux-gnu/libodbcinst.so.2 (0x00007fcef32ed000)
libcwbcore.so => /usr/lib/x86_64-linux-gnu/libcwbcore.so (0x00007fcef2f7a000)
libstdc++.so.6 => /usr/lib/x86_64-linux-gnu/libstdc++.so.6 (0x00007fcef2c75000)
libm.so.6 => /lib/x86_64-linux-gnu/libm.so.6 (0x00007fcef296f000)
libgcc_s.so.1 => /lib/x86_64-linux-gnu/libgcc_s.so.1 (0x00007fcef2759000)
libc.so.6 => /lib/x86_64-linux-gnu/libc.so.6 (0x00007fcef2392000)
libltdl.so.7 => /usr/lib/x86_64-linux-gnu/libltdl.so.7 (0x00007fcef2188000)
libpthread.so.0 => /lib/x86_64-linux-gnu/libpthread.so.0 (0x00007fcef1f6a000)
libdl.so.2 => /lib/x86_64-linux-gnu/libdl.so.2 (0x00007fcef1d65000)
librt.so.1 => /lib/x86_64-linux-gnu/librt.so.1 (0x00007fcef1b5d000)
/lib64/ld-linux-x86-64.so.2 (0x00007fcef37df000)

Now we can rerun isql and test.

isql -v DEV
+---------------------------------------+
| Connected!                           |
|                                      |
| sql-statement                        |
| help [tablename]                     |
| quit                                 |
|                                      |
+---------------------------------------+
SQL

Woot, connected!

Ok now you have a working ODBC connection to your iseries, so you can use it via the DSN and your applications.

The next stage was to use php to link to this odbc.

Php page connecting to DSN [DEV] using a select statement and putting the results into a table:

<!DOCTYPE html>
<html>
<head>
<style>
table, th, td {
border: 1px solid black;
border-collapse: collapse;
}
</style>
</head>
<body>

<?php
try{
$as400conn = new PDO(‘odbc:DEV’); // Note: The name is the same as what’s in our square brackets in ODBC.ini
$as400conn->setAttribute(PDO::ATTR_ERRMODE, PDO::ERRMODE_EXCEPTION);
$library = “as400 library”;
$file = “as400 file”;
$i = 0;
$fields[$i++] = “file field 1”;
$fields[$i++] = “file field 2”;
$fields[$i++] = “file field 3”;
$AryLength = count($fields);

// Create SQL Have to include first field for comma separate outside of the while loop. field,field
$sql = “SELECT ” . $fields[0] ;
for($x = 1; $x < $AryLength; $x++) {
$sql = $sql . “,” . $fields[$x] ;
}
$sql = $sql.” FROM ” . $library . “/” . $file ;
echo $sql;
echo “<br>”;
$result = $as400conn->query($sql);

// Print Table Header //
echo “<table><tr>”;
for($x = 0; $x < $AryLength; $x++) {
echo “<th> $fields[$x] </th>”;
}
echo “</tr>”;

// Output Data of each row
while($row = $result->fetch(PDO::FETCH_ASSOC)) {
echo “<tr> “;
for($x = 0; $x < $AryLength; $x++) {
echo “<td>” . $row[$fields[$x]] . “</td>”;
}
echo “</tr>”;
}
echo “</table>”;
$as400conn = null;

//end of try
}
catch (PDOException $e) {
echo $e->getMessage();
}
?>
</body>
</html>

Install Openmeetings Ubuntu 14.04

Install Ubuntu server 14.04 and update

Add Oracle Java.

sudo add-apt-repository ppa:webupd8team/java
sudo apt-get update
sudo apt-get install oracle-java8-installer

Accept license for Oracle binaries

Install all dependancies available from the repository

sudo apt-get install unzip imagemagick ghostscript jodconverter libjpeg-dev libgif-dev libfreetype6-dev libfreetype6-dev libreoffice sox build-essential git-core checkinstall yasm texi2html libvorbis-dev libx11-dev libvpx-dev libxfixes-dev zlib1g-dev pkg-config netcat libmp3lame-dev libx264-dev

The swftools version for Ubuntu 14.04 doesnt include pdf2swf https://launchpad.net/ubuntu/trusty/+package/swftools so has to be compiled.

cd /opt/
sudo mkdir swftools;cd swftools
sudo wget http://www.swftools.org/swftools-0.9.2.tar.gz
sudo tar zvxf swftools-0.9.2.tar.gz
cd swftools-0.9.2
sudo ./configure
sudo make

swftools 0.9.2 has an error on a makefile which includes an option for rm.
https://lists.nongnu.org/archive/html/swftools-common/2013-02/msg00009.html

edit the makefile in /opt/swftools/swftools-0.9.2/swfs
change:

rm -f $(pkgdatadir)/swfs/default_viewer.swf -o -L $(pkgdatadir)/swfs/default_viewer.swf
rm -f $(pkgdatadir)/swfs/default_loader.swf -o -L $(pkgdatadir)/swfs/default_loader.swf

to

rm -f $(pkgdatadir)/swfs/default_viewer.swf
rm -f $(pkgdatadir)/swfs/default_loader.swf

Then install

sudo make install

Compile and make ffmpeg with the correct flags for mp3lame etc.

Create install-ffmpeg.sh and add following

note the configure options should be double dash –

FFMPEG_VERSION=2.4.2
cd /usr/local/src
if [ ! -d /usr/local/src/ffmpeg-$FFMPEG_VERSION ]; then
sudo wget http://ffmpeg.org/releases/ffmpeg-$FFMPEG_VERSION.tar.bz2
sudo tar -xjf ffmpeg-$FFMPEG_VERSION.tar.bz2
fi
cd ffmpeg-$FFMPEG_VERSION
sudo ./configure –enable-gpl –enable-version3 –enable-postproc –enable-libvorbis –enable-libvpx –enable-libmp3lame –enable-libx264 –enable-nonfree
sudo make
sudo checkinstall

make the script executable and run the installer.

chmod +x install-ffmpeg.sh
./install-ffmpeg.sh

Confirm all components are working,

pdf2swf = it should output the switch options
sox = it should output the switch options
identify = (ImageMagick) it should output the switch options
ghostscript = it should drop to the GS (ghostscript) command prompt, quit to exit.
jodconverter = it should output the switch options
libreoffice –version = libreoffice version confirm
ffmpeg = will output the compiled options

Download and run the openmeetings installer

cd /opt/
sudo mkdir openmeetings; cd openmeetings
sudo wget http://mirror.ox.ac.uk/sites/rsync.apache.org/openmeetings/3.0.4/bin/apache-openmeetings-3.0.4.zip
sudo unzip apache-openmeetings-3.0.4.zip
sudo ./red5.sh

Open the web installer

http://SERVERIP:5080/openmeetings/install

Set initial configuration

Userdata/Organisation(Domain)
Username = Administrator name
Userpass = Administrator password
Email = Administrator email
User Time Zone = Time zone for OpenMeetings
Organisation(Domains) = Domain name

Configuration

Allow self-registering (allow_frontend_register) = No
Send Email to new registered Users (sendEmailAtRegister) = Yes
New Users need to verify their EMail (sendEmailWithVerficationCode) = No
Default Rooms of all types will be created = Yes
Mail-Referer (system_email_addr) = Admin email or noreply
SMTP-Server (smtp_server) = SMTP server
SMTP-Server Port(default Smtp-Server Port is 25) (smtp_port) = SMTP Port
SMTP-Username (email_username) = SMTP user
SMTP-Userpass (email_userpass) = SMTP pass
Enable TLS in Mail Server Auth = Enable TLS for SMTP
Set inviter’s email address as ReplyTo in email invitations (inviter.email.as.replyto) = Reply to inviter
Default Language = Language for Openmeetings
Default Font for Export [default_export_font] = Font used

Converters

swftools zoom 100
swftools jpeg quality 85
all other paths for componets can be left blank or /opt/jod/lib for JOD if all above worked, otherwise enter paths
SWFTools Path
ImageMagick Path
FFMPEG Path
SoX Path
JOD Path /opt/jod/lib
OpenOffice/LibreOffice Path for jodconverter

Crypt Type

leave default crypt type (org.apache.openmeetings.util.crypt.MD5Implementation)
red5SIP Configuration
Enable SIP No
SIP rooms prefix 400
SIP extensions context rooms

Please click “Finish” button to start installation!
Wait for it to finish install, then click “Enter the Application”, login as Admin created.
Cancel the sesison in the console,

sudo /opt/openmeetings/red5-shutdown.sh
sudo /opt/openmeetings/red5.sh &

Now go back to the web interface and enjoy Openmeetings

http://SERVERIP:5080/openmeetings

Vmware guest – : [ warning] [vmusr:vmusr] Error in the RPC receive loop: RpcIn: Unable to send.

Error message : [ warning] [vmusr:vmusr] Error in the RPC receive loop: RpcIn: Unable to send.  Is shown on a VMWARE guest when on ESX 5.1 with VMWARE tools installed.

This should be fixed according to kb2036350, however the tools.conf is not created.

FIX:

Create the file C:\ProgramData\VMware\VMware Tools\tools.conf 

Enter the following into the file:

[logging]
log = true

# Enable tools service logging to vmware.log
vmsvc.level = debug
vmsvc.handler = vmx

# Enable new “vmusr” service logging to vmware.log
vmusr.level = error
vmusr.handler = vmx

# Enable “Volume Shadow Copy” service logging to vmware.log
vmvss.level = debug
vmvss.handler = vmx

Set language on Citrix for MACOS

If the server side citrix has a different language to the users language, the default (User Profile) is taken as the primary language and can result in issue on login and application.

On MACOS with Citrix Receiver, the Config file sets the Keyboardlayout.

To set the default language in Citrix connections to British (other languages available listed on the citrix site ) change the Keyboardlayout.

This is done in the file ~/Library/Application Support/Citrix Receiver/Config under the users home folder.

To change it from default (User Profile) to British open Terminal (Applications – Utilities – Terminal) and paste the following in and enter. The command is all one line, make sure to copy all.

sed -ie ‘s/KeyboardLayout=(User Profile)/KeyboardLayout=British/’ ~/Library/Application\ Support/Citrix\ Receiver/Config

yum-updatesd – No network available

Yum-updatesd reports “Check for updates failed: No network available” and doesnt find any updates.
It appears that the Network manager doesnt report correctly online.

Name : yum-updatesd
Arch : noarch
Epoch : 1
Version : 0.9
Release : 11.fc15

Run yum in debug to check:
yum-updatesd –no-fork –oneshot –debug

Going to exec: [‘/usr/libexec/yum-updatesd-helper’, ‘–check’, ‘–network-fail‘, ‘–email’, ‘–email-from=xx.xx@gmail.com’, ‘–email-to=xx.xx@gmail.com’, ‘–smtp-server=localhost:25’, ‘–sendmail’, ‘–debug’]
Check for updates failed: No network available

Edit yum-updatesd to stop it appending –network-fail:

vim +/NM_ONLINE /usr/sbin/yum-updatesd

bus = dbus.SystemBus()
try:
o = bus.get_object(“org.freedesktop.NetworkManager”, “/org/freedesktop/NetworkManager”)
# if o.state() != NM_ONLINE:
# args.append(“–network-fail”)
except dbus.DBusException:
pass

Comment out the !if ostate() != NM_ONLINE and the args.append(“–network-fail”)

More details can be found in the RH bugzilla, although it is meant to be fixed it appears it still has problems with Network Manager even in the later version (0.9.11.fc15)

https://bugzilla.redhat.com/show_bug.cgi?id=709315

THOMSON TG585v7 HTTPS forwarding (disable admin interface)

Telnet to the box

Administrator (capital A)

Password for the router interface

config dump

[ servmgr.ini ]
ifadd name=PPTP group=lan
ifadd name=HTTP group=lan
ifadd name=HTTPs group=lan
ifadd name=HTTPs group=wan
ifadd name=FTP group=lan
ifadd name=FTP group=wan
ifadd name=TELNET group=lan
ifadd name=TELNET group=wan

Dont know about any one else but i dont really want my router to be accessible for the admin interface to the internet on TELNET and HTTPs 😮

service system ifdelete name=TELNET group=wan
service system ifdelete name=HTTPs group=wan
saveall
system reboot

You can then forward HTTPS from within the web interface to your desired device.

Linux through NTLM proxy (yum, wget..)

Ntlmmaps

http://sourceforge.net/projects/ntlmaps/files/ntlmaps-dev/ntlmaps-0.9.9.6/ntlmaps-0.9.9.6-1.noarch.rpm/download

http://ntlmaps.sourceforge.net/

rpm -Uvh ntlmaps-0.9.9.6-1.i386.rpm
rpm -ql ntlmaps
vim /etc/ntlmaps/server.cfg

PARENT_PROXY:MS-Prosy_IP
PARENT_PROXY_PORT:MS-Proxy_Server_Port
NT_DOMAIN: windows_Domain_name
USER:proxy-user
PASSWORD: Proxy-user_Password

Yum
vim /etc/yum.conf

proxy=http://localhost:5865

Start ntlmaps:
/opt/ntlmaps/main.py &
NTLM authorization Proxy Server v0.9.9.6 Copyright (C) 2001-2005 by Dmitry Rozmanov, Darryl Dixon, and others. Now listening at <hostname> on port 5865

yum update

Wget

vi ~/.wgetrc

Add the following to the newly created file:

http_proxy=http://localhost:5865/
ftp_proxy=http://localhost:5865/
use_proxy=on
wait=15

OR use a export:

export http_proxy=http://127.0.0.1:5865
export http_proxy