Re: A quick question on spool files -- MIDRANGE-L

Paudie O'Riordan wrote
  
>  Is it possible to change spool files into Database members/files for query
>  purposes and how do you do it?

Something you need to be alert to is the frequency with which a report might 
be altered ... if you have a program written by person-A creating report used 
by person-B with this conversion deal getting spool file into data base 
format developed by person-C for use in query to be used by person-D, then 
the original program gets modified, this could mess up the query, unless your 
programming standards demand that the original program include a warning 
about how the end code is to be used.

I see a problem with the data formats ... you will have numeric fields with 
embedded commas, floating dollar signs etc. & sometimes we substitute "none" 
for zero & have other logic that puts different things in same column ... 
there is a conversion issue of getting that data back into a form that is 
easy to manipulate.

I would think it would be simpler to tackle the original program & seek to 
have the data go out in data base rather than printed form, unless you had a 
generic program that could accept any spooled report & get it into data base 
form, like the software that takes AS/400 reports & migrates them to the PC 
application of choice ... spread sheet or whatever ... that kind of software 
is generic in that it can handle any report layout.

We have some programming standards which mean that all programs have a 
certain look & feel that would make this somewhat manageable.

If the data originated in a query/400, you have an option there to send 
output to a new file instead of to a report.

If the spool file originated from an IBM dump of some kind, the original 
output can often be changed from *PRINT to *OUTFILE which comes in database 
form.

Once you have the data base file ... if it was created through one of these 
automated processes in which you do not have to futz with DDS or SQL to 
define the external specifications, DSPFFD will get you a layout of what the 
fields are ... I like to print one of these, then use RUNQRY *N the-file-name 
F4 *YES selection but not actually first time thru, look at data, then F12 & 
experiment with different selections, making notations on my printed DSPFFD 
reference regarding typical contents in the various fields & what seems to be 
most useful to us.

API Get Spooled File QSPGETF and 
API Put Spooled File QSPPUTF were discusssed with
Copy Spooled File command CPYSPLF
in a recent e-newsletter that I received --- attached below via FWD --- 
hopefully this will address some of your interest & inspire you to subscribe 
to this thing.

Al Macintyre  ���
MIS Manager Green Screen Programmer & Computer Janitor of BPCS 405 CD Rel-02 
running on AS/400 V4R3 http://www.cen-elec.com Central Industries of 
Indiana--->Quality manufacturer of wire harnesses and electrical 
sub-assemblies

Subject: Club Tech Programming Tips - 08.02.00
From: "ClubTechPrgrmTips" <ClubTechPrgrmTips@xxxxxxxxxxxxxxxxxxxxx>
Date: Wed, 2 Aug 2000 14:13:06 -0600

*************** Club Tech Programming Tips Newsletter ***************
An AS400 Network Publication http://www.as400network.com
Home of NEWS/400 Magazine
Issue 24 August 2, 2000

Sponsored by LANSA for the Web
EXTEND YOUR SYNON APPLICATIONS TO THE WEB WITH LANSA
*****************************************************************
Long-time Synon shops like John Wiley & Sons have already used
LANSA to quickly & successfully drive their Synon e-business
deployment strategy. If you want to extend YOUR Synon application
to the Web, contact us now! http://www.lansa.com/synon2lansa.htm

******************************************************************

THIS WEEK:
> QSPGETF and QSPPUTF APIs and Commands
> Passing Subfile Records to a Called Program
> SEU Tip: Hiding Lines While Editing
> Convert String to Number Using C Functions, Part II
> The Essential RPG IV Style Guide

Featured Tip:

QSPGETF AND QSPPUTF APIs AND COMMANDS
IBM Knowledgebase item 8011926 describes two APIs that are
undocumented in the AS/400 manuals. The two APIs, Get Spooled File
(QSPGETF) and Put Spooled File (QSPPUTF), copy spool files to and
restore them from physical files, respectively. The advantage of
QSPGETF and QSPPUTF over the well-documented Copy Spooled File command
(CPYSPLF) is that the APIs are able to copy Advanced Function Printing
Data Stream (AFPDS) and Intelligent Printer Data Stream (IPDS) spool
files, whereas CPYSPLF cannot.

You can call the QSPGETF and QSPPUTF APIs directly, or you can call
them using command wrappers over the APIs.

