Learn all there is to know about SQL on the AS/400
Cole
I am looking for some good sites to learn about programming SQL on
the AS/400. I have the pdf versions of the manuals but what I'm
looking for are examples of good SQL code and how people are using it.
One thing I would like to be able to do is to convert queries that
are created with the Query tool to queries that can be run in Query
Manager. Also I have a bunch of queries that we use both types of
queries to come up with a final file and I'd like to be able to
combine them into one clean process using just the Query Manager.
Thanks,
Cole
Rikkie
Goto http://users.skynet.be/vecelzo and select Query.
"Cole" <cole...@yahoo.com> wrote in message
news:38a074e8.02101...@posting.google.com...
Sam L.
using RTVQMQRY? You need to prompt it and specify
ALWQRYDFN(*YES). (I think that is the right keyword.)
You'll get source that can be "compiled" with the CRTQMQRY
command and the resultant object run with the STRQMQRY command.
Be aware, however, that it doesn't do an adequate job of type 2
or type 3 queries. You'll need to tweak these your self using
the "from file1 left join file 2 on F1key1=F2key2 and
F1Key2=F2Key2" kind of syntax.
I don' care a lot for how it formats the statement either, but
you can always clean it up in an editor.
Sam
"Cole" <cole...@yahoo.com> wrote in message
news:38a074e8.02101...@posting.google.com...
Drew Dekreon
If not, I'd recommend trying out microsoft access or msqry32.exe (part
of excel - called in when you do New Database Query).
You can prompt through making various types of queries (select, update,
delete), then switch to SQL mode & view the actual statement -- very
useful for learning this stuff!
as for integration to RPG, I'd recommend Cozzi's book Modern RPG. Great
appendix that lays out how to make the jump from pure rpg to sqlrpg.
Cole
the stuff with the AS/400. There are a lot of different tools
available but tough to know the right questions to ask to get done
what I need to do. I need to work native on the 400 for speed. The
tables that I'm accessing have millions of records and to larger to
work with through ODBC.
Cole
Drew Dekreon <drew_d...@chugachelectric.com.x.com> wrote in message news:<uqs2vdt...@corp.supernews.com>...
Cole
other bits of information? Is it possible to combine multiple SQL
queries into one job stream without using CL? For example let's say I
need to create a summary of costs of inventory items and then link
that summary file to another table to get additional information and
then output that file to build an excel spreadsheet.
Thanks,
Cole
"Sam L." <none...@nonesuch.com> wrote in message news:<Senr9.201$g64....@eagle.america.net>...
Phil
interactively from the qcmd screen provided that you need not pass
parameters to the statements included in the source file member ?
You can even use multiple sql statements in the *same* member provided that
you separate each one with a semicolon (;)
HTH
Phil
"Cole" <cole...@yahoo.com> a écrit dans le message de news:
38a074e8.02101...@posting.google.com...
Kent Milligan
is going to perform the same on large tables as "native" AS/400 SQL.
--
Kent Milligan, DB2 & BI team
PartnerWorld for Developers, iSeries
km...@us.removethis.ibm.com GO HAWKEYES!!
>>> www.iseries.ibm.com/db2
(opinions stated are not necessarily those of my employer)
Sam L.
kind of stuck with it.
Some one has pointed you to RUNSQLSTM to run a series of SQL
statements, each terminated with a ";", from a source file. Be
aware that all you can do by way of output is "INSERT INTO myFile
(SELECT SUM(AnyFile) FROM AnyFile)" type of stuff, though this
isn't a huge restriction, and you can't pass parameters. With
*QMQRY you can do "SELECT SUM(AnyFile) FROM AnyFile" and create a
new file on the fly, though I believe the output data is
processed twice.
If you go with "compile" *QMQRY type objects you can pass in
parameters, but you are restricted to single SQL statements, thus
you need to string them together with CL.
SQL facilities on the AS/400:
STRSQL (interactive SQL.)
STRQM (interactive building of *QMQRY objects.)
Embedded SQL in RPG or Cobol.
SQL stored procedures written in SQL procedural language. (Used
to require a C compiler, but as of V5R1 or V5R2 the compiler is
no longer necessary.)
The above need the SQL Development Kit, an extra cost option.
RUNSQLSTM (runs one or more SQL statements from a source file.)
CRTQMQRY (build *QMQRY objects from source)
STRQMQTY (run *QMQRY objects.)
Sam
"Cole" <cole...@yahoo.com> wrote in message
news:38a074e8.02101...@posting.google.com...
Karl Hanson
A few more:
-- iSeries Access (aka Client Access) has a Run SQL Scripts facility
-- CLI API is part of OS/400, which is like ODBC
-- JDBC
--
Karl Hanson
Sam L.
"Karl Hanson" <kcha...@youess.ibm.com> wrote in message
news:3DAF6A48...@youess.ibm.com...
> A few more:
> -- iSeries Access (aka Client Access) has a Run SQL Scripts
facility
I presume you mean in iSeries Navigator, formerly Operations
Navigator, which is an optional installed part of iSeries Access.
(Or am I confused about the names? I just love trying to keep up
with IBM's product names.)
Peter Gulutzan
> Yes, I'm familar with SQL. It's just difficult wading through all of
> the stuff with the AS/400. There are a lot of different tools
> available but tough to know the right questions to ask to get done
> what I need to do. I need to work native on the 400 for speed. The
> tables that I'm accessing have millions of records and to larger to
> work with through ODBC.
>
> Cole
>
(like "SQL/400 By Example" and "SQL/400: A professional programmer's
guide") are getting a bit obsolete now, but Conte + Cravitz's "SQL/400
Developer's Guide (Conte and Cravitz)" 2nd edition is from 2000 and
has 4.5 stars on amazon. There's also "iSeries and AS/400 SQL at Work"
and "Database Design and Programming for DB2/400".
Peter Gulutzan
www.ocelot.ca
Co-Author of SQL Performance Tuning (http://www.ocelot.ca/tuning.htm)