DB2 on the Cloud - Ooops, broke my smallint

So I was knocking up a test DB using IBM DB2 on Cloud ( a SaaS offering ), for some integration testing between IBM AppConnect Enterprise (ACE) and DB2 itself.

I'll talk about the connectivity in a later post.

However, I created a table: -

CREATE TABLE EMPLOYEE(ID SMALLINT, FIRSTNAME CHAR(30), LASTNAME CHAR(30));

and inserted some data: -

INSERT INTO EMPLOYEE(ID,FIRSTNAME,LASTNAME) VALUES(12345,'Homer','Simpson');

INSERT INTO EMPLOYEE(ID,FIRSTNAME,LASTNAME) VALUES(23456,'Marge','Simpson');

INSERT INTO EMPLOYEE(ID,FIRSTNAME,LASTNAME) VALUES(34567,'Lisa','Simpson');

and then got this: -

SQL0413N   Overflow occurred during numeric data type conversion.

Can you see what I did wrong ??

Yeah, I know, right !

The SMALLINT data type stores small whole numbers that range from –32,767 to 32,767. The maximum negative number, –32,768, is a reserved value and cannot be used.

https://www.ibm.com/support/knowledgecenter/en/SSGU8G_12.1.0/com.ibm.sqlr.doc/ids_sqr_144.htm

So I was trying to insert the value 34567 into a column that was limited to 32767.

Doofus!

I fixed it easily: -

DROP TABLE EMPLOYEE;

CREATE TABLE EMPLOYEE(ID INT, FIRSTNAME CHAR(30), LASTNAME CHAR(30));

INSERT INTO EMPLOYEE(ID,FIRSTNAME,LASTNAME) VALUES(12345,'Homer','Simpson');

INSERT INTO EMPLOYEE(ID,FIRSTNAME,LASTNAME) VALUES(23456,'Marge','Simpson');

INSERT INTO EMPLOYEE(ID,FIRSTNAME,LASTNAME) VALUES(34567,'Lisa','Simpson');

INSERT INTO EMPLOYEE(ID,FIRSTNAME,LASTNAME) VALUES(45678,'Bart','Simpson');