DSN aliases in CLPPlus

The CLPPlus interface supports connecting to DSN aliases defined in the IBM® data server driver configuration file (db2dsdriver.cfg). Before this support, only interactive connects were allowed in the CLPPlus interface.

The IBM data server driver configuration file is an XML file that contains a list of DSN aliases and their properties. It is used to store connection details in one place. The CLPPlus interface can use that information to automatically connect to a data source instead of interactively asking for all the connection details on every connect attempt.

You can set the DB2DSDRIVER_CFG_PATH environment variable to point to the IBM data server driver configuration file. For more information about the DB2DSDRIVER_CFG_PATH environment variable, see the Miscellaneous variables topic listed in the Related reference.

If DB2DSDRIVER_CFG_PATH is not set, the CLPPlus interface searches for the IBM data server driver configuration file in the default directory location. For information about the default location of the IBM data server driver configuration file, see the Related concepts.

If a configuration file is found and it is readable, the CLPPlus interface uses it on the subsequent connect attempts.

A user who attempts a connect is asked for a database name. That database name is treated as a DSN alias and searched in the configuration file. If that DSN alias is found, the connection attributes are read and a password is requested to complete the connection. If the DSN alias is not found, then the host name, port number, user name, and password are requested interactively to go with the original database name, and all information gathered is used to attempt a connection.

You can specify whether the ExtendedIndicators property is enabled or disabled during a database connection. By default, this property is disabled. For more information, see the Related reference.

Examples

Consider the following IBM data server driver configuration file contents:

<configuration>
	<dsncollection>
		<dsn alias="S" name="SAMPLE" host="9.121.221.159" port="50000">
       <parameter name="ExtendedIndicators" value="0"/>
		</dsn>
	</dsncollection>
	<databases>
		<database name="SAMPLE" host="9.121.221.159" port="50000">
			<parameter name="UserID" value="john"/>
		</database>
	</databases>
</configuration>

The following example shows a connection established with the contents of the IBM data server driver configuration file.

First the user sets the DB2DSDRIVER_CFG_PATH environment variable.

C:\>set DB2DSDRIVER_CFG_PATH="C:\john\clpplus"

In the following example, the clpplus command is used to start a CLPPlus session and the connect command is issued to establish connection with DSN alias "S".

C:\>clpplus
CLPPlus: Version 1.1
Copyright (c) 2009, IBM CORPORATION.  All rights reserved.

SQL> connect

Enter DATABASE NAME [SAMPLE]: S
Enter ID [john] :
Enter Password: ********

Database Connection Information
-------------------------------
Hostname = 9.121.221.159
Database server = DB2/NT  SQL09071
SQL authorization ID = john
Local database alias = S
Port = 50000

SQL>

The following example shows a connection when the database name entered is not found as an alias in the IBM data server driver configuration file.

SQL> connect

Enter DATABASE NAME [SAMPLE]: sample
Enter HOSTNAME [localhost]:
Enter PORT [50000]:
Enter ID : john
Enter Password: ********

Database Connection Information
-------------------------------
Hostname = 9.121.221.159
Database server = DB2/NT  SQL09071
SQL authorization ID = john
Local database alias = SAMPLE
Port = 50000

SQL>

Since "sample" is not found as a DSN alias in the configuration file, the remaining values are requested interactively by the CLPPlus interface and then a connection attempt is made.