Mainframe Blog

Language Elements in Db2 12

5 minute read
Stephen Watts

Special Registers

>>__ _CURRENT APPLICATION ENCODING SCHEME_______________________ ______><
ǀ_CURRENT CLIENT_ACCTING____________________________________ǀ
ǀ_CURRENT CLIENT_APPLNAME___________________________________ǀ
ǀ_CURRENT CLIENT_USERID_____________________________________ǀ
ǀ_CURRENT CLIENT_WRKSTNNAME_________________________________ǀ
ǀ_ _CURRENT DATE_____  _____________________________________ǀ
ǀ ǀ                ǀ                                      ǀ
ǀ ǀ_CURRENT DATE___ǀ                                      ǀ     
ǀ_CURRENT DEBUG MODE________________________________________ǀ
ǀ_CURRENT DECFLOAT ROUNDING MODE____________________________ǀ
ǀ_CURRENT DEGREE____________________________________________ǀ
ǀ_CURRENT EXPLAIN MODE______________________________________ǀ
ǀ_CURRENT GET_ACCEL_ARCHIVE_________________________________ǀ
ǀ             _LOCALE_                                      ǀ
ǀ_ _CURRENT__ǀ________ǀ__LC_CTYPE_ _________________________ǀ
ǀ ǀ_CURRENT_LC_CTYPE______________ǀ                         ǀ
ǀ                      _TABLE_          _FOR OPTIMIZATION_  ǀ
ǀ_CURRENT MAINTAINED_ǀ________ǀ__TYPES_ǀ_________________ǀ__ǀ      
ǀ_CURRENT MEMBER____________________________________________ǀ
ǀ_CURRENT OPTIMIZATION HINT_________________________________ǀ
ǀ_CURRENT PACKAGE PATH______________________________________ǀ
ǀ_CURRENT PACKAGESET________________________________________ǀ
ǀ_ _CURRENT PATH_ __________________________________________ǀ
ǀ ǀ_CURRENT PATH_ǀ                                          ǀ
ǀ_CURRENT PRECISION_________________________________________ǀ
ǀ_ CURRENT QUERY ACCELERATION ______________________________ǀ
ǀ_CURRENT REFRESH AGE_______________________________________ǀ
ǀ_CURRENT ROUTINE VERSION __________________________________ǀ
ǀ_CURRENT RULES_____________________________________________ǀ
ǀ_ _CURRENT SCHEMA____ _____________________________________ǀ
ǀ ǀ                   ǀ                                     ǀ
ǀ ǀ_CURRENT SCHEMA____ǀ                                     ǀ
ǀ_CURRENT SERVER____________________________________________ǀ
ǀ_CURRENT SQLID_____________________________________________ǀ
ǀ                      ___(_6_)___   _WITHOUT TIME ZONE_    ǀ
ǀ_ _CURRENT TIMESTAMP__ǀ___________ǀ_ǀ__________________ǀ___ǀ
ǀ ǀ                  ǀ ǀ_(integer)_ǀ_ǀ_WITH TIME ZONE___ǀ   ǀ
ǀ ǀ_CURRENT TIMESTAMP_ǀ                                     ǀ
ǀ_ _CURRENT TIME__________ _________________________________ǀ
ǀ ǀ                       ǀ                                 ǀ
ǀ ǀ_CURRENT TIME__________ǀ                                 ǀ
ǀ_CURRENT TIME ZONE_________________________________________ǀ
ǀ_SESSION TIME ZONE_________________________________________ǀ
ǀ_ENCRYPTION PASSWORD_______________________________________ǀ
ǀ_SESSION_USER______________________________________________ǀ
ǀ    ǀ_USER______ǀ                                          ǀ
ǀ_CURRENT TEMPORAL SYSTEM_TIME______________________________ǀ
ǀ_CURRENT TEMPORAL BUSINESS_TIME____________________________ǀ

(This article is part of our Db2 Guide. Use the right-hand menu to navigate.)

