Collecting data: DB2 Stored Procedures Problems

Technote (FAQ)


Question

MustGather information for DB2® for Linux, UNIX®, and Windows® stored procedure related problems. Gathering this information before calling IBM® support helps familiarize you with the troubleshooting process and saves you time.

Cause

This section lists questions regarding the conditions under which the problem occurred.

  • Are you able to reproduce this behavior from command line? If so, can a testcase be provided?
  • Have there been any recent changes to the file system?
  • Have there been any recent changes to the instance or the database ?
  • Has it worked before and suddenly stop working or you just newly installed the stored procedure?
  • Is the problem intermittent?
  • What type of stored procedure it is ? The answer includes C stored procedure, Java stored procedure, COBOL stored procedure, SQL stored procedure and build-in stored procedure...
  • Is it possible to provide the definition of stored procedure and binary library if there is any ?

Answer

Since stored procedure is running on server side, it is always recommended to test the stored procedure from command line on server side first. Successfully running on server side rules out stored procedure problem.



Please collect the following data based on the different types of stored procedure problem you are encountering:


Stored procedure returns error code

1. DB2 trace on server side even if the stored procedure is invoked from client side.

    1. db2trc on -t -f server.dmp  
    2. <recreate the problem>
    3. db2trc off
    4. db2trc fmt server.dmp server.fmt; db2trc flw -t server.dmp server.flw
1a. If regular trace is not workable due to large size, try trace the return code
    1. db2trc on -t -rc <return code>
    2. <recreate the problem>
    3. db2trc dmp server.dmp
    4. db2trc off
    5. db2trc fmt server.dmp server.fmt; db2trc -t flw server.dmp server.flw

2. Db2support information from server side
    • db2support . -g -s -a

3. Definition of stored procedure (CREATE PROCEDURE...statement)

4. Additional information related to external stored procedure

Java stored procedure
    • $JDK_PATH/jre/bin/Java -version  #JDK_PATH is a parameter defined in DBM CFG
    • set >set.out
    • ls -lr /usr/lib  (Linux only)
    • set DIAGLEVEL to 4, some JVM related information will be dumped to db2diag.log.

C stored procedure
    • library for stored procedure
    • C compiler version

Cobol stored procedure
    • Name and version of COBOL compiler
    • Compiling and linking option

Note: It is recommended to recreate the problem in DEV/TEST system if possible. Depending on how busy the system, the trace can become huge. Try to take trace at non-peak time to reduce both the performance overhead and the trace file size.


SQL stored procedure performance issue (no error code)

1. Take the SQL statement out and run it from CLP, is it slow ?

2. Is statistics updated? If not, rebind package or recreate the stored procedure after REORG, RUNSTATS. Consider using REOPT ONCE/ALWAYS on rebind packages / recreate of the stored procedure. To find the corresponding package name for a SQL stored procedure, see How to map stored procedure names to packages after upgrading to DB2 UDB Version 8.2 or higher

3. DB2 perfcount trace
    1. db2trc on -perfcount -t 
    2. <recreate the problem>
    3. db2trc dmp server.dmp
    4. db2trc off
    5. db2trc perffmt server.dmp server.perfmtrc

DB2 perfcount trace will count the frequency of each function and add up the CPU time spend on each function. It will be less useful if it is not running in a dedicated system.

4. DB2 trace
    1. db2trc on -t -f trace.dmp 
    2. <recreate the problem>
    3. db2trc off
    4. db2trc fmt server.dmp server.fmt; db2trc flw -t server.dmp server.flw
5. Collect stack information
    • db2pd -stack all -repeat <interval>
    • db2pd -stack <pid> -repeat <interval>  
    #if specific PID is found

6. db2support . -g -s -a  

7. Collect new access plan after rebind and recompile.  For instructions see Collecting explain data for SQL stored procedures in DB2



Non-SQL stored procedure performance issue (no error code)

An easy method to collect everything output to the console without redirecting the output to a file is to run the "script" command. The output is then written to a file called "typescript"

Example:
$ script
Script command is started. The file is typescript.

<Execute commands>

$ (Ctrl-D when done)
Script command is complete. The file is typescript.

1. Collect thread level stack information
    AIX and Linux: procstack <pid_fmp>
    Solaris and HP-UX: pstack <pid_fmp>

2. Collect thread level CPU usage

AIX: ps -mp <pid_fmp> -o THREAD,rssize,vsz
Solaris: ps -Le -o pid,user,s,lwp,pcpu,pmem,vsz,args
Linux: ps -Lf <pid_fmp>
HP-UX: Please check with your system administrator

3. Collect FMP memory information
    1. db2pd -fmp -alldatabases -applications -alldatabases -dynamic -edus-repeat <interval>
    2. db2pd -memb fmp -repeat <interval>
    3. db2pd -fmpexechistory (only available on V9.5 FP5 or V9.7 FP1 above)

4. db2support . -g -s -a  

What to do next
Once you have collected the preceding information, submit the diagnostic information to IBM support.

Related information

Additional Collecting Data documents

Rate this page:

(0 users)Average rating

Document information


More support for:

DB2 for Linux, UNIX and Windows

Software version:

9.5, 9.7, 10.1, 10.5

Operating system(s):

AIX, HP-UX, Linux, Solaris, Windows

Reference #:

1383251

Modified date:

2009-08-11

Translate my page

Machine Translation

Content navigation