Mainframe Blog

Scalar Functions in Db2 12

4 minute read
Stephen Watts

(This article is part of our Db2 Guide. Use the right-hand menu to navigate.)

ABS

>__ABS(numeric-expression)________________________________________________>

ACOS

>__ACOS(numeric-expression)_______________________________________________>

ADD_MONTHS

>__ADD_MONTHS(expression,numeric-expression)______________________________>

ARRAY_DELETE

>__ARRAY_DELETE(array-expression___________________________________)______>
ǀ_,_array-index1_________________ǀ
ǀ_,ǀ_array-index2_ǀ

ARRAY_FIRST

>__ARRAY_FIRST(array-expression___________________________________________>

ARRAY_LAST

>__ARRAY_LAST(array-expression____________________________________________>

ARRAY_NEXT

>__ARRAY_FIRST(array-expression, array-index)_____________________________>

ARRAY_PRIOR

>__ARRAY_PRIOR(array-expression, array-index)_____________________________>

ASCII

>_ASCII(string-expression)________________________________________________>

ASCII_CHR

>_ASCII_CHR(expression)___________________________________________________>

ASCII_STR

>_ASC11_CHR(string-expression)____________________________________________>

ASIN

>__ASIN(numeric-expression)_______________________________________________>

ATAN

>__ATAN(numeric-expression)_______________________________________________>

ATANH

>__ATANH(numeric-expression)_____________________________________________>

ATAN2

>__ATAN2(numeric-expression1,numeric-expression2)_________________________>

BIGINT

Numeric to Big Integer:

>_BIGINT(numeric-expression______________________________________________>

String to Big Integer:

>_BIGINT(string-expression_______________________________________________>

BINARY

>_BINARY(string-expression_________________)_____________________________>
ǀ_,_integer_ǀ

BITAND, BITANDNOT, BITOR, BITXOR and BITNOT

>__BITAND_____(_expression1__, expression2__)____________________________>
ǀ_BITANDNOT_ǀ
ǀ_BITOR_____ǀ
ǀ_BITXOR____ǀ
>_BITNOT(_expression_)___________________________________________________>

BLOB

>__BLOB(expression_______________)_______________________________________>
ǀ_,__integer_ǀ

CARDINALITY

>__CARDINALITY(array-expression)_________________________________________>

CCSID_ENCODING

>__CCSID_ENCODING(expression)____________________________________________>

CEILING

>__CEILING_______(__expression__)________________________________________>

CHAR

Datetime to Character:

>__CHAR(datetime-expression________________)_____________________________>
ǀ_,____ISO____ǀ
ǀ_USA___ǀ
ǀ_EUR___ǀ
ǀ_JIS___ǀ
ǀ_LOCAL_ǀ

Character to character:

>__CHAR(character-expression_______________________________)_____________>
ǀ_,_integer__________________ǀ
ǀ_,ǀ_CODEUNITS16_ǀ
ǀ_CODEUNITS32_ǀ
ǀ_OCTETS______ǀ

Graphic to character:

__CHAR(graphic-expression____________________________________)__________>
ǀ_,_integer__________________ǀ
ǀ_,ǀ_CODEUNITS16_ǀ
ǀ_CODEUNITS32_ǀ

Integer to character:

>__CHAR(integer-expression)______________________________________________>

Decimal to character:

>__CHAR(decimal-expression)___________________________)__________________>
ǀ_,__decimal-character_ǀ

Decimal floating-Point to Character:

>__CHAR(decimal-floating-point-expression)______________________________>

Floating-Point to Character:

>__CHAR(floating-point-expression)______________________________________>

Row ID to Character:

>__CHAR(row-ID-expression)______________________________________________>

CHAR9

>__CHAR9__(decimal-expression______________________________)____________>
ǀ_,_decimal-character__ǀ

CHARACTER_LENGTH

Character sting:

>__CHARACTER_LENGTH(character-expression__,_ CODEUNITS16_____)__________>
ǀ_CODEUNITS32_ǀ
ǀ_OCTETS______ǀ
Graphic string:
>__CHARACTER_LENGTH(graphic-expression__,____CODEUNITS16_____)__________>
ǀ_CODEUNITS32_ǀ

CLOB

Character to CLOB:

>__CLOB(character-expression___________________________________)________>
ǀ_,_integer_________________ǀ
ǀ_,ǀ_CODEUNITS16_ǀ
ǀ_CODEUNITS32_ǀ
ǀ_OCTETS______ǀ
GRAPHIC to CLOB:
>__CLOB(graphic-expression_____________________________________)________>
ǀ_,_integer_________________ǀ
ǀ_,ǀ_CODEUNITS16_ǀ
ǀ_CODEUNITS32_ǀ

COALESCE

                             <___________
>____COALESCE___(expression__,expressionǀ_)_____________________________>

COLLATION KEY

>__COLLATION_KEY(string-expression, collation-name______________)_______>
ǀ_,integer_ǀ

COMPARE_DECFLOAT

>__COMPARE_DECFLOAT(decfloat-expression1, decfloat-expression2__________>

CONCAT

>_____CONCAT____(expression1,expression2)_______________________________>
ǀ_"ǀǀ"____ǀ

CONTAINS

>__CONTAINS____(column-name, search-argument)___________________________>
ǀ_,string-constant_ǀ

Search-argument-options:

          <______________________
>________________________________ǀ______________________________________>
ǀ_QUERYLANGUAGE = value_ǀ
ǀ_RESULTLIMIT = value___ǀ
ǀ            _OFF_      ǀ
ǀ_SYNONYM = ǀ_ON__ǀ_____ǀ

COS

>__COS(numeric-expression)_______________________________________________>

COSH

>__COSH(numeric-expression)______________________________________________>

DATE

>__DATE(expression)______________________________________________________>

DAY

>__DAY(expression)_______________________________________________________>

DAYOFMONTH

>__DAYOFMONTH(expression)________________________________________________>

DAYOFWEEK

>__DAYOFWEEK(expression)_________________________________________________>

DAYOFWEEK_ISO

>__DAYOFWEEK_ISO(expression)_____________________________________________>

DAYOFYEAR

>__DAYOFYEAR(expression)_________________________________________________>

DAYS

>__DAYS(expression)______________________________________________________>

DBCLOB

Character to DBCLOB:

>__DBCLOB(character-expression__________________________________)________>
ǀ_,_integer__________________ǀ
ǀ_,ǀ_CODEUNITS16_ǀ
ǀ_CODEUNITS32_ǀ

GRAPHIC to DBCLOB:

>__DBCLOB(graphic-expression____________________________________)________>
ǀ_,_integer__________________ǀ
ǀ_,ǀ_CODEUNITS16_ǀ
ǀ_,ǀ_CODEUNITS32_ǀ

DECFLOAT

numeric to DECFLOAT:

                                   _,34_
>__DECFLOAT_(_numeric-expression__ǀ_____ǀ_______________________)________>
ǀ_,16_ǀ

string to DECFLOAT:

                                  _,34_
>__DECFLOAT_(_string-expression__ǀ_____ǀ________________________)________>
ǀ_,16_ǀ

DECFLOAT_FORMAT

>_____DECFLOAT_FORMAT___(___string-expression_____________________)______>
ǀ_,_format-string_ǀ

DECFLOAT_SORTKEY

>_DECFLOAT_SORTKEY_ (_decfloat-expression_)______________________________>

DECIMAL or DEC

Numeric to decimal:

>_____DECIMAL___(___numeric-expression_____________________________)_____>
ǀ_DEC_____ǀ                          ǀ_,precision___________ǀ
ǀ_scale_ǀ

String to decimal:

>_DECIMAL_(string-expression______________________________________)______>
ǀ_DEC__ǀ                    ǀ_,precision______________________ǀ
ǀ_,scale_____________ǀ
ǀ_,decimal-char_ǀ

DECODE

                       <__________________________________
>_DECODE_(_expression1__,_expression2_,_result-expression_ǀ______________>
>________________________________________________________________)_______>
ǀ_,_else-expression_ǀ

DECRYPT

>>___ _DECRYPT_BINARY__________(encrypted-data___________________________>
ǀ_DECRYPT_BIT___ǀ
ǀ_DECRYPT_CHAR__ǀ
ǀ_DECRYPT_DB____ǀ
>_____ __________________________________________________________)_______>
ǀ_,_ _password-string___________ _ _ ____________________ǀ
ǀ_DEFAULT___________________ǀ_ǀ_,__ccsid-constant_ǀ

DEGREES

>__DEGREES(expression)___________________________________________________>

DIFFERENCE

>__DIFFERENCE(expression1, expression2)__________________________________>

DIGITS

>__DIGITS(expression)____________________________________________________>

DOUBLE or DOUBLE_PRECISION

>__DOUBLE__________________(___numeric-expression___)____________________>
ǀ_DOUBLE_PRECISION_ǀ      ǀ_string-expression__ǀ

DSN_XMLVALIDATE

>__DSN_XMLVALIDATE_______________________________________________________>
>_(string-expression_,_schema-name-string________________________________>
ǀ_xml-expression_ǀ ǀtarget-namespace-uri-sting,schema-location-stringǀ

EBCDIC_CHR

>>__EBCDIC(expression)___________________________________________________>

EBCDIC_STR

>>__EBCDIC_STR(string-expression)________________________________________>

ENCRYPT_TDES

>>__ENCRYPT_TDES(data-string____________________________________)________>
ǀ_,password-string,hint-string

EXP

>__EXP(numeric-expression)_______________________________________________>

EXTRACT

Extract date values:

>__EXTRACT(__YEAR____)_FROM__date-expression__________)_________________>
ǀ_MONTH_ǀ       ǀ_timestamp-expression_ǀ
ǀ_DAY___ǀ

Extract time values:

>__EXTRACT(___HOUR____)_FROM__time-expression__________)________________>
ǀ_MINUTE_ǀ       ǀ_timestamp-expression_ǀ
ǀ_SECOND_ǀ

Extract time zone values:

>__EXTRACT(___HOUR____)________FROM__date-expression___________)________>
ǀ_MINUTE__________ǀ     ǀ_time-expression_______ǀ
ǀ_SECOND__________ǀ     ǀ_timestamp-expression__ǀ
ǀ_TIMEZONE_HOUR___ǀ
ǀ_TIMEZONE_MINUTE_ǀ

FLOAT

>__FLOAT(numeric-expression)____________________________________________>

LOWER

>__LOWER(string-expression____________________________________)_________>
ǀ_,_locale-name_ǀ ǀ_,_integer_ǀ

LPAD

>__LPAD(string-expression,integer______________)________________________>
ǀ_,_pad_ǀ

LTRIM

>__LTRIM(string-expression)______________________________)______________>
ǀ_,_trim-expression_ǀ

MAX

                   <___________
>__MAX(expression, expression1_ǀ_)______________________________________>

MAX CARDINALITY

>__MAX_CARDINALITY(array-expression)____________________________________>

MICROSECOND

>__MICROSECOND(expression)______________________________________________>

MIDNIGHT_SECONDS

>__MIDNIGHT_SECONDS(expression)_________________________________________>

MIN

                   <__________
>__MIN(expression, expression_ǀ_)_______________________________________>

MINUTE

>__MINUTE(expression)___________________________________________________>

MOD

>__MOD(numeric-expression1,numeric-expression2)_________________________>

MONTH

>__MONTH(expression)____________________________________________________>

MONTHS_BETWEEN

>__MONTHS_BETWEEN(expression1, expression2)_____________________________>

MULTIPLY_ALT

>__MULTIPLY_ALT(exact-numeric-expression1,exact-numeric-expression2)____>

NEXT_DAY

>__NEXT_DAY(expression,string-expression)_______________________________>

NORMALIZE_DECFLOAT

>__NORMALIZE_DECFLOAT(decfloat-expression)______________________________>

NORMALIZE_STRING

                                     _NFC__
>__NORMALIZE_STRING(Unicode-string,_ǀ_NFD__ǀ________________)___________>
ǀ_NFKC_ǀ ǀ_,_integer_ǀ
ǀ_NKFD_ǀ

NULLIF

>__NULLIF(expression,expression)________________________________________>

NVL

                   <__________
>__NVL(expression, expression_ǀ_)_______________________________________>

OVERLAY

>_OVERLAY(source-string_,insert-string_,_length_,_CODEUNITS16____)______>
ǀ_CODEUNTIS32_ǀ
ǀ_OCTETS______ǀ

PACK

                            <__________
>__PACK(___CCSID 1208_____,_expression_ǀ_)______________________________>
ǀ_CCSID DEFAULT_ǀ

POSITION

>_POSTION(search-string,source-string_,_ CODEUNITS16__)_________________>
ǀ_CODEUNITS32_ǀ
ǀ_OCTETS______ǀ

POSSTR

>__POSSTR(source-string, search-string)_________________________________>

POWER

>__POWER(numeric-expression1,numeric-expression2)_______________________>

QUANTIZE

>__QUANTIZE(expression-1,expression-2)__________________________________>

QUARTER

>__QUARTER(expression)__________________________________________________>

RADIANS

>__RADIANS(numeric-expression)__________________________________________>

RAISE_ERROR

>__RAISE_ERROR(sqlstate,diagnostic-string)______________________________>

RAND

>__RAND(_________________________)______________________________________>
ǀ_numeric-expression_ǀ

REAL

>__REAL(____numeric-expression___)______________________________________>
ǀ_string-expression__ǀ

FLOOR

>__FLOOR(numeric-expression)____________________________________________>

GENERATE_UNIQUE and GENERATE_UNIQUE_BINARY

>__GENERATE_UNIQUE()____________________________________________________>
ǀ_GENERATE UNIQUE BINARY()_ǀ

GETHINT

>__GETHINT(encrypted-data)______________________________________________>

GETVARIABLE

>__GETVARIABLE(string-constant_ ________________________________________>
ǀ_,__default-value________________ǀ
ǀ_,__CAST_(_NULL_AS_VARCHAR(1)_)__ǀ

GRAPHIC

Character to Graphic:

>__GRAPHIC(character-expression____________________________________)_______>
ǀ_,_integer______________________ǀ
ǀ_,_CODEUNITS16____ǀ
ǀ_CODEUNITS32_ǀ

Graphic to Graphic:

>__GRAPHIC(graphic-expression___________________________________)_______>
ǀ_,_integer__________________ǀ
ǀ_,_CODEUNITS16___ǀ
ǀ_CODEUNITS32_ǀ

HASH_CRC32, HASH_MD5, HASH_SHA1, and HASH_SHA256

>__HASH_CRC32_____(expression)__________________________________________>
ǀ_HASH_MD5______ǀ
ǀ_HASH_SHA1_____ǀ
ǀ_HASH_SHA256___ǀ

HEX

>__HEX(expression)______________________________________________________>

HOUR

>__HOUR(expression)_____________________________________________________>

IDENTITY_VAL_LOCAL()

>__IDENTITY_VAL_LOCAL()_________________________________________________>

IFNULL

>__IFNULL(expression,expression)________________________________________>

INSERT

>_INSERT(source-string),start,length,insert-string____________________)___>
ǀ_,_CODEUNITS16___ǀ
ǀ_CODEUNITS32_ǀ
ǀ_OCTETS______ǀ

INTEGER or INT

>_____INTEGER___(___numeric-expression___)________________________________>
ǀ_INT_____ǀ   ǀ_string-expression__ǀ

JULIAN_DAY

>__JULIAN_DAY(expression)_________________________________________________>

LAST_DAY

>__LAST_DAY(expression)___________________________________________________>

LCASE

>____LCASE___(string-expression)__________________________________________>
ǀ,_locale-name_ǀ ǀ_,_integer_ǀ

LEFT

Character string:

>_LEFT(character-expression,length____________________)___________________>
ǀ_,_CODEUNITS16__ǀ
ǀ_CODEUNITS32_ǀ
ǀ_OCTETS______ǀ

Graphic string:

>_LEFT(graphic-expression,length_____________________)____________________>
ǀ_,_CODEUNITS16__ǀ
ǀ_CODEUNITS32_ǀ

Binary string:

>_LEFT(binary-expression,length______________________)____________________>

LENGTH

>__LENGTH(expression)_____________________________________________________>

LN

>__LN(numeric-expression)_________________________________________________>

LOCATE

>_LOCATE(search-string,source-string_________________)____________________>
ǀ_,_start_ǀ  ǀ_,_CODEUNITS16___ǀ
ǀ_CODEUNITS32_ǀ
ǀ_OCTETS______ǀ

LOCATE_IN_SPRING

>_LOCATE(source-string,search-string______________________________________>
ǀ_,_start________________ǀ
ǀ_,instance_ǀ
>_____________________________________________________)___________________><
ǀ_,_CODEUNITS16__ǀ
ǀ_CODEUNITS32_ǀ
ǀ_OCTETS______ǀ

LOG10

>__LOG10(numeric-expression)______________________________________________>

REPEAT

>__REPEAT(expression,integer)_____________________________________________>

REPLACE

>__REPLACE(source-string, search-string, replace-string)__________________>

RID

>__RID(table-designator)__________________________________________________>

RIGHT

>__RIGHT(string-expression,length)_______________________________________>
ǀ_,_CODEUNITS16___ǀ
ǀ_CODEUNITS32_ǀ
ǀ_OCTETS______ǀ

ROUND

                               _,_0___________________
>__ROUND(numeric-expression1,_ǀ_______________________ǀ_)________________>
ǀ_,_numeric-expression2_ǀ

ROUND_TIMESTAMP

                                _,_’DD’_________
>__ROUND_TIMESTAMP(_expression_ǀ________________ǀ_)______________________>
ǀ_,format-string

ROWID

>__ROWID(expression)_____________________________________________________>

RPAD

>__RPAD(string-expression,integer____________)___________________________>
ǀ_,_pad_ǀ

RTRIM

>__RTRIM(string-expression_______________________)_______________________>
ǀ_,_trim-expression_ǀ

SCORE

>__SCORE(column-name, search-argument, string-constant)__________________>

Search-argument-options:

      <______________________
>____________________________ǀ___________________________________________>
ǀ_QUERYLANGUAGE = value_ǀ
ǀ_RESULTLIMIT = value___ǀ
ǀ            _OFF_      ǀ
ǀ_SYNONYM = ǀ_ON__ǀ_____ǀ

SECOND

>__SECOND(expression)____________________________________________________>
ǀ_,integer-constant_ǀ

SIGN

>__SIGN(numeric-expression)______________________________________________>

SIN

>__SIN(numeric-expression)_______________________________________________>

SINH

>__SINH(numeric-expression)______________________________________________>

SMALLINT

>__SMALLINT(___numeric-expression___)____________________________________>
ǀ_string-expression__ǀ

SOUNDEX

>__SOUNDEX(expression)___________________________________________________>

SOAPHTTPC and SOAPHTTPV

>__SOAPHTTPC___(endpoint_url, soap_action, soap_body)____________________>
ǀ_SOAPHTTPV_ǀ

SPACE

>__SPACE(numeric-expression)_____________________________________________>

SQRT

>__SQRT(numeric-expression)______________________________________________>

STRIP

>__STRIP_(_string-expression___________________________________________)_>
ǀ___,BOTH_____________________________ǀ
ǀ_,B________ǀ  ǀ_,strip-character_ǀ
ǀ_,LEADING__ǀ
ǀ_,L________ǀ
ǀ_,TRAILING_ǀ
ǀ_,T________ǀ

SUBSTR

>__SUBSTR(string-expression,start______________)_________________________>
ǀ_,length_ǀ

SUBSTRING

Character:

>_SUBSTRING(character-expression,start,___________,_CODEUNITS16____)_____>
ǀ_,length_ǀ  ǀ_CODEUNITS32_ǀ
ǀ__OCTETS_____ǀ

Graphic:

>_SUBSTRING(graphic-expression,start,____________,_CODEUNITS16____)______>
ǀ_,length_ǀ  ǀ_CODEUNITS32_ǀ

Binary:

>_SUBSTRING(binary-expression,start,_____________)_______________________>
ǀ_,length_ǀ

TAN

>__TAN(numeric-expression)_______________________________________________>

TANH

>__TANH(numeric-expression)______________________________________________>

TIME

>__TIME(expression)______________________________________________________>

TIMESTAMP

>__TIMESTAMP(expression)_________________)_______________________________>
ǀ_,expression_ǀ

