Posts Tagged: ‘System i V7R1’

IBM has announced IBM i 7.1 Technology Refresh 7

7. Oktober 2013 Posted by Ralf Petter

Today IBM announces the latest Technology Refresh for IBM i. This Technology Refresh provides many new functions and improvements like:

  • An enhanced RPG IV programming language which includes new Free Format H, F, D and P statements. With the exception of old RPG Cycle Code like I, O statements this is the first time you can write RPG programs entirely in Free Form! more...
  •  An improved DB/2 Version which can handle larger indices (1.7 TB) and new system management catalogs. Dynamic Compound Statements. New Possibilities to control the blocking size in sequential SQL reads and many other DB/2 Enhancements.
  • Java has got some performance improvement and the API has updated to 1.7. more...
  • TR 7 brings also support for new Hardware Options
You can find all other improvements in the announcement letter from IBM.

I think TR 7 was really worth the wait and should be available in November.

Read Character Columns with CCSID 65535 in JDBC

12. Juni 2013 Posted by Ralf Petter

In DB2 on the System i every Character Column have a CCSID which defines with which characterset the data of this column is encoded. For example a column with german characters should normally be encoded with 273 or 1141. When you access this columns from JDBC the data will be automatically converted from the characterset used in the column to Unicode. But when the CCSID of the column is 65535 (binary data) the jdbc driver will not convert and since all data on the System i is stored in EBCDIC you will only get garbage from this columns.

But there is an easy way to fix this problem. You need to set the translateBinary Option to true when you connect to your System i and every 65535 character field will be translated from EBCDIC to Unicode automatically.

  AS400JDBCDataSource dataSource = new AS400JDBCDataSource(systemName, userName, password);
Connection conn = dataSource.getConnection();

// Or when you prefer the old connection Method
Connection conn2 = DriverManager.getConnection("jdbc:as400://" + systemName + ";translate binary=true",
userName, password);

Then the result is human readable.

Add a column to a DB2 table in a specific position in V7R1

10. Juni 2013 Posted by Ralf Petter

With the SQL command "ALTER TABLE" you are able to add columns to your DB2 Tables for a long time. But before V7R1 you can only add columns to the end of the row. In V7R1 you can use the "BEFORE column" clause to specify the exact position where your new column should be inserted. Here is an example:

Table "customer"


With "ALTER TABLE customer ADD COLUMN Customer_Name2 CHAR(30) BEFORE Customer_address" can you add a second name column to the right position.

Visual Explain explained

8. Juni 2013 Posted by Ralf Petter

Visual Explain in Db2 is a great tool which shows you how the query optimizer actually processes your sql statements. It gives you every information that you need to improve your sql statements or gives you hints which indexes you should create to get a better performance. Here is an example of the output of visual explain:

Every little icon represents one process step in your query. Some of the icons are pretty clear. Everyone knows what a table scan or and index probe means. But what about the others? The online help of Visual explain is not really helpfull, because there are only very short descriptions of every icon. But fortunatly there is a detailed description with examples of every access method the query optimizer can choose to process your sql statements in the IBM i information center. If you prefer to read this informations on paper you can download a PDF from

IBM System i Pre-Upgrade Verification Tool

5. Juni 2013 Posted by Ralf Petter

On the next weekend i will upgrade our System i from V6R1 to V7R1 TR 6. One valuable tool for the upgrade preparation from IBM is the Pre-Upgrade Verification Tool. This tool runs on your windows workstation and checks if your system i meets all requirements and prerequisites to install the new release. To run the tool simply download and decompress the tool to a folder. In this folder run the "run_pruv.bat". An wizard opens, which will guide you through the process of the verification. After the wizard finishes the tool presents an overview with errors or warnings which could break the installation of the new operating system.

For every warning or error you will get a detailed description how to solve the problem.

So in my opinion a really cool tool to make your upgrade easier.