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