TIMESTAMPADD

>__TIMESTAMPADD(interval, number, expression)____________________________>

TIMESTAMP_FORMAT

>__TIMESTAMP_FORMAT(string-expression,format-string______________________>
_,6________________________
>__ǀ___________________________ǀ______)__________________________________>
ǀ_,_precision-constant______ǀ

TIMESTAMP_ISO

>_TIMESTAMP_ISO(expression)______________________________________________>

TIMESTAMPDIFF

>__TIMESTAMPDIFF(numeric-expression,string-expression)___________________>

TIMESTAMP_TZ

>_TIMESTAMP_TZ(expression1)______________________________________________>
ǀ_,expression2_ǀ

TO_CHAR

>__TO_CHAR(string-expression,format-string)______________________________>

TO_DATE

>__TO_DATE(string-expression,format-string)______________________________>
_,6_______________________
>__ǀ__________________________ǀ_______)__________________________________>
ǀ_,_precision-constant_____ǀ

TO_NUMBER

>_TO_NUMBER(string-expression)______________________)____________________>
ǀ_,_format string_ǀ

TOTALORDER

>__TOTALORDER(expression1, expression2)__________________________________>

TRANSLATE

>_TRANSLATE(expression_____________________________________________)_____>
ǀ_,_to-string_______________________________________ǀ
ǀ                _,__’ ‘___________  ǀ
ǀ_,__from-string_ǀ__________________ǀ_ǀ
ǀ_,__pad-character_ǀ

