Question & Answer
Question
How can I use JDBC to access physical file members other than the default?
Answer
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). If you are using Toolbox JDBC to access files and members created outside of SQL, you may need to trick SQL into using members other than the first.
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)
Now, you can access the individual member JANUARY by using a select command such as SELECT * FROM SALES_JANUARY. However, if you rename a member of MYLIB/SALES, the alias will no longer point to it. You can either create a new alias for the member you renamed or create a new member with the old member name.
// Here is the exact CREATE ALIAS command string.
String aliasCommand = "CREATE ALIAS MYLIB/SALES_JANUARY " + "FOR MYLIB/SALES(JANUARY) ";
// Create a Statement and issue the CREATE ALIAS SQL command.
Statement statement = connection.createStatement();
statement.executeUpdate(aliasCommand);
[{"Business Unit":{"code":"BU058","label":"IBM Infrastructure w\/TPS"},"Product":{"code":"SWG60","label":"IBM i"},"Component":"","Platform":[{"code":"PF012","label":"IBM i"}],"Version":"All Versions","Edition":"","Line of Business":{"code":"LOB57","label":"Power"}}]
Product Synonym
IBM i;AS400
Was this topic helpful?
Document Information
Modified date:
17 March 2020
UID
ibm11118025