Ca datacom db sql user guide

. It provides functionality without dependence on the SQL interface or any requirement to preprocess and compile an SQL program. DBSQLPR is for users who want quick access to SQL functionality but do not have access to CICS or the

DBSQLPR outputs message

to the JESMSGLG each time it runs. The message displays the date/time when the running DBSQLPR was compiled, the

release it is part of, and the latest PTF applied (if any). Consider the following guidelines: SQL statements must be terminated with a semicolon (;) unless the parameter is used (see DBSQLPR Options). SQL statements may not contain host variable references.

All nullable columns are printed with one extra character to the left. This character is blank when a value is present and an asterisk (*) when the data is NULL.

When using DBSQLPR, specify to default to that value. Processing The execution flow for DBSQLPR is as follows:

DBSQLPR examines any input parameters that are passed programmatically or through the command line (the PARM= specification in the JCL). See DBSQLPR Options.

The options file is opened and processed. Except for the parameter, which may appear on the input parameter string, any option can appear in the options file or the specification. Options that are specified on the input parameter string override any duplicate specification in the options file.

The same set of plan options you can specify in the COBOL Preprocessor can be specified in DBSQLPR. See Specifying Processing Options in COBOL.

The SYSIN file is read, and the input is processed as a series of commands (see Line Commands) and SQL statements.

When a print line is longer than , what happens depends on the options that are in effect: In this case, the width of each line is constant and the following message is printed:

UNABLE TO PRINT TABULAR FORMAT OUTPUT: PRTWIDTH SPEC.nnn EXCEEDED BY nnn BYTES

DATASEPARATOR is not used, the output switches to THIN mode to print one column per line. When DATASEPARATOR is used, no lines are printed.

In this case, the width of each line is variable. At the end of the report, the following message shows you the length of the longest line with the current data:

USE PRTWIDTH nnn TO PRINT nnn ROWS THAT ARE TOO LONG.

DATASEPARATOR is used, rows that are too long are not printed. DATASEPARATOR is not used, rows that are too long are printed. However, the output will be wrapped or truncated.

The CC of the job is set based on the messages output for the job, reflecting the highest severity message issued.

No messages issued: CC=0 WARNING level message(s) issued: CC=4 ERROR level message(s) issued: CC=8 Line Commands The following in-line commands are accepted by DBSQLPR in your input file: auth-id.plan-name You can submit a statement in Version 12 and above as an in-line command in DBSQLPR by using auth-id.plan-name is an optional authorization ID followed by a period, and the is the required name of the plan you want to drop). Best practice is to avoid using to drop plans related to procedures and functions. Use the DROP PROCEDURE statement and DROP FUNCTION statements to drop procedures. For more information about Two dashes at the start of any line causes the line to be interpreted as a comment and ignored.

An asterisk at the start of any line causes the line to be interpreted as a comment and ignored, unless that line contains one of the following line commands that begins with an asterisk:

