Demo - How to have two connections for a database and switch between them

0 Likes

Problem:

This demo illustrates how to access multiple databases from a single cobol program. The databases are DB2 and Sql*Server. The concept is to use named connections and the SET CONNECTION statement. At the beginning of your program a connection to each database is established. Each CONNECT statement specifies the AS clause which associates a user-defined name to the database connection. When access to a specific database is required the SET CONNECTION statement is issued specifying the name associated with the desired database. All subsequent SQL will access this database until another SET CONNECTION statement is issued.

Resolution:

BUILD W/RELEASE:     Net Express 3.1

The program will prompt you to enter your db2 datasource name, your db2 userid and password, your sql server datasource name and your sql server userid and password. The format for the userid and password is userid.password. If no password is required than merely enter your userid. The program opens a cursor on the SQL Server discounts table, fetches a row and, based on the value for discount-stor-id, retrieves a row from the DB2 db2disc table.  The program displays a fetch count, store id, and discount. If the store id is not specified, it will display the message 'no store id specified'. If an associated store id record is found in the db2 table, then it will also display the associated name.

SOURCE FILES:

=========

Program Files     Description

----------------     -----------------------------------------------------------

sqlconn.cbl         The cobol program that access two databases.

Copy Files:

----------------     -----------------------------------------------------------

discounts.cpy

db2disc.cpy

sqlca.cpy

REQUIREMENTS:

==========

This demo requires Sql Server pubs database and the creation of db2disc table in the db2 sample database. There is a db2 script, setup.ddl, that must be executed before running the sample. This script can be changed to connect to a database other than sample. The script can be imported using the Db2 script center, modified if necessary and then executed.

This demo also requires the establishment of two datasources, one which points to a db2 server and database and the other which points to sql server pubs database.

OPERATION:

=======

- Unzip the db2conn.zip file.

- Import the setup.ddl script and execute it to create the db2disc table and insert one row into the table for stor-id '8042' with associated name of 'Smith'.

- Establish the two datasources, one to point to SQL SERVER pubs database and one to point to DB2 database that contains the db2disc table.

- Bring up Net Express and open the application conndb2. Rebuild the application and then run or animate it.

The program will prompt you to enter your db2 datasource name, and then your db2 userid and password. The format for the userid and password is userid.password. If no password is required than merely enter your userid. A message will be displayed if you sucessfully connect to db2   Connected to db2, connection name is DB1. The program will prompt you to enter your SQL Server datasource, and then your userid and password. The format is also userid.password. Once again if password is required merely enter your userid. A message will be displayed   Connected to SQL Server, connection name is MS1. The program displays a fetch count, store id, and discount. If the store id is not specified, it will display the message  'no store id specified'. If an associated store id record is found in the db2 table, then the associated name will also be displayed. Since only one row was entered into the db2disc table, the only name that will appear is 'Smith' and it will be associated with store id '8042'.

NOTES:

====

If the db2 datasource is set up using the Data Direct DB2 driver then you must bind six Net Express files to your db2 database before using the DB2 driver. The files are located in NETEXPRESS\ODBC\db2_bind You can do this using the db2 command line processor:

1. connect to your database by issuing the following command where database is the name of your db2 database, username and password are your valid DB2 logon parameters

     CONNECT TO database USER username USING password

2. bind using the following command where dd indicates the drive letter where the NetExpress bnd files are located and filename is the name of the bnd file, including the bnd extension. This command will have to be executed for each bnd file in located in the dd:\NETEXPRESS\ODBC\db2_bind directory.

     BIND dd:\NETEXPRESS\ODBC\db2_bind\filename

==========================================================

Keywords: Example, sample, demo, demonstration, SQL, conn2db.zip

demo.ex

demo.ne

Old KB# 4349
Comment List
Anonymous
Related Discussions
Recommended