Using SET OPTIONS to Select Options

You can optionally include a SET OPTIONS statement in an IDMSBCF (batch) or OCF (online) session to do the following:
You can optionally include a SET OPTIONS statement in an IDMSBCF (batch) or OCF (online) session to do the following:
  • Control the processing of statements
  • Format the output from the execution of SQL SELECT statements
  • Control input and output of the batch command facility (IDMSBCF)
  • Change the command delimiter
                    ┌──────────────────────────────┐ ►►─── SET OPTIONS ──▼─┬─ 
───►◄                       ├─ 
 ────────┤                       ├─ 
 ────────────┤                       └─ 
Expansion of session-option
►►─┬─────────────────────────────────────────────┬───────────────────────────►◄    └─┬─ AUTOCOMMIT ──┬─ ON ◄──┬────────────┬─┬─┬─┘      │               │        └─ CONTINUE ─┘ │ │      │               ├─ COMMAND ─────────────┤ │      │               └─ OFF ─────────────────┘ │      └─ ON ERROR ─┬─ END ────────┬─────────────┘                   ├─ CONTINUE ◄──┤                   └─ ROLLBACK ───┘
Expansion of format-option
►►──┬──────────────────────────────────────────────────────────────────────┬─►◄     └─┬─ TITLE ─┬─ '
' ─┬─────────────────────────────────────────────┬┘       │         └─ DEFAULT ─┘                                             │       ├─ HEADINGS ─┬─ OFF ──┬─────────────────────────────────────────────┤       │            └─ ON ◄──┘                                             │       ├─ LINES ─┬─ 60 ◄────────┬──────────────────────────────────────────┤       │         └─ 
 ─┘                                          │       ├─ WIDTH ─┬─ PAGE 
 ────────────────────────────────┬─┤       │         ├─ CHARacter ─┬─ 80 ◄─────────────────────┬─────────────┤ │       │         │             └─ 
 ─┘             │ │       │         ├─ NUMeric 
 ─────────────────────┤ │       │         └─ COLUMN ─┬─ 
column-number  maximum-character-count
 ─┬─┘ │       │                    └─ OFF ────────────────────────────────────┘   │       ├─ UNDERLINE 
 ───────────────────────────────────┤       ├─ PAGEBREAK ─┬─ 
 ─┬─────────────────────────────────┬─┤       │             │                 └ 
 ┬────────────────┬┘ │       │             │                                 └ 
 ─┘  │       │             └─ OFF ───────────────────────────────────────────────┤       ├─ STATUS ─┬─ OFF ───┬──────────────────────────────────────────────┤       │          └─ ON ◄───┘                                              │       ├─ COMPRESS ─┬─ ON ────┬────────────────────────────────────────────┤       │            └─ OFF ◄──┘                                            │       └─ COLUMN WRAP ─┬─ OFF ──┬──────────────────────────────────────────┘                       └─ ON ◄──┘
Expansion of io-option
►►─┬── OUTPUT TO ────┬─── SYSLST ◄────────┬───────────────────────────┬─────►◄    │                 └─ 
 ────┘                           │    └── INPUT FROM ────┬─ SYSIPT ◄──────┬──────────────────────────────┘                       └─ 
Expansion of cmd-delimiter-option
►►──── COMMAND DELIMITER ───┬──── DEFAULT ◄─────────────┬────────────────────►◄                             └─── '
' ───┘
  • cmd-delimiter
    The current command delimiter to terminate the SET OPTIONS command. Initially the command delimiter is the semicolon(;) character, but it can be modified with the SET OPTIONS
  • session-option
    On a SET OPTIONS statement, specifies session control options such as when to commit and rollback work and whether to continue processing when a statement execution results in an error.
    Specifies AUTOCOMMIT options after successful statement execution (for more information on options for statements that return errors, see the ON ERROR parameter). AUTOCOMMIT options are shown in the following table.
AUTOCOMMIT does not apply to utility statements.
Executes a COMMIT WORK RELEASE after the last statement in the input stream.
Executes a COMMIT WORK CONTINUE at the end of the edit buffer. Executes a COMMIT WORK RELEASE at the end of the SQL session; resources are released and temporary tables are dropped.
Executes a COMMIT WORK RELEASE at the end of the module.
Holds resources until a COMMIT is issued, enabling you to keep temporary tables; note that this option can slow performance and should be used with caution.
Executes a COMMIT WORK CONTINUE after each successfully executed statement.
No automatic COMMIT occurs; you must specify COMMIT WORK RELEASE sectionly.
-- A release is issued at the end of processing, and this may cause a rollback if any work has not been committed.
    Specifies whether the session ends when a statement returns an error status code (a code with a value less than zero). Options are shown in the following table.
ROLLBACK does not apply to utility statements.
Continues executing.
Continues executing.
Continues executing.
Ends job step and session. Proceeds with the AUTOCOMMIT option.
Ends execution of commands in the current edit buffer and ends the SQL session. Does not end the OCF session. Proceeds with the AUTOCOMMIT option.
Ends execution of commands in the module and ends the session. Proceeds with the AUTOCOMMIT option.
Issues a ROLLBACK RELEASE and ends job step.
Issues a ROLLBACK RELEASE and ends the SQL session. Does not end the OCF session.
Issues a ROLLBACK RELEASE and ends the execution of the module.
  • format-option
    On a SET OPTIONS statement, specifies options for formatting the output of an SQL SELECT statement.
    Specifies a user-defined title line or resets the default title line. The TITLE parameter applies to IDMSBCF only.
  • '
    Overrides the default title line with the specified title. You must enclose a title in single quotes.
    Resets the title line to the default. The default title line is 'IDMS Batch Command Facility'.
    Enables or suppresses page breaks and the output formatting that goes with them.
    • OFF
      Suppresses page breaks. The title and column headings appear only once. Use this parameter when you intend to browse batch output online. OFF is the OCF default.
    • ON
      Enables page breaks so that column name headings appear at the top of each page. ON is the IDMSBCF default.
    Sets the number of lines per page (the default is 60).
    Sets page and/or column widths, or resets default widths.
    • PAGE
      Sets the width of a page for output. The range for IDMSBCF or OCFX is 40 to 132 (anything less than 40 is set to 40, anything greater than 132 is set to 132). The maximum value you should specify for OCF is 76; this reserves three characters for the *+ and space character preceding output; to suppress these characters, specify WIDTH PAGE 79.
      The PAGE parameter overrides the CHARACTER parameter.
    • CHARacter
      Sets the maximum width of
      columns. Range is 1 to the width of the page (the default is 80). Columns longer than
      are wrapped within the line. WIDTH CHAR must be less than the value specified for WIDTH PAGE.
    • NUMeric
      Sets the maximum width of
      columns. Range is 1 to 32 (the default is the maximum length for the data type). If an exact value (including the sign) contains more digits than
      , the value is replaced in output by a string of asterisks. Approximated values are rounded to fit the specified width of the column.
    • COLUMN
      Sets the maximum width of the column identified by
      . The maximum value you can specify for
      is the width of the page. This parameter overrides WIDTH CHARACTER and WIDTH NUMERIC parameters for the specified column.
      Turns off all column width settings that were specified using the WIDTH COLUMN parameter.
    Specifies the character that is to be used to underline column headings in output. The default is a blank space.
    Enables or disables settings required for a page break.
    • column-number
      Specifies the number of an output column that is to be used to control page breaks. A change in the value of a column forces a page break. You can specify up to three column numbers. The HEADING OFF and COLUMN WRAP ON parameters disable this parameter.
    • OFF
      Clears the column settings specified in the PAGEBREAK parameter of a previous SET OPTIONS statement.
    Controls the display of return codes.
    • OFF
      Specifies that only non-zero status codes are to be displayed after statement execution.
    • ON
      Specifies that return codes are to be displayed after the execution of every statement. ON is the default.
    Controls truncation of the output resulting from the execution of an SQL SELECT statement.
    • ON
      Specifies truncation of output columns so that an entire row fits on one line.
    • OFF
      Specifies normal output without any truncation of lines. OFF is the default.
    Enables or disables line wrap for output columns.
    • OFF
      Specifies that non-numeric columns that are longer than the available output space are to be truncated.
    • ON
      Specifies that non-numeric columns that are longer than the available output space are to be displayed on multiple lines. ON is the default unless the COMPRESS ON parameter is used.
  • io-option
    On a SET OPTIONS statement, specifies options for controlling the input and output of the command facility. The io-option is for IDMSBCF (batch) only.
    Specifies where to write data output.
    • SYSLST
      Writes data output to SYSLST. If the prior assignment of the OUTPUT stream was not SYSLST, the prior OUTPUT assignment is closed.
    • output-ddname
      Specifies the z/OS DD name, z/VSE file name link name of a sequential data set to use for writing the data output.
      When output is assigned to
      , these rules apply:
      • WIDTH PAGE is automatically set to the record length (or maximum record length for variable record files) that was specified when the file associated with
        was created. If no record length and record format were specified, the record format defaults to variable and the record length to block size - 4; if no block size was specified, a block size of 4096 is used.
      • HEADINGS are set to OFF.
      • The "non-data" information like the echoed command, eventual headers, the number of rows processed, and the SQL return code are output to SYSLST.
      • Output data are not prefixed by "*+".
      • Data are represented in string format, not in the native format. For example, a column defined as INT with value 12345678 is internally stored as a 4-byte binary value X'00BC614E'; in the output data however, the column value is 8-byte character string '12345678'.
      • The width of each column in the output file is determined by the larger value of the column width and the column header. For example, a column named "Date", defined as CHAR(10) uses 10 positions in the output file; a column named "MiddleInitial", defined as CHAR(1) uses 13 positions.
      • IDMSBCF inserts two blanks in between successive columns.
      • The
        file is closed on the next SET OPTIONS OUTPUT or at program end.
    Specifies where to read input.
    • SYSIPT
      Reads input from SYSIPT.
    • input-ddname
      Specifies the DD name of a sequential data set to use for reading commands.
      When input is assigned to
      , these rules apply:
      • Input from
        can be any type and length supported by the operating system, that is, input is not limited to 80 character lines.
      • Columns 73 through 80 of the input are NOT considered as a line sequence number, that is, they should contain valid input data.
      • End-of-file on the
        file automatically reassigns input to SYSIPT.
  • cmd-delimiter-option
    On a SET OPTIONS statement, specifies the command facility terminator.
    Specifies the character string whose value will be used to delimit command facility statements after the SET OPTIONS command has been executed.
    • new-cmd-delimiter
      Specifies the character string literal to be used as a delimiter. '
      ' must be a 1- to 32-character string.
      Specifies that the default of a semicolon (;) will be used as a delimiter.
Statement terminator
Use the command delimiter, by default a semicolon, to terminate a SET OPTIONS statement. The use of an alternate command delimiter is required when entering multi-statement SQL routine bodies using the CREATE PROCEDURE or CREATE FUNCTION SQL DDL commands. According to the SQL procedural language, multiple SQL statements must be separated by the semicolon. However, using the semicolon also as the command terminator would truncate the CREATE command after the first semicolon, and any statements thereafter would erroneously be interpreted as new commands for the command facility and not as statements that make up the rest of the SQL routine body.
Specifying a command terminator string replaces the previous specified one or the default, the semicolon, if none was specified. The specification of a command delimiter, just as any SET OPTIONS parameter remains in effect until a new SET OPTIONS COMMAND DELIMITER is issued or until the end of the command facility session.
Number of SET OPTIONS statements in a job step
You can use more than one SET OPTIONS statement in an IDMSBCF or OCF session. This enables you to change session, formatting, io and command delimiter options without requiring you to end the session.
Each parameter you specify remains in effect to the end of the session unless you explicitly change that same parameter in a subsequent SET OPTIONS statement.
Page breaks for ordered information
You can use the PAGEBREAK parameter of SET OPTIONS to separate information sorted by the ORDER BY clause of the SQL SELECT statement.
Input and output assignment
You can use the OUTPUT TO parameter to output the resulting data of, for example, SQL commands to an intermediate file, which can then be used as input to IDMSBCF or a user written program.
Combining the OUTPUT TO and INPUT FROM parameters allow you to write IDMSBCF scripts to perform the following tasks:
  • Unload/Load or copy of selective table(s) using SQL DML.
  • Automatic access module recompile script for all access modules that are affected by an update statistics or change in table or any other condition that can be detected by looking in the catalog/dictionary.
  • Build LOAD file for loading data using SQL DML.
  • Build XML scripts to unload/load data from/to CA IDMS to/from XML documents.
