Printing Overlays

On the taebo invoice, I used the following steps
-Setup up IBM AFP600 printer in windows
-Click Start, settings, printer
-Double click add printer
-Click local printer
-Click IBM
-Click AFP 600 (600 dpi)
-Select print to file
-Create overlay with Microsoft Word
-Start Microsoft Word
-Allows you to design the form.
-Control A to select all
-Control C to copy
-Paint brush
-Click start, programs, accessories, paint
-In paint, control V to paste
-Click Yes "the image in the clipboard is larger than the bitmap."
-Print to AFP600 printer file
-Click File, Page Setup
-Click landscape, set the margins left, top, right, bottom to 0.25
-Click ok
-Click file, print
-Click printer file name, and select IBM AFP 600
-Click properties
-Choose 300 or 600 dpi
-Click overlay
-Click ok - properties
-Click ok – to print
-Select the folder "C:My Documents"
-Type in the file name "Overlay.prn"
-Click OK
-Create an AS400 physical file, once
-CRTPF FILE(EASTAFP/OVERLAY) RCDLEN(32766) TEXT(OVERLAY)
-Make sure that Overlay.prn is not larger than 32K
-Copy printer file to AS400 QDLS
-Start As400 operational navigator
-Click on 38.195.141.4 computer
-Click on File systems
-If file systems does not appear, install this client access component.
-Click on QDLS
-Click on EASTON
-Start windows explorer
-Right click start, click explorer
-Click on My documents
-Drag Overlay to QDLS (copy)
-Menus AFP
-Menu option 3, Copy qdls file to AS400 physical file
-CPYFRMPCD FROMFLR(EASTON) TOFILE(eastafp/OVERLAY) FROMDOC(OVERLAY.PRN) TRNTBL(*NONE) TRNFMT(*NOTEXT)
-Menu option 4, Delete existing overlay
-dltovl eastafp/taeboovl
-Menu option 5, Compile Overlay
-CRTOVL OVL(EASTAFP/cmasOVL) FILE(EASTAFP/OVERLAY) MBR(OVERLAY) TEXT(OVERLAY)
-Compile print file devtype(*apf)
-CRTPRTF FILE(EASTAFP/cmasOVLP) SRCFILE(EASTAFP/QSRC) SRCMBR(cmasOVLP) DEVTYPE(*AFPDS) REPLACE(*YES)
-Menu option 6, Test Overlay
-Call taeboovl
-You may want to modify TAEBOOVLP, adjusting the starting position.
-Note, the overlay was printed landscape (rotated) and the text is rotated too
-Menu option 7, Print invoice
Call AL267BC
Note: change the overlay(xxx 0 0) won’t adjust down, it does adjust across
Notes, problems
-AFP600 support 300 & 600 DPI. 600 dpi takes 30 second, 300 dpi takes 10 seconds to transmit and a print 1 page overlay.
-If you print to AFP600 printer using word, the overlay will not come out correct.
-Print settings in Word (portrate/lanscape) doesn’t effect the final outcome.
-Changing the margins in Paint brush does effect final overlay, but it is better to adjust to make left & right adjustments in the DDS overlay(xxx 1.0 1.0)
-Make sure the overlay is not greater than 32K
-See manual AS/400 Guide to AFP and PSF
-Chapter 11.2.1-1 for DDS keywords
-Chapter 9.2.3-1 for creating overlays
-Example software may be downloaded from
-http://www.printers.ibm.com/R5PSC.NSF/Web/4sample
-http://www.printers.ibm.com/SEARCH.NSF/search?openagent&Query=sampler
-The Overlay command doesn’t work with client access express
-See manual Printer device programming , apendix 1.4.5.12.1 for fonts. Run program ipds test to see fonts printed. And see library QSAMPLER.

Converting character fields to numeric fields with SQL

CONVERTING CHARACTER FIELDS TO NUMERIC FIELDS WITH SQL
One question that arises frequently is how to convert a character field to a
numeric field without use the Move op-code. In past issues we've discussed APIs
that perform the conversion. You might be interested in knowing that SQL's Cast
statement lets you change a field's data type. With embedded SQL, you can
therefore convert a character field value to a numeric field. The following code
excerpt initializes character field CharFld to the value '123.45' and uses Cast
to convert it to numeric field NbrFld.

******************************************************************
Sample code demonstrating SQL Cast to convert character field
to numeric field
******************************************************************

D CharFld S 6 Inz( '123.45' )
D NbrFld S 5S 2

C/Exec SQL
C+ Set :NbrFld = Cast( :CharFld as Dec( 5 , 2 ) )
C/End-Exec

Deleting records with duplicate data

DELETING RECORDS WITH DUPLICATE DATA -- REVISITED
If you're looking for a way to delete records with duplicate data without
writing special programs, copying files, and other such tasks, consider a simple
solution that SQL provides. Let's see how it's done.

