What does db2top Start Date and Start Time actually mean?

While supporting a high-volume transaction period in our shop, I noticed that db2top Start Date and Start Time (option d for database) showed a very recent date and time. I was 100% sure that the DB was not restarted or activated recently.

[-]19:57:36,refresh=2secs(0.002)                                 Database                                AIX,member=[1/1],DB2INST1:MYDB

                                  lqqqqqqqqqqqqqqwqqqqqqqqqqqqwqqqqqqqqqqqqwqqqqqqqqqqqqwqqqqqqqqqqqk

                                  x              x         25%x         50%x         75%x       100%x

                                  xMaxActSess    x                                                  x

                                  xSortMemory    x                                                  x

                                  xLogUsed       x-                                                 x

                                  xFCM BufLow    x                                                  x

                                  mqqqqqqqqqqqqqqvqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqqj

                              Start Date Start Time     Status    Shthres    Buffers     FCMBuf   OtherMem

                              2017/11/25   09:23:44     Active          0      16.4G     768.0K       2.2G

                                Sessions    ActSess   LockUsed LockEscals  Deadlocks   LogReads  LogWrites

                                      48          0         0%          0          0          0          0

                                 L_Reads    P_Reads   HitRatio    A_Reads     Writes   A_Writes  Lock Wait

                                   7,197          0    100.00%      0.00%          0          0          0

                                Sortheap    SortOvf PctSortOvf AvgPRdTime AvgDRdTime AvgPWrTime AvgDWrTime

                                   20.0K          3      2.50%       0.00       0.00       0.00       0.00

My co-worker did some research and gave a beautiful explanation. Thanks Mr. Manoj. Here it is.

This command gives you info on when the DB was deactivated/activated. But there are some caveats, read carefully:

Go to the db2diaglog path and issue this.

cat db2diag.log|grep -ip "FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FreeResourcesOnDBShutdown"

The output will look something like this. Give attention to the last line in each paragraph:

2017-10-17-15.52.19.081050-240 E1495007A440         LEVEL: Event

PID     : 15073374             TID : 26036          PROC : db2sysc 0

INSTANCE: db2inst1             NODE : 000           DB   : MYDB

APPHDL  : 0-3

HOSTNAME: myhost

EDUID   : 26036                EDUNAME: db2agent (idle) 0

FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FreeResourcesOnDBShutdown, probe:15579

STOP    : DATABASE: MYDB : DEACTIVATED: YES

2017-10-24-07.35.32.144318-240 E110103488A527       LEVEL: Event

PID     : 33572592             TID : 29101          PROC : db2sysc 0

INSTANCE: db2inst1             NODE : 000           DB   : MYDB

APPHDL  : 0-29188              APPID: 100.00.000.00.12345.171024112052

AUTHID  : myid                 HOSTNAME: myhost

EDUID   : 29301                EDUNAME: db2agent (idle) 0

FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FreeResourcesOnDBShutdown, probe:15579

STOP    : DATABASE: MYDB : DEACTIVATED: NO

2017-11-25-09.23.43.726522-300 E261286318A515       LEVEL: Event

PID     : 33572592             TID : 40505          PROC : db2sysc 0

INSTANCE: db2inst1             NODE : 000           DB   : MYDB

APPHDL  : 0-52137              APPID: 172.16.104.17.38242.171125142340

AUTHID  : MYID                 HOSTNAME: myhost

EDUID   : 40505                EDUNAME: db2agent (mydb) 0

FUNCTION: DB2 UDB, base sys utilities, sqeLocalDatabase::FirstConnect, probe:1000

START   : DATABASE: MYDB : ACTIVATED: NO

ACTIVATED or DEACTIVATED “YES” means, the database has been activated or deactivated by an user that has the access to do it, such a DBA or System Admin.

ACTIVATED “NO” means, the database has been activated by DB2 automatically when the database was not started manually, and a CONNECT TO (or an implicit connect) has been issued in an application.

DEACTIVATED “NO” means, the database has been deactivated by DB2 automatically when the last application has disconnected from the DB.

Best practice is to activate the DB manually as soon as the database is started.

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 Export error - SQL10018N The disk is full

While trying to run this export command

db2 "export to /filepath/schema.tablename.ixf of ixf messages v.msgs select * from wcsproln.catentryattr”

I got this error "SQL10018N  The disk is full.  Processing was ended.”

df -g showed that /proddump had 19 GB free space.

