Query for iSeries Workshop

2 Days

This course will teach the student the basic structure of database files on the AS/400 and how to define, run and change queries of those files, using the IBM Query for AS/400 program product (5769-QU1 for V4 or 5722-QU1 for V5).

This course is intended for programmers and non-programmers. It is intended for anyone  who  needs  to  produce reports from a DB2 UDB for iSeries files with which they are familiar.

Students  should  be  able  to  select  options  from  an  AS/400  menu  and  enter AS/400 commands.

After completing this course, you should be able to:

  • Describe the structure of physical and logical files and how they are defined on the AS/400
  • List the major features and functions of AS/400 Query
  • Define a query with multiple break levels and summary calculations
  • Define a query with result fields that are computed using arithmetic calculations
  • Embed a field name in break text
  • Perform summary calculations on result fields
  • Select records based on whether or not a field is NULL
  • Use date-type fields in a query
  • Use date, time and timestamp expressions in result fields
  • Compute the  date  duration  between  two  fields  that  contain  dates,  but which are character or numeric fields, not date-type fields
  • Use the DIGITS  function  to  return  the  character  representation  of  a number
  • Use substring  and  concatenation  operations  to  manipulate  character fields
  • Define queries for all three types of joins allowed by query:
    • 1= Matched records
    • 2= Matched records with primary file
    • 3= Unmatched records with primary file
  • Join three files in one query
  • Define a  query  with  fields  of  length  zero  so  it  will  not  show  in  the  detail lines of a report, but will be available in break text
  • Write a query in which fields in detail lines are compared to break level summary values