Environment Setting

The Environment Setting facility of the Natural Tools for DB2 allows you to issue special SQL statements interactively.

For details on the SQL statements described in this section, see the relevant DB2 literature by IBM.

This section covers the following topics:


Invoking the Environment Setting Facility

Start of instruction set To invoke the Environment Setting facility

  • On the Natural Tools for DB2 Main Menu, enter function code S and press Enter.

    The Environment Setting screen is displayed.

     15:01:49              ***** NATURAL TOOLS FOR DB2 *****             2009-10-07
                                - Environment Setting -
    
    
             Code Function                    Code Function SET CURRENT
    
              CO  CONNECT                      SS  SQLID
              RE  RELEASE (connection)         SP  PACKAGESET
              SC  SET CONNECTION               SD  DEGREE
              SY  SET ENCRYPTION PASSWORD      SU  RULES
              SR  Display SPECIAL REGISTER     SO  OPTIMIZATION HINT
              ?   Help                         SL  LOCALE LC_CTYPE
              .   Exit                         SA  PATH
                                               SE  PRECISION
                                               SM  MAINTAINED TABLE TYPES FOR OPT
                                               SB  PACKAGE PATH
      Code .. __                               SF  REFRESH AGE
                                               SH  SCHEMA
                                               SN  APPLICATION ENCODING SCHEME
    
    Command ===>
    

    This screen offers you the following functions:

    CO Specifies and executes the SQL statement CONNECT.
    RE Specifies and executes the SQL statement RELEASE.
    SC Specifies and executes the SQL statement SET CONNECTION.
    SS Specifies and executes the SQL statement SET CURRENT SQLID.
    SP Specifies and executes the SQL statement SET CURRENT PACKAGESET.
    SD Specifies and executes the SQL statement SET CURRENT DEGREE.
    SU Specifies and executes the SQL statement SET CURRENT RULES.
    SO Specifies and executes the SQL statement SET CURRENT OPTIMIZATION HINT.
    SL Specifies and executes the SQL statement SET CURRENT LOCALE LC_CTYPE.
    SA Specifies and executes the SQL statement SET CURRENT PATH.
    SE Specifies and executes the SQL statement SET CURRENT PRECISION.
    SM Specifies and executes the SQL statement SET CURRENT MAINTAINED TABLE TYPE FOR OPTIMIZATION.
    SB Specifies and executes the SQL statement SET CURRENT PACKAGE PATH.
    SF Specifies and executes the SQL statement SET CURRENT REFRESH AGE.
    SH Specifies and executes the SQL statement SET CURRENT SCHEMA.
    SN Specifies and executes the SQL statement SET CURRENT APPLICATION ENCODING SCHEME.
    SY Specifies and executes the SQL statement SET ENCRYPTION PASSWORD.
    SR Displays the current values of the supported special registers.

Connect

Start of instruction set To invoke the Connect function

  • On the Environment Setting screen, enter function code CO and press Enter.

    The Connect screen is displayed:

      14:23:29              ***** NATURAL TOOLS FOR DB2 *****             2006-04-13
                                         - Connect -
    
    
    
    
    
    
       >>---- CONNECT ---+-- _ --------------------------------+-------------------><
                         !                                     !
                         !                                     !
                         +-- _ --- TO ---- ________________  --+
                         !                  (location name)    !
                         !                                     !
                         +-- _ --- RESET ----------------------+
    
    
        Current Server Version ________
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec                                            Canc

    The Connect function connects the current application to a designated server. This server is the current server, which is displayed in the Current Server Version field.

    On the Connect screen, you identify the current server by specifying a location name. The identified server must be known to the local DB2 subsystem.

Release

Start of instruction set To invoke the Release function

  • On the Environment Setting screen, enter function code RE and press Enter.

    The Release screen is displayed:

      14:24:29              ***** NATURAL TOOLS FOR DB2 *****            2006-04-13
                                        - Release -
    
    
    
    
    
       >>--- RELEASE ------+-------- ________________ ------+---------------------><
                           !         location-name          !
                           !                                !
                           +-- _ --- CURRENT ---------------+
                           !                                !
                           !-- _ --- ALL SQL ---------------!
                           !                                !
                           +-- _ --- ALL PRIVATE -----------+
    
    
    
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec                                            Canc

    The Release function places one or more connections in the release pending state.

Set Connection

Start of instruction set To invoke the Set Connection function

  • On the Environment Setting screen, enter function code SC and press Enter.

    The Set Connection screen is displayed:

      14:23:47              ***** NATURAL TOOLS FOR DB2 *****            2006-04-13
                                      - Set Connection -
    
    
    
    
    
    
    
       >>--- SET CONNECTION -------- ________________ ----------------------------><
                                     location-name
    
    
    
    
    
    
    
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec                                            Canc

    On the Set Connection screen, you identify a server by specifying a location name. The identified server must be known to the local DB2 subsystem.

Set Current SQLID

Start of instruction set To invoke the Set Current SQLID function

  • On the Environment Setting screen, enter function code SS and press Enter.

    The Set Current SQLID screen is displayed:

      14:23:47              ***** NATURAL TOOLS FOR DB2 *****             2006-04-13
                                    - Set Current SQLID -
    
    
    
    
    
    
    
            >>--- SET CURRENT SQLID = ----- __________ --------------------><
                                       ( USER,
                                         string-constant)
    
    
    
    
    
    
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec  Free                                      Canc

    The Set Current SQLID function changes the value of the SQL authorization identifier. With SQL statements that use unqualified table names, DB2 uses the SQLID as an implicit table qualifier. This enables you to access identical tables with the same table name but with different creator names.

    On the Set Current SQLID screen, you can replace the value of CURRENT SQLID by the value of the special register USER or by a string constant. The string constant can be up to 8 characters long.

    In all supported TP-monitor environments, the SQLID can then be kept across terminal I/Os until its resetting or the end of the session.

Set Current Packageset

Start of instruction set To invoke the Set Current Packageset function

  • On the Environment Setting screen, enter function code SP and press Enter.

    The Set Current Packageset screen is displayed:

    09:39:07              ***** NATURAL TOOLS FOR DB2 *****             2006-04-18
    
                               - Set Current Packageset -
    
    
    
    
    >>--- SET CURRENT PACKAGESET = --------------------------------------------->
    
    
    
     >-+-- _ - USER ----------------------------------------------------------+-><
    
       !                                                                      !
    
       +-- _________________________________________________________________  !
    
                                   (string-constant)                          !
    
           _________________________________________________________________ -+
    
                                (string-constant cont.)
    
    
    Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec                                            Canc

    The SET CURRENT PACKAGESET statement assigns a value to the special register CURRENT PACKAGESET.

    On the Set Current Packageset screen, you can replace the value of CURRENT PACKAGESET by the value of the special register USER or by a string constant of up to 18 characters.

Set Current Degree

Start of instruction set To invoke the Set Current Degree function

  • On the Environment Setting screen, enter function code SD and press Enter.

    The Set Current Degree screen is displayed:

      14:23:58              ***** NATURAL TOOLS FOR DB2 *****             2006-04-13
                                  - Set Current Degree -
    
    
    
    
    
    
    
       >>--- SET CURRENT DEGREE ---------- ___ -----------------------------------><
                                       ( 1 or ANY )
    
    
    
    
    
    
    
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec                                            Canc

    CURRENT DEGREE specifies the degree of parallelism for the execution of queries that are dynamically prepared by the application process.

Set Current Rules

Start of instruction set To invoke the Set Current Rules function

  • On the Environment Setting screen, enter function code SU and press Enter.

    The Set Current Rules screen is displayed:

      14:23:58              ***** NATURAL TOOLS FOR DB2 *****             2006-04-13
                                   - Set Current Rules -
    
    
    
    
    
    
    
       >>--- SET CURRENT RULES ----------- ___ -----------------------------------><
                                     ( DB2 or STD )
    
    
    
    
    
    
    
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec                                            Canc

    CURRENT RULES specifies whether certain SQL statements are executed in accordance with DB2 rules or the rules of the SQL standard.

Set Current Optimization Hint

Start of instruction set To invoke the Set Current Optimization Hint function

  • On the Environment Setting screen, enter function code SO and press Enter.

    The Set Current Optimization Hint screen is displayed:

       09:41:43              ***** NATURAL TOOLS FOR DB2 *****             2006-04-18
    
                            - Set Current Optimization Hint -
    
    
    
    
     >>--- SET CURRENT OPTIMIZATION HINT --------------------------------------->
    
    
    
      >--- _________________________________________________________________
    
                                    (string-constant)
    
           _________________________________________________________________ ---><
    
                                 (string-constant cont.)
    
     Command ===>
    
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec                                            Canc

    CURRENT OPTIMIZATION HINT specifies the user-defined optimization hint that DB2 should use to generate the access path for dynamic statements.

Set Current Locale LC_CType

Start of instruction set To invoke the Set Current Locale LC_CType function

  • On the Environment Setting screen, enter function code SL and press Enter.

    The Set Current Locale LC_CType screen is displayed:

     14:58:12              ***** NATURAL TOOLS FOR DB2 *****             2006-04-13
                            - Set Current Locale LC_CType -
    
    
    
    
    
    
    
    >>--- SET CURRENT LOCALE LC_CTYPE ----------------------------------------->
    
     >--------- ____________________________________________________ ----------><
                                 (string-constant)
    
    
    
    
    
    
    
     Command ===>
     Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
           Help  Error Exit  Exec                                            Canc

    CURRENT LOCALE LC_CTYPE specifies the LC_CTYPE locale that will be used to execute SQL statements that use a built-in function that references a locale.

Set Current Path

Start of instruction set To invoke the et Current Path function

  • On the Environment Setting screen, enter function code SA and press Enter.

    The Set Current Path screen is displayed:

    09:42:09              ***** NATURAL TOOLS FOR DB2 *****             2006-04-18
    
                                  - Set Current Path -
    
    
    
    
    
    >>- SET CURRENT PATH ------------------------------------------------------->
    
    
    
       +------------------------------<--( , )--------------------------------+
    
       !                                                                      !
    
    >-++-------------------------------- _ ---------------------------------++-><
    
        !                   (schema-name<,schema-name,...>)                  !
    
        !                                                                    !
    
        +- _ ----------------------- SYSTEM PATH ----------------------------+
    
        !                                                                    !
    
        +- _ -------------------------- USER --------------------------------+
    
        !                                                                    !
    
        +- _ ---------------------- CURRENT PATH ----------------------------+
    
        !                                                                    !
    
        +- _ ------------------ CURRENT PACKAGE PATH ------------------------+
    
    Command===>
    Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
         Help  Error Exit  Exec                                             Canc

    CURRENT PATH specifies the SQL path used to resolve unqualified data type names and function names in dynamically prepared SQL statements.

Set Current Precision

Start of instruction set To invoke the Set Current Precision function

  • On the Environment Setting screen, enter function code SE and press Enter.

    The Set Current Precision screen is displayed:

     15:01:17              ***** NATURAL TOOLS FOR DB2 *****             2006-04-13
                               - Set Current Precision -
    
    
    
    
    
    
    
    >>--- SET CURRENT PRECISION ------- DEC15 ---------------------------------><
                                 (DEC15,DEC31,15,31,
                             D15.1 - D15.9,D31.1 - D31.9)
    
    
    
    
    
    
    
    
      Command ===>
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec                                            Canc

    CURRENT PRECISION specifies the rules to be used when both operands in a decimal operation have precisions of 15 or less.

