Creating a new SQL query and a form

Decision Support for z/OS, Version 1.8.1

Creating a new SQL query and a form

When you press F5, Query/Fm in the Report Definition pop-up, the SQL Query pop-up (Figure 45) is shown.

Figure 45. SQL Query pop-up, creating a new report

SQL Query               NEWREPORT                                 LINE 1

 -- NEWREPORT
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 1=Help        2=Run        3=End       4=Print      5=Chart     6=Draw
 7=Backward    8=Forward    9=Form     10=Insert    11=Delete   12=Report
Command ===> ___________________________________________________________

An SQL query is used to find information in tables to display it in a report. The SQL language uses normal English words to do this. Some commonly used clauses in a query are:

SELECT: You select columns in the order you want them to be displayed in the report (for example, user ID, system ID).
FROM: You select the columns from one or more tables.
WHERE: You select the rows (in the columns) for which a certain condition is true.
ORDER BY: You can order the rows in the columns in a certain order. For example, you can order the rows of a column in alphabetical order by name.
SORT BY: You can sort the rows in the columns in ascending or descending order.
GROUP BY: You can group the rows in the columns.

For more information about SQL, refer to DATABASE 2 SQL Learner’s Guide or DATABASE 2 SQL Reference

Write the query in the SQL Query pop-up.

If you want to delete the entire query and start again, type CLEAR on the command line on the SQL Query pop-up.

If you know the names of the table and columns you want to use, you can start typing the query. Otherwise, Tivoli Decision Support for z/OS helps you find out which tables and columns to use.

To see a list of tables available on your system, press F5.

Tivoli Decision Support for z/OS displays the Tables pop-up (Figure 46).

Figure 46. Tivoli Decision Support for z/OS Tables pop-up

                                   Tables           ROW 229 TO 242 OF 277

 Select table(s).  Then press Enter to see the column definitions.

 /   Tables              Prefix    Type
 _   SAMPLE_H            DRL       TABLE
 /   SAMPLE_M            DRL       TABLE
 _   SAMPLE_USER         DRL       TABLE
 _   SCHEDULE            DRLSYS    TABLE
 _   SPECIAL_DAY         DRLSYS    TABLE
 _   USER_GROUP          DRL       TABLE
 _   VMACCT_SESSION_D    DRL       TABLE
 _   VMACCT_SESSION_M    DRL       TABLE
 _   VMPARM              DRL       TABLE
 _   VMPRF_CONFIG_T      DRL       TABLE
 _   VMPRF_DASD_D        DRL       TABLE
 _   VMPRF_DASD_H        DRL       TABLE
 _   VMPRF_DASD_M        DRL       TABLE
 _   VMPRF_PROCESSOR_D   DRL       TABLE

 Command ===> ___________________________________________________________
  F1=Help     F2=Split    F7=Bkwd     F8=Fwd      F9=Swap    F12=Cancel

If you want to see information about the table, you can type a question mark (?) beside the table, and press Enter. Tivoli Decision Support for z/OS goes into Bookmarker and displays information about the table from the relevant online book. Press F3 to return to the table list.

Select one or more tables, and press Enter.

Example:

For this example, select the SAMPLE_M table. To locate it, type lo samp on the command line and press Enter.

Tivoli Decision Support for z/OS displays the Columns of Selected Tables pop-up. If you need information about a column, you can type a question mark (?) beside the column, and press Enter. Tivoli Decision Support for z/OS goes into Bookmanager and displays information about the column and table from the relevant online book. Press F3 to return to the column list (Figure 47).

Figure 47. Tivoli Decision Support for z/OS Columns of Selected Tables pop-up

                         Columns of selected Tables          ROW 1 TO 8 O

 Select columns to generate a Query. Then press Enter to return.

 /   Column              Type        Len   Key  Table
 /   DATE                DATE        4     Yes  SAMPLE_M
 _   SYSTEM_ID           CHAR        4     Yes  SAMPLE_M
 /   DEPARTMENT_NAME     CHAR        8     Yes  SAMPLE_M
 /   USER_ID             CHAR        8     Yes  SAMPLE_M
 /   TRANSACTIONS        INTEGER     4     No   SAMPLE_M
 _   RESPONSE_SECONDS    INTEGER     4     No   SAMPLE_M
 _   CPU_SECONDS         FLOAT       8     No   SAMPLE_M
 /   PAGES_PRINTED       INTEGER     4     No   SAMPLE_M
 ***************************** BOTTOM OF DATA ***************************




 Command ===> ___________________________________________________________
  F1=Help     F2=Split    F7=Bkwd     F8=Fwd      F9=Swap    F12=Cancel

