联邦 － 数据库互操作性，继续大胆尝试（第 2 部分）

C:\Program Files\IBM\SQLLIB\cfg>type db2dj.ini
INFORMIXDIR=C:\PROGRA~1\Informix\CLIENT~1
INFORMIXSERVER=fliif
INFORMIXSERVER=flspy
INFORMIXSERVER=flxps
          ORACLE_HOME=C:\oracle\ora92
        

set ORACLE_HOME=c:\oracle\ora92
export ORACLE_HOME=/export/home/oracle

db2 => create table LURIE.do_not_mail (customer_num int)  
options (REMOTE_SERVER 'oralin', 
  REMOTE_SCHEMA 'SCOTT', REMOTE_TABNAME 'donotmail')
DB20000I  The SQL command completed successfully.

db2 => insert into do_not_mail values (101), (105), (110)
DB20000I  The SQL command completed successfully.

db2 => select * from do_not_mail
CUSTOMER_NUM
------------
         101
         105
         110
  3 record(s) selected.

IBM][CLI Driver][DB2/NT] SQL1822N  Unexpected error 
code "12541" received from data source "oralin". 
Associated text and tokens are "ORA-12541: 
TNS:no listener ".  SQLSTATE=560BD 

-- get a database connection 
connect to sample;
-- STEP 3 create the wrapper, indicating which dll to use
CREATE WRAPPER "NET8" LIBRARY 'db2net8.dll';
-- STEP 4 create the server that defines
--  the remote data source
-- notice the use of the MAXIMAL pushdown option
CREATE SERVER "oralin" 
TYPE ORACLE VERSION '9i'  
WRAPPER "NET8" OPTIONS ( NODE 'foo',
	 ADD DB2_MAXIMAL_PUSHDOWN 'Y' );
-- STEP 5 define the user mapping for authenticating
-- to the remote server
CREATE USER MAPPING FOR "LURIE" 
SERVER "oralin" OPTIONS( REMOTE_AUTHID 'scott', 
 REMOTE_PASSWORD 'use_yours');
-- STEP 6 with the configuration defined above, set 
-- up some tables in the remote server with nicknames
create nickname ora_dept for 
  "oralin"."SCOTT"."DEPT";
create nickname ora_card1 for 
  "oralin"."SCOTT"."card1";
select count(*) from ora_card1;
create nickname ora_card2 for 
  "oralin"."SCOTT"."ORA_CARD2";
select count(*) from ora_card2;

create wrapper excelw library 'db2lsxls.dll';
create server excel_src wrapper excelw; 
db2 => create nickname rc_mkt 
  ( customer_num integer ) 
  for server excel_src options (file_path 
  'c:\edrive\src\ii\mkt_list.xls', range 'a2:a20')

db2 => select * from rc_mkt
CUSTOMER_NUM
------------
         102
         107
         111
         103
         104
         105
  6 record(s) selected.

db2 => create wrapper odbc library 'db2rcodbc.dll'
DB20000I  The SQL command completed successfully.
-- the NODE is the odbc dsn name
db2 => create server ifxodbc type odbc version 3.0 
  wrapper ODBC options (node 'stores_odbc')
DB20000I  The SQL command completed successfully.
db2 => create user mapping for lurie server ifxodbc 
  options (REMOTE_AUTHID 'informix', 
  REMOTE_PASSWORD 'use_yours' )
DB20000I  The SQL command completed successfully.

db2 => set passthru ifxodbc
DB20000I  The SQL command completed successfully.
db2 => select count(*) from customer
-----------------
              28.
  1 record(s) selected.
db2 => set passthru reset
db2 => create nickname odbc_customer for 
 ifxodbc."informix"."customer"
DB20000I  The SQL command completed successfully.

db2 => create table lurie.credit_rating  ( 
customer_num int, credit_rank int) 
options ( remote_server 'rcfliif' , 
remote_schema 'informix', 
remote_tabname 'cust_credit' );
DB20000I  The SQL command completed successfully.

db2 => insert into credit_rating (customer_num, credit_rank)
  select superkey, c3 from ora_card2
DB21034E  The command was processed as an SQL statement because 
it was not a valid Command Line Processor command.  During SQL 
processing it returned:
SQL20190N  Federated insert, update, or delete operation 
cannot be compiled because of potential data inconsistency.  
SQLSTATE=0A503

db2 => altER SERVER "rcfliif" OPTIONS 
  (  ADD IUD_APP_SVPT_ENFORCE 'N')
DB20000I  The SQL command completed successfully.

db2 => insert into credit_rating 
  (customer_num, credit_rank) 
   select superkey, c3 from ora_card2
DB20000I  The SQL command completed successfully.
db2 => select count(*) from credit_rating
1
-----------
      50001
  1 record(s) selected.

db2 => select * from credit_rating 
  where customer_num > 100 fetch first 10 rows only
CUSTOMER_NUM CREDIT_RANK
------------ -----------
         101           2
         102           3
         103           1
         104           2
         105           3
         106           1
         107           2
         108           3
         109           1
         110           2
  10 record(s) selected.

-- set up the customer table nickname
db2 => create nickname rc8_customer 
  for "rc_xps"."informix"."customer"
DB20000I  The SQL command completed successfully.

        -- the rc8_customer table is the system of 
-- record for addresses
-- so get all the address fields there
select x.fname, 
	x.lname, 
	x.lname, 
	x.company, 
	x.address1, 
	x.city,
	x.state, 
	x.zipcode,
        -- use the CASE SQL statement to put in the logic?
