AS/400 connection strings

IBM Client Access OLE DB provider

Standard

Provider=IBMDA400;Data Source=MY_SYSTEM_NAME;User Id=myUsername;Password=myPassword;

Where MY_SYSTEM_NAME is the name given to the system connection in OperationsNavigator

Specifying cellection

Provider=IBMDA400;Data Source=MY_SYSTEM_NAME;User Id=myUsername;Password=myPassword;Default Collection=MY_LIBRARY;

Where MY_SYSTEM_NAME is the name given to the System Connection, and MY_LIBRARY is the name given to the library in iSeries Navigator.

IBM i Access ODBC driver

Standard

Driver={IBM i Access ODBC Driver};System=mySystem;Uid=myUser;Pwd=myPassword;

Variant for reserved error (-7778)

Try this if you encounter a "runtime error 3000; reserved error (-7778); there is no message for this error" message.

Driver={IBM i Access ODBC Driver};System=mySystem;Uid=myUser;Pwd=myPassword;MGDSN=0;

Read-only connection

Enbales SELECT statements only. No UPDATE etc.

Driver={IBM i Access ODBC Driver};System=mySystem;UID=myUser;PWD=myPassword;CONNTYPE=2;

Default is CONNTYPE=0 meaning all SQL statements allowed. CONNTYPE=1 means SELECT and CALL statements is allowed.

Disable compression

Driver={IBM i Access ODBC Driver};System=mySystem;UID=myUser;PWD=myPassword;COMPRESSION=0;

Default is compression=1 meaning compression is enabled.

Disable SELECT prefetching

Use this connection string to disable prefetching for SELECT statements.

Driver={IBM i Access ODBC Driver};System=mySystem;UID=myUser;PWD=myPassword;PREFETCH=0;

Default behaviour is that prefetch is enabled (PREFETCH=1) which will increase performance for SELECT statements when accessing the initial rows.

Specify sorting language (collation)

Example using spanish (ESP) language for sorting.

Driver={IBM i Access ODBC Driver};System=mySystem;UID=myUser;PWD=myPassword;SORTTYPE=2;LANGUAGEID=ESP;

Language codes: AFR, ARA, BEL, BGR, CAT, CHS, CHT, CSY, DAN, DES, DEU, ELL, ENA, ENB, ENG, ENP, ENU, ESP, EST, FAR, FIN, FRA, FRB, FRC, FRS, GAE, HEB, HRV, HUN, ISL, ITA, ITS, JPN, KOR, LAO, LVA, LTU, MKD, NLB, NLD, NON, NOR, PLK, PTB, PTG, RMS, ROM, RUS, SKY, SLO, SQI, SRB, SRL, SVE, THA, TRK, UKR, URD, VIE

Case sensitive sorting (collation)

Driver={IBM i Access ODBC Driver};System=mySystem;UID=myUser;PWD=myPassword;SORTTYPE=2;LANGUAGEID=ESP;SORTWEIGHT=1;

Default SORTWEIGHT value is 0 meaning case insensitive sorting.

IBM Client Access ODBC driver

Standard

Driver={Client Access ODBC Driver (32-bit)};System=my_system_name;Uid=myUsername;Pwd=myPassword;

Variant

Try this if you encounter a "runtime error 3000; reserved error (-7778); there is no message for this error" message.

Driver={Client Access ODBC Driver (32-bit)};System=my_system_name;Uid=myUsername;Pwd=myPassword;MGDSN=0;

IBM iSeries Access ODBC driver

Standard

This driver is newer than the one above.

Driver={iSeries Access ODBC Driver};System=my_system_name;Uid=myUsername;Pwd=myPassword;

Variant

Try this if you encounter a "runtime error 3000; reserved error (-7778); there is no message for this error" message.

Driver={iSeries Access ODBC Driver};System=my_system_name;Uid=myUsername;Pwd=myPassword;MGDSN=0;