dynamic sql snapshot vs static sql snapshot

sethwai

Hi,

I find the "snapshot for dynamic sql" a great tool for identifying
the most frequently executed dynamic sql statements, most cpu used,
etc. It is a great help for system tuning. My understanding is it
does not display static sql statements. Is a statement event monitor
the only way to capture the same information for static sql in
packages?

Lew

Sep 11 '06 #1

Subscribe Reply

3903

Mark A

<se*****@yahoo. comwrote in message
news:11******** **************@ d34g2000cwd.goo glegroups.com.. .

Hi,

I find the "snapshot for dynamic sql" a great tool for identifying
the most frequently executed dynamic sql statements, most cpu used,
etc. It is a great help for system tuning. My understanding is it
does not display static sql statements. Is a statement event monitor
the only way to capture the same information for static sql in
packages?

Lew

REBIND will re-explain packages that were created with the explsnap bind
option set to YES or ALL (indicated in the EXPLAIN_SNAPSHO T column in the
SYSCAT.PACKAGES catalog table entry for the package) or with the explain
bind option set to YES or ALL (indicated in the EXPLAIN_MODE column in the
SYSCAT.PACKAGES catalog table entry for the package). The Explain tables
used are those of the REBIND requester, not the original binder.

If the package was not bound with explsnap YES or ALL, you can do a BIND
REPLACE.

Then you can use the db2exfmt command to format reports from the explain
tables.

Sep 11 '06 #2

Pierre Saint-Jacques

Have you tried using the Activity monitor.
I t has capabilities to show you appls. and stmts with the metrics you
mention. To my knowledge, that report does not differentiate between static
or dynamic sql.
It shows most cpu used and tec.. for all.
HTH, Pierre.
PS: I don't remember if there's an equivalent command line to that tool.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<se*****@yahoo. coma écrit dans le message de news:
11************* *********@d34g2 00...legr oups.com...

Hi,

I find the "snapshot for dynamic sql" a great tool for identifying
the most frequently executed dynamic sql statements, most cpu used,
etc. It is a great help for system tuning. My understanding is it
does not display static sql statements. Is a statement event monitor
the only way to capture the same information for static sql in
packages?

Lew

Sep 11 '06 #3

sethwai

Hi Pierre,

It looks like the Activity monitor is going against the dynamic sql
cache so I don't believe it will have package statements. I guess I'll
have to set up a statement event monitor which traps both types of sql
statements.

Thanks.

Lew
Pierre Saint-Jacques wrote:

Have you tried using the Activity monitor.
I t has capabilities to show you appls. and stmts with the metrics you
mention. To my knowledge, that report does not differentiate between static
or dynamic sql.
It shows most cpu used and tec.. for all.
HTH, Pierre.
PS: I don't remember if there's an equivalent command line to that tool.

--
Pierre Saint-Jacques
SES Consultants Inc.
514-737-4515
<se*****@yahoo. coma écrit dans le message de news:
11************* *********@d34g2 00...legr oups.com...
Hi,

I find the "snapshot for dynamic sql" a great tool for identifying
the most frequently executed dynamic sql statements, most cpu used,
etc. It is a great help for system tuning. My understanding is it
does not display static sql statements. Is a statement event monitor
the only way to capture the same information for static sql in
packages?

Lew

Sep 12 '06 #4

Similar topics

3960

Snapshot Missing Dynamic SQL Total Execution Time

by: Jennie | last post by:

I need to collect dynamic SQL execution times for a production database. However, the totals are not being updated, despite setting the DFT_MON_TIMESTAMP on. The DFT_MON_TIMESTAMP is listed in the V8 documentation as configurable online. However, after setting the value on, the 'Total execution time' for dynamic SQL is still not collected. I tried resetting the monitors, etc., but the only thing that seemed to work is recycling DB2...

DB2 Database

14812

get snapshot for dynamic sql

by: Ina Schmitz | last post by:

Hello, I would like to get the logical and physical reads for every sql statement executed. Thatfore, I used the command "db2 get snapshot for dynamic sql on <mydatabase>". There, I could see the logical and physical reads from bufferpool for every statement monitored. Am I right? But now, I would like to read these results out of a table so that I could process them automatically. In which table(s) are the results of snapshot for...

DB2 Database

2976

Dynamic Arrays

by: Materialised | last post by:

Hi Everyone, I apologise if this is covered in the FAQ, I did look, but nothing actually stood out to me as being relative to my subject. I want to create a 2 dimensional array, a 'array of strings'. I already know that no individual string will be longer than 50 characters. I just don't know before run time how many elements of the array will be needed. I have heard it is possible to dynamically allocate memory for a 2

C / C++

1590

Snapshot problem for dynamic-sql: zero cpu counters

by: mike | last post by:

Problem: the snapshot output for dynamic SQL shows some SELECT statements executed many times but all three CPU counters (total/system/user) in the dynamic-sql-snapshot are zero, but most other SQL statements are correctly timed. Environment: DB2 v7.2 fp13 on AIX all 32-bit. At this site, a script executes once an hour which enables STATEMENT monitor switches, resets

DB2 Database

4422

Snapshot of databases = Hourly job

by: MPD | last post by:

