Surviving in a legacy AS/400 world with a taste of Groovy.

IBM System i, iSeries, AS/400,…

You may have heard of IBM System i, iSeries, AS/400,… he was rebranded multiple times but for most of you it’s a green screen 5250. This system is fairly widespread in our european industry. For java developpement you have access to iseries via jt400 (driver + api for most concept (jobs, program call,…))

Groovy + jt400 + system tables = automation for lazy dba.

Last month, we did a new release of our application and this one required a new set of indexes.

The good news is that the iSeries, when preparing sql statements, is doing an explain plan and logs it’s advised indexes in a system tables. But all advised indexes aren’t good to create, may be you can reuse an existing one by re-phrasing your sql statement. So we had to list advised indexes and existing one for each table, take a look at the number of times the index was advised,…

Doing this manually in the UI tool was in fact too error-prone, too boring. As a java/groovy developper, I should automate this with a groovy script.

Existing tables

So first let’s list all existing tables (physical file) in a given schema (library) using the system view SYSTABLES. Our dba prefer systemName (short name vs long name)

import groovy.sql.Sql
import java.util.*

def getTableSystemNames = {library,sql ->
    sql.rows(""" select * from QSYS2/SYSTABLES where table_schema = '${library}'
                 fetch first 500 rows only with ur""".toString()).collect { it.SYSTEM_TABLE_NAME}
}

Existing indexes

first step, let’s get the existing indexes from sysindexes.
One line is a column of one index… so let’s use groovy goodness groupBy, collect and join to get them one line of format : “column1, column2, column3″

    def getExistingIndexes = { library,tableSystemName,sql ->

    def existingIndexSQL = """with INDX as (
        select INDEX_NAME,SYSTEM_INDEX_NAME,COLUMN_NAME,SYSTEM_COLUMN_NAME from qsys2/SYSKEYS
        where
          index_name in (SELECT INDEX_NAME FROM qsys2/sysindexes 
                         where SYSTEM_INDEX_SCHEMA = '$library' and system_table_name = '$tableSystemName' )
           and index_SCHEMA='$library'
        )
        select * from INDX
        fetch first 500 rows only with ur
    """;
    rows=sql.rows(existingIndexSQL.toString())
    existingIndexes = [:];
    def existingIndexesColumns = rows.groupBy { it.SYSTEM_INDEX_NAME}
    existingIndexesColumns.each {row -> existingIndexes.put row.key, row.value.collect {it.COLUMN_NAME} .join(',') }
    return existingIndexes
}

Advised indexes

second step, get the advised indexes
KEY_COLUMNS_ADVISED is already “column1, column2, column3″ format

    def getAdvisedIndexes= {library,tableSystemName,sql ->
    def advisedIndexesSQL = """
        select * from qsys2/SYSIXADV where
        TABLE_SCHEMA = '${library}' and
        SYSTEM_TABLE_NAME like '${tableSystemName}%'
        and TIMES_ADVISED > 1
        and index_type = 'RADIX'
        order by TIMES_ADVISED desc, MTI_CREATED desc
        fetch first 500 rows only with ur
        """

    rows = sql.rows(advisedIndexesSQL.toString())
    rows.collect { it.KEY_COLUMNS_ADVISED+" "+it.TIMES_ADVISED+" "+it.INDEX_TYPE}
}

It works !

last step, put everything together with an sql connection

    def dumpAdvisedAndExistingIndexes = { library,sql ->
    tables=getTableSystemNames(library,sql)
    tables.each() { tableSystemName->
        advised=getAdvisedIndexes(library,tableSystemName,sql)
        if (advised.isEmpty())
          return
        println "###### ${library}.${tableSystemName}"
        println "****************** existing indexes ****************"
        getExistingIndexes(library,tableSystemName,sql).each {println it}
        println "****************** advised indexes ****************"
        advised.each {println it}
    }

}
def as400 = "myas400"
def as400User = "myuser"
def as400Pwd = "mypwd"

def sql = Sql.newInstance("jdbc:as400://${as400};naming=system;libraries=*LIBL;date format=iso;prompt=false", as400User,as400Pwd, "com.ibm.as400.access.AS400JDBCDriver");

dumpAdvisedAndExistingIndexes('LIB1',sql )
dumpAdvisedAndExistingIndexes('LIB2',sql )
dumpAdvisedAndExistingIndexes('LIB3',sql )

A little further

Ok now I have beautifull script… what can I do with it. You can for example

• reuse these closures to compare two library, two different iseries,…
• put this kind of groovy script in a jenkins job. I did similar script to detect reserved keyword and each developper can test his own library/schema via a Parameterized groovyjenkins job.
• document your database with similar scripts or tool like schemaspy.
• reuse the same approach for the dbms like DB2 luw, oracle, mysql,…
• mix these system informatin with your naming conventions check
• …