How can I get the Primary Keys from logical Tables in DB2?

Question

So I am working on AS400, DB2 System. I wrote a method which provides me the Primary Keys of every physical table. But on some tables the primary keys are only set on the logical table. There my method does not work.

@Override
    public ArrayList<Field> getPKS(String lib) {
        ArrayList<Field> pkList = new ArrayList<>();
        try (Connection connection = DriverManager.getConnection("jdbc:as400://" + ConnectionData.SYSTEM + ";naming=system;libraries=*" + lib + ";",
                ConnectionData.USER, ConnectionData.PASSWORD);
                            
                ResultSet rs = connection.getMetaData().getPrimaryKeys(null, connection.getSchema(), "LSAVSLA")){
                while (rs.next()) {
                    pkList.add(new Field(rs.getString("COLUMN_NAME")));
                }
           
        } catch (SQLException e) {
            // TODO Auto-generated catch block
            e.printStackTrace();
        }
        return pkList;
    
    }

For a pysical table it's working, but for a logical Table it is not. Do you have any idea how to get the primary keys from the logical table.

Answer 1

I found a solution by selecting the "DBKFLD" field from "QSYS.QADBKATR"
The SQL Query:
SELECT DBKFLD FROM QSYS.QADBKATR WHERE DBKLIB = "Your lib" AND DBKFIL = "Your table"

The Java Code:

Statement statement = connection.createStatement(ResultSet.TYPE_SCROLL_INSENSITIVE, ResultSet.CONCUR_READ_ONLY);
ResultSet resultSetQuery = statement.executeQuery("select DBKFLD from QSYS.QADBKATR where DBKLIB = '" + lib + "' and DBKFIL = '" + tablename + "'")) {
    
    ResultSetMetaData metadata = resultSetQuery.getMetaData();
                    int columnCount = metadata.getColumnCount();
        
                    while (resultSetQuery.next()) {
        
                        
                        for (int i = 1; i <= columnCount; i++) {
        
                            String pk = resultSetQuery.getString(i);
                            pk = pk.replaceAll("\\s+", "");
        
                            pkList.add(new Feld(pk));
        
                        }
                        
                    }
        
                    return pkList;