DZone Snippets: DB2 code

Configure MSSQL Linked Server to DB2 (via ODBC System DSN)

wjchay (Chay Weei Jye) — Mon, 26 Mar 2007 08:49:53 GMT

1. Install DB2 on MSSQL machine
2. Start Configuration Assistant. Add a new database mapping to the desired target DB2 database. Select option to create a System DSN along the way.
3. Start Microsoft’s ODBC Data Source Administrator. There should be a System DSN created from the previous step. Configure it with the userid/password for the target DB2 database.
4. Create linked server in MSSQL: EXEC sp_addlinkedserver @server = 'TMON', @srvproduct = '', @provider = 'MSDASQL', @datasrc = 'TMON'
5. Map access to linked server: EXEC sp_addlinkedsrvlogin 'TMON', 'false', NULL, 'db2admin', 'db2admin'
6. 2 ways to test the link: SELECT * FROM TMON..DB2ADMIN.USERS -- use uppercase for server, schema, table names SELECT * from OPENQUERY (TMON,'select * from users')

Notes:
* TMON is the remote DB2 database, also used as the DSN name.
* Remote DB2 server uses access id: db2admin and password: db2admin
* USERS is a table in the remote DB2 database

DB2 Manual Installation on Linux

wjchay (Chay Weei Jye) — Fri, 16 Mar 2007 11:50:43 GMT

Install

* Login as root.
* Create groups:
o groupadd -g 999 db2iadm1
o groupadd -g 998 db2fadm1
o groupadd -g 997 dasadm1

* Create users for each group:
o useradd -u 1004 -g db2iadm1 -m -d /home/db2inst1 db2inst1
o useradd -u 1003 -g db2fadm1 -m -d /home/db2fenc1 db2fenc1
o useradd -u 1002 -g dasadm1 -m -d /home/dasusr1 dasusr1

* Set password for each users created:
o passwd db2inst1
o passwd db2fenc1
o passwd dasusr1

* cd to installation file directory:
o Example: /tmp/db2/exp/disk1

* Run installation script:
o ./db2_install.sh

Post Install

* Login as root.
* Install license (example):
o /opt/ibm/db2/V9.1/adm/db2licm –a /tmp/db2/exp/disk1/db2/license/db2exp_uw.lic

Create the DB2 Administration Server (DAS)

* Login as root.
* Create DAS with dasusr1
o /opt/ibm/db2/V9.1/instance/dascrt -u dasusr1

* Login as dasuser1
o Start the DAS: db2admin start

* Optional: to enable autostarting of the DAS upon reboot
o /opt/ibm/db2/V9.1/instance/dascrt/dasauto –on

Create DB2 instance

* Login as root.
* Create instance with users db2fenc1 and db2inst1:
o /opt/ibm/db2/V9.1/instance/db2icrt -a server -u db2fenc1 db2inst1
* Optional: enable autostarting of the db2inst1 instance
o /opt/ibm/db2/V9.1/instance/db2iauto –on db2inst1

Update environment variables

* Example:
* Login as db2inst1
* edit .bash_profile
o vi /home/db2inst1/.bash_profile
o insert the following line at the end of the file – “. /home/db2inst1/sqllib/db2profile”

* Do the same for dasusr1, using its corresponding directory.

Database service

* Login as root:
* Add new service entry:
o vi etc/services
o insert this line “DB2_TMINST 50000/tcp” at the end of the file

Verification

* Login as db2inst1
* List installed DB2 products and features: db2ls
* Display the default instance: db2 get instance
o Result: The current database manager instance is: db2inst1
* Start the database instance: db2start
o Result: SQL1063N DB2START processing was successful.
* Stop the database instance: db2stop
o Result: SQL1064N DB2STOP processing was successful.

DB2 Federation

wjchay (Chay Weei Jye) — Fri, 16 Mar 2007 11:46:43 GMT

Server A configuration:
OS: WinXP
Instance name: TMINST
Instance user and password: db2admin/db2admin
Required nickname for remote table: DB2ADMIN.R_CUSTOMERS

Server B configuration:
OS: Linux
IP: 10.60.100.21
DB2 service port: 50000
Instance user and password: db2inst1/db2inst1
Database name: TM_ON
Table name to be linked: CUSTOMERS

CLP Commands @Server A:



db2set DB2INSTDEF=TMINST

SET DB2INSTANCE=TMINST

db2 UPDATE DATABASE MANAGER CONFIGURATION USING FEDERATED YES IMMEDIATE

db2 CATALOG TCPIP NODE RTMINST REMOTE 10.60.100.21 SERVER 50000 REMOTE_INSTANCE db2inst1 OSTYPE Linux

db2 CATALOG DATABASE TM_ON AS RTM_ON AT NODE RTMINST 

db2start

db2 CONNECT TO TM_ON USER db2admin USING db2admin

db2 CREATE WRAPPER DRDA

db2 "CREATE SERVER RTM_ON TYPE DB2/UDB VERSION '9.1' WRAPPER DRDA AUTHORIZATION \"db2inst1\" PASSWORD \"db2inst1\" OPTIONS( ADD DBNAME 'RTM_ON')"

db2 CREATE USER MAPPING FOR DB2ADMIN SERVER RTM_ON OPTIONS ( ADD REMOTE_AUTHID 'db2inst1', ADD  REMOTE_PASSWORD 'db2inst1') 

db2 CREATE NICKNAME DB2ADMIN.R_CUSTOMERS FOR RTM_ON.DB2INST1.CUSTOMERS

db2 SELECT * FROM R_CUSTOMERS

DB2 enable/disable constraints

wjchay (Chay Weei Jye) — Fri, 16 Mar 2007 11:44:32 GMT



-- All FKs will be enabled/disabled, PKs not affected

-- usage: 

--        enforce -> db2 call SET_CONSTRAINTS('YES')

--        not enforced -> db2 call SET_CONSTRAINTS('NO')



-- To check if referential integrity check is enforced:

-- select * from syscat.tabconst where tabschema='DB2ADMIN' and TYPE='F'



drop procedure SET_CONSTRAINTS!

create procedure SET_CONSTRAINTS (ENFORCE VARCHAR(3))

LANGUAGE SQL

BEGIN

declare v_tabname varchar(128);

declare v_constname varchar(128);

declare v_rows integer;

declare v_alter_table_sql varchar(256);

declare tmp_cursor cursor for SELECT tabname, constname FROM syscat.references WHERE TABSCHEMA = CURRENT SCHEMA ORDER BY CONSTNAME,TABNAME, REFTABNAME;

select count(*) into v_rows from syscat.references WHERE TABSCHEMA = CURRENT SCHEMA;

open tmp_cursor;

while (v_rows > 0) DO

FETCH tmp_cursor INTO v_tabname, v_constname;

SET v_alter_table_sql = 'alter table ' || current SCHEMA || '.' || v_tabname ||  ' alter foreign key ' || v_constname;

IF (ENFORCE = 'NO') THEN 

    SET v_alter_table_sql = v_alter_table_sql || ' NOT ENFORCED';

ELSE

    SET v_alter_table_sql = v_alter_table_sql || ' ENFORCED';

END IF;

execute immediate v_alter_table_sql;

SET v_rows = v_rows - 1;

end while;

close tmp_cursor;

END!