Select the columns you want to use, and press Enter.

Example:

For this example, select the DATE, DEPARTMENT_NAME, USER_ID, TRANSACTIONS, and PAGES_PRINTED columns, and press Enter. (A confirmation pop-up might be displayed, asking you to confirm that you want to replace the existing query definition. Press Enter.)

The table and column names are copied to your SQL Query pop-up, and SQL SELECT and FROM clauses are added (see Figure 47).

Example:

In our example, DRL is the table prefix; it might be something else in your installation. The prefix is shown in the Prefix for all other tables field on the Dialog Parameters window.

Figure 48. SQL Query pop-up, table and columns selected

SQL Query               NEWREPORT                                 LINE 1

 -- NEWREPORT

  SELECT DATE, DEPARTMENT_NAME, USER_ID, TRANSACTIONS, PAGES_PRINTED
    FROM DRL.SAMPLE_M
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 _______________________________________________________________________
 1=Help        2=Run        3=End       4=Print      5=Chart     6=Draw
 7=Backward    8=Forward    9=Form     10=Insert    11=Delete   12=Report
Command ===> ___________________________________________________________

Note:

If you know the name of the table you want to use, but are unsure about the column names, you can enter the name of the table in your SELECT statement, and press F6 to see a list of the columns included in that table.

If you have selected a table from the Tables pop-up and want to use another table, you must go back to the Tables pop-up and:

Select a new table, or
Press F12 (Cancel) from the Tables pop-up without selecting a table, and enter a new table name in the query

Edit the query, to add more clauses.
Example:

In our example, we want to show the sum of transactions and pages printed for each user. Press F10 to go into Edit mode, and change the query so that it looks like this:
```
SELECT DATE, DEPARTMENT_NAME, USER_ID,
       SUM(TRANSACTIONS),
       SUM(PAGES_PRINTED)
 FROM DRL.SAMPLE_M
```
Press F3 to leave Edit mode.
You can specify conditions for the rows to use with a WHERE condition. You can use variables in this condition to make the query more flexible. Variables are preceded by an ampersand (&), and can be 18 characters, including the ampersand. When the report is run, the Data Selection pop-up is displayed, and you can specify values for the variables.
Example

In our example, no WHERE condition is needed. An example of a query with a WHERE condition is shown in Figure 40.
Select the order in which the information should be shown with an ORDER BY condition, or specify a GROUP BY condition.
Example:

In our example, we must group the date, department name, and user ID columns. We also order the date (column 1), department name (column 2), and user ID (column 3) columns in ascending order. Add these clauses to the query:
```
GROUP BY DATE, DEPARTMENT_NAME, USER_ID
ORDER BY 1 ASC, 2 ASC, 3 ASC
```

Press F4 to run the query to verify that the report contains the information you want.

Notes:

If the query contains variables, you must replace those variables with values before you can run the query.
If you get an SQL error message when you try to run the query, there is probably something wrong with the query’s syntax. A column name might be misspelled, or a comma might be missing.

Example:

The report you see after pressing F4 can look like the example in Figure 49. (Only the first page of the report is shown here. In the dialog you can page forward with F8.)

Figure 49. An intermediate report shown with F4 (Run)

 DATE       DEPARTMENT USER
            NAME       ID
 ---------- ---------- -------- ----------- -----------
 2000-01-01 Appl Dev   ADAMS           1109         821
 2000-01-01 Appl Dev   JONES           1138        1055
 2000-01-01 Appl Dev   SMITH            870         864
 2000-01-01 Finance    GEYER            509         529
 2000-01-01 Finance    HAAS             786         648
 2000-01-01 Finance    PARKER           462         704
 2000-01-01 Finance    SPENCER          800         640
 2000-01-01 Manufact   LEE             1197        1086
 2000-01-01 Manufact   LUTZ             606         623
 2000-01-01 Manufact   MEHTA            968         748
 2000-01-01 Manufact   PULASKI          716         738
 2000-01-01 Marketng   KWAN             637         577
 2000-01-01 Marketng   STERN            474         792
 2000-01-01 Retail     GOUNOT           798         790
 2000-01-01 Retail     MARINO           653         685
 2000-01-01 Retail     PEREZ            716        1060
 2000-01-01 Sys Supp   PIANKA           770        1210
 2000-01-01 Sys Supp   THOMPSON         509         395
 2000-02-01 Appl Dev   ADAMS            422         650
 2000-02-01 Appl Dev   JONES            893         826
 2000-02-01 Appl Dev   SMITH            842         842
 2000-02-01 Finance    GEYER            379         515
 2000-02-01 Finance    HAAS             674         771
 
