the DB2 server log

Dear all,

Right now we have problem on DB2 but as we are not DB2 person, we don't know what is going on.

please see attach using notepad ++, other tools can't open it.

Please suggest what did you see and how to fix it.

Currently we are using Toad for DB2 to connect to it and we are not Soarix user, please help.

DB2 veriosn 8.1
DB2LOG.txt
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Kent OlsenDBACommented:
Hi Marrowyung,

It looks like all of your issues are related to statistics and the the statistics collection failed due to a HEAP error.

Let's see how big the heap is.  Run these statements and post the results.

db2 connect to {databasename}
db2 get db cfg |grep HEAP



Kent
0
marrowyungSenior Technical architecture (Data)Author Commented:
Thanks for that.

but from Toad for DB2, what command I should do ?
0
Kent OlsenDBACommented:
Ohh..... Tougher question...  I don't have Toad for DB2 installed so I don't have a definitive answer.

Do you have the DB2 client installed on your desktop system?  If so, the path to the command line processor is probably:

  START / All Programs / IBM DB2 / Instance Name (DB2COPY1) / Command Line Tools / Command Line Processor

If you're on a unix or linux desktop, the command line processor should be available just by running the "db2" command (if the db2 profile was executed).

Kent
0
Introduction to Web Design

Develop a strong foundation and understanding of web design by learning HTML, CSS, and additional tools to help you develop your own website.

marrowyungSenior Technical architecture (Data)Author Commented:
"Do you have the DB2 client installed on your desktop system?  If so, the path to the command line processor is probably:"

we can see Toad as the native DB2 client interface.

"If you're on a unix or linux desktop, the command line processor should be available just by running the "db2" command (if the db2 profile was executed)."

we are not.

"START / All Programs / IBM DB2 / Instance Name (DB2COPY1) / Command Line Tools / Command Line Processor
"

let me check.
0
marrowyungSenior Technical architecture (Data)Author Commented:
I open a command prompt from Toad and I run that, it gives.

SQL0104N  An unexpected token "db2" was found following "BEGIN-OF-STATEMENT".
Expected tokens may include:  "SELECT".  SQLSTATE=42601

What is that mena ?
0
Kent OlsenDBACommented:
That probably means that you're already in the DB2 command line processor.  It's hard to say exactly where you are or the connection status in that window, so let's experiment.  :)

db2 connect to {databasename}
db2 get db cfg |grep HEAP

Those two command execute the db2 command line processor, run the command that's passes as a parameter, and exits.  If you're already in the CLP, you can do the same thing by just executing the command, though *grep* won't work unless you've installed it separately.  Try running this:

 get db cfg


Then search the output for any line with HEAP in it.  *grep* makes it easier as the commands that I first suggested would return something like this:

 Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = AUTOMATIC(7592)
 Sort list heap (4KB)                         (SORTHEAP) = AUTOMATIC(379)
 Database heap (4KB)                            (DBHEAP) = AUTOMATIC(1187)
 Utilities heap size (4KB)                (UTIL_HEAP_SZ) = 7262
 SQL statement heap (4KB)                     (STMTHEAP) = AUTOMATIC(2048)
 Default application heap (4KB)             (APPLHEAPSZ) = AUTOMATIC(256)
 Statistics heap size (4KB)               (STAT_HEAP_SZ) = AUTOMATIC(4384)

Open in new window

0
marrowyungSenior Technical architecture (Data)Author Commented:
I can't connect to it but someone do it for me, lucky:


Database heap (4KB)                            (DBHEAP) = 1334
Utilities heap size (4KB)                (UTIL_HEAP_SZ) = 15000
Percent of mem for appl. group heap   (GROUPHEAP_RATIO) = 22
Max appl. control heap size (4KB)     (APP_CTL_HEAP_SZ) = 1600
Sort heap thres for shared sorts (4KB) (SHEAPTHRES_SHR) = (SHEAPTHRES)
Sort list heap (4KB)                         (SORTHEAP) = 256
SQL statement heap (4KB)                     (STMTHEAP) = 6144
Default application heap (4KB)             (APPLHEAPSZ) = 256
Statistics heap size (4KB)               (STAT_HEAP_SZ) = 5555

