Holiday reading: Analyze performance in DB2 on IBM i

It is very easy to write working SQL statements to access data stored in DB2 on your IBM i, but if your users want their data fast, you need a deep understanding how data retrieval on the i works and how to tune your SQL statements and indexes to get maximum performance. So my today holiday reading is the ultimative source of informations regarding SQL performance on the i. It is a little bit old, but still one of the most valuable reading.

OnDemand SQL Performance Analysis Simplified on DB2 for i5/OS

To get up to date you can read the DB2 section of the following two redbooks.

IBM i 6.1 Technical Overview
IBM i 7.1 Technical Overview with Technology Refresh Updates

Migrate a Twinax Console to a Lan Console on IBM i.

I do not know if i was the last on earth who was still using a Twinax Console to operate an i/5, but today i have migrated  succesfully our twinax console to a LAN Console. I had to do the following steps for the migration:

Locate the LAN Console Port on your system. For our Modell 520 it is the T5 port. If you are unsure check the Power Systems info center for informations how to locate the Lan Console port. Check with wrkhdwrsc *cmn  and wrkcfgsts *lin that there is no active line description configured for this ethernet port. The Lan Console port cannot be shared with other Ethernet lines on your system.

Then sign in to the System Service Tools with strsst

Choose Option 8 "work with service tools user IDs and Devices"

Choose option 4 "Configure service tools LAN adapter"

Change all values on the "select console" so that the match the screenshot and the press "Enter" to continue.

Press F11 to go to configure your new service LAN Adapter.

Fill in a static IP Address for the LAN Adapter and the default gateway and subnet mask matching your LAN configuration. As an Hostname for service tools you can use every symbolic name you want for example "SYSCONSOLE1". This IP address habe to be unused and must not match any IP Address of other servers in your network. Then press "F7" to store your configuration and go back with multiple "F3 presses to the start screen of the SST.

Normally you have to make an IPL to activate your new console configuration. But if you are not able to IPL your machine you can try the following steps to activate the LAN Console without an IPL.

Start SST with STRSST if you are not already in the SST Screen.

Select Option 1 "Start a service tool"

Select Option 4 "Display/Alter/Dump"

Select Option 1 "Display/Alter storage"

 Select Option 2 "Licensed Internal Code (LIC) data"

 Press Page Down to go to the second menu of "Select Lic Data".

Select Otion 14 "Advanced Analysis" and page down in the List of the Macros till you see the "OPCONSOLE" macro.

Select the "OPCONSOLE" macro with "1"

Fill in "RESTART" as the option and press enter to run the macro. After that you can leave the SST.

Now you can try your new LAN console on your local workstation. Start the "Operations Console" from the i Access start menu.

When everything is configured correctly the operations console will discover the console port automatically and you can connect to the console of your system.

After the connection has been established you have to sign in with a SST user. Be aware that the username and password is case sensitive.

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

Howto split a String with a delimiter in SQL

Today i want to show how to split a String with a delimiter in SQL for example in a table with modelnumbers which consists of two parts sperated by a slash.

model
A1/44
CX3/2
C/140

To get the first and second part of the modelnumber you can use

select  LEFT( trim(model),LENGTH(trim(model))-ABS(LOCATE('/',trim(model))-1)) model1, RIGHT( trim(model), LENGTH(trim(model))-LOCATE('/',trim(model))) model2 from modeltable

to get the result:

model1   model2
A1       44
CX3      2
C        140
     

Performanceverbesserungen durch Indexonly Access beim Zugriff auf die DB/2

Sehr oft braucht man bei relationalen Zugriffen nicht den ganzen Datensatz sondern nur ein Feld. Ein typisches Beispiel ist der Kundenstamm. Meistens braucht man in Selects für die Anzeige nur den Kundennamen nicht aber den Rest der Informationen. In einer Auftragsanzeige könnte z.B. die Auftragsnummer, Kundennummer ,Kundenname, Artikelnummer angezeigt werden.

Das SQL Statement dafür wäre:

select auftraege.auftragsnummer, auftraege.kundennummer, kunden.kundenname, auftraege.artikelnummer from auftraege, kunden where auftraege.kundennummer=kunden.kundennummer

Um die Verarbeitung zu beschleunigen wurde ein Index über die Kundentabelle mit der kundennummer als Unique Key erstellt. Damit wird bei der Verarbeitung folgender Zugriffsplan verwendet:

Die Auftragsdatei wird mittels Tablescan komplett durchgelesen. Für jeden Auftrag wird ein Index Zugriff durchgeführt und mit den Auftragen zusammengejoint. Dann muss aber noch für jeden Kunden ein extra Zugriff auf den Satz in der Kunden Tabelle erfolgen, um den Namen zu lesen. In unserer Beispieldatenbank sind weil wir 14.941 Aufträge haben dafür genau so viele Zugriffe notwendig.

Wenn diese Abfrage in der Anwendung sehr oft verwendet wird, entsteht natürlich ein relativ großer Overhead. Dies kann man durch einen besseren Index, der die Verwendung von Index only Access erlaubt verbessert werden.

Wir ergänzen daher unseren Index und fügen hinter der kundennummer noch das Feld kundenname an.

Wenn wir nun die selbe SQL Anweisung wie oben ausführen, bekommen wir einen schnelleren Zugriffsplan:

  
Die aufwendige Verarbeitung des Tableprobes ist komplett weggefallen. In den Erläuterungen zu dem Zugriffsplan steht auch, dass die Indexonly Methode für den Zugriff verwendet wurde.

Bei meinen Tests konnte ich eine 25% Performanceverbesserung des Query mit dem verbesserten Index gegenüber der ursprünglichen Variante feststellen.

Der Indexonly Zugriff beschleunigt nicht nur Joins sondern auch normale Zugriffe.

Das SQL:

select kundenname from kunden where kundennummer=4711

wird wenn der um kundenname erweiterte Index vorhanden ist mit einem optimierten Zugriffsplan ausgeführt.

statt dem schlechteren Zugriffsplan wenn nur die kundenNummer im Index vorhanden ist.

Die Tests wurden auf einen i/5 V6R1 durchgeführt. Sollten sich aber auf anderen Systemen ebenfalls nachvollziehen lassen. Einfach einmal ausprobieren.