Host Variables

>__:host-identifier_____________________________________________________>
                       ǀ   _INDICATOR_                   ǀ
                       ǀ__ǀ___________ǀ_:host-identifier_ǀ

In Java, the syntax of host-variable is:

>__:__________java-identifier___________________________________________>
     ǀ_IN____ǀ  ǀ_(java_expression_)_ǀ  ǀ _INDICATOR_                  ǀ
     ǀ_OUT___ǀ                         ǀ_ǀ_________ǀ_:_Java-identifier_ǀ
     ǀ_INOUT_ǀ

In PL/I, C, and COBOL, the syntax of host-variable is:

>__:_________________host-identifier____________________________________>
      ǀ_host-identifier._ǀ
>_______________________________________________________________________>
      ǀ     _INDICATOR_                                            ǀ
      ǀ___ǀ_____________ǀ_:________________________host-identifier_ǀ
                           ǀ_host-identifier._ǀ

Functions

>__function-name__(__________________________________________________)_____>
                    ǀ_ALL______ǀ  ǀ <_,_________________________________ ǀ
                    ǀ_DISTINCT_ǀ  ǀ_____expression_____________________ǀ_ǀ
                                       |_TABLE__transition-table-name_ǀ

Table Function

>_TABLE_(function_name__(________________________________))_correlation_clause__>
                    ǀ <_,____________________________ ǀ
                    ǀ_ǀ_expression__________________ǀ_ǀ
                      ǀ_TABLE_transition_table_name_ǀ

Expressions

             <_operator________________________
       >______ǀ_________function-invocation____ǀ________________________________>
              ǀ_+_ǀ ǀ_(expression)____________ ǀ
              ǀ_-_ǀ ǀ_constant________________ ǀ
                    ǀ_column-name_____________ ǀ
                    ǀ_variable________________ ǀ
                    ǀ_special-register________ ǀ
                    ǀ_scalar-fullselect_______ ǀ
                    ǀ_time-zone-expression____ ǀ
                    ǀ_labeled-duration________ ǀ
                    ǀ_case-expression_________ ǀ
                    ǀ_cast-specification______ ǀ
                    ǀ_XMLCAST-specification___ ǀ
                    ǀ_sequence-reference______ ǀ
                    ǀ_row-change-expression___ ǀ
                    ǀ_OLAP-specfication_______ ǀ
>________CONCAT_________________________________________________________________>
       ǀ_ǀǀ_____ǀ
       ǀ_/______ǀ
       ǀ_*______ǀ
       ǀ_+______ǀ
       ǀ_-______ǀ

Labeled durations

>_____function-invocation____YEAR______________________________________>
       ǀ_(expression)___ǀ  ǀ_YEARS_________ǀ
       ǀ_constant_______ǀ  ǀ_MONTH_________ǀ
       ǀ_column-name____ǀ  ǀ_MONTHS________ǀ
       ǀ_variable_______ǀ  ǀ_DAY___________ǀ
                           ǀ_DAYS__________ǀ
                           ǀ_HOUR__________ǀ
                           ǀ_HOURS_________ǀ
                           ǀ_MINUTE________ǀ
                           ǀ_MINUTES_______ǀ
                           ǀ_SECOND________ǀ
                           ǀ_SECONDS_______ǀ
                           ǀ_MICROSECOND___ǀ
                           ǀ_MICROSECONDS__ǀ

CASE expressions

                                  _ELSE NULL_______________
>__CASE___searched-when-clause___ǀ_________________________ǀ___________>
        ǀ_simple-when-clause___ǀ ǀ_ELSE__result-expression_ǀ
>__END_________________________________________________________________>

searched-when-clause:

    <_________________________________________________________
>______WHEN____search-condition__THEN____result-expression____ǀ________>

simple-when-clause:

                    <______________________________________________
>_____expression______WHEN__expression__THEN____result-expression__ǀ___>

Cast

