Monthly Archives: June 2015

Testing DB2 prepare and execute select statements in IBM i Navigator to debug ZF2 ZFCUSER bug using a sql stored procedure

Have you ever wanted to run a prepare and execute select sql statement that you have in PHP in IBM i navigator’s “Run SQL”?

take for example this PHP:

This is how you can create the Album table and then create a stored procedure to test the SQL select prepared statement

ODBC tracing a windows application to an IBMi

In ODBC Administrator go to Tracing tab and click “Start Tracing Now”

Go to run cmd.exe and run cwbcotrc on /allusers

Run the ODBC application until your down with what you wanted to trace

Go to run cmd.exe and run cwbcotrc off

In ODBC Administrator go to Tracing tab and click “Stop Tracing Now”.

Do a search for SQL.Log on your c:/ drive

Go to run cmd.exe and run cwbsvget
Hit enter to open explorer to the cwbsvget.zip file

In that zip file there’s a lot of ODBC api calls to help you better understand whats going on.  I used notepad++’s find in files search to dig into it.

#IBMi 7.1 Gotcha – No Field Names in Data Transfer from IBM i

UPDATE: Apparently you need to run this:

  • RCLDBXREF OPTION(*CHECK) if there is a problem try RCLDBXREF OPTION(*FIX). If that doesn’t work try RCLSTG SELECT(*DBXREF) when the system can be put into a restricted state and the system can be down for a bit.

Anyone else running into this issue?  Where the field names don’t show up when using Data Transfer from IBM i in IBMi Access for Windows 7.1?  You select the IBMi (running V7R1 TR10) and then the file name and click Data Options… and then Details and you’ll get these 2 error messages CWBDB0014 Internal Software error (System %1) and CWBDB0036 – Server returned SQL error Token / was not valid.

Click image below for a bigger picture

DataTransferFromIBMi

IBMi 7.1 Client Tools gotcha – “Run SQL” not in IBMi Access Client Solutions or Navigator for i yet

You might run into this mutually exclusive thinking that you need IBMi ACS WAP and IBMi Access for Windows 7.1.

The latest IBMi tools do not have Run SQL or visual explain built into them.  When installing the IBMi ACS Windows Application package you’ll find out that they tell you to uninstall IBM i Access for Windows (5770XE1) product, which will remove your System i Navigator and your ability to do “Run SQL”. “Run SQL” is not built into the web client IBM Navigator for i (https://myibmi:2005/ibm/console/) or IBM i Access Client Solutions or the IBM i Access Client Solutions – Windows Application Package.

So what do you?  I don’t know.  I guess I’ll uninstall IBMi – ACS – WAP, install IBM i Access for Windows 7.1  and wait until they put that developer critical piece back in.

There’s IBMi specific functionality built into “Run SQL” like running CL statements (via “CL: <CL COMMAND>”) and dynamic compound SQL (which is not valid in a jdbc connection), and a host of other awesome features….  You can copy and paste a DB script into there and run multiple lines of SQL…

This surprises me because IBM has been pushing for SQL development in the sessions i attended at the COMMONUG…   Run SQL was the easiest way to test your SQL code before adding it to your application.

7.1gotcha

IBMi V6R1 to V7R1 TR10 Gotcha – ODBC – No Field Names retrieved

The odbc connection is connecting to the IBMi (formerly known as iSeries, As400) and pulling back “physical files”/”table names”, but not pulling back column names.  The errors from UPS World Ship were “The selected import/export map is no longer valid because the column names in the external database have changed.”  “Import data source not valid.”

Ways to try and resolve this issue

  1. Install the latest IBM i Access v7.1 windows client to get the latest ODBC driver.
  2. Install the latest service pack for IBM i Access (SI55797_64a.exe at the time of this writing)
  3. Delete QGPL/QZDAPKG
  4. RCLDBXREF OPTION(*CHECK) if there is a problem try RCLDBXREF OPTION(*FIX). If that doesn’t work try RCLSTG SELECT(*DBXREF) when the system can be put into a restricted state and the system can be down for a bit.

References

Deleting QGPL/QADAPKG  http://www-01.ibm.com/support/docview.wss?uid=nas8N1015556

RCLDBXREF –  http://www-01.ibm.com/support/knowledgecenter/ssw_ibm_i_61/cl/rcldbxref.htm

RCLSTG – http://www-01.ibm.com/support/docview.wss?uid=nas8N1010683