Tag Archives: DB2

JDBC JT400 setting to get CrUD and Show SQL features added in Netbeans with IBM DB2 for i

I finally figured out the missing connection property needed so that Netbeans will allow you to add, edit, and delete rows with inline editors and selected rows.  In addition to showing SQL Scripts for Create,Insert,Delete and Update.  It is the extended metadata property which is by default set to false and needs to be set to true.  I found this out by looking the jdbc properties at http://www.ibm.com/support/knowledgecenter/ssw_ibm_i_71/rzahh/jdbcproperties.htm and looking for properties effecting read only status, which is why those features were blocked on Netbeans.

In your connection properties add

extended metadata=true

by right clicking your connection in the database panel of netbeans while disconnected and going to properties

Screen Shot 2016-08-21 at 5.45.29 PM.png

Click the ellipses …) next to connection properties
Screen Shot 2016-08-21 at 5.45.53 PM.png

Click under the property column and type extended metadata.  Click under Value and type true

Screen Shot 2016-08-21 at 5.46.28 PM.png

Now run a SELECT statement and you’ll be able to Create, Update and Delete records by using the icons above the result set or by right clicking the result set area and using the context menu.  Which also had the Show SQL Scripts for CrUD.

Screen Shot 2016-08-21 at 5.28.59 PM.png

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

 

Netbeans: Creating Stored Procedures or running Dynamic compound statement on IBMi DB2

I finally figured out how you can create a stored procedure or run Dynamic compound statements in Netbean’s Database service on the IBMi.  My problem was that whenever I tried to run a create stored procedure script the SQL editor didn’t know how to parse it correctly so it would send the command line by line to the database using the semi colon as the delimiter instead of as one contiguous command.

The key to getting this to work in Netbeans is to create a Delimiter that marks the end of the SQL that you want to send to the system.  In the example below I send a request that creates 2 temp tables, and then 2 requests to pull the data from those tables.  This example is simple and doesnt show the full power of a DCS.  The dynamic compound statements allow you to use variables, control logic, cursors, etc… to do database specific tasks.  Its like a stored procedure except you don’t have to create it first.   see:

https://www.ibm.com/developerworks/community/wikis/home?lang=en#!/wiki/IBM%20i%20Technology%20Updates/page/Dynamic%20Compound%20statement

and

https://www.ibm.com/support/knowledgecenter/ssw_ibm_i_72/sqlp/rbafydyncompound.htm

It did seem that a dynamic compound statement can’t return a result set for some reason (at least when i tried).

Below is an example of the error that I was getting when i was running into this issue where the database service was not parsing the CREATE PROCEDURE statement as one contiguous chunk.

Error code -104, SQL state 42601: [SQL0104] Token <END-OF-STATEMENT> was not valid. Valid tokens: ;.
Line 2, column 1

Below is me trying DCS in different IBMi tools

cimmfjkweaexyhx

IBM i DB2 – Synchronizing local database table with Remote table

Setup remote db in Green Screen:

-- Use ADDSVRAUTE so that the system knows the username and password
-- when connecting to the remote system
ADDSVRAUTE USRPRF(MYUSRPRF) SERVER(PRODSERVER) PASSWORD()
-- Or if you already have an entry you may need to change your entry with
CHGSVRAUTE USRPRF(MYUSRPRF) SERVER(PRODSERVER) PASSWORD()
-- Use wrkrdbdire to create/edit remote db connection configurations
WRKRDBDIRE
-- Use CHGDDMTCPA to setup how TCP connection will work (enforcing
-- password policy and encryption)
CHGDDMTCPA AUTOSTART(*YES) PWDRQD(*USRENCPWD) ENCALG(*DES)

alternatively you could create the connection every time doing:


CONNECT TO REMOTE_SYSTEM_NAME USER USERPRF_NAME USING 'MY_PASSWD_IN_SINGLE_QUOTES';

SQL

-- ::Local IBMi::

-- Let's back up the table before we wipe and replace it
CREATE SCHEMA BACKUP

--Create backup
CREATE TABLE BACKUP.TABLETOSYNC
AS (SELECT * FROM MYLIB.TABLETOSYNC)
WITH DATA;

--Wipe local table
DELETE FROM MYLIB.TABLETOSYNC;

--Replace with Production data
INSERT INTO MYLIB.TABLETOSYNC (
SELECT * FROM PRODSERVER.MYLIB.TABLETOSYNC);

--View the table data
SELECT * FROM MYLIB.TABLETOSYNC;

--::MORE ADVANCED WAY WITH MERGE::
--::This would be more elegant but time consuming by setting up MERGE with update insert rules
--instead of wiping and replacing. More advanced example here:http://www.itjungle.com/fhg/fhg092210-story02.html

--Create temp table from production data
CREATE TABLE QTEMP.TABLETOSYNC
AS (SELECT * FROM PRODSERVER.MYLIB.TABLETOSYNC)
WITH DATA;

