10.05.2006

DB2 Creating (faking) a Boolean datatype

When creating a table in DB2, to create a Boolean datatype column (since DB2 doesn’t have this natively) you have to use a check constraint on a smallint column. So if you have a column named “ACTIVE” that you want to be Boolean, you would create the that col like so: ACTIVE SMALLINT NOT NULL, CONSTRAINT CCACTIVE1 CHECK (ACTIVE in(0,1)) Then obviously, when you use that field, you can only insert/update using 1 and 0 respectively for your true and false values.

DB2 Creating Sequences to use with Primary Key ID fields

Well, another job and another database system to learn. I've got practically all of them under my belt now ;-) I finished reading this article on sequences vs key managers vs identity columns and decided I needed to go with a sequence. To create a new sequence called “USERS_SEQ” for a schema named “TEST” CREATE SEQUENCE TEST.USERS_SEQ AS BIGINT START WITH 1 INCREMENT BY 1 NO MAXVALUE NO CYCLE; To use that sequence to generate unique, incremented, primary key IDs for a table: INSERT INTO TEST.USERS (id, name_first) VALUES (NEXTVAL FOR TEST.USERS_SEQ, 'Rich'); To use that generated id value, you can use PREVVAL to get it back