DB2 Scalar functions - INTNAND, INTNOR, INTNNOR, and INTNNOT

INTNAND, INTNOR, INTNNOR, and INTNNOT

These bitwise functions operate on the "two's complement" representation of the integer value of the input arguments and return the result as a corresponding base 10 integer value.

The schema is SYSIBM.

In each function, the placeholder N represents the byte size of the integer data type that the function operates on and returns, as shown in Table 2.

Table 1. The bit manipulation functions

Function	Description	A bit in the two's complement representation of the result is:
INTNAND	Performs a bitwise AND operation.	1 only if the corresponding bits in both arguments are 1.
INTNOR	Performs a bitwise OR operation.	1 unless the corresponding bits in both arguments are zero.
INTNXOR	Performs a bitwise exclusive OR operation.	1 unless the corresponding bits in both arguments are the same.
INTNNOT	Performs a bitwise NOT operation.	Opposite of the corresponding bit in the argument.

Table 2. Meaning of placeholder N

Value of `N`	Data type function operates on and returns
2	SMALLINT
4	INTEGER
8	BIGINT

expression or expression1 or expression2

The arguments must be integer values represented by the data types SMALLINT, INTEGER, BIGINT, DECFLOAT, DECIMAL, REAL, or DOUBLE.
If the input argument is not of the same data type as represented by N, then the input is implicitly cast to the data type represented by N.
As a result, if a value larger than the maximum value supported by the data type represented by N is passed as input to the function, then an overflow can occur (SQLSTATE=22003).

If either argument can be null, the result can be null; if either argument is null, the result is the null value.

Due to differences in internal representation between data types and on different hardware platforms, using functions (such as HEX) or host language constructs to view or compare internal representations of BIT function results and arguments is data type-dependent and not portable.
The data type and platform-independent way to view or compare BIT function results and arguments is to use the actual integer values.

Examples

A value larger than the maximum supported by 2 byte SMALLINT is passed as input to the function INT2AND.

   SELECT INT2AND(1234567,1) 
   FROM   SYSIBM.SYSDUMMY1

   SQL0413N  Overflow occurred during numeric data type conversion.  
   SQLSTATE=22003

Assume BIGINT columns col1 and col2 have values 137266 and 123825 respectively.
```
   SELECT INT8AND(COL1,COL2) 
   FROM   TAB1

   returns the value 48
```
Assume SMALLINT columns col1 and col2 have values 12 and 13 respectively.
```
   SELECT INT2AND(COL1,COL2) 
   FROM   TAB1

   returns the value 12
```