Tag Archives: IBM

Using a sequence object in DB2

Simple snippet to show how to use a Sequence object that auto increments in an SQL statement.  Just realize that when used in a stored procedure you cannot drop the sequence as the database will let you know that there’s a dependency of this object within a stored proc.

CREATE SEQUENCE MYLIB.MYAUTO_INCREMENTING_SEQ
AS BIGINT
START WITH 1
INCREMENT BY 1
NO ORDER
NO CYCLE
NO MINVALUE
NO MAXVALUE
CACHE 20;

INSERT INTO MYLIB.MYTABLE
(PRODUCTID,PRODUCTNAME)
VALUES (NEXT VALUE FOR MYLIB.MYAUTO_INCREMENTING_SEQ,'Godzilla Ice Cream');

ZF2 – Zend_DB Support for number sign # (AKA pound, hash) in a field / column on the IBMi DB2

If you use Zend Framework 2’s Zend\Db\Sql\Select or Zend\Db\Sql\Join “on expression” to create queries for the IBM i DB2 you’ll sometime’s run into the error

“Column or global variable SomeField”# not found. SQLCODE=-206”

This is because the the Adapter/Platform/AbstractPlatform’s quoteIdentifierInFragment function is used to parse the input you provide. It currently doesn’t allow for the pound sign # to be in a column name and it incorrectly puts quotes around the #.  SomeField# becomes “SomeField””#” , i believe.

If you have this problem you can use my change to /Adapter/Platform/IbmDb2.php found here https://github.com/zendframework/zend-db/pull/95/files .  This change creates a quoteIdentifierInFragment function in /Adapter/Platform/IbmDb2.php that overrides the Base Class AbstractPlatform’s quoteIdentifierInFragment function and uses one that supports # by adding # to the regex.

More info on this getting added to the master branch can be found on my pull request here.  Please feel free to add comments, or commit other code that could be helpful:

https://github.com/zendframework/zend-db/pull/95

Insert statements are not effected because columns are quoted differently.  An array of columns is process and passes the entire column element to: $platform->quoteIdentifier($column); https://github.com/zendframework/zend-db/blob/master/src/Sql/Insert.php#L164

The main change is the regex and you can try different cases here: http://sandbox.onlinephpfunctions.com/code/9b50346237ed0de83f2244282081ae5080ad9f75

 

Setting a default SQL library and library list on a ODBC connection for a IBMi DB2 connection in Windows and Linux

UPDATE: In a JDBC connection using JTOpen 400, library lists are only used when the connection properties is set to naming=system;libraries=MYLIB1,MYLIB AND there’s no default “Schema=” or default schema in the Database URL (i.e. jdbc:as400:MYIBMI/DEFAULTSCHEMA). I’m unsure if this is the same way in ODBC.


ODBC allows you to set a Default SQL Schema or Library and a Library list. This allows you to point your application at different IBMi DB2 libraries.  This could be used so you can have a development, qa, and production libraries and have your application change what data it uses based on your odbc settings.  This also allows you to not need to fully qualify your table names such as SELECT * FROM MYLIB.MYTABLE would be come just SELECT * FROM MYTABLE .  They way this happens is  the default library will by tried first {DefaultLibrary}.MYTABLE and if it doesn’t find it in there it will try the libraries in the library list {library_list…}.MYTABLE [MYLIB1.MYTABLE, MYLIB2.MYTABLE …] .

Below is how to set this up in Windows and in Linux environments.  Make sure you’ve done the steps in this guide to get the odbc driver: https://godzillai5.wordpress.com/2016/09/02/execute-sql-remotely-on-an-ibm-i-via-phps-pdo-odbc-from-a-windows-linux-or-mac/ (mainly this guide http://www-01.ibm.com/support/docview.wss?uid=nas8N1010355)

Windows (ODBC Administration)

Its under the server tab of ODBC Administration tools in windows.  This tool can be found at

