How to Identify Query/Queries in Patricular Time

[former_member618864]

Hi All,

We are facing performance issues on ECC EHP6 running on DB2 10.1 Database. From last few days we are continously ecountering huge number of dumps " DBIF_RSQL_ERROR". As, per dump analysis I able to understand some sql statments are too long/complex/expensive, due to this system is unable to allocate heap memorry. We don't have sufficient hardware to increase heap memory any more.. I able to see huge number of dumps are generating around 10:00--11:00 my System time..

My questions are:

1)  How would i able to identify queries/query running on SAP System from 10:00--11:00?

2)  After identifying query, how would we able to identify which user is trigeering it?

Thanks

Regars,

Raj

[former_member618864 · Accepted solution]

Hi,

Thanks all for your tremendous support and quick turn around.. As, i stated in first message problem is with the STMM Heap memory in db2 cfg parameters we have already set value to auto.. Even though it is taking 8192 .

SQL statement heap (4KB)                     (STMTHEAP) = AUTOMATIC(8192)

Which is not sufficient in our case, we plan to increase instance memroy. Thanks.

Regards,

Raj

[Former Member]

Hi Raj,

can you please share/attach the dump details. to check the error analysis.

Regards,

Rajshekar

[former_member618864]

HI RajSekhar,

Please find the attachment. FYI.. as i have been telling in the message thread.. only DUMP is same program /user is varying..

dump.txt.zip

[Former Member]

Hi Raj,

Note 1429354 - DBIF_RSQL_SQL_ERROR during package compilation on DB6

please go through the note, might helpful.

Regards,

Rajshekar

[Sriram2009]

Hi Raj

I think your DB2 heap size too small kindly increase the heap by using the IBM like

IBM SQL0101N error message appears in DB2CLI log - United States

IBM SQL0101N the statement is too long or too complex. SQLSTATE=54001 - United States

BR

SS

[Sriram2009]

Hi Raj

1)  How would i able to identify queries/query running on SAP System from 10:00--11:00?
2)  After identifying query, how would we able to identify which user is trigeering it?

For you both query's you can use the transaction code ST03/ST03N. refer the SCN link about ST03

Workload Monitor (ST03 or ST03N) - MaxDB - SCN Wiki

Check the time profile - refer the link http://www.sap-perf.ca/run-sap-st03n/

2. Could you share the dump details as attachment? & SAP SP & OS/DB versions?

BR

SS

[former_member618864]

Hello Sriram,

Thanks for the information.. My query is i don't wont to identify the reponse times.. I need to identify the SQL Queries running on particular point of time. Thanks

BR

Raj

[Sriram2009]

Hi Raj

1. During the time when there are lot of dumps as you mentioned, you can enable the trace by using the transaction code ST12, and then check the over all usage of ABAP & DB,

2. If you know the transaction code which is having performance issue you can check that explicitly from  ST12.

3. You can find the transaction or program which is causing the dumps from the dump analysis itself in the option "Users and transactions", and put the trace on that program. Once you get the trace result, analyse it to find which statement appears to be expensive.

4. ST03 or ST03n also will be of great help for the analysis.

BR

SS

[former_member618864]

Hello Sriram,

1. I am having strange problem with Abap Dump/runtime Error.. It is not specific to one or two users.. Dump is getting generated for all the users who has logged in to system @ partcular time and programs is also different from user to user.

2.  In order to enable the trace,

runtime error is not specific to user/ program what can be done,  is there a best possibility to identify?

3. Dumps is not specific to single program/user.

How we have option from dbacockpit to identify expensive sql statments in sap system.  Do, we have a way/option were we can identify queries had run on specific time.. Ex; I want all  SQL queries that had run in my system let's say 10:00 -- 11:00.. from SAP Txn code.

Thanks for reply and much appreciated.

Regards,

Raj

[Sriram2009]

Hi

1. You have to download the full dump as text format and then attach, with snapshot we can't find any think. & You are in which OS ?

2. Since the DB2 DB any one having database level access? like querying the table at database level?

3. You said that you have find the expensive sql statement from DBACOCKPIT which is pointing to any report or Z transaction?

BR

SS