>__CAST__(____expression___________AS__data-type__)____________________>
             ǀ_NULL_____________ǀ
             ǀ_parameter-marker_ǀ

data-type:

>_______built-in-data-type_____________________________________________>
       ǀ_distinct-type-name_ǀ
       ǀ_array-type_________ǀ

built-in data-type:

>__SMALLINT_______________________________________________________________________ ><
ǀ ǀ_INTEGER__ǀ                                                                     ǀ
ǀ ǀ ǀ INT__ǀ ǀ                                                                     ǀ
ǀ ǀ_BIGINT___ǀ                                                                     ǀ
ǀ              ______(5,0)_________________                                        ǀ
ǀ__DECIMAL____ǀ____________________________ǀ______________________________________ ǀ
ǀǀ_DEC_____ǀ  ǀ_(integer_______________)___ǀ                                       ǀ
ǀǀ_NUMERIC_ǀ             ǀ_, integer_ǀ                                             ǀ
ǀ            _(34)_                                                                ǀ
ǀ_DECFLOAT_ ǀ______ǀ______________________________________________________________ ǀ
ǀ           ǀ_(16)_ǀ                                                               ǀ
ǀ            _(53)____________                                                     ǀ
ǀ___FLOAT___ǀ_________________ǀ___________________________________________________ ǀ       
ǀ ǀ             ǀ_(_integer_)_ǀ                                                    ǀ
ǀ_ǀ_REAL______________________ǀ                                                    ǀ
ǀ ǀ            _PRECISION_    ǀ                                                    ǀ
ǀ_ǀ_DOUBLE____ǀ___________ǀ   ǀ                                                    ǀ
ǀ                       _(1 OCTETS)____                                            ǀ
ǀ_______CHARACTER_____ǀ___________________________________________________________ ǀ
ǀ ǀ ǀ ǀ_CHAR______ǀ  ǀ_(_length_)______ǀ ǀ ǀǀ_CCSID__ASCII_ǀ ǀ_FOR__SBCS__DATA_ǀ ǀ ǀ
ǀ ǀ ǀ___CHARACTER__VARYING___(_length_)__ǀ ǀ     ǀ_EBCDIC_ǀ     ǀ_MIXED_ǀ     ǀ  ǀ ǀ
ǀ ǀ  ǀ ǀ_CHAR____ǀ        ǀ                ǀ     ǀ_UNICODE_ǀ    ǀ_BIT___ǀ     ǀ  ǀ ǀ
ǀ ǀ  ǀ__VARCHAR___________ǀ                ǀ_CCSID_integer_____________________ǀ ǀ ǀ
ǀ ǀ                                                                              ǀ ǀ
ǀ ǀ                          _(IM OCTETS)__                                      ǀ ǀ
ǀ_ǀ__CHARACTER_LARGE_OBJECT_ǀ______________ǀ____________________________________ ǀ ǀ
ǀ ǀ ǀ_CHAR____ǀ         ǀǀ_(lob length)_ ǀ ǀǀ_CCSID__ASCII__ǀǀ_FOR__SBCS__DATA_ǀ ǀ ǀ
ǀ ǀ_CLOB_________________ǀ                  ǀ      ǀ_EBCDIC__ǀ     ǀ_MIXED_ǀ     ǀ ǀ
ǀ                                           ǀ      ǀ_UNICODE_ǀ     ǀ_BIT___ǀ     ǀ ǀ
ǀ                                           ǀ_CCSID_integer_____________________ ǀ ǀ
ǀ             _(1 CODEUNITES16)_                                                   ǀ
ǀ____GRAPHIC_ǀ__________________ǀ__________________________________________________ǀ
ǀ ǀ          ǀ_(__length___)____ǀ    ǀ        ǀ_CCSID__ASCII___ǀ                   ǀ
ǀ ǀ_VARGRAPHIC__(__length__)_________ǀ               ǀ_EBDIC___ǀ                   ǀ
ǀ ǀ         __(1M CODEUNITS16)_                      ǀUNICODE__ǀ                   ǀ
ǀ ǀ_DBCLOB_ǀ___________________ǀ                     ǀ_integer_ǀ                   ǀ
ǀ          ǀ_(lob length)______ǀ                                                   ǀ
ǀ            _(1)________                                                          ǀ
ǀ_ _BINARY__ǀ____________ǀ________________________________________________________ ǀ
ǀ ǀ         ǀ_(integer)_ ǀ                                   ǀ                     ǀ
ǀ ǀ__BINARY VARYING _(integer)______________________________ ǀ                     ǀ
ǀ ǀ ǀ_VARBINARY_____ǀ________________________________________ǀ                     ǀ
ǀ ǀ                           _(1M)_________________         ǀ                     ǀ
ǀ ǀ_BINARY LARGE OBJECT______ǀ______________________ǀ________ǀ                     ǀ
ǀ  ǀ_BLOB_________________ǀ  ǀ_(__integer______)____ǀ                              ǀ
ǀ                                         ǀ_K_ǀ                                    ǀ
ǀ                                         ǀ_M_ǀ                                    ǀ
ǀ                                         ǀ_G_ǀ                                    ǀ
ǀ__DATE____________________________________________________________________________ǀ
ǀ ǀ_TIME______ǀ                                                                    ǀ
ǀ ǀ            __(_6_)____      _WITHOUT TIME ZONE_                                ǀ
ǀ ǀ_TIMESTAMP_ǀ___________ǀ____ǀ___________________ǀ_______________________________ǀ
ǀ             ǀ_(integer)_ǀ    ǀ_WITH TIME ZONE____ǀ                               ǀ
ǀ_ROWID____________________________________________________________________________ǀ
ǀ XML______________________________________________________________________________ǀ

