How to find the database connection strings?

To find the database connection strings such as url, port number etc:

Database URL can be found using the command ifconfig -a.

en1: flags=xxxxx,480

        inet xxx.xxx.xx.xx netmask 0xffffff00 broadcast xxx.xxx.xx.xx

         tcp_sendspace 12345 tcp_recvspace 12345 rfc1323 1

en2: flags=xxxxx,480

        inet xxx.xxx.xx.xx netmask 0xffffff00 broadcast xxx.xxx.xx.xx

         tcp_sendspace 12345 tcp_recvspace 12345 rfc1323 1

lo0: flags=xxxxx,c0

        inet xxx.xxx.xx.xx netmask 0xff000000 broadcast xxx.xxx.xx.xx

         tcp_sendspace 12345 tcp_recvspace 12345 rfc1323 1

Port number can be found using these steps:

• db2 get dbm cfg|grep -i svcename
  •  TCP/IP Service name                          (SVCENAME) = db2_db2inst1

• Search for the svcename value in /etc/services file (cat /etc/services|grep -i db2_db2inst1)
  • db2_db2inst1   50000/tcp 

• Check if the port is listening
  • netstat -Aan|grep -i 50000

DB2 LUW - Connection refused. ERRORCODE=-4499, SQLSTATE=08001

Application developer says that he is not able to connect to the database, he's getting a message similar to this:
"Error opening socket to server /nnn.nnn.nnn.nnn on port 50,000 with message: Connection refused. ERRORCODE=-4499, SQLSTATE=08001"

How do you resolve it?

• Get the TCP/IP service name: 

• Issue this command: db2 get dbm cfg|grep -i svce
• The output will be something like this:
• TCP/IP Service name                          (SVCENAME) = svce_db2inst1
 
• Verify the port number:

• Issue this command: cat /etc/services|grep -i svce_db2inst1
• The output will be something like this: svce_db2inst1   50000/tcp
 
• Make sure the port is listening:

• Issue this command: netstat -Aan|grep -i 50000
• If you do not see any output, it means the port is not listening and that's a problem

• If the port is not listening, check the db2set parameter:

• Issue this command:  db2set
• If you don't see a DB2COMM parameter, then it needs to be set for applications to communicate with DB2
• Update the DB2COMM parameter with this command: db2set db2comm=TCPIP
• Stop and restart the DB2 instance: db2stop and db2start 

• After restarting DB2, check if the port is listening by issuing the command "netstat -Aan|grep -i 50000". If the port is listening, you'll see the output similar to this:

• f1000e00057aebb8 tcp4       0      0  *.50000               *.*                   LISTEN
• f1000e00054ddbb8 tcp4       0      0  nnn.nnn.nnn.nnn.50000   nnn.nnn.nnn.mmm 33xxx   ESTABLISHED
• f1000e000b3653b8 tcp4       0      0  nnn.nnn.nnn.nnn.50000   nnn.nnn.nnn.mmm 33yyy   ESTABLISHED

HTH !!

Accessing DB2 LUW table through a COBOL program on mainframe

If you are planning to write a COBOL program to access data from a table on DB2 LUW, you need to do the right setup first. I've described them here as much as I remember. If you get into any trouble, let me know, we can try to resolve it together.

Things to do on Mainframe DB2

Insert row in SYSIBM.LOCATIONS with these values

• LOCATION - dbname <-- This is the DB2 LUW database name

• LINKNAME - linkname <-- This is parent of a row in SYSIBM.IPNAMES or SYSIBM.LUNAMES (For this to work it needs to be the name of the DB on the UDB box, because this is how it determines which DB to connect to if that DB2 instance has more than one DB under it.)

• IBMREQD - N

• PORT - portnum <-- This is the connection port on the UDB box. To find this on AIX do: "cat /etc/services". The end of the file will have the connection port for the DB2 instance. On NT it will be 50000.

• TPN - Leave this empty

• Insert row in SYSIBM.IPNAMES

• LINKNAME - linkname This ties to a row in SYSIBM.LOCATIONS with column LINKNAME set to same

• SECURITY_OUT - A A means Already verified

• USERNAMES -

• IBMREQD - N

• IPADDR - nn.nn.nn.nn This is the IP address. You can find this by doing a ping command from a TSO command line or from a NT command prompt. (e.g. PING EXPLORER)

Then the DDF address space must be stopped and restarted to pick up the changes made to the SYSIBM.IPNAMES, SYSIBM.LUNAMES.


Things to do on DB2 LUW

You need to update the DataBaseManager configuration which is at the DB2 Instance level. You can display the current settings with this command from a command line on NT or AIX

DB2 GET DBM CFG | more

Part of the output will look like this:

Database manager authentication (AUTHENTICATION) = SERVER

Cataloging allowed without authority (CATALOG_NOAUTH) = NO
Trust all clients (TRUST_ALLCLNTS) = YES
Trusted client authentication (TRUST_CLNTAUTH) = CLIENT

There are 2 parms that must be changed from the default to allow connecting from the host--AUTHENTICATION and TRUST_ALLCLNTS. Use the following commands to change these. The changes don't take effect until the db2 instance is stopped and started, so you may need to coordinate cycling the instance with applications and the server owner(s).


UPDATE DBM CFG USING AUTHENTICATION CLIENT
UPDATE DBM CFG USING TRUST_ALLCLNTS DRDAONLY

To cycle the DB2 instance use DB2STOP After it stops do a DB2START. Its a good idea to do a DB2 LIST APPLICATIONS beforehand to see if there are any threads. You can use a FORCE command if you need to to kill any threads.

On top of the above things, if you are writing a COBOL program on host and trying to connect to DB2 LUW, these steps must also be followed.

1. BIND PACKAGE (luwdbname.collectionid) OWNER(CHGTMANP) ISOLATION(CS) QUALIFIER(ALIUPDP) MEMBER(programname) DYNAMICRULES(RUN) VALIDATE(BIND) SQLERROR(CONTINUE) ACTION(REPLACE)

2. Make sure the above BIND statement doesn't fail on anything other than -204 (especially look for -551s, because this could lead to -525 at run time)

3. Grant DBADM authority to the userid that is specified in the OWNER parameter of your BIND card

4. Add the new "dabase.collection.*" to PKLIST on the plan that you will use to run your cobol program
e.g.,

BIND PLAN(plan name) OWNER(owner) QUALIFIER(qualifier)+

     NODEFER(PREPARE) VALIDATE(BIND) ISOLATION(CS)+
     CACHESIZE(1024) CURRENTDATA(NO) DEGREE(1)+
     SQLRULES(DB2) ACQUIRE(USE) RELEASE(COMMIT)+
     EXPLAIN(NO) REOPT(NONE) KEEPDYNAMIC(NO)+
     IMMEDWRITE(NO) DBPROTOCOL(DRDA) ENCODING(37)+
     DISCONNECT(EXPLICIT)+
     PKLIST(collction1.*,+
            collection2.*,+
            database.collection3.*)+
     ENABLE(*)+
     ACTION(REPLACE) RETAIN

5. In the COBOL program, make sure the programmers are coding these in the right order:


SET CURRENT PACKAGESET = 'collectionname'
CONNECT TO :database-name

6. Give EXECUTE access to PUBLIC on the package on LUW for the first time it is bound. This should be done for each new package you create