Results 1 to 2 of 2
  1. #1
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2

    Answered: db2 federation (db link)

    I'm tring to enable the option to select from a remote db.

    This is what I did:

    Code:
    #--target
    create database oltp_tst
    db2 connect to oltp_tst
    db2 grant dbadm on database to coreadm
    db2 connect to oltp_tst user coreadm
    db2 "create table oltp_tab (name varchar(100))"
    db2 "insert into oltp_tab values('asd')"
    db2 "insert into oltp_tab values('oltp db')"
    
    
    db2 get dbm cfg | grep -i svcename
    
    #--source
    #db2 get dbm cfg | grep -i federated
    db2 update dbm cfg using federated yes
    db2stop
    db2start
    
    db2 catalog tcpip node OLTP_FED remote targetsrv server 50000
    db2 catalog db oltp_tst as OLTP_DB at node OLTP_FED
    
    #test
    db2 connect to oltp_db user coreadm
    db2 "select * from oltp_tab"
    
    #continue
    create database archive
    db2 connect to archive
    db2 grant dbadm on database to coreadm
    db2 
    connect to archive
    create wrapper drda
    create server OLTP_SRV type db2/udb version 10.5 wrapper drda authorization "coreadm" password "coreadm" options (DBNAME 'OLTP_DB')
    create user mapping for coreadm server OLTP_SRV options (remote_authid 'coreadm', remote_password 'coreadm')
    And when I try to select:
    Code:
    db2 => select * from OLTP_SRV.coreadm.oltp_tab
    SQL0204N  "COREADM.OLTP_TAB" is an undefined name.  SQLSTATE=42704
    
    db2 => create nickname ntest1 for oltp_srv.coreadm.oltp_tab
    DB21034E  The command was processed as an SQL statement because it was not a
    valid Command Line Processor command.  During SQL processing it returned:
    SQL1101N  Remote database "OLTP_DB" on node "" could not be accessed with the
    specified authorization id and password.  SQLSTATE=08004
    I don't know if the ` node "" ` should alert me although I do set a node for it.

    By the way I test this on LUW 10.5 but it is needed for 9.7 also
    Last edited by AntiLopa; 01-18-16 at 08:19.

  2. Best Answer
    Posted by AntiLopa

    "the user mapper should be the instance owner and not the schema name.

    solution:

    Code:
    create user mapping for db2inst1 server OLTP_SRV options (remote_authid 'coreadm', remote_password 'coreadm')
    "


  3. #2
    Join Date
    Nov 2015
    Posts
    36
    Provided Answers: 2
    the user mapper should be the instance owner and not the schema name.

    solution:

    Code:
    create user mapping for db2inst1 server OLTP_SRV options (remote_authid 'coreadm', remote_password 'coreadm')

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •