DBSQLPR examines any input parameters (shown as input-parameter in the syntax diagram) that are passed through the OPTIONS file or the command line (the PARM= specification in the JCL). Any option can appear in either the OPTIONS file or the PARM= specification with the exception of OPTFILE=, which can only appear in the PARM= specification.
Note: In addition to the following, all plan options valid for the COBOL Preprocessor are also valid. See Specifying Processing Options in COBOL.
Determines the default authorization ID for non-qualified SQL names.
an authorization ID name of from 1 to 18 characters
SYSADM
DATASEPARATOR produces output in a form ready for import into spreadsheet software. The separator character you specify is placed after each data item returned from a SELECT statement.
The c specifies the data-separator character and is generally a comma but can be most non-blank characters that work for your data. We recommend that you do a test with your choice of separator character to determine whether it works as desired.
This option works only for data that can be represented in tabular format. The combination of SQUISH and a large PRTWIDTH specification (up to 1500 is allowed) can be used to force some non-tabular output to become tabular.
Specification of a blank is not allowed because of the way z/VSE handles execution parameters, but blank-delimited output is easily produced by either of the two following methods:
For data-only output (column-names, data types, and data only), specify NOECHO and NOPAGES with DATASEPARATOR=. If you want column-names and data types eliminated, add NOCOLHDR. NOTYPE can be used instead of NOCOLHDR if you do not want the data types but still want to see the column-names. If you need to eliminate unneeded spaces, add SQUISH. Any column containing a null-indicator still has a space or asterisk preceding the data-value.
a comma, or any non-blank character that works for your data,
or a B (for a blank space)
a blank space
Specifies that a certain SQLCODE, if encountered, aborts the execution of DBSQLPR.
Note: The in-line command *$ZERO zeros the jobstep return code.
any valid SQLCODE, for example, ERRABORT=-117
If not specified, this feature is inactive.
Specifies the minimum SQLCODE that does not abort DBSQLPR. That is, the format of the specification is ERRMIN=sqlcode, where sqlcode is the lowest numbered SQLCODE that does not cause DBSQLPR to abort. For example, if you wanted DBSQLPR to terminate on any negative SQLCODE, you would code ERRMIN=0. If you wanted even positive (warning) SQLCODEs to abort the program, you could code 9999.
Note: The in-line command *$ZERO zeros the jobstep return code.
-9999 through 9999
-9999
The -9999 default means that DBSQLPR does not terminate on any SQLCODE.
FORMFEED= changes the FORM-FEED character. Also see NOFORMFEED.
any decimal value between 1 and 255 that works correctly in your environment
12 decimal (z/OS) or 241 decimal (z/VSE)
Specify HEXCHAR to request hexadecimal output for all CHAR data. If you do not specify HEXCHAR, you get character output with binary zeros and new-lines blanked, and all other control characters printed.
Specify HEXGRAPHIC to request hexadecimal output for all GRAPHIC data. If you do not specify HEXGRAPHIC, you get character output.
Specify INFILE= to request an alternate DDNAME (in z/OS) or DTFNAME (in z/VSE) for the STDIN/SYSIN input.
Note: Specification of STDIN (the default) or any other file automatically opened by the C runtime environment causes a duplicate open error.
a valid alternate DDNAME (in z/OS) or DTFNAME (in z/VSE)
STDIN
Specifies a column beyond which the specified SYSIN lines are to be ignored. May be used to ignore line numbers or other unwanted information to the right of your intended input line but preceding any line-break.
50 thru the maximum your system supports, up to 99999
999
NOCOLHDR eliminates column headers from tabular output. Form-feeds and page headers still print, unless NOPAGES is also specified.
Specifying NOECHO indicates that only the data and any error summaries are printed. If you do not specify NOECHO, user input is echo-printed, that is, not only the data and error summaries are printed.
NOFORMFEED works the same as NOPAGES. Both suppress form-feeds and CA Datacom copyright page headers. Column-name headers for tabular output still occur exactly once at the top of the output, unless NOCOLHDR is also specified. NOPAGEHDR also suppresses CA Datacom copyright page headers but does not suppress form-feeds.
Specify NOPAGEHDR to suppress page headers. If you do not specify NOPAGEHDR, page headers are therefore not suppressed.
NOPAGES works the same as NOFORMFEED. Both suppress form-feeds and CA Datacom copyright page headers. Column-name headers for tabular output still occur exactly once at the top of the output, unless NOCOLHDR is also specified. NOPAGEHDR also suppresses CA Datacom copyright page headers but does not suppress form-feeds.
Specify NOTYPE to request that data types be omitted from the printed output. If you do not specify NOTYPE, data types are not omitted from the printed output.
Specifies an alternate DDNAME (in z/OS) or DTFNAME (in z/VSE) for the options file, but OPTFILE= itself can only be specified in the PARM= specification.
a valid DDNAME (in z/OS) or DTFNAME (in z/VSE)
OPTIONS
This parameter specifies the number of output lines per page. Use a high number if you do not like the page headers.
40 through 2147483647
the page length specification in the LINES= parameter in your DBSIDPR module, or 56 if DBSIDPR contains a number lower than LINES=40 (see the CA Datacom/DB Database and System Administration Guide for more information about DBSIDPR)
Specifies the name of the plan to create for this execution.
a valid plan name
DBSQLx, where x consists of selected portions of the system clock
Specifies the name of the plan to create for this execution. It is compatible syntax for PLANAME=.
a valid plan name
DBSQLx, where x consists of selected portions of the system clock
Use this parameter to specify tabular or column output. The default is WIDE (or W), to print wide (tabular output) when possible.
ROW (or R) gives tabular output but specifies that the width of the print is only as wide as the data, truncating the column name and data-type-descriptor-string to the length of the data, even if the data is only one byte long, which allows more data to fit onto each line.
THIN (or T) and COLUMN (or C) print one column value per line.
Note: These functionally equivalent in-line commands can be used: $WIDE (or *$W), *$ROW (or *$R), *$THIN (or *$T), *$COLUMN (or *$C). In-line commands allow the format to be changed on the fly.
WIDE or W, ROW or R, COLUMN or C, THIN or T
WIDE
This parameter specifies an alternate DDNAME (in z/OS) or DTFNAME (in z/VSE) for the STDOUT output.
a valid alternate DDNAME (in z/OS) or DTFNAME (in z/VSE)
STDOUT
Do not use this parameter unless directed to do so by CA Support.
Maximum row width for PRTFORMAT=ROW rows. Specifies where the line is to be split. That is, you can use PRTWIDTH= to define the width that can be printed before either data truncation or a forced switch from tabular (wide) to column-at-a-time (thin) output occurs. However, be aware that some types of output files wrap lines at the specification for LRECL=, while some output file types truncate. PRTWIDTH= should therefore be used to tell DBSQLPR when to force column-based output to occur, unless for some reason you want file type-dependent behavior to occur. Also be aware that, to prevent column values from spanning line boundaries, the line may be split earlier than specified by PRTWIDTH=.
80 through 1500
132 (tabular output, when possible, is the default)
This parameter truncates FETCH sequences that retrieve too many rows. That is, you can use ROWLIMIT= to truncate FETCH sequences that retrieve more rows than you want to retrieve.
Note: If the ROWLIMIT is exceeded, DBSQLPR returns a -2009 DBSQLPR error code and issues an error message. If you know a particular query may exceed the limit and still want a job-step return code of 0, place the *$ZERO line command following that query in your input file.
0 through 999999999
1000
SQUISH removes unneeded spaces from column headers and data that can then, if enough, be output in tabular format. Do not use SQUISH for non-tabular data. SQUISH can, when used with a large PRTWIDTH, enable non-tabular output to become tabular, but SQUISH does not eliminate spaces that have been generated to represent null indicators.
SQUISH can cause column data output to vary in length. If this creates a problem for you, try using PRTFORMAT=R (ROW) or line command *$R (*$ROW) as an alternative method to reduce column widths while preserving the tabular appearance of some output that is useful for certain features of spreadsheet packages.
Specifies how frequently to repeat the header lines that precede table-format output. Specify this parameter only if you do not want report-headers at the top of each page of a query's output, but prefer instead to see them at longer or shorter intervals. If the number you specify matches your PAGELEN= specification or default, adjustments are made to help ensure that table-headers appear at the top of each new page, even if NOPAGEHDR has been specified. Otherwise, this is the number of rows printed before subsequent table-headers appear.
Note: You can use the *$PAGE line command to help ensure that your output starts at the top of a page. Specifying the *$PAGE line command before a query helps ensure a full page of output before a page-break and a new set of table-headers appears.
40 through 2147483647
This range of valid values is the same as the valid values for PAGELEN=.
Produces one set of headers on each new page (even if NOPAGEHDR is specified, as previously described).
Specifies a character to terminate SQL statements.
The terminating character is changed to a semicolon (;) in the SQL statement that is passed to the DBMS and therefore, regardless of what terminating character you specify with the TERM= parameter, appears in DBSQLPR output reports as a semicolon.
SQL statements that appear inside the compound statements of SQL Procedures are terminated by semicolons, but semicolons are also used as the default termination character in DBSQLPR for complete SQL statements. The DBSQLPR parameter TERM= can be used, however, to prevent DBSQLPR from truncating compound statements in a CREATE PROCEDURE statement at the first semicolon. We therefore recommend that you add TERM=@ (specifying an @ symbol) to your DBSQLPR command line or options-file options. Then, although semicolons are still used inside the compound statements embedded in your CREATE PROCEDURE statement, at the end of each complete SQL statement, including the CREATE PROCEDURE statement itself, the @ symbol can be used as the termination character instead of a semicolon to avoid this ambiguity.
May be any character that is not alphanumeric and not valid as part of an SQL statement. Valid SQL-statement characters include, but are not limited to SQL-identifier characters, parentheses, dollar signs, percent signs, underscores, commas, quotes, apostrophes, asterisks, pound signs, and arithmetic and bitwise operators. One example of a valid terminating character is the at sign (@).
a semicolon (;)
The inclusion of this keyword causes all traces internal to DBSQLPR to be printed.
Important! Use this option only when CA Support tells you to use it and only as CA Support instructs you to use it.
The inclusion of this keyword causes certain traces internal to DBSQLPR to be printed. Traces specifically related to calls to CA Datacom are printed.
Important! Use this option only when CA Support tells you to use it and only as CA Support instructs you to use it.
The inclusion of this keyword causes certain traces internal to DBSQLPR to be printed. Traces specifically related to calls to CA Datacom are printed.
Important! Use this option only when CA Support tells you to use it and only as CA Support instructs you to use it.
|
Copyright © 2014 CA.
All rights reserved.
|
|