DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data sharing

KAPIL MATHUR

DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data sharing

Hi DB2-L members,

I am in a DB2 11 for z/OS 4-way data sharing shop and I am looking for documentation and your shop's experience with defining indexes with index columns defined in "RANDOM" order (not ASC or DESC !) to alleviate insert hotspots that develop with very high insert rates from multiple members in data-sharing environments.

The DB2 syntax is valid but DB2 11 'Managing Performance' Guide is silent on this topic.
Given that this feature has existed DB2 10 onwards, I am looking for feedback from anyone who actually tried it successfully.

I found a reference to it a 2011 presentation by John Campbell titled 'DB2 for z/OS Best Practices - Optimizing Insert Performance - Part 2' on a slide on topic Randomised Index Key but apart from the usage and limitations, it also mentions:-
Careful trade-off required between lock contention relief and additional getpages, read/write I/Os, and increased number of lock requests
This type of index can provide dramatic improvement or degradation!
Recommend making randomized indexes only when bufferpool resident

Is there any documentation on how random indexes are internally organized - what are their runtime overheads and performance tradeoffs?
Is there any IBM documentation on how to try them out and identify the "dramatic improvement or degradation" (Class 2 times)?
Have you used this feature and succeeded in reducing insert hotspots in your data-sharing environment?
Is this feature/solution superseded by Fast-Traverse-Block indexes in DB2 12 ?

I am grateful for feedback regarding your opinions/ experiences or for any additional related IBM documentation you could point me to. Thanks

alain pary

RE: DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data sharing
(in response to KAPIL MATHUR)

hello, 

do you have already activated all the specific options to help in this situation like  

MEMBER CLUSTER

TRACKMOD NO 

you can also create an affinity with your datasharing memeber and a physical partition , but can impact also your select acticity upon the tables 

regards 

Alain Pary

KAPIL MATHUR

RE: DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data sharing
(in response to alain pary)

Thanks Alain,

Yes - I have MEMBER CLUSTER already set & TRACKMOD NO is also set. ? 

Daniel Luksetich

DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data sharing
(in response to KAPIL MATHUR)
I have found from personal experience at multiple customer locations
that highly random anything is a very bad idea. "Hotspots", in my
opinion is an outdated concept. If you have DASD that is less than 10
years old then you should be taking advantage of all the DASD has to
offer for improving insert performance. As far as Db2 for z/OS, I have
found that the secret to success, if you DO NOT have a sequential insert
strategy for your index, is to make sure that there is enough PCTFREE
for the index as to avoid page splits in between REORGs.

Here it is in a sentence:
Set your PCTFREE such that you avoid page splits in between index
REORGs, balancing free space with index REORG frequency.

That's the key....

I hope I made my point. I've done 56,000 inserts per second on a
nine-way.

cheers,
Dan

On 2018-03-05 00:24, KAPIL MATHUR wrote:
> Hi DB2-L members,
>
> I am in a DB2 11 for z/OS 4-way data sharing shop and I am looking for
> documentation and your shop's experience with defining indexes with
> index columns defined in "RANDOM" order (not ASC or DESC !) to
> alleviate insert hotspots that develop with very high insert rates
> from multiple members in data-sharing environments.
>
> The DB2 syntax is valid but DB2 11 'Managing Performance' Guide is
> silent on this topic.
> Given that this feature has existed DB2 10 onwards, I am looking for
> feedback from anyone who actually tried it successfully.
>
> I found a reference to it a 2011 presentation by John Campbell titled
> 'DB2 for z/OS Best Practices - Optimizing Insert Performance - Part 2'
> on a slide on topic Randomised Index Key but apart from the usage and
> limitations, it also mentions:-
> Careful trade-off required between lock contention relief and
> additional getpages, read/write I/Os, and increased number of lock
> requests
> This type of index can provide dramatic improvement or degradation!
> Recommend making randomized indexes only when bufferpool resident
>
> Is there any documentation on how random indexes are internally
> organized - what are their runtime overheads and performance
> tradeoffs?
> Is there any IBM documentation on how to try them out and identify the
> "dramatic improvement or degradation" (Class 2 times)?
> Have you used this feature and succeeded in reducing insert hotspots
> in your data-sharing environment?
> Is this feature/solution superseded by Fast-Traverse-Block indexes in
> DB2 12 ?
>
> I am grateful for feedback regarding your opinions/ experiences or for
> any additional related IBM documentation you could point me to. Thanks
>
> -----End Original Message-----
>
>
> Links:
> ------
> [1] https://www.idug.org/p/fo/st/?post=185043&anc=p185043#p185043
> [2] https://www.idug.org/p/fo/si/?topic=19
> [3] https://www.idug.org/p/us/to/

Daniel Luksetich

DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data sharing
(in response to alain pary)
MEMBER CLUSTER is for table spaces.
Dan

On 2018-03-05 02:21, alain pary wrote:
> hello,
>
> do you have already activated all the specific options to help in this
> situation like
>
> MEMBER CLUSTER
>
> TRACKMOD NO
>
> you can also create an affinity with your datasharing memeber and a
> physical partition , but can impact also your select acticity upon the
> tables
>
> regards
>
> Alain Pary
>
> -----End Original Message-----
>
>
> Links:
> ------
> [1] https://www.idug.org/p/fo/st/?post=185044&anc=p185044#p185044
> [2] https://www.idug.org/p/fo/si/?topic=19
> [3] https://www.idug.org/p/us/to/

Michael Hannan

RE: DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data sharing
(in response to KAPIL MATHUR)

In Reply to KAPIL MATHUR:

This type of index can provide dramatic improvement or degradation!
Recommend making randomized indexes only when bufferpool resident

Is there any documentation on how random indexes are internally organized - what are their runtime overheads and performance tradeoffs?
Is there any IBM documentation on how to try them out and identify the "dramatic improvement or degradation" (Class 2 times)?
Have you used this feature and succeeded in reducing insert hotspots in your data-sharing environment?
Is this feature/solution superseded by Fast-Traverse-Block indexes in DB2 12 ?

I am grateful for feedback regarding your opinions/ experiences or for any additional related IBM documentation you could point me to. Thanks

Thanks to Daniel L re the "hot spots" can be an outdated concept. Randomness is generally bad. Pseudo random is also bad. For fast retrieval with a minimum of Getpages, you want all the data rows related to the customer (as an example) clustered as closely together as possible. You do not want business related rows spread randomly to "all ends of the galaxy" (putting it in emotive terms to understand how I feel about it).

Db2 minimises Getpages by index lookaside and similarly for data pages. Randomness defeats Db2 's best efficiency measures and makes it perform as badly as other random organisation DBMSes.

So "random" column in an index is a bit of a curiosity that I hardly ever recommend. As you all know well, I hope, type 2 index pages are not locked, the underlying data rows (or pages) are locked when updating, inserting, deleting.

Indexes experience latch (very brief) contention on the local member, and also Page p-lock (for index page consistency) contention between data sharing members. When one member needs to update an index page it signals other members to release that page if necessary. The member does not need to retain p-locks till commit time, instead retains control till some other member needs that page.

I am recommending new indexes in Data Sharing all the time. It is very rare for these to cause a major contention issue, as they group and sequence data according to business needs. Picking next sequential key off a list to use, is a special case.

So looking at random columns in an index, what happens? Db2 uses a hashing function on the randomised columns to put them in the index in a randomised place. This is only likely to be a useful thing in extreme cases where new key values were allocated in sequential sequence.

The disadvantages of the randomised columns are strong. You can find the index entries using Equals predicates only. Range predicates will not be able to find the index entries due to the hashing algorithm. You cannot use the index to ORDER BY the data. You can still use it for GROUP BY.

Randomised indexes is likely to cause increased Getpages and I/Os, a bad thing, unless all probes on the index are very random in any case.

Index Scans will return entries in sequence grouped by the individual (hashed) values, but not in order.

In my tuning work, I am sometimes needing to look at ways to decrease Getpages in queries by undoing the spread caused by single column randomised cluster keys on the tables with very large numbers of rows. So I look to change the cluster index to be something else, then see if possible to get rid of the single column random index completely.

"Balanced" partitioning of tables using a randomised key with no business meaning is also a really bad thing. I often seek to un partition a table and return it to Partitioned by Growth, so that a good cluster index will not be spoiled by the partitioning scheme.

Generally the data update contention problem is on data pages, spacemap pages, etc. There are ways to improve that others have mentioned. Randomised columns in an index is probably not the answer to 99% of the problems.

I really wonder how often a ramdomised column in an index is really needed? I would indeed love to hear some very special cases where this was not a "solution looking for a problem". I am sure it is useful but rarely.

So randomised key column could be a solution to a severe problem, but no one is going to start with this as a default, before determining it is really necessary. 

In most sites and most tables, querie costs on a table out weigh the cost of Insert to the table and it's indexes, by large margins. So we generally tune for best query performance. Huge tables with heavy Insert and low on search queries maybe a special case and worth tuning index freespace carefully as Dan mentions. 5 to 10 for index PCTFREE is a good default if index entry inserts are spread out. No need to use FREEPAGE in the modern day. We look to Bufferpools to keep I/Os down.

Basic techniques to avoid contention:

* Use WITH UR on queries wherever possible. It's not a "dirty read" at all.

* Consider Row Level Locking

* Make sure access paths are well tuned with good indexes. If do not touch a row at all, then it won't cause a lock.

* Use multi-column keys for "child" tables to provide grouping of related data rows. Some higher level key values may be semi random like customer number, but not stored with "random" sequence in indexes.

* Sequential Key allocation schemes like a Timestamp may cause contention if table is clustered by that key and page level locking used, however Inserters look for an unlocked page to use. So it's updates causing more problems. If clustered by something else, that helps, or use Member Cluster and Insert Algorithm 2 in Db2 12. Indexes with sequential keys can be tolerated a lot more in the modern day, although high levels of Insert spread across more than one member of Data Sharing will cause index and spacemap page p-lock (for page consistency) contention over ever ascending key values. If heavy Insert is in batch, run the parallel jobs all on one member will be very helpful. Single column random key clustering on large tables can be worst for performance by costing extra Getpages and I/Os.

Ramdomised columns may not be available for Db2 12 Fast Traversal Block. I did not check the manuals. Anyway it is not a superceeding technique.

I do not use randomised column indexes for my tuning work! Forget them until you have a very specific need, and be sure they are in fact the right solution (which is unlikely). I don't actually know what Db2 stores in the index key for a randomised column. Perhaps stores the original value prefixed by a hashed randomiser of length ???. Has anyone looked to see what is stored?

I have recommended Hash organised tables, a different thing where Db2 does not build an index tree at all for Hash key access. This removes all Index Getpages when access is always random. Normal Indexes can still be created. Most sites are not implementing Hash organised tables, unfortunately due to not supported by the change management software. Ramdomised columns in an index is probably a poor man's alternative to Hash Organisation, with less benefit (since Index Getpages not eliminated).

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Mar 06, 2018 - 03:25 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2018 - 03:45 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2018 - 03:52 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2018 - 04:07 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2018 - 04:12 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2018 - 05:10 AM (Europe/Berlin)

KAPIL MATHUR

RE: DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data sharing
(in response to Michael Hannan)

Thanks a bunch Michael for taking the time to share the knowledge. 

This is, by far, the most detailed cogent answer that I have received to any question on this forum. I am sure it will be extremely useful knowledge, over time, for the other list members as well.  

Michael Hannan

RE: DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data sharing
(in response to KAPIL MATHUR)

I happened to be not busy (raining heavy at the organic vege farm) and Randomisation is one of my most favourite subjects. I like to broaden the topic at times. LOL

As an after thought, I used the term "contention" to mean waits. Not to mean Timeout or Deadlock. Data Sharing p-locks cause global wait for a negotiation, but it the resource will be obtained in due course. If you are not in Data Sharing, Index Contention is just not likely to be an issue (merely wait for latches). Data Page contention is possible. So be careful how you cluster data.
 
In Reply to KAPIL MATHUR:

Thanks a bunch Michael for taking the time to share the knowledge. 

