Puntos clave: Elementos del lenguaje en Db2 12 cubre componentes esenciales de programación como registros especiales, variables anfitriones, funciones y expresiones, con ejemplos para el uso adecuado de la sintaxis.
- Explica los registros especiales y sus aplicaciones.
- Detalla diversas funciones y expresiones con ejemplos de sintaxis.
Registros especiales
>>__ _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____________________________ǀ
Variables anfitrionas
>__:host-identifier_____________________________________________________>
ǀ _INDICATOR_ ǀ
ǀ__ǀ___________ǀ_:host-identifier_ǀ
En Java, la sintaxis de la variable anfitriona está:
>__:__________java-identifier___________________________________________>
ǀ_IN____ǀ ǀ_(java_expression_)_ǀ ǀ _INDICATOR_ ǀ
ǀ_OUT___ǀ ǀ_ǀ_________ǀ_:_Java-identifier_ǀ
ǀ_INOUT_ǀ
En PL/I, C y COBOL, la sintaxis de la variable anfitriona están:
>__:_________________host-identifier____________________________________>
ǀ_host-identifier._ǀ
>_______________________________________________________________________>
ǀ _INDICATOR_ ǀ
ǀ___ǀ_____________ǀ_:________________________host-identifier_ǀ
ǀ_host-identifier._ǀ
Funciones
>__function-name__(__________________________________________________)_____>
ǀ_ALL______ǀ ǀ <_,_________________________________ ǀ
ǀ_DISTINCT_ǀ ǀ_____expression_____________________ǀ_ǀ
|_TABLE__transition-table-name_ǀ
Función de la tabla
>_TABLE_(function_name__(________________________________))_correlation_clause__>
ǀ <_,____________________________ ǀ
ǀ_ǀ_expression__________________ǀ_ǀ
ǀ_TABLE_transition_table_name_ǀ
Expresiones
<_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_________________________________________________________________>
ǀ_ǀǀ_____ǀ
ǀ_/______ǀ
ǀ_*______ǀ
ǀ_+______ǀ
ǀ_-______ǀ
Duraciones etiquetadas
>_____function-invocation____YEAR______________________________________>
ǀ_(expression)___ǀ ǀ_YEARS_________ǀ
ǀ_constant_______ǀ ǀ_MONTH_________ǀ
ǀ_column-name____ǀ ǀ_MONTHS________ǀ
ǀ_variable_______ǀ ǀ_DAY___________ǀ
ǀ_DAYS__________ǀ
ǀ_HOUR__________ǀ
ǀ_HOURS_________ǀ
ǀ_MINUTE________ǀ
ǀ_MINUTES_______ǀ
ǀ_SECOND________ǀ
ǀ_SECONDS_______ǀ
ǀ_MICROSECOND___ǀ
ǀ_MICROSECONDS__ǀ
Expresiones CASE
_ELSE NULL_______________
>__CASE___searched-when-clause___ǀ_________________________ǀ___________>
ǀ_simple-when-clause___ǀ ǀ_ELSE__result-expression_ǀ
>__END_________________________________________________________________>
cláusula de búsqueda cuando:
<_________________________________________________________
>______WHEN____search-condition__THEN____result-expression____ǀ________>
cláusula simple-cuando-de-TODO:
<______________________________________________
>_____expression______WHEN__expression__THEN____result-expression__ǀ___>
Reparto
>__CAST__(____expression___________AS__data-type__)____________________>
ǀ_NULL_____________ǀ
ǀ_parameter-marker_ǀ
Tipo de datos:
>_______built-in-data-type_____________________________________________>
ǀ_distinct-type-name_ǀ
ǀ_array-type_________ǀ
Tipo de dato incorporado:
>__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______________________________________________________________________________ǀ
Longitud:
>_integer_______________________________________________________________________>
ǀ_CODEUNITS16_ǀ
ǀ_CODEUNITS32_ǀ
ǀ_OCTETS______ǀ
longitud del globo:
>_integer_______________________________________________________________________>
ǀ_K_ǀ ǀ_CODEUNITS16_ǀ
ǀ_M_ǀ ǀ_CODEUNITS32_ǀ
ǀ_G_ǀ ǀ_OCTETS______ǀ
Especificación XMLCAST
>__XMLCAST_(__expression____________AS__data-type_______________________________>
ǀ_NULL_____________ǀ
ǀ_parameter-marker_ǀ
Expresión en matriz
>__array-expression__(array-index)__________________________________________>
Constructor de matrices
>__ARRAY_[_______________________________]__________________________________>
ǀ_fullselect_______________ǀ
ǀ _,___________________ ǀ
ǀ ǀ ǀ ǀ
ǀ V ǀ ǀ
ǀ___element-expression__ǀ__ǀ
ǀ_NULL______________ǀ
SIGUIENTE EXPRESIÓN DE VALOR
>__NEXT VALUE FOR sequence-name_____________________________________________>
EXPRESIÓN DE VALOR ANTERIOR
>__PREVIOUS VALUE FOR sequence-name_________________________________________>
EXPRESIÓN DE CAMBIO DE FILA
>_ROW CHANGE____TIMESTAMP____FOR__table-designator__________________________>
ǀ_TOKEN_____ǀ
OLAP
>____ordered-OLAP-specification_____________________________________________>
ǀ_numbering-specification___ǀ
ǀ_aggregation-specification_ǀ
especificación ordered-OLAP
>__RANK________(_)_OVER_(_____________________________window-order-clause_)_>
ǀ_DENSE_RANK_(_)ǀ ǀ_window-partition-clause_ǀ
Especificación de numeración:
>__ROW_NUMBER___()_OVER_(_____________________________window-order-clause_)_>
ǀ_window-partition-clause_ǀ
Especificación de agregación:
>_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__________________ǀ
cláusula-partición-ventana:
<_,_____________________
>_PARTITION BY__partition-expression__ǀ____________________________________>
cláusula de orden de ventana:
<_,_________________________________________________
_NULLS LAST_ ǀ
_ASC__ǀ____________ǀ___ ǀ
>_ORDER BY____sort-key-expression_______ǀ_______________________ǀ_ǀ________>
ǀ_ASC NULLS FIRST______ǀ
ǀ _NULLS FIRST_ ǀ
ǀ_DESC_ǀ_____________ǀ_ǀ
ǀ_DESC NULLS LAST______ǀ
Función agregada:
>___AVG FUNCTION___________________________________________________________>
ǀ_CORRELATION function_ǀ
ǀ_COUNT function_______ǀ
ǀ_COUNT_BIG function___ǀ
ǀ_COVARIANCE function__ǀ
ǀ_MAX function_________ǀ
ǀ_MIN function_________ǀ
ǀ_STDDEV function______ǀ
ǀ_SUM function_________ǀ
ǀ_VARIANCE function____ǀ
cláusula-grupo-agregación de ventanas
>___ROWS______________group-start_________________________________________>
ǀ_RANGE_ǀ ǀ_group-between_ǀ
ǀ_group-end_____ǀ
Salida de grupo
>___UNBOUNDED PRECEDING___________________________________________________>
ǀ_unsigned-constant_PRECEDING_ǀ
ǀ_CURRENT ROW_________________ǀ
Grupo entre
>___BETWEEN__group-bound-1__AND__group-bound-2____________________________>
Grupo-bound-1
>___UNBOUNDED PRECEDING___________________________________________________>
ǀ_unsigned-constant_PRECEDING_ǀ
ǀ_unsigned-constant_FOLLOWING_ǀ
ǀ_CURRENT ROW_________________ǀ
Extremo de grupo
>___UNBOUNDED FOLLOWING___________________________________________________>
ǀ_unsigned-constant_FOLLOWING_ǀ
Predicados
>________basic predicate__________________________________________________>
ǀ_quantified predicate___ǀ
ǀ_ARRAY_EXISTS predicate_ǀ
ǀ_BETWEEN predicate______ǀ
ǀ_DISTINCT predicate_____ǀ
ǀ_EXISTS predicate_______ǀ
ǀ_IN predicate___________ǀ
ǀ_LIKE predicate_________ǀ
ǀ_NULL predicate_________ǀ
ǀ_XMLEXISTS predicate____ǀ
Predicado básico
>>__ _expression_ _=______ _expression__________________________ __________><
ǀ ǀ_<>_____ǀ ǀ
ǀ ǀ_<______ǀ ǀ
ǀ ǀ_>______ǀ ǀ
ǀ ǀ_<=_____ǀ ǀ
ǀ ǀ_>=_____ǀ ǀ
ǀ ǀ
ǀ__row-value-expression___ _=____ ___row-value-expression___ǀ
ǀ_<>___ǀ
ǀ_<____ǀ
ǀ_>____ǀ
ǀ_<=___ǀ
ǀ_>=___ǀ
Predicado cuantificado
>>__ _expression__ _=______ __ _SOME_ __(fullselect1)____ _______________><
ǀ ǀ ǀ ǀ_ANY__ǀ ǀ
ǀ ǀ_<>_____ǀ ǀ_ALL__ǀ ǀ
ǀ ǀ_<______ǀ ǀ
ǀ ǀ_>______ǀ ǀ
ǀ ǀ_<=_____ǀ ǀ
ǀ ǀ_>=_____ǀ ǀ
ǀ_( row-value-expression)=_ _SOME_ _(fullselect2)___ ǀ
ǀ ǀ_ANY_ǀ ǀ
ǀ_( row-value-expression)___<>______ALL(fullselect2)_ǀ
ARRAY_EXISTS predicado
>__ARRAY_EXISTS_(array-expression, array-index)___________________________><
Predicado BETWEEN
>__expression___________BETWEEN__expression__AND__expression______________><
ǀ_NOT_ǀ
Predicado DISTINTO
>__expression_IS________DISTINCT FROM__expression_________________________><
ǀ ǀ_NOT_ǀ ǀ
ǀ_(row-value-expression)_IS______DISTINCT FROM_(row-value-expression)__ǀ
ǀ_NOT_ǀ
Predicado EXISTS
>__EXISTS(fullselect)_____________________________________________________><
Predicado IN
>>__ _expression1__ _____ __IN_ _(fullselect1)_________ __ _____________><
ǀ ǀ_NOT_ǀ ǀ <_,_____________ ǀ ǀ
ǀ ǀ_(____expression2__ǀ_)_ǀ ǀ
ǀ_(row-value-expression)__ _____ __IN__(fullselect2)__ǀ
ǀ_NOT_ǀ
Predicado LIKE
>__match-expression___________LIKE__pattern-expression____________________>
ǀ_NOT_ǀ
>_________________________________________________________________________>
ǀ_ESCAPE__escape-expression_ǀ
Predicado NULL
>__expression__IS_____________NULL________________________________________>
ǀ_NOT_ǀ
XMLEXISTS
>_XMLEXISTS_(_xquery-expression-constant__________________________________>
>__________________________________________________)______________________><
ǀ _BY REF_ ǀ
ǀ_PASSING_ǀ______ǀxquery-argumentǀ__ǀ
argumento xquery
>__xquery-context-item-expression_________________________________________><
ǀ_xquery-context-item-expression_AS_identifier_ǀ
Condiciones de búsqueda
>__________predicate______________________________________________________>
ǀ_NOT_ǀ ǀ ǀ_SELECTIVITY_numeric-constant_ǀ ǀ
ǀ_(search-condition)_______________________________ǀ
<____________________________________________
>________________________________________________ǀ_______________________>
ǀ___AND_______________predicate__________ǀ
ǀ_OR__ǀ ǀ_NOT_ǀ ǀ_(search-conditon)_ǀ
Aviso legal: Esta Guía de Referencia de Db2® 12 para z/OS fue desarrollada para ayudar a los usuarios en sus actividades diarias en la administración y programación en Db2 para z/OS. No son garantías expresadas ni implícitas con el contenido de esta guía. Queremos proporcionar una referencia de calidad y útil para los usuarios. Por favor, notifícanos cualquier error o error en esta guía de referencia en blogs@bmc.com. Db2 están una marca registrada de IBM Corporation.
Guía Db2
- Db2 12 para z/OS: Una introducción
- Funciones escalares en Db2 12
- Cómo leer diagramas sintácticos de IBM<sup>®</sup> para Db2 12
- Elementos del lenguaje en Db2 12
- Funciones agregadas en Db2 12
- Funciones de tabla y fila en Db2 12
- Consultas en Db2 12
- Comandos en Db2 12
- Sentencias de control SQL en Db2 12
- Explica las tablas en Db2 12
- DSNZPARM en Db2 12
- Parámetros de enlace en Db2 12
- Límites de Db2 en Db2 12
- Área de Comunicación SQL (SQLCA) en Db2 12
- REXX SQLCA en Db2 12
- Obtén diagnósticos en Db2 12
- IFCIDS en Db2 12
- Tablas de catálogo en Db2 12
- Excepciones en Db2 12
- Tablas del Repositorio de Esquemas XML en Db2 12
- Estadísticas actualizables del catálogo en Db2 12
- IBM Utilities en Db2 12
- Códigos de retorno positivo SQL en Db2 12
- Códigos de retorno de errores SQL en Db2 12
Estas publicaciones son mías y no representan necesariamente la posición, estrategias u opinión de BMC.