Java access multiple member Physical file in iSeries

Note: In Java this is a very good replacement for business logic developed using library QTEMP to have session specific data. Accessing QTEMP in Java creates headache as the java JVM run under a separate Job.

Although the IBM i database supports physical files with multiple members, SQL (and consequently JDBC) is designed to access only the first member (which is usually the member with the same name as the file/table). You can do this by creating an SQL Alias. Suppose that you have a physical file MYLIB/SALES with twelve members (one member for each month of the year). To access JANUARY, you must first create an alias with the following SQL command:

CREATE ALIAS MYLIB/SALES_JANUARY FOR MYLIB/SALES(JANUARY)

Sample Java program to access multiple member Physical file

package com.as400samplecode;

import java.io.FileInputStream;
import java.sql.Connection;
import java.sql.DriverManager;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.util.Properties;

public class As400MultiFormatJava {

    static Properties props;

    public static void main(String[] args)
    {
        String db2FileName = "";
        String db2MemberName = "";

        if (args.length < 2)
        {
            As400MultiFormatJava as400MultiFormatJava = new As400MultiFormatJava();
            System.err.println("Usage: java "+ as400MultiFormatJava.getClass().getName()+
            " Db2_File_Name Db2_File_Member_Name");
            System.exit(1);
        }

        db2FileName = args[0].trim();
        db2MemberName = args[1].trim();
        AccessMultiFormatFile(db2FileName, db2MemberName);

    }

    private static void  AccessMultiFormatFile(String db2FileName, String db2MemberName){

        try {

            props = new Properties();
            props.load(new FileInputStream("properties/dataconnection.properties"));

            String DRIVER = "com.ibm.as400.access.AS400JDBCDriver";        
            String URL = "jdbc:as400://" + props.getProperty("local_system").trim() + ";naming=system;errors=full";
            Connection conn = null; 
            String sql = null;

            //Connect to iSeries                                         
            Class.forName(DRIVER);                                       
            conn = DriverManager.getConnection(URL,props.getProperty("userId").trim(),props.getProperty("password").trim());   

            //Create alias if other than the default member is accessed
            if(db2MemberName.equalsIgnoreCase("") == false){
                sql = "Create ALIAS QTEMP/" + db2FileName + "_" + db2MemberName +
                " for " + db2FileName + "(" + db2MemberName + ")";   
                System.out.println("SQL: " + sql);
                PreparedStatement stmt = conn.prepareStatement(sql);
                stmt.executeUpdate();
                sql = "Select * from QTEMP/" + db2FileName + "_" + db2MemberName;
            }
            else {
                sql = "Select * from " + db2FileName;
            }
            System.out.println("SQL: " + sql);

            //Database 
            PreparedStatement stmt = conn.prepareStatement(sql);
            ResultSet rs = stmt.executeQuery();

            while (rs.next()) {

                System.out.println("Order#: ----> " + rs.getString("X1ORD"));
                System.out.println("Order Date: " + rs.getString("X1ORDT"));
                System.out.println("Customer: " + rs.getString("X1CUST"));
                System.out.println("PO Number: " + rs.getString("X1PO"));
                System.out.println("Order Total: " + rs.getString("X1OTOT"));
            }

            rs.close();
            stmt.close();
            conn.close();


        }
        catch (Exception e) {
            System.out.println(e);
        }

    }

}