Session control parameters
The following SET OPTIONS statement specifies that a COMMIT WORK CONTINUE is to be issued after the successful execution of each statement; ON ERROR END specifies that the session is to end if the execution of a statement results in an error.
set options autocommit command         on error end;
Submitting batch and viewing online
The following example shows SET OPTIONS parameters that you might typically use to submit an IDMSBCF batch job and view the output online.
set options title 'employee list'         headings on         underline *         lines 24         width page 80         width char 10         width num 5         width column 2 6         pagebreak 2 4         column wrap off;
With these parameters specified, the output is formatted as follows:
  • Headings appear at the top of each page and are underscored using asterisks (underline *).
  • The page length is set to the number of lines on the screen (24).
  • The page width is set to the width of the screen.
  • Non-numeric and numeric output columns are set to 10 and 5, respectively, with a maximum width in column 2 of 6 characters.
  • Any change in the value of column 2 or column 4 will force a new page.
  • Non-numeric columns will be truncated as needed so output rows can appear on a single line (WRAP OFF).
The following example shows the output of a SELECT statement when the output exceeds the value specified by WIDTH PAGE. Because the screen is not wide enough to display all four columns of data, the data for the fourth column is displayed after all of the data for the first three columns is displayed.
                   OCF nn.n IDMS  PAGE 1 LINE 1                 1/137  cv SELECT EMP_LNAME,EMP_FNAME,D.DEPT_ID, DEPT_NAME FROM DEMOEMPL.DEPARTMENT D,DEMOEMPL.EMPLOYEE E WHERE D.DEPT_ID=E.DEPT_ID; *+ *+ EMP_LNAME             EMP_FNAME             DEPT_ID *+ ---------             ---------             ------- *+ Brooks                John                     3510 *+ Park                  Deborah                  2210 *+ Smith                 Carl                     3530 *+ Spade                 Samuel                   4600 *+ Loren                 Martin                   4600 *+ Anderson              Alice                    6200 *+ MacGregor             Bruce                    2200 *+ Lynn                  David                    2200 *+ *+ DEPT_NAME *+ --------- *+ APPRAISAL - USED CARS *+ SALES - NEW CARS *+ APPRAISAL - SERVICE *+ MAINTENANCE *+ MAINTENANCE *+ CORPORATE ADMINISTRATION *+ SALES - USED CARS *+ SALES - USED CARS *+ 8 rows processed
