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

• Bundling multiple one-page reports from IBM i into single document

  To print a single document with all of the one page reports generated by the iSeries you need to first create a program that bundles all the individual spool files into a single spool file.  Continue Reading

• How to migrate old *ALLUSR data using BRMS to V6R1

  When upgrading from an older I5/OS release to V6R1 using BRMS use the detailed instructions provided by IBM to help using BRMS to restore *ALLUSR data.  Continue Reading

• Parallel backups using save option 21

  If you are using a save option 21 for parallel backups on the AS/400, learn how to use BRMS.  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

• Will overloaded discs impact iSeries performance?

  With the AS/400, use the performance tools to collect data during a busy part of the day and then analyze it to see if your disk performance is impacting overall system performance.  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

• Blocking AS/400 DB2 users

  To block DB2 users from altering data using WinSQL tool, a security software tool may be the best bet for true protection.  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

• Trouble accessing IFS path from Win2k3 server

  What to do when an application is published in IFS from Win2k3 server, and users are unable to access the shared folder on AS/400.  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

• Allow access to data from a stored procedure result set using COBOL or RPG

  For COBOL and RPG programs to process the data after the stored procedure call completes, you need to direct the SQL stored procedure to place the result set in a global temporary table. COBOL and RPG cannot access embedded SQL as of V6R1.  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

• Solve printing problems when using Personal Communications 5.7

  When encountering difficulties printing using Personal Communications 5.7, analyze the printer setup process to fix any problems.  Continue Reading

• Determine printing options available for preprinted medical forms

  Use forms design and data mapping tools to create soft forms that the print data can be dragged and dropped onto rather than using preprinted medical forms.  Continue Reading

• Acknowledging control characters in a print file on IBM i without writing a program

  Issue an override print file command before copying a file to QSYSPRT in order to acknowledge control characters without writing a program on the AS/400.  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

• Provide authorization in order for a user to start a subsystem within startup routine

  Look in the audit journal (QAUDJRN) on the AS/400 for an authority failure message with the name of the library as the object name. Use the information provided by the DSPAUDJRNE command to solve the problem.  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

• Define journal code value "K"

  On AS/400, the journal type AF subtype K, shows that a user profile lacks the special authority required by the function attempting to run.  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

• Prevent insiders from obtaining user ids and passwords on the IBM i

  Configure all FTP and iSeries Access connections to use SSL in order to prevent outsides from obtaining user ids and passwords.  Continue Reading

• Allow a user to view a library prod without granting full access to all data

  Implement the *USE command to provide user access via the view to the library, withholding the user from *OBJOPR object authority. This prevents the user from gaining access to the physical file.  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

• Transfer files from one environment to another without closing all other AS/400 sessions

  On the AS/400 system, close all sessions in the receiving envrionment when transferring files from one environment to another if the sessions hold exclusive locks.  Continue Reading

• Extend storage capacity on an IBM i without negatively effecting system performance

  After extending storage capacity on an IBM 520, the system performance depends on the workload amount and should not be affected negatively as long as the system has a good controller with lots of free space.  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

• Perform a full system backup without user profile QSECOFR

  On the System i, to perform a fully system backup without user profile QSECOFR, just implement a user profile with special authority *SAVSYS.  Continue Reading

• Faster backup method for System i

  If you System i backups are taking too long, you can create a CLP with basic commands to save time.  Continue Reading

• Authority restrictions on the AUTL on backup/test systems and AUTL on live system

  When restoring an object on the System i, the authority the object recieves from an Authority List will not change. Although two Authority Lists on different systems may have the same name, an object with a link to an Authority List will have ...  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

• System i backup not advised using network NETBACKUP

  Backup the AS/400 with locally attached LTO3 tapes or utilize BU software to ensure a SAVSYS tape can be made in case a system recovery is required.  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

• Control and fix date format problems on AS/400 SQL

  To avoid date format problems on the DB2 for iSQL interface, specify a date format that supports a four-digit year value with the DATFMT parameter in the SQL precompiled commands.  Continue Reading

