TL;DR Summary: Language Elements in Db2 12 covers essential programming components such as special registers, host variables, functions, and expressions, with examples for proper syntax usage.
- Explains special registers and their applications.
- Details various functions and expressions with syntax examples.
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.
Db2 Guide
- Db2 12 for z/OS: An Introduction
- Scalar Functions in Db2 12
- How to Read IBM<sup>®</sup> Syntax Diagrams for Db2 12
- Language Elements in Db2 12
- Aggregate Functions in Db2 12
- Table & Row Functions in Db2 12
- Queries in Db2 12
- Commands in Db2 12
- SQL Control Statements in Db2 12
- Explain Tables in Db2 12
- DSNZPARMs in Db2 12
- Bind Parameters in Db2 12
- Db2 Limits in Db2 12
- SQL Communication Area (SQLCA) in Db2 12
- REXX SQLCA in Db2 12
- Get Diagnostics in Db2 12
- IFCIDS in Db2 12
- Catalog Tables in Db2 12
- Exceptions in Db2 12
- XML Schema Repository Tables in Db2 12
- Updatable Catalog Statistics in Db2 12
- IBM Utilities in Db2 12
- SQL Positive Return Codes in Db2 12
- SQL Error Return Codes in Db2 12
These postings are my own and do not necessarily represent BMC's position, strategies, or opinion.