iDB2SQLErrorException unless connection string contains library list - why?

Question

I'm trying to update an app that writes to a table in a Mapics database living on an AS400 (IBM DB2 midrange). In doing so, I removed the library list from the connection string, and now it's throwing an exception when I try to insert.

Why does this query, which should not require a library list at all given that it's already specifying the library, throw an error when the connection string doesn't contain a library list? And more importantly, how can I fix it without needing to update the connection string?

Below is some code that demonstrates the issue.

try
{
    var connectionString = "DataSource=as400.myCompany.com" +
                           "; UserID=" + ConfigurationManager.AppSettings["UserName"] +
                           "; Password=" + ConfigurationManager.AppSettings["Password"] +
                           "; InitialCatalog=PRODTEST" +
                           //"; LibraryList=*USRLIBL, AMALIBT" + //Works... WHY?
                           //"; LibraryList=AMALIB" + //Doesn't work.
                           //"; LibraryList=*USRLIBL" +//Doesn't work.
                           "; DataCompression=True; CheckConnectionOnOpen=True; Naming = System;";
    const string sql = "insert into AMFLIBT.SHIPVF(VIACD, VIADS, MDATE) values(?, ?, ?) with NC";
    var parameters = new List<IBM.Data.DB2.iSeries.iDB2Parameter>
    {
        new IBM.Data.DB2.iSeries.iDB2Parameter("NameDoesNotMatter","AAA"),
        new IBM.Data.DB2.iSeries.iDB2Parameter("NameDoesNotMatter","TEST555"),
        new IBM.Data.DB2.iSeries.iDB2Parameter("NameDoesNotMatter", 1240129),
    };
    var conn = new IBM.Data.DB2.iSeries.iDB2Connection(connectionString);
    conn.Open();
    var transaction = conn.BeginTransaction();
    var command = new IBM.Data.DB2.iSeries.iDB2Command(sql, conn, transaction);
    command.SetParameters(parameters);
    var rc = command.ExecuteNonQuery();
    command.Dispose();
}
catch (IBM.Data.DB2.iSeries.iDB2SQLErrorException e)
{
    //e.ToString() returns "SQL0901 SQL system error.".
    Console.WriteLine(e);
    throw;
}

UPDATE: Tried running it then checking the job log, and this is the resultant messages:

SQL system error.
Trigger error.
153 - CALL PGM(QMHSNDPM) /* The CALL command contains paramsters */
Error found on CALL command.
Program AMZTRG3R in library %LIBL not found.
122 - CALL PGM(AMZTRGRR) /* The CALL command contains paramsters */
112 - CHDTAARA DTAARA(ZTRIGCOMM (1 10)) VALUE('*ALL     ')
Data area ZTRIGCOMM created in library QTEMP.
110 CRTDTAARA DTAARA(QTEMP/ZTRIGCOMM) TYPE (*CHAR) LEN(1024)
Data area ZTRIGCOMM created in *LIBL not found.
108 - RTVDTAARA DTAARA(ZTRIGCOMM (1 10)) RTNVAR(&TRGSRC)
107 - RTVDTAARA DTAARA(*LDA *ALL) RTNVAR(&LDA)

So when a table is updated, a trigger is trying to call an RPG program which uses a library list... And there's no way for me to delete that trigger... Is there no way around this other than keeping a library list in the connection string?

Answer 1

Without knowing exactly what the exception is, I would guess that there's a trigger attached to the table and either the trigger itself or some code it calls is using the library list.

Running the code in debug and checking the IBM i job's joblog after the exception should provide additional information.

EDIT

Yup, looks like it's a trigger error... question becomes what I do, then... I can't delete the trigger. Is there no way around adding a library list to the connection string?

Short answer, you'll need the correct library list set before you do the insert so that the trigger works properly. You can do that on the connection string or for example by adding a call to set it before hand.

Question is why do you think you don't want to do so in the connection string? (your answer is possibly a new SO question)