Triton Consulting

Question:

I need to find out what DB2 LUW V9.5 processes are running on a Linux machine?   Is there another way other than ‘ps –ef’ or ‘ps aux’ than grepping out the result set?

Answer:

There is.  A script by the name db2_local_ps that does the work for you.  It saves time because one does not have to fumble around figuring out the command syntax  to perform the actions to achieve similar results.

Actually, there are three scripts that are linked together to do the work.

‘db2_local_ps’ is the initial script that is called.  This script checks for the DB2INSTANCE environment variable.  If it is set and valid, you progress.  If not, it exits and a usage message is displayed.

The other scripts that are in the chain that are called in order are ‘db2nps’ and ‘db2gdep’, both shell scripts.  ‘db2nps’ obtains the process name and number and calls the script ‘db2gdep’ for each process for the information on that specific process.

The output from the ‘db2_local_ps’ follows:

cvross@myhost:~> db2_local_ps

Node 0

     UID        PID       PPID    C     STIME     TTY     TIME CMD

db2inst1      20979      20978    0     08:21   pts/1 00:00:00 db2sysc 0

    root      20980      20979    0     08:21   pts/1 00:00:00 db2ckpwd 0

    root      20981      20979    0     08:21   pts/1 00:00:00 db2ckpwd 0

    root      20982      20979    0     08:21   pts/1 00:00:00 db2ckpwd 0

    root      20983      20979    0     08:21   pts/1 00:00:00 db2pmd 0

db2inst1      20984      20979    0     08:21   pts/1 00:00:00 db2gds 0

db2inst1      20985      20979    0     08:21   pts/1 00:00:00 db2licc 0

db2inst1      20986      20979    0     08:21   pts/1 00:00:00 db2ipccm 0

db2inst1      20987      20979    0     08:21   pts/1 00:00:00 db2tcpcm 0

db2inst1      20988      20979    0     08:21   pts/1 00:00:00 db2tcpcm 0

db2inst1      20992      20979    0     08:21   pts/1 00:00:00 db2resync 0

db2inst1      20994      20979    0     08:21   pts/1 00:00:00 db2acd 0 ,0,0,0,1,0,0,0,897e0c,14,1e014,2,0,1,11fd0,0×11f90000,0×11f90000,1610000,8b001e,2,530011

db2inst1      21201      20984    0     08:23   pts/1 00:00:00 db2logts (SAMPLEA) 0

db2inst1      21202      20984    0     08:23   pts/1 00:00:00 db2logmgr (SAMPLEA) 0

db2inst1      21203      20984    0     08:23   pts/1 00:00:00 db2loggr (SAMPLEA) 0

db2inst1      21207      20984    0     08:23   pts/1 00:00:00 db2loggw (SAMPLEA) 0

db2inst1      21208      20984    0     08:23   pts/1 00:00:00 db2lfr (SAMPLEA) 0

db2inst1      21209      20984    0     08:23   pts/1 00:00:00 db2dlock (SAMPLEA) 0

db2inst1      21210      20984    0     08:23   pts/1 00:00:00 db2pclnr 0

db2inst1      21211      20984    0     08:23   pts/1 00:00:00 db2pfchr 0

db2inst1      21212      20984    0     08:23   pts/1 00:00:00 db2pfchr 0

db2inst1      21213      20984    0     08:23   pts/1 00:00:00 db2pfchr 0

db2inst1      21214      20984    0     08:23   pts/1 00:00:00 db2stmm (SAMPLEA) 0

db2inst1      21215      20984    0     08:23   pts/1 00:00:00 db2taskd (SAMPLEA) 0

db2inst1      21216      20984    0     08:23   pts/1 00:00:00 db2evmgi (DB2DETAILDEADLOCK) 0

db2inst1      21200      20986    0     08:23   pts/1 00:00:00 db2agent (SAMPLEA) 0

db2inst1      21471      20986    0     08:26   pts/1 00:00:00 db2agent (idle) 0

db2inst1      21472      20986    0     08:26   pts/1 00:00:00 db2agent (idle) 0

This is definitely something worthy to know about.  One could alias the command with a name that is more useful for a specific computing environment.  Or, just look at the code and roll your own version.

The only caveat with this command is that the db2profile needs to be sourced or the DB2 environment variables need to be loaded in your user profile.

Happy Trails. 

How many times have I asked customers for the DB2 diagnostic log file (db2diag.log) only to be told “it’s too large to send” or “it’s taking too long to open.” This is because the DB2 diagnostic files have been append only since time immemorial, their growth only restricted by the maximum space available in the file system they reside in. The only way to curb this growth would be to rename the files which would then force the creation of new ones. Scripts had to be written to automate this process and to delete old files after a certain period.

With the advent of DB2 9.7, all this is now history! The new diagsize database manager parameter allows a DBA to control the maximum sizes of the DB2 diagnostic log and administration notification log files. When this parameter is set to a non-zero value (which is the default pre-DB2 9.7 behavior), a series 10 rotating diagnostic log files and a series of rotating administration notification log files (only on UNIX and Linux) are used. It is also smart enough to clean up the diagnostic log directory of old log files. When the size of 10th file is full, the oldest file is deleted, and a new file is created. More information is available at: http://publib.boulder.ibm.com/infocenter/db2luw/v9r7/topic/com.ibm.db2.luw.admin.config.doc/doc/r0054862.html?resultof=%22%64%62%32%64%69%61%67%22%20.