This is, by far, the most detailed cogent answer that I have received to any question on this forum. I am sure it will be extremely useful knowledge, over time, for the other list members as well.  

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Mar 06, 2018 - 06:52 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2018 - 06:53 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 06, 2018 - 07:01 AM (Europe/Berlin)

Walter Janißen

AW: DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data shar
(in response to Michael Hannan)
Hi

Probably Fast Traversal can greatly improve the use of random indexes, because it avoids the number of getpages and unclustered indexes are the best use case here.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Michael Hannan [mailto:[login to unmask email]
Gesendet: Dienstag, 6. März 2018 03:19
An: [login to unmask email]
Betreff: [DB2-L] - RE: DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data sharing


In Reply to KAPIL MATHUR:

This type of index can provide dramatic improvement or degradation!
Recommend making randomized indexes only when bufferpool resident

Is there any documentation on how random indexes are internally organized - what are their runtime overheads and performance tradeoffs?
Is there any IBM documentation on how to try them out and identify the "dramatic improvement or degradation" (Class 2 times)?
Have you used this feature and succeeded in reducing insert hotspots in your data-sharing environment?
Is this feature/solution superseded by Fast-Traverse-Block indexes in DB2 12 ?

I am grateful for feedback regarding your opinions/ experiences or for any additional related IBM documentation you could point me to. Thanks

Thanks to Daniel L re the "hot spots" can be an outdated concept. Randomness is generally bad. Pseudo random is also bad. For fast retrieval with a minimum of Getpages, you want all the data rows related to the customer (as an example) clustered as closely together as possible. You do not want business related rows spread randomly to "all ends of the galaxy" (putting it in emotive terms to understand how I feel about it).

Db2 minimises Getpages by index lookaside and similarly for data pages. Randomness defeats Db2 's best efficiency measures and makes it perform as badly as other random organisation DBMSes.

So "random" column in an index is a bit of a curiosity that I hardly ever recommend. As you all know well, I hope, type 2 index pages are not locked, the underlying data rows (or pages) are locked when updating, inserting, deleting.

Indexes experience latch (very brief) contention on the local member, and also Page p-lock (for index page consistency) contention between data sharing members. When one member needs to update an index page it signals other members to release that page if necessary. The member does not need to retain p-locks till commit time, instead retains control till some other member needs that page.

I am recommending new indexes in Data Sharing all the time. It is very rare for these to cause a major contention issue, as they group and sequence data according to business needs. Picking next sequential key off a list to use, is a special case.

So looking at random columns in an index, what happens? Db2 uses a hashing function on the randomised columns to put them in the index in a randomised place. This is only likely to be a useful thing in extreme cases where new key values were allocated in sequential sequence.

The disadvantages of the randomised columns are strong. You can find the index entries using Equals predicates only. Range predicates will not be able to find the entries due to the hashing algorithm. You cannot use the index to ORDER BY the data. You can still use it for GROUP BY.

Randomised indexes is likely to cause increased Getpages and I/Os, a bad thing, unless all probes on the index are very random in any case.

Index Scans will return entries in sequence grouped by the individual (hashed) values, but not in order.

In my tuning work, I am sometimes needing to look at ways to decrease Getpages queries by undoing the spread using single column randomised keys on the tables with very large numbers of rows.

"Balanced" partitioning of tables using a randomised key with no business meaning is also a really bad thing. I often seek to un partition a table and return it to Partitioned by Growth, so that a good cluster index will not be spoiled by the partitioning scheme.

Generally the data update contention problem is on data pages, spacemap pages, etc. There are ways to improve that others have mentioned. Randomised columns in an index is probably not the answer to 99% of the problems.

I really wonder how often a ramdomised column in an index is really needed? I would indeed love to hear some very special cases where this was not a "solution looking for a problem". I am sure it is useful but rarely.

So randomised key column could be a solution to a sever problem, but no one is going to start with this as a default, before determining it is really necessary.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Steven Lamb

RE: AW: DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data shar
(in response to Walter Janißen)

Again, there's APPEND YES on the table to consider if it is the performance of the Inserts that you are concerned about. FREEPAGE and PCTFREE have already been mentioned - we use zero for both on some tables with high Insert rates - MCA00 as it's "affectionately" known here".

 

Regards,

Steve

Michael Hannan

RE: AW: DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data shar
(in response to Walter Janißen)

In Reply to Walter Janißen:

Probably Fast Traversal can greatly improve the use of random indexes, because it avoids the number of getpages and unclustered indexes are the best use case here.

At this point we are well diverted from the topic of "Index Hotspots" (implying heavy sequential inserts)!

We have to be careful about use of the word random here. FTBs (Fast Traverse Block for index non-leaf pages) are indeed best suited to indexes with random access patterns but not related to columns ordered "Random" in the index definition, since random sequenced columns are rarely used. Indexes with very much sequential scan access patterns will not be chosen for FTB (in Db2 12), because the "random" probe type access benefits (The automation does not want to waste the memory used for FTB). Benefit is by avoiding Getpages on upper levels of index. The Performance Red Book indicates that this is an alternative to Hash Organisation of the table. Hash Organisation is actually better for very random probe type access as the access is without Index Getpages. However many sites did not use Hash Organisation where they could benefit from it.

I am not sure that indexes being "Unclustered" matters at all for FTB. Some Unclustered Indexes maybe providing "index only" access in important queries, in order to keep total Getpages down, but that would be for scanning rather than simple probe situations. I do see that Cluster Indexes are much more likely to be used for Sequential processing of the data, provided that that clustering index was well chosen. However we do see many site tables make a single column primary key, to be the cluster index (only likely to be useful for joins to child tables).

The percentage of Indexes in an installation meeting the requirements to be relatively static with little structure modification and largely probe, non sequential scanning access, may be small. Indexes for common lookup tables. These could well have been Hash Organisation tables if desired. FTBs are going to be best for cases where one index is always used for very random access, but another index is used for sequential access, e.g. in batch processing.

Perhaps the best way to ensure that one normal index without FTB assist is used for Sequential scanning type processing, and another index is used only randomly might be to "randomise" the sequence of an Index Column, in an attempt to disable any useful index scanning (LOL), to try to make it more likely to qualify for FTB. This is wild supposition since I don't yet have any practical cases to demonstrate this. If the index was 3 columns and any access had 2 matching columns only, both equals matches, the random column index could still be used for a sequential scan, and may or may not be suited to FTB. I am guessing very short scans of just several entries may be still very acceptable to FTB. Because FTB is so automated, IBM don't tell us much about thresholds for FTB selection or non selection. In due course we can see the FTB behaviour using IFCIDS and a Display Command. I have not got into it too deeply as yet, expecting it not to make a huge difference to the organisation, perhaps a little bit too pessimistically.

Does anyone have any massive success stories from FTB ?

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Mar 07, 2018 - 08:31 AM (Europe/Berlin)

Michael Hannan

RE: AW: DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data shar
(in response to Michael Hannan)

Those bored already, need read no further, yet I think the topic of Sequential and Random keys in Data Sharing is a very important one, even have veered off the topic somewhat.

In Data Sharing, do we want to have indexes with multiple parallel processes, inserting very sequential keys, and fighting each other for the last leaf page of the index, to update the latest version of that page, using Group Buffer Pool to pass the page around?

The answer depends on the purpose of the table, and the key design. Suppose it's an Audit Trail table and we wanted the index to be the INSERT_TIMESTMP? Why? Because we want to be able to find all the rows within a certain TIMESTAMP range by a query. The Sequential nature of the Insert could be alleviated by placing a column with a small number of values in front of INSERT_TIMESTMP, e.g. USERID making the index by USERID, INSERT_TIMESTMP. Table and index could be partitioned by USERID ranges. To find the rows in a TIMESTAMP range quickly, we would need to join to a reference table containing all USERID values.

We don't want to make TIMESTAMP sequenced random in the index in this case, since that would defeat the purpose to be able to find rows within a TIMESTAMP range by a fast query. If we were happy to find rows always with tablespace scan, partition scan, or on the Analytics Accelerator, then we don't need a TIMESTAMP index at all. If it was purely used to make a Unique Key, then we can afford to put some other smaller cardinality non sequential column in front of the INSERT_TIMESTMP, or specify a Unique index on INSERT_TIMESTMP RANDOM. So I have found or suggested a possible extreme use for it. The underlying table would be MEMBER CLUSTER APPEND YES etc.

Here is an idea to avoid index insert page p-lock waits (while members negotiate who has the page). Suppose their are 4 members of the data sharing group, MBRCYCLE has values 1, 2, 3, 4 with each used by just one member per day and used cyclically. Partition the Audit_Trail table by MBRCYCLE (4 parts or more if desired). Now if we want an index by INSERT_TIMESTMP, make it a DPSI and Cluster. Queries with range predicates will get matching index scan on each of the 4 parts. If we want the index to enforce uniqueness (why?), can make it by columns: INSERT_TIMESTMP, MBRCYCLE. The idea is to have each Data Sharing Member inserting to a different partition of the index alleviating the index page contention. This is an extreme technique for a very heavy insert case where someone is insisting on a sequential key used for range predicates. However note that I don't want the processing slowed down by totally random Inserts, which cost for Getpages and I/Os in original Inserts and in queries. I most certainly don't want a totally random clustering of the table.

If a table was keyed by ACCOUNT_NBR, INSERT_TIMESTMP, then it is not requiring such a technique as parallel processes should be processing different Account Numbers of different ranges of account numbers.

Indexes with highly Sequential Insert spread across all the data sharing members can be tolerated, when the Insert rates into the table are not very high. Each member waits for the other member holding the last page with inserted entries, to release its control (p-lock) even if other member is not yet committed. Yes this processing causes global waits. I have not been seeing a lot of tables with this as a severe problem, but does exist.

I have seen tables with one index on a single column highly randomised, yet on same table another very Sequential TIMESTAMP index exists, which will have the Insert delays mentioned. If all Inserters to the Index happened to be running on the same Data Sharing member, that would solve the problem, reducing it to only latch waits. 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Edited By:
Michael Hannan[Organization Members] @ Mar 07, 2018 - 09:15 AM (Europe/Berlin)
Michael Hannan[Organization Members] @ Mar 07, 2018 - 09:19 AM (Europe/Berlin)

Michael Hannan

RE: AW: DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data shar
(in response to Michael Hannan)

The IBM documentation when Index column random came in in DB2 9, was a bit weak in my opinion: 

e.g. in the Tech Overview:
"A randomized index key to avoid hot spots can be beneficial for data sharing because of index page P-lock contention. It is a trade-off between contention relief and additional getpage, read/write I/O, and lock request. This performs better for indexes resident in buffer pool. The use of the NOT LOGGED table space option where appropriate can help reduce log latch contention. The APPEND option in insert can be used to reduce a longer chain of spacemap page search as the table space keeps getting bigger."

I found doco saying index cannot avoid DB2 Sort with ORDER BY on the random column. Fairly obvious. I also deduced that cannot get an index match on a range predicate since the entries will not be in normal sequence. If the normal value is still stored after a randomising prefix, then Index Screening would still be possible, and Index Only access would still be possible. I therefore expect that to be the case to preserve as many things to still work as possible. So random column might be just a bit longer than normal, speculating.

Setting RANDOM on an otherwise highly sequential column could be the answer to index p-lock contention (waits), if column was not preceded by some other column with sufficient values to spread the insert work on the index, and several members having high Insert rates on the sequential key value.

Randomising the column can reduce global waits at the cost of more Getpages and more CPU time for the Inserts. So its a trade off. Still have not seen so many actual uses to date, where was necessary.

 

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Larry Kintisch

DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data sharing
(in response to KAPIL MATHUR)
Kapil,
I've read all of the responses so far. I'll
admit to having no real knowledge of DATA SHARING
nor of operations since Ver 9 [when I stopped
teaching Application Performance classes for
IBM]. As mentioned, with sufficient INDEX PCTFREE
and nightly REORG INDEX for those in high
contention you should reduce the "splits" problem.

But you did not mention if the index was being
updated with random ID's [say random CUSTNO's] or
with sequential numbers that were somehow
generated by either (a) a table of "next numbers"
[dangerous!] or (b) by a sequence or (c) by a timestamp.

Also you didn't mention if the INSERTed table had another index or more?