Set Current Maintained Types for Optimization

Start of instruction set To invoke the Set Current Maintained Types function

  • On the Environment Setting screen, enter function code SM and press Enter.

    The Set Current Maintained Types for Optimization screen is displayed:

    09:36:51              ***** NATURAL TOOLS FOR DB2 *****             2006-04-18
    
                            - Set Current Maintained Types -
    
    
    >>--- SET CURRENT MAINTAINED TYPES --- SYSTEM -----------------------------><
    
                                 ( ALL, NONE, SYSTEM or USER )
    
    
    
    
    
    
    
    
     Command ===>
    
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec                                            Canc

    CURRENT MAINTAINED TABLE TYPES FOR OPTIMIZATION specifies a value that identifies the types of objects that can be considered to optimize the processing of dynamic SQL queries. This register contains a keyword representing table types.

Set Current Package Path

Start of instruction set To invoke the Set Current Package Path function

  • On the Environment Setting screen, enter function code SB and press Enter.

    The Set Current Package Path screen is displayed:

     09:37:22              ***** NATURAL TOOLS FOR DB2 *****             2006-04-18
                               - Set Current Package Path -
    
    
    >> - SET CURRENT PACKAGE PATH ------------------------------------------------>
    
    
        +------------------------------< --( , )--------------------------------+
         !                                                                     !
      > -++-------------------------------- _ ---------------------------------++-><
         !                 (collection-id< ,collection-id,...> )               !
         !                                                                     !
         +- _ -------------------------- USER --------------------------------+
         !                                                                    !
         +- _ ---------------------- CURRENT PATH ----------------------------+
         !                                                                    !
         +- _ ------------------ CURRENT PACKAGE PATH ------------------------+
    
    
     Command ===>
    
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec                                            Canc

    CURRENT PACKAGE PATH specifies a value that identifies the path used to resolve references to packages that are used to execute SQL statements.

Set Current Refresh Age

Start of instruction set To invoke the Set Current Refresh Age function

  • On the Environment Setting screen, enter function code SF and press Enter.

    The Set Current Refresh Age screen is displayed:

    09:37:40              ***** NATURAL TOOLS FOR DB2 *****             2006-04-18
    
                              - Set Current Refresh Age -
    
    
    
    
    
    
    >> --- SET CURRENT REFRESH AGE ----- _____________________ -----------------><
    
                             ( 0 or ANY/99999999999999.000000 )
    
    
    
    
    
    
    
    Command ===>
    
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec                                            Canc

    CURRENT REFRESH AGE specifies a timestamp duration value with a data type of DECIMAL.

Set Current Schema

Start of instruction set To invoke the Set Current Schema function

  • On the Environment Setting screen, enter function code SH and press Enter.

    The Set Current Schema screen is displayed:

    09:38:01              ***** NATURAL TOOLS FOR DB2 *****             2006-04-18
    
                                 - Set Current Schema -
    
    
    
    
    >>- SET CURRENT SCHEMA ----------------------------------------------------->
    
    
    
     >--+- ________________________________________________________________ -+--><
    
        !                            (schema-name)                           !
    
        !                                                                    !
    
        +- _ --------------------------- USER -------------------------------+
    
        !                                                                    !
    
        +- _ -------------------------- DEFAULT -----------------------------+
    
        !                                                                    !
    
        +- ________________________________________________________________ -+
    
                                   (string-constant)
    
    
    
    
    
    Command ===>
    
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec                                            Canc

    The CURRENT SCHEMA, or equivalently CURRENT_SCHEMA, special register specifies the schema name used to qualify unqualified database object references in dynamically prepared SQL statements.

Set Current Application Encoding Scheme

