User and Password when Connecting to DB2 via JDBC

Question

I am trying to connect to a local DB2 10.5 Express-C server. This is a testing environment so I don't care about security.

I am able to connect to the Command Line Processor (running on Windows), and I changed the configuration settings for AUTHENTICATION=CLIENT and TRUST_ALLCLNTS=YES. I expected to be able to connect without authentication at that point (I restarted the DB2 service), but I still get an error.

When I try to connect without username/password

jdbc:db2://localhost:50000/test

I get the error: com.ibm.db2.jcc.am.SqlSyntaxErrorException: [jcc][t4][10205][11234][4.14.146] Null userid is not supported. ERRORCODE=-4461, SQLSTATE=42815

And when I try to add "bogus" user/password (because I can't figure out how to set or reset credentials), e.g.

jdbc:db2://localhost:50000/test:user=userid;password=password;

I get the error: com.ibm.db2.jcc.am.SqlInvalidAuthorizationSpecException: [jcc][t4][201][11237][4.14.146] Connection authorization failure occurred. Reason: Security mechanism not supported. ERRORCODE=-4214, SQLSTATE=28000

How can I connect using JDBC?

DB2 Configuration below:

db2 get dbm cfg


          Database Manager Configuration     Node type = Database Server with local and remote clients Database manager configuration release level            = 0x1000 CPU speed (millisec/instruction)             (CPUSPEED) = 1.023413e-007 Max number of concurrently active databases     (NUMDB) = 32
 Federated Database System Support           (FEDERATED) = NO
 Transaction processor monitor name        (TP_MON_NAME) = 
 Default charge-back account           (DFT_ACCOUNT_STR) = 
 Java Development Kit installation path       (JDK_PATH) = C:\PROGRA~1\IBM\SQLLIB\java\jdk Diagnostic error capture level              (DIAGLEVEL) = 3
 Notify Level                              (NOTIFYLEVEL) = 3
 Diagnostic data directory path               (DIAGPATH) = E:\Data\DB2\\IBM\DB2\DB2COPY1\DB2\
 Current member resolved DIAGPATH                        = E:\Data\DB2\\IBM\DB2\DB2COPY1\DB2\
 Alternate diagnostic data directory path (ALT_DIAGPATH) = 
 Current member resolved ALT_DIAGPATH                    = 
 Size of rotating db2diag & notify logs (MB)  (DIAGSIZE) = 0 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
   Timestamp                         (DFT_MON_TIMESTAMP) = ON
   Unit of work                            (DFT_MON_UOW) = OFF
 Monitor health of instance and databases   (HEALTH_MON) = OFF SYSADM group name                        (SYSADM_GROUP) = 
 SYSCTRL group name                      (SYSCTRL_GROUP) = 
 SYSMAINT group name                    (SYSMAINT_GROUP) = 
 SYSMON group name                        (SYSMON_GROUP) = 
 Client Userid-Password Plugin          (CLNT_PW_PLUGIN) = 
 Client Kerberos Plugin                (CLNT_KRB_PLUGIN) = IBMkrb5
 Group Plugin                             (GROUP_PLUGIN) = 
 GSS Plugin for Local Authorization    (LOCAL_GSSPLUGIN) = 
 Server Plugin Mode                    (SRV_PLUGIN_MODE) = UNFENCED
 Server List of GSS Plugins      (SRVCON_GSSPLUGIN_LIST) = 
 Server Userid-Password Plugin        (SRVCON_PW_PLUGIN) = 
 Server Connection Authentication          (SRVCON_AUTH) = NOT_SPECIFIED
 Cluster manager                                         = 
 Database manager authentication        (AUTHENTICATION) = CLIENT
 Alternate authentication           (ALTERNATE_AUTH_ENC) = NOT_SPECIFIED
 Cataloging allowed without authority   (CATALOG_NOAUTH) = YES
 Trust all clients                      (TRUST_ALLCLNTS) = YES
 Trusted client authentication          (TRUST_CLNTAUTH) = CLIENT
 Bypass federated authentication            (FED_NOAUTH) = YES Default database path                       (DFTDBPATH) = C: Database monitor heap size (4KB)          (MON_HEAP_SZ) = AUTOMATIC(66)
 Java Virtual Machine heap size (4KB)     (JAVA_HEAP_SZ) = 2048
 Audit buffer size (4KB)                  (AUDIT_BUF_SZ) = 0
 Global instance memory (4KB)          (INSTANCE_MEMORY) = AUTOMATIC(3592673)
 Member instance memory (4KB)                            = GLOBAL
 Agent stack size                       (AGENT_STACK_SZ) = 16
 Sort heap threshold (4KB)                  (SHEAPTHRES) = 0 Directory cache support                     (DIR_CACHE) = YES Application support layer heap size (4KB)   (ASLHEAPSZ) = 15
 Max requester I/O block size (bytes)         (RQRIOBLK) = 65535
 Workload impact by throttled utilities(UTIL_IMPACT_LIM) = 10 Priority of agents                           (AGENTPRI) = SYSTEM
 Agent pool size                        (NUM_POOLAGENTS) = AUTOMATIC(100)
 Initial number of agents in pool       (NUM_INITAGENTS) = 0
 Max number of coordinating agents     (MAX_COORDAGENTS) = AUTOMATIC(200)
 Max number of client connections      (MAX_CONNECTIONS) = AUTOMATIC(MAX_COORDAGENTS) Keep fenced process                        (KEEPFENCED) = YES
 Number of pooled fenced processes         (FENCED_POOL) = AUTOMATIC(MAX_COORDAGENTS)
 Initial number of fenced processes     (NUM_INITFENCED) = 0 Index re-creation time and redo index build  (INDEXREC) = RESTART Transaction manager database name         (TM_DATABASE) = 1ST_CONN
 Transaction resync interval (sec)     (RESYNC_INTERVAL) = 180 SPM name                                     (SPM_NAME) = WORKS12
 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) = db2c_DB2
 Discovery mode                               (DISCOVER) = SEARCH
 Discover server instance                (DISCOVER_INST) = ENABLE SSL server keydb file                   (SSL_SVR_KEYDB) = 
 SSL server stash file                   (SSL_SVR_STASH) = 
 SSL server certificate label            (SSL_SVR_LABEL) = 
 SSL service name                         (SSL_SVCENAME) = 
 SSL cipher specs                      (SSL_CIPHERSPECS) = 
 SSL versions                             (SSL_VERSIONS) = 
 SSL client keydb file                  (SSL_CLNT_KEYDB) = 
 SSL client stash file                  (SSL_CLNT_STASH) = 
 Maximum query degree of parallelism   (MAX_QUERYDEGREE) = ANY
 Enable intra-partition parallelism     (INTRA_PARALLEL) = NO No. of int. communication buffers(4KB)(FCM_NUM_BUFFERS) = AUTOMATIC(1024)
 No. of int. communication channels   (FCM_NUM_CHANNELS) = AUTOMATIC(512)
 db2start/db2stop timeout (min)        (START_STOP_TIME) = 10 WLM dispatcher enabled                 (WLM_DISPATCHER) = NO WLM dispatcher concurrency            (WLM_DISP_CONCUR) = COMPUTED WLM dispatcher CPU shares enabled (WLM_DISP_CPU_SHARES) = NO WLM dispatcher min. utilization (%) (WLM_DISP_MIN_UTIL) = 5 Communication buffer exit library list (COMM_EXIT_LIST) = 
 Current effective arch level         (CUR_EFF_ARCH_LVL) = V:10 R:5 M:0 F:5 I:0 SB:0
 Current effective code level         (CUR_EFF_CODE_LVL) = V:10 R:5 M:0 F:5 I:0 SB:0 Keystore type                           (KEYSTORE_TYPE) = NONE
 Keystore location                   (KEYSTORE_LOCATION) = 

