Ask the Experts

Ask the Experts

• Running a job on the iSeries after it has been transferred to a PC

  The REXEC command on a PC or the RMTCMD within iSeries Access allows you to call a program on the iSeries from a Windows PC.  Continue Reading

• Backing up PDFs within the IFS on AS/400

  PDFs are stored within the IFS on AS/400, and backup proceedures should include the SAV command for the directory in which the PDFs are located.  Continue Reading

• SQL7008 error in iSeries Access and journals

  SQL7008 error in iSeries Access is most frequently caused by trying to update a file that is not journaled or when a user lacks authority to the journal.  Continue Reading

• Converting and transferring AS/400 packed decimal fields to ASCII systems

  You can send a physical file with packed decimal fields to an ASCII system via iSeries Access or via the CPYTOIMPF command and then send the file with FTP.  Continue Reading

• Creating a remote output printing queue from the AS/400

  A simple series of commands in AS/400 can automatically have a remote printer print every spool file released into the output queue.  Continue Reading

• Transferring binary files to IFS from a PC via FTP

  You can transfer a PC file to the IFS on AS/400 using client access, but an easier method involves using Netserver and FTP.  Continue Reading

• Checking if a local port is used by another job on AS/400

  A command called NETSTAT shows who is using local ports on AS/400 but it can only be used interactively to create spooled output.  Continue Reading

• Why backup your libraries to virtual tape instead of SAVF?

  If SAVF files can be FTP'd to anywhere, why is virtual tape necessary? The answer: Many libraries versus just one.  Continue Reading

• Running source member SQL statements with RUNSQLSTM

  The RUNSQLSTM command expects a semi-colon (;) at the end of each SQL statement.  Continue Reading

• Enabling IBM i to access Microsoft SQL server data

  Microsoft SQL Server does not fully support the open group DRDA standard, and thus middleware is needed to enable the AS/400 to access Microsoft SQL server data.  Continue Reading

• Using SQL to search within a DB2 field on IBM i

  By taking advantage of the Row_Number function introduced in IBM i's V5R4, you can process a range of values within a field.  Continue Reading

• Controlling a library list on a remote system

  The key to determining the library list used for SQL statements executed in a remote SQL package is found in the configuration and description of the QRWTSRVR prestart jobs that are used by DRDA connections on the AS/400.  Continue Reading

• FTP from AS/400 to PC folder

  By using the CPYTOSTMF command you can create a stream file in the IFS that can be sent via FTP to anywhere you want.  Continue Reading

• Conducting a SAVSYS before installing PTFs

  Determining whether or not to do a SAVSYS instead of just SAVSECDTA and SAVCFG prior to a PTF doesn't have to be tricky. The SAVSECDTA and SAVCFG commands don't save License Code PTFs, but SAVSYS does.  Continue Reading

• Auditing: RPG with embedded SQL showing up as a changed object

  DB2 stores access plans inside the SQLRPG program object for each SQL statement embedded within the program. When DB2 updates these access plans because of a system environment change, the change date of the program object will be updated at the ...  Continue Reading

• Selecting a PF with multiple members from SQL

  The simplest technique for selecting a PF with multiple members on AS/400 from SQL is to create an alias.  Continue Reading

• Referencing a renamed column on SELECT clause in SQL

  The SQL Standard does not allow a renamed column on the SELECT clause to be referenced on a different part of the SQL statement such as the WHERE clause. To do it on a single statement, an SQL common table expression can be used.  Continue Reading

• Copying the spool file stream to IFS

  Instead of printing the spool file to a PC, an easier approach is to copy a spool file to IFS, which makes it available on the PC.  Continue Reading

• Telnet connection repeatedly closes

  If a Telnet connection to the AS/400 keeps closing, the keep-alive settings may need to be set to lower time limits.  Continue Reading

• Changing system CCSID

  If done without attention to detail, changing the CCSID on the AS/400 can lead to very messy database files.  Continue Reading

• Error: CWBC01049

  If you get the error CWBC01049, it can often mean that the Client Access servers haven't been started.  Continue Reading

