Setting a default SQL library and library list on a ODBC connection for a IBMi DB2 connection in Windows and Linux

UPDATE: In a JDBC connection using JTOpen 400, library lists are only used when the connection properties is set to naming=system;libraries=MYLIB1,MYLIB AND there’s no default “Schema=” or default schema in the Database URL (i.e. jdbc:as400:MYIBMI/DEFAULTSCHEMA). I’m unsure if this is the same way in ODBC.


ODBC allows you to set a Default SQL Schema or Library and a Library list. This allows you to point your application at different IBMi DB2 libraries.  This could be used so you can have a development, qa, and production libraries and have your application change what data it uses based on your odbc settings.  This also allows you to not need to fully qualify your table names such as SELECT * FROM MYLIB.MYTABLE would be come just SELECT * FROM MYTABLE .  They way this happens is  the default library will by tried first {DefaultLibrary}.MYTABLE and if it doesn’t find it in there it will try the libraries in the library list {library_list…}.MYTABLE [MYLIB1.MYTABLE, MYLIB2.MYTABLE …] .

Below is how to set this up in Windows and in Linux environments.  Make sure you’ve done the steps in this guide to get the odbc driver: https://godzillai5.wordpress.com/2016/09/02/execute-sql-remotely-on-an-ibm-i-via-phps-pdo-odbc-from-a-windows-linux-or-mac/ (mainly this guide http://www-01.ibm.com/support/docview.wss?uid=nas8N1010355)

Windows (ODBC Administration)

Its under the server tab of ODBC Administration tools in windows.  This tool can be found at

C:\Windows\System32\odbcad32.exe – 32 bit
C:\Windows\SysWOW64\odbcad32.exe – 64 bit

You then select your data-source name, configure the data-source, go to the server tab, enter your Default SQL Schema or Library, and the Library list for this connection.

odbc-default-library-and-library-list-ibmi-db2

Linux (unixODBC Manager)

edit the /etc/odbc.ini. 

1) add your default library to DefaultPkgLibrary

2) add your library list to DefaultLibraries 

[iSeriesDSN]
Description = iSeries Access ODBC Driver DSN for iSeries
Driver = iSeries Access ODBC Driver
System = iSeriesSystemName
UserID =
Password =
Naming = 0
DefaultLibraries = MYLIB,MYLIB2,MYLIB3
Database =
ConnectionType = 0
CommitMode = 2
ExtendedDynamic = 0
DefaultPkgLibrary = MYLIB
DefaultPackage = A/DEFAULT(IBM),2,0,1,0,512
AllowDataCompression = 1
LibraryView = 0
AllowUnsupportedChar = 0
ForceTranslation = 0
Trace = 0

JDBC

Below is the JDBC connection string with system naming on and the library list to use.  Default schema can’t be specified or it won’t use the libraries.

jdbc:as400:MYIBMI/;naming=system;libraries=MYLIB1,MYLIB

Work-arounds

1. Change the library list in the job description (JOBD) for the ODBC connection. Do a WRKACTJOB and look for QZDASOINIT jobs handles connections and see what JOBD they are using and change it to include the libraries you need.
2. Call the Stored procedure QCMDEXC to run ADDLIBLE.

CALL QSYS . QCMDEXC (‘ADDLIBLE MYLIB’ ) ; 

This will add the library to the library list entries.

Advertisements

2 thoughts on “Setting a default SQL library and library list on a ODBC connection for a IBMi DB2 connection in Windows and Linux

  1. Pingback: Execute SQL remotely on an IBM i via PHP’s PDO ODBC from a Windows, Linux or Mac | GodzillaI5: PHP on IBM i (ISeries, AS400 and System i)

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out /  Change )

Google photo

You are commenting using your Google account. Log Out /  Change )

Twitter picture

You are commenting using your Twitter account. Log Out /  Change )

Facebook photo

You are commenting using your Facebook account. Log Out /  Change )

Connecting to %s