• Disable SQL query optimizer tool that removes trailing blanks

  When working with the length function used in a WHERE or ORDER BY clause, disable the DB2 SQL query optimizer from removing trailing blanks by changing the variable length optimization settings.  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

• Remove character limits from domain or users on RMTUSER

  Use the STRPCCMD command or install advanced rexec daemon to allow more characters for a domain/user on a PC network.  Continue Reading

• Generically send a text file from the IFS via FTP

  When using mget or get on iSeries, it should work. However, one user received an error message when trying to do this.  Continue Reading

• Configuring Exchange server to allow mail forwarding from AS/400

  To forward mail from AS/400, configure the Exchange server to allow mail forwarding requests.  Continue Reading

• Automate Client Access to find files on Windows scheduler

  Use the RTOPCB command to transfer files from the AS/400 to the PC when automating file transfers.  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

• Remove a media with a *PERM expiration date from the inventory

  Use BRMS Media to expire and remove a *PERM backup tape from the inventory.  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

• Deleting records in SQL database files when fields are duplicated in another file

  To delete a file that contains fields duplicated elsewhere, you need to code a subquery on your SQL statement.  Continue Reading

• Using CHGPF to change file attributes of core application files

  Manually recompiling RPG programs and AS/400 queries is necessary when using the CHGPF command to alter attributes of database files.  Continue Reading

• How to summarize an alias reference outside of SQL views and MQTs

  Apply a user-defined table function to summarize an alias reference and reference the UDTF on a SELECT statement.  Continue Reading

• Uniquely matching two fields in an SQL table to fields in a different table

  Use an UPDATE statement in SQL to match fields in one SQL table on AS/400 to fields in another SQL table.  Continue Reading

• Timestamp not in valid format using CPYFRMIMPF

  A timestamp error is caused by either not specifying the proper string delimiter on the CPYFRMIMPF command or not formatting the quote properly.  Continue Reading

• A solution to write data to QDLS in Excel format

  Use the CPYTOIMPF command to convert data to Excel format in QDLS file system.  Continue Reading

• SAVF by FTP on AS/400 -- error "source file not found"

  On the AS/400, if you don't specify the correct commands on the "source" system to locate the source file, you will get the source file not found error when you try to use FTP. Ken Graap provides an example of using FTP to transfer a save file from ...  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

• Comparing AS/400 data to a backup tape

  To ensure everything that should be backed up from your AS/400 has been backed up, a disaster recovery test is the best bet. There is no automated technology to compare the contents of a backup tape to the data on an iSeries.  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

• AS/400 system user class settings versus CL command authorities

  An AS/400 user class setting is not necessarily the setting that allows a user to run a command on the system. CL command authorities are separate settings that need to be understood.  Continue Reading

• Detecting copied files on the AS/400 using audit journals

  Want to know if a user has copied a file from the AS/400? Use QUADJRN and look at ZC and ZR to detect changes and reads of the files.  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

• AS/400 receipt printing stuck in SND

  When a receipt is sent to a printer, the printer will print the receipt but then remain stuck in SND and the receipt must be manually deleted in order for the next receipts to print.  Continue Reading

• Workstation customization is necessary for printer drawer selection

  A workstation customization project (WSCST) may be necessary to resolve problems printing from the AS/400 to a specific printer tray (drawer).  Continue Reading

• RPG fetch statement coding to dynamically call unknown fields and data types

  On the AS/400 in DB2 it may be necessary to make a RPG program that calls select statements with unknown data and fields. SQL Descriptor Area processing or SQL CLI can provide a solution to programmers in need.  Continue Reading

• Size limit of DB2 table in V6R1 on IBM i

  The upper limit of a DB2 table on V6R1 remains unchanged from V5R4 at 1.7 TB. However, SQL can be used to create larger partitioned tables with IBM's DB2 Multisystem product. Each partition in this arrangement can store 1.7 TB.  Continue Reading

• Displaying an image from the AS/400 on a PC, troubleshooting

  If an image from the AS/400 does not display on a PC using STRPCCMD, you may need to check to make sure the PC is connected to the iSeries. If it is not, make sure that NetServer is working. If it is, but you can't see the directory QDLS, you need ...  Continue Reading

• 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

• 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