RE: LVLCHK *NO

Peter wrote:
>> So how's the performance? I would assume not too bad or you wouldn't be
>> using that technique.

Honestly cannot remember. I think I remember something in the range of 10%
more time to do opens but other should know that better than I do.
Especially all the guys who do all their development in SQL RPG.

We use SQL in batch programs so the performance issues do not matter much
and you are only having to code one SQL statement as a driver. Coding a
screen program with a lot of tables would be a lot of work and, of course,
the big problem with SQL is that it provides a whole new level of
complexity. SQL picks its own way of processing and in many cases, that is
not what you want so you end up doing a lot of tuning and SQL is set based.
It tends to do real well processing large sets of data but not very good at
running single record. 

>> In which case, why not have a dynamically-linked file
>> type for RPG? Just specify the field names you're going to use and let
the
>> compiler find the type and size from the current file, then have the
program
>> determine the location at run time, just as with your SQL RPG program.

The problem, as I indicated in one of my previous posts, is not so much just
providing an ability to select fields. That means you have no ability to
transform data or do joins. You would still have to build logical files for
any transforms or joins. It would be an improvement over what we have now
but I don't think enough. What I have suggested is having the ability to
create something close to a logical file without some of the capabilities
and have that access object be stored with the program as I am pretty sure
SQL RPG does. 

Thanks. 

-----Original Message-----
From: Peter Dow [mailto:pcdow@yahoo.com]
Sent: Wednesday, October 06, 1999 5:53 PM
To: RPG400-L@midrange.com
Subject: Re: LVLCHK *NO


Thanks again Alan. Yes, that helps. It appears that at run time, the program
knows everything about the fields (size, type) except the location within
the record, so it can do the equivalent of a level check, and determine the
current locations of the field within the format when opening the file.

So how's the performance? I would assume not too bad or you wouldn't be
using that technique. In which case, why not have a dynamically-linked file
type for RPG? Just specify the field names you're going to use and let the
compiler find the type and size from the current file, then have the program
determine the location at run time, just as with your SQL RPG program.

Peter Dow
Dow Software Services, Inc.
909 425-0194 voice/fax


----- Original Message -----
From: Alan Campin <Alan.Campin@CaseLogic.com>
To: <RPG400-L@midrange.com>
Sent: Wednesday, October 06, 1999 12:56 PM
Subject: RE: LVLCHK *NO


