How can I insert additional libraries to my jdbc/DB2 connection?

Question

I'm writing a little java program to write data in a AS/400 DB2 table via jdbc (db2jcc.jar version 1.0.581) and a trigger is associated to the INSERT operation. This trigger works on various tables associated with libraries different from that (jdta73p10) which contains my table (f4104).

Follows the code I use to establish connection and read data that perfectly runs.

import java.sql.*;
import com.ibm.db2.jcc.*;

public class ProvaNUMEAN13 {

public static void main(String[] args) throws SQLException, ClassNotFoundException {

    DB2DataSource dbds = new DB2DataSource();

    dbds.setDriverType(4);
    dbds.setServerName("a60d45bb");
    dbds.setPortNumber(446);
    dbds.setDatabaseName("prodgrp");
    dbds.setDescription("Prova collegamento");
    dbds.setUser("XXXXX");
    dbds.setPassword("XXXXX");

    Connection con = dbds.getConnection();

    Statement stmtNum = con.createStatement();
    stmtNum.executeQuery("select * from INTERFACCE.NUMEAN13");
    ResultSet rs = stmtNum.getResultSet();

    rs.next();  
    System.out.println("Valore numeratore: " + rs.getString("E13EAN"));
    System.out.println("Tipo numeratore: " + rs.getString("K13KEY"));

    stmtNum.close();

    Statement stmtAnag = con.createStatement();
    stmtAnag.executeQuery("select * from jdta73p10.f4101lb where IMLITM = " + "'" + args[0] + "'");
    ResultSet rsAna = stmtAnag.getResultSet();
    int idCodice = 0;
    if (!rsAna.next()) {

        System.out.println("Il codice " + args[0] + " non esiste in anagrafica!");

    } else {

        idCodice = rsAna.getInt("IMITM");
        System.out.println("idCodice per " + args[0] + ": " + Integer.toString(idCodice));
        Statement stmtQEAN = con.createStatement();
        stmtQEAN.executeQuery("select IVALN, IVCITM, IVLITM, IVDSC1 from jdta73p10.f4104 where IVXRT = 'B ' and IVALN = '8000000000000'");
        ResultSet rsQEAN = stmtQEAN.getResultSet();

        if (rsQEAN.next()) {
            System.out.println("Codice EAN per " + args[0] + " già presente: " + rsQEAN.getString("IVALN"));
            System.out.println("Valore EAN13: " + rsQEAN.getString("IVCITM"));
            System.out.println("Risultato ricerca per EAN13: " + rsQEAN.getString("IVLITM")+" - "+rsQEAN.getString("IVDSC1"));

        }
    }
}
}

Problem is when I try to execute an INSERT operation (like that below); an error is generated in AS/400 due to trigger execution.

stmtQEAN.execute("insert into jdta73p10.f4104 (IVXRT,IVITM,IVCITM,IVDSC1,IVALN,IVLITM) values ('B ','18539','8000000000000','Prodotto PROVA','8000000000000','ABABABAB')");

This is the error AS/400 side:

Message ID . . . . . . : RNQ0211 Severity . . . . . . . : 99

Message type . . . . . : Inquiry

Date sent . . . . . . : 08/01/15 Time sent . . . . . . : 10:01:31

Message . . . . : Error occurred while calling program or procedure *LIBL/PRHWRAPUSE (C G D F). Cause . . . . . : RPG procedure TRG_F4104A in program INTERFACCE/TRG_F4104A at statement 152 attempted to call program or procedure *LIBL/WS_MATERI, but was unable to access the program or procedure, the library, or a required service program. If the name is *N, the call was a bound call by procedure pointer.

Recovery . . . : Check the job log for more information on the cause of the error and contact the person responsible for program maintenance. Possible choices for replying to message . . . . . . . . . . . . . . . :

D -- Obtain RPG formatted dump.

S -- Obtain system dump.

My question is: how can I specify the other libraries that trigger need? In a old version of my tools (written in Delphi) I used the Client/Access ODBC where there was a special field where you can enter additional libraries but now I don't know how to do.

Answer 1

AS400 (iSeries) allows a comma-separated library list in the jdbc url:

jdbc:as400://someserver;naming=system;libraries=devfiles,prodfiles,sysibm,etc

naming=system indicates sql will use a library list. For example:

select * from NUMEAN13

naming=sql indicates that sql will contain library name prefixed in table references. For example:

select * from INTERFACCE.NUMEAN13

My experience is that you can't mix them. If you use library list (naming=system), then all sql must not contain library names. If you use non library list (naming=sql), then all sql must contain the library names.

Answer 2

There are several ways to handle this. The user profile has a job description and that job description has a library list. I would set up a user profile / job description combination for your JDBC connexion.

If that isn't dynamic enough, consider writing a stored procedure that you can call which will set the library list the way you need it.

Another way is probably too inflexible but I mention it as an alternative. Instead of using *LIBL for the service program, specify the library. On the one hand this makes it impossible to use the same program in test and production. On the other hand, it makes it impossible for someone to insert their own library in the middle.

If you are really stuck and no one on the IBM side is able to make changes for you, you can CALL QCMDEXC as a stored procedure and alter the library list yourself, from the client. This is the least desirable because it means tight coupling between the client and server. If the IBM team ever trues to set up a test environment (or disaster recovery environment!) you will have to change all the references in your client code and distribute the changes to everyone using it.

Answer 3

Thank you for the tips.

I also was thinking to use a stored procedure (as you suggest) but in the end I discovered that using an other IBM package, jt400.jar, is available a DataSource class with a method to set a list of AS/400 libraries that you need to use.

Below how I modified my code (that now works!) using the method setLibraries.

    import com.ibm.as400.access.*;

    ...

    AS400JDBCDataSource dbds = new AS400JDBCDataSource();

    dbds.setServerName("a60d45bb");
//  dbds.setPortNumber(446);
    dbds.setDatabaseName("prodgrp");
    dbds.setDescription("Prova collegamento a numeratore EAN13");
    dbds.setUser("XXXXX");
    dbds.setPassword("XXXXX");
    dbds.setLibraries("JCOM73P10 JDTA73P10 KLDADBFER KLDADBGAM INTERFACCE SAP");


    Connection con = dbds.getConnection();

This class has not available the method setPort but if you use the standard port (like in my case) there are no problems. If will be necessary I'll try to discover how to set it.

Answer 4

Couple solutions.

quick real time fix Copy the trigger program to QGPL (Temporary fix. A permanent fix would need to be implemented ASAP)

or

Change the JOBD of the user profile used to connect to the AS400 so it has the correct list. The user profile used for JDBC should already be locked down or it's the jdbc of a user in a group so this is a simple CHGJOBD JOBD(x) LIBL(xxx xxx xxx xxx) but the connections will have to be recycled.

or

Change the trigger program so that it has a hard coded library. I'd bet you'd need exclusive access to the file though. I'm not working (no access to iseries) so I can't verify this solution.

I recommend against changing the connection string. You'll end up having to change it for every machine that connects to the database.