Ask the Experts

Ask the Experts

• Running AS/400 query to find records based on date

  Learn about a quick way of running an AS/400 query to select records based on current sysdate.  Continue Reading

• Omitting processes during a save while active backup?

  Find out if log files can be omitted during a save without causing problems in a full restore.  Continue Reading

• Consolidation across multiple LPARs using BRMS

  The BRMS Network feature allows a BRMS system to connect to other BRMS systems via a network, and enables a user to consolidate media such as backup tapes.  Continue Reading

• Options other than RCLSTG to enable file journaling?

  The only option to correct damage preventing file journaling is to use the RCLSTG command.  Continue Reading

• Good strategies for iSeries backup

  IBM's iSeries Backup and Recovery manual answers many questions related to system backup and disaster recovery.  Continue Reading

• How to save HMC critical data through the console

  On the AS/400 the HMC console backup is initiated from the HMC console, not via save option 21. These instructions will show you how to save HMC critical data.  Continue Reading

• How to print RPG output using iSeries Access

  Use work station customize object or printer type in iSeries Access to print the output of an RPG file from the AS/400 to an HP printer.  Continue Reading

• How to view source files in the library

  On the AS/400 library, source files are multi-member. View source files using GET QCLSRC.membername or MGET QRPGSRC.*  Continue Reading

• Error CWBCO1049 on AS/400

  Connecting the iSeries in AS/400, issue STRHOSTSVR *ALL. Make sure the as-signon port is in listen state and check the firewall settings.  Continue Reading

• How to use an API for adding users to EIM SSO implemenation

  Searching for the right API to add users to an EIM SSO implementation? The IBM site includes sample APIs based on C.  Continue Reading

• Incorrect CCSID on FTP connection from AS/400 to Windows

  Sending the file binary to the AS/400 after defining the destination file tagged with CCSID of 1200 may solve the problem of incorrect CCSID numbers on FTP transfers.  Continue Reading

• User being locked out by Windows share on iSeries

  Use the the iSeries QSYSOPR message to look for any errors in passwords when running a program on the iSeries that transfers data to a spreadsheet on a Windows share.  Continue Reading

• Creating a host variable of the 'where in' statement in SQL

  Create a host variable of the where in statement on the fly with dynamic SQL.  Continue Reading

• SQL server error message -321

  To solve the SQL error -321 on IBM i6.1, use the new values statement to overcome the error. If you are using an older release, declare a cursor using dynamic SQL.  Continue Reading

• Choose which column names are returned via ODBC when working with DB2 files

  When working with DB2 files with columns that have both short and long names, there is no option choose which column names are returned via ODBC because SQL-based interfaces use the long column name by default.  Continue Reading

• Developing tables in a parent-child relationship in DB2

  When developing tables in a parent-child relationship, use a primary key and a foreign key along with a unique ID to make your database easier to maintain over time.  Continue Reading

• When is the YES option for 'reuse deleted files' function the best choice?

  IBM did not change the default of the reuse deleted files function to YES for physical files in order to minimize the impact on any existing applications that may have dependencies on deleted records that are not being used. However, there there are...  Continue Reading

• Access path and an open data path differences

  On AS/400 DB2 an access path describes the order in which the rows are retrieved from a database file. The open data path (ODP) is the path through which all input and output operations are performed on a database file. As data is retrieved from or...  Continue Reading

• Convert a numeric physical file to a character in SQL without leading zeros

  In SQL, when converting a numeric physical file to digits, pass the results of the DIGITS function through the STRIP function in order to avoid leading zeros.  Continue Reading

• Inserting data from a CTE into a file in SQL

  To insert data from the CTE into a file in SQL, the common table expression definitions need to be declared within the nested SELECT statement when using a SQL insert statement.  Continue Reading

• Recovering from DST QSECOFR password disablement on V5R4

  If the DST QSECOFR and QSECOFR passwords have been disabled from multiple attempts at user login, the OS may need to be reinstalled on IBM i to recover function.  Continue Reading

