Tag Archives: IBM i

DB2 for i in the Cloud – Connecting to PUB400 via JDBC in Netbeans

1.Sign up for an account at http://pub400.com/signup.html

2. Download and extract jtopen which contains lib/jt400.jar driver for connecting to an IBM i http://jt400.sourceforge.net/

3. Download and install Netbeans https://netbeans.org/ .  I do the PHP bundle, but i believe they all have the Database module.

4. Go into the “services” tab under database and right click the Drivers folder and click “New Driver”Screen Shot 2016-08-20 at 7.30.57 PM.png

Find where you extracted JT400 and select lib/jt400.jar

Screen Shot 2016-08-20 at 7.32.19 PM.png

Press the “Find” button so it can find the AS400JDBCDriver class in the jar file and give the driver a name like “JT400 JDBC Driver”

Screen Shot 2016-08-20 at 7.34.55 PM.png

Right click the driver and click “connect using” Screen Shot 2016-08-20 at 7.36.33 PM.png

Fill out the connection wizard with your information given to you from PUB400.com

User Name: <Your username for PUB400>
Password: <Your password for PUB400>
JDBC URL: jdbc:as400://pub400.com/{replace curly bracket and this with your schema name}

Screen Shot 2016-08-20 at 7.09.37 PM.png

After clicking next, Select your Schema

Screen Shot 2016-08-20 at 7.10.21 PM.png

Give the connection a name

Screen Shot 2016-08-20 at 7.10.38 PM.png

Now you can connect to the DB2 for i in the cloud by right clicking the connection and pressing connect

Screen Shot 2016-08-20 at 7.46.26 PM.png

Now you have an easy to get to database connection to a sandbox DB2 for i!  Time to play with the temporal table support and other new enhancements for DB2!

Screen Shot 2016-08-20 at 7.48.05 PM.png

After connecting, your connection properties should show what settings are being used on the connection and even what version of the DB2.  In this case the server is on V7R2m0 :

Screen Shot 2016-08-20 at 7.13.29 PM.png

Step2 add CrUD features by modifying the connection property

https://godzillai5.wordpress.com/2016/08/21/jdbc-jt400-setting-to-get-crud-and-show-sql-features-added-in-netbeans-with-ibm-db2-for-i/

 

 

Advertisements

Compiling latest #NGINX from source on #IBMi PASE (#AIX) and running #PHP through Nginx instead of Apache

Below is my guide to setup Nginx on IBM i PASE environment and have it work with PHP.  Let me know if you have any problems.  If you don’t already have PHP installed and compiled from source you can check my guide here: https://godzillai5.wordpress.com/2016/06/21/compiling-php7-from-source-on-ibmi-pase-aix/ .

Wow! look at whats coming for #IBMi TR11 TR3 #db2 #sql

IBMi Software Announcement: Complete info from IBM

  1. IBM i Access Client Solutions has “Run SQL” interface
    • “Run SQL” scripts was previously only available in the Windows Client (System i Navigator / System i Access for Windows).
  2. Web Sockets w/ Apache level 2.4.12
    • allows for real-time communication.  A lot of chat applications, and real time collaboration apps are using Web Sockets because of the server’s connection to the client.
    • Learn more here
  3. Free-form RPG is no longer forced to columns 8 – 80.
  4. Zend Server for IBM i 8.5 (5639-ZC1)

DB2 for I new features and enhancements

    • ASSOCIATE LOCATOR for processing result sets from a remote db.
    • LOCATE_IN_STRING() – SQL Function –  Find a string inside a string. (PHP devs think strpos)
    • OVERLAY() – SQL Function – easily insert a string/variable inside a string (PHP devs think sprintf but not exactly)
    • Viewing Record locks or Object Locks in SQL by utilizing the DB2 for i services

Other New features:

  1. Open Source tools now included
    1. GCC, Git, .zip, .tar, bash, Python 2.7, and many others in Open Source for IBM i (5733-OPS)
  2. Integrated Web Services (IWS) server makes it easy to open your ILE objects up to webservices done in SOAP or REST

References:

DB2 for i services

System Lock

Record Lock

Pulling #IBMi Source Members to Stream files to run Git on #RPG/#CL/etc… source code via #CL & #PHP #sourcecontrol

Currently in the proof of concept phase but I think this could be used to convert File Members to stream files so they could be used to check into a git repository.

