Shahar Mor,
President, Barmor Information Systems

Shahar has worked on the iSeries 400 for 16 years. He started as a system administrator responsible for security, performance, database administration, VoIP, backup and recovery procedures and connectivity issues. He later became a senior programmer in a bank and then head of infrastructure for an insurance company. Shahar now owns Barmor, a consulting firm in Israel, which employs over 20 people that work on projects for the iSeries 400 in the network environment. He also has written a Redbook for IBM on iSeries e-commerce and he is Search400.com site expert for connectivity issues on the iSeries.

Top Ten Questions

1. Send a PC command from an iSeries program


2. Send files to a Windows machine from a CLP program


3. Automate the process of sending files from a PC to an iSeries


4. Access data in an external database from an RPG program


5. Pull an iSeries HTML file from the Web using HTTP


6. Importing iSeries database files to Excel


7. Send data from Excel to an iSeries database


8. Get a lot of files in one FTP script on the iSeries


9. Call an RPG program from VB using ODBC


10. Print a PDF file from the iSeries

Question:
How can I send a PC command from an iSeries program?

Answer:
There are two methods you can use:

• Use STRPCCMD: This command can send a limited length string to the PC, it requires an emulation (this works from an interactive session only.)
• Use RUNRMTCMD: This command can be used from batch and interactive programs -- it is not limited in length, however it requires the remote command service from Client Access to run on the PC.
  

Return to Table of Contents

Question:
How can I send files to a Windows machine from a CLP program?

Answer:
It is possible to use QNTC. QNTC is a file system that allows iSeries programs to write data directly to a shared directory on Windows NT.

You will need the same user and password on the iSeries and the NT; you will need to define the directory required as shared on the NT side. When QNTC is configured correctly, the iSeries command CPY can copy from and to QNTC as if it is local.

Return to Table of Contents

Question:
How can I automate the process of sending files from a PC to an iSeries?

Answer:
Use any PC scheduler to launch script to FTP the required file to the iSeries.
For example:

FTP -s:myscriptfile.txt where myscriptfile.txt contains something like this:

Return to Table of Contents

Question:
Can I access data in an external database from an RPG program?

Answer:
It is possible to access external data from the iSeries using JDBC. If the access has a pure JDBC driver, then you may do the following:

• Install the driver.
• Write a small Java program to read the access file.

Return to Table of Contents

Question:
Can the iSeries pull an HTML file from the Web using HTTP?

Answer:
In order to connect to a Web site from the iSeries via HTTP you can:

• Write a small socket program that behaves like a browser.
• Write HTTP client program using Java.
• Buy an "iSeries HTTP client" such as GETURI from BVSTools (it is considered shareware).
• Use Curl from a pase environment.

Return to Table of Contents

Question:
How can I import iSeries database files to Excel?

Answer:
iSeries Access has an Excel Add-In that provides data transfer support. During iSeries Access installation process, install the following components:

Once installed, activate the iSeries Access Data Transfer feature add-in:

1. Open Excel worksheet
2. Select Add-Ins from the Tools pull-down menu.
3. Select the iSeries Access Data Transfer feature

To set up an SQL statement that returns data to the worksheet:

1. From the pull-down menu "Data", select "Transfer Data from iSeries"
2. From Transfer Request dialog window, select "Create New"
3. Specify the iSeries system name
4. On next dialog window, specify the library/file name
5. On next dialog window, press the "Properties" button
6. Select the "SQL" tab
7. Select the option "Process Select as native SQL" and Press OK
8. Press the "Data Options" button
9. From the "Change SQL Select Options" dialog window, enter your SQL statement and press OK
10. Press Next
11. If you want to save this definition so you can run it again later, specify a file name with the .DTF extension
12. Press Finish

The results of your query should be returned to the active worksheet.

Excel also has some data transfer features that allow you access toiSeries data using ODBC drivers to access the system. If you want to play around with those, here's how to get started:

1. From the pull-down menu "Data",
   select "Import External Data" -> "New Database Query"

Return to Table of Contents

Question:
How can I send data from Excel to an iSeries database?

Answer:
I don't think you'll find any OS/400 feature that makes it substantially easier. To do that, you'd probably need to buy a third-party data movement or replication product.

One approach would be using the CPYFRMIMPF CL command.

1. Define target table with DDS or SQL.
2. Export Excel file into delimited format.
3. FTP delimited format file to your iSeries server.
4. Use CPYFRMIMPF CL command to load the delimited format file into the target table.

Return to Table of Contents

Question:
How can I get a lot of files in one FTP script on the iSeries?

Answer:
MGET can be used in FTP script.

• It's easy to set the destination library to be different then QGPL'. For example, lcd mylib will direct the files to mylib.
• If the file names are short enough, the iSeries FTP client will behave the way you want (i.e. new file for every remote file), however, for longer then 10 character file names, this may be an issue.
• The easiest way to work around this naming restriction is to direct the FTP incoming files to the directory on the IFS.
  
  For example:
  
  lcd /tmp
  mget longnamesintheremote server*

Return to Table of Contents

Question:
What's the best way to call an RPG program from VB using ODBC?

Answer:
It is possible to call an iSeries program from VB using stored procedure support as follows:

1. Get mdac up and running -- This is the Microsoft part.
2. Create the procedure on the iSeries using one of the compilers (i.e. RPG' Cobol' c' pure SQL).
3. Register the procedure using the create function as follows:
   
   CREATE PROCEDURE QGPL.myproc
   (parm1 OUT CHAR(10), parm2 OUT CHAR(10))
   (EXTERNAL NAME QGPL.pgmname LANGUAGE CL SIMPLE CALL)
   
   
4. Embed the code for procedure call, this is using the CALL statement in the execute.

Return to Table of Contents

Question:
How can I print a PDF file from the iSeries?

Answer:
You can use FTP to create a spool file with the PDF in it. For example:

1. crtprtf mylib/myprtf *usrascii
2. ftp loopback
   user password
   bin
   cd mylib
   put mypdf myprtf
   quit

This creates a new spool file with the PDF file in it.

Return to Table of Contents

# # #