TRIM

>__TRIM_(_string-expression____________________________________________)_>
ǀ___,BOTH__________________________FROM_ǀ
ǀ_,B________ǀ  ǀ_,trim-constant_ǀ
ǀ_,LEADING__ǀ
ǀ_,L________ǀ
ǀ_,TRAILING_ǀ
ǀ_,T________ǀ

TRIM_ARRAY

>______TRIM_ARRAY___(array-expression, numeric-expression_)_____________>
ǀ_ARRAY_TRIM__ǀ

TRUNCATE or TRUNC

                                  _,_0___________________
>__TRUNCATE_(numeric-expression1_ǀ_______________________ǀ_)____________>
ǀ__TRUNC__ǀ                     ǀ_,numeric expression2__ǀ

TRUNC TIMESTAMP

                                 _’DD’___________
>__TRUNC_TIMESTAMP_(expression1_ǀ________________ǀ_)____________________>
ǀ_,format-string_ǀ

UCASE

>____UCASE___(string-expression_________________________________)_______>
ǀ_,_locale-name_ǀ ǀ_,-integer_ǀ

UNICODE

>____UNICODE___(string-expression)______________________________________>

UNICODE_STR

                                      _,UTF-8_
>____UNICODE_STR_(string-expression)_ǀ________ǀ_)________________________>
ǀ_,UTF16_ǀ