Consider the following DDS for file MyFile:

* ======================================
* = Sample file MyFile =
* ======================================
A R MYREC
A LASTNAME 20
A FIRSTNAME 20

Now, consider that file MyFile contains the following data:

JOHNSON BILL
EDWARDS MICHAEL
MORRIS SANDRA
DAVIS TOM
MORAY ANNA
DAVIS RYAN
PAGE LEE
EDWARDS TIM

Presume you want a last name to occur only once in the file. Records for Ryan
Davis and Tim Edwards duplicate those for Tom Davis and Michael Edwards,
respectively, and should be deleted.

The following SQL statement will do just that.

Delete From MyLib/MyFile A
Where RRN(A) >
(Select Min(RRN(B)) From MyLib/MyFile B
Where A.LastName = B.LastName)

The statement deletes records from MyFile when the relative record number (RRN)
is greater than the lowest relative record number for each last name. The lowest
relative record name for a name is found using the Min (minimum) function in the
subquery.

After executing the above SQL statement, file MyFile contains the following
data:

JOHNSON BILL
EDWARDS MICHAEL
MORRIS SANDRA
DAVIS TOM
MORAY ANNA
PAGE LEE

If you would like to view the duplicate data rather than delete it (or as a
precaution before the actual delete), you can use the following SQL statement:

Select * From MyLib/MyFile A
Where RRN(A) >
(Select Min(RRN(B)) From MyLib/MyFile B
Where A.LastName = B.LastName)

===============================================================

In the last issue, we included a tip on deleting duplicate records using SQL. We
received a considerable amount of reader feedback on the item. One
correspondence from Derek Sutcliffe suggests a variant that he suggests will
improve performance, particularly on large files.

Following is Derek's suggested replacement, which lets you view the duplicates
to be deleted:

Select RRN(B) From MyLib/MyFile B, Mylib/Myfile C
Where B.LastName = C.LastName and RRN(B) > RRN(C)

His suggested replacement that performs the deletion of duplicate records is as
follows:

Delete From MyLib/MyFile A
Where RRN(A) in
(Select RRN(B) From MyLib/MyFile B, Mylib/Myfile C
Where B.LastName = C.LastName and RRN(B) > RRN(C))

PRINTING A PC FILE TO AN AS/400 PRINTER USING FTP

If you're on a PC without Client Access or NetServer installed and
need to print a text file to an AS/400 printer, you can use FTP to
transfer the file to the QSYSPRT queue. Here's a sample session that
prints myfile.txt to QSYSPRT:

C:\>ftp as400
User (as400:(none)): chuck
331 Enter password.
Password: xxxxxxx
230 CHUCK logged on.
ftp> cd qsys
250 Current library changed to QSYS.
ftp> ascii
200 Representation type is ASCII nonprint.
ftp> put myfile.txt qsysprt
local: myfile.txt remote: qsysprt
200 PORT subcommand request successful.
150 Sending file to member QSYSPRT in library QSYS.
250 File transfer completed successfully.
4240 bytes sent in 0.49 seconds (8.49 KB/s)
ftp> quit
221 QUIT subcommand received.

Adapted from an answer by Scott Klement.

14 Tips on Freeing Up Hard Disk Space Article Information

A common message in the AS/400 newsgroups and forums is a frantic plea
for help from someone whose AS/400 is in danger of running out of disk
space. The following are 14 ways you can free up disk space (in no
particular order).

1. Run the WRKF (Work with Files) command to search for all files in
library QSYS that fit generic name QHST* (system history log), and
then delete all but two or three of the most recent QHST* files.

2. Run the WRKPRB (Work with Problems) command to list all problem
logs, and then delete all unnecessary logs. You may have to change
system value QPRBHLDITV, which governs the problem log hold interval,
to a lower value so the system will let you delete recent logs.

3. Run GO CLEANUP, select option 1, and choose the appropriate cleanup
options for your installation. For more info, read
http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/QB3AGO04/4.3 .

4. Remove obsolete user profiles, which take up space on your system.
You can learn how to remove them at
http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/QB3AGO04/4.3.2 .

5. Run the RCLSTG (Reclaim Storage) command to reclaim storage wasted
by damaged objects, objects without an owner, objects not in a
library, and so on. This command can take hours to run (and it can't
be run in batch). To run RCLSTG, you sign on to the system console,
run the ENDSYS (End System) command to put the system in a restricted
state, and run RCLSTG interactively. For more information, see the tip
"Unattended Reclaim Storage" at
http://www.as400network.com/article.cfm?ID=6515 and a follow-up letter
at http://www.as400network.com/article.cfm?ID=2726 . (For AS400
Network Professional members only.)

6. Apply PTFs permanently because temporarily applied PTFs eat up
DASD. Until a PTF is applied permanently, the system has to keep two
copies of the code affected by the PTF.