length:

>_integer_______________________________________________________________________>
             ǀ_CODEUNITS16_ǀ
             ǀ_CODEUNITS32_ǀ
             ǀ_OCTETS______ǀ

lob-length:

>_integer_______________________________________________________________________>
             ǀ_K_ǀ  ǀ_CODEUNITS16_ǀ
             ǀ_M_ǀ  ǀ_CODEUNITS32_ǀ
             ǀ_G_ǀ  ǀ_OCTETS______ǀ

XMLCAST specification

>__XMLCAST_(__expression____________AS__data-type_______________________________>
             ǀ_NULL_____________ǀ
             ǀ_parameter-marker_ǀ

Array-expression

>__array-expression__(array-index)__________________________________________>

Array-constructor

>__ARRAY_[_______________________________]__________________________________>
             ǀ_fullselect_______________ǀ
             ǀ  _,___________________   ǀ
             ǀ ǀ                     ǀ  ǀ
             ǀ V                     ǀ  ǀ
             ǀ___element-expression__ǀ__ǀ
                ǀ_NULL______________ǀ

NEXT VALUE expression

>__NEXT VALUE FOR sequence-name_____________________________________________>

PREVIOUS VALUE expression

>__PREVIOUS VALUE FOR sequence-name_________________________________________>

ROW CHANGE expression

>_ROW CHANGE____TIMESTAMP____FOR__table-designator__________________________>
              ǀ_TOKEN_____ǀ

OLAP

>____ordered-OLAP-specification_____________________________________________>
    ǀ_numbering-specification___ǀ
    ǀ_aggregation-specification_ǀ

ordered-OLAP-specification

>__RANK________(_)_OVER_(_____________________________window-order-clause_)_>
  ǀ_DENSE_RANK_(_)ǀ        ǀ_window-partition-clause_ǀ

numbering specification:

>__ROW_NUMBER___()_OVER_(_____________________________window-order-clause_)_>
                           ǀ_window-partition-clause_ǀ

aggregation specification:

>_aggregate_function_()_OVER_(________________________window-order-clause_)_>
                               ǀ_window-partition-clause_ǀ
     __RANGE BETWEEN UNBOUNDED PRECEDING AND UNBOUND FOLLOWING____________
>___ǀ_____________________________________________________________________ǀ__>
    ǀ               _RANGE BETWEEN UNBOUNDED PRECEEDING AND CURRENT ROW_ ǀ
    ǀ_window-order_ǀ___________________________________________________ǀ_)ǀ
                   ǀ_ window-aggregation-group-clause__________________ǀ