-- here is the magic to deal with each customer 
-- situation, on the marketing list, or a range of 
-- credit ratings
	case 
        -- if the customer is on the marketing list send
-- the "super" mailing
	 when x.customer_num in 
(select customer_num from rc_mkt ) then 'super' 
        -- if the customer has a good credit, a 1 of 3
-- then send a "deluxe" offer, not quite as good as super
	 when i.credit_rank = 1  then 'deluxe' 
        -- average rating, a 2, send standard mailing
	 when i.credit_rank = 2  then 'standard' 
        -- for the 3 rankings, they owe us money from the last 
--  order
	  when i.credit_rank = 3  then 'collection letter' 
	 else 'standard' 
	  end 
        -- join condition across the the tables
from rc8_customer x, credit_rating i 
where x.customer_num = i.customer_num 
        -- now suppress all maling to those on the do not mail list
	and i.customer_num not in 
( select z.customer_num from do_not_mail z)
;
      

select x.fname, x.lname, x.company, x.address1, x.city, x.state, x.zipcode, case
 when x.customer_num in (select customer_num from rc_mkt ) then 'super' when i.c
redit_rank = 1  then 'delux' when i.credit_rank = 2  then 'standard' when i.cred
it_rank = 3  then 'collection letter' else 'standard' end from rc8_customer x, c
redit_rating i where x.customer_num = i.customer_num and i.customer_num not in (
 select z.customer_num from do_not_mail z)
FNAME           LNAME           COMPANY              ADDRESS1             CITY
          STATE ZIPCODE 8
--------------- --------------- -------------------- -------------------- ------
--------- ----- ------- -----------------
Carole          Sadler          Sports Spot          785 Geary St         San Fr
ancisco   CA    94117   super
Philip          Currie          Phil's Sports        654 Poplar           Palo A
lto       CA    94303   super
Anthony         Higgins         Play Ball!           East Shopping Cntr.  Redwoo
d City    CA    94026   super
George          Watson          Watson & Son         1143 Carver Place    Mounta
in View   CA    94063   delux
Charles         Ream            Athletic Supplies    41 Jordan Avenue     Palo A
lto       CA    94304   super
Donald          Quinn           Quinn's Sports       587 Alvarado         Redwoo
d City    CA    94063   collection letter
Jane            Miller          Sport Stuff          Mayfair Mart         Sunnyv
ale       CA    94086   delux
Frances         Keyes           Sports Center        3199 Sterling Court  Sunnyv
ale       CA    94085   super
Margaret        Lawson          Runners & Others     234 Wyandotte Way    Los Al
tos       CA    94022   delux
Lana            Beatty          Sportstown           654 Oak Grove        Menlo
Park      CA    94025   standard
(... rows not shown) 

drop view v_mkt_list;
create view v_mkt_list (fname, lname, company, 
 addr, city, st,zip, mail_type) as
select x.fname, 
	x.lname, 
	x.lname, 
	x.company, 
	x.address1, 
	x.city,
	x.state, 
	case 
	 when x.customer_num in (select customer_num from rc_mkt ) 
	   then 'super' 
	 when i.credit_rank = 1  then 'delux' 
	 when i.credit_rank = 2  then 'standard' 
	  when i.credit_rank = 3  then 'collection letter' 
	 else 'standard' 
	  end 
from rc8_customer x, credit_rating i 
where x.customer_num = i.customer_num 
	and i.customer_num not in 
	 ( select z.customer_num from do_not_mail z)
;

create table soundex_test( lname char(18) )
DB20000I  The SQL command completed successfully.
insert into soundex_test values 
  ('LURIE'), ('Laurie'), ('Luria')
DB20000I  The SQL command completed successfully.
select * from soundex_test
LNAME
------------------
LURIE
Laurie
Luria
  3 record(s) selected.
select soundex(lname) from soundex_test
1
----
L600
L600
L600
  3 record(s) selected.

select rec2xml ( 1.0, 'COLATTVAL','MKT_MAILING'
	, fname 
	, lname 
	, company 
	, addr 
	, city 
	, st 
	,zip 
	,zip 
	, mail_type)
from v_mkt_list;

<MKT_MAILING>
<column name="FNAME">Frank          </column>
<column name="LNAME">Lessor         </column>
<column name="COMPANY">Phoenix University  </column>
<column name="ADDR">Athletic Department </column>
<column name="CITY">Phoenix        </column>
<column name="ST">AZ</column>
<column name="ZIP">85008</column>
<column name="MAIL_TYPE">standard</column>
</MKT_MAILING>

create view mkt_xml (mail_label_xml) as (
select rec2xml ( 1.0, 'COLATTVAL','MKT_MAILING'
	, fname 
	, lname 
	, company 
	, addr 
	, city 
	, st 
	,zip 
	,zip 
	, mail_type)
from v_mkt_list )
;

à\IBM\SQLLIB\cfg>enable_mqfunctions -n sample -u lurie -p use_yours
  *** Please wait: creating queue manager (DB2MQ_DEFAULT_MQM) ...
  *** Please wait: starting the queue manager (DB2MQ_DEFAULT_MQM) ...
 (0) db2mq UDFs were found
     -- Create MQ Functions ...
  Validate MQ Functions ...
  Validate successfully.
  *** Please allow a few seconds to clean up the system ...

#start the queue and command server
strmqm DB2MQ_DEFAULT_MQM
strmqcsv DB2MQ_DEFAULT_MQM

C:\edrive\src\ii>db2 select db2mq.mqsend ( mail_label_xml ) from mkt_xml
1
-
1
(... rows not shown) 
1
1
1
  25 record(s) selected.