• How to FTP without knowing the file name

  Using the mget nbor command on the AS/400 when FTPing from a PC will get all the file names starting with gbor to the current library.  Continue Reading

• Why is a printer in pending mode?

  When a printer can be pinged but won't print from the AS/400, how can you determine why the job is pending?  Continue Reading

• User authorities required to run a proceedure on another platform

  If a program automatically transfers a file from Windows to AS/400 and then runs the program on the AS/400, certain user authorities must be given in order for the program to run.  Continue Reading

• Users unable to sign off of AS/400

  When Windows users use option 90 to sign off of AS/400 and they are automatically reconnected, this can be a problem. This can be altered by setting the ENDCNN to *yes.  Continue Reading

• IFS folder error

  If you are trying to FTP a file into an IFS folder on AS/400 and receive an error, the naming format may not be set properly.  Continue Reading

• Creating the correct member type in a source physical file via FTP

  On AS/400, if you use FTP to copy source files from Windows and you need them to be RPGLE type, you can send a remote command to change the type after the transfer is completed.  Continue Reading

• Changing user password expiration

  On the AS/400, if you set QPWDEXPITV to 60 days but want specific users to be exempted, you can specify them and set PWDEXPITV to *NOMAX.  Continue Reading

• User authorities for running a remote procedure on the AS/400

  Running a procedure on another platorm and transferring a file and running a remote command on the AS/400 requires specific user authorities. These don't need to be Admin settings, but certain authorities must be available to make it work.  Continue Reading

• Detecting system changes made by outside IP address

  If you suspect that changes are being made by an external source, you can use an exit point program to monitor the activity and trace the IP address.  Continue Reading

• Changing BRMS configurations for new naming conventions for i5

  Use BRMMED to adapt the i5 to new location names for relocated machines.  Continue Reading

• Revisiting automating email for a backup joblog on AS/400

  If you want to get an email automatically sent to you that includes the backup joblog for your AS/400, Ken Graap outlines the steps you need to take.  Continue Reading

• Primary group authority: How it works

  If you're wondering if primary group authority impacts the system performance of your AS/400, Carol Woodbury provides insight into how this setting works on the System i.  Continue Reading

• Blocking access to SQL line commands

  You may think that blocking access to SQL line commands will protect your data. However, Carol Woodbury warns that object level security is the only real way to keep your data safe from unwanted manipulation.  Continue Reading

• Moving files to new libraries allows access to only groups or users that are authorized

  If you have a file in AS/400 that needs to be replaced in a library, but get an error message indicating that a user cannot access a library, the best option is to create a new library and give access only to the group that needs access.  Continue Reading

• Changing telnet ports: A security solution?

  Changing telnet ports is not a means by which to make your AS/400 servers secure. To manage the TCP/IP settings on the System i, you use the STRTCPSVR command. But you need to know how to manage these settings first.  Continue Reading

• Moving to security level 30

  On the AS/400, the key to moving from security level 20 to level 30 is determining where users will get their authority since the system removes user's *ALLOBJ when the system is IPLed. IBM's iSeries Security Reference Manual provides steps to take ...  Continue Reading

• Menu security's relationship to object authority

  On AS/400, if a group of users has been assigned a menu that does not get them to any security functions, they are still able to access sensitive objects because of the *ALL authority to PUBLIC.  Continue Reading

• HIPER PTF installation and cover letters for SF99097

  Should HIPER PTF be installed without Group PTF on an AS/400? Also, how to locate PSP documents including cover letters.  Continue Reading

• Encrypting files or fields on the iSeries

  Encrypting files on iSeries is no trivial undertaking. Using vendor technologies such as those from Linoma Software and nuBridges may be easier than cobbling it all together with IBM tools.  Continue Reading

• Library QUSRSYS not completely installed

  If you receive an error following a QUSRSYS install on AS/400, you may need to reinstall while keeping a backup of the previous library because it contains user data.  Continue Reading

• Changing the QSECOFR password

  If the QSECOFR password on your system has been incorrectly set, you have to go in and reset it. The instructions for this are outlined in IBM's iSeries Service tools user IDs and passwords manual.  Continue Reading