window-partition-clause:

              <_,_____________________
>_PARTITION BY__partition-expression__ǀ____________________________________>

window-order-clause:

              <_,_________________________________________________
                                                _NULLS LAST_      ǀ
                                         _ASC__ǀ____________ǀ___  ǀ
>_ORDER BY____sort-key-expression_______ǀ_______________________ǀ_ǀ________>
                                        ǀ_ASC NULLS FIRST______ǀ
                                        ǀ       _NULLS FIRST_  ǀ
                                        ǀ_DESC_ǀ_____________ǀ_ǀ
                                        ǀ_DESC NULLS LAST______ǀ

aggregate function:

>___AVG FUNCTION___________________________________________________________>
  ǀ_CORRELATION function_ǀ
  ǀ_COUNT function_______ǀ
  ǀ_COUNT_BIG function___ǀ
  ǀ_COVARIANCE function__ǀ
  ǀ_MAX function_________ǀ
  ǀ_MIN function_________ǀ
  ǀ_STDDEV function______ǀ
  ǀ_SUM function_________ǀ
  ǀ_VARIANCE function____ǀ

window-aggregation-group-clause

>___ROWS______________group-start_________________________________________>
  ǀ_RANGE_ǀ         ǀ_group-between_ǀ
                    ǀ_group-end_____ǀ

group-start

>___UNBOUNDED PRECEDING___________________________________________________>
  ǀ_unsigned-constant_PRECEDING_ǀ
  ǀ_CURRENT ROW_________________ǀ

group-between

>___BETWEEN__group-bound-1__AND__group-bound-2____________________________>

group-bound-1

>___UNBOUNDED PRECEDING___________________________________________________>
  ǀ_unsigned-constant_PRECEDING_ǀ
  ǀ_unsigned-constant_FOLLOWING_ǀ
  ǀ_CURRENT ROW_________________ǀ

group-end

>___UNBOUNDED FOLLOWING___________________________________________________>
  ǀ_unsigned-constant_FOLLOWING_ǀ

Predicates

>________basic predicate__________________________________________________>
       ǀ_quantified predicate___ǀ
       ǀ_ARRAY_EXISTS predicate_ǀ
       ǀ_BETWEEN predicate______ǀ
       ǀ_DISTINCT predicate_____ǀ
       ǀ_EXISTS predicate_______ǀ
       ǀ_IN predicate___________ǀ
       ǀ_LIKE predicate_________ǀ
       ǀ_NULL predicate_________ǀ
       ǀ_XMLEXISTS predicate____ǀ

Basic Predicate

>>__ _expression_ _=______ _expression__________________________ __________><
    ǀ            ǀ_<>_____ǀ                                     ǀ
    ǀ            ǀ_<______ǀ                                     ǀ
    ǀ            ǀ_>______ǀ                                     ǀ
    ǀ            ǀ_<=_____ǀ                                     ǀ
    ǀ            ǀ_>=_____ǀ                                     ǀ
    ǀ                                                           ǀ
    ǀ__row-value-expression___ _=____ ___row-value-expression___ǀ
                              ǀ_<>___ǀ
                              ǀ_<____ǀ
                              ǀ_>____ǀ
                              ǀ_<=___ǀ
                              ǀ_>=___ǀ

