CODEWITHSUNDEEP
sqldb2
H. Trujillo
H. Trujillo

Reputation: 437

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

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?

Upvotes: 3

Views: 26413

Answers (3)

Gordon Linoff
Gordon Linoff

Reputation: 1269933

In other databases, you could be explicit:

select (case when u.isLocked = 0 then FALSE else TRUE end)

But DB2 explicitly only supports boolean in programming code:

  • The Boolean data type cannot be returned in a result set.

Upvotes: -1

Paul Vernon
Paul Vernon

Reputation: 3901

You give a link to the Db2 11.1 manuals, and a second link to the DB2 9.7 manuals. Hereby lies your answer. BOOLEAN is supported as a column data type in Db2 11.1.1.1, but not in DB2 9.7.

See the enhancements listed for Mod Pack 1 Fix Pack 1 of Db2 11.1 here https://www.ibm.com/support/knowledgecenter/en/SSEPGG_11.1.0/com.ibm.db2.luw.wn.doc/doc/c0061179.html#c0061179__FP1 where it says

The BOOLEAN data type can now be used for table columns and for expression results. This enhances compatibility with other relational database management systems

Upvotes: 3

mao
mao

Reputation: 12267

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.

Upvotes: 4

Related Questions