STRSQL on the AS/400
Jonathan Wilson
SQL from a Delphi app. on the AS/400...
One of the common suggestions is to use
STRDBG-Start session debug
STRSQL-Start interactive SQL
Type/run sql
Exit (F3 or F12) interactive SQL
ENDDBG
DSPJOBLOG
(Thanks to G. Bradley MacDonald for that)
In addition to the above you can also move the cursor to any error
message (usually a high intensity message on the 24th line) and
press...
F1-Show detailed help/error text
F10-Show job log
F18-Move to the bottom/last message in the job log.
After reviewing messages
F3 and or F12-Exit/go back a screen.
This is especially helpful in that the STRSQL program does not need to
be exited for each SQL command.
While viewing the list of messages you can at any time move the cursor
over a message and press F1 to view a more detailed text, F12 exits
this additional text screen.
Some additional/general keys that are useful are:
F17-Move to the top/first message in the job log.
F12-Go back a screen
F3-Exit program (depending on how the programs are written F3 and F12
may have the same effect)
F4-prompt (similar to the (...) buttons in delphi)
F5-Refresh/update/reset the current screen
F7/F8,Page up/Page down,Roll down/Roll up
F9-recall previous (usually commands)
F9 is a useful beast, using the example above after the ENDDBG command
press F9, you should now see the ENDDBG re-displayed. Press F9 and you
should now see the STRSQL shown, press F9....... To restart from the
last entered command blank out the command line and press F9, the
ENDDBG will be re-shown.
F4 is also very useful, All commands on the AS/400 have their own in
built prompt processor.... For example type STRDBG at a command line
and press F4, the system will show the enterable parameters. On each
"parm" you can press F4 and a list of choices will be shown. You can
also use the F1 to bring up location sensitive help text at any time.
F1-Help, if a program allows it then F1 brings up a help screen, or
when the cursor is over a message it will show, where available,
second level (more detailed) help text.
One thing I have found difficult for people to grasp is the AS/400
screens have defined input fields.... you can place the cursor into a
non-enterable location by using the arrow keys. Typing text then
causes an error, the best way to place the cursor into a typeable area
is to use the "tab" key.
..................................................................................................................................
Where is my STRSQL?
The AS/400 has an in built DB, and SQL data base processor as part of
the base product (think of this as a clean NT server running SQL7 with
no enterprise manager or ISQL) to use the DB the developers use source
code to create "files" and usually access this data through RPG
programs.
Using ODBC drivers from a PC you can access the DB2/400 server in the
same manner as SQL7 from a remote machine running ISQL.
Locally (on the AS/400) however you must have an additional package
"interactive SQL" installed to be able to use the STRSQL command....
this costs :-( This package must also be installed to create native
(RPG) programs using pre-compiled SQL.
However for those who wish to use SQL (a limited version) you can
create source file members containing the required SQL and using (If I
remember correctly) CRTQMQRY, and STRQMQRY (it might be RUNQMQRY) you
can actually get around the no STRSQL problem..... You will however
need to see the AS/400 bods for more information, If they don't know
then suggest they get News/400 magazine from Duke press.. There are
restrictions using this method in that parameters cannot be used, and
the SQL is only sintax checked when using the STRQMQRY.
Ask the AS/400 guys about "books on line", and there is an IBM web
site for the AS/400 manuals.... sorry I don't have the location to
hand.
I won't have AS/400 access for about 4 weeks (nice long holiday) but
if anyone has any questions I'll try to help from an AS/400 position.
Jon
Jonathan Wilson, Snr AP AS400
Someday England will be Internet complient,
Until then the phone bill goes up
Please CC by email to 10157...@compuserve.com
G. Bradley MacDonald
Good Info Post!
A couple of items.
The last time I looked (over a year ago) the base SQL system was free with
OS/400. This allows you to run DML SQL Statements (select, Insert, etc) -
but you had to purchase the second half of the SQL System that allows for
package generation and DDL SQL Statments (Create, Alter, etc). I could be
out of date on this - but I am not sure. Anyone out there familiar with
IBM's latest billing practices on this one??
F4 - if you are in the STRSQL command - you can enter the first part of the
command and hit F4. The AS/400 will then run you through a series of
prompts to help you build the SQL Statement. Very useful!!
I think a lot of beginning Delphi - AS/400 developers will find your post
very useful.
--
G. Bradley MacDonald
Delphi & AS/400 Consultant
Jonathan Wilson
<Bradley_...@telus.net> wrote:
>Hello Jonathan,
>
>Good Info Post!
>
>A couple of items.
>
>The last time I looked (over a year ago) the base SQL system was free with
>OS/400. This allows you to run DML SQL Statements (select, Insert, etc) -
>but you had to purchase the second half of the SQL System that allows for
>package generation and DDL SQL Statments (Create, Alter, etc). I could be
>out of date on this - but I am not sure. Anyone out there familiar with
>IBM's latest billing practices on this one??
I always thought (although I may be wrong) that the base SQL was only
the ability to create SQL via QMQRY and "QUERY" (STRQRY) although QRY
does not show the SQL but is a "report" program.
SQL is implemented in the BASE product, and can be accessed, but the
interactive SQL and the ability to package SQL into other languages
(RPG/COBOL/C/C++/ILExxx) is an add on package.
I know from experience that creating SQL RPG and then finding that you
cannot make on site changes because the client does not have SQL can
be a real &*&"£&@@ .
IBM may have changed the policy with V4.Rx or V3.Rx but at V2 it was
an add on. I believe the type of as/400 purchased can make a
difference as the "server" version is geared up to be a DB server, and
even steals CPU cycles if an interactive signon runs with too much CPU
in relation to the batch (SQL/remote/DB serving) jobs.
>
>F4 - if you are in the STRSQL command - you can enter the first part of the
>command and hit F4. The AS/400 will then run you through a series of
>prompts to help you build the SQL Statement. Very useful!!
:-) saved my skin loads of times as my knowledge of SQL was lacking.
The online help for SQL can be a help if the system has the manuals
installed onto the AS/400.
>
>I think a lot of beginning Delphi - AS/400 developers will find your post
>very useful.
I hope so.... I think the match between the AS/400 reliability (I've
only ever heard of 1 disk failing in over 13 years and the only
un-timed down time I have known was through running at 98% and having
successive re-boots due to storage running out.) And delphis ease of
development (even just SQL/ODBC) makes for very easy GUI application
development, and far more robust than VB, and far easier that C++.
It's just a shame IBM fail to sell the AS/400. The TCO is so much
lower than "PC" servers. Far easier to use than UNIX.
The funny thing is the AS/400 has been 64bits for years, the PC is
only just getting there. The AS/400 has always used independent
processors for IO (both disk, display, and just about every other
hardware subsystem), Intel is only now releasing the I2O.
Oh well.
Frank M. Villafane
You are correct in surmising that the interactive Query Manager sold by
IBM does create QMQRY and QMFORM objects. However, you do not need their
product to do this. If you create a QQMQRYSRC source file(length 91: 12 + 79),
and QQMFORMSRC source file(length 162: 12 + 150), you can create your own SQL
queries and Encoded Report Forms(the latter is rather difficult...read the
manual). I have been doing this since V2R2, without any problems. If you, and
anyone else reading this, are(is) interested, I could post some of my gp tools
for manipulating QMQRY/QMFORM objects which have been in the public domain since
approximately 1990. Incidentally, the same Query Engine used by Interactive
SQL, is used by QMQRY/QMFORM, ditto ODBC, ditto DRDA SQL, ad nauseum. SQL is
SQL, and is very powerful. Let me know about the tools...
Frank