Looked into the db2diag log and found this:

2016-05-17-18.51.46.775102-240 E48658514A815      LEVEL: Error (OS)

PID     : 48627918             TID  : 1           PROC : db2bp

INSTANCE: db2inst1             NODE : 000

APPID   : *LOCAL.db2inst1.160517215412

EDUID   : 1

FUNCTION: DB2 UDB, oper system services, sqlowrite, probe:60

MESSAGE : ZRC=0x850F000C=-2062614516=SQLO_DISK "Disk full."

          DIA8312C Disk was full.

CALLED  : OS, -, write

OSERR   : EFBIG (27) "A file cannot be larger than the value set by ulimit."

DATA #1 : File handle, PD_TYPE_SQO_FILE_HDL, 8 bytes

0x0FFFFFFFFFFFE170 : 0000 0004 0000 0080                        ........

DATA #2 : unsigned integer, 8 bytes

34

DATA #3 : signed integer, 8 bytes

-1

DATA #4 : String, 105 bytes

Search for ossError*Analysis probe point after this log entry for further

self-diagnosis of this problem.

I did some research on this error message "A file cannot be larger than the value set by ulimit.” and found that file size limit set may be too low.

Command “ulimit -a” showed the limits set:

time(seconds)        unlimited

file(blocks)         2097151

data(kbytes)         131072

stack(kbytes)        32768

memory(kbytes)       32768

coredump(blocks)     2097151

nofiles(descriptors) 2000

threads(per process) unlimited

processes(per user)  unlimited

There are two ways to change these values:

1. Edit the limits file under /etc/security/limits (takes effect after reboot) 
2. Use the chuser command to change individual user settings (logout and login required)

Used the chuser command to change the fsize value:

chuser fsize=5000000 db2inst1

Logged off and logged back in and tried the export command. Voila !! It worked.

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 !!

DB2 LUW - List of tables involved in a referential integrity chain

How do you find all the tables that are connected through referential integrity?

For example, let's assume that I've a table called TABA. This is a parent of TABB and TABB is a parent of TABC and TABC is a parent of TABD and so on. How do I find the all these related tables?

Method 1 - Query SYSCAT.REFERENCES:

db2 "select substr(TABNAME,1,30) as PARENT, substr(CONSTNAME,1,20) as CONSTRAINT, substr(REFTABNAME,1,30) as CHILD, substr(FK_COLNAMES,1,40) as REF_COL from syscat.references where tabname='TABA'"

This will give the child tables of TABA. Run the above query again for the child tables. Continue doing this until the last child table in the chain.

Method 2 - Through db2expl:

Run db2expl on "delete from tabschema.tabname" and parse the output.

db2expln -d lwscomdb -g -q "delete from SCHEMA.TABA" -t | grep -i "Delete:  Table Name"

This will do a db2 explain on the query to delete and then parse the output to get all the table names involved in the referential integrity chain.

My personal preference is Method 2.

Which method do you prefer and why? If you use a different method, please post it as well.

DB2 Performance Improvement tip using COALESCE function

One of the developers at our shop fed his query to an optimizer tool and the optimized query looked kind of weird.

This is the original query:
DELETE FROM TABLE_A
 WHERE COL1 IN (SELECT COL1
                          FROM TABLE_A R
                               INNER JOIN TABLE_B O
                                  ON O.COL2 = R.COL1
                         WHERE O.COL3 != R.COL4)


Query suggested by the optimizer:
DELETE
  FROM TABLE_A
 WHERE EXISTS (SELECT 'X'
                 FROM TABLE_A R
                      INNER JOIN TABLE_B O
                         ON R.COL4 <> COALESCE (O.COL3, O.COL3)
                            AND R.COL1 = COALESCE (O.COL2, O.COL2)
                WHERE COL1 = TABLE_A.COL1
                ORDER BY R.COL4)

The puzzling part here is in the revised query: COALESCE (O.COL3, O.COL3). Why would anyone feed the same column twice to the COALESCE function? It's going to give the same result.

Here is the explanation by IBM though: http://www-01.ibm.com/support/docview.wss?uid=swg21259831

In their own words:

Question

Why might adding a special form of the COALESCE predicate produce cheaper data access plans and result in better performance.

Cause

