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____________________________ǀ
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.
