IBM iSeries a.k.a AS400

Owned by Former user (Deleted)
Last updated: Nov 10, 2011 by Former user (Deleted)Version comment

Used Ports for JDBC

Please see the following link for the default ports for JDBC to iSeries:
http://www-03.ibm.com/systems/i/software/toolbox/faqports.html

The ports listed are the defaults for the servers. These port assignments can be modified using the "WRKSRVTBLE" command.

You can also check with "NETSTAT" on the iSeries if the ports are open. 

JDBC driver versions

Please see the comments on IBM DB2.

EBCDIC Encoding

One of our partners came across a special issue with an AS/400 (a.k.a. iSeries a.k.a IBM System i) returning data in EBCDIC encoding to Pentaho Data Integration.

For instance this can happen when one of the tables or fields have a CCSID of 65535 what means no encoding.

PDI in this instance was running on the Pentaho Platform.

There are 2 relevant entries in the JT Open FAQ :

- What character conversion issues must my program deal with?
- Why is the Toolbox JDBC returning EBCDIC characters to my Java program?

The second entry led to the solution.  All that was needed was adding the following option to the URL in the JNDI data source:

;translate binary=true

If you are using a JDBC connection, you can add the option to in the Options pane: 

Option: translate binary
Value : true

This solved the issue.

Zoned or Packed Fields

The IBM Toolbox for Java JDBC properties list the property "big decimal". When set to "true" it returns a Kettle data type of a BigNumber instead of a Number that could help in specific cases. It is recommended to set this option to "true" due to possible rounding issues with Numbers (double precision values), but has a small performance impact.

Monitoring

Have a look at the QZDASOINIT jobs at the AS/400. These jobs are serving the database SQL server jobs for JDBC and looking into these give a lot of extra information.