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 "Turnidge, Dave" <DTurnidge@xxxxxxxxxxxxxxxxxxxx> Sent by: rpg400-l-bounces@xxxxxxxxxxxx 10/27/2006 10:49 AM Please respond to RPG programming on the AS400 / iSeries <rpg400-l@xxxxxxxxxxxx> To "RPG programming on the AS400 / iSeries" <rpg400-l@xxxxxxxxxxxx> cc Subject Embedded SQL newby question... I have a file defined as: JRSYS K1 A 8 1 8 System JRONAM A 10 9 18 Original Object Name JRFNAM K4 A 10 19 28 Final Object name JRCDAT K2 L 10 29 38 Creation date (YYYYMMDD) JRCTIM K3 T 8 39 46 Creation time (HHMMSS) JRVOL A 10 47 56 Volume JRSEQ P 5 0 57 59 Sequence number JROSIZ P 15 0 60 67 Original Size JRSSIZ P 15 0 68 75 Saved Size JRNOTE A 50 76 125 NOTE Here is the first record in that file: System..................: MD400 Original Object Name....: QUDRCV1321 Final Object name.......: QUDRCV1321 Creation date (YYYYMMDD): 0001-01-01 Creation time (HHMMSS)..: 00.00.00 Volume..................: AUD001 Sequence number.........: 209 Original Size...........: 205672448 Saved Size..............: 45297664 NOTE....................: This is my data structure: ** Data Structure for SQL Fetch d DetailStr ds inz d jrsys 8a d jronam 10a d jrfnam 10a d jrcdat 10d d jrctim 8t d jrvol 10a d jrseq 5p 0 d jrosiz 15p 0 d jrssiz 15p 0 d jrnote 50a This is my SQL statement: ** Select records from SAVHISTORY c/exec sql c+ Declare DtlSelect Cursor c+ For Select jrsys, c+ jronam, c+ jrfnam, c+ jrcdat, c+ jrctim, c+ jrvol, c+ jrseq, c+ jrosiz, c+ jrssiz, c+ jrnote c+ From $auditstg/savhistory c+ Order by jrsys, c+ jrcdat, c+ jrctim, c+ jrfnam c/end-exec I am getting the following CPF5035 error: Message . . . . : Data mapping error on member SAVHISTORY. Cause . . . . . : A data mapping error occurred on field DbopExprKey(QdsSortedPtl(Node_11), SAVHISTORY_1.JRCDAT, 1) in record number 0, record format *FIRST, member number 1, in member SAVHISTORY file SAVHISTORY in library $AUDITSTG, because of error code 18. The error codes and their meanings follow: 18 -- There is data in a date, time, or timestamp field that is not valid. The following SQL0181 error follows the previous: Message . . . . : Value in date, time, or timestamp string not valid. Cause . . . . . : The string representation of a date, time or timestamp value is not in the acceptable range. JRCDAT is either the character string constant that is not valid or the column or host variable that contained the string. If the name is *N, then the value was found in an expression specified in the statement. If the value was found in a host variable, then the host variable number is 4. The proper ranges for date, time, or timestamp values are as follows: -- The range for years is from 0001 to 9999. -- The range for months is from 1 to 12. -- The range for days is from 1 - 30 for April, June, September, and November, from 1 - 28 for February and from 1 to 31 for all other months. In a leap year, the range for February can be from 1 to 29. -- The range for days in a Julian date is from 001 to 366 for a leap year or 001 to 365 days for all other years. -- The range for hours is from 0 to 24. If the hour is 24, then the other parts of the time values must be zeros. If the time format is USA, then the hour cannot be greater than 12. -- The range for minutes is from 0 to 59. -- The range for seconds is from 0 to 59. -- The range for microseconds is from 0 to 999999. Recovery . . . : Ensure that the date, time, or timestamp value conforms to the ranges for the data type it represents. Try the request again. I believe my issue has to do with defining the date/time field, and I don't know what to put where to fix it. Please direct me to a page in a manual that describes what I have done and how to fix it. Or, if it's simple... just respond gently... :-) Thank you, Dave -- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l. -- This is the RPG programming on the AS400 / iSeries (RPG400-L) mailing list To post a message email: RPG400-L@xxxxxxxxxxxx To subscribe, unsubscribe, or change list options, visit: http://lists.midrange.com/mailman/listinfo/rpg400-l or email: RPG400-L-request@xxxxxxxxxxxx Before posting, please take a moment to review the archives at http://archive.midrange.com/rpg400-l.