·
·
·

Tivoli Decision Support for z/OS displays the results of the query. Press F3 to go back to the SQL Query pop-up. Modify the query if necessary, and press F4 again to check the results.

You can add comments to the query, for example to explain your modifications. Start each comment line with two dashes (– –). Comments are not shown in the report.

When you are satisfied with the contents of the query, you can change the layout of the report. Press F11 to go to the Form for Report report name pop-up.

Example:

The Form for Report NEWREPORT pop-up is shown. (For a new report, you might need to press F5 to build the form from the query.) The resulting form can look like the example in Figure 50.

Figure 50. A new report form

 FORM.MAIN                NEWREPORT

 COLUMNS:                    Total Width of Report Columns: 52
  NUM COLUMN HEADING                               USAGE   INDENT  WIDTH EDIT SEQ
  --- --------------                               ------- ------  ----- ---- ---
    1 DATE                                                  0       10    C    1
    2 DEPARTMENT_NAME                                       1       8     C    2
    3 USER_ID                                               1       8     C    3
    4                                                       1       11    L0   4
    5                                                       1       11    L0   5

 PAGE:    HEADING  ===>  &REPORT_TITLE
          FOOTING  ===>  &PRODUCT_NAME: &REPORT_ID
 FINAL:   TEXT     ===>
 BREAK1:  NEW PAGE FOR BREAK?  ===>  NO
          FOOTING  ===>
 BREAK2:  NEW PAGE FOR BREAK?  ===>  NO
          FOOTING  ===>
 OPTIONS: OUTLINE? ===>  YES                DEFAULT BREAK TEXT?  ===>  NO

 1=Help        2=Check       3=Check        4=Show        5=Chart        6=Query
 7=Backward    8=Forward     9=            10=Insert     11=Delete      12=Report
 OK, FORM is displayed.
 COMMAND  ===> _                                                SCROLL  ===>  PAGE

Example:

First of all, you must add column headings for columns four and five. Move the cursor to the empty line after column three, and type Transactions. Type Pages_printed on line five. (The underscore (_) makes printed Pages appear on two lines.)
If you run the report using F4, you will notice that some headings are uppercase and some are lowercase. Also, some headings are truncated because they are too long. To solve this, you can either add an underscore to break the heading into two lines, or increase the width of the column.
- Change the width of the transactions column to 12 instead of 11.
- Change the heading DEPARTMENT_NAME to Depart-_ment, and press F4 again.
- Change other headings from uppercase to lowercase if needed.

Break the report into sections to make the information more readable. You can also summarize data for each section. You do this with usage codes. (For a full description of the usage codes available, see Usage codes.) Fill in the usage code columns as shown in the example in Figure 51.

Figure 51. A modified report form

 FORM.MAIN                NEWREPORT

 COLUMNS:                    Total Width of Report Columns: 52
  NUM COLUMN HEADING                               USAGE   INDENT  WIDTH EDIT SEQ
  --- --------------                               ------- ------  ----- ---- ---
    1 Date                                         BREAK1   0       10    C    1
    2 Department                                   BREAK2   1       8     C    2
    3 User_ID                                               1       8     C    3
    4 Transactions                                 SUM      1       12    L0   4
    5 Pages_printed                                SUM      1       11    L0   5

 PAGE:    HEADING  ===>  &REPORT_TITLE
          FOOTING  ===>  &PRODUCT_NAME: &REPORT_ID
 FINAL:   TEXT     ===>
 BREAK1:  NEW PAGE FOR BREAK?  ===>  NO
          FOOTING  ===>
 BREAK2:  NEW PAGE FOR BREAK?  ===>  NO
          FOOTING  ===>
 OPTIONS: OUTLINE? ===>  YES                DEFAULT BREAK TEXT?  ===>  NO

 1=Help        2=Check       3=Check        4=Show        5=Chart        6=Query
 7=Backward    8=Forward     9=            10=Insert     11=Delete      12=Report
 OK, FORM is displayed.
 COMMAND  ===> _                                                SCROLL  ===>  PAGE

Example:

With these usage codes, the date and department names are shown only once instead of being repeated. The transactions and pages printed columns are summarized for each department and time period.

When you have finished working with the form, press Enter to save it. Then press Enter to save the query.
On the Report Definition pop-up, you can press F10 to add header and footer lines to your report. For more information about adding header and footer lines, see Adding page headers and footers to a report.

When you have finished working with the report, press Enter to save the report definition. Your report is included in the list of reports, and can be selected and displayed.

Feedback

[ Top of Page | Previous Page | Next Page | Contents ]