This article was previously published under Q145943
Microsoft SNA Server 2.11 includes the StarSQL ODBC/DRDA drivers forWindows 3.x and Windows NT client computers. The drivers allow applicationsthat are designed to use the ODBC interface and SQL access to databases onIBM host computers and AS/400 without a host-based database gateway.
NOTE: SNA Server 2.11 and SNA Server 2.11 Service Pack 1 include a singlelicense of the StarSQL ODBC/DRDA driver. Therefore, the driver can only beused on a single client computer. If you want to install the driver onmultiple client computers, make sure you have a valid SNA Server ClientAccess License (CAL). You can purchase additional licenses for theODBC/DRDA driver from StarWare at (510) 704-2000.
The SNA Server 2.11 includes the following informational ODBC files:
The configuration procedure of ODBC connection to an AS/400 varies. Itdepends on the StarSQL driver and OS/400 version on the AS/400. Thefollowing information is in AS400.TXT:
One Time Setup for StarSQL to AS/400
The following instructions detail the latest setup procedure for StarSQL onan IBM AS/400. On v3r05 & earlier all steps must be carried out by a userwith QSECOFR authority or the equivalent. Please read all instructionscarefully and take special note of Isolation Level considerations and ofdifferences between installation procedures for OS/400 v3r1 and OS/400v3r05 & earlier.
StarSQL v1.05 and later:
Upon successful completion the user should be able to access all physicalfiles (journaled and non-journaled) on an AS/400 running any version ofOS/400.
Upon successful completion the user should be able to access all physicalfiles (journaled and non-journaled) on an AS/400 running OS/400 v3r1.
NOTE: For access to journaled physical files on OS/400 v3r05 and earlier,follow StarSQL instructions for creating a datasource which usescollections (see "CREATING COLLECTION ON THE AS/400" IN "HINTS.TXT").
Create a new library (using a 5250 terminal session) where the SQL packages used by StarSQL will be created. This library does not need to be a SQL collection, but it should be accessible to all StarSQL users.
Create a new library STARSQL:
Determine the RDB (Relational Database) name of the AS/400, creating an entry if one does not already exist. This name will be used in Step 2for the "Database Server Name" in StarSQL data source setup.
From the AS/400 command line, work with Relational Database Directory Entries by typing:
Look for an entry with the value of Remote Location set to *LOCAL. If such an entry does not exist, create it with the 1=Add option. A suggested convention is to use the same name as the AS/400 system name.
Create a new StarSQL data source through Control Panel | ODBC Administrator and specify:
Database Server Name= <Database Server Name> (specified in Step 2) Catalog Qualifier = QSYS (v3r01 and earlier) or QSYS2 (v3r1) Package Collection = <library> (created in Step 1)
Using QSYS or QSYS2 in the Catalog Qualifier field will cause the StarSQL driver to return a list of all physical files on the AS/400. If there are a large number of tables on the AS/400 you may want to use the filtering capability available in StarSQL version 1.04.19.19 or later. This will allow users to filter the table and view names returned when you retrieve a list of available tables (views and physical files) to what is available in a single AS/400 collection or library.
To filter table and view names returned from the list, change the Catalog Qualifier to QSYS.library_name (v3r05 & before) or QSYS2.library_name (v3r1).
MSSNA 2.11 RELEASE CUSTOMERS:
The above filtering option is not available with the version of StarSQL that is included in the Microsoft SNA 2.11 Release.
Connect to the AS/400 using the data source specified in Step 2. When you connect to the AS/400, SQL packages are created in the library specified in the data source. These packages are named QSYS and either SWRC0000, SWRU0000 or SWNC0000 (depending on the isolation level used). Creation of the packages may take several minutes.
Grant *USE authority to PUBLIC on the packages that were created Step 4 using one of the following methods:
From the AS/400 command line:
WRKLIB STARSQL 12 (work with objects) 2 (edit authority on each object one at a time) change PUBLIC *EXCLUDE to PUBLIC *USE
Use the GRTOBJAUT command (using a 5250 terminal session) to grant *USE authority for the library and execute authority for the packages to all StarSQL users.
Use SQL/400 or SQL commands sent from an ODBC-enabled application:
GRANT EXECUTE ON PACKAGE STARSQL/QSYS, STARSQL/SWRC0000 TO PUBLIC
ACCESSING NON-JOURNALED PHYSICAL FILES ON OS/400 V3R1 (PRE-STARSQL V1.05):
To access non-journaled physical files on OS/400 v3r1, editWINDOWS\ODBC.INI (16 bit) or NT Registry (32 bit). Add the following to theStarSQL data source description:
For WINDOWS or WFW (16bit):
[STARSQL]Driver=C:\WINDOWS\SYSTEM\swodbc16.dllDescription=example for v3r1Server=RDBELLECatQual=QSYS2PkgColId=STARSQLNetlib=SWWAPPC.DLLReadOnly=NoLocalLU=SISYPHUSRemoteLU=ELLEModeName=QPCSUPPAutoTypDefNam=QTDSQL400AutoTypDefOvr=37,0,0UID=PETERIsolationLevel=0
For Windows NT (32 bit):
The NT registry is found in WINNT35\SYSTEM32\REGEDT32.EXE. To edit datasources, go to HKEY_CURRENT_USER. On Local Machine, go to SOFTWARE | ODBC | ODBC.INI | <data source name>. Highlight the appropriate StarSQL data source name. To add IsolationLevel:
Go to EDIT | Add Value. Enter "IsolationLevel" with a Data Type of REG_SZ. Select OK, then Enter, and add the appropriate number (0) for the Value.
The third-party products discussed here are manufactured by vendorsindependent of Microsoft; we make no warranty, implied or otherwise,regarding these products' performance or reliability.