Ask the Experts

Ask the Experts

• 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

• 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

• 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

• Monitoring members 'stuck' within a physical file on an EDI system

  To monitor members stuck within a physical file on AS/400, you can periodically use the display file description (DSPFD) command to create an output file that can be analyzed, or occasionally run queries against the SYSPARTITIONSTAT catalog view in ...  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

• 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

• 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

• 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

• 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

• 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