> Peter wrote:
>
> >> If I understand your sample correctly, all that RPG code was generated
by
> >> the pre-compiler.
>
> That is correct. The only thing that was entered in the program was the
> various SQL statements that are now commented out. All the code you see
was
> generated by precompile.
>
> >> What I don't see is the data structure definition. When
> >> and by whom is that defined? Does the definition contain field sizes
and
> >> types?
>
> Here is the DDS for creating the external file and the Data Structure
> definition.
>
>    *_> CNLLSTSPLF SRCFILE(@2/@1) SRCMBR(@3)
>    *_> DLTF FILE(@5/@4)
>    *_> CRTPF FILE(@5/@4) SRCFILE(@2/@1) SRCMBR(@3) +
>    *_>         MBR(*NONE)
>   A                                      REF(FRF)
>   A          R R0035T01
>    *
>   A            ICINVN    R               REFFLD(INVN)
>   A            ICTYPP    R               REFFLD(TYPP)
>   A            ICINYY    R               REFFLD(INYY)
>   A            ICORNO    R               REFFLD(ORNO)
>   A            ICDUPF         1
>   A            ICPAPE         1
>
>   *--------------------------------------------------------------
>   * Define SQL data structure.
>  d zsin          e ds                  extname(IV0035_T01)
>   *--------------------------------------------------------------------
>
> Obviously this one was done with an external file. I just create a PF with
> *NONE members and use this to define the data structure. You could, also,
> just enter the definition manually. Either way works. It gets a bit more
> tricky when you want the null because you have to include a one byte field
> for each data field for the null indicator. Paul Conte has some great
stuff
> on SQL and I have some handouts from a SQL classes he did that are very
> good.
>
> Hope this helps.
>
> -----Original Message-----
> From: Peter Dow [mailto:pcdow@yahoo.com]
> Sent: Wednesday, October 06, 1999 1:19 AM
> To: RPG400-L@midrange.com
> Subject: Re: LVLCHK *NO
>
>
> Thanks Alan!
>
> If I understand your sample correctly, all that RPG code was generated by
> the pre-compiler. What I don't see is the data structure definition. When
> and by whom is that defined? Does the definition contain field sizes and
> types?
>
> From what you know, how close is this to what's been discussed about
dynamic
> file linking? It sounds pretty close to me so far. And if that's so, then
I
> have to agree that for NEP's and other long-running programs, the
> "mini-compile" at run time has to be insignificant. IMHO, it's probably
> insignificant in an interactive program running on a reasonably fast RISC
> box.
>
> In other words, why not build that in to RPG instead of having to use SQL
> syntax and a pre-compiler? Maybe an SQL-function file instead of a
> Full-function file. What do you think?
>
> Peter Dow
> Dow Software Services, Inc.
> 909 425-0194 voice/fax
>
>
> ----- Original Message -----
> From: Alan Campin <Alan.Campin@CaseLogic.com>
> To: <RPG400-L@midrange.com>
> Sent: Tuesday, October 05, 1999 6:19 PM
> Subject: RE: LVLCHK *NO
>
>
> > Peter wrote:
> > >> How much does it take to process a dynamic SELECT statement?
> >
> > With SQL in an RPG program, 99% of the time you are not issuing Dynamic
> SQL.
> > The only reason you would do so is if you did not know what the SQL
> > statement is going to be at compile. The norm is to enter the SQL
> statement
> > and associate it a data structure. One element per field in the select
> > statement. The precompiler just takes what you have defined and turns it
> > into dynamic calls to a SQL program that does the I/O but the SQL is
> already
> > precompiled and the AS/400 has to only execute the access plan. (Note
that
> > the AS/400 still has to do a mini-compile to get the location of the
data
> in
> > the table each time the program runs.) When the precompiler finishes,
you
> > have regular RPG and the RPG compiler is called. All languages where SQL
> is
> > not built in work this way. Here is what it looks like after compile.
Note
> > that the SQL statement is just comments.
> >
> >   *-----------------------------------------------------------------
> >   * Declare SQL to read IVCTRL records.
> >  c*exec sql
> >  c*  declare c1 cursor for
> >  c* select icinvn,ictypp,icinyy,icorno,icdupf,icpape
> >  c*  from ivctrl
> >  c*  where icreq# = :x0req# and
> >  c*        ictypp = :x0typp and icedii = :x0edii
> >  c*  order by icinvn for update of icreq#
> >  c*end-exec
> >   *-----------------------------------------------------------------
> >   * Open SQL cursor.
> >  c*exec sql
> >  c* open c1
> >  c*end-exec
> >
> >  C                   EVAL      SQL_00004  = X0REQ#
> >  C                   EVAL      SQL_00005  = X0TYPP
> >  C                   EVAL      SQL_00006  = X0EDII
> >  C                   Z-ADD     -4            SQLER6
> >  C     SQL_00002     IFEQ      0
> >  C     SQL_00003     ORNE      *LOVAL
> >  C                   CALL      'QSQROUTE'
> >  C                   PARM                    SQLCA
> >  C                   PARM                    SQL_00000
> >  C                   ELSE
> >  C                   CALL      'QSQLOPEN'
> >  C                   PARM                    SQLCA
> >  C                   PARM                    SQL_00000
> >  C                   END
> >
> >
> >  c                   dou       sqlcod <> 0
> >  c*exec sql
> >  c*  fetch c1 into :zsin                               <<< Note the data
> > structure name.
> >  c*end-exec
> >  C                   Z-ADD     -4            SQLER6
> >  C                   CALL      'QSQROUTE'
> >  C                   PARM                    SQLCA
> >  C                   PARM                    SQL_00007
> >  C     SQL_00010     IFEQ      '1'
> >  C                   EVAL      ICINVN = SQL_00011
> >  C                   EVAL      ICTYPP = SQL_00012
> >  C                   EVAL      ICINYY = SQL_00013
> >  C                   EVAL      ICORNO = SQL_00014
> >  C                   EVAL      ICDUPF = SQL_00015
> >  C                   EVAL      ICPAPE = SQL_00016
> >  C                   END
> >
> > We use SQL for almost all of our new batch or server programs because of
> the
> > greater efficiency. There is a cost but especially in a never ending
batch
> > program, it becomes unimportant and, of course, we never have to touch
the
> > program if we maintain the table.
> >
> > Peter wrote:
> >
> > >> ??If RPG were modified to dynamically link files, would
> > >> the definition happen at compile-time or run-time?
> >
> > The compile time processing would still occur as normal. The difference
is
> > that at run-time, the AS/400 goes out and looks up the file format, and
> > determines what the starting positions are for each variable you are
> reading
> > in. In effect, a mini-compile.
> >
> > A dynamic SQL is very expensive so you only use them in cases where you
> have
> > to.
> >
> > Hope this is helpful.
> >
> > -----Original Message-----
> > From: Peter Dow [mailto:pcdow@yahoo.com]
> > Sent: Tuesday, October 05, 1999 3:44 PM
> > To: RPG400-L@midrange.com
> > Subject: Re: LVLCHK *NO
> >
> >
> > > Okay, so the gist of this is, it would take too much time and
processor
> > power
> > > to dynamically link the files, and you think we want the extra check
at
> > run time.
> >
> > > The main advantage to static linking is the system can open the
programs
> > faster,
> > > and the program won't run if it fails the level check.  Although I
think
> > that the way
> > > RPG shotguns memory locations for files may come into play also.
> > >
> > > Has anyone ever did a test on the added time and processor needed to
be
> > able
> > > to link database files dynamically on the AS/400?
> >
> > I've seen several postings in this list nudging us towards using SQL in
> our
> > RPG programs, and none of them complain about time and processing power.
> How
> > much does it take to process a dynamic SELECT statement? Also, how many
of
> > us use some file utility such as DBU, VIEW or even the lowly UPDDTA? All
> of
> > these obtain the field list for the file and format it for us, and while
> it
> > may take a second or two longer, I for one am not complaining. And how
> much
> > time are we talking about anyway? Minutes? Seconds? Subseconds? If we're
> > talking about a program that prints a report from thousands or millions
of
> > records, what is that percentage-wise?
> >
> > OTOH, there's the consideration of what this would do to RPG. I haven't
> used
> > SQL with RPG yet, so I'm not familiar with how variables are defined,
but
> > since the manual talks about a pre-compiler it's probably done from the
> > external definition. If RPG were modified to dynamically link files,
would
> > the definition happen at compile-time or run-time? If it happens at
> > run-time, things could get really interesting (does EVAL A = B + C mean
> add
> > two numbers or concatenate two strings? would this be determined at
> > run-time?)  If it happens at compile-time, that means some more checking
> at
> > file open time to determine if the fields not only have the same name,
but
> > the same type and precision.
> >
> > Maybe some of you people that use SQL with RPG could give us some hints
on
> > what it would be like to have dynamically linked files built into RPG.
> >
> >
> > >
> > > | This is the RPG/400 Mailing List!
> > > | To submit a new message, send your mail to RPG400-L@midrange.com.
> > > | To subscribe to this list send email to RPG400-L-SUB@midrange.com.
> > > | To unsubscribe from this list send email to
> RPG400-L-UNSUB@midrange.com.
> > > | Questions should be directed to the list owner/operator:
> > david@midrange.com
> > > +---
> >
> >
> > __________________________________________________
> > Do You Yahoo!?
> > Bid and sell for free at http://auctions.yahoo.com
> >
> > +---
> > | This is the RPG/400 Mailing List!
> > | To submit a new message, send your mail to RPG400-L@midrange.com.
> > | To subscribe to this list send email to RPG400-L-SUB@midrange.com.
> > | To unsubscribe from this list send email to
RPG400-L-UNSUB@midrange.com.
> > | Questions should be directed to the list owner/operator:
> > david@midrange.com
> > +---
> > +---
> > | This is the RPG/400 Mailing List!
> > | To submit a new message, send your mail to RPG400-L@midrange.com.
> > | To subscribe to this list send email to RPG400-L-SUB@midrange.com.
> > | To unsubscribe from this list send email to
RPG400-L-UNSUB@midrange.com.
> > | Questions should be directed to the list owner/operator:
> david@midrange.com
> > +---
>
>
> __________________________________________________
> Do You Yahoo!?
> Bid and sell for free at http://auctions.yahoo.com
>
> +---
> | This is the RPG/400 Mailing List!
> | To submit a new message, send your mail to RPG400-L@midrange.com.
> | To subscribe to this list send email to RPG400-L-SUB@midrange.com.
> | To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator:
> david@midrange.com
> +---
> +---
> | This is the RPG/400 Mailing List!
> | To submit a new message, send your mail to RPG400-L@midrange.com.
> | To subscribe to this list send email to RPG400-L-SUB@midrange.com.
> | To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
> | Questions should be directed to the list owner/operator:
david@midrange.com
> +---


__________________________________________________
Do You Yahoo!?
Bid and sell for free at http://auctions.yahoo.com

+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator:
david@midrange.com
+---
+---
| This is the RPG/400 Mailing List!
| To submit a new message, send your mail to RPG400-L@midrange.com.
| To subscribe to this list send email to RPG400-L-SUB@midrange.com.
| To unsubscribe from this list send email to RPG400-L-UNSUB@midrange.com.
| Questions should be directed to the list owner/operator: david@midrange.com
+---