• Resolving printing problem where columns are omitted from print job

  If a print job from AS/400 is omitting part of the text, the printer defintion table (PDT) may need to be changed to one with a smaller font.  Continue Reading

• HP 4300 printing errors from iSeries 515

  Some printer configurations are more tricky on the AS/400 than would first be anticpated. There are a couple of steps using WSCST that may need to be taken, and some helpful configurations for HP printers that need to be completed.  Continue Reading

• Delayed printing from AS/400 on Windows 2003 network server

  A delay in printing from AS/400 on Windows 2003 network server is likely because of the default time for the Windows server to accept the incoming connection.  Continue Reading

• Separator pages: Printer generated or AS/400 generated?

  If a print job contains separator page and you don't know if it's because of the printer settings or the settings on the AS/400, you can look at the device configuration settings for Separator drawer and Separator program -- if they are set to ...  Continue Reading

• Tools for printing a PDF from IFS on iSeries

  There are two options for printing PDFs from IFS on the AS/400. One requires a printer that supports POSTSCRIPT printing directly, and the other requires obtaining iSeries Office Integrator from RJS Software.  Continue Reading

• Tray selection for remote printers from AS/400

  Tray selection is equivalent to DRAWER attribute of the spooled file attributes on the AS/400. If there is a problem with which tray is being selected, a custom object WSCST may need to be set up.  Continue Reading

• File transfer DTF "hooks"

  Using DTF client access to transfer files from iSeries to Windows can be tricky if the file contains Polish characters. To ensure the file transfers correctly, make sure that the Windows regional settings are set to the right language, and ensure ...  Continue Reading

• Users not recognized on iSeries when logged in from Windows 2000

  The iSeries Navigator properties control how Windows logs into the Windows server. If the user in Windows is not known to the iSeries (i.e does not exist ) you get an error. Change the properties in iSeries Navigator to use another form of user ...  Continue Reading

• Joining files using SQLRPGLE

  In DB/400, for a join operation returning a single row, the SELECT INTO statement can be used in SQLRPGLE.  Continue Reading

• Querying system catalog tables to determine table alias

  In order to determine the alias of a table in DB2 on i, you can query the system catalog tables. AS/400 expert Kent Milligan shares how.  Continue Reading