If the DB2-LIST will pardon some repetition
here, the duration of a lock [and I assume a
P-lock as well] may depend upon the physical
delay in a GETPAGE [SyncRd = long delay; "in-BP" = tiny delay].

I've mentioned in earlier posts the "Swiss
solution" in the case of avoiding DEADLOCKs due
to INSERTs to random index pages [possibly
multiple indexes]. That solution was to pre-read
"with UR" [ and index only] the index page(s)
needed so they would be in the BP before the
INSERT was issued [starting the x-lock and
p-lock]. The SyncRd delay [if needed] WOULD NOT
be part of the X-lock holding time, and the
actual INSERTs with their locks should be blazingly fast.

You can contact me off-line for more info or
look up in the DB2-List archive from Sept 17,
2017 the thread message "RE: Chicken or the egg problem" for more explanation.

Hope this helps. LK

Larry Kintisch Pres, ABLE Information Services [login to unmask email]

At 04:24 AM 3/5/2018, you wrote:

>Hi DB2-L members,
>
>I am in a DB2 11 for z/OS 4-way data sharing
>shop and I am looking for documentation and your
>shop's experience with defining indexes with
>index columns defined in "RANDOM" order (not ASC
>or DESC !) to alleviate insert hotspots that
>develop with very high insert rates from
>multiple members in data-sharing environments.
>
>The DB2 syntax is valid but DB2 11 'Managing
>Performance' Guide is silent on this topic.
>Given that this feature has existed DB2 10
>onwards, I am looking for feedback from anyone
>who actually tried it successfully.
>
>I found a reference to it a 2011 presentation by
>John Campbell titled 'DB2 for z/OS Best
>Practices - Optimizing Insert Performance - Part
>2' on a slide on topic Randomised Index Key but
>apart from the usage and limitations, it also mentions:-
>Careful trade-off required between lock
>contention relief and additional getpages,
>read/write I/Os, and increased number of lock requests
>This type of index can provide dramatic improvement or degradation!
>Recommend making randomized indexes only when bufferpool resident
>
>Is there any documentation on how random indexes
>are internally organized - what are their
>runtime overheads and performance tradeoffs?
>Is there any IBM documentation on how to try
>them out and identify the "dramatic improvement
>or degradation" (Class 2 times)?
>Have you used this feature and succeeded in
>reducing insert hotspots in your data-sharing environment?
>Is this feature/solution superseded by Fast-Traverse-Block indexes in DB2 12 ?
>
>I am grateful for feedback regarding your
>opinions/ experiences or for any additional
>related IBM documentation you could point me to. Thanks
>
>Site Links:
> https://www.idug.org/p/fo/st/?post=185043&anc=p185043#p185043 View
>post
>online
> https://www.idug.org/p/fo/si/?topic=19 View
>mailing list
>online <mailto:[login to unmask email]>Start new
>thread via
>email
><mailto:[login to unmask email]?Subject=Unsubscribe>Unsubscribe
>from this mailing
>list https://www.idug.org/p/us/to Manage
>your subscription This email has been sent to: [login to unmask email]
>
>** ** ** IDUG DB2 Data and Analytics Technical
>Summit in Bengaluru, India 2018 ** ** ** --->
>Bengaluru, India, March 27, 2018 <---
> http://ibm.biz/IDUGBengaluru2018 http://ibm.biz/IDUGBengaluru2018
>
>
>Use of this email content is governed by the terms of service at:
> http://www.idug.org/p/cm/ld/fid=2 http://www.idug.org/p/cm/ld/fid=2

Michael Hannan

RE: DB2 11 on z/OS - using indexes with columns ordered RANDOM to avoid insert hotspots in data sharing
(in response to Larry Kintisch)

In Reply to Larry Kintisch:

Kapil,
I've read all of the responses so far. I'll
admit to having no real knowledge of DATA SHARING
nor of operations since Ver 9 [when I stopped
teaching Application Performance classes for
IBM]. As mentioned, with sufficient INDEX PCTFREE
and nightly REORG INDEX for those in high
contention you should reduce the "splits" problem.
If the DB2-LIST will pardon some repetition
here, the duration of a lock [and I assume a
P-lock as well] may depend upon the physical
delay in a GETPAGE [SyncRd = long delay; "in-BP" = tiny delay].

I've mentioned in earlier posts the "Swiss
solution" in the case of avoiding DEADLOCKs due
to INSERTs to random index pages [possibly
multiple indexes]. That solution was to pre-read
"with UR" [ and index only] the index page(s)
needed so they would be in the BP before the
INSERT was issued [starting the x-lock and
p-lock]. The SyncRd delay [if needed] WOULD NOT
be part of the X-lock holding time, and the
actual INSERTs with their locks should be blazingly fast.

 Hope this helps. LK

Larry,

A couple of points.

Db2 Data Sharing doesn't change massively from one release to another so any V9 level knowledge will still be relevant. Data Sharing page consistency p-Locking is of course a very different ball game to non-Data Sharing. Index hotspots is not really a problem in non-Data Sharing, other than page split is more frequent than for spread out Inserts. Not much to be done about that unless want to make the index page size bigger.

Since Indexes are not Locked (in non-Data Sharing) they don't participate in deadlocks. Underlying rows can be deadlocked. The Swiss Solution and others was highlighted in an early DB2 Design Red Book by Jan Henderickyxc (and others). Pity they had Country names as myself being from Australia, I completely disowned the so called "Australian Solution", and never saw it in Australia. No clue where and if it was actually proposed. The Swiss technique may well be out dated as well due to modern innovations (I have not checked it again so don't remember its detail). e.g. Sequences, SKIP LOCKED ROW Cursor, and ability for Select to skip Inserted rows not yet committed in V10 (in place of reading uncommitted with UR).

In Data Sharing, indexes should not dead-lock from p-locks, since they have very different duration characteristics and can be released when requested. So if a p-lock was part of a potential deadlock scenario, the member holding it would be requested/ordered to release it. So a proper deadlock would not occur, I believe.

Index page splits take CPU and require latches locally and p-locks in Data Sharing. However the main page p-lock contention issue on an index, is due to multiple members wanting to update the same index page, due to very sequential style of key Inserts.

You made a good point that if a key being inserted is randomised or semi-randomised already, there would be little need to sequence the index column as RANDOM as well. The technique could only be useful in case of very localised insertions (to same page) by multiple concurrent members, different index entries but all collating to the same target leaf page. Each index is an independent problem since some may be highly sequential and some maybe accessed quite randomly.

I don't think Index freespace is a big issue for highly sequential Inserters, in fact indexes with highly sequential Insertion are often given no distributed freespace (or minimal) since it won't be used. Distributed PCTFREE of 5 to 10 is advisable for randomly access indexes, to minimise page splits. With PCTFREE 0 and after a Reorg, the Index could have a massive spike in Page splits, so to me PCTFREE 0 is dangerous unless sure that there are very few points in the index where inserts will occur. Uneven page splits for Sequential Inserters was a big improvement to reduce page splits and half used pages. I prefer to see small PCTFREE rather than PCTFREE 0 for indexes. A bit safer.

Major delays in index p-locks in data sharing are to do with any long distance to the coupling facility to reqister a lock, false contentions on the Hash Class in CF, and real contentions requiring negotiation to order the holding member to release its hold of the p-lock (fortunately not waiting till transaction commit), and perhaps Getpage resulting in retrieval from the Group BP, when member does not have the latest copy. Hopefully I/O delay is low especially with "Hotspots". Distance between Data Sharing members can be a big issue.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Leila Hosseini

Statement ID in Dsn-statement-cache-table
(in response to Michael Hannan)



Hello,

My question is for Statement ID which is a column of DSN-STATEMENT-CAChe-TABLE




I would like to know 

1- how it be generated

2-when it reused  

3-what would be its maximum value

4- in DSN-STATEMENT-CAChe-TABLE ,we can find statement id for dynamic SQL

Is there any STMTID for static SQL

5-if any trace would be active and record something about the SQL statement ,the STMTID would be the same in SMF and DSN-STATEMENT-CAche-TABLE

6-SMF record date-time would be the same as CACHED-TS  column in DSN-STATEMENT-CACHE-TBLE?




thanks for your help 

Regards 

Leila 

Michael Hannan

RE: Statement ID in Dsn-statement-cache-table
(in response to Leila Hosseini)

Leila,
Good question but it deserves to be in a new thread as people may well be bored with the current thread topic which is unrelated to your question.

Michael Hannan,
DB2 Application Performance Specialist
CPT Global Ltd

Leila Hosseini

Statement ID in EDM and SMF
(in response to Leila Hosseini)


Hello 
My question is for statement ID in DSN-Statement-cache-Table which is assigned to Dynamic SQLs in EDM .It can be a system level STMT-ID ?In that case it could be reflected in the SMF records too ,and we use it to trace specific SQL statements ?




How this STMT-ID is generated and reuse,

Is there any chance to have duplicate Statement IDs , which require more details for trace such as time-stamp of the event.

Again how would be possible to match the time-stamp in DSn-Statement-cache-table and SMF records.




thanks for your help 

Regards 

Leila 



Roy Boxwell

Statement ID in EDM and SMF
(in response to Leila Hosseini)
The DSC statement Id is unique to that specific Db2 member and will, as far as I am aware, simply wrap around when it hits 2 billion or so, also remember that statement id one is reserved for IBM Internal use.
The SSC statement id is generated at BIND time and is guaranteed to be unique for that complete DB2 system. Here remember that the next BIND *will* change all of these - So a sort of “history” is well worth creating!


Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