Start of instruction set To invoke the Set Current Application Encoding Scheme function

  • On the Environment Setting screen, enter function code SN and press Enter.

    The Set Current Application Encoding Scheme screen is displayed:

    09:38:21              ***** NATURAL TOOLS FOR DB2 *****             2006-04-18
    
                       - Set Current Application Encoding Scheme -
    
    
    
    
    
    
    
    
     >>--- SET CURRENT APPLICATION ENCODING SCHEME ----------------------------->
    
    
    
      >--------------------------------- _______ -------------------------------><
    
                                ( ASCII, EBCDIC, UNICODE
    
                                      or 1 - 65533)
    
    
    
    
    
    
     Command ===>
    
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec                                            Canc

    CURRENT APPLICATION ENCODING SCHEME specifies which encoding scheme is to be used for dynamic statements. It allows an application to indicate the encoding scheme that is used to process data.

Set Encryption Password

Start of instruction set To invoke the Set Encryption Password function

  • On the Environment Setting screen, enter function code SY and press Enter.

    The Set Encryption Password screen is displayed:

    09:36:13              ***** NATURAL TOOLS FOR DB2 *****             2006-04-18
    
                              - Set Encryption Password -
    
    
    
    
    
    
    
    
    
    
    
    >>--- SET ENCRYPTION PASSWORD ---------------------------------------------->
    
    
    
     >---- _________________________________________________________________
    
                              (password-string-constant)
    
           ________________________________________________________________ ---->
    
                           (password-string-constant cont.)
    
    
    
     >-+----------------------------------------------------------------------+-><
    
       !                                                                      !
    
       +--- WITH HINT --- __________________________________ -----------------+
    
                                (hint-string-constant)
    
    
    
    
    
    Command ===>
    
      Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Exec                                            Canc

    The Set Encryption Password function sets the value of the encryption password and, optionally, the password hint.

Display Special Registers

Start of instruction set To invoke the Display Special Registers function

  • On the Environment Setting screen, enter function code SR and press Enter.

    The Display Special Registers screen is displayed:

    15:18:07              ***** NATURAL TOOLS FOR DB2 *****            2006-04-13
                             - Display Special Registers -
     Current
      +Client_Acctng .........
      +Client_ApplName .......
      +Client_UserID .........
      +Client_WrkStnName .....
       Appl.Encoding Scheme .. EBCDIC
       Date .................. 13.04.2006
       Degree ................ 1
       LC_CType ..............
      +Maintained Types ...... SYSTEM
    
       Member ................ DB28
      +Optimization Hint .....
    
      +Package Path ..........
    
    
    
    Command ===>
     Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
            Help  Error Exit  Updat                                     Next  Canc

    When you press PF11, the next screen of Special Register values is displayed. 

    15:31:20              ***** NATURAL TOOLS FOR DB2 *****            2006-04-13
                             - Display Special Registers -
     Current
      +PackageSet ............
    
      +Path .................. "SYSIBM","SYSFUN","SYSPROC","GGS"
    
    
       Precision ............. DEC15
       Refresh Age ...........
       Rules ................. DB2
      +Schema ................ GGS
    
       Server ................ DAEFDB28
       SQLID ................. GGS
       Time .................. 15.31.20
       TimeStamp ............. 2006-04-13-15.31.20.948481
       TimeZone .............. 10000
       User .................. GGS
    
    Command ===>
     Enter-PF1---PF2---PF3---PF4---PF5---PF6---PF7---PF8---PF9---PF10--PF11--PF12---
           Help  Error Exit  Updat                               Prev        Canc

    When you press PF10, the previous screen of Special Register values is displayed.

    The Display Special Registers screens show you the current values of the Special Registers of DB2 supported by Natural for DB2.

    Fields, which are prefixed with a plus sign (+), may contain more data than displayed on the screen. You can display the full contents either when you position the cursor on the field (description or data) and press ENTER, or when you enter the abreviation of the field (which are the capital letters of the description) prefoxed by the plus sign (+) in the command line. For example, +PS shows a window with the full value of the Current Package Set.