Odbc Connection String Format, Not File Search

Question

This is a kind of “double” question, which may have one answer.

I am working with Odbc Connection with AS / 400 with my connection string as follows:

driver={iSeries Access ODBC Driver}; system={0}; uid={1}; pwd={2}; DefaultLibraries=*USRLIBL; 

I can connect to the system in order.

* USRLIBL contains all the necessary libraries from the user (which is of type "only API", which has access to all user libraries).

However, when I try to access some ERP libraries, he says that they cannot be found, while others can.

So, as an extremely simple walkthrough:

 1. Open Connection - Query File 1 from Library A: OK! - Close Connection 2. Open Connection - Query File 2 from Library A: OK! - Close Connection 3. Open Connection - Query File 1 from Library B: Exception SQL0204 - in UserName type *FILE not found 

Ok, so I added to the specific library that there would be ERP files by entering the connection string as follows to check the program:

 driver={iSeries Access ODBC Driver}; system={0}; uid={1}; pwd={2}; DefaultLibraries=*USRLIBL, LibraryB; 

But then I start to get another problem (another extremely basic walkthrough)

 1. Open Connection - Query File 1 from Library A: OK! - Close Connection 2. Open Connection - Query File 2 from Library A: OK! - Close Connection 3. Open Connection - Query File 1 from Library B: OK! - Close Connection 4. Open Connection - Query File 1 from Library A again: Exception SQL0202 - in LibraryB type *FILE not found. 

So my questions are:

Why doesn't it remove the binding string odbc DefaultLibraries = * USRLIBL, doesn't return the correct libraries? (Note: I also tested this with iDB2Connection, which actually works great ... however, iDB2Connection cannot be deployed because it literally crashes the server)

Why the second pass makes an exception, it just seems that it missed * USRLIBL after reading from LibraryB even once.

Any thoughts?

Start editing:

There are actually two users: DEV and PROD

* USRLIBL gets all the necessary libraries from the environment itself, so if, upon opening the connection, it detects the localhost environment or something that is unsafe (plus a few other caveats), by default it uses the DEV credentials before creating the connection. This is why the system, uid and pwd are designated as parameters in the connection (and not just I-dont-want-to-out-out storage placeholders),

USRLIBL then retrieves the required libraries from the user API.

To clarify, the way it is configured works using the iDB2 connector, but due to the limitations of our ERP system (we believe) using it with the IIS 7 server leads to a catastrophic failure, so we are working with the ODBC connector.

End edit:

Answer 1

You can qualify the names of your tables as library.filename and not deal with problems with the list of libraries.

For more information:

ODBC Client Access: Setting Default Libraries

ODBC Connection String Keywords

---

Excerpts from the relevant parts:

With the SQL naming convention, the operating system does not search the list of libraries to find an unqualified object. If a default collection is defined, the default collection is used to resolve unqualified SQL statements.

...

Under the SYS naming convention, unqualified SQL statements go to the default collection. If there is no default collection, the current library is used. If no current library is specified, the list of libraries is used.

...

Default collection

An ODBC-defined job attribute that defines the library used to process SQL statements containing unqualified SQL names. When the default collection contains all unqualified objects except procedures, the functions and types must be in the default collection, regardless of the naming convention.

...

How can I get ODBC to search the list of libraries?

As explained above, edit the ODBC data source and set the system name for SYS. The default library must be empty or in versions older than R510, the default settings for libraries must begin with a comma so that no default collection is set (for example, ", MYLIB1, MYLIB2").

---

Try this connection string to enable system naming and not set the default library:

driver={iSeries Access ODBC Driver}; system={0}; uid={1}; pwd={2}; naming=1; DefaultLibraries=,*USRLIBL,LibraryB;

Answer 2

If someone came across this post and uses the IBM.Data.DB2.iSeries.NET data provider like me, the key point, taken from above, used naming=1 and did not indicate "default collection". Finally, I successfully used the following portion in the connection string

 LibraryList= MyLibrary1,MyLibrary2,MyLibrary3,MyLibrary4;naming=1; 

Answer 3

An alternative is to create a separate user profile for each environment. Since * USRLIBL is defined by the job description, this will also be associated with setting up a separate job description. For instance:

user: WEB job desc: List of WEB libraries: CUSTPROD, ITEMPROD, UTILITY

User: WEBTEST job desc: List of WEBTEST libraries: CUSTTEST, ITEMTEST, UTILITY

C # code does not change, except that a user test or production identifier is used for authentication.