i5/OS V5R4 performance issue

[Udyan]

On our database we have a sort of latency issues with db2. These latencies reach a totally random.

A single query will go through such (at its first use) in 1sec then if I redo the test after 10 min (so that it is the access map in the SQE plan cache) it will take 7sec and something like that.

I could make DBMON to see that it was the FETCH stage that was problematic. OK, but I do not know what to do with this, or explain why we have such behavior.

Is there a tool that will see at once if the fetch we have a lock for example that we blocked? Do you have any other line of research to which I can turn my attention to understand why we have such cases?

[Modifier]

Few questions:

Your files, are they logged.
Are you under commitment control?
> your cursor, is it declared the WITH NC
> or set option commit *none

The fetch in question is that the first fetch?

[Udyan]

So my files are logged and it has the option 42. We are committed to control: uncommitted read. Its a java application that attack our db via jdbc connection with the driver jt400. (or via a layer beaver .. it's like hibernate)

For management db2 cursors is who manages his sauce in the job QZDASOINIT (not 100% for this). I made my tests using the interface of sql iseries to go through QZDASOINIT.

I have 2 DBMON for the same query with the problem, a case made with our Java application and the other made the request directly with the SQL interface.

In 2 cases the complaint has been fully constructed (access map and odp), and it was a hard close of the cursor.

And this is the 1st fetch that can be long. I am aware that the 1st use of the application will be longer given that db2 will do this but temp oscillates between 1sec and 7sec for this search query.

More generally we have this problem on another application, we test and why.

[CodGuru]

A single query will go through such (at its first use) in 1sec then if I redo the test after 10 min (so that it is the access map in the SQE plan cache) it will take 7sec and something like that.

I do not understand the comment "so that we [had] more in terms of access in the plan sqe cache. Can you explain?

Same question for the hard close:

In 2 cases the complaint has been fully constructed (access map and odp), and it was a hard close of the cursor.

How does SQL
The first time a SQL statement is executed, the entire optimization procedure is implemented, ie
- Rewriting the SQL statement,
- Control of all indexes (including DDS)
- Creation of temporary objects necessary to accomplish the request (hash tables, lists of NHS, bitmaps, etc.)
- Creating a map that defines the method by which access to data, which indexes and use temporary objects which may be constructed,
- Creation of the ODP (Open Data Path).

At the end of the first execution of the SQL, the cursor is being a hard-close and the ODP is deleted. In the second pass of the same instruction, the access plan is validated and the ODP is reopened. From the third passage in the same direction, the ODP remains open and only the data are updated in the ODP. You can see that the performance of the entire optimization procedure is time consuming. Remember that this procedure is performed for each SQL statement.

Impact Close SQL Cursor
We must distinguish between "hard close" and "soft close" of the cursor.
When the instruction "Close cursor" is executed, it accomplishes a soft close, which means that the ODP is left open and that only feels refreshed data in this temporary object following the execution of this same instruction.

When a cursor is closed by a hard close, ODP is destroyed. Therefore, when the same SQL statement is re-executed, the entire optimization procedure is again set to work with all the overhead that implies.

[Udyan]

We notice this problem during the 1st query execution. For these 2 points I wanted to clarify that the 2 tests I did with dbmon in support were with queries that had not yet been executed.

I understand the mechanism that you post, but I do not understand is why during the 1st run the same application (creation of the access plan creation odp +) I have a response time that varies between 1sec in 7sec and wheelbarrows.

Looking at the results of dbmon I notice that the stage of 'fetch' is slow.