RE: SQL0312 on UPDATE but not on INSERT

Peter,

As far as RRN.  I'm concerned that with just the code shown, there's no 
assurance that the RRN of a record in the source file has the same RRN in the 
destination file.

Consider the effects of REUSEDLT(*YES), how can you be sure that a recorded 
added to the source at RRN 20 gets added to the destination at RRN 20.  Also 
consider a RGZPFM on the source file, how can you assure that the file is 
destination stays in sync.

Additionally, there's a need to make sure that the files are in sync and 
provide some method to correct the problem if not.

I've looked at iTera, IIRC one thing they do is compare the source before 
update record to the destination before update record.  How exactly they do 
this I'm not sure because as I remember they don't require BEFORE & AFTER 
images to be journaled!

I guess what I'm saying is that I consider this a pretty complex OS level task. 
 Not that you couldn't do it, but I know it wouldn't be cost effective for me 
to write my own.  Particularly if I were to try to provide an equivalent level 
of features/functionality.

Has the client looked at the HA packages?  They are considerably cheaper than 
they used to be.  iTera was I believe <30K for a P20.

As far as the searching. I used the following google search:
http://www.google.com/search?q=update+%22set+ROW%22+DB2&hl=en&lr=&safe=off&start=90&sa=N

I found the link to archives.midrange.com on page 10.

HTH,

Charles Wilt
iSeries Systems Administrator / Developer
Mitsubishi Electric Automotive America
ph: 513-573-4343
fax: 513-398-1121
 

> -----Original Message-----
> From: rpg400-l-bounces+cwilt=meaa.mea.com@xxxxxxxxxxxx
> [mailto:rpg400-l-bounces+cwilt=meaa.mea.com@xxxxxxxxxxxx]On Behalf Of
> Peter Dow
> Sent: Wednesday, April 13, 2005 4:57 PM
> To: RPG programming on the AS400 / iSeries
> Subject: Re: SQL0312 on UPDATE but not on INSERT
> 
> 
> Hi Charles,
> 
> Well, the RRN works fine on the DELETE; do you know of some 
> reason why it
> wouldn't on the update?  Actually, it also works on the 
> update if (as you
> suggested) if the individual fields are specified -- we tried that
> yesterday.
> 
> Yes, this is a simple setup to keep a hot site ready for a 
> customer (a very
> small customer running a 170 with v4r4). If they were big 
> enough, we would
> definitely recommend a package; but in this situation, I get 
> to learn a
> little something.  It looks like we're out of luck even if 
> they currently
> have modified the precompiler to allow host structures.  I 
> was trying to
> avoid having to specify individual fields, in fact, the more 
> generic the
> program, the better -- there are 27 files to handle.  Volume 
> is seasonal,
> and even in the peak season is not all that big.
> 
> Thanks for the research! Btw, what did you search for?  I 
> think I got hung
> up looking for SQL0312 when I should've expanded my search 
> horizons. And all
> the documentation I read (SQL Reference Manual, SQL 
> Programming with Host
> Languages, etc.) seemed to indicate it would work, e.g. from the SQL
> Programming with Host Languages:
> 
> "The ILE RPG for iSeries data structure name can be used as a 
> host structure
> name if subfields exist in
> the data structure. The use of the data structure name in an 
> SQL statement
> implies the list of subfield
> names making up the data structure."
> 
> and from the SQL Reference Manual:
> 
> "A host structure can be referenced in any context where a 
> list of host
> variables can be referenced. A host structure reference is 
> equivalent to a
> reference to each of the host variables contained within the 
> structure in
> the order which they are defined in the host language structure
> declaration."
> 
> There was nothing in the description of the UPDATE ... SET ROW = ..
> statement that indicated you couldn't use a host structure.
> 
> Peter Dow
> Dow Software Services, Inc.
> 909 793-9050 voice
> 909 793-4480 fax
> 951 522-3214 cell
> 
> ----- Original Message ----- 
> From: "Wilt, Charles" <CWilt@xxxxxxxxxxxx>
> 
> 
> > Found this in a SQL Precompiler Enhancement Survey:
> > http://archive.midrange.com/rpg400-l/200106/msg00104.html
> >
> > "g. Support for host structure in Update statements
> >
> > The current version of the SQL precompiler lets you specify a data
> structure
> > in place of a list of host variables in the Select Into, 
> Fetch, and Insert
> > statements. The precompiler treats a data structure as if 
> you specified a
> > list of the subfields within the data structure. This 
> SQL/400 extension
> can
> > simplify coding and improve performance in some situations. This
> precompiler
> > enhancement would let you specify a data structure in an 
> Update statement,
> > as well. For example, you might code: Update T Set Row = ( 
> :MyStructure )"
> >
> > Note that it's from 2001, but perhaps this enhancement has 
> never made it
> to production.
> >
> > I'd give IBM a call to double check.
> 
> 
> 
> -- 
> No virus found in this outgoing message.
> Checked by AVG Anti-Virus.
> Version: 7.0.308 / Virus Database: 266.9.8 - Release Date: 4/13/2005
> 
> 
> -- 
> 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.
> 
>