StarQuest Technical Documents

Using a DSN-less Connection with StarSQL

Last Update: 11 January 2017
Product: StarSQL
Versions: 5.x & 6.x
Article ID: SQV00SQ036

Abstract

This technical document describes how to create a StarSQL connection to DB2 that does not use a data source name (DSN). This type of connection is known as a DSN-less connection. The use of DSN-less connections eliminates the need to create ODBC DSNs with ODBC Administrator in order to access the database server. 

Solution

A DSN-less connection string must contain all of the required connection parameters in order for StarSQL to connect to the DB2 system. The parameters that are required for TCP/IP connections differ from those required for SNA connections. The following table lists the required parameters for TCP/IP and SNA connections, respectively. Sample connection strings for both types of connections are provided later in the document.

The examples provided with this solution work with applications that use Microsoft RDO, ADO/RDS, and ODBC.NET data access technologies. 

Required Parameters for TCP/IP Connections

Parameter Name	Description
Driver	This value must be set to either: StarSQL 32-bit (Windows): {StarSQL 32} StarSQL 64-bit (Windows): {StarSQL (64-bit)} StarSQL 32-bit (UNIX): {StarSQL} StarSQL 64-bit (UNIX): {StarSQL64}
Server	This value corresponds to the Database Server Name in a StarSQL DSN. On DB2 for z/OS, the database server name is the Location Name in the Distributed Data Facility (DDF). You can get the Location Name by examining the DDF Communication record on the host. On DB2 for i, the database server name is the RDB or Relational Database Name. To find or create the relational database name, use the AS/400 command WRKRDBIRE on the host. On DB2 UDB for Linux, UNIX, and Windows, the database server name is the Database Name. On DB2 for VSE & VM, the database server name is the Global Resource Name.
HostName	The host name refers to the TCP/IP server, which is the same as the host name of the database server. Use either a TCP/IP host name or an IP address in dotted decimal notation.
UID	Enter your username or authorization identifier for connecting to the database.
PWD	Enter your password for connecting to the database.

Required Parameters for SNA Connections (StarSQL 5.x & earlier)

Parameter Name	Description
Driver	This value must be set to {StarSQL 32}. Note: StarSQL (64-bit) does not currently support the SNA networking protocol.
Server	This value corresponds to the Database Server Name in a StarSQL DSN. On DB2 for z/OS , the database server name is the Location Name in the Distributed Data Facility (DDF). You can get this by examining the DDF Communication record on the host. On DB2 for i, the database server name is the RDB or Relational Database Name. To find or create the relational database name, use the AS/400 command WRKRDBIRE on the host. On DB2 UDB for Linux, UNIX, and Windows, the database server name is the Database Name. On DB2 for VSE & VM the database server name is the Global Resource Name.
NetLib	This value must be set to SWAPPCNT.DLL.
LocalLU	Specify the name of the local LU.
RemoteLU	Specify the name of the remote LU.
ModeName	Specify the mode name for the SNA session.
UID	Enter your username or authorization identifier for connecting to the database.
PWD	Enter your password for connecting to the database.

Optional Parameters for Both Types of Connections

There are many optional parameters that may be specified in a DSN-less connection string. To view a complete list of the available parameters and the valid values on Windows, create a StarSQL System or User DSN using the ODBC Data Source Administrator and review the Data Source Entry list on the Expert Page panel. With this panel active, click Help to display the topic that describes the optional parameters. On UNIX, you can view the complete list of parameters in the sample odbc.ini file in $STARSQL/etc.

Following are some of the most commonly-used optional parameters:

Parameter Name	Description
NetLib	Set this value to SWTCP32.DLL (for TCP/IP) or SQSSL.DLL (for SSL). The default is SWTCP32.DLL.
PkgColId	Enter the location on the DB2 host of the packages required by StarSQL to execute Dynamic SQL. The default is the name of the UserID being used for the connection; we recommend specifying STARSQL (assuming that the DB2 Admin has bound packages in collection STARSQL with the StarAdmin utility). Note: the parameter name PkgColId is an abbreviation for Package Collection ID, and contains a lower case letter L, followed by an upper case letter I.
Port	This parameter is used for TCP/IP and SSL connections. The default is 446which is the typical value for a DB2 for z/OS or DB2 for i host; note that the typical value for a DB2 for LUW server is 50000. Set this value if your DB2 system listens for requests on a port other than 446. The typical value for an SSL connection is 448.
CatFilters	For the Catalog Filters you can select one or more (up to ten) qualifiers to restrict the amount of data retrieved by ODBC. Each filter must be within single quotes and separated by a comma, such as 'QAUSER','PRODUSER'.

Parameters that are not specified in the connection string use default values. Refer to the StarSQL Help for the default values that are used for the optional parameters.

DSN-less Connection String Examples

To construct a DSN-less connection string, create a string that contains the required and optional parameters with their appropriate values, each in the format of parameter=value. Each parameter=value pair must be separated by a semi-colon as shown in the below examples.

TCP/IP Example

The following strings will work with RDO, ADO/RDS, and ODBC.NET.  Use the Provider parameter only with ADO or RDS connections.

StarSQL 32-bit:

"Driver={StarSQL 32};Provider=MSDASQL;Server=DB2PROD;NetLib=SWTCP32.DLL;
HostName=db2host.company.com;Port=7446;PkgColID=STARSQL53;UID=db2user;PWD=secret"

StarSQL 64-bit:

"Driver={StarSQL (64-bit)};Provider=MSDASQL;Server=DB2PROD;NetLib=SWTCP32.DLL;
HostName=db2host.company.com;Port=7446;PkgColID=STARSQL53;UID=db2user;PWD=secret"

SNA Example

The following string will work with RDO, ADO/RDS, and ODBC.NET.  Use the Provider parameter only with ADO or RDS connections. Note: the SNA networking protocol is supported only by 32-bit StarSQL v5.x.

"Driver={StarSQL 32};Provider=MSDASQL;Server=DB2PROD;NetLib=SWAPPCNT.DLL;LocalLU=MYSYS; RemoteLU=DB2PROD;ModeName=LU62STAR;CatFilters='QAUSER';UID=db2user;PWD=secret"