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.

Business, Faster than Humanly Possible

BMC empowers 86% of the Forbes Global 50 to accelerate business value faster than humanly possible. Our industry-leading portfolio unlocks human and machine potential to drive business growth, innovation, and sustainable success. BMC does this in a simple and optimized way by connecting people, systems, and data that power the world’s largest organizations so they can seize a competitive advantage.
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.