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 [email protected]. 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 [email protected].

Business, Faster than Humanly Possible

BMC empowers 86% of the Forbes Global 50 to accelerate business value faster than humanly possible. Our industry-leading portfolio unlocks human and machine potential to drive business growth, innovation, and sustainable success. BMC does this in a simple and optimized way by connecting people, systems, and data that power the world’s largest organizations so they can seize a competitive advantage.
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.