10.05.2006

DB2 Creating (faking) a Boolean datatype

No comments:
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

No comments:
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