Auf einen Blick: Sprachelemente in Db2 12 behandeln wesentliche Programmierkomponenten wie spezielle Register, Host-Variablen, Funktionen und Ausdrücke, mit Beispielen für die korrekte Verwendung der Syntax.
- Erklärt spezielle Register und deren Anwendungsbereiche.
- Beschreibt verschiedene Funktionen und Ausdrücke mit Syntaxbeispielen.
Sonderregister
>>__ _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-Variablen
>__:host-identifier_____________________________________________________>
ǀ _INDICATOR_ ǀ
ǀ__ǀ___________ǀ_:host-identifier_ǀ
In Java lautet die Syntax der Host-Variable:
>__:__________java-identifier___________________________________________>
ǀ_IN____ǀ ǀ_(java_expression_)_ǀ ǀ _INDICATOR_ ǀ
ǀ_OUT___ǀ ǀ_ǀ_________ǀ_:_Java-identifier_ǀ
ǀ_INOUT_ǀ
In PL/I, C und COBOL lautet die Syntax der Host-Variable:
>__:_________________host-identifier____________________________________>
ǀ_host-identifier._ǀ
>_______________________________________________________________________>
ǀ _INDICATOR_ ǀ
ǀ___ǀ_____________ǀ_:________________________host-identifier_ǀ
ǀ_host-identifier._ǀ
Funktionen
>__function-name__(__________________________________________________)_____>
ǀ_ALL______ǀ ǀ <_,_________________________________ ǀ
ǀ_DISTINCT_ǀ ǀ_____expression_____________________ǀ_ǀ
|_TABLE__transition-table-name_ǀ
Tabellenfunktion
>_TABLE_(function_name__(________________________________))_correlation_clause__>
ǀ <_,____________________________ ǀ
ǀ_ǀ_expression__________________ǀ_ǀ
ǀ_TABLE_transition_table_name_ǀ
Ausdrucksformen
<_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_________________________________________________________________>
ǀ_ǀǀ_____ǀ
ǀ_/______ǀ
ǀ_*______ǀ
ǀ_+______ǀ
ǀ_-______ǀ
Beschriftete Dauern
>_____function-invocation____YEAR______________________________________>
ǀ_(expression)___ǀ ǀ_YEARS_________ǀ
ǀ_constant_______ǀ ǀ_MONTH_________ǀ
ǀ_column-name____ǀ ǀ_MONTHS________ǀ
ǀ_variable_______ǀ ǀ_DAY___________ǀ
ǀ_DAYS__________ǀ
ǀ_HOUR__________ǀ
ǀ_HOURS_________ǀ
ǀ_MINUTE________ǀ
ǀ_MINUTES_______ǀ
ǀ_SECOND________ǀ
ǀ_SECONDS_______ǀ
ǀ_MICROSECOND___ǀ
ǀ_MICROSECONDS__ǀ
KASUS-Ausdrücke
_ELSE NULL_______________
>__CASE___searched-when-clause___ǀ_________________________ǀ___________>
ǀ_simple-when-clause___ǀ ǀ_ELSE__result-expression_ǀ
>__END_________________________________________________________________>
Gesuchte-wann-Klausel:
<_________________________________________________________
>______WHEN____search-condition__THEN____result-expression____ǀ________>
einfach-wann-Klausel:
<______________________________________________
>_____expression______WHEN__expression__THEN____result-expression__ǀ___>
Besetzung
>__CAST__(____expression___________AS__data-type__)____________________>
ǀ_NULL_____________ǀ
ǀ_parameter-marker_ǀ
Datentyp:
>_______built-in-data-type_____________________________________________>
ǀ_distinct-type-name_ǀ
ǀ_array-type_________ǀ
Eingebauter Datentyp:
>__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______________________________________________________________________________ǀ
Länge:
>_integer_______________________________________________________________________>
ǀ_CODEUNITS16_ǀ
ǀ_CODEUNITS32_ǀ
ǀ_OCTETS______ǀ
Lob-Länge:
>_integer_______________________________________________________________________>
ǀ_K_ǀ ǀ_CODEUNITS16_ǀ
ǀ_M_ǀ ǀ_CODEUNITS32_ǀ
ǀ_G_ǀ ǀ_OCTETS______ǀ
XMLCAST-Spezifikation
>__XMLCAST_(__expression____________AS__data-type_______________________________>
ǀ_NULL_____________ǀ
ǀ_parameter-marker_ǀ
Array-Expression
>__array-expression__(array-index)__________________________________________>
Array-Konstruktor
>__ARRAY_[_______________________________]__________________________________>
ǀ_fullselect_______________ǀ
ǀ _,___________________ ǀ
ǀ ǀ ǀ ǀ
ǀ V ǀ ǀ
ǀ___element-expression__ǀ__ǀ
ǀ_NULL______________ǀ
NÄCHSTER WERT-Ausdruck
>__NEXT VALUE FOR sequence-name_____________________________________________>
VORHERIGER WERT-Ausdruck
>__PREVIOUS VALUE FOR sequence-name_________________________________________>
ZEILENWECHSEL-Ausdruck
>_ROW CHANGE____TIMESTAMP____FOR__table-designator__________________________>
ǀ_TOKEN_____ǀ
OLAP
>____ordered-OLAP-specification_____________________________________________>
ǀ_numbering-specification___ǀ
ǀ_aggregation-specification_ǀ
geordnet-OLAP-Spezifikation
>__RANK________(_)_OVER_(_____________________________window-order-clause_)_>
ǀ_DENSE_RANK_(_)ǀ ǀ_window-partition-clause_ǀ
Nummerierungsspezifikation:
>__ROW_NUMBER___()_OVER_(_____________________________window-order-clause_)_>
ǀ_window-partition-clause_ǀ
Aggregationsspezifikation:
>_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__ǀ____________________________________>
Fenster-Ordnung-Klausel:
<_,_________________________________________________
_NULLS LAST_ ǀ
_ASC__ǀ____________ǀ___ ǀ
>_ORDER BY____sort-key-expression_______ǀ_______________________ǀ_ǀ________>
ǀ_ASC NULLS FIRST______ǀ
ǀ _NULLS FIRST_ ǀ
ǀ_DESC_ǀ_____________ǀ_ǀ
ǀ_DESC NULLS LAST______ǀ
aggregierte Funktion:
>___AVG FUNCTION___________________________________________________________>
ǀ_CORRELATION function_ǀ
ǀ_COUNT function_______ǀ
ǀ_COUNT_BIG function___ǀ
ǀ_COVARIANCE function__ǀ
ǀ_MAX function_________ǀ
ǀ_MIN function_________ǀ
ǀ_STDDEV function______ǀ
ǀ_SUM function_________ǀ
ǀ_VARIANCE function____ǀ
Fenster-Aggregations-Gruppen-Klausel
>___ROWS______________group-start_________________________________________>
ǀ_RANGE_ǀ ǀ_group-between_ǀ
ǀ_group-end_____ǀ
Gruppenstart
>___UNBOUNDED PRECEDING___________________________________________________>
ǀ_unsigned-constant_PRECEDING_ǀ
ǀ_CURRENT ROW_________________ǀ
Gruppen-Zwischengruppe
>___BETWEEN__group-bound-1__AND__group-bound-2____________________________>
Gruppengebunden-1
>___UNBOUNDED PRECEDING___________________________________________________>
ǀ_unsigned-constant_PRECEDING_ǀ
ǀ_unsigned-constant_FOLLOWING_ǀ
ǀ_CURRENT ROW_________________ǀ
Group-End
>___UNBOUNDED FOLLOWING___________________________________________________>
ǀ_unsigned-constant_FOLLOWING_ǀ
Prädikate
>________basic predicate__________________________________________________>
ǀ_quantified predicate___ǀ
ǀ_ARRAY_EXISTS predicate_ǀ
ǀ_BETWEEN predicate______ǀ
ǀ_DISTINCT predicate_____ǀ
ǀ_EXISTS predicate_______ǀ
ǀ_IN predicate___________ǀ
ǀ_LIKE predicate_________ǀ
ǀ_NULL predicate_________ǀ
ǀ_XMLEXISTS predicate____ǀ
Grundprädikat
>>__ _expression_ _=______ _expression__________________________ __________><
ǀ ǀ_<>_____ǀ ǀ
ǀ ǀ_<______ǀ ǀ
ǀ ǀ_>______ǀ ǀ
ǀ ǀ_<=_____ǀ ǀ
ǀ ǀ_>=_____ǀ ǀ
ǀ ǀ
ǀ__row-value-expression___ _=____ ___row-value-expression___ǀ
ǀ_<>___ǀ
ǀ_<____ǀ
ǀ_>____ǀ
ǀ_<=___ǀ
ǀ_>=___ǀ
Quantifiziertes Prädikat
>>__ _expression__ _=______ __ _SOME_ __(fullselect1)____ _______________><
ǀ ǀ ǀ ǀ_ANY__ǀ ǀ
ǀ ǀ_<>_____ǀ ǀ_ALL__ǀ ǀ
ǀ ǀ_<______ǀ ǀ
ǀ ǀ_>______ǀ ǀ
ǀ ǀ_<=_____ǀ ǀ
ǀ ǀ_>=_____ǀ ǀ
ǀ_( row-value-expression)=_ _SOME_ _(fullselect2)___ ǀ
ǀ ǀ_ANY_ǀ ǀ
ǀ_( row-value-expression)___<>______ALL(fullselect2)_ǀ
ARRAY_EXISTS Prädikat
>__ARRAY_EXISTS_(array-expression, array-index)___________________________><
BETWEEN-Prädikat
>__expression___________BETWEEN__expression__AND__expression______________><
ǀ_NOT_ǀ
UNTERSCHIEDLICHES Prädikat
>__expression_IS________DISTINCT FROM__expression_________________________><
ǀ ǀ_NOT_ǀ ǀ
ǀ_(row-value-expression)_IS______DISTINCT FROM_(row-value-expression)__ǀ
ǀ_NOT_ǀ
EXISTIERT Prädikat
>__EXISTS(fullselect)_____________________________________________________><
IN-Prädikat
>>__ _expression1__ _____ __IN_ _(fullselect1)_________ __ _____________><
ǀ ǀ_NOT_ǀ ǀ <_,_____________ ǀ ǀ
ǀ ǀ_(____expression2__ǀ_)_ǀ ǀ
ǀ_(row-value-expression)__ _____ __IN__(fullselect2)__ǀ
ǀ_NOT_ǀ
LIKE-Prädikat
>__match-expression___________LIKE__pattern-expression____________________>
ǀ_NOT_ǀ
>_________________________________________________________________________>
ǀ_ESCAPE__escape-expression_ǀ
NULL-Prädikat
>__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_ǀ
Suchbedingungen
>__________predicate______________________________________________________>
ǀ_NOT_ǀ ǀ ǀ_SELECTIVITY_numeric-constant_ǀ ǀ
ǀ_(search-condition)_______________________________ǀ
<____________________________________________
>________________________________________________ǀ_______________________>
ǀ___AND_______________predicate__________ǀ
ǀ_OR__ǀ ǀ_NOT_ǀ ǀ_(search-conditon)_ǀ
Haftungsausschluss: Dieser Db2® 12 für z/OS Referenzleitfaden wurde entwickelt, um Nutzern bei ihren täglichen Aktivitäten bei der Verwaltung und Programmierung in Db2 für z/OS zu helfen. Mit den Inhalten dieses Leitfadens gibt es keine ausdrücklich oder stillschweigenden Garantien. Wir möchten den Nutzern eine qualitativ hochwertige und nützliche Referenz bereitstellen. Bitte informieren Sie uns über Fehler oder Fehler in diesem Referenzhandbuch unter blogs@bmc.com. Db2 ist eine eingetragene Marke der IBM Corporation.
Db2-Leitfaden
- Db2 12 für z/OS: Eine Einführung
- Skalare Funktionen in Db2 12
- Wie man IBM<sup>®</sup> Syntaxdiagramme für Db2 12 liest
- Sprachelemente in Db2 12
- Aggregierte Funktionen in Db2 12
- Tabellen- und Zeilenfunktionen in Db2 12
- Anfragen in Db2 12
- Befehle in Db2 12
- SQL-Kontrollanweisungen in Db2 12
- Erkläre Tabellen in Db2 12
- DSNZPARMs in Db2 12
- Bindeparameter in Db2 12
- Db2-Grenzen in Db2 12
- SQL Kommunikationsbereich (SQLCA) in Db2 12
- REXX SQLCA in Db2 12
- Diagnoses in Db2 12 erhalten
- IFCIDS in Db2 12
- Katalogtabellen in Db2 12
- Ausnahmen in Db2 12
- XML-Schema-Repository-Tabellen in Db2 12
- Aktualisierungsfähige Katalogstatistiken in Db2 12
- IBM Utilities in Db2 12
- SQL Positive Return-Codes in Db2 12
- SQL-Fehlerrückgabecodes in Db2 12
Diese Beiträge sind meine eigenen und spiegeln nicht unbedingt die Position, Strategien oder Meinung von BMC wider.