UPPER

>____UPPER___(string-expression_________________________________)________>
ǀ_,_locale-name_ǀ ǀ_,_integer_ǀ

VALUE

                       <____________
>____VALUE_(expression_, expression_ǀ_)__________________________________>

VARBINARY

>__VARCHAR(string-expression______________)______________________________>
ǀ_,__integer_ǀ

VARCHAR

Varchar to Character:

>__VARCHAR(character-expression_______________________________)__________>
ǀ_,_integer_________________ǀ
ǀ_,_CODEUNITS16_ǀ
                                           ǀ_CODEUNITS32_ǀ
                                           ǀ_Octets_ǀ

Graphic to Varchar:

>__VARCHAR(graphic-expression___________________________________)__________>
                              ǀ_,_integer__________________ǀ
                                         ǀ_,_CODEUNITS16_ǀ
                                           ǀ_CODEUNITS32_ǀ

Datetime to Varchar:

>__VARCHAR(datetime-expression)____________________________________________>

Integer to Varchar:

>__VARCHAR(integer-expression)_____________________________________________>

Decimal to Varchar:

>__VARCHAR(decimal-expression)_____________________________)_______________>
                                ǀ_,__decimal-character_ǀ

Decimal floating-Point to Varchar:

>__VARCHAR(decimal-floating-point-expression)______________________________>