As an alternative, you can specify COMPRESS ON. This truncates column output as needed so that all output can fit on a single line.
If you specify too many columns in the SELECT statement, COMPRESS ON may not work. In this case, an error message will alert you.
The following example shows how output may be displayed when WIDTH CHARACTER is specified. The maximum width of non-numeric columns is set to 10. This causes the output for the 20-character JOB_TITLE column to wrap to subsequent output lines, as needed.
                   OCF nn.n IDMS  NO ERRORS                     1/59   cv SET OPTIONS WIDTH CHAR 10; *+ Status = 0 SELECT JOB_ID, JOB_TITLE, MIN_RATE, MAX_RATE, EFF_DATE FROM JOB; *+ *+ JOB_ID  JOB_TITLE         MIN_RATE        MAX_RATE  EFF_DATE *+ ------  ---------         --------        --------  -------- *+   4666  Sr Mechani        20500.00        45500.00  yyyy-mm-dd *+         c *+   5555  Salesperso        15000.00        39500.00  yyyy-mm-dd *+         n *+   4123  Recruiter         17500.00        28000.00  yyyy-mm-dd *+ *+   4025  Writer - M        15500.00        25000.00  yyyy-mm-dd *+         ktng *+   4023  Accountant        22000.00        60000.00  yyyy-mm-dd *+ *+   8001  Vice Presi        45000.00        68000.00  yyyy-mm-dd *+         dent *+   2077  Purch Cler         8500.00        15000.00  yyyy-mm-dd *+         k *+   2051  AP Clerk              4.80           10.60  yyyy-mm-dd *+  .  .  .
