Querying Joined Tables

carcudb2
2
2
You can use Join/Edit to set up a query against several joined tables without the need for detailed knowledge of SQL. Queries can be made immediately or saved as DB2 views.
When you create, alter, or template a join/edit, this does not necessary mean that you are editing the tables. It can also mean that you are browsing the tables.
Access Join/Edit Function
You can access Join/Edit as described in this section.
To access Join/Edit, specify 
J
 in the Object field of the Main Menu, specify 
C
A
, or 
in the Option field, and then press Enter.
Depending on the specified option, the Join Create, Join Alter, or Join Template screen appears.
Join/Edit Create
You can create a join as described in this section.
Follow these steps:
  1. From the main menu, perform the following actions and press Enter:
    1. Type 
      J
       in the Object field.
    2. Type 
      C
       In the Option field.
      You can also specify
      A
      to alter or
      T
      to template.
    The Join Create screen appears.
    The status of the CAPS and NULLS commands is shown in the first line of this screen. The header fields (Option, Object, Mode, Item Name, and Creator) determine the execution mode, the object type, option, and the selection criteria for selecting the objects. The Where header field is not functional for this object type.
  2. Select your tables. Enter the main table for the query in the Table field. 
    The first part of this field is the table creator and is initially copied from the Creator field in the header. You can enter the full name or use the percent sign (%) to display a selection list. For example, USERID2.J% will produce a list of all tables created by USERID2 starting with J. On the previous screen, USERID1.jemptab is specified.
  3. Set up the SELECT list by selecting the secondary tables that will be joined to the main table. As you select tables, their columns are displayed at the bottom of the screen.
    The table names are prefixed by a table correlation character: A for the main table, B for the second table, and so on.
  4. Specify a value in the DS field to indicate whether to display the column as follows:
    • Y
      Includes the column in the displayed data.
    • S
      Displays and sort the column.
    • D
      Displays and sort the column in descending sequence. The sort order follows the display order.
    Any other value will cause the column not to be displayed. Where more than one sort field is specified, the sort sequence will be the same as the sequence in which the columns are displayed.
    You can rearrange the columns with ISPF-like commands. The # field displays a sequence number for referencing display lines.
  5. Specify a list of columns to be selected from the table for display in the SELECT field (see DS field above) or as the 'left hand' side of a JOIN or WHERE clause. 
    You can enter values (preceded by the table correlation character and a period) directly in the SELECT, JOIN and WHERE fields (see following values), but it is easier to select them from the column lists. Fields that can be edited are indicated by underscore or hyphen characters. For information about the fields, press F1 (Help).
  6. Set up a join by placing the column for the 'right-hand side' of the JOIN clause in the JOIN list next to the column in the SELECT list, which corresponds to the 'left-hand side' of the JOIN (add the 'left-hand' column if necessary).
    Display this column by blanking out the DS field.Type a value for the join type in the Type field as follows:
    – Omits rows if the 'left-hand' column or the 'right-hand' column in the JOIN clause does not have a counterpart value.
     
    Includes rows without counterpart values.
     
    Includes rows where the right-hand column is null.
     
    Includes rows where the left-hand column is null.You can specify more than one pair of join columns, in which case they will be linked by 'AND'.
    A JOIN is always assumed to be on equality, that is ON A = B.
  7. Set up the WHERE clause, as you did for the JOIN clause, by placing the 'right-hand' column in the WHERE list in the row where the corresponding 'left-hand' column appears. 
    The first sub-field (2 characters wide) is where you place the comparison type. Valid values for DB2 are:
    = < > <> <= >= !< !> != ^< ^> ^= IN LK
    The program will only check that this field is not blank. Further validation is left to DB2. LK is accepted as an abbreviation for LIKE. You can also type literal values in the WHERE column. If more than one WHERE clause is specified, they will be assumed to be joined by AND, unless the operator OR is placed on an empty line between the two columns in the WHERE list.
  8. Enter one of the following values in the CMD line:
    • Enter 
      ALL 
      to select all columns from all tables displayed.
    • Enter 
      to SELECT an individual column and place it in the next available entry in the SELECT list.
    • Enter 
      S
      J
      , or 
      plus a row number to overwrite a displayed entry in the SELECT, JOIN or WHERE lists.
  9. Specify a value in the Save field as follows:
    • Enter 
      Y
       in the Save field to save the data as a view.
      To edit the data, a view must be created and saved. If any of the tables in the join do not have a primary key defined, you can only browse, not edit after a save.
    • Enter 
      N
       in the Save field to browse the data using SQL. 
      This is the default. It is not necessary to save the join unless you want to reuse it in the future.
  10. Specify 
    Y
     in the Check Option field (to add the DB2 WITH CHECK OPTION to a view).
  11. Press F3 (End) to create SQL ready to be processed by the Batch Processor.
    If you specified Y for Save, a CREATE VIEW syntax appears; otherwise, a direct SELECT appears. You can edit this SQL by entering ED or EDIT in the Command line.
  12. Press F3 to exit.
  13. Press Enter to process the SQL.