*$COLUMN (or *$C) Specifies one data item per line of output (same as Generates a form-feed and page-header. Specifies tabular output with column headings that are truncated to width of data. Specifies one data item per line of output (same as Prints output in tabular form, reverting to if data exceeds specification. Zeroes the job-step return code. DBSQLPR Syntax Review the following syntax for DBSQLPR:

 ┌─ , ───────────────┐ ►►─ EXEC PGM=DBSQLPR ─ ,PARM=' ─▼─ input-parameter ─┴─ ' ───────────────►◄

In z/VSE environments, parameter separators in the input parameter string must be spaces. DBSQLPR Options 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 specification in the JCL). Any option can appear in either the OPTIONS file or the specification except for , which can only appear in the specification.

In addition to the following list, 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. Valid Entries: an authorization ID name of from 1 to 18 characters Default Value: DATASEPARATOR=

Produces output in a form ready for import into spreadsheet software. The separator character that you specify is placed after each data item returned from a

specifies the data-separator character and is generally a comma but can be most non-blank characters that work for your data. Best practice is to 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 can be used to force some non-tabular output to become tabular.

Since output is often directed to a file, and a line is truncated based on the file's LRECL record length, PRTWIDTH is set to LRECL. This truncation occurs even with print output.

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 following methods:

DATASEPARATOR= is interpreted as a blank. A space is automatically employed as the separator when the DATASEPARATOR=

option is not used. In this case, you could get two spaces in a row when null indicators indicate non-null, or you could get multiple spaces if SQUISH is not specified. Specifying SQUISH compresses the data and eliminates the solid line that appears under the column headers, because the columns are no longer fixed-length when using SQUISH.

For data-only output (column-names, data types, and data only), specify DATASEPARATOR= . If you want column-names and data types to be eliminated, add can be used instead of

if you do not want the data types but want to see the column-names. To eliminate unneeded spaces, add

. The column that is used to indicate that a null value does not appear when DATASEPARATOR

is used. Rows that are too long are not printed. At the end of the report, the following error message supplies the

that is wide enough for all rows, and the number of rows that are too long. The LRECL of the output file also must be set to this value.

USE PRTWIDTH nnnn TO PRINT nnnn rows that are too long. This PRTWIDTH assumes that SQUISH is used. Valid Entries: a comma, or any non-blank character that works for your data, or a B (for a blank space) Default Value: a blank space Specifies that a certain SQLCODE, if encountered, aborts the execution of DBSQLPR. The in-line command zeros the jobstep return code.

Specifies the minimum SQLCODE that does not abort DBSQLPR. That is, the format of the specification is

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

. If you wanted even positive (warning) SQLCODEs to abort the program, you could code 9999. The in-line command zeros the jobstep return code. Changes the FORM-FEED character. Also see NOFORMFEED Valid Entries: Any decimal value from 1 through 255 that works correctly in your environment Default Value: 12 decimal (z/OS) or 241 decimal (z/VSE) to request hexadecimal output for all CHAR data. If you do not specify

, you get character output with binary zeros and new-lines blanked, and all other control characters printed.

HEXGRAPHIC HEXGRAPHIC to request hexadecimal output for all GRAPHIC data. If you do not specify HEXGRAPHIC , you get character output. to request an alternate DDNAME (in z/OS) or DTFNAME (in z/VSE) for the STDIN/SYSIN input.

Specification of STDIN (the default) or any other file that is automatically opened by the C runtime environment causes a

duplicate open INPUTWIDTH=

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.

Valid Entries: 50 through the maximum your system supports, up to 99999 Default Value:

Specify the MSG plan option when rebinding plans. The x refers to precompile-time messages and y refers to messages generated by the Optimizer when the statement is executed. Use S to specify summary, D to specify detail, or N to specify none.

Valid Entries: SS, DD, SD, DS, NS, ND, DN, SN, or NN For more information, see SQL Query Optimization Messages.

Eliminates column headers from tabular output. Form-feeds and page headers still print, unless NOPAGES is also specified.

Indicates that only the data and any error summaries are printed. If you do not specify , user input is echo-printed; not only the data and error summaries are printed. NOFORMFEED Works the same as . Both suppress form-feeds and

is also specified. also suppresses copyright page headers but does not suppress form-feeds. to suppress page headers. If you do not specify , page headers are not suppressed. works the same as NOFORMFEED. Both suppress form-feeds and

is also specified. also suppresses copyright page headers but does not suppress form-feeds. to request that data types be omitted from the printed output. If you do not specify , data types are included in the printed output. Specifies an alternate DDNAME (in z/OS) or DTFNAME (in z/VSE) for the options file, but itself can only be specified in the specification. Valid Entries: A valid DDNAME (in z/OS) or DTFNAME (in z/VSE) Default Value:

This parameter specifies the number of output lines per page. Use a high number if you do not like the page headers.

Valid Entries: 40 through 2147483647 Default Value: the page length specification in the parameter in your DBSIDPR module, or 56 if DBSIDPR contains a number lower than Specifies the name of the plan to create for this execution. Valid Entries: a valid plan name Default Value: consists of selected portions of the system clock Specifies the name of the plan to create for this execution. It is compatible syntax for Valid Entries: a valid plan name Default Value: 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 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> This truncation allows more data to fit onto each line.

THIN (or T) and COLUMN (or C) print one column value per line.

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. This parameter specifies an alternate DDNAME (in z/OS) or DTFNAME (in z/VSE) for the STDOUT output. Valid Entries: a valid alternate DDNAME (in z/OS) or DTFNAME (in z/VSE) Default Value: Do not use this parameter unless directed to do so by Broadcom Support Maximum row width for rows. Specifies where the line is to be split. You can use

to define the width that can be printed before data truncation or a forced switch from tabular (wide) to column-at-a-time (thin) output occurs. However, some types of output files wrap lines at the specification for

, while some output file types truncate.

should therefore be used to tell DBSQLPR when to force column-based output to occur, unless you want file type-dependent behavior. Also, to prevent column values from spanning line boundaries, the line can be split earlier than specified by

Valid Entries: Logical record length Default Value: 132 (tabular output, when possible, is the default) This parameter truncates FETCH sequences that retrieve too many rows. That is, you can use to truncate FETCH sequences that retrieve more rows than you want to retrieve. Valid Entries:

The minimum value to specify is 0. The maximum value to specify is 999999999. If a smaller or larger value is specified, a warning message is output and the value is reset to the nearest allowable value.

Default Value:

is exceeded, DBSQLPR returns a -2009 DBSQLPR error code and issues an error message. If you know that a query may exceed the limit and still want a job-step return code of 0, place the

line command after that query in your input file.

removes unneeded spaces from column headers and data that can then, if enough, be output in tabular format. Do not use

for non-tabular data. can, when used with a large , enable non-tabular output to become tabular, but does not eliminate spaces that have been generated to represent null indicators. can cause column data output to vary in length. If this creates a problem, try using (ROW) or line command

) as an alternate 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 if you

