DB2 CHAR VS VARCHAR

CHAR

VARCHAR disadvantages:
- uses 2 extra bytes to store the length
-  needs a little extra effort when storing and manipulating length of VARCHAR -> may affect efficiency


General Rule:

• Use CHAR if average length of field is more than 6 bytes to save space
• if average length of field is less than 6 bytes, there will be no storage savings for using char
• Use varchar for columns with more that 30 characters (can be shorter for others)

Derby

http://db.apache.org/derby/docs/10.9/devguide/cdevdvlp17453.html

Useful DB2 Queries

ALL TABLES:
select * from sysibm.systables

TABLE DESCRIPTION:
select * from sysibm.syscolumns where tbname = 'tableName'
- NOT VERY RELIABLE, SOME COLs MIGHT BE MISSING

or

select colname, typename, length, scale from syscat.columns
where tabschema = 'schemaName' and tabname = 'tableName'
- NOT VERY RELIABLE, SOME COLs MIGHT BE MISSING

or

describe table tableName - DOES NOT WORK :(

Note: Use SYSCAT views rather than the SYSIBM tables. Reason being is that from release to release the tables are being revised while DB2 development tries hard to keep the SYSCAT view signatures compatible. For this reason SYSCAT is being described in the SQL Reference while SYSIBM is not.

GET CURRENT DATE/TIME:
select current date from sysibm.sysdummy1
select current time from sysibm.sysdummy1
select current timestamp from sysibm.sysdummy1

Note: sysibm.sysdummy1 table is a special in-memory table that can be used to discover the value of DB2 registers

Sources:
http://www.databaseteam.org/9-db2/b4ac3f33bec9fb23.htm#.UfdJQ41OS6M
http://www.ibm.com/developerworks/data/library/techarticle/0211yip/0211yip3.html

Database Normalization

First Normal Form (1NF)
- no duplicate columns, e.g. teacher, student1, student2, student3
- no non-atomic columns, e.g. teacher, students
- rows must be have unique identifier (primary key), e.g. teacher, studentID, studentName

 Second Normal Form (2NF)
- satisfied 1NF
use foreign key (move subset of data/column that apply to multiple rows of a table to another table and connect them through the foreign key)
e.g.
studentID, studentName, courseID, courseName to
studentID, studentName, courseID and courseID, courseName

Third Normal Form (3NF)
- 2NF
- remove columns that are not dependent upon primary key
e.g.

Boyce-Codd Normal Form (BCNF or 3.5NF)

Fourth Normal Form

Common DB2 SQLCODE

SQLCODE	Description
-301	THE VALUE OF INPUT HOST VARIABLE OR PARAMETER NUMBER position-number CANNOT BE USED AS SPECIFIED BECAUSE OF ITS DATA TYPE
-302	THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER position-number IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE
-440	THE NUMBER OF PARAMETERS IN THE PARAMETER LIST DOES NOT MATCH THE NUMBER OF PARAMETERS EXPECTED FOR STORED PROCEDURE name, AUTHID authid, LUNAME luname. number PARAMETERS WERE EXPECTED.
-551	auth-id DOES NOT HAVE THE PRIVILEGE TO PERFORM OPERATION operation ON OBJECT object-name

Complete list: http://www.caliberdt.com/tips/sqlcode.htm