• Excluding characters from AS/400 DB2 SELECT statements prevents mapping errors

  In DB2 on AS/400, rows with characters that cannot be converted to a numeric value (A or #) need to be excluded from a SELECT statement in order to avoid a data mapping error when running the INT function on GLSBL.  Continue Reading

• How to use the QQQVEXPL API

  The QQQEXL API does not need to be coded to on AS/400 DB2, instead you may use a Visual Explain tool which transparently uses the API to analyze the access plans for SQL statements and query requests.  Continue Reading

• Monitoring escape messages and using RCVMSG for diagnostic messages

  When an error occurs on an IBM command, the diagnostic messages are sent first, then an escape message, which you can monitor. On AS/400 you see the escape message, and then you use the RCVMSG command to get the last diagnostic message sent to your ...  Continue Reading

• SAVF FTP on AS400 LAN: 504 Binary image transfer required for save files

  Use the FTP subcommand BIN to set up for a binary image transfer prior to conducting SAVF file transfer between AS/400 machines.  Continue Reading

• Restricting specific user command line access

  On i5/OS, to give command line access to users but restrict certain parameter values for some selective commands, Ken Graap recommends that a combination of the "Limit Capabilities" attribute be used in combination with the "Allow Limited User" ...  Continue Reading

• Sending a message to an output file after backup completion

  On AS/400, it may be easier to create a special message queue for backup processes and send completion and/or error messages there instead of trying to write them to spool or to a physical file.  Continue Reading

• Print private authorities (PRTPVTAUT) command for user objects in a library with *public exclude

  On i5/OS, the print private authority (PRTPVTAUT) command allows you to print a report of all the private authorities for objects of a specified type in a specified library, folder or directory.  Continue Reading

• ASP configuration restore

  Configuring auxillary storage pools on the System i5 is necessary before a system restore all.  Continue Reading

• Decrease iSeries downtime caused by SAVSYS backup

  It is possible to save time on SAVSYS backups on the AS/400. The SAVSYS command requires that your system be in a restrictive state. The only active job in a restrictive state is the system console. IBM has split the SAVSYS command into three parts,...  Continue Reading

• Automatic email for backup job log on AS/400

  How to automatically generate an email after the job log on a System i has completed the backup.  Continue Reading

• Changing the default system printer for AS400

  Using WRKSYSVAL QPRTDEV you can change the default system printer for your System i.  Continue Reading

• JDBCODBC functionality -- Java to Excel for complex workbooks

  Java to Excel with the AS400 may be attractive, but if you have complex VBA macros in Excel, the JDBCODBC bridge will not work well for you. A better option may be using the Groovy COM interface.  Continue Reading

• Printing IPDS with overlays to Xerox MFU printers

  Using an AS400 to print IPDS with overlays to Xerox MFU printers can be made easier if you use Host Print Transform.  Continue Reading

• Remote access from behind a SonicWall firewall

  Firewall rules may be preventing remote connections from a PC to AS400. A common problem is iSeries emulation because it requires the signon port to be open (and not just the telnet port).  Continue Reading

• Setting up personal communications between iSeries and Windows via the internet

  To connect iSeries access emulation over the Internet, connect the Internet to your iSeries, and set the AS400 IP adress on the connection setting in Windows.  Continue Reading

• Automating data transfer to iSeries using Client Access

  To automate data transfer through a command instead of GUI, run the RFROMPCB.EXE from an AS400 command line.  Continue Reading

• Deleting iSeries audit logs

  An audit log on AS/400 is a journal, and the data is stored in receivers attached to the journal. These can be deleted, but for future forensic reasons, you'll want to save it before you delete it.  Continue Reading

• Moving to security level 40

  What should be considered before moving a System i machine from security level 30 to security level 40?  Continue Reading

• Using remote AS400 commands in a Windows environment

  RUNRMTCMD is the AS400 implementation of REXEC client and it supports 2000 characters, but the REXEC daemon from iSeries Access only supports up to 1000 characters. To run a long command, such as a SQL Server IS package, you will want to use a ...  Continue Reading

• Overriding the timeout interval on specific terminals

  If you've set your AS/400 job time-out interval to a short period for security purposes, but find it's too short for specific terminals, you need to write a program or get vendor software to do this for you says expert, Carol Woodbury.  Continue Reading

• Listing contents of multiple FTP directories on AS/400

  Learn how to 'append data to the file' with the FTP DIR command, and eliminate the hassle of having to end the FTP session and restart to view multiple directories.  Continue Reading

• Using SQL syntax to override DB2 automatic column name generator on iSeries

  If you don't want to drop and recreate a table to change the internal names in your AS/400 DB2, a special SQL syntax can solve your problem.  Continue Reading

• Chain operation error after converting RPG3 to ILE RPG (RPGIV)

  RPGIV will 'think of' incoming integer fields as binary, and then will redefine them in the program structures as PACKED. So, when you chain a record, the incoming data has to be converted from integer (which RPGIV thinks is Binary) to packed. This ...  Continue Reading

• Making a file with an automatic key in iSeries with DDS keyword

  If you want to create an automatic key using a DDS keyword on AS/400, Kent Milligan explains that any native writes performed against an SQL table created with an identity table will result in DB2 generating identity values.  Continue Reading

• Accessing AS/400 data using Excel ODBC drivers

  There are a couple of options for accessing AS/400 data, from the old way using Client Access ODBC drivers, to Java, to a new application from Groovy.  Continue Reading

• Adding journal tapes to the BRMS media pool on AS/400

  There are a couple of options for adding journal tapes to the backup for AS/400, explains Ken Graap.  Continue Reading

• The SETOBJACC command

  The SETOBJACC command loads data into memory, and with abnormal system end, will not prevent changed data from being written to disk.  Continue Reading

• Determining relationships between tables and files in DB2 UDB

  There is no SQL command that can simply determine relationships between tables and files on your iSeries. If you have referential integrity constraints in place, you can write queries against the system catalog objects such as QSYS2/SYSREFCST to ...  Continue Reading

• Retrieving data from AS/400 into Crystal Reports

  Crystal Reports does not have direct access to data in AS/400, therefore an external stored procedure or an external user-defined function is necessary.  Continue Reading

• SQL Updates with functions

  How to code an SQL UPDATE on AS/400 with mathematical functions is explained by Kent Milligan.  Continue Reading

• Killing option 21 backup midsave

  Is it a backup sin to kill or end the process once an AS/400 system is in the midst of an option 21 backup? The good news is that doing this doesn't destroy files on your disk, but it does make the backup process that has occurred up until the point...  Continue Reading

• Getting modules to adopt authorities from the main program

  Using an AS/400 program with modules that do user profile maintenance? What if all the modules don't adopt the same authority, and thus abend? Ken Graap provides an elegant solution: set your modules to have adopt authority also.  Continue Reading

• SQL statement history storage

  Hitting the wrong button in iSeries can cause a serious headache. If you have exited an STRSQL session without saving and want to retrieve your lost information, you may be out of luck. But Scott Ingvaldson explains where SQL statement history is ...  Continue Reading

• Setting up an automatic reply in the system reply list

  AS/400 systems can use the system reply list to automatically reply, but only if the inquiry message reply attribute is specified as INQMSGRPY(*SYSRPYL).  Continue Reading

• Clearing physical member file records

  If you are trying to clear a physical member file, but get a message that says: FILE *** in LIBRARY *** is in use, what do you do? Scott Ingvaldson provides a quick fix.  Continue Reading

• Drive space management commands

  Helpful commands to manage drive space and view disk status.  Continue Reading

• Group PTFs

  Do you want to know what group PTFs are? Better yet, do you want to know which ones may be installed on your AS/400? Scott Ingvaldson explains what they are and why they exist, and best of all, where to find out which ones you have installed in your...  Continue Reading

• Space offset X'0015FF00' or X'0000000000000000' is outside current limit for object QIGC2424C

  When AS/400 running STOP gives the error message 'Space offset X'0015FF00' or X'0000000000000000' is outside current limit for object QIGC2424C', the value being given to the space pointer is greater than the size of the object, and system stops the...  Continue Reading

• Searching part of a name or address in AS/400

  In ILE RPG code can be written to conduct the search on part of a name or address using the field D#SFLN.  Continue Reading

• iSeries application display subfiles with other program windows

  In iSeries, the display file for programs may need to be set to DFRWRT(*NO) to display other program windows when using multiple programs jointly. DFRWRT(*YES) tells the workstation controller to 'Defer writing the display formats until you receive ...  Continue Reading

• Searching fields for values

  In AS/400, if you need to find database fields that contain a specific value, in ILE RBG the field to use is D1FIND. The trick is that you have to add code to turn all search terms into uppercase, or the program won't recognize the search term if ...  Continue Reading

• Transferring data via FTP on AS/400 systems

  Using the iSeries' built in FTP server to transfer data between two AS/400 systems is explained, with step-by-step instructions.  Continue Reading

• Alternatives to ODBC

  If ODBC seems to be slow, it may be because of the SQL server. But alternatives exist, such as the ado.net  Continue Reading

• How to combine multiple spooled files into a PDF

  Chip Milosch responds to a reader question about combining multiple spool files into a single bookmarked PDF using SpoolFlex.  Continue Reading

• Cleaning up overlayed documents

  Chip Milosch answers a question on cleaning up scanned images used as an overlay in AFP Workbench Viewer before printing using the AS/400.  Continue Reading

• Ensuring sufficient iSeries system backup for data protection

  Ken Graap explains how an option 21 save (full system save) once a month is not sufficient to protect system disk information if only a daily data backup is conducted.  Continue Reading

• Finding initialization information for LTO tapes in AS/400

  How to find the initialization information for LTO tapes in AS/400 may not be possible without the job log of the job that performed the INZTAP command.  Continue Reading

• Remote *LAN printer configuration: Not printing full documents

  Configuring a remote *LAN printer to print full documents, ensuring that the *DEVD uses proper port values is key to avoiding time out or contention issues.  Continue Reading