Avoiding Clutter from VIEW Objects
To prevent cluttering up the DB2 catalog with VIEW objects created once, use the parmlib join/edit creation of VIEW objects keyword. The keyword has the values:
  • N
    Accepts only SAVE=N on the Join Create panel. This setting provides total avoidance of clutter.
  • E
    Accepts SAVE=Y on the Join Create panel. This setting does not restrict clutter.
  • B
    Accepts SAVE=Y on the Join Create panel but only creates a permanent VIEW if the name of the VIEW is TEMPJVIEW. Clutter is limited to one VIEW per creator.
Column Selection
To select all columns in a table, specify an asterisk (*) next to the table name. The columns will be added into blank SELECT rows, skipping over any non-blank entries. If two or more tables contain the same column name, do not select the second and succeeding column names. You will receive an sqlcode:
-612, columnname is a duplicate column name
To select an individual column, specify an S next to a column name to move it to the first blank SELECT row. You can also specify S
nn
 to overwrite a displayed row, where 
nn
 is the number of the row displayed under #. J
nn
 and W
nn
 can also be used to move column names to specific JOIN and WHERE rows. You do not have to delete trailing hyphens in the SELECT, JOIN, or WHERE fields as they will be removed automatically.
Example
An example of what might appear after column selection is shown on the following screen:
ROPJCRC -- (CAPS ON)  ---- Join Create --- (NULLS ON)  ---  COMMAND ===>                                                 SCROLL ===> PAGE Option     ==> C                  Object    ==> J        Mode  ==> O ONLINE Item Name  ==> J1                 Creator   ==> USERID1  Where ==> N SSID: D81B ------------------------------------------------------------ USERID1 View  ==> USERID1  >.                  > Save => N         Check Option ==> N  Table ==> USERID1  >.                   > CMD # DS SELECT.............. JOIN..............TYPE WHERE.................. ___ 01 Y A.LASTNAME           -------------------- I =  -------------------- ___ 02 D A.FIRSTNAME          -------------------- I =  -------------------- ___ 03 Y B.DEPTITLE           -------------------- I =  -------------------- ___ 04   A.DEPT               B.DEPT               I >  '0000'-------------- ___ 05 Y -------------------- -------------------- I =  -------------------- ___ 06 Y TIMESTAMP----------- --------------------   BT '2001-01-01-00.00.00 ___ 07 Y -------------------- --------------------      .000000'------------ ___ 08 Y -------------------- --------------------   &  '2001-12-31-11.59.59 ___ 09 Y -------------------- --------------------      .999999'------------ Top area Up/Down: PF7/8 ************************ Bottom area Up/Down: PF10/11 _ A.USERID1.JEMPTAB ___    1 LASTNAME           CHAR          ___    2 FIRSTNAME          CHAR ___    3 DEPT               CHAR          ___     _ B.USERID1.JDEPTTAB ___    4 DEPT               CHAR          ___    5 DEPTITLE           CHAR ___    6 TIMESTAMP          TIMESTMP ******************************** BOTTOM OF DATA ******************************
In this example:
  • You want to select A.LASTNAME, A.FIRSTNAME, and B.DEPTITLE for display, sorted in descending order of A.FIRSTNAME.
  • You want to JOIN table A (USERID1.JEMPTAB) to table B (USERID1.JDEPTTAB) on the fields A.DEPT and B.DEPT. This is an INNER JOIN, so null values will be excluded.
  • You want to include rows that have DEPT higher than '0000' and a TIMESTAMP between the two values specified (note the use of the continuation column for the timestamp literals here).
  • Row 05 will be ignored because it is empty (it has no SELECT column).
When you are satisfied with the data, you can run the SQL or save it as a DB2 view:
  • To generate a view, set the Save field to Y and if necessary, enter as the View Creator one of the user IDs to which you have access. SORT information cannot be saved in a view and will therefore be lost.
  • To generate the SQL, press F3.
Join/Edit Template
The template option allows creation of a new join using an existing join as a template. A template session is actually a create session with the additional step of selecting a join as a starting point. You must change the name of the templated join, but the other fields do not have to change.
Dependent objects are not included in the template operation.
Join/Edit Alter
The Alter Join option lets you change any characteristic of a join. Change fields as necessary, according to the procedure outlined in Join Create.
Join/Edit DDL Execution
When F3 (End) is pressed from the Create, Alter, Template or Drop screen, a confirmation screen appears before the DDL is executed. Accept, edit, or reject the DDL to be used to complete the request. 
If your changes are complex, require dropping and recreating objects (and their dependencies), or the current CA RC/Update Operation Mode is set to A (RC/Alter), you can perform a complete analysis of your changes that includes rebuilding the DDL and optionally including utility statements and extended DDL generation and analysis options.
Join/Edit Commands
The following commands can be used when processing join/edits:
  • ALL
    Selects all displayed columns.
  • CAPS
    Sets translation to uppercase or lowercase for fields for which it is optional. Specify 
    CAPS ON
     or 
    CAPS OFF
    .
  • COLS
    Controls the display of the column area at the bottom of the screen. Specify 
    COLS ON
     or 
    COLS OFF
    .
  • HEADER
    Controls the display of the header area at the top of the screen. Specify 
    in the command line to toggle the header.
  • NC/CN
    Inverts NULLS and CAPS settings.
  • NULLS
    Sets nulls on or off in displayed fields. Nulls On allows insertion of extra characters in fields.