C:\Windows\System32\odbcad32.exe – 32 bit
C:\Windows\SysWOW64\odbcad32.exe – 64 bit

You then select your data-source name, configure the data-source, go to the server tab, enter your Default SQL Schema or Library, and the Library list for this connection.

odbc-default-library-and-library-list-ibmi-db2

Linux (unixODBC Manager)

edit the /etc/odbc.ini. 

1) add your default library to DefaultPkgLibrary

2) add your library list to DefaultLibraries 

[iSeriesDSN]
Description = iSeries Access ODBC Driver DSN for iSeries
Driver = iSeries Access ODBC Driver
System = iSeriesSystemName
UserID =
Password =
Naming = 0
DefaultLibraries = MYLIB,MYLIB2,MYLIB3
Database =
ConnectionType = 0
CommitMode = 2
ExtendedDynamic = 0
DefaultPkgLibrary = MYLIB
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 1
LibraryView = 0
AllowUnsupportedChar = 0
ForceTranslation = 0
Trace = 0

JDBC

Below is the JDBC connection string with system naming on and the library list to use.  Default schema can’t be specified or it won’t use the libraries.

jdbc:as400:MYIBMI/;naming=system;libraries=MYLIB1,MYLIB

Work-arounds

1. Change the library list in the job description (JOBD) for the ODBC connection. Do a WRKACTJOB and look for QZDASOINIT jobs handles connections and see what JOBD they are using and change it to include the libraries you need.
2. Call the Stored procedure QCMDEXC to run ADDLIBLE.

CALL QSYS . QCMDEXC (‘ADDLIBLE MYLIB’ ) ; 

This will add the library to the library list entries.

How to change the library list before calling an external RPG program in a DB2 SQL stored procedure on IBMi

Below is how you can create a SQL stored procedure that calls the CL command ADDLIBLE to change the library list before calling an external RPG program that may require that Library in the Library List to run properlyWe also handle the error scenario where you might add a library that already exists (which would crash your SQL stored proc).  Let me know if this is helpful by commenting below or tweeting a reply to @php_dave

PHP IBM i Toolkit – Security Awareness of HTTP transport – Sending UserId and Password in Clear Text

With many open source security exploits coming out (shellshock, Heartbleed and recently Ghost exploits) I decided to look into the open source PHP IBM i toolkit that many people are using to access the IBMi. The whole idea of open source is that by having many eyes looking at something that bugs and security issues would be figured out and new features can be contributed by anyone. The issue I found with the PHP toolkit is that there’s no warning about using the HTTP transport to connect to the iSeries. It’s actually sending your user id and password over the network to the web server in plain text. This is an issue with the XMLService as well. It really should not allow you to connect via HTTP and should force HTTPS connections. I looked into odbc_connect and it appears to me that its doing some type of encryption as I was not able to pick up my username with wireshark. Since IBM_DB2 is using SQLConnect in the php extension i’d assume the same goes for that transport method. Therefore, by default the toolkit appears safe, but if your project requires you to connect via HTTP make sure you refactor the transport to use HTTPS instead.  Also please don’t use the GET method as it puts the parameters (the userid and password) in the url string which is sometimes saved into access logs.

You can see this issue in the send method of httpsupp class

http

To see how to securely create a https request look at the comments from: jrubenstein at gmail dot com and louis dot huppenbauer at gmail dot com on php.net’s stream_context_create

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 can handle multiple result sets from a DB2 SQL Stored Procedures on IBM i

Did you know that a DB2 SQL stored procedure can pull back multiple result sets?  Its as simple as adding this to your Create Procedure statement:  DYNAMIC RESULT SETS 2.  You can use any integer value but I used 2.

To access the additional result sets in PHP you can use either odbc_next_result or db2_next_result.  The main advantage of pulling back multiple result sets is that you only have to issue 1 call to a stored procedure to pull back all the information that you may need.

http://php.net/manual/en/function.odbc-next-result.php

http://php.net/manual/en/function.db2-next-result.php