Things to note:

·         diagsize is not configurable online. It needs an instance restart after an update for the new value to take effect.

·         diagsize is the value (in MB) of the total size of all the DB2 diagnostic log and administration notification log files on UNIX and Linux. 90% of the total size is allocated for the 10 diagnostic log files and 10% of the total size is allocated for the 10 administration notification log files. On Windows, diagsize is the value (in MB) of the total size of all the 10 DB2 diagnostic log files on Windows. The link above gives a good example to clarify this.

A recording of the live DB2 chat with the lab webinar held in July.

DB2 9.7 for Linux, Unix, and Windows has out-of-the-box support for Oracle’s SQL and PL/SQL dialects. This allows many applications written for Oracle to execute virtually unchanged with DB2. In this chat Serge Rielau, SQL Architect for DB2 LUW, provides an overview of Oracle compatability features in DB2 9.7.

IBM DB2 9.7 introduces enhancements that allow you to make changes to the database objects while keeping these objects fully accessible to the database users during the modification. These enhancements allow database administrators to make vital changes to the database schema on the fly without any disruption to users. This article includes examples that demonstrate how to use the ALTER TABLE command to rename columns and change column data types. Other examples demonstrate how to use the ADMIN_MOVE_TABLE routine to move and modify tables while keeping them accessible.

Take a look at IBM Developerworks to view the full article.

Part 1 – Modifying settings, setting up alerts and using System Health data views

Performance monitoring and tuning are critical tasks for a database administrator (DBA). DB2® Performance Expert helps DBAs tackle immediate performance problems, as well as avoid problems in the future. 

Take a look at the IBM Information Management developer works pages to view part 1 of these top tips.

1.      During a recent migration exercise at a customer site the following error was encountered whilst migrating one of the databases:

2009-04-08-14.21.06.625141+060 E61438A838         LEVEL: Error (OS)

PID     : 18892                TID  : 164         PROC : db2sysc 0

INSTANCE: db2inst1             NODE : 000

EDUID   : 164                  EDUNAME: db2pclnr (MQTRACK) 0

FUNCTION: DB2 UDB, oper system services, sqloLioAIOCollect, probe:100

MESSAGE : ZRC=0×870F0024=-2029060060=SQLO_MEM “out of memory”

          DIA8533C The system memory limit was reached.

CALLED  : OS, -, aio_return

OSERR   : ENOMEM (12) “Not enough space”

DATA #1 : File handle, PD_TYPE_SQO_FILE_HDL, 8 bytes

0xFFFFFFFF643FD328 : 0000 0005 0000 0000                        ……..

DATA #2 : unsigned integer, 8 bytes

8192

DATA #3 : signed integer, 8 bytes

6684672

DATA #4 : String, 105 bytes

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

self-diagnosis of this problem.

2009-04-08-14.21.06.628605+060 I62277A2206        LEVEL: Error (OS)

PID     : 18892                TID  : 164         PROC : db2sysc 0

INSTANCE: db2inst1             NODE : 000

EDUID   : 164                  EDUNAME: db2pclnr (MQTRACK) 0

FUNCTION: DB2 Common, OSSe, ossErrorIOAnalysis, probe:100

CALLED  : OS, -, aio_return

OSERR   : ENOMEM (12) “Not enough space”

DATA #1 : String, 110 bytes

A total of 5 analysis will be performed :

 - User info

 - ulimit info

 - Memory info

 Target file handle = 5

DATA #2 : String, 190 bytes

  Real user ID of current process       = 10002

  Effective user ID of current process  = 10002

  Real group ID of current process      = 1116

  Effective group ID of current process = 1116

DATA #3 : String, 353 bytes

Current process limits (unit in bytes except for nofiles) :

  mem     (S/H) = unlimited / unlimited

  core    (S/H) = unlimited / unlimited

  cpu     (S/H) = unlimited / unlimited

  data    (S/H) = unlimited / unlimited

  fsize   (S/H) = unlimited / unlimited

  nofiles (S/H) = 65536 / 65536

  stack   (S/H) = 8388608 / unlimited

  rss     (S/H) = 0 / 0

DATA #4 : String, 119 bytes

System RAM information (in megabytes) :

  Total       = 8064

  Free        = 4572

  Available   = -1

  Addressable = -1

DATA #5 : String, 69 bytes

Swap space information (in megabytes) :

  Total = 8194

  Free  = 8194

DATA #6 : String, 117 bytes

Virtual Memory Information (in megabytes) :

  Total     = 16258

  Reserved  = -1

  Available = -1

  Free      = 12766