From: Leila hosseini [mailto:[login to unmask email]
Sent: Thursday, March 15, 2018 1:39 PM
To: [login to unmask email]
Subject: [DB2-L] - Statement ID in EDM and SMF



Hello

My question is for statement ID in DSN-Statement-cache-Table which is assigned to Dynamic SQLs in EDM .It can be a system level STMT-ID ?
In that case it could be reflected in the SMF records too ,and we use it to trace specific SQL statements ?

How this STMT-ID is generated and reuse,
Is there any chance to have duplicate Statement IDs , which require more details for trace such as time-stamp of the event.
Again how would be possible to match the time-stamp in DSn-Statement-cache-table and SMF records.

thanks for your help
Regards
Leila
________________________________

-----End Original Message-----

Leila Hosseini

Statement ID in EDM and SMF
(in response to Roy Boxwell)


Roy
Thanks  a lot

Sent from Yahoo Mail for iPhone


On Thursday, March 15, 2018, 9:58 AM, Boxwell, Roy <[login to unmask email]> wrote:

#yiv2018641475 #yiv2018641475 -- _filtered #yiv2018641475 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv2018641475 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv2018641475 {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}#yiv2018641475 #yiv2018641475 p.yiv2018641475MsoNormal, #yiv2018641475 li.yiv2018641475MsoNormal, #yiv2018641475 div.yiv2018641475MsoNormal {margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv2018641475 a:link, #yiv2018641475 span.yiv2018641475MsoHyperlink {color:blue;text-decoration:underline;}#yiv2018641475 a:visited, #yiv2018641475 span.yiv2018641475MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv2018641475 p {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv2018641475 span.yiv2018641475E-MailFormatvorlage18 {color:#1F497D;}#yiv2018641475 .yiv2018641475MsoChpDefault {font-size:10.0pt;} _filtered #yiv2018641475 {margin:70.85pt 70.85pt 2.0cm 70.85pt;}#yiv2018641475 div.yiv2018641475WordSection1 {}#yiv2018641475
The DSC statement Id is unique to that specific Db2 member and will, as far as I am aware, simply wrap around when it hits 2 billion or so, also remember that statement id one is reserved for IBM Internal use.

The SSC statement id is generated at BIND time and is guaranteed to be unique for that complete DB2 system. Here remember that the next BIND *will* change all of these - So a sort of “history” is well worth creating!

 

 

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

 

From: Leila hosseini [mailto:[login to unmask email]
Sent: Thursday, March 15, 2018 1:39 PM
To: [login to unmask email]
Subject: [DB2-L] - Statement ID in EDM and SMF

 



Hello 

 

My question is for statement ID in DSN-Statement-cache-Table which is assigned to Dynamic SQLs in EDM .It can be a system level STMT-ID ?

In that case it could be reflected in the SMF records too ,and we use it to trace specific SQL statements ?



 



How this STMT-ID is generated and reuse,



Is there any chance to have duplicate Statement IDs , which require more details for trace such as time-stamp of the event.



Again how would be possible to match the time-stamp in DSn-Statement-cache-table and SMF records.



 



thanks for your help 



Regards 



Leila 

-----End Original Message-----
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** IDUG DB2 Data and Analytics Technical Summit in Chennai, India 2018 ** ** **
---> Chennai, India, March 29, 2018 <---
http://ibm.biz/IDUGChennai2018



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2




Leila Hosseini

Statement ID in EDM and SMF
(in response to Leila Hosseini)
Duration for SMF data collection is same as DSC ,and STMT-TYPE in SMF record is DYnamic.But correlated STMT-ID not found in DSC,Some body has any idea ?Thanks for your help.Leila


Sent from Yahoo Mail for iPhone


On Thursday, March 15, 2018, 4:10 PM, Leila hosseini <[login to unmask email]> wrote:



Roy
Thanks  a lot

Sent from Yahoo Mail for iPhone


On Thursday, March 15, 2018, 9:58 AM, Boxwell, Roy <[login to unmask email]> wrote:

#yiv9534310648 -- filtered {panose-1:2 4 5 3 5 4 6 3 2 4;}#yiv9534310648 filtered {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}#yiv9534310648 filtered {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}#yiv9534310648 p.yiv9534310648MsoNormal, #yiv9534310648 li.yiv9534310648MsoNormal, #yiv9534310648 div.yiv9534310648MsoNormal {margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv9534310648 a:link, #yiv9534310648 span.yiv9534310648MsoHyperlink {color:blue;text-decoration:underline;}#yiv9534310648 a:visited, #yiv9534310648 span.yiv9534310648MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv9534310648 p {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv9534310648 span.yiv9534310648E-MailFormatvorlage18 {color:#1F497D;}#yiv9534310648 .yiv9534310648MsoChpDefault {font-size:10.0pt;}#yiv9534310648 filtered {margin:70.85pt 70.85pt 2.0cm 70.85pt;}#yiv9534310648 div.yiv9534310648WordSection1 {}#yiv9534310648
The DSC statement Id is unique to that specific Db2 member and will, as far as I am aware, simply wrap around when it hits 2 billion or so, also remember that statement id one is reserved for IBM Internal use.

The SSC statement id is generated at BIND time and is guaranteed to be unique for that complete DB2 system. Here remember that the next BIND *will* change all of these - So a sort of “history” is well worth creating!

 

 

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

 

From: Leila hosseini [mailto:[login to unmask email]
Sent: Thursday, March 15, 2018 1:39 PM
To: [login to unmask email]
Subject: [DB2-L] - Statement ID in EDM and SMF

 



Hello 

 

My question is for statement ID in DSN-Statement-cache-Table which is assigned to Dynamic SQLs in EDM .It can be a system level STMT-ID ?

In that case it could be reflected in the SMF records too ,and we use it to trace specific SQL statements ?



 



How this STMT-ID is generated and reuse,



Is there any chance to have duplicate Statement IDs , which require more details for trace such as time-stamp of the event.



Again how would be possible to match the time-stamp in DSn-Statement-cache-table and SMF records.



 



thanks for your help 



Regards 



Leila 

-----End Original Message-----
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** IDUG DB2 Data and Analytics Technical Summit in Chennai, India 2018 ** ** **
---> Chennai, India, March 29, 2018 <---
http://ibm.biz/IDUGChennai2018



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2





Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** IDUG DB2 Data and Analytics Technical Summit in Chennai, India 2018 ** ** **
---> Chennai, India, March 29, 2018 <---
http://ibm.biz/IDUGChennai2018



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



Roy Boxwell

Statement ID in EDM and SMF
(in response to Leila Hosseini)
Just a normal flushed statement. To capture the flushed statements you must write an assembler monitor to capture the 316s when they are flushed. Or you buy one...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

On 16 Mar 2018, at 19:27, Leila hosseini <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Duration for SMF data collection is same as DSC ,and STMT-TYPE in SMF record is DYnamic.
But correlated STMT-ID not found in DSC,
Some body has any idea ?
Thanks for your help.
Leila


Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS


On Thursday, March 15, 2018, 4:10 PM, Leila hosseini <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Roy

Thanks a lot


Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS


On Thursday, March 15, 2018, 9:58 AM, Boxwell, Roy <[login to unmask email]<mailto:[login to unmask email]>> wrote:

The DSC statement Id is unique to that specific Db2 member and will, as far as I am aware, simply wrap around when it hits 2 billion or so, also remember that statement id one is reserved for IBM Internal use.

The SSC statement id is generated at BIND time and is guaranteed to be unique for that complete DB2 system. Here remember that the next BIND *will* change all of these - So a sort of “history” is well worth creating!





Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675<tel:+49%20211%2096149-675>
Fax +49 (0)211 96149-32<tel:+49%20211%2096149-32>
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



From: Leila hosseini [mailto:[login to unmask email]
Sent: Thursday, March 15, 2018 1:39 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Statement ID in EDM and SMF




Hello



My question is for statement ID in DSN-Statement-cache-Table which is assigned to Dynamic SQLs in EDM .It can be a system level STMT-ID ?

In that case it could be reflected in the SMF records too ,and we use it to trace specific SQL statements ?



How this STMT-ID is generated and reuse,

Is there any chance to have duplicate Statement IDs , which require more details for trace such as time-stamp of the event.

Again how would be possible to match the time-stamp in DSn-Statement-cache-table and SMF records.



thanks for your help

Regards

Leila

-----End Original Message-----

-----End Original Message-----

Leila Hosseini

Statement ID in EDM and SMF
(in response to Roy Boxwell)
I am looking at IFCID(376).Not interested to performance matters.My understanding was statement
“EXPLAIN STATEMENT CACHE ALL “Can flushout Dynamic cache content into a table!

Sent from Yahoo Mail for iPhone


On Saturday, March 17, 2018, 10:04 AM, Boxwell, Roy <[login to unmask email]> wrote:

Just a normal flushed statement. To capture the flushed statements you must write an assembler monitor to capture the 316s when they are flushed. Or you buy one...

Roy BoxwellSOFTWARE ENGINEERING GmbH and SEGUS Inc.-Product Development-Heinrichstrasse 83-8540239 Düsseldorf/GermanyTel. +49 (0)211 96149-675Fax +49 (0)211 96149-32Email: [login to unmask email]http://www.seg.de
Software Engineering GmbHAmtsgericht Düsseldorf, HRB 37894Geschäftsführung: Gerhard Schubert
On 16 Mar 2018, at 19:27, Leila hosseini <[login to unmask email]> wrote:


Duration for SMF data collection is same as DSC ,and STMT-TYPE in SMF record is DYnamic.But correlated STMT-ID not found in DSC,Some body has any idea ?Thanks for your help.Leila


Sent from Yahoo Mail for iPhone


On Thursday, March 15, 2018, 4:10 PM, Leila hosseini <[login to unmask email]> wrote:



Roy
Thanks  a lot

Sent from Yahoo Mail for iPhone


On Thursday, March 15, 2018, 9:58 AM, Boxwell, Roy <[login to unmask email]> wrote:

#yiv0126971281 -- filtered {panose-1:2 4 5 3 5 4 6 3 2 4;}#yiv0126971281 filtered {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;}#yiv0126971281 filtered {font-family:Tahoma;panose-1:2 11 6 4 3 5 4 4 2 4;}#yiv0126971281 p.yiv0126971281MsoNormal, #yiv0126971281 li.yiv0126971281MsoNormal, #yiv0126971281 div.yiv0126971281MsoNormal {margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv0126971281 a:link, #yiv0126971281 span.yiv0126971281MsoHyperlink {color:blue;text-decoration:underline;}#yiv0126971281 a:visited, #yiv0126971281 span.yiv0126971281MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv0126971281 p {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv0126971281 span.yiv0126971281E-MailFormatvorlage18 {color:#1F497D;}#yiv0126971281 .yiv0126971281MsoChpDefault {font-size:10.0pt;}#yiv0126971281 filtered {margin:70.85pt 70.85pt 2.0cm 70.85pt;}#yiv0126971281 div.yiv0126971281WordSection1 {}#yiv0126971281
The DSC statement Id is unique to that specific Db2 member and will, as far as I am aware, simply wrap around when it hits 2 billion or so, also remember that statement id one is reserved for IBM Internal use.

The SSC statement id is generated at BIND time and is guaranteed to be unique for that complete DB2 system. Here remember that the next BIND *will* change all of these - So a sort of “history” is well worth creating!

 

 

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

 

From: Leila hosseini [mailto:[login to unmask email]
Sent: Thursday, March 15, 2018 1:39 PM
To: [login to unmask email]
Subject: [DB2-L] - Statement ID in EDM and SMF

 



Hello 

 

My question is for statement ID in DSN-Statement-cache-Table which is assigned to Dynamic SQLs in EDM .It can be a system level STMT-ID ?

In that case it could be reflected in the SMF records too ,and we use it to trace specific SQL statements ?



 



How this STMT-ID is generated and reuse,



Is there any chance to have duplicate Statement IDs , which require more details for trace such as time-stamp of the event.



Again how would be possible to match the time-stamp in DSn-Statement-cache-table and SMF records.



 



thanks for your help 



Regards 



Leila 

-----End Original Message-----
-----End Original Message-----

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** IDUG DB2 Data and Analytics Technical Summit in Chennai, India 2018 ** ** **
---> Chennai, India, March 29, 2018 <---
http://ibm.biz/IDUGChennai2018



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2






Roy Boxwell

Statement ID in EDM and SMF
(in response to Leila Hosseini)
Nope! It just empties the cache on the db2 member you are connected to - it does not ever extract the sql text. For that you have to ask for the sql text by using the token id. Of course at that point in time your statement might be long gone from the DSC.

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

On 17 Mar 2018, at 16:43, Leila hosseini <[login to unmask email]<mailto:[login to unmask email]>> wrote:

I am looking at IFCID(376).
Not interested to performance matters.
My understanding was statement
“EXPLAIN STATEMENT CACHE ALL “
Can flushout Dynamic cache content into a table!

Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS


On Saturday, March 17, 2018, 10:04 AM, Boxwell, Roy <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Just a normal flushed statement. To capture the flushed statements you must write an assembler monitor to capture the 316s when they are flushed. Or you buy one...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675<tel:+49%20211%2096149-675>
Fax +49 (0)211 96149-32<tel:+49%20211%2096149-32>
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

On 16 Mar 2018, at 19:27, Leila hosseini <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Duration for SMF data collection is same as DSC ,and STMT-TYPE in SMF record is DYnamic.
But correlated STMT-ID not found in DSC,
Some body has any idea ?
Thanks for your help.
Leila


Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS


On Thursday, March 15, 2018, 4:10 PM, Leila hosseini <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Roy

Thanks a lot


Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS


On Thursday, March 15, 2018, 9:58 AM, Boxwell, Roy <[login to unmask email]<mailto:[login to unmask email]>> wrote:

The DSC statement Id is unique to that specific Db2 member and will, as far as I am aware, simply wrap around when it hits 2 billion or so, also remember that statement id one is reserved for IBM Internal use.

The SSC statement id is generated at BIND time and is guaranteed to be unique for that complete DB2 system. Here remember that the next BIND *will* change all of these - So a sort of “history” is well worth creating!





Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675<tel:+49%20211%2096149-675>
Fax +49 (0)211 96149-32<tel:+49%20211%2096149-32>
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



From: Leila hosseini [mailto:[login to unmask email]
Sent: Thursday, March 15, 2018 1:39 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Statement ID in EDM and SMF




Hello



My question is for statement ID in DSN-Statement-cache-Table which is assigned to Dynamic SQLs in EDM .It can be a system level STMT-ID ?

In that case it could be reflected in the SMF records too ,and we use it to trace specific SQL statements ?



How this STMT-ID is generated and reuse,

Is there any chance to have duplicate Statement IDs , which require more details for trace such as time-stamp of the event.

Again how would be possible to match the time-stamp in DSn-Statement-cache-table and SMF records.



thanks for your help

Regards

Leila

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

Walter Jani&#223;en

AW: Statement ID in EDM and SMF
(in response to Roy Boxwell)
Hi Roy

That’s not correct. The statement-text is stored in DSN_STATEMENT_CACHE_TABLE, but no explain-information. This has to be asked for using EXPLAIN STMTCACHE STMTID stmtid and then the statement has to be in the cache.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Sonntag, 18. März 2018 19:13
An: [login to unmask email]
Betreff: [DB2-L] - RE: Statement ID in EDM and SMF

Nope! It just empties the cache on the db2 member you are connected to - it does not ever extract the sql text. For that you have to ask for the sql text by using the token id. Of course at that point in time your statement might be long gone from the DSC.
Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

On 17 Mar 2018, at 16:43, Leila hosseini <[login to unmask email]<mailto:[login to unmask email]>> wrote:
I am looking at IFCID(376).
Not interested to performance matters.
My understanding was statement
“EXPLAIN STATEMENT CACHE ALL “
Can flushout Dynamic cache content into a table!

Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS

On Saturday, March 17, 2018, 10:04 AM, Boxwell, Roy <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Just a normal flushed statement. To capture the flushed statements you must write an assembler monitor to capture the 316s when they are flushed. Or you buy one...
Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675<tel:+49%20211%2096149-675>
Fax +49 (0)211 96149-32<tel:+49%20211%2096149-32>
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

On 16 Mar 2018, at 19:27, Leila hosseini <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Duration for SMF data collection is same as DSC ,and STMT-TYPE in SMF record is DYnamic.
But correlated STMT-ID not found in DSC,
Some body has any idea ?
Thanks for your help.
Leila


Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS

On Thursday, March 15, 2018, 4:10 PM, Leila hosseini <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Roy

Thanks a lot


Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS

On Thursday, March 15, 2018, 9:58 AM, Boxwell, Roy <[login to unmask email]<mailto:[login to unmask email]>> wrote:

The DSC statement Id is unique to that specific Db2 member and will, as far as I am aware, simply wrap around when it hits 2 billion or so, also remember that statement id one is reserved for IBM Internal use.

The SSC statement id is generated at BIND time and is guaranteed to be unique for that complete DB2 system. Here remember that the next BIND *will* change all of these - So a sort of “history” is well worth creating!





Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675<tel:+49%20211%2096149-675>
Fax +49 (0)211 96149-32<tel:+49%20211%2096149-32>
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



From: Leila hosseini [mailto:[login to unmask email]
Sent: Thursday, March 15, 2018 1:39 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Statement ID in EDM and SMF




Hello



My question is for statement ID in DSN-Statement-cache-Table which is assigned to Dynamic SQLs in EDM .It can be a system level STMT-ID ?

In that case it could be reflected in the SMF records too ,and we use it to trace specific SQL statements ?



How this STMT-ID is generated and reuse,

Is there any chance to have duplicate Statement IDs , which require more details for trace such as time-stamp of the event.

Again how would be possible to match the time-stamp in DSn-Statement-cache-table and SMF records.



thanks for your help

Regards

Leila
-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Roy Boxwell

AW: Statement ID in EDM and SMF
(in response to Walter Janißen)
Oops! I stand corrected! The statement text is there...but not the EXPLAIN data – quite correct Walter!

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

From: Walter Janißen [mailto:[login to unmask email]
Sent: Monday, March 19, 2018 10:04 AM
To: [login to unmask email]
Subject: [DB2-L] - AW: RE: Statement ID in EDM and SMF

Hi Roy

That’s not correct. The statement-text is stored in DSN_STATEMENT_CACHE_TABLE, but no explain-information. This has to be asked for using EXPLAIN STMTCACHE STMTID stmtid and then the statement has to be in the cache.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Sonntag, 18. März 2018 19:13
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: Statement ID in EDM and SMF

Nope! It just empties the cache on the db2 member you are connected to - it does not ever extract the sql text. For that you have to ask for the sql text by using the token id. Of course at that point in time your statement might be long gone from the DSC.
Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

On 17 Mar 2018, at 16:43, Leila hosseini <[login to unmask email]<mailto:[login to unmask email]>> wrote:
I am looking at IFCID(376).
Not interested to performance matters.
My understanding was statement
“EXPLAIN STATEMENT CACHE ALL “
Can flushout Dynamic cache content into a table!

Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS

On Saturday, March 17, 2018, 10:04 AM, Boxwell, Roy <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Just a normal flushed statement. To capture the flushed statements you must write an assembler monitor to capture the 316s when they are flushed. Or you buy one...
Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675<tel:+49%20211%2096149-675>
Fax +49 (0)211 96149-32<tel:+49%20211%2096149-32>
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

On 16 Mar 2018, at 19:27, Leila hosseini <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Duration for SMF data collection is same as DSC ,and STMT-TYPE in SMF record is DYnamic.
But correlated STMT-ID not found in DSC,
Some body has any idea ?
Thanks for your help.
Leila


Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS

On Thursday, March 15, 2018, 4:10 PM, Leila hosseini <[login to unmask email]<mailto:[login to unmask email]>> wrote:


Roy

Thanks a lot


Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS

On Thursday, March 15, 2018, 9:58 AM, Boxwell, Roy <[login to unmask email]<mailto:[login to unmask email]>> wrote:

The DSC statement Id is unique to that specific Db2 member and will, as far as I am aware, simply wrap around when it hits 2 billion or so, also remember that statement id one is reserved for IBM Internal use.

The SSC statement id is generated at BIND time and is guaranteed to be unique for that complete DB2 system. Here remember that the next BIND *will* change all of these - So a sort of “history” is well worth creating!





Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675<tel:+49%20211%2096149-675>
Fax +49 (0)211 96149-32<tel:+49%20211%2096149-32>
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert



From: Leila hosseini [mailto:[login to unmask email]
Sent: Thursday, March 15, 2018 1:39 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - Statement ID in EDM and SMF




Hello



My question is for statement ID in DSN-Statement-cache-Table which is assigned to Dynamic SQLs in EDM .It can be a system level STMT-ID ?

In that case it could be reflected in the SMF records too ,and we use it to trace specific SQL statements ?



How this STMT-ID is generated and reuse,

Is there any chance to have duplicate Statement IDs , which require more details for trace such as time-stamp of the event.

Again how would be possible to match the time-stamp in DSn-Statement-cache-table and SMF records.



thanks for your help

Regards

Leila
-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

-----End Original Message-----

Leila Hosseini

AW: Statement ID in EDM and SMF
(in response to Walter Janißen)
Hello Walter,Already DSN_STATEMENT_CACHE_TABLE is populated with STMTID and SQLtxt,My issue is STMTID is not match with the STMTID that was extracted from SMF in the same Time duration.I appreciated if any body has any idea.Regards,Leila

Sent from Yahoo Mail for iPhone


On Monday, March 19, 2018, 5:03 AM, Walter Jani&#223;en <[login to unmask email]> wrote:

#yiv2134762309 #yiv2134762309 -- _filtered #yiv2134762309 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv2134762309 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv2134762309 {panose-1:3 1 1 1 1 1 1 1 1 1;}#yiv2134762309 #yiv2134762309 p.yiv2134762309MsoNormal, #yiv2134762309 li.yiv2134762309MsoNormal, #yiv2134762309 div.yiv2134762309MsoNormal {margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv2134762309 a:link, #yiv2134762309 span.yiv2134762309MsoHyperlink {color:blue;text-decoration:underline;}#yiv2134762309 a:visited, #yiv2134762309 span.yiv2134762309MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv2134762309 p {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv2134762309 p.yiv2134762309msonormal0, #yiv2134762309 li.yiv2134762309msonormal0, #yiv2134762309 div.yiv2134762309msonormal0 {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv2134762309 p.yiv2134762309yahoo-quoted-begin, #yiv2134762309 li.yiv2134762309yahoo-quoted-begin, #yiv2134762309 div.yiv2134762309yahoo-quoted-begin {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv2134762309 p.yiv2134762309yahoo-quoted-begin, #yiv2134762309 li.yiv2134762309yahoo-quoted-begin, #yiv2134762309 div.yiv2134762309yahoo-quoted-begin {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv2134762309 p.yiv2134762309msonormal, #yiv2134762309 li.yiv2134762309msonormal, #yiv2134762309 div.yiv2134762309msonormal {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv2134762309 p.yiv2134762309msochpdefault, #yiv2134762309 li.yiv2134762309msochpdefault, #yiv2134762309 div.yiv2134762309msochpdefault {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv2134762309 span.yiv2134762309msohyperlink {}#yiv2134762309 span.yiv2134762309msohyperlinkfollowed {}#yiv2134762309 span.yiv2134762309e-mailformatvorlage18 {}#yiv2134762309 p.yiv2134762309msonormal1, #yiv2134762309 li.yiv2134762309msonormal1, #yiv2134762309 div.yiv2134762309msonormal1 {margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv2134762309 span.yiv2134762309msohyperlink1 {color:blue;text-decoration:underline;}#yiv2134762309 span.yiv2134762309msohyperlinkfollowed1 {color:purple;text-decoration:underline;}#yiv2134762309 span.yiv2134762309e-mailformatvorlage181 {color:#1F497D;}#yiv2134762309 p.yiv2134762309msochpdefault1, #yiv2134762309 li.yiv2134762309msochpdefault1, #yiv2134762309 div.yiv2134762309msochpdefault1 {margin-right:0cm;margin-left:0cm;font-size:10.0pt;}#yiv2134762309 span.yiv2134762309E-MailFormatvorlage31 {color:#1F497D;font-weight:normal;font-style:normal;text-decoration:none none;}#yiv2134762309 .yiv2134762309MsoChpDefault {font-size:10.0pt;} _filtered #yiv2134762309 {margin:70.85pt 70.85pt 2.0cm 70.85pt;}#yiv2134762309 div.yiv2134762309WordSection1 {}#yiv2134762309
Hi Roy

 

That’s not correct. The statement-text is stored in DSN_STATEMENT_CACHE_TABLE, but no explain-information. This has to be asked for using EXPLAIN STMTCACHE STMTID stmtid and then the statement has to be in the cache.

 

Kind regards
Walter Janißen 

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

 

Von: Boxwell, Roy [mailto:[login to unmask email]
Gesendet: Sonntag, 18. März 2018 19:13
An: [login to unmask email]
Betreff: [DB2-L] - RE: Statement ID in EDM and SMF

 

Nope! It just empties the cache on the db2 member you are connected to - it does not ever extract the sql text. For that you have to ask for the sql text by using the token id. Of course at that point in time your statement might be long gone from the DSC.

Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.

-Product Development-

Heinrichstrasse 83-85

40239 Düsseldorf/Germany

Tel. +49 (0)211 96149-675

Fax +49 (0)211 96149-32

Email: [login to unmask email]

http://www.seg.de

 

Software Engineering GmbH

Amtsgericht Düsseldorf, HRB 37894

Geschäftsführung: Gerhard Schubert


On 17 Mar 2018, at 16:43, Leila hosseini <[login to unmask email]> wrote:


I am looking at IFCID(376).

Not interested to performance matters.

My understanding was statement
“EXPLAIN STATEMENT CACHE ALL “

Can flushout Dynamic cache content into a table!

Sent from Yahoo Mail for iPhone

On Saturday, March 17, 2018, 10:04 AM, Boxwell, Roy <[login to unmask email]> wrote:


Just a normal flushed statement. To capture the flushed statements you must write an assembler monitor to capture the 316s when they are flushed. Or you buy one...

Roy Boxwell

SOFTWARE ENGINEERING GmbH and SEGUS Inc.

-Product Development-

Heinrichstrasse 83-85

40239 Düsseldorf/Germany

Tel. +49 (0)211 96149-675

Fax +49 (0)211 96149-32

Email: [login to unmask email]

http://www.seg.de

 

Software Engineering GmbH

Amtsgericht Düsseldorf, HRB 37894

Geschäftsführung: Gerhard Schubert


On 16 Mar 2018, at 19:27, Leila hosseini <[login to unmask email]> wrote:


Duration for SMF data collection is same as DSC ,and STMT-TYPE in SMF record is DYnamic.

But correlated STMT-ID not found in DSC,

Some body has any idea ?

Thanks for your help.

Leila



Sent from Yahoo Mail for iPhone

On Thursday, March 15, 2018, 4:10 PM, Leila hosseini <[login to unmask email]> wrote:




Roy

 

Thanks  a lot

 


Sent from Yahoo Mail for iPhone

On Thursday, March 15, 2018, 9:58 AM, Boxwell, Roy <[login to unmask email]> wrote:


The DSC statement Id is unique to that specific Db2 member and will, as far as I am aware, simply wrap around when it hits 2 billion or so, also remember that statement id one is reserved for IBM Internal use.

The SSC statement id is generated at BIND time and is guaranteed to be unique for that complete DB2 system. Here remember that the next BIND *will* change all of these - So a sort of “history” is well worth creating!

 

 

Roy Boxwell

SOFTWARE ENGINEERING GMBH and SEGUS Inc.
-Product Development-

Heinrichstrasse 83-85
40239 Duesseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

 

From: Leila hosseini [mailto:[login to unmask email]
Sent: Thursday, March 15, 2018 1:39 PM
To: [login to unmask email]
Subject: [DB2-L] - Statement ID in EDM and SMF

 



Hello 

 

My question is for statement ID in DSN-Statement-cache-Table which is assigned to Dynamic SQLs in EDM .It can be a system level STMT-ID ?

In that case it could be reflected in the SMF records too ,and we use it to trace specific SQL statements ?



 



How this STMT-ID is generated and reuse,



Is there any chance to have duplicate Statement IDs , which require more details for trace such as time-stamp of the event.



Again how would be possible to match the time-stamp in DSn-Statement-cache-table and SMF records.



 



thanks for your help 



Regards 



Leila 


-----End Original Message-----

 

-----End Original Message-----


 

-----End Original Message-----

 
-----End Original Message-----





Attachment Links: image001.png (3 k)  
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** IDUG DB2 Data and Analytics Technical Summit in Bengaluru, India 2018 ** ** **
---> Bengaluru, India, March 27, 2018 <---
http://ibm.biz/IDUGBengaluru2018



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



alain pary

RE: AW: Statement ID in EDM and SMF
(in response to Leila Hosseini)

Hello, 

Why don't  you make 1 or more times by day an EXPLAIN STMTCACHE ALL . The number of times is related to your time residency in the Stmt_cache. 

With this you will increase the number of finding on stmdid .

This can also help you to have a view upon the dynamic statement use in your company . 

 

regards 

 

Alain

Leila Hosseini

AW: Statement ID in EDM and SMF
(in response to alain pary)
I did EXPLAIN STMTCACHE ALL  2 times each day,it has about 2000 records each day.but SMF records for specific IFCID is 10 per day.


Sent from Yahoo Mail for iPhone


On Monday, March 19, 2018, 11:07 AM, alain pary <[login to unmask email]> wrote:


Hello, 

Why don't  you make 1 or more times by day an EXPLAIN STMTCACHE ALL . The number of times is related to your time residency in the Stmt_cache. 

With this you will increase the number of finding on stmdid .

This can also help you to have a view upon the dynamic statement use in your company . 

 

regards 

 

Alain

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** IDUG DB2 Data and Analytics Technical Summit in Bengaluru, India 2018 ** ** **
---> Bengaluru, India, March 27, 2018 <---
http://ibm.biz/IDUGBengaluru2018



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



Roy Boxwell

AW: Statement ID in EDM and SMF
(in response to Leila Hosseini)
That is a very small DSC... what size is it?

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

On 19 Mar 2018, at 16:45, Leila hosseini <[login to unmask email]<mailto:[login to unmask email]>> wrote:

I did EXPLAIN STMTCACHE ALL 2 times each day,it has about 2000 records each day.but SMF records for specific IFCID is 10 per day.


Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS


On Monday, March 19, 2018, 11:07 AM, alain pary <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Hello,

Why don't you make 1 or more times by day an EXPLAIN STMTCACHE ALL . The number of times is related to your time residency in the Stmt_cache.

With this you will increase the number of finding on stmdid .

This can also help you to have a view upon the dynamic statement use in your company .



regards



Alain

-----End Original Message-----

Walter Jani&#223;en

AW: AW: Statement ID in EDM and SMF
(in response to Leila Hosseini)
Hi Leila

I’m still not sure, what you are after, because in one of your posts you mentioned ICFID376, which reports incompatibilities. This trace contains a stmtno, but I am pretty sure that this is not the stmtid, that the statement might have gotten in the DSC. This stmtno is the precompiler-stmtno of the package, which executes the dynamic statement.
IFCID376 can also be written for static statements and for dynamic ones, which were never in the cache.
For these 376-statements I don’t know, how you can get the statement-text.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Leila hosseini [mailto:[login to unmask email]
Gesendet: Montag, 19. März 2018 16:45
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: Statement ID in EDM and SMF

I did EXPLAIN STMTCACHE ALL 2 times each day,it has about 2000 records each day.but SMF records for specific IFCID is 10 per day.


Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS

On Monday, March 19, 2018, 11:07 AM, alain pary <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Hello,

Why don't you make 1 or more times by day an EXPLAIN STMTCACHE ALL . The number of times is related to your time residency in the Stmt_cache.

With this you will increase the number of finding on stmdid .

This can also help you to have a view upon the dynamic statement use in your company .



regards



Alain

-----End Original Message-----
Attachments

  • image001.png (2.6k)

Chris Hoelscher

AW: Statement ID in EDM and SMF
(in response to Walter Janißen)
For ifcid 376 flagged dynamic SQL – we use DETECTOR from CA (formerly from Platinum) – in a batch process once daily
If you have this product I can send more details

Other products have similar options – but we did not find one to work as a batch extract

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266

From: Walter Janißen [mailto:[login to unmask email]
Sent: Monday, March 19, 2018 12:24 PM
To: [login to unmask email]
Subject: [DB2-L] - AW: AW: Statement ID in EDM and SMF

Hi Leila

I’m still not sure, what you are after, because in one of your posts you mentioned ICFID376, which reports incompatibilities. This trace contains a stmtno, but I am pretty sure that this is not the stmtid, that the statement might have gotten in the DSC. This stmtno is the precompiler-stmtno of the package, which executes the dynamic statement.
IFCID376 can also be written for static statements and for dynamic ones, which were never in the cache.
For these 376-statements I don’t know, how you can get the statement-text.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Leila hosseini [mailto:[login to unmask email]
Gesendet: Montag, 19. März 2018 16:45
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: AW: Statement ID in EDM and SMF

I did EXPLAIN STMTCACHE ALL 2 times each day,it has about 2000 records each day.but SMF records for specific IFCID is 10 per day.


Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS

On Monday, March 19, 2018, 11:07 AM, alain pary <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Hello,

Why don't you make 1 or more times by day an EXPLAIN STMTCACHE ALL . The number of times is related to your time residency in the Stmt_cache.

With this you will increase the number of finding on stmdid .

This can also help you to have a view upon the dynamic statement use in your company .



regards



Alain

-----End Original Message-----

-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability or
sex. Humana Inc. and its subsidiaries do not exclude people or treat them differently
because of race, color, national origin, age, disability or sex.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.

Leila Hosseini

AW: AW: Statement ID in EDM and SMF
(in response to Walter Janißen)
Walter,I am looking for sql statements which are incompatible 
Then I started trace with IFCID376.My assumption was STMTID from SMF record could be matched with STMTID in DSC,Hence join by STMTID leads to STMTtxt which is incompatible.The only statements which coming to the DB2 subsystem are dynamic.You mentioned:This stmtno is the precompiler-stmtno of the package, which executes the dynamic statement.
How can  stmtno point out to sqltxt?Thanks for your help.Regards Leila

Sent from Yahoo Mail for iPhone


On Monday, March 19, 2018, 12:23 PM, Walter Jani&#223;en <[login to unmask email]> wrote:

#yiv5053143911 #yiv5053143911 -- _filtered #yiv5053143911 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv5053143911 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv5053143911 {panose-1:3 1 1 1 1 1 1 1 1 1;}#yiv5053143911 #yiv5053143911 p.yiv5053143911MsoNormal, #yiv5053143911 li.yiv5053143911MsoNormal, #yiv5053143911 div.yiv5053143911MsoNormal {margin:0cm;margin-bottom:.0001pt;font-size:12.0pt;}#yiv5053143911 a:link, #yiv5053143911 span.yiv5053143911MsoHyperlink {color:blue;text-decoration:underline;}#yiv5053143911 a:visited, #yiv5053143911 span.yiv5053143911MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv5053143911 p {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv5053143911 p.yiv5053143911msonormal0, #yiv5053143911 li.yiv5053143911msonormal0, #yiv5053143911 div.yiv5053143911msonormal0 {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv5053143911 p.yiv5053143911yahoo-quoted-begin, #yiv5053143911 li.yiv5053143911yahoo-quoted-begin, #yiv5053143911 div.yiv5053143911yahoo-quoted-begin {margin-right:0cm;margin-left:0cm;font-size:12.0pt;}#yiv5053143911 span.yiv5053143911E-MailFormatvorlage20 {color:#1F497D;font-weight:normal;font-style:normal;text-decoration:none none;}#yiv5053143911 .yiv5053143911MsoChpDefault {font-size:10.0pt;} _filtered #yiv5053143911 {margin:70.85pt 70.85pt 2.0cm 70.85pt;}#yiv5053143911 div.yiv5053143911WordSection1 {}#yiv5053143911
Hi Leila

 

I’m still not sure, what you are after, because in one of your posts you mentioned ICFID376, which reports incompatibilities. This trace contains a stmtno, but I am pretty sure that this is not the stmtid, that the statement might have gotten in the DSC. This stmtno is the precompiler-stmtno of the package, which executes the dynamic statement.

IFCID376 can also be written for static statements and for dynamic ones, which were never in the cache.

For these 376-statements I don’t know, how you can get the statement-text.

 

Kind regards
Walter Janißen 

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

 

Von: Leila hosseini [mailto:[login to unmask email]
Gesendet: Montag, 19. März 2018 16:45
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: Statement ID in EDM and SMF

 

I did EXPLAIN STMTCACHE ALL  2 times each day,it has about 2000 records each day.but SMF records for specific IFCID is 10 per day.


Sent from Yahoo Mail for iPhone

On Monday, March 19, 2018, 11:07 AM, alain pary <[login to unmask email]> wrote:


Hello, 

Why don't  you make 1 or more times by day an EXPLAIN STMTCACHE ALL . The number of times is related to your time residency in the Stmt_cache. 

With this you will increase the number of finding on stmdid .

This can also help you to have a view upon the dynamic statement use in your company . 

 

regards 

 

Alain

 
-----End Original Message-----
Attachment Links: image001.png (3 k)  
Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** IDUG DB2 Data and Analytics Technical Summit in Bengaluru, India 2018 ** ** **
---> Bengaluru, India, March 27, 2018 <---
http://ibm.biz/IDUGBengaluru2018



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2




Leila Hosseini

AW: Statement ID in EDM and SMF
(in response to Chris Hoelscher)
Hello Chris
I have used PGM=NSIGHTRW
Which is the CA batch report to extract IFCID376 records from SMF and load as DB2 table .Now I need to know these SMF records are related to which SQL statementThanks for your help
Sent from Yahoo Mail for iPhone


On Monday, March 19, 2018, 12:27 PM, Chris Hoelscher <[login to unmask email]> wrote:

#yiv4788394315 #yiv4788394315 -- _filtered #yiv4788394315 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv4788394315 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv4788394315 {panose-1:3 15 7 2 3 3 2 2 2 4;} _filtered #yiv4788394315 {panose-1:3 1 1 1 1 1 1 1 1 1;}#yiv4788394315 #yiv4788394315 p.yiv4788394315MsoNormal, #yiv4788394315 li.yiv4788394315MsoNormal, #yiv4788394315 div.yiv4788394315MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv4788394315 a:link, #yiv4788394315 span.yiv4788394315MsoHyperlink {color:blue;text-decoration:underline;}#yiv4788394315 a:visited, #yiv4788394315 span.yiv4788394315MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv4788394315 p {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4788394315 p.yiv4788394315msonormal0, #yiv4788394315 li.yiv4788394315msonormal0, #yiv4788394315 div.yiv4788394315msonormal0 {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4788394315 p.yiv4788394315yahoo-quoted-begin, #yiv4788394315 li.yiv4788394315yahoo-quoted-begin, #yiv4788394315 div.yiv4788394315yahoo-quoted-begin {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv4788394315 span.yiv4788394315EmailStyle20 {color:#1F497D;font-weight:normal;font-style:normal;text-decoration:none none;}#yiv4788394315 span.yiv4788394315EmailStyle21 {color:#1F497D;}#yiv4788394315 .yiv4788394315MsoChpDefault {font-size:10.0pt;} _filtered #yiv4788394315 {margin:70.85pt 70.85pt 56.7pt 70.85pt;}#yiv4788394315 div.yiv4788394315WordSection1 {}#yiv4788394315
For  ifcid 376 flagged dynamic SQL – we use DETECTOR from CA (formerly from Platinum) – in a batch process once daily

If you have this product I can send more details

 

Other products have similar options – but we did not find one to work as a batch extract

 

Chris Hoelscher

Technology Architect, Database Infrastructure Services

Technology Solution Services

Humana Inc.

123 East Main Street

Louisville, KY 40202

Humana.com

(502) 476-2538 or 407-7266

 

From: Walter Janißen [mailto:[login to unmask email]
Sent: Monday, March 19, 2018 12:24 PM
To: [login to unmask email]
Subject: [DB2-L] - AW: AW: Statement ID in EDM and SMF

 

Hi Leila

 

I’m still not sure, what you are after, because in one of your posts you mentioned ICFID376, which reports incompatibilities. This trace contains a stmtno, but I am pretty sure that this is not the stmtid, that the statement might have gotten in the DSC. This stmtno is the precompiler-stmtno of the package, which executes the dynamic statement.

IFCID376 can also be written for static statements and for dynamic ones, which were never in the cache.

For these 376-statements I don’t know, how you can get the statement-text.

 

Kind regards
Walter Janißen 

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

 

Von: Leila hosseini [mailto:[login to unmask email]
Gesendet: Montag, 19. März 2018 16:45
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: Statement ID in EDM and SMF

 

I did EXPLAIN STMTCACHE ALL  2 times each day,it has about 2000 records each day.but SMF records for specific IFCID is 10 per day.


Sent from Yahoo Mail for iPhone

On Monday, March 19, 2018, 11:07 AM, alain pary <[login to unmask email]> wrote:


Hello, 

Why don't  you make 1 or more times by day an EXPLAIN STMTCACHE ALL . The number of times is related to your time residency in the Stmt_cache. 

With this you will increase the number of finding on stmdid .

This can also help you to have a view upon the dynamic statement use in your company . 

 

regards 

 

Alain

 

-----End Original Message-----

 
-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability or
sex. Humana Inc. and its subsidiaries do not exclude people or treat them differently
because of race, color, national origin, age, disability or sex.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** IDUG DB2 Data and Analytics Technical Summit in Bengaluru, India 2018 ** ** **
---> Bengaluru, India, March 27, 2018 <---
http://ibm.biz/IDUGBengaluru2018



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2



Chris Hoelscher

AW: Statement ID in EDM and SMF
(in response to Leila Hosseini)
I use CA SMF Director / MICS / MXG to pull the ifcid I want – sort by date time / userid / program name / corr id / workstation / location
I turn on KEYS in my detector collections (and dynamic sql text) – and turn on all keys in my detector collection profile

I do a nightly download of detector per SSID: (this is for distributed only)

DATASTORE=DATASTOR
DATATYPE=STAN
UNLOAD=Y
UNLDOBJ=N
UNLDKEYS=Y
UNLDTEXT=D
AGGREGATE=N
COLLID=NULLID
STARTDATE=03/18/2018
STARTTIME=00:00
ENDDATE=03/19/2018
ENDTIME=00:01
VCAT=DBP1S
SSID=DBP1

Then I match the keys on the ifcid 376 extract to the keys on the detector extract, try to drop as many duplicates as detectable
(there can be false positives) – for distributed – the program name is mostly useless, the section / statement seem to be totally useless

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266

From: Leila hosseini [mailto:[login to unmask email]
Sent: Monday, March 19, 2018 1:06 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: AW: Statement ID in EDM and SMF

Hello Chris
I have used PGM=NSIGHTRW
Which is the CA batch report to extract IFCID376 records from SMF and load as DB2 table .
Now I need to know these SMF records are related to which SQL statement
Thanks for your help
Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS

On Monday, March 19, 2018, 12:27 PM, Chris Hoelscher <[login to unmask email]<mailto:[login to unmask email]>> wrote:

For ifcid 376 flagged dynamic SQL – we use DETECTOR from CA (formerly from Platinum) – in a batch process once daily

If you have this product I can send more details



Other products have similar options – but we did not find one to work as a batch extract



Chris Hoelscher

Technology Architect, Database Infrastructure Services

Technology Solution Services

Humana Inc.

123 East Main Street

Louisville, KY 40202

Humana.com

(502) 476-2538<tel:(502)%20476-2538> or 407-7266<tel:407-7266>



From: Walter Janißen [mailto:[login to unmask email]
Sent: Monday, March 19, 2018 12:24 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - AW: AW: Statement ID in EDM and SMF



Hi Leila



I’m still not sure, what you are after, because in one of your posts you mentioned ICFID376, which reports incompatibilities. This trace contains a stmtno, but I am pretty sure that this is not the stmtid, that the statement might have gotten in the DSC. This stmtno is the precompiler-stmtno of the package, which executes the dynamic statement.

IFCID376 can also be written for static statements and for dynamic ones, which were never in the cache.

For these 376-statements I don’t know, how you can get the statement-text.



Kind regards
Walter Janißen

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996



Von: Leila hosseini [mailto:[login to unmask email]
Gesendet: Montag, 19. März 2018 16:45
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: AW: Statement ID in EDM and SMF



I did EXPLAIN STMTCACHE ALL 2 times each day,it has about 2000 records each day.but SMF records for specific IFCID is 10 per day.


Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS

On Monday, March 19, 2018, 11:07 AM, alain pary <[login to unmask email]<mailto:[login to unmask email]>> wrote:
Hello,
Why don't you make 1 or more times by day an EXPLAIN STMTCACHE ALL . The number of times is related to your time residency in the Stmt_cache.
With this you will increase the number of finding on stmdid .
This can also help you to have a view upon the dynamic statement use in your company .

regards

Alain

-----End Original Message-----

-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability or
sex. Humana Inc. and its subsidiaries do not exclude people or treat them differently
because of race, color, national origin, age, disability or sex.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235<tel:1%E2%80%90877%E2%80%90320%E2%80%901235> (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235<tel:1%E2%80%90877%E2%80%90320%E2%80%901235> (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235<tel:1%E2%80%90877%E2%80%90320%E2%80%901235> (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235<tel:1%E2%80%90877%E2%80%90320%E2%80%901235> (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235<tel:1%E2%80%90877%E2%80%90320%E2%80%901235> (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235<tel:1%E2%80%90877%E2%80%90320%E2%80%901235> (TTY: 711)번으로 전화해 주십시오.

-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability or
sex. Humana Inc. and its subsidiaries do not exclude people or treat them differently
because of race, color, national origin, age, disability or sex.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.

Roy Boxwell

AW: Statement ID in EDM and SMF
(in response to Chris Hoelscher)
I know another one from a vendor close to my heart...

Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

On 19 Mar 2018, at 17:27, Chris Hoelscher <[login to unmask email]<mailto:[login to unmask email]>> wrote:

For ifcid 376 flagged dynamic SQL – we use DETECTOR from CA (formerly from Platinum) – in a batch process once daily
If you have this product I can send more details

Other products have similar options – but we did not find one to work as a batch extract

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com http://Humana.com
(502) 476-2538 or 407-7266

From: Walter Janißen [mailto:[login to unmask email]
Sent: Monday, March 19, 2018 12:24 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - AW: AW: Statement ID in EDM and SMF

Hi Leila

I’m still not sure, what you are after, because in one of your posts you mentioned ICFID376, which reports incompatibilities. This trace contains a stmtno, but I am pretty sure that this is not the stmtid, that the statement might have gotten in the DSC. This stmtno is the precompiler-stmtno of the package, which executes the dynamic statement.
IFCID376 can also be written for static statements and for dynamic ones, which were never in the cache.
For these 376-statements I don’t know, how you can get the statement-text.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Leila hosseini [mailto:[login to unmask email]
Gesendet: Montag, 19. März 2018 16:45
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: AW: Statement ID in EDM and SMF

I did EXPLAIN STMTCACHE ALL 2 times each day,it has about 2000 records each day.but SMF records for specific IFCID is 10 per day.


Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS

On Monday, March 19, 2018, 11:07 AM, alain pary <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Hello,

Why don't you make 1 or more times by day an EXPLAIN STMTCACHE ALL . The number of times is related to your time residency in the Stmt_cache.

With this you will increase the number of finding on stmdid .

This can also help you to have a view upon the dynamic statement use in your company .



regards



Alain

-----End Original Message-----

-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability or
sex. Humana Inc. and its subsidiaries do not exclude people or treat them differently
because of race, color, national origin, age, disability or sex.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.

-----End Original Message-----

Chris Hoelscher

AW: Statement ID in EDM and SMF
(in response to Roy Boxwell)
Well we already licensed detector, ez-db2, and TMON for DB2 – so I had the freedom to pick any solution in the world I wanted as long as it was one of those three …..

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com
(502) 476-2538 or 407-7266

From: Boxwell, Roy [mailto:[login to unmask email]
Sent: Monday, March 19, 2018 1:48 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: AW: Statement ID in EDM and SMF

I know another one from a vendor close to my heart...
Roy Boxwell
SOFTWARE ENGINEERING GmbH and SEGUS Inc.
-Product Development-
Heinrichstrasse 83-85
40239 Düsseldorf/Germany
Tel. +49 (0)211 96149-675
Fax +49 (0)211 96149-32
Email: [login to unmask email]<mailto:[login to unmask email]>
http://www.seg.de

Software Engineering GmbH
Amtsgericht Düsseldorf, HRB 37894
Geschäftsführung: Gerhard Schubert

On 19 Mar 2018, at 17:27, Chris Hoelscher <[login to unmask email]<mailto:[login to unmask email]>> wrote:
For ifcid 376 flagged dynamic SQL – we use DETECTOR from CA (formerly from Platinum) – in a batch process once daily
If you have this product I can send more details

Other products have similar options – but we did not find one to work as a batch extract

Chris Hoelscher
Technology Architect, Database Infrastructure Services
Technology Solution Services
Humana Inc.
123 East Main Street
Louisville, KY 40202
Humana.com http://Humana.com
(502) 476-2538 or 407-7266

From: Walter Janißen [mailto:[login to unmask email]
Sent: Monday, March 19, 2018 12:24 PM
To: [login to unmask email]<mailto:[login to unmask email]>
Subject: [DB2-L] - AW: AW: Statement ID in EDM and SMF

Hi Leila

I’m still not sure, what you are after, because in one of your posts you mentioned ICFID376, which reports incompatibilities. This trace contains a stmtno, but I am pretty sure that this is not the stmtid, that the statement might have gotten in the DSC. This stmtno is the precompiler-stmtno of the package, which executes the dynamic statement.
IFCID376 can also be written for static statements and for dynamic ones, which were never in the cache.
For these 376-statements I don’t know, how you can get the statement-text.

Kind regards
Walter Janißen [standard_IBM+Champ+7+Yr+Analytics]

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]<mailto:[login to unmask email]>

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

Von: Leila hosseini [mailto:[login to unmask email]
Gesendet: Montag, 19. März 2018 16:45
An: [login to unmask email]<mailto:[login to unmask email]>
Betreff: [DB2-L] - RE: AW: Statement ID in EDM and SMF

I did EXPLAIN STMTCACHE ALL 2 times each day,it has about 2000 records each day.but SMF records for specific IFCID is 10 per day.


Sent from Yahoo Mail for iPhone https://overview.mail.yahoo.com/?.src=iOS

On Monday, March 19, 2018, 11:07 AM, alain pary <[login to unmask email]<mailto:[login to unmask email]>> wrote:

Hello,

Why don't you make 1 or more times by day an EXPLAIN STMTCACHE ALL . The number of times is related to your time residency in the Stmt_cache.

With this you will increase the number of finding on stmdid .

This can also help you to have a view upon the dynamic statement use in your company .



regards



Alain

-----End Original Message-----

-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability or
sex. Humana Inc. and its subsidiaries do not exclude people or treat them differently
because of race, color, national origin, age, disability or sex.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.
-----End Original Message-----

-----End Original Message-----

The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability or
sex. Humana Inc. and its subsidiaries do not exclude people or treat them differently
because of race, color, national origin, age, disability or sex.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.

Leila Hosseini

AW: Statement ID in EDM and SMF
(in response to Chris Hoelscher)
Chris,Thanks for sending details,as I have checked we do not have DETECTOR licence now.Regards Leila


Sent from Yahoo Mail for iPhone


On Monday, March 19, 2018, 1:25 PM, Chris Hoelscher <[login to unmask email]> wrote:

#yiv0594964347 #yiv0594964347 -- _filtered #yiv0594964347 {panose-1:2 11 6 9 7 2 5 8 2 4;} _filtered #yiv0594964347 {panose-1:2 4 5 3 5 4 6 3 2 4;} _filtered #yiv0594964347 {font-family:Calibri;panose-1:2 15 5 2 2 2 4 3 2 4;} _filtered #yiv0594964347 {panose-1:3 15 7 2 3 3 2 2 2 4;} _filtered #yiv0594964347 {panose-1:2 11 5 3 2 0 0 2 0 4;} _filtered #yiv0594964347 {} _filtered #yiv0594964347 {panose-1:2 11 6 9 7 2 5 8 2 4;}#yiv0594964347 #yiv0594964347 p.yiv0594964347MsoNormal, #yiv0594964347 li.yiv0594964347MsoNormal, #yiv0594964347 div.yiv0594964347MsoNormal {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv0594964347 a:link, #yiv0594964347 span.yiv0594964347MsoHyperlink {color:blue;text-decoration:underline;}#yiv0594964347 a:visited, #yiv0594964347 span.yiv0594964347MsoHyperlinkFollowed {color:purple;text-decoration:underline;}#yiv0594964347 p {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv0594964347 p.yiv0594964347msonormal0, #yiv0594964347 li.yiv0594964347msonormal0, #yiv0594964347 div.yiv0594964347msonormal0 {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv0594964347 p.yiv0594964347yahoo-quoted-begin, #yiv0594964347 li.yiv0594964347yahoo-quoted-begin, #yiv0594964347 div.yiv0594964347yahoo-quoted-begin {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv0594964347 p.yiv0594964347msonormal0, #yiv0594964347 li.yiv0594964347msonormal0, #yiv0594964347 div.yiv0594964347msonormal0 {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv0594964347 p.yiv0594964347yahoo-quoted-begin, #yiv0594964347 li.yiv0594964347yahoo-quoted-begin, #yiv0594964347 div.yiv0594964347yahoo-quoted-begin {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv0594964347 p.yiv0594964347msonormal, #yiv0594964347 li.yiv0594964347msonormal, #yiv0594964347 div.yiv0594964347msonormal {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv0594964347 p.yiv0594964347msochpdefault, #yiv0594964347 li.yiv0594964347msochpdefault, #yiv0594964347 div.yiv0594964347msochpdefault {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv0594964347 p.yiv0594964347msonormal1, #yiv0594964347 li.yiv0594964347msonormal1, #yiv0594964347 div.yiv0594964347msonormal1 {margin:0in;margin-bottom:.0001pt;font-size:12.0pt;}#yiv0594964347 p.yiv0594964347msonormal01, #yiv0594964347 li.yiv0594964347msonormal01, #yiv0594964347 div.yiv0594964347msonormal01 {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv0594964347 p.yiv0594964347yahoo-quoted-begin1, #yiv0594964347 li.yiv0594964347yahoo-quoted-begin1, #yiv0594964347 div.yiv0594964347yahoo-quoted-begin1 {margin-right:0in;margin-left:0in;font-size:12.0pt;}#yiv0594964347 p.yiv0594964347msochpdefault1, #yiv0594964347 li.yiv0594964347msochpdefault1, #yiv0594964347 div.yiv0594964347msochpdefault1 {margin-right:0in;margin-left:0in;font-size:10.0pt;}#yiv0594964347 span.yiv0594964347msohyperlink {}#yiv0594964347 span.yiv0594964347msohyperlinkfollowed {}#yiv0594964347 span.yiv0594964347emailstyle20 {}#yiv0594964347 span.yiv0594964347emailstyle21 {}#yiv0594964347 span.yiv0594964347msohyperlink1 {color:blue;text-decoration:underline;}#yiv0594964347 span.yiv0594964347msohyperlinkfollowed1 {color:purple;text-decoration:underline;}#yiv0594964347 span.yiv0594964347emailstyle201 {color:#1F497D;font-weight:normal;font-style:normal;text-decoration:none none;}#yiv0594964347 span.yiv0594964347emailstyle211 {color:#1F497D;}#yiv0594964347 span.yiv0594964347EmailStyle36 {color:#1F497D;}#yiv0594964347 .yiv0594964347MsoChpDefault {font-size:10.0pt;} _filtered #yiv0594964347 {margin:1.0in 1.0in 1.0in 1.0in;}#yiv0594964347 div.yiv0594964347WordSection1 {}#yiv0594964347
I use CA SMF Director / MICS / MXG to pull the ifcid I want – sort by date time / userid / program name / corr id / workstation / location

I turn on KEYS in my detector collections (and dynamic sql text) – and turn on all keys in my detector collection profile

 

I do a nightly download of detector per SSID: (this is for distributed only)

 

DATASTORE=DATASTOR  

DATATYPE=STAN       

UNLOAD=Y             

UNLDOBJ=N           

UNLDKEYS=Y          

UNLDTEXT=D          

AGGREGATE=N         

COLLID=NULLID       

STARTDATE=03/18/2018

STARTTIME=00:00     

ENDDATE=03/19/2018  

ENDTIME=00:01       

VCAT=DBP1S          

SSID=DBP1           

 

Then I match the keys on the ifcid 376 extract to the keys on the detector extract, try to drop as many duplicates as detectable

(there can be false positives) – for distributed – the program name is mostly useless, the section / statement seem to be totally useless

 

Chris Hoelscher

Technology Architect, Database Infrastructure Services

Technology Solution Services

Humana Inc.

123 East Main Street

Louisville, KY 40202

Humana.com

(502) 476-2538 or 407-7266

 

From: Leila hosseini [mailto:[login to unmask email]
Sent: Monday, March 19, 2018 1:06 PM
To: [login to unmask email]
Subject: [DB2-L] - RE: AW: Statement ID in EDM and SMF

 

Hello Chris
I have used PGM=NSIGHTRW
Which is the CA batch report to extract IFCID376 records from SMF and load as DB2 table .

Now I need to know these SMF records are related to which SQL statement

Thanks for your help
Sent from Yahoo Mail for iPhone

On Monday, March 19, 2018, 12:27 PM, Chris Hoelscher <[login to unmask email]> wrote:


For  ifcid 376 flagged dynamic SQL – we use DETECTOR from CA (formerly from Platinum) – in a batch process once daily

If you have this product I can send more details

 

Other products have similar options – but we did not find one to work as a batch extract

 

Chris Hoelscher

Technology Architect, Database Infrastructure Services

Technology Solution Services

Humana Inc.

123 East Main Street

Louisville, KY 40202

Humana.com

(502) 476-2538 or407-7266

 

From: Walter Janißen [mailto:[login to unmask email]
Sent: Monday, March 19, 2018 12:24 PM
To: [login to unmask email]
Subject: [DB2-L] - AW: AW: Statement ID in EDM and SMF

 

Hi Leila

 

I’m still not sure, what you are after, because in one of your posts you mentioned ICFID376, which reports incompatibilities. This trace contains a stmtno, but I am pretty sure that this is not the stmtid, that the statement might have gotten in the DSC. This stmtno is the precompiler-stmtno of the package, which executes the dynamic statement.

IFCID376 can also be written for static statements and for dynamic ones, which were never in the cache.

For these 376-statements I don’t know, how you can get the statement-text.

 

Kind regards
Walter Janißen 

ITERGO Informationstechnologie GmbH
Anwendungsentwicklung
Technische Anwendungsarchitektur
Victoriaplatz 2
D-40198 Düsseldorf
[login to unmask email]

ITERGO Informationstechnologie GmbH
Vorsitzender des Aufsichtsrats: Christian Diedrich
Geschäftsführung: Dr. Bettina Anders (Vorsitzende),
Lothar Engelke, Ina Kirchhof, Dr. Michael Regauer
Sitz: Düsseldorf, Handelsregister: Amtsgericht Düsseldorf HRB 37996

 

Von: Leila hosseini [mailto:[login to unmask email]
Gesendet: Montag, 19. März 2018 16:45
An: [login to unmask email]
Betreff: [DB2-L] - RE: AW: Statement ID in EDM and SMF

 

I did EXPLAIN STMTCACHE ALL  2 times each day,it has about 2000 records each day.but SMF records for specific IFCID is 10 per day.


Sent from Yahoo Mail for iPhone

On Monday, March 19, 2018, 11:07 AM, alain pary <[login to unmask email]> wrote:


Hello, 

Why don't  you make 1 or more times by day an EXPLAIN STMTCACHE ALL . The number of times is related to your time residency in the Stmt_cache. 

With this you will increase the number of finding on stmdid .

This can also help you to have a view upon the dynamic statement use in your company . 

 

regards 

 

Alain

 

-----End Original Message-----

 

-----End Original Message-----



The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability or
sex. Humana Inc. and its subsidiaries do not exclude people or treat them differently
because of race, color, national origin, age, disability or sex.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로전화해 주십시오.

 
-----End Original Message-----
The information transmitted is intended only for the person or entity to which it is addressed
and may contain CONFIDENTIAL material. If you receive this material/information in error,
please contact the sender and delete or destroy the material/information.

Humana Inc. and its subsidiaries comply with applicable Federal civil rights laws and
do not discriminate on the basis of race, color, national origin, age, disability or
sex. Humana Inc. and its subsidiaries do not exclude people or treat them differently
because of race, color, national origin, age, disability or sex.

English: ATTENTION: If you do not speak English, language assistance services, free
of charge, are available to you. Call 1‐877‐320‐1235 (TTY: 711).

Español (Spanish): ATENCIÓN: Si habla español, tiene a su disposición servicios
gratuitos de asistencia lingüística. Llame al 1‐877‐320‐1235 (TTY: 711).

繁體中文(Chinese):注意:如果您使用繁體中文,您可以免費獲得語言援助
服務。請致電 1‐877‐320‐1235 (TTY: 711)。

Kreyòl Ayisyen (Haitian Creole): ATANSION: Si w pale Kreyòl Ayisyen, gen sèvis èd
pou lang ki disponib gratis pou ou. Rele 1‐877‐320‐1235 (TTY: 711).

Polski (Polish): UWAGA: Jeżeli mówisz po polsku, możesz skorzystać z bezpłatnej
pomocy językowej. Zadzwoń pod numer 1‐877‐320‐1235 (TTY: 711).

한국어 (Korean): 주의: 한국어를 사용하시는 경우, 언어 지원 서비스를 무료로
이용하실 수 있습니다. 1‐877‐320‐1235 (TTY: 711)번으로 전화해 주십시오.

Site Links: View post online   View mailing list online   Start new thread via email   Unsubscribe from this mailing list   Manage your subscription  

This email has been sent to: [login to unmask email]
** ** ** IDUG DB2 Data and Analytics Technical Summit in Bengaluru, India 2018 ** ** **
---> Bengaluru, India, March 27, 2018 <---
http://ibm.biz/IDUGBengaluru2018



Use of this email content is governed by the terms of service at:
http://www.idug.org/p/cm/ld/fid=2