0

I am trying the db2diag command to get the all the logs ,captured in diaglog, containing a particular SQLCODE. Can any one help me with the command?

Kalpna Kashyap
  • 11
  • 5
  • Henrik's response below is right on the money. Out of curiosity, what do you plan to do with this data? If you want to keep this for the purpose of root cause analysis, it is always better to save or examine the entire db2diag.log. Sometimes problems begin far before they result in an SQL code, and the preceding diagnostic messages might be quite important. – pavel_sustr Aug 25 '15 at 15:33
  • The problem is my Db got crashed saying No storage available in Database Heap Memory.And as per IBM its a bug in 10.1 and they hv gvn fix in 10.5. And in order to avoid this DB crash in future I am planning to hv one script to monitor my diaglog and as it will see that any error like this has been captured, it will alert me. But the problem is eveytym this error got captured, am not able to find anything common in that using which I can generalize the command. Thats wat i am looking for. A generalized command for monitoring this error. – Kalpna Kashyap Aug 25 '15 at 16:20
  • If the return code is always the same (such as SQLO_NOMEM or something similar), you can also do a simple string search using `db2diag` or simple `grep`. – pavel_sustr Aug 25 '15 at 18:30
  • Thanks pavel_sustr, this grep option worked for me. – Kalpna Kashyap Aug 26 '15 at 18:35

1 Answers1

2

Use the db2diag command and filter the DATA section for "sqlcode" followed by the particular code:

db2diag -g 'data:=sqlcode: -1063' would search for the SQLCODE -1063 (error SQL1063N).

You probably have the full documentation of the db2diag tool. You could then format the output and extract only the part of the log records you need.

data_henrik
  • 16,724
  • 2
  • 28
  • 49
  • I have one more problem. There is a database heap memory full issue in my db and because of that once DB got crashed and once backup also failed. And the errors that got captured are showing No storage available in database heap memory. But the details for all the errors are different and I need to have one generalized command to fetch all the errors of level Severe and saying that No storage available in Database heap memory, so that i can monitor my diaglog and i can avoid Db crash in future. Can U plz help me ? – Kalpna Kashyap Aug 25 '15 at 16:09
  • Ask a new question and provide details of what you tried so far. And mark this as complete. – data_henrik Aug 25 '15 at 16:20
  • i tried this command: db2diag -g -rc:ret_code level=Severe n=000; But it didnt return anything – Kalpna Kashyap Aug 25 '15 at 16:21
  • db2diag -g -msg:=SQL9001N level=Severe n=000; But it didnt return anything – Kalpna Kashyap Aug 25 '15 at 16:41
  • I tried grepping for a particular word and that was the only option left i guess. – Kalpna Kashyap Aug 26 '15 at 18:34
  • In your previous attempts with db2diag you searched for "SQL9001N". Do you wanted "SQL0901N"? That would explain why there is no result. – data_henrik Aug 26 '15 at 18:55
  • I tried in other ways also to get a particular error which has been captured many times. But cudnt find anything. So finally i went for grep and it worked – Kalpna Kashyap Aug 27 '15 at 05:36