7. Use the RGZPFM (Reorganize Physical File Member) command to
reorganize database files that contain many deleted records. Another
option is to specify REUSEDLT(*YES) on the CRTPF command when creating
a new physical file or on the CHGPF command when modifying an existing
physical file. You can read about the ramifications of using
REUSEDLT(*YES) in "Reusing Deleted Records" in the May 24, 2000, Club
Tech newsletter at
http://www.as400network.com/resources/artarchive/index.cfm?fuseaction=viewarticle&CO_ContentID=8236 .

8. Delete unnecessary libraries using the SBMJOB (Submit Job) command
with the DLTLIB (Delete Library) command:

SBMJOB CMD(DLTLIBxxx)

Don't use DLTLIB with IBM-supplied libraries, however, because
deleting an IBM-supplied library using DLTLIB may affect the system
configuration.

9. Run GO LICPGM to uninstall unnecessary IBM licensed programs. For
example, you may have the S/36 environment installed even if you don't
use it. There's more info at http://publib.boulder.ibm.com/cgi-bin/bookmgr/BOOKS/QB3AGO04/4.3.4 .

10. If you use OfficeVision, run the RCLDLO (Reclaim Document Library
Object) command to reclaim document storage.

11. Run the CPROBJ (Compress Object) command on objects (such as
programs or panel groups) that are rarely used but that must be on the
system in case someone needs them.

12. Delete unnecessary files in the QEZJOBLOG queue. You can view the
QEZJOBLOG files with

WRKOUTQ *ALL

13. Clear or delete old save files. To display a list of save files in
user libraries, use the command

WRKF FILE(*ALLUSR/*ALL) FILEATR(SAVF)

14. Locate the objects using the most storage and delete them from
your system. One method for determining which objects are using the
most disk space is to use the tools available in the GO DISKTASKS
menu. These tools are described in Chapter 7 of the "AS/400 Disk
Storage Topics and Tools" Redbook (SG24-5693-00) at http://publib-b.boulder.ibm.com/Redbooks.nsf/RedbookAbstracts/sg245693.html?Open .

Another way to analyze object disk usage is with the DSPOBJD command,
which can create an outfile showing object storage:

DSPOBJD OBJ(*ALLUSR/*ALL) OBJTYPE(*FILE) OUTPUT(*OUTFILE)
OUTFILE(yourlib/youroutfile)

Using the outfile created with the above command, you can create
queries such as the one below, which creates a list of files on the
system, in order from largest to smallest size:

SELECT ODLBNM, ODOBNM, ODOBSZ
FROM yourlib/youroutfile
ORDER BY ODOBSZ DESC

Use the following query to view library disk usage:

SELECT ODLBNM, SUM(ODOBSZ) AS SIZE
FROM yourlib/youroutfile
GROUP BY ODLBNM
ORDER BY SIZE DESC

Use the following query to view disk usage by users:

SELECT ODOBOW, SUM(ODOBSZ) AS SIZE
FROM yourlib/youroutfile
GROUP BY ODOBOW
ORDER BY SIZE DESC

The above tips were adapted from a NEWS/400 Tech Corner item by Ernie
Malaga and Juergen Specht and from newsgroup answers by Thad Rizzi,
Wayne Sadecki, and Michael Buus Sorensen.

Short to access to AS/400 command

If we want to access the AS/400 command line when we go on the interactive SQL session or the interactive STRISDB by without exit from interactive session, we can easily do by

-type ‘CALL QCMD’ on SQl command line and follow with AS/400 command.
-type ‘SYS’ on STRISDB command line and follow with AS/400 command.

For example, suppose we want to use command WRKSPLF. We only type ‘ SYS WRKSPLF’

Converting Character Data to Numeric in Query/400

Query/400 does not provide a native method for converting character data to numeric, but with the following hack, the conversion is possible. I have a character field (4 digits) that’s used for generic purposes in a particular database file. Although the field’s format is character, the field sometimes represents numeric data. To perform math operations on such data in Query, you must first convert the field to numeric. By using the MICROSECOND keyword in Define Result Fields section of Query, you can convert the character data to a numeric field for later use in calculation.
For example, create a field CHARFIELD4 in this example represents a four-digit character field containing the character 0 through 9.
Next, use the MICROSECOND keyword on the TIMESTAMP field to create a numeric copy of CHARFIELD4’s character date. The MICROSECOND keyword takes the six rightmost digits in the TIMESTAMP field and converts them to numeric. To the example, the character field is only four digits long, so 00 is concatenated with the character field to create a valid timestamp with six digits of millisecond.
If a given character field is larger than six digits, you’ll need to break down the above process into multiple steps and perform a math operation on the individual numeric component to combine them.
For Example,
Field Expression Column Heading Len Dec
TIMESTAMP ‘1988-12-25-17.30.00.00’ | | CHARFIELD 26
NUMERIC6 MICROSECOND(TIMESTAMP) 6 0