want report-headers at the top of each page of a query's output, but prefer to see them at longer or shorter intervals. If the number you specify matches your

specification or default, adjustments are made to help ensure that table-headers appear at the top of each new page, even if

has been specified. Otherwise, this is the number of rows that are printed before subsequent table-headers appear.

You can use the line command to help ensure that your output starts at the top of a page. Specifying the

line command before a query helps ensure a full page of output before a page-break and a new set of table-headers appears.

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

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

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 (specifying an

symbol) to your DBSQLPR command line or options-file options. Then, although semicolons are still used inside the compound statements that are 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. Valid Entries:

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 bit-wise operators. One example of a valid terminating character is the

Default Value: a semicolon (;) The inclusion of this keyword causes all traces internal to DBSQLPR to be printed. Use this option only when Broadcom Support tells you to use it and only as Broadcom Support instructs you to use it. TRACEDETAIL

The inclusion of this keyword causes certain traces internal to DBSQLPR to be printed. Traces related to calls to

are printed. Use this option only when Broadcom Support tells you to use it and only as Broadcom Support instructs you to use it.

The inclusion of this keyword causes certain traces internal to DBSQLPR to be printed. Traces related to calls to

are printed. Use this option only when Broadcom Support tells you to use it and only as Broadcom Support instructs you to use it. DROP PLAN (DBSQLPR) (Executable only from DBSQLPR.)

A plan is the representation in SQL of an SQL application and, unless removed with the DROP PLAN statement, can continue to occupy table space long after the application is retired. When you drop a plan, make certain that the plan is not related to an SQL application that is still in use.

Do not use to drop plans related to procedures and functions. Use the DROP PROCEDURE statement and DROP FUNCTION statements to drop procedures and functions. The syntax for the statement is as follows.

statement is executable only from DBSQLPR, but it can also be submitted as a DBSQLPR in-line command (see Line Commands).

►►─ DROP PLAN ─┬────────────┬─ plan-name ─────────────────────────────────────►◄ └─ auth-id. ─┘

