ODP not deleted

[rx_b10]

hi all,

i've a problem
pls hlp me

After i insert into table

c/exec SQL
c+ INSERT INTO TABLEA
c+ VALUES :TBL#0
c/end-exec

then i see my joblog (DSPJOBLOG)

1 rows inserted in TABLEA in LUSDATA.
ODP not deleted.

then i see help

Message ID . . . . . . : SQL7914 Severity . . . . . . . : 00
Message type . . . . . : Information
Date sent . . . . . . : 09/12/07 Time sent . . . . . . : 10:49:18

Message . . . . : ODP not deleted.
Cause . . . . . : The Open Data Path (ODP) for this statement or cursor has
not been deleted. This ODP can be reused on a subsequent run of the
statement. This will not generate an entry in the journal.

but i don't understand
what wrong with my code ???
pls help me
thx u all

[B.Hauser]

Re: ODP not deleted

Hi,

your code is absolutely correct.

If the ODP is not deleted it is always a good sign. That means for all subsequent execution of the insert statement the alreaday opened ODP can be reused.
For each SQL-Statement that will be executed an access plan must be built or at least validated, that means all access paths (either in keyed logical files or SQL indexes) must be validated and the needed temporäry objects (such as Hash Tables, relative record lists) must be determined. After the access plan is built, the data path must be opened (ODP), i.e. the temporary objects must be built and filled with data. This optimation process takes the most time when executing a SQL statement.

If the ODP is not deleted after the execution of a SQL statement, the temporary objects will not be deleted. The next time the SQL statement will be executed in the same job, only the data in the temporary objects must be actualized. That means all subsequent exeutions of the same statement in the same job, perform much better than the previous executions.

Birgitta

[rx_b10]

Re: ODP not deleted

ok thx u very much
so no problem in my job log if there are "ODP not Deleted" .
the query will be faster, coz temporary object not deleted, isn't it?

thx u B.Hauser

[BenThurley]

Re: ODP not deleted

Is there any occasion where you might not want to reuse the ODP?

[bryce4president]

Re: ODP not deleted

sure, I have one. There is a certain bug in one of our ERP programs that causes a slight error in our data. Its just a misplaced value. This probably happens about 3-4 times a month. So we have a standard sql statement that we just insert our new value into the statement and it fixes it. Since its the same problem we are using the same SQL statement, therefore the ODP path not being deleted is a good thing.

[BenThurley]

Re: ODP not deleted

The ODP is for selects as well isn't it? I heard something somewhere that it can sometimes be bad for performance if it decides to use an ODP that it really shouldn't. What would happen if you had one really specific search, maybe on a whole customers name, followed by a similar search but nowhere near as specific, for example all customers whose name begins in A?

Is this perhaps a concern that is out of date due to better SQL engines?

[kast1]

Re: ODP not deleted

I believe the non delete of the ODP is new for V5R4 and the temp object will remain until the next IPL. To Ben's question I don't think the ODP can have negative affects on a query, if a new temp object needs to be created because a "new" search needs to be done another ODP will be created anyway, OR the SQL engine will use the ODP from the previous query if necessary. Either way the fact that ODP remains out there is a benefit.

[B.Hauser]

Re: ODP not deleted

Hi,

I think I have to explain some more details:

First we speak about static SQL in programs:
1. The first time a static SQL statement in a program is performed, the full optimization or a full open must be executed. An access path must be created or at least validated. That means all indexes and statistics must be checked and the optimal access paths determined. Also, the optimizer must determine which temporary objects with which depencies must be created. After the OPD will be opened, i.e. the temporary objects get created and filled with data.
After the first execution of the SQL statement, always a hard close will be performed, that means all temporary objects neccessary for executing the SQL-statement will be deleted. In this way it's no problem to exeute a specific query only once or twice a month. The ODP will be deleted anyway.

2. The second time the same SQL statement in the same activation group in the same job will be executed, the already created access plan is validated again. After the ODP will be recreated. If the ODP is reuseable, the temporary objects will not be deleted after the second execution. For all subsequent executions only the data in the temporary objects will be actualized.

This behaviour is not new to release V5R4 and has nothing to do with query engine that is used. The newer SQE (SQL Query Engine) normally performs much better than the older CQE (Classic query engine), because it is written in an OO desing and can act more specific.

If dynamic SQL (embedded dynamic SQL, interactive SQL) is used, the reusabilty of ODPs is different. A full open must always be performed after a prepare statement. If you work without parameter markers and prepare several times the same SQL statment, each time the statement is prepared and opened a full open (full optimization) must be performed. Contrary to static SQL no access plan gets stored in the program object. That means if the SQL-statement is performed by the CQE the access plan must be built by scratch. If the SQL-statement is performed by the SQE an access plan stored in the SQE plan cache can be valiated. Up to 3 access plan can be stored for each SQL statement.

If an ODP rests open and new access paths (either keyed logical files or SQL indexes) are created, that may help the SQL statement to perform better, no new optimization will be performed.
To get advantage of the new access plans the ODPs first must be deleted.
To delete an opened ODP, you have to end the activation group in which the program runs that contains the SQL statement. If the default activation group is used and the files are not qualified in the SQL statement, removing and readding the data library to the library list will also hard close the ODPs.

... and each SQL statement gets its own ODP. That means if the same SQL statement is coded and executed several times in different programs, procedures oder subroutines, for each of these statements a full open must be performed. Minimizing ODPs means, externalizing SQL-Statements into procedures, that run in service programs in named activation groups.

What's new with release V5R4 are MTIs (maintained temporary indexes). Before release V5R4 the SQE did not create temporary indexes, because to create an index several tables scans are necessary. In this way a single table scan was prefered. It was only the CQE that could create temporary indexes. The disadvantage of these temporary indexes was, if the same statement was performed in several jobs, for each of these jobs the temporary index with all overhead was created. The new MTIs are quasi permanent indexes, that can be used from all jobs. Information about these MTIs are stored in the access plans in the SQE plan cache. MTIs stay available until the last access plan that uses a specific MTI was removed from the SQE plan cache. ... and the SQE plan cache gets cleared with IPL.

Birgitta

[kast1]

Re: ODP not deleted

Thanks Birgitta

You cleared up some confusing information on ODP creation/deletion that I found on various links and blogs, that was one of best ODP explanations I have read.

[B.Hauser]

Re: ODP not deleted

that was one of best ODP explanations I have read.

Thanks

Birgitta

[kishor]

Re: ODP not deleted

Hi,

m facing a prob here.. my job is generating so many spool files with this message repeating alternatively after each insert "OPD not cleared".

could someone please suggest how to stop generating so many job logs

thanks

[kishor]

Re: ODP not deleted

got it.......... those joblogs are getting generated only when program executes in debug mode...