a. Calling the APIs Directly
The QSPGETF API call below saves spool file QPRINT to database file
SPOOLDB in USER1LIB library, member MBR1, where the spool file number
is 1 and the spool file job is 010160/user1/dsp03:

CALL PGM(QSYS/QSPGETF) PARM('QPRINT ' 'SPOOLDB USER1LIB ' +
'DSP03 USER1 010160' X'0001' 'MBR1 ')

The QSPPUTF API call below creates a spool file in USER1 output queue
in QGPL library from the MBR1 member in the SPOOLDB database file:

CALL PGM(QSYS/QSPPUTF) PARM('SPOOLDB USER1LIB ' 'USER1 QGPL ' 'MBR1 ')

b. Calling the APIs from command wrappers
The two calls below correspond to the examples above, but these
examples use the command calls instead of calls directly to the APIs:

GETSPLF FILE(QPRINT) TOFILE(USER1LIB/SPOOLDB) +
JOB(010160/USER1/DSP03) SPLNBR(1) TOMBR(MBR1)

PUTSPLF FROMFILE(USER1LIB/SPOOLDB) OUTQ(QGPL/USER1) +
FROMMBR(MBR1)

Although the QSPGETF and QSPPUTF APIs are included in every OS/400
release since V3R2, the commands are not included. Fortunately, you
can obtain the compiled commands and the command source code from the
Knowledgebase item, which can be found at

http://as400service.ibm.com/8625680A007CA5C6/0/10AFE2F98C7F5FB9862565C2007D46A8?Open&Highlight=2,8011926
.

This item was adapted from IBM Knowledgebase item 8011926

Short Takes:

1. PASSING SUBFILE RECORDS TO A CALLED PROGRAM
Q. Is there any way to pass all the records in a subfile to a called
program?

A. You can make subfile records available in a called program by
defining the same display file in both the calling and called programs
and sharing the open data path (ODP).

For example, suppose PgmA, which uses display file DspA, calls PgmB.
To make DspA's subfile records available to PgmB, define file DspA in
PgmB. Then issue the command OVRDSPF FILE(DspA) SHARE(*YES) before
calling PgmA. When PgmA calls PgmB, the ODP is shared and PgmB can
read records from DspA. You don't need to write any of DspA's record
formats from PgmB. However, for PgmB to compile, you need to reference
at least one of them. Simply write an EXFMT statement and condition it
so that it will never be executed.

From a Tech Corner answer by Gary Guthrie

2. SEU TIP: HIDING LINES WHILE EDITING
When you are working with source members that have many source lines,
it is useful to exclude certain lines from the displayed member,
allowing you to see lines from different areas of the source on the
screen at the same time. Place X (Exclude) on any line you want to
exclude from the display. This doesn't delete the line from the source
member; it simply causes the excluded line(s) to be temporarily
removed from the SEU display.

You can also use the X command in block format (XX). The example below
shows how to exclude lines 3 through 6 (I entered a blank after the XX
command to make it more readable):

*************** Beginning of data **************************
0001.00 111111111111111
0002.00 222222222222222
XX 3.00 333333333333333
0004.00 444444444444444
0005.00 555555555555555
XX 6.00 666666666666666
0007.00 777777777777777
****************** End of data *****************************

The result below shows that lines 3 through 6 have been hidden from
view:

*************** Beginning of data **************************
0001.00 111111111111111
0002.00 222222222222222
- - - - ------------- 4 data records excluded ----------------------
0007.00 777777777777777
****************** End of data *****************************

To redisplay the lines, press F5 (Refresh) key, or use the SF or SL
commands. SF (Show first) command shows the first excluded line, and
SL (Show last) shows the last excluded line. You can also use SFn or
SLn to show the first n lines or the last n lines of the excluded
group of lines. The example below shows how to use the SF2 command to
show the first two excluded lines:

*************** Beginning of data **************************
0001.00 111111111111111
0002.00 222222222222222
SF2 - - ------------- 4 data records excluded ----------------------
0007.00 777777777777777
****************** End of data *****************************

Notice below that the display shows the first two excluded lines and
the text has changed to "2 data records excluded":

*************** Beginning of data **************************
0001.00 111111111111111
0002.00 222222222222222
0003.00 333333333333333
0004.00 444444444444444
- - - - ------------- 2 data records excluded ----------------------
0007.00 777777777777777
****************** End of data *****************************

