Read Character Columns with CCSID 65535 in JDBC

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);
  dataSource.setTranslateBinary(true);
  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

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"

Field	Type
Customer_Number	CHAR(10)
Customer_Name1	CHAR(30)
Customer_address	CHAR(30)

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

Visual Explain explained

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 http://pic.dhe.ibm.com/infocenter/iseries/v7r1m0/topic/rzajq/rzajq.pdf

IBM System i Pre-Upgrade Verification Tool

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.

Gratulation Bayern München und David Alaba

Unvorstellbar, das erste mal in der Geschichte der Championsleague spielt ein Österreicher im Finale der Champions League mit und dann gleich ein Sieg. Da kann man sich sogar mit den Bayern mitfreuen. Jetzt hoffen ich nur, dass David auch in der Nationalmannschaft eine ähnlich gute Leistung abrufen kann.

Howto make regular expressions caseinsensitive

Regular expression are very useful for string operations, but one annoying thing is that regular expressions are normally case sensitive. But with a simple option you can change this default behavior. You only have to add the "(?i)" clause to your regular expression. Here is an example:

  
  String test = "The lazy brown fox jumps over the quick Dog";
  // The following command will not replace Dog, because the regex is not
  // case insensitive.
  System.out.println(test.replaceAll("dog", "cat"));
  // With (?i) added the regex will ignore case and Dog will be replaced
  // by cat.
  System.out.println(test.replaceAll("(?i)dog", "cat"));

But be aware, that the "(?i)" clause does only work for ASCII strings. If you have international characters in your regular expressions you have to add the "u" flag to your regex.

 
  String test = "Äpfel und Birnen";
  // The following command will not replace Äpfel, because the regex is
  // not unicode aware.
  System.out.println(test.replaceAll("(?i)äpfel", "Zitronen"));
  // With (?iu) added the regex is unicode aware and Äpfel will be
  // replaced by Zitronen.
  System.out.println(test.replaceAll("(?iu)äpfel", "Zitronen"));

Notes Domino 9 group 1 language versions are availabe now.

The first national language versions (Group 1) for example Catalan, Italian and German of Notes Domino 9 are available in passport advantage now.

So there is no more reason to postpone your upgrade to the greatest version of Notes/Domino.

The GA2 languages like Finnish, Norwegian, Swedish, Danish, Dutch will be availabe on 04.06 and the remaining languages like Greek, Slovenian, Thai, Turkish, Russian, IB Portuguese, Hungarian, Slovakian, Hebrew, Kazakh, Polish, Czech should be available on 23.07

Improve File enumeration performance in the admin client

Since Domino 8.5 there have been many files in the data/domino directory on the server. So every time you refresh the files tab you have to wait while the server scans the whole data/domino directory. Although some contents like domino/js is not scanned this can take some time. Fortunatly there is a notes.ini setting which disable file scans in the domino directory.

To enable this feature you cann add ADMIN_CLIENT_SKIP_DOMINO=1 to the notes.ini of the server or you can set this value with SET CONFIGURATION ADMIN_CLIENT_SKIP_DOMINO=1 on the console.

I have tried this on my development server. Without ADMIN_CLIENT_SKIP_DOMINO=1 the file enumeration runs 4 seconds and with this setting in the notes.ini of the server the enumeration has finished immediatly after pressing the F9 key.