Re: Embedded SQL + Closing an ODP

[DAsmussen@xxxxxxx]

Joe!

In a message dated 2/11/01 4:31:49 PM US Eastern Standard Time, 
joepluta@PlutaBrothers.com writes:

> I wonder if this is a normal behavior of embedded SQL.  I've recently begun
>  working with the latest version of the BPCS ERP package, and have come
>  across a rather nasty situation.  It seems that what SSA did was to create 
a
>  fat order entry client in C, then when their fat client initiative failed,
>  they rewrote the C code into AS/SET code on the AS/400 and made it into a
>  green screen.  I'm sure you can see that there are quite a few scary things
>  in that statement, but let's move to the specific one.
>  
>  Evidently, the C programmer was fond of using SELECT INTO, and in fact 
would
>  use this regularly to retrieve single fields from different files.  These
>  got translated back to the AS/400 as embedded SQL in the generated RPG code
>  (AS/SET, for those who haven't worked with it, is basically a CASE tool 
that
>  generates RPG code).
>  
>  So now we have a 44000-line (yes, that's 44000 - as in cannot be edited by
>  SEU) RPG program with embedded SQL.  I ran the program recently to try and
>  see where a certain field was getting updated, and for the heck of it, I 
did
>  a quick check of the open files using option 14 of DSPJOB.  I almost choked
>  when I saw that there were FOUR HUNDRED AND FORTY THREE open data paths.
>  Many files had 20 or more ODPs over them.  I am assuming that this is
>  because of the various SELECT INTO statements.
>  
>  What do you folks think?

Well, as I have been lead to believe by others more knowledgeable than I, the 
order entry program in its fat client form was _SUCH_ a pig and so slow that 
SSA purchased the green screen version from a company touted on BPCS-L.  
About a week after someone announced the availability of this product, it was 
suddenly no longer available to the general public.  One could assume that 
the green screen version was written in AS/Set.

The SELECT INTO problem is quite prevalent in any post-V5 version of BPCS due 
to three factors:

1.  Programmers used SELECT * whether they needed to or not in order to speed 
the release of V5 (remember, they moved you off the project just when we 
needed you most).
2.  ADK presents a 400 some-odd character limit as to how long an SQL 
statement can be so, intensive WHERE and ORDER/GROUP statements require that 
* be used to save space.
3.  OPM SQLRPG (which ADK generates to this day) has a 256 character 
limitation as to how long dynamically generated SQL statements can be for 
PREPARE logic, and thus * is used quite frequently there as well.

All three of the above utilize SELECT INTO specifying one or more data 
structures based on an externally defined file.  *ENDJOB helps the ODP 
situation, but not much unless the job is in batch.  Keeping the ODP open can 
be a good thing for interactive jobs if the program is well written, but you 
know most BPCS programs are not.  I'm afraid that RCLRSC is about your only 
alternative, except that I've encountered problems with that command and 
interactive BPCS itself.

Regards,

Dean Asmussen
Enterprise Systems Consulting, Inc.
Fuquay-Varina, NC  USA
E-mail:  DAsmussen@aol.com

"Fortune knocks but once, but misfortune has more patience." -- Laurence J. 
Peter
+---
| This is the Midrange System Mailing List!
| To submit a new message, send your mail to MIDRANGE-L@midrange.com.
| To subscribe to this list send email to MIDRANGE-L-SUB@midrange.com.
| To unsubscribe from this list send email to MIDRANGE-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---