A "no-op" coalesce() predicate of the form "COALESCE(X, X) = X" introduces an estimation error into the planning of any query using it. Currently the DB2 query compiler doesn't have the capability of dissecting that predicate and determining that all rows actually satisfy it. As a result, the predicate artificially reduces the estimated number of rows coming from some part of a query plan. This smaller row estimate usually reduces the row and cost estimates for the rest of query planning, and sometimes results in a different plan being chosen because relative estimates between different candidate plans have changed.

Why can this do-nothing predicate sometimes improve query performance? The addition of the "no-op" coalesce() predicate introduces an error that masks something else that is preventing optimal performance.

What some performance enhancement tools do is a brute-force test: the tool repeatedly introduces the predicate into different places in a query, operating on different columns, to try to find a case where, by introducing an error, it stumbles onto a better-performing plan. This is also true of a query developer hand-coding the "no-op" predicate into a query. Typically, the developer will have some insight on the data to guide the placement of the predicate.

Using this method to improve query performance is a short-term solution which does not address root cause. It hides potential areas for performance improvements and could have the following implications:

This workaround does not guarantee permanent performance improvements. The DB2 query compiler might eventually handle the predicate better, or it might be affected by other random factors.

Other queries might be affected by the same root cause. The performance of your system in general might be suffering as a result.

Answer

Try to identify and address root cause by determining why the original plan chosen by the query compiler did not perform optimally.

Although the actual estimates in a better-performing plan using "COALESCE(X, X) = X" cannot be trusted, such a plan can still be useful because its "shape" (for example, its join order and access methods) can be used as clues to determine where a problem might lie with the original plan.

Here are some examples of questions that can help to better identify the source of performance issues:

Are the statistics out-of-date?

Should more statistics be collected?

Are there statistical correlations that have not been detected?

Are there other characteristics of the data that are not yet captured or modeled?

Are there hardware problems? (e.g. a disk is misbehaving)

Are there configuration problems?

Are any queries stuck behind a lock?

DB2 Stored Procedure Last Used Date

Ever wonder when was a stored procedure last used/invoked in DB2 LUW? Knowing this may be helpful in determining whether a stored procedure is still in use or how recently it was used. Run this query to find this information.

SELECT PR.PROCSCHEMA
      ,PR.PROCNAME
      ,PK.LASTUSED
  FROM SYSCAT.PROCEDURES PR
      ,SYSCAT.ROUTINEDEP RD
      ,SYSCAT.PACKAGES   PK
 WHERE PR.SPECIFICNAME =  RD.ROUTINENAME
   AND RD.BNAME        =  PK.PKGNAME
   AND PR.PROCSCHEMA  <> 'SYSIBMADM'
   AND PK.LASTUSED     > '01/01/0001'
 ORDER BY PK.LASTUSED DESC
  WITH UR;

Remember that, you can use this query only from DB2 LUW V9.7 onwards.

DB2 DIAGNOSE DISPLAY MEPL

DIAGNOSE is a DB2 for z/OS utility that, as the name suggests, is used in diagnosing problems. It generates information that is useful in diagnosing problems. But you should use this utility only under the direction of IBM Software Support staff, except for DIAGNOSE DISPLAY MEPL.

You can use the DISPLAY MEPL even without contacting IBM in the following scenario.

First of all, MEPL is an acronym for Module Entry Point List (MEPL). DIAGNOSE DISPLAY MEPL dumps module entry point lists to SYSPRINT.

Recently, we had an issue with a query after applying the maintenance RSU1304. I opened a SR (Service Request) with IBM and the support staff found a PTF in PE status. It took about 2 months to find a fix and eventually when I applied a PTF to fix the PTF in PE, it didn't work. The query still had the same problem.

IBM asked me to run this utility to find if the PTF is in fact applied to the subsystem that we were testing the query. The SYSPRINT from this utility didn't show the PTF. That's when I realized that the PTF was applied to the maintenance library, but the load module was not copied to the production run time library. After copying the load module from maintenance library to run time library, the query ran good.

Usually I do search the SMPPTS library to see if PTF that was applied recently is found, but that's not foolproof. This method of running the DIAGNOSE DISPLAY MEPL utility looks foolproof. Lesson learnt !!

Here is a sample job to run this utility:

//jobcard
//JOBLIB   DD  DISP=SHR,DSN=SYS2.DB2.DBxx.SDSNLOAD
//STEP0010 EXEC PGM=DSNUTILB,PARM='DBxx,MEPL',REGION=4M
//SYSPRINT DD SYSOUT=*
//SYSUDUMP DD SYSOUT=*
//SYSIN    DD  *
  DIAGNOSE DISPLAY MEPL