MERGE INTO MYLIB.TABLETOSYNC AS TARGET_TABLE
USING QTEMP.TABLETOSYNC AS SOURCE_TABLE
ON (TARGET_TABLE.TABLEKEYFIELD=SOURCE_TABLE.TABLEKEYFIELD )
WHEN MATCHED THEN
UPDATE SET
TARGET_TABLE.COL1 = SOURCE_TABLE.COL1
WHEN NOT MATCHED THEN
INSERT (COL1)
VALUES(SOURCE_TABLE.COL1 )
ELSE IGNORE;

-- More info:
-- https://publib.boulder.ibm.com/iseries/v5r1/ic2924/index.htm?info/ddp/rbal1mst63.htm
-- http://www.itjungle.com/fhg/fhg072512-story02.html

-- Another thing i looked into using
-- The connect to command actually puts you into that server instead of just giving you a reference to it....
-- CONNECT TO PRODSERVER USER MYUSRPRF USING 'password';

UPDATE: I’ve put all this logic into a stored procedure and you can download the code here: https://github.com/phpdave/DB2-for-IBMi/blob/master/Stored%20Procedures/SYNCTABLE.sql Let me know if you have any comments for improvement. The current stored procedure has commitment control to save you from sql exceptions or something going wrong in the middle of execution. If a problem occurs everything will be rolled back to a save point (Imagine deleting the database and then it not inserting.)

This might also be helpful and simpler-  https://www.ibm.com/developerworks/community/wikis/home?lang=en#/wiki/IBM%20i%20Technology%20Updates/page/INSERT%20with%20remote%20SUBSELECT

iSeries Navigator

You can also just go to the table in iSeries navigator MS windows client and right click the table and click copy. Then go to the target system and right click on the library you want to paste the table in. This is great for quick manual copies.

iSeries Access 64 Bit Driver problems Segmentation Faults

Update: May 17 2016 – At common I found out that 64 bit issues should now all be resolved when using the latest PHP 5.6, the unixODBC manager 2.2.14+ and IBM i Access for Linux ODBC driver.  This was effecting PHP extension: PDO ODBC, and ODBC_* functions

PHP 5.6.x+
http://php.net/downloads.php

UnixODBC Manager
http://www.unixodbc.org/

IBM i Access ODBC Driver [for Linux] (ibm-iaccess-1.1.0.1-1.0.x86_64)
http://www-03.ibm.com/systems/power/software/i/access/linux.html

Kevin Adler’s Article from IBM
https://www.ibm.com/developerworks/ibmi/library/i-ibmi-access-client-solutions-linux/

Segmentation Fault Problem

I’ve had a lot of trouble with using the iSeries Access 64 bit odbc driver for Linux (iSeriesAccess-5.4.0-1.6.x86_64.rpm) on my RHEL linux box to connect toa  V5R4 iSeries server.  I switched back to using the 32 bit driver and saying i’ll wait until they update the 64 bit driver to work.

I was going to the PHP page and finding just a blank screen returned to the browser, which made it a pain to debug.  I found out after looking at the apache error log file in /var/log/httpd/error_log  that there were segmentation faults with the following error:

[notice] child pid XXXXX exit signal Segmentation fault (11)

To try and debug the PHP to see what causing the problem i put die(‘here’) statements in my code until i found out where the interpretor wasn’t getting to because of the seg fault.   I found out on the return of the Zend Framework’s PDO ODBC Fetchall statement the program was causing the seg fault.  I also noticed that this only occured when i had a null field in the DB2 udb for iSeries database.  The field was a char datatype.

If you’re having this problem i’d recommend just installing the 32 bit driver(iSeriesAccess-5.4.0-1.2.i386.rpm).

Debugging

Here’s how i understand requests get called in case you want to try and debug the problem:

  1. Request for page comes in from Apache
  2. PHP runs and makes db calls through  a DSN
  3. All db calls go through unixODBC manager
  4. The db call then goes through the IBM Client Access driver you have specified and executes the sql on the iSeries.
  5. If the driver has a problem with a null field it would create a segmentation fault.

Here’s a link to someone’s explanation of the problems using the 64 bit driver:

https://www.ibm.com/developerworks/forums/thread.jspa?messageID=14510046&#14510046

Need help understanding how to install the driver and setup all the linux files (/etc/odbc.ini /etc/odbcisnt.ini)

Here’s the link to the driver download

iSeries Access Linux Driver download

Remember to edit your
/etc/odbcinst.ini

[iSeries Access ODBC Driver]

Description &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; = iSeries Access for Linux ODBC Driver
Driver &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp;= /opt/ibm/iSeriesAccess/lib/libcwbodbc.so
Setup &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; = /opt/ibm/iSeriesAccess/lib/libcwbodbcs.so
NOTE1 &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; = If using unixODBC 2.2.11 or later and you want the 32 and 64-bit ODBC drivers to share DSN's,
NOTE2 &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; = the following Driver64/Setup64 keywords will provide that support.
Driver64 &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp;= /opt/ibm/iSeriesAccess/lib64/libcwbodbc.so
Setup64 &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; = /opt/ibm/iSeriesAccess/lib64/libcwbodbcs.so
Threading &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; = 2
DontDLClose &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; = 1
UsageCount &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp; &amp;amp;amp;amp;nbsp;= 1

and /etc/odbc.ini

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

Giving credit to IBM for this example file