Connecting CLPPlus to a Db2 database
Command line processor plus (CLPPlus) provides a command-line interface that you can use to connect to the BLUDB database, and to define, edit, and run statements, scripts, and commands. CLPPlus is included in the Db2 driver package .
Before you begin
The JAVA_HOME environment variable is set to the Java installation directory on your computer.
The PATH environment variable setting includes the bin subdirectory of the Java installation directory on your computer.
In a command shell on Linux operating systems, at the Windows command prompt, or in the Db2 command window on Windows operating systems,
enter the following commands.
These commands create new entries in the driver configuration file (db2dsdriver.cfg) on your computer and set the connection attributes. You need to do this step only one time.
For a connection without SSL:
db2cli writecfg add -database BLUDB -host hostname -port 50000 db2cli writecfg add -dsn alias -database BLUDB -host hostname -port 50000
For a connection with SSL:
db2cli writecfg add -database BLUDB -host hostname -port 50001 -parameter "SecurityTransportMode=SSL" db2cli writecfg add -dsn alias -database BLUDB -host hostname -port 50001
- hostname is the host name of the server
- alias is an alias you choose
To start CLPPlus with a connection to the Db2 database that uses the entries in the db2dsdriver.cfg file, enter the
- Windows environments:
- Linux® environments:
clpplus -nw userid@alias
- userid is the user ID from the connect credentials you collected beforehand
- alias is the alias that you created with the db2cli writecfg command
Issuing this command opens a CLPPlus window.
- Windows environments:
In the CLPPlus window, enter your password.
The database information is displayed, followed by an SQL prompt. Sample output follows:
Hostname = 192.0.2.0 Database server = DB2/LINUXX8664 SQL10054 SQL authorization ID = smith Local database alias = BLUDB Port = 50001 SQL>
The following examples use a short script that retrieves rows from the sample table GOSALES.BRANCH. The script file is named cities.sql and is on the local Windows computer in the C:\temp directory. The cities.sql file contains this text:
SET ECHO ON SELECT branch_code, city from GOSALES.BRANCH;
Example 1: To run the script interactively:
- Start CLPPlus with your user ID and the alias that you created in the
- Enter your password.
- At the SQL prompt, enter the following text:
Example 2: Start CLPPlus with your user ID and the alias that you created in the db2dsdriver.cfg file and run the script in one step:
clpplus user_id/password@alias @C:\temp\cities.sql
Sample output from the cities.sql script follows:
BRANCH_CODE CITY ----------- -------------------------------------------------- 6 Paris 7 Milano 9 Amsterdam 13 Hamburg 14 München 15 Kista 17 Calgary 18 Toronto 19 Boston 20 Seattle 21 Los Angeles 22 Miami 23 Lyon 24 Distrito Federal 25 Tokyo 26 Osaka City 28 Melbourne 29 Bilbao 30 Sao Paulo 31 Kuopio 32 Seoul 33 Singapore BRANCH_CODE CITY ----------- -------------------------------------------------- 34 Shanghai 35 London 36 Birmingham 37 Zürich 38 Heverlee 39 Wien 40 Geneve 29 rows were retrieved.