/*

Thanks for stopping by.

How to load LOB tablespaces using DSN1COPY?

In the past, I've tried to load tablespaces with LOB columns using the regular IBM Load, BMC Load Plus & CA Fast Load. None of these tools support LOB tablespaces. You get into a series of errors.

The only utility that has ever worked for me is DSN1COPY. It's a pain to use DSN1COPY though. You need several things to go right for this to work. Let me try to explain as simple as possible.

In this example, I'm copying data from an imagecopy dataset into a table in a TEST subsystem. The first thing you need to remember is that, you need to know the DBID of the database, PSID of the tablespace and OBID of the table for both the source and target subsystems.

These are the steps:

1. STOP the base and LOB tablespaces and indexes for the LOB table on the target subsystem

2. Run DSN1COPY with the right parameters. I use PARM='PAGESIZE(32K),LOB,FULLCOPY,OBIDXLAT,RESET'. Supply the imagecopy dataset of the table on the source subsystem for SYSUT1 and supply the DSNDBC linear dataset of the table on the target subsystem for SYSUT2.

3. START the base and LOB tablespaces and indexes for the LOB table on the target subsystem

4. Rebuild the indexes

5. Run runstats

If the DSN1COPY step abends with "DSN1992I VSAM PUT ERROR, RPLERREG = 008, RPLERRCD = 028", check JESMSGLG. If you see a message like this "IEC070I 204-220,jobname,stepname,SYSUT2,6BF7,DBD115& IEC070I DB2CATx.DSNDBC.dbname.tsname.I0001.A001", it means you do not have enough space in that dataset. Make sure, you assign multiple datasets with suffix A001, A002, etc. in the target subsystem, it needs to accommodate all data from the source.

If you see a message like this "IEC070I 209-220,jobname,stepname,SYSUT2,89B5,DBD235,& IEC070I DB2CATx.DSNDBC.dbname.tsname.I0001.A001,", that means no space is available in the DASD volume. To fix this problem, make the dataset multi volume so that it can extend space into multiple volumes.

HTH !!

Date of a particular day

A developer asked me, how do I find the date for last Sunday. It was a Wednesday when he asked. After some thinking, this is what I gave him as solution. This works in DB2, not sure about other databases. 

SELECT CURRENT DATE - (DAYOFWEEK(CURRENT DATE) - 1) DAYS 
  FROM SYSIBM.SYSDUMMY1                                 

This solution works only for Sunday. For Monday, change 1 to 2, for Tuesday 3 and so on.

I've not done an extensive testing, so take it with a grain of salt :)

DB2 for zOS Index on Expression

DB2 for z/OS version 9 brought in a new feature called "Index on Expression". Yes, you can create indexes on frequently used expressions in your queries. For example, if you often do a SELECT FIRST_NAME WHERE UPPER(LAST_NAME) = 'SMITH', then creating an index on UPPER(LAST_NAME) will be useful.

Until V8, you can create index on just columns, not on expressions/functions etc. So, you would have created an index on LAST_NAME, but DB2 would have ignored it because when you use a function on a column, DB2 ignores index access. But starting in V9, you can create an index on UPPER(LAST_NAME) and DB2 will use this index.

Now, to the tricky part ...

I tried to create an index on UPPER(LAST_NAME), DB2 complained that I need to specify 'locale' name. After reading a little bit about 'locale names', I specified UPPER(LAST_NAME, 'En_US') and DB2 happily created the index. I also ran runstats on the table. However, when I did an explain on the query SELECT FIRST_NAME WHERE UPPER(LAST_NAME) = 'SMITH', DB2 didn't seem to pick up the newly created index. That puzzled me.

After reading a little further, I found that I need to specify the same 'locale' in the query too. After specifying the 'locale', I did an explain and DB2 picked up the index. This is the modified query:

SELECT FIRST_NAME WHERE UPPER(LAST_NAME, 'EN_US') = 'SMITH'

Keywords: DB2, z/OS, Index, Expression, Upper, Locale, function

How to find the RSU or PUT level maintenance through SMP/E?

Usually, if you are working on a mainframe issue (DB2 or CICS or MQ or any mainframe stuff), and if you are dealing with a 3rd party vendor, they would ask the maintenance level or patch level or PUT level or RSU level of your system or product. How do you find the RSU/PUT level?


If you install your products through SMP/E, this is how you find this information.
Go to SMP/E panel, and enter SMCSI dataset (global zone data set) and select option 3 and hit enter.

Select option 3 from this screen and hit enter.

Just hit enter again (no input for ZONE NAME).

Now select the region you are interested in. GLOBAL zone will show all the RSU that has been RECEIVED (it may or may not be APPLYed already). TARGET zone will show all the RSU that has been APPLYed.


e.g., this screen shows the last maintenance applied (RSU1103), along with the previous maintenance:

DB2 V9 for z/OS - New ZPARM EDM_SKELETON_POOL

I'm working on upgrading DB2 for zOS from V8 to V9. I completed upgrading it to V9 CM (Compatibility/Conversion Mode) in one of our PROD subsystems about 2 weeks ago and the next business day was horrible. The performance was bad for the entire subsystem. The LPAR CPU peaked to 98% of the total MIPS capacity.

The activity rate against the SPT01 tablespace jumped several times and the CICS regions started sucking 20% more CPU. We noticed a few bad queries through Omegamon and we fixed them by either tweaking the stats or putting some new indexes. But that didn't bring down the CPU consumption much. The interesting part was, our zIIP engine started to peak at 100%, which usually stays at 40%. We found one query that had done a bunch of updates and got stuck - it was using a lot of CPU but was not doing any productive work. We cancelled this thread, but it wouldn't go away and the zIIP engine was still at 100%. Many more transactions/threads started to back log because of lack of CPU availability. The cancelled thread didn't die and still was burning CPU. We ended up cycling DB2 with a MODE(FORCE) command. Still, when DB2 came back, it started recovery of the cancelled thread, so we had to force kill it.

When that thread was at last gone, we looked at the CPU rate, and it was right back at the 98% with the only exception that zIIP processor was at 75% level.

We opened a Sev 1 PMR with IBM. As usual, they asked for dumps, SMF, RMF records etc. [Rant On] I would have thought IBM provided a solution to try for a problem with such a mature product. But instead they were waiting on us to provide docs. [Rant Off]


Anyway, we did some more research and found that, in DB2 V9, copies of packages and plans have been moved from below the 2GB bar to above the bar. This is done through a new zparm called EDM_SKELETON_POOL. The default value for this zparm is too low. So we increased it by 20 times. The system is in a much better shape now.

This is from Omegamon for DB2:
Package Table (PT) Reqs         1005483K 
PT Loads                        80073250 
% of PT Loads from DASD             7.96%


The "% of PT Loads from DASD" should be less than 10% for a healthy system. It was about 55% in our system before increasing the value of EDM_SKELETON_POOL. With any other virtual memory, make sure that there is enough physical memory to support this increase.


After this fix, the CPU looks much better but we are still seeing some runaway transactions or changed accesspath (which are not good in many cases). We're fixing them by rewriting the queries in most cases.

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

DB2 UDB Partitioned Database - Unique Index

In the absence of the DBA that usually works on DB2 UDB, I tried to create an unique index on a table that is defined on a partitioned database. I realized that it's not very straight forward to do so.

From DB2 manual: "There are performance advantages to creating a table across several database partitions in a partitioned database environment. Creating a table that will be a part of several database partitions is specified when you are creating the table. There is an additional option when creating a table in a partitioned database environment: the distribution key. A distribution key is a key that is part of the definition of a table. It determines the database partition on which each row of data is stored.

You must be careful to select an appropriate distribution key because it cannot be changed later. Furthermore, any unique indexes (and therefore unique or primary keys) must be defined as a superset of the distribution key. That is, if a distribution key is defined, unique keys and primary keys must include all of the same columns as the distribution key (they might have more columns)."

This is where I got totally lost "any unique indexes (and therefore unique or primary keys) must be defined as a superset of the distribution key. That is, if a distribution key is defined, unique keys and primary keys must include all of the same columns as the distribution key (they might have more columns)". I was trying create the unique index on a column which was not the hash key and I was getting an SQL0270N Reason Code 1. After including the hash key along with the other key, it worked.

DB2 4K space filled up

It was a Friday night and I got a call around 2 AM from Operations people saying, a lot of applications are spewing some error message in DB2 Master address space. I saw these messages in DB**MSTR, which started about 30 minutes ago and still continuing.

I looked up both the reason codes 00C900A5 and 00C90084. The DB2 messages and codes guide said "Explanation: The temporary file with the page size shown in NAME is not available. An error was detected either during the opening of the page set or during the formatting of the header and the space map pages of the pageset.

If the reason code appears in a DSNT500I message issued during the -START DATABASE command, the request fails. In either case, the temporary file function does not allocate any temporary file on the page set."

I checked the 4K datasets DB2CAT.DSNDBC.DSNDB07.DSN4Kxx.I0001.A001. We've 6 of them. I checked the extents on these, and they had spanned for only one extent. So the problem must be something else. I decided to stop and start the 4K tablespaces and issued a STOP against these 6 tablespaces. However, they were in STOPP (Stop Pending) status for a long time. That's when I looked in Omegamon for DB2 and found that there were 4 threads that we running for a long time and had consumed millions of getpages, they were sorting large amounts of data, which means they were using up most of the 4K space. I killed these 4 threads and voila, all the 6 tablespaces stopped. I then issued the START command and started them. They came up without any hiccup and all the error messages stopped. 

SQLCODE -331

Recently I altered a tablespace CCSID from EBCDIC to UNICODE. This tablespace had a PLAN_TABLE in it. After altering the tablespace, I couldn't retrieve certain rows from this PLAN_TABLE. I started getting SQLCODE -331. The message appeared like this:

"CHARACTER CONVERSION CANNOT BE PERFORMED BECAUSE A STRING, POSITION43 CANNNOT BE CONVERTED FROM 367 TO 37, REASON 12"

Position 43 refers to column IBM_REQUIRED_DATA in the PLAN_TABLE. As a short term solution, I spaced out the value in this column and it fixed it. However, it kept appearing for different programs/queries. So, I altered the table column attribute for IBM_REQUIRED_DATA from "FOR SBCS DATA" to "FOR BIT DATA". It fixed the problem.

SQLCODE -518

The reason mentioned in the DB2 manual for SQLCODE -518 is "THE EXECUTE STATEMENT DOES NOT IDENTIFY A VALID PREPARED STATEMENT"

You may get this SQLCODE for many reasons. I don't know them all. For me this is what happened. When I tried to connect to a remote DB2 database (on LUW) from a COBOL program on the mainframe, the CONNECT was successful. However when I tried to run an SQL on that database I got this SQLCODE.

After researching for a while, I found that I had an existing SQL statement within EXEC SQL and END-EXEC. That worked good. But when I commented out one of WHERE clause statements (using * at column 7), it gave me this error. I moved the commented statement to the line after END-EXEC and it started working again.

Bottom line is DB2 doesn't like COBOL-like commented code inside EXEC SQL and END-EXEC when you are trying to run the SQL at a remote DB2 database.

SQLCODE -804

SQLCODE -804 means something messed up with the SQLDA. Since SQLDA is internally created by DB2 for static COBOL programs, it is possible for a COBOL subscript to overwrite SQLDA statements which are inserted by DB2 at the end of the Working Storage section.

If your programmer complains about this sqlcode, have him/her increase the table size (increase the 'TIMES' parameter value) and recompile the program.

UNIQUE WHERE NOT NULL - Explained

I always had confusion over the meaning of indexes created with UNIQUE WHERE NOT NULL clause on DB2 for z/OS. The manuals don't seem to do a good job explaining it. I understand it now and here is how it works.

It means its ok to have multiple rows with null values in columns included in the index, but its not ok to have rows where the combination of the columns included in the index is not unique and all the columns are not null. I tested this on a three column index. I was able to insert rows rows shown below just fine, the only duplicate exception I got was where column a, b, c were the same value for two different rows and none of the 3 columns contained a null value.

  COL1  COL2  COL3
  ----  ----  ----
   A     B     C
   A     B     -
   A     B     -
   A     -     -
   A     -     -
   -     -     -
   -     -     -

CREATE UNIQUE WHERE NOT NULL INDEX PRE.IDX1
       ON PRE.TEST
       (COL1   ASC
       ,COL2   ASC
       ,COL3   ASC)
       USING STOGROUP ASTERISK
             PRIQTY 12
             SECQTY 10800
             ERASE NO
       FREEPAGE 0
       PCTFREE 0
       BUFFERPOOL BP1
       CLOSE YES
       PIECESIZE 2097152  K;

CREATE TABLE PRE.TEST
       (COL1     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       ,COL2     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       ,COL3     CHARACTER(1)   FOR SBCS DATA WITH DEFAULT NULL
       )
       IN DB.TS;