One useful trick is to exclude all lines in an RPG source file in SEU
by entering XX in the first and last lines of the member, then
entering

F BEGSR

on the command line. This will exclude all lines except those
containing "BEGSR". This is a great way to easily see all the
subroutines in your program.

Adapted from "Desktop Guide to AS/400 Programmers' Tools"
http://www.29thstreetpress.com

3. CONVERT STRING TO NUMBER USING C FUNCTIONS, PART II
In the July 14 Club Tech Programming Tips newsletter, I described how
to convert a string to a number using C functions atoi (ASCII to
Integer) and atof (ASCII to Floating point). Here is some follow-up
information on those functions:

a. Several readers wrote asking why these functions weren't found on
their system. The C functions are included in the "Common Programming
APIs Toolkit," which comes free with OS/400. If it isn't on your
system, install it from the OS/400 CD-ROM or tape.

b. In the July 14 item, I stated that the atoi function handles only
digits. It also allows + and - signs. Oops.

c. IBM's Barbara Morris provided code that vastly improves the
floating point number conversion with atof. The newer code also
handles more input formats, including embedded commas and blanks, ()
and CR for negative values, leading or following +/- signs, as well as
optionally allowing comma for the decimal point and period for the
thousands separator. You can download her newer code from

http://www.as400network.com/noderesources/code/clubtechcode/atof.txt .

d. You can read more about the atoi and atof functions in the "ILE C
for AS/400 Run-Time Library Reference" manual at
http://publib.boulder.ibm.com/cgibin/bookmgr/bookmgr.cmd/BOOKS/QB3AT500/CCONTENTS
.

4. The Essential RPG IV Style Guide
What's the best field-naming scheme in RPG? Should I use conditioning
indicators? Where's the best place to declare variables? These and
many other RPG programming style questions are answered in the
definitive RPG IV Style Guide, which was created by Bryan Meyers with
the help of many participants in the RPG Forum (under the RPG Style
topic) at http://www.as400network.com/Forums/Index.cfm?cfapp=54 . You
can read the RPG IV Style Guide at

http://www.as400network.com/resources/artarchive/index.cfm?fuseaction=viewarticle&CO_ContentID=2461
.

************************** ADVERTISING **************************:
MASTER THE BASICS OF JAVA WITH A NEWS/400 1-DAY SEMINAR!
Do you find yourself faced with Java and don't know where to turn? Let
NEWS/400's one-day seminar, Java on the AS/400: Where to Begin, be
your solution. Sessions include: Java Overview; Programming Techniques
& Examples; The Screen Design Aid Replacement; and Graphical User
Interfaces & Programming Examples. Register to attend and get started
on the "right" foot with Java! For information, go to:
http://www.as400network.com/str/seminars/bldSem.cfm?StoreID=340

********** ABOUT CLUB TECH PROGRAMMING TIPS NEWSLETTER **********:

Club Tech Programming Tips Newsletter is published every other
Wednesday. We publish Club Tech Systems Management Newsletter on
alternate Wednesdays. Also from NEWS/400, NEWSWire/400 is published
Tuesdays and Thursdays and covers the latest AS/400 industry news.
NEWSWire/400 Product Extra is published Mondays and contains the
latest new product announcements. All are *FREE OF CHARGE*!

FOR NEW SUBSCRIPTIONS, you can subscribe by joining the AS400 Network
with a handy Web form at http://www.as400network.com/join/ .

TO UNSUBSCRIBE or CHANGE E-MAIL ADDRESSES, go to
http://www.as400network.com/info/profile/profilelogin.cfm . If you
don't have Web access, you can also unsubscribe to this newsletter
(without unsubscribing to NEWSWire/400) by replying to this message
with UNSUBSCRIBE in the Subject line.

You are subscribed as: macwheel99@aol.com

IF YOU HAVE a technical question, please submit it to
clubtech@as400network.com or post it in the appropriate
as400network.com forum. If you have a response to a tip in this
newsletter, please e-mail clubtech@as400network.com .

IF YOU WANT TO SPONSOR a Club Tech Programming Tips Newsletter, please
contact your AS400 Network sales manager. Click here for details:
http://www.as400network.com/info/mediakit/Sales/Index.htm .
___________________________
Copyright 2000, NEWS/400
http://www.as400network.com