DB2 DATE,UNPACK and TRIM Functions

CICS+Interview +QuestionsTRIM

TRIM is a scalar function in DB2. As of DB2 V11 we will see how this function will work.

Example:

Initial value in :HELLO1 = HELLO1

SELECT TRIM(:HELLO1), TRIM(TRAILING FROM :HELLO1) FROM SYSIBM.SYSDUMMY1

Result: HELLO1,HELLO1 – Both are same
In DB2 TRIM is having two arguments. One is trim constant, second one is string expression.

Valid first argument values:

  • TRAILING FROM
  • LEADING FROM
  • L ‘0’
  • R ‘ ‘
  • B ‘ ‘

Second argument:

The second argument is an expression. It can be any string or host variable. By default without giving first argument, it will remove spaces from both ends.

If second argument is NULL, we get NULL value.

**Interview Question

Since, in any function, the basic rule is it should return a value. So, if second argument is NULL, then result will be NULL.

UNPACK – It is a row function in DB2

The UNPACK function returns a row of values that are derived from unpacking the input binary string. It is used to unpack a string that was encoded according to the PACK function.

One excellent example on PACK and UNPACK is as follows:

SET :udf_result = PACK(CCSID 1208, ‘Alina’, DATE(‘1977-08-01’),
DOUBLE(0.5));
Let us assume that, we have create an UDF with the above query, with a name “myfirstudf”.

How to use UNPACK

SELECT UNPACK(myfirstudf(C1)).* AS(Name VARCHAR(40) CCSID UNICODE,
DOB DATE, Score DOUBLE) FROM T1

The result from UNPACK should be valid value. Never we get null value.

DATE
Date function always return date from an expression.

DATE(timestamp1) => From the “timestamp1” it returns Date.

timestamp1=1988-12-25-17.12.30.000000
Result= 25 DEC 1988

** Interview Question

DATE(DATECOl1)

Value in DATECOL1 Char(07) = ‘1989061’

The result will be 02 MAR 1989. This is internal representation value.

Note: Even though the size of the variable is CHAR(07), it considers as valid date, and it gives internal representation value.

Advertisements

Author: Srini

Experienced software developer. Skills in Development, Coding, Testing and Debugging. Good Data analytic skills (Data Warehousing and BI). Also skills in Mainframe.