Changes to the source file will hopefully kick off the trigger, which will then call CL program to call the PHP script to pull down all the file’s members into stream files.  Then its in the developer’s hand to run git on the files.  I’ve also considered creating an automated job that would auto-commit an auto-update the git repo so the code would always be checked in and know one would need to do the commits and updates.

difference between PC file structures and AS400/IBMi Difference between PC file structures and AS400/IBMi

PHP IBM i toolkit – Thoughts on its future development

The PHP IBM i toolkit is a collection of PHP files that interact with XMLSERVICE on the IBM i to give you access to IBM i objects. I’ve been thinking that this approach was probably taken because it opens the doors to any programming language that wants to interact with the IBM i (PHP, Node.js, Ruby, asp.net etc… can all use this API). The problem I see with the current implementation is that requires xml encoding, xml parsing, and function calls that aren’t compiled. I think it makes sense that a PHP extension be written in C with hooks into the IBM i objects, much like how IBM_DB2.so php extension has hooks into Physical files, stored procedures, etc… IBM has C ILE that already can interact with all IBM i objects why not have a direct API to call from your PHP like db2_connect().  Your PHP project would call the compiled C PHP extension.  There’s already a starting point since you can view the source of ibm_db2.so.  Chuk from twitter mentioned creating it myself, which is tempting but unfortunately I’m not that familiar with “under the hood” of the IBM i.

IBM i PHP Toolkit

Is it all about performance? Increase in usability?

  1. There has to be a balance between performance and the ability to get the job done quickly, and easily maintainable in the future.
  2. With the PHP extension it would make it more usable as you won’t have to worry about including the CW.php wrapper.
  3. You can use the function calls provided from the php extension anywhere.
  4. Upgrades are as simple as overwriting the .so file in the php extensions folder.

Why C/C++?

  1. You can extend the functionality and performance of PHP with a compiled PHP extension.
  2. You can create C ILE on the IBM i instead of using RPG ILE.  Chris Hird says he does most of his development in C instead of RPG. If he wants to write an application on Linux he can keep using C.  RPG is proprietary and stuck on the IBM i until IBM decides to open source it.
  3. Its popular based on TIOBE rating

Alternative to toolkit to access RPG, CL programs

  1. You can create external stored procedures that attach to RPG or CL Programs (any language in drop down below)

externalproc

  1. QSYS.QCMDEXC can be called in a Stored Procedure to call CL (not sure about the other languages)
  2. The disadvantage is that you have to write and create the stored procedure for every External program.  Which is not an optimal workflow for PHP development (Write PHP, Write Stored Proc, Create Stored Proc)

What is client IBM_DB2 PHP Extension look like under the hood?

So I looked into the IBM_DB2 c source code and figured out that its more like a wrapper around SQL* ODBC functions with additional specific DB2 features added on.  So I guess that really wouldn’t be a starting point for accessing IBM i programs directly.

Responses from Twitter

TIOBE Rating

tiobeprogramming

Links

XMLSERVICE

PHP IBM i Toolkit Gihub Source

Tutorial to create PHP Extension | Another Article on PHP Extensions | More on PHP Extensions

IBM_DB2 PHP Extension Source from PECL

IBM_DB2 uses some ODBC functions and you can find info on them here

PHP Security on the IBM i – Locking down the IFS permissions – Best way to handle authorities in the web root and subdirectories.

Are you unable to modify another user’s PHP file on the IBM i? Do you constantly need to give QTMHHTTP read permissions to the new PHP you uploaded? After going through this guide you’ll fix these issues and streamline your PHP development on the IBM i while maintaining security of the IFS.

Overview of permissions on your Webroot folder for Zend Server:
*PUBLIC: DTAAUT ( *NONE)
PRIMARY GROUP: NOGROUP = DTAAUT (*RX)
OWNER: WEBCODERS= DTAAUT (*RWX)

Summary: Public gets no access, Primary Group is a group that the user QTMHHTTP is a part of and only has read access, and the owner is your development team group profile (WEBCODERS) with your web development team user profiles in that group.

As always test this on a development machine DON’T DO THIS IN PRODUCTION unless you’ve tested it

5 Steps to secure your PHP installation

1. Don’t give *PUBLIC access

CHGAUT OBJ('/www/zendsvr/htdocs/') USER(*PUBLIC) DTAAUT(*NONE) OBJAUT(*NONE) SUBTREE(*ALL)

I’ve heard many people who are insecurely using PHP on the IBM i. If you are giving *PUBLIC any access to your files under /www/zendsvr/htdocs you are giving too much access. You don’t want anyone with access to your IBM I to read your PHP files or your configuration files with database username and password. You should make sure *PUBLIC has no data authorities on all files under web root directory using the CHGAUT command recursively.

2. Set the Primary Group on the webroot (/www/zendsvr/htdocs) to a group with QTMHHTTP in it

CHGPGP OBJ('/www/zendsvr/htdocs') NEWPGP(NOGROUP) RVKOLDAUT(*NO) SUBTREE(*ALL)
CHGAUT OBJ('/www/zendsvr/htdocs/') USER(NOGROUP) DTAAUT(*RX) OBJAUT(*NONE) SUBTREE(*ALL)

Remember that each new object under a parent directory inherits the *PUBLIC authority, primary group authority and the owner authority of the parent. So you’ll want to set the primary group to NOGROUP and give it Read access. Make sure QTMHHTTP is a user of this group. This is the user that PHP is using to access the files and is typically called APACHE or NOBODY in linux systems.

3. Give access to a development team group profile so your web developers have write access to create new files and directories and read access to view the files on the server.  Unfortunately you’ll always have to re-run CHGAUT for WEBCODERS as when someone uploads a file they become the owner.  You may want to consider a daily job that automatically runs this or have your developers share the login information for WEBCODERS and always upload with that profile. 

CHGOWN OBJ('/www/zendsvr/htdocs/') NEWOWN(WEBCODERS) RVKOLDAUT(*NO) SUBTREE(*ALL)
CHGAUT OBJ('/www/zendsvr/htdocs/') USER(WEBCODERS) DTAAUT(*RWX) OBJAUT(*ALL) SUBTREE(*ALL)

4. Give write permissions to directories that QTMHHTTP needs write access.  If your PHP is saving a file or creating a file to the IFS it will need write permissions to that directory. 

CHGAUT OBJ('/www/zendsvr/writeable/uploads') USER(QTMHHTTP) DTAAUT(*RWX) OBJAUT(*NONE) SUBTREE(*NO)

Below are some shell functions you can use if you have bash or bourne shell

5. (Optional) Set the umask to set the default permissions given to new files created by a program (like FTP, SFTP, SSH).  In the example below the first 0 means give user rwx, 2 means give group rx, and 7 means give other nothing.

umask 027
#u=rwx,g=rx,o=

For SFTP you’d modify sshd_config to load a shell .profile that would then run the umask.  (replace * with the version number of openssh you’re using or find it by running find)

find / | grep sshd_config
vi /QOpenSys/QIBM/UserData/SC1/OpenSSH/openssh-*.*p*/etc/sshd_config
# set ibmpaseforishell to your favorite shell (in this case bash)
ibmpaseforishell=/QOpenSys/opt/freeware/bin/bash
#set the umask in our .profile so it will always load by using this command to append 
umask 022 to the end of the file (.profile).
echo "umask 022" >> ~/.profile

#shout out to @aaronbartell for informing me of umask

Experiment using Authorization List: 

Now I looked into using Authorization lists but they don’t inherit from the parent directory IF you’re using the mkdir command API (different from ibm i command line mkdir alias).  That would be the best case scenario since then new objects would get the WEBDEVAUTL authorization list inherited and your development team would be in that list and everyone on your team could create new files and directories and everyone else could modify them later.  Below are the commands to create a AUTL, but remember it will only work if your NOT using the mkdir command

CRTAUTL AUTL(WEBDEVAUTL) TEXT(‘Auth List for Web Developers’)
ADDAUTLE AUTL(WEBDEVAUTL) USER(WEBDEV1 WEBDEV2) AUT(*ALL)
CHGAUT OBJ(‘/www/zendsvr/htdocs/’) AUTL(WEBDEVAUTL) DTAAUT(*RWX) OBJAUT(*ALL) SUBTREE(*ALL)

More info on Mkdir not inheriting from the parent directory here: https://www.ibm.com/developerworks/community/forums/html/topic?id=77777777-0000-0000-0000-000014510624 .  Hopefully IBM will one day have mkdir have the same functionality as CRTDIR CL command.  Another good read about the IFS: http://publib.boulder.ibm.com/iseries/v5r1/ic2924/books/c415300522.htm