• Direct default print job to tray 2 from IBM i

  Set tray 2 as the ASCII printer's default by changing the drawer attribute of the spooled files to 2 or by creating a customized work station (WCSCT) object to send requests for tray 1 to tray 2.  Continue Reading

• Changing password security levels and upgrading operating systems on the IBM i

  Before changing password levels and upgrading operating systems on the AS/400, ensure the clients connecting to the NetServer do not need the old Lanman password and customize the sign-on screen that provides a 128-character password field.  Continue Reading

• Resolving AS/400 error messages regarding attempts to use an object without authority

  When error messages arise concerning attempts to use a permanent system object without authority, find the source of the issue by looking for an AF subtype A entry where the object name is.  Continue Reading

• Determine the value of parameter UPPWEI in the DSPUSRPRF field

  The UPPWEI field corresponds to the password expiration interval field, and its values "0" and "-1" represent the *SYSVAL and *NOXMAX commands.  Continue Reading

• Change password parameters on the AS/400 without deactivating user's passwords

  Changing the QPWDMINLEN parameter or other password values will not deactivate user account passwords. The change will take affect the next time users change their passwords.  Continue Reading

• Send a *LMSG successfully on AS/400 using the SNDDST command

  When encountering problems using the SNDDST command to send a *LMSG on the iSeries, specify a valid SMTP name in the directory entry of the sender to receive a failure message from the mail router.  Continue Reading

• Configure the iSeries v5r4 to open the HTTPS port for incoming connections

  When the HTTPS 443 port is not accessible from interned but is open within the local network, open the port for incoming connections by recognizing the problem as a network firewall issue rather than an iSeries issue.  Continue Reading

• How to clear expired BRMS created *SAVF files from System i

  Use the STRMNTBRM command to expire a *SAVF on the day it is scheduled to be expired and delete an expired *SAVF if it has already been saved to tape via the SAVSAVFBRM command. If an expired *SAVF will never be saved to tape, delete it by running ...  Continue Reading

• Restore a full system backup from the production partition to the test partition

  Save a complete image of your System i production partition using option 21 from the save menu, and use these tapes to fully recover your damaged partition.  Continue Reading

• Implement variables in SQL when creating an alias

  Use dynamic SQL to implement variables at runtime in order to build the CREATE ALIAS statement, or embed the PREPARE and EXECUTE statements in a high-level language.  Continue Reading

• Simplify the process of converting a spool file from iSeries into an Excel spreadsheet

  To simplify and accelerate the process of converting a spool file from iSeries into an Excel spreadsheet, eliminate the creation of the spool file and instead create a CVS text file, which can easily be loaded into Excel.  Continue Reading

• How to resolve a CPI93B8 error message: LIC problem data has been logged for #MSTRTNS

  Use WRKPRB command to determine meaning of CPI93B8 error message  Continue Reading

• Enrolling a user profile in the i5/OS system distribution directory

  Use the WRKDIRE command to access the system distribution directory and enroll a user profile.  Continue Reading

• Booting iSeries from internal or external sources

  A NORMAL IPL of iSeries uses the IPL Load Source disk. An alternative is to boot from a backup tape by initiating a D type IPL.  Continue Reading

• Messages and spool hanging up SAVLIB jobs when OUTPUT(*PRINT) is specified

  Override QPSAVOBJ to eliminate messages hanging up SAVLIB jobs when utilizing the OUTPUT(*PRINT) command.  Continue Reading

• CL program for daily backups

  IBM makes the program source code for daily backups available on the IBM i with a simple command.  Continue Reading

• Changing user password settings

  When changing minimum password length settings or other password requirements, AS/400 users will have to work with the updated password settings the next time they are required to change their password, but their current passwords will not be ...  Continue Reading

• Using AFPRSC to print .jpg or .pdf files from the IFS on AS/400

  While AFPRSC is imperfect, it does work to print JPG and PDF files from the AS/400 IFS to an IPDS printer.  Continue Reading

