ASC has those advantages, but there's a flip side to it too, meaning a
few advantages of the regular SQL engine on the system has over ASC. We
still use it, though. It does accommodate some things better than IBM's
SQL, presumably because of the ANSI limitations...After all, anyway,
it's oriented toward helping the super-user roll his own, although it
seems to me most uses would be in programming shops.
We bumped up against some statement-size limits. It's fair-sized for
most SQL statements, but a couple of us let rip some complex SQL
sometimes. Variable substitution hits the limits dynamically if you
don't account for it. Other things too, but it makes some things easier.
I didn't know about this SERVER(*LOCAL) option for their commands. This
will likely prove useful.
--Alan
-----Original Message-----
From: midrange-l-bounces@xxxxxxxxxxxx
[mailto:midrange-l-bounces@xxxxxxxxxxxx] On Behalf Of Elvis Budimlic
Sent: Monday, February 25, 2008 12:29 PM
To: 'Midrange Systems Technical Discussion'
Subject: RE: RPG vs SQL
Thanks for the update JK. Their answer makes perfect sense.
Elvis
Celebrating 11-Years of SQL Performance Excellence on IBM i5/OS and
OS/400 www.centerfieldtechnology.com
-----Original Message-----
Subject: RE: RPG vs SQL
The HelpSystems support desk confirmed the speculation that the (IMO)
more-intuitive syntax of their Sequel product is possible because the
Sequel statement is parsed and reformatted before being passed to the
standard i5 SQL engine. Their reply is copied below.
"SERVER(*SEQUEL) invokes our Sequel syntax giving us direct control over
field and expression length and column heading attributes, use of
derived fields in the JOIN clause and other special derivations such as
WDATA and CVTDATE that are not available in STANDARD SQL. These
features are supported through our use of the Classic Query Engine and
the Query api [qqqqry].
SERVER(*LOCAL) invokes standard SQL syntax. Views designated as
SERVER(*LOCAL)
are run using the newer Sequel Query Engine. While this syntax does not
support the special features of our special SEQUEL syntax, it gives
users all the benefits of the newer SQE"
JK
-----Original Message-----
Elvis replied:
DB2 for i5/OS sticks to ANSI SQL primarily to maintain compatibility
with
DB2 LUW
& DB2 for z/OS. They want IBM customers to be able to port SQL
applications without worrying about incompatibilities between different
IBM database platforms.
I personally would wish they don't have to be such stickler to ANSI
SQL...
so
many opportunities out there (i.e. my wish list includes making views
keyed since we already have keyed LFs).
Based on that, it looks to me that ASC programmers must have their own
parsing routines. I can't tell for sure as I am unfamiliar with Sequel's
internals.
Long
time ago I heard that they used to rely on QQQQry API for execution, but
recently someone mentioned they don't necessarily do that any longer.
As you said, best to go to the source and ask them.
Elvis
-----Original Message-----
Subject: RE: RPG vs SQL
Elvis,
Many thanks for posting that link on SQL joined updates. Our shop has
used ASC's "Sequel" product for a decade or more and I thought I was
getting fairly good at SQL - blithely assuming that the differences
between Sequel and standard SQL were trivial ones of syntax.
The article shows, however, that standard SQL requires a rather
non-intuitive syntax for joined UPDATE statements, i.e.: you can't just
wrap a "FROM TableA a INNER JOIN TableB b ON a.key_field = b.key_field"
with an UPDATE/SET and expect it to work correctly.
The article mentions that SQL Server's T-SQL extensions do allow the
JOIN to be inside the FROM but that standard SQL does not. I bring this
up because the equivalent "Sequel" statement allows a standardized
syntax and performs exactly the way you'd wish: updates TableA only when
the keys match. No worries about
SQL0811 and no unwanted nulls. See below example.
UPDATE SET((a.update_field b.update_field)) SQL('FROM TableA a, TableB b
INNER JOIN
a.key_field=b.key_field')
Question: Is it possible that iSeries SQL allows non-standard SQL
extensions or have the ASC programmers cleverly parsed and reformatted
the statement to allow their own syntax? I'm making a note for next week
to ask their tech-support about this, but thought I'd see whether anyone
on this list has already investigated this.
JK
--
This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing
list To post a message email: MIDRANGE-L@xxxxxxxxxxxx To subscribe,
unsubscribe, or change list options,
visit:
http://lists.midrange.com/mailman/listinfo/midrange-l
or email: MIDRANGE-L-request@xxxxxxxxxxxx Before posting, please take a
moment to review the archives at
http://archive.midrange.com/midrange-l.
midrange.com