CALLSTCK:

  [0] 0xFFFFFFFF781947B4 ossLogSysRC + 0×3A0

  [1] 0xFFFFFFFF78186F60 ossErrorNameMapSystem + 0×1AC0

  [2] 0xFFFFFFFF7C45F250 sqloSystemErrorHandler + 0×860

  [3] 0xFFFFFFFF7C4B5A94 __1cUSQdDLO_LIO_HANDLE_DATARsqloLioAIOCollect6MLpnXSQdDLO_LIO_COLLECT_STATUS_ppnLSQdDLO_IO_REQdD__i_ + 0×6E4

  [4] 0xFFFFFFFF7C4B646C sqloLioCollectNBlocks + 0×51C

  [5] 0xFFFFFFFF7ADD789C __1cWsqlbClnrCollectSomeAIO6FpnMSQdDLB_CLNR_CB_L_v_ + 0×74

  [6] 0xFFFFFFFF7ADD82D4 __1cVsqlbClnrCollectAllAIO6FpnMSQdDLB_CLNR_CB__v_ + 0×74

  [7] 0xFFFFFFFF7ADDAAFC __1cQsqlbClnrFindWork6FpnMSQdDLB_CLNR_CB__i_ + 0×11F4

  [8] 0xFFFFFFFF7ADDBAE8 __1cSsqlbClnrEntryPoint6FpCI_v_ + 0xD0

  [9] 0xFFFFFFFF7C4CE414 sqloEDUEntry + 0×3A4

DB2 subsequently went into a panic and the instance crashed. This happened for another database as well. We resolved the error by changing the stack size value in unlimit from 8K to “unlimited”.

As always, the answer to this is “it depends”! However, here are some basic rules of thumb that you may find useful:

• Define an application environment specifically for your Java stored procedures. This should have NUMTCB set to a relatively low value (6-8 is good) as Java stored procedures typically require a lot of storage. Java SPs also have their own JCL DD statement requirements, such as entries for JAVAENV and JSPDEBUG.
• Define another environment specifically for your REXX stored procedures. Only a single REXX SP can run in any given application environment at any one time, so you need to set NUMTCB=1 for these.
• Define an application environment specifically for stored procedures that need to run under RACF program control (some of the IBM-supplied SPs fall into this category, such as DSNACCUC). These SPs need their own environment: if you try to execute both program-controlled and non program-controlled SPs in the same environment you’ll receive message ICH420I and the controlled SPs will fail.
• Define an application environment to host all of the IBM-supplied stored procedures that don’t fall into one of the above categories.
• Define 1-n environments for your “normal” COBOL, PL/1, C or SQL stored procedures. NUMTCB for these can typically be set fairly high (40-50 isn’t uncommon).
• Finally, you may want to consider having one or more test environments for your normal procedures, but with NUMTCB=1. This forces procedures to be run individually, which can be very useful during debugging when your procedure writes messages to address space log. Failing to do this can result in several procedures interleaving their debug messages, which can get very confusing. A simple ALTER PROCEDURE statement is all that is required to change the application environment, but don’t forget to stop/start the procedure afterwards.

One of the major headaches all DB2 users face when upgrading to a new release is the possibility of access path regression. In order to benefit from enhancements to the optimiser, plans and packages need to be rebound under the new release. The vast majority of the time, this will result in the same or better access path being selected, but just occasionally DB2 may select a worse one and performance suffers.

IBM has delivered some very useful new functionality in the maintenance stream for DB2 9 for z/OS to help to address this issue. PK52523 provides some new options for REBIND that allow the old version of an access path to be stored. If regression occurs, the previous access path can be quickly and easily re-established with another REBIND. This will be a significant benefit for V8 customers moving to V9, removing one of the biggest migration pain points. This functionality will be further enhanced in future releases of DB2.

Ever needed to track down those pesky connections to a database you’re trying to backup?

No idea how to work out who is constantly connected to a database via Command Centre?

Well, below is a simple guide to finding out where those database connections originate from by converting the application handle from the list applications command to an ip address.

Suppose you’ve just run a db2 list applications for database sampledb and get the following output: -

Auth       Application    Appl.      Application Id                       

Id           Name            Handle                                            

user01   db2bp.exe     354        J66D5F7E.ID08.025CC3135357      

user02   javaw.exe      66         J66C89C5.D40D.023443140109    

 If you want to determine the ip address for user01, then follow the steps below: -

1.   Take the first part of the Application Id as the ip address and the second part as the port number.

      For user01 this will result in

      ip address = J66D5F7E

      port number = ID08

 2.   If the first character of either the ip address or port number is a value anything other than A,B,C,D,E or F then this needs to be converted as follows: -

      G = 0, H = 1, I = 2, J = 3, K = 4, L = 5, M = 6, N = 7, O = 8, P = 9

      For user01 we now have

      ip address = 366D5F7E

      port number = 2D08

 3.   Break the ip address into two character hex values and convert to decimal

      36  6D  5F  7E

      36 = 54

      6D = 109

      5F = 95

      7E = 126

      ip address = 54.109.95.126

4.   Finally convert the port number from hex to decimal

      2D08 = 11528

So for user01 the originating address is

      ip address = 54.109.95.126

      port number = 11528

 Following the same steps the address for user02 would be: -

      ip address = 54.108.137.197

      port number = 54285