Quantified Predicate

  >>__ _expression__ _=______ __ _SOME_ __(fullselect1)____ _______________><
      ǀ             ǀ        ǀ  ǀ_ANY__ǀ                   ǀ
      ǀ             ǀ_<>_____ǀ  ǀ_ALL__ǀ                   ǀ
      ǀ             ǀ_<______ǀ                             ǀ
      ǀ             ǀ_>______ǀ                             ǀ
      ǀ             ǀ_<=_____ǀ                             ǀ
      ǀ             ǀ_>=_____ǀ                             ǀ
      ǀ_( row-value-expression)=_ _SOME_ _(fullselect2)___ ǀ
      ǀ                          ǀ_ANY_ǀ                   ǀ
      ǀ_( row-value-expression)___<>______ALL(fullselect2)_ǀ

ARRAY_EXISTS predicate

>__ARRAY_EXISTS_(array-expression, array-index)___________________________><

BETWEEN predicate

>__expression___________BETWEEN__expression__AND__expression______________><
                ǀ_NOT_ǀ

DISTINCT predicate

>__expression_IS________DISTINCT FROM__expression_________________________><
  ǀ             ǀ_NOT_ǀ                                                  ǀ
  ǀ_(row-value-expression)_IS______DISTINCT FROM_(row-value-expression)__ǀ          
                             ǀ_NOT_ǀ

EXISTS predicate

>__EXISTS(fullselect)_____________________________________________________><

IN predicate

  >>__ _expression1__ _____ __IN_ _(fullselect1)_________ __ _____________><
      ǀ              ǀ_NOT_ǀ     ǀ   <_,_____________    ǀ  ǀ
      ǀ                          ǀ_(____expression2__ǀ_)_ǀ  ǀ
      ǀ_(row-value-expression)__ _____ __IN__(fullselect2)__ǀ
                                ǀ_NOT_ǀ

LIKE predicate

>__match-expression___________LIKE__pattern-expression____________________>
                     ǀ_NOT_ǀ
>_________________________________________________________________________>
    ǀ_ESCAPE__escape-expression_ǀ

NULL predicate

>__expression__IS_____________NULL________________________________________>
                    ǀ_NOT_ǀ

XMLEXISTS

>_XMLEXISTS_(_xquery-expression-constant__________________________________>
>__________________________________________________)______________________><
           ǀ        _BY REF_                   ǀ
           ǀ_PASSING_ǀ______ǀxquery-argumentǀ__ǀ

xquery-argument

>__xquery-context-item-expression_________________________________________><
  ǀ_xquery-context-item-expression_AS_identifier

Search conditions

>__________predicate______________________________________________________>
   ǀ_NOT_ǀ          ǀ                ǀ_SELECTIVITY_numeric-constant_ǀ  ǀ
                    ǀ_(search-condition)_______________________________ǀ
    <____________________________________________
>________________________________________________ǀ_______________________>
         ǀ___AND_______________predicate__________ǀ
           ǀ_OR__ǀ  ǀ_NOT_ǀ  ǀ_(search-conditon)_ǀ

Disclaimer: This Db2® 12 for z/OS Reference Guide was developed to help users in their daily activities in administrating and programming in Db2 for z/OS. There are no guarantees expressed or implied with the contents in this guide. We want to provide a quality and useful reference for users. Please notify us of any mistakes or errors in this reference guide at blogs@bmc.com. Db2 is a registered trademark of the IBM Corporation.

Order now! Get your free Db2 collateral from BMC!

Get your free Reference Guide and Catalog Tables Poster for z/OS from BMC. This collateral helps Db2 for z/OS users with daily activities in administering and programming for Db2 on z/OS.


These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.

See an error or have a suggestion? Please let us know by emailing blogs@bmc.com.

BMC Brings the A-Game

BMC works with 86% of the Forbes Global 50 and customers and partners around the world to create their future. With our history of innovation, industry-leading automation, operations, and service management solutions, combined with unmatched flexibility, we help organizations free up time and space to become an Autonomous Digital Enterprise that conquers the opportunities ahead.
Learn more about BMC ›

About the author

Stephen Watts

Stephen Watts (Birmingham, AL) contributes to a variety of publications including, Search Engine Journal, ITSM.Tools, IT Chronicles, DZone, and CompTIA.