Never been to DZone Snippets before?

Snippets is a public source code repository. Easily build up your personal collection of code snippets, categorize them with tags / keywords, and share them with the world

« Newer Snippets
Older Snippets »
Showing 1-4 of 4 total  RSS 

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

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
to sql server mssql DB2 dblink by wjchay on Mar 26, 2007

DB2 Manual Installation on Linux

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.
to linux install DB2 manual installation by wjchay on Mar 16, 2007

DB2 Federation

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
to remote DB2 dblink federation by wjchay on Mar 16, 2007

DB2 enable/disable constraints

-- 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!
to constraints DB2 referential integrity by wjchay on Mar 16, 2007
« Newer Snippets
Older Snippets »
Showing 1-4 of 4 total  RSS