Solution

You need to use a JDBC Type 2 URL (jdbc:db2:test) Instead of the Type 4 URL you are using. You can use an empty username/password.

Since your application is running on the same machine as the database server, you don't even need to set AUTHENTICATION = CLIENT or TRUST_ALLCLNTS = YES.

Comments

[isapir]

When I try that there's no error from DriverManager.getConnection(). but any query that I try to execute with either Statement.executeQuery() or Statement.executeUpdate() throws an ArrayIndexOutOfBoundsException: java.lang.ArrayIndexOutOfBoundsException at java.lang.System.arraycopy(Native Method) at com.ibm.db2.jcc.t4.y.a(y.java:102) at com.ibm.db2.jcc.t4.y.a(y.java:150) at com.ibm.db2.jcc.uw.UWReply.b(UWReply.java:62) at com.ibm.db2.jcc.t4.y.c(y.java:360) at com.ibm.db2.jcc.t4.y.d(y.java:375) at com.ibm.db2.jcc.t4.y.f(y.java:646) at com.ibm.db2.jcc.t4.z.ic(z.java:4622)

[Ian Bjorhovde]

Seems like you may have a mismatch between the JDBC driver and the client. Are you using the db2jcc.jar file that's included with your Express-C install (i.e., in C:\Program Files\IBM\SQLLIB\java), or a different driver that you downloaded?

[isapir]

A different driver I downloaded from the IBM website, and I actually use db2jcc4.jar which might cause the incompatibility with a JDBC 2 connection string?. I will try to use the one you mentioned and report back.