db2guide.blogspot.com Open in urlscan Pro
2a00:1450:4001:829::2001  Public Scan

Form analysis
0 forms found in the DOM

Text Content

DB2 :PROGRAMMER'S GUIDE

A complete guide to DB2 for Z/OS.





LONG TIME NO SEE


Its been a long time since i have published any new topic. I have been busy with
career and some other personal issues. But i am back and planning to be more
active now.

In coming days, i will add lot of interesting topics on DB2.

And finally one good news , along with this blog , i am planning to reach you
with new interactive way.

New You tube Channel ,yeah... this will be really interesting.

More details Soon.....

Share your suggestions,thoughts for this new You tube Channel in comments.


Posted by Nitin Gandhi 37 comments
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest




INDEXES IN DB2 - PART II



In this post we will talk more about the types of index and in next post how
indexes works.

Theoretically speaking there can be indexes categorized in to various types but
we will talk more about the types which matters most to the programmers.

Unique Index  

This is the type of index which ensures that the value in a column or set of
column of table is Unique.

So Unique Index is the way using which DB2 ensures that no identical key values
are stored in table.

So when the Unique index creation is must??

When you create a table, say EMP_DETAILS which has all employee details, and one
of the column of this table is EMP_NO (Employee Number). You , for obvious
reasons, want this column to have only unique values so you define this column
as PRIMARY KEY.

The definition of this PRIMARY KEY is not complete until you define the UNIQUE
INDEX on it.So you must create unique index on this PRIMARY KEY which is the
only way DB2 ensures that your PRIMARY KEY will not contain any duplicate
values.

Creating Unique index is very simple just add the word UNIQUE to normal CREATE
INDEX definition.



CREATE UNIQUE INDEX EMPINDEX
   ON EMP_DETAILS (EMP_NO);


So once this UNIQUE INDEX is created DB2 prevents inserting any value in this
table which has existing EMP_NO.

What if program tries to insert the existing value of EMP_NO in this table??

The DB2 throws error and SQLCODE is -803 which is UNIQUE INDEX violation.

Clustering Index

When you define the clustering index on the table, then this index will
determine how the rows are physically stored/ordered from table in table space.

Why as a programmer i should be bothered about how the rows are physically
stored in table space , you might ask? 

Well ,CLUSTERING  INDEX  provides significant performance advantage in some
operations.

Examples :- When there are grouping/ordering operations are performed in
programs, it is very beneficial to have clustering index on table.

This is how you create CLUSTERING INDEX on table,just add the word CLUSTER at
end.



CREATE INDEX EMPINDEX
  ON EMP_DETAILS
    (EMP_NO ASC) 

     CLUSTER;  


Posted by Nitin Gandhi 18 comments
Email ThisBlogThis!Share to TwitterShare to FacebookShare to Pinterest


Older Posts Home




DB2 REAL TIME

 * About DB2 Real Time
 * ☞ DB2 Interview Questions
 * ☞ DB2 Certifications
 * ☞ DB2 Books
 * ☞ DB2 Resources


PREVIOUS POST (BLOG ARCHIVE)

 * ▼  2018 (1)
   * ▼  December (1)
     * Long time no see

 * ►  2017 (1)
   * ►  March (1)

 * ►  2016 (6)
   * ►  July (2)
   * ►  June (4)

 * ►  2015 (2)
   * ►  October (2)

 * ►  2013 (3)
   * ►  June (3)

 * ►  2012 (1)
   * ►  July (1)

 * ►  2011 (22)
   * ►  June (1)
   * ►  April (1)
   * ►  March (7)
   * ►  February (13)


UNIQUE BLOG VISITORS.





THAT'S ME

Nitin Gandhi Portsmouth, New Hampshire, United States IBM Certified DB2
Developer. Areas of specialization: handling very large databases,performance
tuning of DB2 UDB.


CONTACT





FOLLOWERS






VISITOR TRACKER




Theme images by mariusFM77. Powered by Blogger.



Diese Website verwendet Cookies von Google, um Dienste anzubieten und Zugriffe
zu analysieren. Deine IP-Adresse und dein User-Agent werden zusammen mit
Messwerten zur Leistung und Sicherheit für Google freigegeben. So können
Nutzungsstatistiken generiert, Missbrauchsfälle erkannt und behoben und die
Qualität des Dienstes gewährleistet werden.Weitere InformationenOk