Monthly Archives: July 2015

Simplify debugging of PHP DB2 IBMi errors w/ a Error handler that retrieves the job log

Ever run a query that calls a CL, RPG or SQL and fails somewhere in the process? Then you have to modify the PHP code to dump out your variables or go to a 5250 session to view the job log? Wish you could just see the information from the job log on the webpage or have it email you the information in an email (if your in production)? Well with QSYS2.JOBLOG_INFO(*) you can!

Below is a demo code on how to accomplish this using a PHP’s set_error_handler. The demo code will try to run a SQL SELECT on a nonexistent table that will invoke myErrorHandler and it will call QSYS2.JOBLOG_INFO(*) on the current connection to get all the information from IBMi DB2 and then output it to the screen for you. You could change the echo statement to a mail statement if you’d rather it send you an email in case you are in production. You could have it dump out other information such as the $_REQUEST, $_SESSION to get more information on what was happening when PHP failed

Note: There’s alot more information obtained than what you get from db2_stmt_error() and db2_stmt_errormsg()

db2_ibmi_callstack

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.