authorization problems when creating server for federated database
Igor
I desperately need help to get started with federated databases.
I am trying to configure the simplest case of a database on
machine A (Solaris 8, DB2 7.1) using database
on machine B (either Solaris 8, DB2 7.1 or Linux, RedHat 7.1, DB2
7.1).
I believe all nodes and databases are properly cataloged because
I have no trouble connecting db2 client from either of my machine
to some database on the other.
However, when I try to create server either with db2cc or command line
I get an error:
SQL1101N: remote database "S1" on node "TEST" could not be accessed
with the specified authorization id and password.
I am absolutely lost.
Please, help.
Thank you.
Igor
Here are more details about my DB2 configuration and what I am doing.
---------------------------------
Machine A's output from
db2 list admin node directory
Node Directory
Number of entries in the directory = 3
Node 1 entry:
Node name = LINUXBOX
Comment =
Protocol = TCPIP
Hostname = linuxbox1
Service name = 523
..............................
---------------------------------
Machine A's output from
db2 list node directory
Node Directory
Number of entries in the directory = 2
......................
Node 2 entry:
Node name = TEST
Comment =
Protocol = TCPIP
Hostname = linuxbox1
Service name = 50000
---------------------------------
Machine A's output from
db2 list database directory
System Database Directory
Number of entries in the directory = 6
Database 1 entry:
Database alias = SAMPLE1
Database name = SAMPLE1
Local database directory = /scratch/db2inst1
Database release level = 9.00
Comment = none
Directory entry type = Indirect
Catalog node number = 0
...............................
Database 3 entry:
Database alias = S1
Database name = SAMPLE
Node name = TEST
Database release level = 9.00
Comment =
Directory entry type = Remote
Catalog node number = -1
.............................
Here is what I am doing on machine A:
1) connect to sample1
2) create wrapper DRDA
3) create server "ftest" type DB2/LINUX version 7.1 wrapper "DRDA"
authorization db2inst1 password XXX options (node 'TEST', dbname 'S1')
Here I get the error:
SQL1101N Remote database "S1" on node "TEST" could not be accessed
with the
specified authorization id and password.
If I do this:
connect to s1 user db2inst1 using XXX
I have no trouble connecting and using the remote database.
---------------------------------
Machine A's output from
db2 get database manager configuration
Database Manager Configuration
Node type = Database Server with local and remote clients
Database manager configuration release level = 0x0900
CPU speed (millisec/instruction) (CPUSPEED) =
1.310287e-06
Max number of concurrently active databases (NUMDB) = 8
Data Links support (DATALINKS) = NO
Federated Database System Support (FEDERATED) = YES
Transaction processor monitor name (TP_MON_NAME) =
Default charge-back account (DFT_ACCOUNT_STR) =
Java Development Kit 1.1 installation path (JDK11_PATH) =
Diagnostic error capture level (DIAGLEVEL) = 3
Diagnostic data directory path (DIAGPATH) =
/scratch/db2inst1/sqllib/db2dump
Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = OFF
Lock (DFT_MON_LOCK) = OFF
Sort (DFT_MON_SORT) = OFF
Statement (DFT_MON_STMT) = OFF
Table (DFT_MON_TABLE) = OFF
Unit of work (DFT_MON_UOW) = OFF
SYSADM group name (SYSADM_GROUP) = DB2IADM1
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
Database manager authentication (AUTHENTICATION) = SERVER
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Default database path (DFTDBPATH) =
/scratch/db2inst1
Database monitor heap size (4KB) (MON_HEAP_SZ) = 56
UDF shared memory set size (4KB) (UDF_MEM_SZ) = 256
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 2048
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Backup buffer default size (4KB) (BACKBUFSZ) = 1024
Restore buffer default size (4KB) (RESTBUFSZ) = 1024
Sort heap threshold (4KB) (SHEAPTHRES) = 20000
Directory cache support (DIR_CACHE) = YES
Application support layer heap size (4KB) (ASLHEAPSZ) = 15
Max requester I/O block size (bytes) (RQRIOBLK) = 32767
Query heap size (4KB) (QUERY_HEAP_SZ) = 1000
DRDA services heap size (4KB) (DRDA_HEAP_SZ) = 128
Priority of agents (AGENTPRI) = SYSTEM
Max number of existing agents (MAXAGENTS) = 200
Agent pool size (NUM_POOLAGENTS) = 4
(calculated)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = MAXAGENTS
Max no. of concurrent coordinating agents (MAXCAGENTS) =
MAX_COORDAGENTS
Max number of logical agents (MAX_LOGICAGENTS) =
MAX_COORDAGENTS
Keep DARI process (KEEPDARI) = YES
Max number of DARI processes (MAXDARI) =
MAX_COORDAGENTS
Initialize DARI process with JVM (INITDARI_JVM) = NO
Initial number of fenced DARI process (NUM_INITDARIS) = 0
Index re-creation time (INDEXREC) = RESTART
Transaction manager database name (TM_DATABASE) = 1ST_CONN
Transaction resync interval (sec) (RESYNC_INTERVAL) = 180
SPM name (SPM_NAME) =
SPM log size (SPM_LOG_FILE_SZ) = 256
SPM resync agent limit (SPM_MAX_RESYNC) = 20
SPM log path (SPM_LOG_PATH) =
TCP/IP Service name (SVCENAME) =
db2cdb2inst10
APPC Transaction program name (TPNAME) =
IPX/SPX File server name (FILESERVER) =
IPX/SPX DB2 server object name (OBJECTNAME) =
IPX/SPX Socket number (IPX_SOCKET) = 879E
Discovery mode (DISCOVER) = SEARCH
Discovery communication protocols (DISCOVER_COMM) = TCPIP
Discover server instance (DISCOVER_INST) = ENABLE
Directory services type (DIR_TYPE) = NONE
Directory path name (DIR_PATH_NAME) =
/.:/subsys/database/
Directory object name (DIR_OBJ_NAME) =
Routing information object name (ROUTE_OBJ_NAME) =
Default client comm. protocols (DFT_CLIENT_COMM) =
Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = NO
No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 1024
Number of FCM request blocks (FCM_NUM_RQB) = 512
Number of FCM connection entries (FCM_NUM_CONNECT) =
(FCM_NUM_RQB * 0.75)
Number of FCM message anchors (FCM_NUM_ANCHORS) =
(FCM_NUM_RQB * 0.75)
---------------------------------
Machine A's output from
db2 get database configuration for sample1:
Database Configuration for Database sample1
Database configuration release level = 0x0900
Database release level = 0x0900
Database territory = US
Database code page = 819
Database code set = ISO8859-1
Database country code = 1
Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE
Directory object name (DIR_OBJ_NAME) =
Discovery support for this database (DISCOVER_DB) = ENABLE
Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) = 0
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20
Backup pending = NO
Database is consistent = YES
Rollforward pending = NO
Restore pending = NO
Multi-page file allocation enabled = NO
Log retain for recovery status = NO
User exit for logging status = NO
Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0
Database heap (4KB) (DBHEAP) = 1200
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 64
Log buffer size (4KB) (LOGBUFSZ) = 8
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
Buffer pool size (pages) (BUFFPAGE) = 1000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 100
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128
Sort list heap (4KB) (SORTHEAP) = 256
SQL statement heap (4KB) (STMTHEAP) = 2048
Default application heap (4KB) (APPLHEAPSZ) = 128
Package cache size (4KB) (PCKCACHESZ) =
(MAXAPPLS*8)
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = -1
Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1
Number of I/O servers (NUM_IOSERVERS) = 3
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32
Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
Max number of active applications (MAXAPPLS) = 40
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 64
Log file size (4KB) (LOGFILSIZ) = 1000
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 2
Changed path to log files (NEWLOGPATH) =
Path to log files =
/scratch/db2inst1/db2inst1/NODE0000/SQL00002/SQLOGDIR/
First active log file =
Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF
Auto restart enabled (AUTORESTART) = ON
Index re-creation time (INDEXREC) = SYSTEM
(RESTART)
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366
TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =
---------------------------------
Machine B's output from
db2 get database manager configuration
Database Manager Configuration
Node type = Database Server with local and remote clients
Database manager configuration release level = 0x0900
CPU speed (millisec/instruction) (CPUSPEED) =
4.000000e-05
Max number of concurrently active databases (NUMDB) = 8
Data Links support (DATALINKS) = NO
Federated Database System Support (FEDERATED) = YES
Transaction processor monitor name (TP_MON_NAME) =
Default charge-back account (DFT_ACCOUNT_STR) =
Java Development Kit 1.1 installation path (JDK11_PATH) =
Diagnostic error capture level (DIAGLEVEL) = 3
Diagnostic data directory path (DIAGPATH) =
Default database monitor switches
Buffer pool (DFT_MON_BUFPOOL) = OFF
Lock (DFT_MON_LOCK) = OFF
Sort (DFT_MON_SORT) = OFF
Statement (DFT_MON_STMT) = OFF
Table (DFT_MON_TABLE) = OFF
Unit of work (DFT_MON_UOW) = OFF
SYSADM group name (SYSADM_GROUP) =
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
Database manager authentication (AUTHENTICATION) = SERVER
Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Default database path (DFTDBPATH) =
/usr1/db2inst1
Database monitor heap size (4KB) (MON_HEAP_SZ) = 56
UDF shared memory set size (4KB) (UDF_MEM_SZ) = 256
Java Virtual Machine heap size (4KB) (JAVA_HEAP_SZ) = 512
Audit buffer size (4KB) (AUDIT_BUF_SZ) = 0
Backup buffer default size (4KB) (BACKBUFSZ) = 1024
Restore buffer default size (4KB) (RESTBUFSZ) = 1024
Sort heap threshold (4KB) (SHEAPTHRES) = 20000
Directory cache support (DIR_CACHE) = YES
Application support layer heap size (4KB) (ASLHEAPSZ) = 15
Max requester I/O block size (bytes) (RQRIOBLK) = 32767
Query heap size (4KB) (QUERY_HEAP_SZ) = 1000
DRDA services heap size (4KB) (DRDA_HEAP_SZ) = 128
Priority of agents (AGENTPRI) = SYSTEM
Max number of existing agents (MAXAGENTS) = 200
Agent pool size (NUM_POOLAGENTS) = 4
(calculated)
Initial number of agents in pool (NUM_INITAGENTS) = 0
Max number of coordinating agents (MAX_COORDAGENTS) = MAXAGENTS
Max no. of concurrent coordinating agents (MAXCAGENTS) =
MAX_COORDAGENTS
Max number of logical agents (MAX_LOGICAGENTS) =
MAX_COORDAGENTS
Keep DARI process (KEEPDARI) = YES
Max number of DARI processes (MAXDARI) =
MAX_COORDAGENTS
Initialize DARI process with JVM (INITDARI_JVM) = NO
Initial number of fenced DARI process (NUM_INITDARIS) = 0
Index re-creation time (INDEXREC) = RESTART
Transaction manager database name (TM_DATABASE) = 1ST_CONN
Transaction resync interval (sec) (RESYNC_INTERVAL) = 180
SPM name (SPM_NAME) =
SPM log size (SPM_LOG_FILE_SZ) = 256
SPM resync agent limit (SPM_MAX_RESYNC) = 20
SPM log path (SPM_LOG_PATH) =
TCP/IP Service name (SVCENAME) =
db2cdb2inst1
APPC Transaction program name (TPNAME) =
IPX/SPX File server name (FILESERVER) =
IPX/SPX DB2 server object name (OBJECTNAME) =
IPX/SPX Socket number (IPX_SOCKET) = 879E
Discovery mode (DISCOVER) = SEARCH
Discovery communication protocols (DISCOVER_COMM) = TCPIP
Discover server instance (DISCOVER_INST) = ENABLE
Directory services type (DIR_TYPE) = NONE
Directory path name (DIR_PATH_NAME) =
/.:/subsys/database/
Directory object name (DIR_OBJ_NAME) =
Routing information object name (ROUTE_OBJ_NAME) =
Default client comm. protocols (DFT_CLIENT_COMM) =
Maximum query degree of parallelism (MAX_QUERYDEGREE) = ANY
Enable intra-partition parallelism (INTRA_PARALLEL) = NO
No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = 1024
Number of FCM request blocks (FCM_NUM_RQB) = 512
Number of FCM connection entries (FCM_NUM_CONNECT) =
(FCM_NUM_RQB * 0.75)
Number of FCM message anchors (FCM_NUM_ANCHORS) =
(FCM_NUM_RQB * 0.75)
---------------------------------
Machine B's output from
db2 get database configuration for sample
Database Configuration for Database sample
Database configuration release level = 0x0900
Database release level = 0x0900
Database territory = US
Database code page = 819
Database code set = ISO8859-1
Database country code = 1
Dynamic SQL Query management (DYN_QUERY_MGMT) = DISABLE
Directory object name (DIR_OBJ_NAME) =
Discovery support for this database (DISCOVER_DB) = ENABLE
Default query optimization class (DFT_QUERYOPT) = 5
Degree of parallelism (DFT_DEGREE) = 1
Continue upon arithmetic exceptions (DFT_SQLMATHWARN) = NO
Default refresh age (DFT_REFRESH_AGE) = 0
Number of frequent values retained (NUM_FREQVALUES) = 10
Number of quantiles retained (NUM_QUANTILES) = 20
Backup pending = NO
Database is consistent = YES
Rollforward pending = NO
Restore pending = NO
Multi-page file allocation enabled = NO
Log retain for recovery status = NO
User exit for logging status = NO
Data Links Token Expiry Interval (sec) (DL_EXPINT) = 60
Data Links Number of Copies (DL_NUM_COPIES) = 1
Data Links Time after Drop (days) (DL_TIME_DROP) = 1
Data Links Token in Uppercase (DL_UPPER) = NO
Data Links Token Algorithm (DL_TOKEN) = MAC0
Database heap (4KB) (DBHEAP) = 1200
Catalog cache size (4KB) (CATALOGCACHE_SZ) = 64
Log buffer size (4KB) (LOGBUFSZ) = 8
Utilities heap size (4KB) (UTIL_HEAP_SZ) = 5000
Buffer pool size (pages) (BUFFPAGE) = 1000
Extended storage segments size (4KB) (ESTORE_SEG_SZ) = 16000
Number of extended storage segments (NUM_ESTORE_SEGS) = 0
Max storage for lock list (4KB) (LOCKLIST) = 100
Max appl. control heap size (4KB) (APP_CTL_HEAP_SZ) = 128
Sort list heap (4KB) (SORTHEAP) = 256
SQL statement heap (4KB) (STMTHEAP) = 2048
Default application heap (4KB) (APPLHEAPSZ) = 128
Package cache size (4KB) (PCKCACHESZ) =
(MAXAPPLS*8)
Statistics heap size (4KB) (STAT_HEAP_SZ) = 4384
Interval for checking deadlock (ms) (DLCHKTIME) = 10000
Percent. of lock lists per application (MAXLOCKS) = 10
Lock timeout (sec) (LOCKTIMEOUT) = -1
Changed pages threshold (CHNGPGS_THRESH) = 60
Number of asynchronous page cleaners (NUM_IOCLEANERS) = 1
Number of I/O servers (NUM_IOSERVERS) = 3
Index sort flag (INDEXSORT) = YES
Sequential detect flag (SEQDETECT) = YES
Default prefetch size (pages) (DFT_PREFETCH_SZ) = 32
Default number of containers = 1
Default tablespace extentsize (pages) (DFT_EXTENT_SZ) = 32
Max number of active applications (MAXAPPLS) = 40
Average number of active applications (AVG_APPLS) = 1
Max DB files open per application (MAXFILOP) = 64
Log file size (4KB) (LOGFILSIZ) = 1000
Number of primary log files (LOGPRIMARY) = 3
Number of secondary log files (LOGSECOND) = 2
Changed path to log files (NEWLOGPATH) =
Path to log files =
/usr1/db2inst1/db2inst1/NODE0000/SQL00001/SQLOGDIR/
First active log file =
Group commit count (MINCOMMIT) = 1
Percent log file reclaimed before soft chckpt (SOFTMAX) = 100
Log retain for recovery enabled (LOGRETAIN) = OFF
User exit for logging enabled (USEREXIT) = OFF
Auto restart enabled (AUTORESTART) = ON
Index re-creation time (INDEXREC) = SYSTEM
(RESTART)
Default number of loadrec sessions (DFT_LOADREC_SES) = 1
Number of database backups to retain (NUM_DB_BACKUPS) = 12
Recovery history retention (days) (REC_HIS_RETENTN) = 366
TSM management class (TSM_MGMTCLASS) =
TSM node name (TSM_NODENAME) =
TSM owner (TSM_OWNER) =
TSM password (TSM_PASSWORD) =
-------------------------------
Machine's A output from
db2 get admin configuration
Admin Server Configuration
Node type = Database Server with local and remote clients
Database manager configuration release level = 0x0900
Diagnostic error capture level (DIAGLEVEL) = 3
Diagnostic data directory path (DIAGPATH) =
/scratch/db2as/sqllib/db2dump
SYSADM group name (SYSADM_GROUP) = DB2ASGRP
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
Database manager authentication (AUTHENTICATION) = SERVER
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Query heap size (4KB) (QUERY_HEAP_SZ) = 250
TCP/IP Service name (SVCENAME) = 523
APPC Transaction program name (TPNAME) =
IPX/SPX File server name (FILESERVER) =
IPX/SPX DB2 server object name (OBJECTNAME) =
IPX/SPX Socket number (IPX_SOCKET) = 87A2
Discovery mode (DISCOVER) = SEARCH
Discovery communication protocols (DISCOVER_COMM) = TCPIP
-------------------------------
Machine's B output from
db2 get admin configuration
Admin Server Configuration
Node type = Database Server with local and remote clients
Database manager configuration release level = 0x0900
Diagnostic error capture level (DIAGLEVEL) = 3
Diagnostic data directory path (DIAGPATH) =
SYSADM group name (SYSADM_GROUP) =
SYSCTRL group name (SYSCTRL_GROUP) =
SYSMAINT group name (SYSMAINT_GROUP) =
Database manager authentication (AUTHENTICATION) = SERVER
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT
Query heap size (4KB) (QUERY_HEAP_SZ) = 250
TCP/IP Service name (SVCENAME) = 523
APPC Transaction program name (TPNAME) =
IPX/SPX File server name (FILESERVER) =
IPX/SPX DB2 server object name (OBJECTNAME) =
IPX/SPX Socket number (IPX_SOCKET) = 87A2
Discovery mode (DISCOVER) = SEARCH
Discovery communication protocols (DISCOVER_COMM) = TCPIP
Jhanarda Ragam
already logged in as the instance id.
> Here is what I am doing on machine A:
> 1) connect to sample1
change 1) above to ==> connect to sample1 user <valid id> using <passwd>
Igor
I tried that but I am getting the same error.
Can it be connected to some authentication options on either client
or server machines?
Are there any restrictions on passwords federated database can use?
My password consists of 8 small and capital letters and numbers.
It works fine with anything else in DB2.
Might there be something in the network configuration of client
and server that would not affect db2 client connecting and working with
db2 server but would affect federated database? Does federated database
use the same ports as db2 client or does it use something extra that
might be prohibited by some firewall option?