Floating-Point to Varchar:

>__VARCHAR(floating-point-expression)______________________________________>

Row ID to Varchar:

>__VARCHAR(row-ID-expression)______________________________________________>

VARCHAR_BIT_FORMAT

>__VARCHAR_BIT_FORMAT(expression,format-string)____________________________>

VARCHAR9

>__VARCHAR9(decimal-expression__________________________________)__________>
                              ǀ_,_decimal-character_____ǀ

VARCHAR_FORMAT

Character to VARCHAR:

>__VARCHAR_FORMAT(character-expression)____________________________________>

Timestamp to VARCHAR:

>__VARCHAR_FORMAT(timestamp-expression,format-string)______________________>

Decimal floating-point to VARCHAR:

>__VARCHAR_FORMAT(decimal-floating-point-expression,format-string)_________>

VARGRAPHIC

Character to Vargraphic:

   >__VARGRAPHIC(character-expression____________________________)________>
                                       ǀ_,_integer____________ǀ
                                          ǀ_,_CODEUNITS16__ǀ
                                           ǀ__CODEUNITS32_ǀ

Graphic to Vargraphic:

   >__VARGRAPHIC(graphic-expression_____________________________)_________>
                                       ǀ_,_integer____________ǀ
                                          ǀ_,_CODEUNITS16__ǀ
                                           ǀ__CODEUNITS32_ǀ

