INF107 - Minicomputer Operations
Class Eight

 
  1. Chapter 23 – Structured Query Language (SQL/400)
    1. SQL Overview
      1. Industry-wide way to define and manipulate relational databases
      2. Was "theoretical" for many years because the hardware on ANY platform was not powerful enough to drive it
        1. This is why most IBM iSeries eServer (AS/400) shops do NOT use it.
          1. They defined their databases at a time when the IBM iSeries eServer (AS/400) did not have enough power to drive it.
          2. Instead, they use Data Definition Specifications
      3. We are studying SQL because:
        1. It forms the basis for ODBC (Open Data Base Connectivity) used in client / server applications
        2. It is used in many information retreival tools.
        3. More SQL based applications will be written
        4. It comes "free" with the system
        5. You will probably be exposed to it at some point!
    2. "SQL Terms"
      1. SQL uses different names for the same concepts than the "native" Data Description Specifications (DDS) language
      Concept SQL Name "native" DDS Name
           
      Container for objects Collection Library, database journal, journal receiver, system catalog, and data dictionary
      Container for data Table File
      Alternate view for data View Logical File
      Type of data item Column Field
      Occurrence of data item Row Record
      System level knowledge of database Catalog System commands DSPFD, DSPFFD, & DSPDBR (for example)
    3. Data Definition Language
        1. Used to create, define, and delete items in SQL
        2. CREATE xxx, where xxx is an SQL name from above, creates the SQL object
        3. DROP deletes an SQL object
    4. Data Manipulation Language
        1. Used to perform operations on SQL based data
        2. SELECT – Retrieve a set of data on which to perform operations
          1. Columns of data to select
            1. SUM – compute sum of selected rows
            2. COUNT – determine count of rows selected
          2. FROM – name of table(s) containing columns
          3. WHERE – select / omit criteria
          4. ORDER BY – sort the data
          5. GROUP BY – Subtotal sums or counts for a variable
            1. HAVING – performs select / omit for groups
        3. INSERT – Add a row to the table
        4. UPDATE – Change the contents of a column
          1. SET – new contents of column
          2. WHERE – which rows should the column be updated
        5. DELETE – Remove rows from a table
          1. WHERE – which rows should be deleted
    5. SQL as implemented on the IBM iSeries eServer (AS/400)
        1. Another example of implementing a newer technology over the IBM iSeries eServer (AS/400)’s existing architecture
          1. without compromising the existing function
          2. protecting the investment in existing technology
        2. Interactive SQL
          1. Use the STRSQL command to get into Interactive SQL
          2. Use the command prompter (F4 key) to get menus on what commands to use
        3. SQL commands can be buried into high level language programs
          1. I don’t recommend it!
    6. Instructor demonstration of SQL on the IBM iSeries eServer (AS/400)
        1. Sample SQL/400 Session 1


          2. > CREATE COLLECTION IT107

          Collection IT107 created.

          Session was saved and started again.

          STRSQL parameters were ignored.

          > CREATE TABLE IT107/FALL_1998_STUDENTS (STUDENT_NAME FOR COLUMN NAME

          CHARACTER (30) NOT NULL WITH DEFAULT, MAILING_ADDRESS FOR COLUMN

          ADDRESS CHARACTER (30) NOT NULL WITH DEFAULT, GRADE_POINT_AVG FOR

          COLUMN GPA DECIMAL (3, 2) NOT NULL WITH DEFAULT)

          Table FALL_1998_STUDENTS created in IT107.

          > INSERT INTO IT107/FALL_1998_STUDENTS (STUDENT_NAME,

          MAILING_ADDRESS, GRADE_POINT_AVG) VALUES('John Myers', 'C/O BCC',

          3.62)

          1 rows inserted in FALL_00001 in IT107.

          > INSERT INTO IT107/FALL_1998_STUDENTS (STUDENT_NAME,

          MAILING_ADDRESS, GRADE_POINT_AVG) VALUES('Joe Doakes', 'C/O ABC',

          2.34)

          1 rows inserted in FALL_00001 in IT107.

          > SELECT GRADE_POINT_AVG, STUDENT_NAME FROM IT107/FALL_1998_STUDENTS

          WHERE GRADE_POINT_AVG > 3.0

          SELECT statement run complete.

          > SELECT GRADE_POINT_AVG, STUDENT_NAME FROM IT107/FALL_1998_STUDENTS

          SELECT statement run complete.

          > SELECT GRADE_POINT_AVG, STUDENT_NAME FROM IT107/FALL_1998_STUDENTS

          ORDER BY GRADE_POINT_AVG

          SELECT statement run complete.

          > SELECT GRADE_POINT_AVG, STUDENT_NAME FROM IT107/FALL_1998_STUDENTS

          ORDER BY GRADE_POINT_AVG

          SELECT statement run complete.

          Session was saved and started again.

          STRSQL parameters were ignored.

        2. Sample SQL/400 Session 2
    > CREATE TABLE IT107/COURSE_MASTER (COURSE_ID FOR COLUMN COURSE CHAR

    (15) NOT NULL WITH DEFAULT, COURSE_NAME FOR COLUMN NAME CHAR (30)

    NOT NULL WITH DEFAULT, COURSE_CREDITS FOR COLUMN CREDITS DECIMAL

    (1, 0) NOT NULL WITH DEFAULT, COURSE_HOURS FOR COLUMN CHOURS

    DECIMAL (3, 2) NOT NULL WITH DEFAULT)

    Table COURSE_MASTER created in IT107.

    > INSERT INTO IT107/COURSE_MASTER (COURSE_ID, COURSE_NAME,

    COURSE_CREDITS, COURSE_HOURS) VALUES('IT-107',

    'Intro to Minicomputer Ops', 3.0, 4.0)

    1 rows inserted in COURS00001 in IT107.

    > INSERT INTO IT107/COURSE_MASTER (COURSE_ID, COURSE_NAME,

    COURSE_CREDITS, COURSE_HOURS) VALUES('MA-031', 'Algebra 1', 3.0,

    4.0)

    1 rows inserted in COURS00001 in IT107.

    > INSERT INTO IT107/COURSE_MASTER (COURSE_ID, COURSE_NAME,

    COURSE_CREDITS, COURSE_HOURS) VALUES('AC-101',

    'Accounting Fundimentals', 3.0, 4.0)

    1 rows inserted in COURS00001 in IT107.

    > SELECT COURSE_NAME, COURSE_ID, COURSE_CREDITS FROM

    IT107/COURSE_MASTER ORDER BY COURSE_NAME

    SELECT statement run complete.

    > SELECT COURSE_NAME, COURSE_ID, COURSE_CREDITS FROM

    IT107/COURSE_MASTER WHERE COURSE_NAME = 'IT-107' ORDER BY

    COURSE_NAME

    SELECT statement run complete.

    > SELECT COURSE_NAME, COURSE_ID, COURSE_CREDITS FROM

    IT107/COURSE_MASTER WHERE COURSE_ID = 'IT-107' ORDER BY COURSE_NAME

    SELECT statement run complete.
     

  2. Chapter 17 - Using Data Definition Specifications to define Database Files
    1. Using DDS (Data Definition Specifications)
      1. Data definition language of the IBM IBM iSeries eServer (AS/400)
      2. Fixed format language
      3. Create externally defined files
        1. Data (Physical & Logical)
        2. Device (Display, Print Communications)
      4. Source is entered by Source Entry Utility (SEU)
      5. Standard file name is QDDSSRC
      6. Files are created by a "compile"
      7. Source member type determines the type of file which will be created
        1. PF - Physical File
        2. LF - Logical File
        3. DSPF - Display File
        4. PRTF - Print File
        5. CMNF - Communications File
    2. Structure of DDS
      1. Levels of Specifications
        1. File level
        2. Record Format level
          1. Physical files can have only ONE format
          2. Other types can have multiple formats
        3. Join level
          1. Logical files ONLY
          2. Specifies which physical files are joined
          3. Specifies which fields are used for the join
        4. Field level
          1. In a logical file, if you do not specify the fields, the file will contain ALL of the fields
        5. Key-field level
          1. Data files (physical & logical) only
          2. Keys are how you locate individual records in the file
        6. Select/Omit level
          1. Logical files ONLY
          2. Allows you to selectively select or omit records
      2. DDS Form
        1. KNOW each set of columns specified in the text p. 340-1
    3. Defining Physical Files
    4. Defining Logical Files
      1. "Simple"
      2. "Joined"
  3. Lab Exercise
    1. Build a "course master" file by copying & modifying the DDS source.
      1. Member SCMMSTP in file QDDSSRC in library JMYERSDATA contains the source for file SCMMSTP (the course master file)
      2. Using the copy function of the Program Development Manager, copy source member SCMMSTP to file QDDSSRC in YOUR library.
      3. Use the compile option to create file SCMMSTP.
      4. Use the DSPFD command to view the characteristics of the file in your library. Use the PrtScn (print screen) button in order and print the first page of the DSPFD display.
    2. Add data to your "course master" file
      1. Use the CPYF command to copy data from file SCMMSTP in library JMYERSDATA to the file in your library.
      2. Use the following parameters to the command:


        3. CPYF FROMFILE(JMYERSDATA/SCMMSTP) TOFILE(~~yourlib~~/SCMMSTP) MBROPT(*REPLACE)
         
         

      3. Use the command DSPPFM to display the data copied into YOUR file in YOUR library. Use the PrtScn (print screen) button to print this screen.
    3. Build a "student transcript" file by entering the DDS source (using the Source Entry Utility)
      1. Enter the following DDS Source into member SSTMSTP of file QDDSSRC in your library.


        2. A*

        A* SSTMSTP - STUDENT TRANSCRIPT MASTER RECORD

        A*

        A UNIQUE

        A R SSTFMT

        A*

        A STIDS 15 COLHDG('STUDENT' 'ID')

        A STYEAR 4S 0 COLHDG('COURSE' 'YEAR')

        A STSEM 10 COLHDG('COURSE' 'SEMESTER')

        A STIDC 15 COLHDG('COURSE' 'ID')

        A*

        A STGRAD 3 2 COLHDG('GRADE' 'EARNED')

        A*

        A STADCC 2S 0 COLHDG('H/Y' 'ADDED')

        A STADYY 2S 0 COLHDG('YEAR' 'ADDED')

        A STADMM 2S 0 COLHDG('MONTH' 'ADDED')

        A STADDD 2S 0 COLHDG('DAY' 'ADDED')

        A*

        A K STIDS

        A K STYEAR

        A K STSEM

        A K STIDC
         
         

      2. Use the compile option to create file SSTMSTP.
      3. Use the DSPFD command to view the characteristics of the file in your library. Use the PrtScn (print screen) button in order and print the first page of the DSPFD display.
    4. Staple the pages together and write your name on the printed output
    5. Hand in your work