Printing Table Data
Enter option 1 from the Print menu to access the Print Options screen.
Print Options Screen
The Print Options screen (see the following figure) is used to specify the Db2 table and the data selection criteria. You also indicate whether the table data is to be printed in row or table mode.
Print Options screen
Specify DB2 Table Area
Creator
ID of the person who created the table or view you plan to print. Wild card characters (*, _, %) are valid. See Wildcard-Characters for more information.
If you enter a wild card character in this field, PRINTP Table List Window appears.
Table Name
Name of the object you want to edit. Tables, aliases, synonyms and views are valid. Wild card characters (*, _, %) are also valid. See Wildcard-Characters for details.
If you enter a wild card character in this field, the PRINTP Table List window (PRINTP Table List Window) appears.
Optional Area
Database
Name of the database to which you want to confine your selection list.
Tablespace
Name of the tablespace to which you want to confine your selection list.
Specify Selection Criteria Information Area
Selection Criteria Usage
Specify whether you want to use a previously saved selection criteria dataset. Selection criteria consists of conditions to limit and order column selection and/or row selection based on column values.
Specify the letter that represents the activity you want to perform:
T | Create Temporary selection criteria. The Criteria Display Format field determines the format used for the criteria. |
M | View or modify existing saved selection criteria. The criteria contained in the selection criteria dataset is displayed in the format specified by the Criteria Display Format field. |
E | Execute existing saved selection criteria. The criteria is not displayed. |
N | Do not apply selection criteria. All rows for the object are printed. |
Criteria Display Format
Specify whether selection criteria is to be entered on the Template, or as native SQL. This field only applies when the Selection Criteria Usage field is T (Temporary), or M (Modify).
Specify the letter that represents the activity you want to perform:
T | Selection criteria is processed using the Template. If the SQL is unable to be parsed for display on the Template, then a pop-up window displays stating that the SQL cannot be parsed and asking you if you want to view the native SQL. |
S | Selection criteria is processed in Edit using native SQL. |
Criteria Dataset Name
If you specify M (Modify) or E (Existing) Selection Criteria Usage, specify an existing sequential or partitioned dataset with RECFM=FB and LRECL=80. File-AID for Db2 prefills this field with the name of the last specified selection criteria dataset.
If you specify T (Temporary) Selection Criteria usage, a temporary data set (hlq.userid.SYSyyddd.Thhmmss.SHOW.SQL) containing the SQL source is created. An ISPF edit session is initiated, enabling you to edit the SQL. If you want to save the SQL source, you must use the ISPF CREATE or COPY command to a permanent data set before exiting the edit session. The temporary data set will be deleted when returning to the Print Options Screen.
Member
Specify the member of the data set you want to use. The field is prefilled with the last specified member name. If you leave this field blank or specify an asterisk (*), File-AID for Db2 displays the Member List for the data set. Specify a pattern, for example PART*, to narrow down the member list to the matching member names. The asterisk (*) is the only valid pattern character.
Specify Print Options Area
Print Mode:
Enter the desired presentation mode for printing. This field is updated with the last mode in use.
1 | Presents multiple rows per screen or page. Column names are listed across the top of the screen. This is called table mode. |
2 | Presents one row per screen or page. Column names are listed down the left side of the screen or page and column data is listed down the right side of the screen or page. This is called row mode. |
Print HEX in ROW Mode:
Enter the desired hexadecimal presentation mode for Row mode data printing. This option is ignored if Print Mode is Table.
1 | Never print hexadecimal lines. |
2 | Always print hexadecimal data values right below the character data line. |
3 | Default. Print hexadecimal data values right below the character data line, if the column data contains non-displayable characters. |
Maximum Rows to Select
Specify a numeric value indicating the maximum number of rows to be selected, or specify an asterisk (*) to select all rows. The default value is retrieved from User Parms (Option 0). See Browse-Edit-SQL-Analysis-Display-Options for information on changing the default. See Maximum Rows to Select for more information on this field.
SELECT FROM Temporal Table
Enter Yes if you want to specify or modify a time-period and date/timestamps for temporal tables. File-AID will display the SELECT FROM Temporal Table screen when you press Enter. Starting with Db2 Version 11, temporal support allows period specifications and period clauses to be specified for views. The same rules apply for views as do for tables.
Enter No (default) if you don’t want to display the SELECT FROM Temporal Table screen.
The status *Defined* displays if a SELECT FROM Temporal Table statement has already been defined, otherwise *None* displays.
Selection Criteria Member List
The Selection Criteria Member List appears if you left the Selection Criteria Member field blank (or entered a *) on the Edit Options screen. It provides a list of all members of the specified Selection Criteria dataset.
Print Selection Criteria Member List
Sel
Enter the S line command to select one of the listed members as your selection criteria.
Member Name
Lists all member names of the selection criteria dataset.
PRINTP Table List
The PRINTP Table List window appears when you enter a wild card character in the Creator and/or Table Name field on the Print Options screen. It provides a list of tables or views to be selected for printing.
PRINTP Table List Window
Cmd
Enter S to select the table or view.
Creator
Lists creator IDs of the tables to be selected.
Table
Lists the tables to be selected.
Type
Object type for each object listed.
PRINTP Selection Template
The PRINTP Selection Template screen appears after you complete the Print Options screen. On this screen you can specify the rows or columns of the object you want to print, the order in which the columns are printed, and the rows you want to use and the order in which they are printed.
If you are using an existing Edit/Print Criteria Dataset the SEL column is already prefilled with the selections from the Edit/Print Criteria Dataset. You can then either accept or modify them as desired.
Print Selection Template
Table Name
Creator ID and name of the object you are editing.
Max Rows to Select
Maximum number of rows to be returned based on your selection criteria. See Maximum Rows to Select for details.
SEL
Type S (select) beside the column or columns you want to edit. If you want the columns displayed in a particular order, type the appropriate integer beside each column. Mixing integers and “S” selections results in the integer-selected columns being displayed first, with the S-selected columns following. If you do not indicate any specific columns for selection, all columns are selected and presented in the order listed.
Key/Index Indicator
(Untitled 1 byte field) Indicates P Primary key, F Foreign Key, or I High Order Index key (if not Primary or Foreign) for the column as an aid for users to create SQL that will perform well.
Column Name
Names of all columns within the table or view.
Type(Length)
Type and length of data in the column. If the column type is Unicode, File-AID displays one of these Unicode format indicators
- UTF-8
- UTF-16
- UTF-8S(CCSID=367,ASCII)
Distinct types are identified by their names.
Order
Specify an integer to define how the returned rows should be sorted on the screen, based on the data content of the columns. Entering 1 indicates the primary sort column.
A/D
Specify whether the rows on this column data are sorted in ascending (A) or descending (D) order. If neither is specified, the columns are sorted in ascending order.
Where
Specify values limiting the rows displayed. The WHERE delimiter is analogous to the SQL WHERE clause in a SELECT statement. The WHERE field can be scrolled left and right. To scroll the WHERE field to the right, place the cursor in the WHERE field and press PF11. To scroll to the left, place the cursor in the WHERE field and press PF10. Scrolling allows you to view up to 256 characters.
You may use the following delimiters in your WHERE clauses:
> | OR | < > |
< | > = | BETWEEN |
= | <= | IN |
¬ = | ¬ > | LIKE |
AND | ¬ < | NOT |
> | > = | < > |
< | < = | ¬ < |
= | ¬ = | ¬ > |
For all other WHERE clauses, you must enter the CAST function manually.
Executing or Saving Template, or Viewing/Editing SQL
After the appropriate selection criteria has been provided, you can execute or save the Template Selection Criteria, or view/edit the SQL. You can also view an alternate display of the template.
Use EXEC to Continue
Type EXECUTE on the command line and press Enter, or press the associated PF key to process the request. The Edit Table screen displays.
SQL to View/Edit SQL Source
Type SQL on the command line, and press Enter. File-AID for Db2 generates all SQL for the current function and places the SQL statements into a data set.
If you specify M-Modify or E-Existing Selection Criteria Usage, this data set must be an existing sequential or partitioned data set with RECFM=FB and LRECL=80. The data set name is specified on the options panel in the Selection Criteria Dataset Name field. View/edit the data set, and then press END (PF3) to save any changes and exit the window.
If you specify T-Temporary Selection Criteria usage, a temporary data set containing the SQL source is created. An ISPF edit session is initiated, enabling you to view/edit the SQL. If you want to save the SQL source, you must use the ISPF CREATE or COPY command before exiting the edit session.
SAVE
Type SAVE at the command line, and press Enter. The Selection Criteria Data set Specification window (see the following figure) displays. This allows you to save the current Template Selection Criteria to a data set.
Selection Criteria Dataset Specification for Print
Dataset
Specify the name of the data set you want the selection criteria to be saved to. File-AID for Db2 prefills this field with the name of the last specified selection criteria dataset name.
Member
Specify the member of the data set you want to save the selection criteria to.
Press END if you do not want to save the current selection criteria to a data set. You are returned to the PRINTP Selection Template.
SIZE
The SIZE command provides an alternate display of the PRINTP Selection Template which allows 58 positions of the WHERE clause to be displayed, and does not include all of the lines on the original display. The alternate display is designed to maximize the amount of rows displayed on the panel, so that you have a larger area to build a WHERE clause.
Type SIZE at the command line and press Enter. A panel is displayed (see the following figure) with only the Column name and WHERE columns displayed. Enter SIZE again to return to the original display of the PRINTP Selection Template.
Alternate Display of PRINTP Selection Template
Selection Template Not Prefilled
The Selection Template screen cannot be prefilled when there is a mismatch between the table in the Use Existing Criteria and the one specified in the Table field of the Print Options screen, or the specified criteria does not contain a valid SELECT statement.
Print Selection Template Not Prefilled
Now you have several choices:
- Execute the SQL of the existing Selection Criteria Dataset. If it contains a valid SELECT statement you can then print the table specified in the selection criteria.
- Enter the SQL command to view and edit the SQL of the existing Selection Criteria Dataset before executing it.
- Return to the Print Options screen and select a different creator and table combination.
- Return to the Print Options screen and specify NO in the Use Existing Criteria field.
- Return to the Print Options screen and specify a different Edit/Print Criteria dataset member combination.
SQL Entry Panel
The SQL Entry Panel (see the following figure) displays after you have return from being in SQL mode. The panel prompts you for the name of a input criteria dataset and a member name. The data set can have a previously saved SELECT statement or it can be empty, allowing you to enter a new SELECT statement. If the data set contains more than one SELECT statement, only the first statement is executed. The following figure shows a sample SQL Entry Panel.
SQL Entry Panel After SQL Mode
Specify Input Dataset Name Area
Criteria Dataset Name
Name of the data set in which the SQL query resides or will reside.
Member
Specify the desired member.
Specify Processing Options Area
Maximum Rows to Select
Maximum number of rows to be returned for the SELECT. (An asterisk in this field indicates that all rows should be returned. See Maximum Rows to Select for more information.) Only the first SELECT command in a data set is executed.
Edit SQL
Specify whether you want to edit the SQL before it is executed.
If the value of the Edit SQL field is YES, pressing Enter displays the Edit SQL Selection screen (Edit SQL Selection Screen). If the value of the Edit SQL field is NO, pressing Enter executes the SQL statement and displays the Print mode screen.
Edit SQL Selection Screen
You access the Edit SQL Selection screen from one of the following:
- SQL Entry Panel when you enter YES in the Edit SQL field on the SQL Entry Panel.
- Prefilled EDIT Selection Template screen when you enter the SQL command. In that case the SQL is saved in the default SQL data set.
On this screen, you can enter a new SELECT statement or alter the existing one. The statement should be in standard SPUFI format.
Edit SQL Selection Screen
Text Entry Area
Change or add to the SELECT statement using ISPF/PDF Edit. For more information concerning SPUFI, see the IBM SQL Reference Guide.
When you are satisfied with your SQL, press END to end the edit session.
- If you entered from the SQL Entry panel you return to the SQL Entry panel and are advised to press Enter to execute the SQL statement (SQL Entry Panel to Execute SQL Statement).
- If you entered from the PRINTP Selection Template screen and modified the displayed SQL, you are asked Execute modified SQL?. YES executes the SQL statement and you enter Edit or Browse; NO returns to the Selection Template screen.
SQL Entry Panel to Execute SQL Statement
Table Print Output Specification Screen
The Table Print Output Specification screen (see the following figure) appears when you complete the PRINTP Selection Template screen. You need to identify where File-AID for Db2 directs the print output, either to a printer or a data set. After you have specified the output destination, press ENTER.
File-AID for Db2 generates a temporary JCL data set which is presented on an Edit screen. Modify it as necessary, then submit the job to process the print request.
Table Print Output Specification
TO PRINTER Area
Enter the S command when you want the print output to go directly to a printing device.
Printer Destination
Use the LOCAL Printer ID or JES queue.
Sysout Class
Enter a valid sysout class.
TO DATASET Area
Enter the S command when you want the print output to be stored in a data set.
Report Dataset Name
Enter the name of the designated output data set. File-AID for Db2 will append the output to the specified data set.
Volume Serial
Enter the name of the disk pack on which the data set is to reside.
Print Reports
Example Row Mode Table Data Print to Data set represents a Sample Row Mode Report.
Example Table Mode Table Data Print to Database represents a Sample Table Mode Report.
An asterisk (*) in front of the column value of a LOB or XML column indicates that the printed LOB column value has been truncated (LOB data exceeds retrieved data). The LOB options user parameter (Maximum size LOB data retrieved) determines the maximum size LOB/XML data to be retrieved.
Example Row Mode Table Data Print to Dataset
Example Table Mode Table Data Print to Database
Related topics