what can you said by that ?
0
Kent OlsenDBACommented:
Cool.  The first error message indicated that the Statistics Heap size is too small.  That's the last item in that list.  I'd start by doubling that size.  Run this from the command line processor.

db2 connect to {database}
db2 update db cfg using STAT_HEAP_SZ 10000

You'll need system or dba privileges.


Kent
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
marrowyungSenior Technical architecture (Data)Author Commented:
but one thing, should we change other value ? it seem they all needs to be adjust because of that too.
0
marrowyungSenior Technical architecture (Data)Author Commented:
once doing this:

db2 connect to {database}
db2 update db cfg using STAT_HEAP_SZ 10000

restart is needed, right?

for V8.1, how to do it?
0
marrowyungSenior Technical architecture (Data)Author Commented:
Sorry just thing about one thing, once the vaule is change and DB2 restarted but we found out that the DB2 can't restart, what do it by that? game over, right?


how to restore it back? possible before DB2 start?

we have to try that..
0
Kent OlsenDBACommented:
Sorry....  Been away from the computer for a day or so...

What error are you getting when you try and restart DB2?
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Sorry....  Been away from the computer for a day or so..."

we are in holiday too, no worry, I am not in hurry now as after adding index, index and table reorg and runstats, the log seems good.

but in case the log still showing heap size too small error, you suggest to change it to :

"db2 connect to {database}
db2 update db cfg using STAT_HEAP_SZ 10000"

restart is needed, right? last time we did this and it solve memory problem temporoary as we don't change anything.

But based on some studies:
http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/admin/t0005243.htm


http://publib.boulder.ibm.com/infocenter/db2luw/v8/index.jsp?topic=/com.ibm.db2.udb.doc/core/r0001933.htm

what we try to do but didn't do is :

Preliminary calculations for existing configuration: 
ACTLHeap(old) = ( 100 - GROUPHEAP_RATIO ) / 100 * APP_CTL_HEAP_SZ = (100-70)/100 * 40480.= 12144
AppsInGroup = APPGROUP_MEM_SZ / APP_CTL_HEAP_SZ  = 40000/40480 = 0.98 (round up to 1)
AGSHeap = ( GROUPHEAP_RATIO / 100 ) * APPGROUP_MEM_SZ = (70/100) * 40000 = 28000

The element we want to change is ACTLHeap(new) and let’s assume that we increase the application control heap by 50 * 4K page :
ACTLHeap(new) = ACTLHeap(old) + ACTLHeap(delta) = 12144 + 50= 12194.

Open in new window


so restart is need and if the change of your suggest fuck up the DB2, anyway we can change it back?
0
Kent OlsenDBACommented:
DB2 Version 8 is pretty old.  It's at least 4 years out of support so we may need to resort to older tools/methods.  (And I have to remember them.....)

The simplest solution is to restore the database from your most recent backup.  If that will result in a loss of data, we'll look at other solutions.

Doesn't this database run on a Solaris server?  
-- Is it 32 or 64 bits?  
-- How much memory is on the system?
-- How big is the swap space?

What error are you getting when you try to start the database?

If you have DBA/sysadmin access, you may be able to run the Configuration Assistant.  It's one of the wizards that helps to set up the database(s).  The Configuration Assistant may be able to modify the configuration parameters with the database offline.  (I don't remember for sure.)
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Doesn't this database run on a Solaris server?  "

yes, it is version 9.

But I can't see how to know it is 64 bits or x86.

"-- How much memory is on the system?
-- How big is the swap space?"

how to find out ?

"What error are you getting when you try to start the database?"

no, what I want to knows is, WHAT if it has problem after changing the configuration. I haven't change it yet.

so some preparation is needed.

"
If you have DBA/sysadmin access, you may be able to run the Configuration Assistant"

But the problem is, the DB is offline already and should be no way to reconnect to it ?

One thing, changing only one parameter do not need to adjust the others setting ? you can see what I prepared above.
0
Kent OlsenDBACommented:
I suppose it's possible to set the heap size so large that DB2 won't start, but I've never heard of it happening and a quick search returns nothing to match that description.

Here's the first message in the error log:

2013-09-14-14.14.55.949820+000 I79435050C508      LEVEL: Error
PID     : 7261                 TID  : 5382        PROC : db2hmon
INSTANCE: db2inst6             NODE : 000
APPID   : *LOCAL.db2inst6.130914141459
FUNCTION: DB2 UDB, Automatic Table Maintenance, AtmTable::runstats, probe:600
MESSAGE : ZRC=0xFFFFF6F8=-2312
DATA #1 : <preformatted>
AutoStats: Non zero (-2312) return code from Runstats
SQL2312N  Statistics heap size is too small to carry out the operation. 
Suggested heap size is "5999" pages.

Open in new window


Note the last two lines.  The error is that the " Statistics heap size is too small".  No issue(s) with the other heaps are reported, so the solution is to increase the size of the statistics help.  That should result in 1)  The problem corrects and the tasks runs normally;  2)  This problem corrects and a different error is reported; or 3)  The process fails again because the heap is still not large enough.

Earlier I suggested that you double the heap size.  Based on the suggestion from DB2, increase it to 5999.

db2 connect to {database}
db2 update db cfg using STAT_HEAP_SZ 5999

Open in new window


Kent
0
marrowyungSenior Technical architecture (Data)Author Commented:
"Earlier I suggested that you double the heap size.  Based on the suggestion from DB2, increase it to 5999"

so we only need to do this:

Earlier I suggested that you double the heap size.  Based on the suggestion from DB2, increase it to 5999.

Open in new window


so configure the rest:

ACTLHeap(old) = ( 100 - GROUPHEAP_RATIO ) / 100 * APP_CTL_HEAP_SZ = (100-70)/100 * 40480.= 12144
AppsInGroup = APPGROUP_MEM_SZ / APP_CTL_HEAP_SZ  = 40000/40480 = 0.98 (round up to 1)
AGSHeap = ( GROUPHEAP_RATIO / 100 ) * APPGROUP_MEM_SZ = (70/100) * 40000 = 28000

The element we want to change is ACTLHeap(new) and let’s assume that we increase the application control heap by 50 * 4K page :
ACTLHeap(new) = ACTLHeap(old) + ACTLHeap(delta) = 12144 + 50= 12194. 

Open in new window


is not necessary ?

so we just do what you suggest and follow by restart?
0
Kent OlsenDBACommented:
Unless you encounter other issues, I see no need to change the other configuration items.  Only this one parameter (metric) affects the statistics heap size.


Kent
0
marrowyungSenior Technical architecture (Data)Author Commented:
ok, then restart ?

one more thing, anyway to find out the log sent you from inside DB2 by running script ?
0
Kent OlsenDBACommented:
Yep.  Then restart.

You should be able to capture the last *n* rows from the db2log file with a single unix command.  This will get the last 200 rows.


  tail -n 200 {\path\}db2log

Redirect that to any file that you want, then edit, mail, etc the file.


Kent
0
marrowyungSenior Technical architecture (Data)Author Commented:
thanks:

"tail -n 200 {\path\}db2log"

how can I get the \path\ from within DB2 UI ?

Can this "tail -n 200 {\path\}db2log" run from UI ?
0
Kent OlsenDBACommented:
That's a unix command.  You'll have to run it from a command shell.  

I don't think that the UI has a command shell interface.


And a better approach is to use the *db2diag* command to format the log.  (Sorry, but using *tail* requires that the log already be formatted.  Not the best advise that I've ever given.)
0
marrowyungSenior Technical architecture (Data)Author Commented:
db2diag again must be from Solaris command prompt?
0
Kent OlsenDBACommented:
Yep.  It runs from the command line, not within the DB2 command line processor.
0
marrowyungSenior Technical architecture (Data)Author Commented:
very hard to see why we can't get the DB2 log from within DB2 console .
0
Kent OlsenDBACommented:
The customary database tools and protocols don't apply.  The log doesn't resemble a tablespace, table, or row so SQL operations would have no idea what to do with it.

And when you think about it for a few seconds, it makes sense.  The log can record all kinds of events, from trivial information to catastrophic errors.  If there's a severe error in the database, it may not be able to write to a database structure so using lower level I/O to write sequentially to a file may be the operation most likely to succeed.


Kent
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
DB2

From novice to tech pro — start learning today.