VERIFY_GROUP_FOR_USER

                                                     <_,_________
>__VERIFY_GROUP_FOR_USER_(_SESSION_USER__,_group-name-expression_ǀ_)______>
                         ǀ_USER_______ǀ

VERIFY_ROLE_FOR_USER

                                                    <_,________
>__VERIFY_ROLE_FOR_USER_(_SESSION_ROLE__,_role-name-expression_ǀ_)________>
                         ǀ_USER_______ǀ

WEEK

>__WEEK(expression)_______________________________________________________>

WEEK_ISO

>__WEEK_ISO(expression)___________________________________________________>

WRAP

>__WRAP(object-definition-string)_________________________________________>

XMLATTRIBUTES

                  <_,______________________________________________
>__XMLATTRIBUTES(_attribute-value-expression________________________ǀ_____>
                                              ǀ_AS_attribute-name_ǀ

XMLCOMMENT

>__XML2COMMENT(string-expression)_________________________________________>

XMLCONCAT

                                <_________________
>__XMLCONCAT(XML-expression_____,__XML-expression_ǀ_)_____________________>

XMLDOCUMENT

>__XMLDOCUMENT(XML-expression)____________________________________________>

XMLELEMENT

>__XMLELEMENT(_NAME__ element-name________________________________________>
ǀ_,_xmlnamespace-declaration_ǀ
<_______________________________
>_________________________________________________________________ǀ_______>
ǀ,_XML-attributes-function_ǀ    ǀ_,_element-content-expression_ǀ
>_________________________________________________________________________>
ǀ                                                ǀ
ǀ         <___________________________________   ǀ
ǀ           _EMPTY ON NULL_                   ǀ  ǀ
ǀ_OPTION___ǀ_NULL ON NULL__ǀ_________________ ǀ__ǀ
ǀ              _USING_              ǀ
ǀ__XMLBINARY__ǀ_______ǀ_BASE64______ǀ
ǀ |            _USING_            | ǀ
ǀ |_XMLBINARY_|      |_HEX________|_ǀ

XMLFOREST

>_XMLFOREST(_ ___________________________________________________________>
ǀ_XMLnamespace-function_,_ǀ
<__,____________________________________________
>_ element-content-expression_____________________ǀ______________________>
ǀ_AS_element-name_ǀ
>________________________________________________________________________>
ǀ         <__________________________________     ǀ
ǀ             _EMPTY ON NULL_                ǀ    ǀ
ǀ_OPTION_____ǀ_NULL ON NULL__ǀ______________ ǀ____ǀ
ǀ              _USING_           ǀ
ǀ _XMLBINARY__ǀ______ǀ_BASE64__  ǀ
ǀǀ             _USING_         ǀ ǀ
ǀǀ_XMLBINARY__ǀ______ǀ_HEX_____ǀ_ǀ

XMLMODIFY

>__XMLMODIFY(__xquery-update-constant_____________________________)______>
ǀ    <_________________________________________  ǀ
ǀ_,___xquery-variable-expression_AS_identifier ǀ_ǀ

XMLNAMESPACE

                 <_,__________________________________
>__XMLNAMESPACES(__namespace-uri_AS_namespace-prefix__ǀ_)________________>
ǀ_DEFAULT_XML-namespace-uri________ǀ
ǀ_NO DEFAULT_____________ǀ

XMLPARSE

                                         _STRIP WHITESPACE_______
>_XMLPARSE(DOCUMENT_string-expression___ǀ________________________ǀ_)_____>
ǀ_XML-host-variable_ǀ  ǀ_PRESERVE WHITESPACE_ǀ

XMLPI

>_XMLPI(NAME_pi-name________________________________)____________________>
ǀ_,_string-expression_ǀ

XMLQUERY

>_XMLQUERY(xquery-expression-constant)____________________________________>
ǀ         _BY REF_                 ǀ
ǀ_PASSING_ǀ_______ǀ_xquery-argument_ǀ
_BY REF_
_RETURNING SEQUENCE_ǀ________ǀ_   _EMPTY ON EMPTY_
>_ǀ_______________________________ǀ_ǀ________________ǀ__)_________________>

Xquery-argument:

>____xquery-context-item-expression_______________________________________>
ǀ_xquery-variable-expression AS identifier__ǀ

XMLSERIALIZE

                 _CONTENT_
>_XMLSERIALIZE(_ǀ_________ǀ_XML-expression AS data-type___________________>
<_____________________________________
_VERSION__’1.0’__________________    ǀ
>__ǀ_________________________________ǀ___ǀ________________________________>
ǀ  _EXCLUDING XMLDECLARATION__    ǀ
ǀ_ǀ_INCLUDING XMLDECLARATION__ǀ___ǀ

data-type:

                                        _(_1M_)___________
>______CHARACTER______LARGE OBJECT_____ǀ__________________ǀ_______________>
ǀ ǀ ǀ_CHAR_______ǀ               ǀ ǀ ǀ_(__integer_____)_ǀ
ǀ ǀ_CLOB_________________________ǀ ǀ             ǀ_K_ǀ
ǀ_DCLOB____________________________ǀ             ǀ_M_ǀ
ǀ__BINARY LARGE OBJECT_____________ǀ             ǀ_G_ǀ
ǀ_BLOB________________ǀ

XMLTEXT

>_XMLTEXT(_string-expression______________________)_______________________>

XMLXSROBJECTID

>_XMLXSROBJECTID(_xml-value-expression __)________________________________>

XSLTRANSFORM

>_XSLTRANSFORM(_xml-document, xsl-stylesheet, xsl-parameters)_____________>

YEAR

>_YEAR(expression)________________________________________________________>

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.

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 blogs@bmc.com.

BMC Bring the A-Game

BMC works with 86% of the Forbes Global 50 and customers and partners around the world to create their future. With our history of innovation, industry-leading automation, operations, and service management solutions, combined with unmatched flexibility, we help organizations free up time and space to become an Autonomous Digital Enterprise that conquers the opportunities ahead.
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.