What is the best way to handle BOOLEAN values in Db2?

Question

Database

Db2

Scenario

I have a column that needs to be true or false. I have found two sources that point to how to achieve this; however, when I bring them together I get an error.

1. Boolean values
2. Casting between data types

Current Solution

CREATE TABLE USERS
(
   ID INT NOT NULL,
   .
   .
   .
   IS_LOCKED SMALLINT NOT NULL WITH DEFAULT 0,
   PRIMARY KEY(ID)
);

SELECT U.ID, CAST(U.IS_LOCKED AS BOOLEAN) as IS_LOCKED FROM USERS U

Error: A value with data type "SYSIBM.SMALLINT" cannot be CAST to type "SYSIBM.BOOLEAN"

Question

How can I use BOOLEANs in Db2?

Answer 1

Db2 V11.1 on Linux/Unix/Windows supports BOOLEAN as a column data type and such columns can be returned in a result set. Here is an example using the command-line-processor (at the bash shell):

create table mytable( id integer, mybool boolean with default true )
DB20000I  The SQL command completed successfully.

insert into mytable(id, mybool) values (1, false), (2, true), (3, false)
DB20000I  The SQL command completed successfully.

select id,mybool from mytable order by 1

ID          MYBOOL
----------- ------
          1      0
          2      1
          3      0

  3 record(s) selected.

However, while plain DDL and the CLP for SQL DML support boolean, consider the impact on the applications of using the Db2 column-datatype boolean. Check how PHP , Python, Java, .net, etc can manipulate this datatype according to whatever languages are used to access your databases.

Tip: when asking for help about Db2, it is wise to always mention your Db2-version and the operating-system that runs the Db2-server (i.e. z/os , iSeries, linux/unix/windows) and tag your question accordingly.