• Print overlays from the AS/400 using the IBM AFP printer driver

  To create .ovl files from the AS/400 to xerox printers it is necessary to download the IBM AFP printer driver.  Continue Reading

• Setting up double-sided printing from the AS/400

  When setting up double-sided printing from the AS/400 you want to ensure that BACKOVL is set as a constant back, the printer file definition includes DUPLEX (*YES), and when writing to the page, only the front page is printed to.  Continue Reading

• Do AS/400 printers have to be IPDS compatible?

  A user heard that AS/400 printers have to be IPDS compatible, but Chip Milosch says: Myth!  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

• 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

• 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

• 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

• Error: CWBC01049

  If you get the error CWBC01049, it can often mean that the Client Access servers haven't been started.  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

• 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

• 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

• 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

• 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

• 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

• 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

• 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

• Changing the default system printer for AS400

  Using WRKSYSVAL QPRTDEV you can change the default system printer for your System i.  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

• 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

• 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

• 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 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

• 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

• 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

• 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

• 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

• How to view AFP print spools remotely

  In this ATE response, Chip Milosch explains how to user iSeries Navigator to view AFP spool files.  Continue Reading

• Saving a message from a user's message queue

  ISeries expert Ken Graap explains how to save a message from a user's message queue by using the DSPMSG command or writing an exit program.  Continue Reading

• Copying a physical file back to a spool file

  ISeries expert Ken Graap explains how to copy a physical file back to a spool file.  Continue Reading

• Tracking row changes in DB2 tables

  ISeries database expert Kent Milligan discusses how to track changes made to DB2 tables.  Continue Reading

• Identifying users' system authority

  How do users get the authority to run certain commands? How do you identify those users? System i security expert Carol Woodbury has the answers.  Continue Reading

• Using an FTP script to send a text file from AIX to AS/400

  ISeries PC/Windows connectivity expert Shahar Mor explains how to send a text file from an AIX machine on Linux to a remote AS/400.  Continue Reading

• How to FTP an IFS file on AS/400

  ISeries PC/Windows connectivity expert Shahar Mor discusses how to FTP an IFS file to another system.  Continue Reading

• An FTP script to get a file from a PC to AS/400

  ISeries PC/Windows connectivity expert Shahar Mor gives an example of an FTP script that will move a file from a PC to AS/400.  Continue Reading

• Viewing the job log of an *ALLOBJ user

  ISeries security expert Carol Woodbury explains how a non-*ALLOBJ user can view the job log of a user with *ALLOBJ access.  Continue Reading

• BRMS backup jobs: Clearing expired save files

  ISeries backup and recovery expert Ken Graap explains how a user can clear expired save files during BRMS backup jobs.  Continue Reading

• Completing an unattended Option 21 save to tape

  ISeries backup and recovery expert Ken Graap explains how to do an unattended Option 21 save to tape.  Continue Reading

• Executing a CL command on AS/400

  ISeries backup and recovery expert Ken Graap explains how to use FTP, the QUOTE command and the RCMD command to execute a CL command on AS/400.  Continue Reading

• Identifying large Integrated File System files

  ISeries backup and recovery expert Ken Graap discusses how to find large files in the integrated file system (IFS).  Continue Reading

• Dynamic SQL statement in an RPG program

  DB2/400 and DB2 UDB expert Kent Milligan discusses how to substring a character field on an SQL join statement.  Continue Reading

• Risk associated with *ALLOBJ access in iSeries

  ISeries security expert Carol Woodbury discusses the risks associated with granting users *ALLOBJ access.  Continue Reading

• Copying IFS files with QCMDEXC as SQL stored procedure

  ISeries PC/Windows connectivity expert Shahar Mor discusses how to copy IFS files to AS/400 with the QCMDEXC command.  Continue Reading

• AS/400 security levels

  System i security expert Carol Woodbury explains the difference between level 30 security and level 40 security on AS/400.  Continue Reading