is the authorization ID, followed by a period, related to the plan name that you want to drop. Valid Entries: A valid authorization ID Default Value: (No default) is the name of the plan that you want to drop. Valid Entries: A valid plan name Default Value: (No default) Example JCL Users of z/OS can use either spaces or commas as parameter separators in the

input parameter string in the JCL. In z/VSE environments, the SYSLST file must be assigned to a POWER-controlled print device, and parameter separators in the

input parameter string must be spaces. Review the following example input parameter string specification.

 PARM='PRTWIDTH=255,INPUTWIDTH=72,PAGELEN=56,TBLHDRRPT=56'

input parameter string is limited by IBM to 100 bytes. In the following example, lines have been spanned. The first line ends in column 71 and the second line starts in column 16.

 PARM='PRTWIDTH=255,INPUTWIDTH=72,ERRBORT=-56,OPTFILE=OP,ROWLI MIT=9'

Review the following z/OS JCL example:

Use the following example as a guide to prepare your JCL. The JCL statements are for example only. Lowercase letters in a statement indicate a value that you must supply. Code all statements to your site and installation standards.

 //jobname See the note above. // //SQLEXEC EXEC PGM=DBSQLPR. see //* // PARM='prtWidth=999,inputWidth=80' //* //SYSUDUMP DD SYSOUT=* //SYSPRINT DD SYSOUT=* //STDERR DD SYSOUT=* //STDOUT DD SYSOUT=* //OPTIONS DD * AUTHID=SYSUSR /* //SYSIN DD * create table testTable (colChar char(18), colInt integer); insert into testTable values ('colChar row 1', 1); insert into testTable values ('colChar row 2', 2); -- Output appears as a table unless "PRTWIDTH=" is exceeded. select colChar, colInt from testTable; rollback work; /*

To route the SQL Optimization Report triggered by the option to a separate SYSOUT file, add //SYSMSG DD SYSOUT=* to your DBSQLPR job step. Review the following sample z/VSE JCL.

* $$ JOB . See the note above.. * $$ LST JOB jobname * CREATE OPTIONS FILE USING DITTO // UPSI 1 // EXEC PROC=yourproc // ASSGN SYSnnn,DISK,VOL=volser,SHR // DLBL OPTIONS,'dataset.name',0,SD // EXTENT SYSnnn,volser,1,0,reltrk,1 // EXEC DITTO $$DITTO CSQ FILEOUT=OPTIONS,CISIZE=512,BLKFACTOR=1 AUTHID=SYSADM /* $$DITTO EOJ /* * EXECUTE DBSQLPR // EXEC DBSQLPR,SIZE=AUTO SELECT * FROM table; /* /& * $$ EOJ

Sample Report The following example shows the first two pages of a DBSQLPR SQL Processor report.

Command Line Options ___________________ INPUTWIDTH=72 OPTFILE=OP PRTWIDTH=999 Option File Options ___________________ AUTHID=SYSADM INPUT STATEMENT: create table testTable (colChar char(18), colInt integer); ___ SQLCODE=0, SQLSTATE=00000 ___ INPUT STATEMENT: insert into testTable values ('colChar row 1', 1); ___ SQLCODE=0, SQLSTATE=00000, ROWS AFFECTED=1 ___ INPUT STATEMENT: insert into testTable values ('colChar row 2', 2); ___ SQLCODE=0, SQLSTATE=00000, ROWS AFFECTED=1 ___ -- Output appears as a table unless "PRTWIDTH=" is exceeded. INPUT STATEMENT: select colChar, colInt from testTable; COLCHAR COLINT CHAR(18) INT ___________________ ____________ colChar row 1 1 colChar row 2 2 ___ 2 rows returned ___ INPUT STATEMENT: rollback work; ___ SQLCODE=0, SQLSTATE=00000 ___ ================================================= == DBSQLPR is completing with return code 0000 == == == == Statements Found: 00005 == == Statement Errors: 00000 == == Statement Warnings: 00000 == =================================================