วันพฤหัสบดีที่ 13 มีนาคม พ.ศ. 2551

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.

วันอังคารที่ 11 มีนาคม พ.ศ. 2551

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

AS400 TCP/IP on "V4R4"

AS400 TCP/IP

Now the problem to pass secret from your AS400 to a partner across internet without buying extra security Hardware will be solve with new IBM AS400 “V4R4”
With V4R4, it will add and change many function to support AS400 TCP/IP, such as
- Layer 2 tunneling protocol (L2TP) : for establishing point to point virtual link across internet.
- Secure Socket Layer (SSL) : for establishing encrypted interactive terminal session.
- IP SEC. (IP Security) : for support for public key encryption of L2TP link
- Internet Security Association Key Management protocol (ISAKMP) : for exchanging encryption keys safety across in-secure connection.
- TCP/IP Architecture change to enhance overall performance
- Improve SMTP (Simple Mail transfer Protocol), FTP and telnet more support and security.
Now I will explain in more detail about VPN.

VPN (Virtual Private Network)
VPN describes the use of Public, Insecure network to carry traffic that would be trusted only to private physical network.
VPN consist of virtual connection (Tunnels), between nodes in network. The tunnel carry data in encrypted form, so if has anyone intercepting data, it can’t read or modify data stream at all.
In previous version of IBM AS400 “V4R3” it’s support VPN component in a firewall-to-firewall connection using IPCS which require tunnel terminate at IPCS (at firewall). There are many disadvantage of this technology such as expensive, hard to maintain and inflexible.
So, in NEW IBM AS400 “V4R4” will change to use VPN connection in a “Host-to-Host” connection that let you create VPN tunnel directly between any AS400, so your data will completely protect from location to location.

CPYTOPCD command

Copy file from AS/400 to PC you can use the CPYTOPCD command but this command will appends carriage-returen (CR) and line-feed (LF) characters to end of each record. If you don't want to (CR) and (LF) you can use CPYTOSTMF command

CPYTOSTMF
FROMMBR('qsys.lib/library.lib/file.file/member.mbr') +
TOSTMF('/directory/destfilename')' +
STMFOPT(*replace) +
ENDLINFMT(*CRLF)

Modifying IBM edit codes

You can modify edit codes 5 through 9 for your own purposes although IBM predefines them
1. Type GO CMDEDTD at an AS/400 command line to display a list of the available edit description commands.
2. you must delete it with the DLTEDTD command and create a new one with the CRTEDTD command