|
midrange.com
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
+---
As an Amazon Associate we earn from qualifying purchases.
This mailing list archive is Copyright 1997-2024 by midrange.com and David Gibbs as a compilation work. Use of the archive is restricted to research of a business or technical nature. Any other uses are prohibited. Full details are available on our policy page. If you have questions about this, please contact [javascript protected email address].
Operating expenses for this site are earned using the Amazon Associate program and Google Adsense.
