Showing posts with label Database System. Show all posts
Showing posts with label Database System. Show all posts

Wednesday, July 9, 2014

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)

Monday, June 18, 2012

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

Tuesday, May 22, 2012

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

Thursday, January 20, 2011

Common DB2 SQLCODE

SQLCODE Description
-301THE VALUE OF INPUT HOST VARIABLE OR PARAMETER NUMBER position-number CANNOT BE USED AS SPECIFIED BECAUSE OF ITS DATA TYPE
-302THE VALUE OF INPUT VARIABLE OR PARAMETER NUMBER position-number IS INVALID OR TOO LARGE FOR THE TARGET COLUMN OR THE TARGET VALUE
-440THE 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