RE: Embedded SQL newby question...

Thank you. I've been wondering when I would have a question to ask that would 
receive a response from the "guru" :-)  I hope Rob responds with why he uses 
the other method.

Thanks again,

Dave 

-----Original Message-----
From: rpg400-l-bounces@xxxxxxxxxxxx [mailto:rpg400-l-bounces@xxxxxxxxxxxx] On 
Behalf Of HauserBirgitta
Sent: Friday, October 27, 2006 10:33 AM
To: RPG programming on the AS400 / iSeries
Subject: Re: Embedded SQL newby question...

Hi,

I'd not suggest to use CLOSQLCSR set to *ENDMOD. It's a performance killer.

This option closes all ODPs (Open Data Path) that are used to access the 
database data, each time the module will be ended.
Consequently each time the module gets called the complete optimization process 
must be completely executed, i.e. an access plan must be built or at least 
validated, the optimal index must be searched and finally temporary objects 
that are neccessary (such as hash tables) must be created, before the data can 
be accessed.

When *ENDACTGRP is used instead, the optimization process is executed only the 
first and second time. That means the ODP gets automatically deleted after the 
first run, but stays open after the second. (assumed the ODP is reuseable). 
Beginning with the third execution the ODP will be reused without any 
additional optimization.

The ODP stays open, until the activation group gets closed, or overrides on the 
tables will be deleted, or if the library list get changed when *SysNaming is 
used and the tables are used unqualified.

Mit freunlichen Grüßen / Best regards

Birgitta Hauser

"Shoot for the moon, even if you miss, you'll land among the stars." (Les
Brown)
"If you think education is expensive, try ignorance." (Derek Bok)




----- Original Message ----- 
From: <rob@xxxxxxxxx>
To: "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx>
Sent: Friday, October 27, 2006 17:06
Subject: Re: Embedded SQL newby question...

Could be an SQL vs rpg date format.  And, yes, it would be nice if it was
true that "a date is a date is a date".  However take a look at adding the
following, (especially the date format option):
    C/EXEC SQL
    C+ Set Option
    C+     Naming    = *Sys,
    C+     Commit    = *None,
    C+     UsrPrf    = *User,
    C+     DynUsrPrf = *User,
    C+     Datfmt    = *iso,
    C+     CloSqlCsr = *EndMod
    C/END-EXEC
This is the SQL equivalent of the H spec.

Rob Berendt
-- 
Group Dekko Services, LLC
Dept 01.073
PO Box 2000
Dock 108
6928N 400E
Kendallville, IN 46755
http://www.dekko.com