Additional space for wrapping the 20 characters in JOB_TITLE is provided even when the column output does not require it. If you specify COLUMN WRAP OFF, the non-numeric columns will be truncated instead of wrapping.
Sample IDMSBCF script
The following IDMSBCF example is a fairly generic script to unload/load or copy a table or set of tables. The sample script allows null values; however, it does not allow data containing quotes, more exotic data types, such as GRAPHIC, VARGRAPHIC, BINARY, and so on.
Input Script
------------------------------------------------------------------------- -- This scripts copies the rows from a source table to a target table. -- It is assumed that the target table is already defined ------------------------------------------------------------------------- -- -- Helper view to set the params of the Table copy -- drop   view usera01.CopyTabParm; create view usera01.CopyTabParm as   select SCHEMA     as SrcSchema        , Name       as SrcTable        , 'USERA01'  as TgtSchema  -- Set value of TgtSchema        , 'EMPLOYEE' as TgtTable   -- Set value of TgtTable    from SYSTEM.TABLE   where SCHEMA = 'DEMOEMPL'       -- Set value of SrcSchema     and NAME   = 'EMPLOYEE'       -- Set value of SrcTable ; -- -- Create the Unload syntax -- set options OUTPUT to Unload; select 'select ''insert into '      || trim(TgtSchema) || '.' || trim(TgtTable)      || ' VALUES( '''      , '-'||'-', 0 as sequence from usera01.CopyTabParm union SELECT  '||''' || SUBSTR(', ', CAST(1/NUMBER as SMALLINT) + 1, 1)  || '''||TRIM(VALUE('  || SUBSTR('CAST(      ''''''''||     ''''''''||CAST('          , (11 * (  LOCATE(TYPE, 'CHARACTER           ', 1)                   + LOCATE(TYPE, 'VARCHAR             ', 1)                   + LOCATE(TYPE, ' DATE               ', 1)) + 1)          , 11)  || trim(NAME) || ' '  || SUBSTR(     'as char(10))      ||''''''''            as char(10))||'''''''''          , (18* (  LOCATE(TYPE, 'CHARACTER           ', 1)                  + LOCATE(TYPE, 'VARCHAR             ', 1)                  + LOCATE(TYPE, ' DATE               ', 1)) + 1)          ,18)  || ',''NULL''))'   , '-'||'-', NUMBER as sequence FROM SYSTEM.COLUMN, usera01.CopyTabParm  WHERE TABLE  = SrcTable    and schema = SrcSchema union select '||'');'' from '      || trim(SrcSchema) || '.' || trim(SrcTable) || ';'      , '-'||'-', 99999 as sequence from usera01.CopyTabParm order by sequence ; -- -- Create the Load syntax for the new Table -- set options OUTPUT to Load; set options INPUT from Unload; -- -- Load the new Table -- set options OUTPUT to SYSLST; set options INPUT from Load;
Output from Sample Generic Table Copy Script
select 'insert into USERA01.EMPLOYEE VALUES( ' -- 0 ||' '||TRIM(VALUE(CAST(      EMP_ID as char(10))      ,'NULL'))           --  1 ||','||TRIM(VALUE(CAST(      MANAGER_ID as char(10))      ,'NULL'))       --  2 ||','||TRIM(VALUE(''''||     EMP_FNAME ||''''            ,'NULL'))        --  3 ||','||TRIM(VALUE(''''||     EMP_LNAME ||''''            ,'NULL'))        --  4 ||','||TRIM(VALUE(CAST(      DEPT_ID as char(10))      ,'NULL'))          --  5 ||','||TRIM(VALUE(''''||     STREET ||''''            ,'NULL'))           --  6 ||','||TRIM(VALUE(''''||     CITY ||''''            ,'NULL'))             --  7 ||','||TRIM(VALUE(''''||     STATE ||''''            ,'NULL'))            --  8 ||','||TRIM(VALUE(''''||     ZIP_CODE ||''''            ,'NULL'))         --  9 ||','||TRIM(VALUE(''''||     PHONE ||''''            ,'NULL'))            --  10 ||','||TRIM(VALUE(''''||     STATUS ||''''            ,'NULL'))           --  11 ||','||TRIM(VALUE(CAST(      SS_NUMBER as char(10))      ,'NULL'))        --  12 ||','||TRIM(VALUE(''''||CAST(START_DATE as char(10))||'''','NULL'))       --  13 ||','||TRIM(VALUE(''''||CAST(TERMINATION_DATE as char(10))||'''','NULL')) --  14 ||','||TRIM(VALUE(''''||CAST(BIRTH_DATE as char(10))||'''','NULL'))       --  15 ||');' from DEMOEMPL.EMPLOYEE; -- 99999
insert into USERA01.EMPLOYEE VALUES(  2299,NULL,'Samuel ','Spade               ',4600,'47 London St ','Canton              ','MA','02020    ',NULL,'L',33892200,'1991-02-04',NULL,'1958-01-09'); insert into USERA01.EMPLOYEE VALUES(  3411,2894,'Catherine ','Williams            ',5200,'566 Lincoln St ','Boston              ','MA','02010    ',NULL,'A',83356561,'1993-09-30',NULL,'1967-10-28'); insert into USERA01.EMPLOYEE VALUES(  4773,3082,'Janice ','Dexter              ',3510,'399 Pine St ','Medford             ','MA','02432 ','5083847566','A',89675632,'1997-06-14',NULL,'1969-11-19');    ...    ...    ... insert into USERA01.EMPLOYEE VALUES(  3118,3222,'Alan ','Wooding             ',4500,'196 School St ','Canton              ','MA','02020 ','5083766984','A',98746783,'1992-11-18',NULL,'1969-05-17'); insert into USERA01.EMPLOYEE VALUES(  3769,2894,'Julie ','Donelson            ',3520,'14 Atwood Rd ','Grover              ','MA','02976 ','5084850432','A',67783532,'1994-08-31',NULL,'1967-08-15');
Using an alternate command delimiter
The definition of the following SQL procedure requires the use of an alternate command delimiter to avoid a collision of the default, the semicolon, with the delimiter in the SQL procedural language.
set options command delimiter '++'; drop procedure PRODUCTION.PROCESS ++ commit++ create procedure PRODUCTION.PROCESS  (PROC_TYPE integer,PROC_VALUE char(2_))    external name DPROCESS language SQL begin   set PROC_TYPE = 12;   set PROC_VALUE = 'High'; end ++ set options command delimiter DEFAULT ++