Hi How can I create a job in sql agent to create a new snapshot every hour? I have, for eg a T-SQL that does it manually. create database Snapshotter_snap_20070418_1821 on ( name = Snapshotter, filename = 'c:\temp\Snapshotter_snap_20070418_1821.ss') as snapshot of Snapshotter

Microsoft SQL Server

5713

Dynamic statement cache on LUW

by: aj | last post by:

DB2 LUW 8.1 fixpak 14 Red Hat EL AS 4.4 I'm trying to diagnose some nocturnal CPU pressure, and am trying to understand the dynamic statement cache as it applies to LUW. The only doc/redbooks I am finding are for Z/OS, which I am completely ignorant of. I am using only Java and JDBC in my applications. No static SQL.

DB2 Database

1569

Snap.com and dynamic content

by: Razzbar | last post by:

I was wondering if anyone has experience with snap.com's service, where you put a reference to a remote script on your page. The script causes a small snapshot of a web page to appear wherever the user mouses over a link on your page. This is very simple to implement on a static page -- paste in one line of code. But what about when you have a dynamic page with content pulled in by AJAX requests? On a static page, the script is loaded...

Javascript

5673

db2 get snapshot for dynamic sql output question

by: aj | last post by:

DB2 LUW 8.1 FP14 RedHat AS In "db2 get snapshot for dynamic sql" output I see (amongst other things): Number of executions = 235 Number of compilations = 1 Worst preparation time (ms) = 4 Best preparation time (ms) = 4

DB2 Database

8953

FLUSH PACKAGE CACHE DYNAMIC

by: Damir | last post by:

Hello! I have noticed that after (sucessfully) executing the command: FLUSH PACKAGE CACHE DYNAMIC the dynamic SQL statement cache is not completely cleared (some of the dynamic SQL statement elements are not reset). For example, looking at the dynamic SQL snapshot I see a certain SQL statement that has been executed a number of times and has read a (big) number of rows. Then I execute the "FLUSH PACKAGE CACHE DYNAMIC" command.

DB2 Database

8485

What is ONU?

by: marktang | last post by:

ONU (Optical Network Unit) is one of the key components for providing high-speed Internet services. Its primary function is to act as an endpoint device located at the user's premises. However, people are often confused as to whether an ONU can Work As a Router. In this blog post, we’ll explore What is ONU, What Is Router, ONU & Router’s main usage, and What is the difference between ONU and Router. Let’s take a closer look ! Part I. Meaning of...

General

8930

Problem With Comparison Operator <=> in G++

by: Oralloy | last post by:

Hello folks, I am unable to find appropriate documentation on the type promotion of bit-fields when using the generalised comparison operator "<=>". The problem is that using the GNU compilers, it seems that the internal comparison operator "<=>" tries to promote arguments from unsigned to signed. This is as boiled down as I can make it. Here is my compilation command: g++-12 -std=c++20 -Wnarrowing bit_field.cpp Here is the code in...

C / C++

8828

Maximizing Business Potential: The Nexus of Website Design and Digital Marketing

by: jinu1996 | last post by:

In today's digital age, having a compelling online presence is paramount for businesses aiming to thrive in a competitive landscape. At the heart of this digital strategy lies an intricately woven tapestry of website design and digital marketing. It's not merely about having a website; it's about crafting an immersive digital experience that captivates audiences and drives business growth. The Art of Business Website Design Your website is...

Online Marketing

6238

Access Europe - Using VBA to create a class based on a table - Wed 1 May

by: isladogs | last post by:

The next Access Europe User Group meeting will be on Wednesday 1 May 2024 starting at 18:00 UK time (6PM UTC+1) and finishing by 19:30 (7.30PM). In this session, we are pleased to welcome a new presenter, Adolph Dupré who will be discussing some powerful techniques for using class modules. He will explain when you may want to use classes instead of User Defined Types (UDT). For example, to manage the data in unbound forms. Adolph will...

Microsoft Access / VBA

5704

Couldn’t get equations in html when convert word .docx file to html file in C#.

by: conductexam | last post by:

I have .net C# application in which I am extracting data from word file and save it in database particularly. To store word all data as it is I am converting the whole word file firstly in HTML and then checking html paragraph one by one. At the time of converting from word file to html my equations which are in the word document file was convert into image. Globals.ThisAddIn.Application.ActiveDocument.Select();...

C# / C Sharp

4227

Trying to create a lan-to-lan vpn between two differents networks

by: TSSRALBI | last post by:

Hello I'm a network technician in training and I need your help. I am currently learning how to create and manage the different types of VPNs and I have a question about LAN-to-LAN VPNs. The last exercise I practiced was to create a LAN-to-LAN VPN between two Pfsense firewalls, by using IPSEC protocols. I succeeded, with both firewalls in the same network. But I'm wondering if it's possible to do the same thing, with 2 Pfsense firewalls...

Networking - Hardware / Configuration

4417

Windows Forms - .Net 8.0

by: adsilva | last post by:

A Windows Forms form does not have the event Unload, like VB6. What one acts like?

Visual Basic .NET

2819

transfer the data from one system to another through ip address

by: 6302768590 | last post by:

Hai team i want code for transfer the data from one system to another through IP address by using C# our system has to for every 5mins then we have to update the data what the data is updated we have to send another system

C# / C Sharp

2062

How to add payments to a PHP MySQL app.

by: muto222 | last post by:

How can i add a mobile payment intergratation into php mysql website.

PHP