ROWID in DB2...

ROWID in DB2…

The Row id is part of every successful database in today’s world.

In DB2 every record stores a RID in the form of hex values. ROWID is basically a combination of page(block),slot (within a page),and file. Fetching the records using ROWID’s is amongst the most optimized method and is used when indexes are used in the predicates. In fact, ROWID is the fastest way of retrieving records.

ROWID is internally generated unique row identifier which is constant and immutable unless a REORG is done.

In DB2 ROWID is of 4 bytes(3 bytes for page and 1 for slot).Since 1 byte is 8 bits and 8 bits can go up to 255,therefore one page in DB2 can contain 255 records and thats why we have the limitation of MAXROWS as 255 per page.

ROWID can be used separately in DB2 by adding a column of ROWID data type.

This column can then be retrieved in a Select and stored in a program's host variable. It can later be used to directly retrieve the row in the table to perform a Select, Update, or Delete. When using a Rowid in this way, a row is directly accessed without performing a Table scan and without needing to use an Index.

Select * from table_name where rowid_column = :hostVarRowid;

In DB2, ROWID is actually a data type of a column.

Cheers!!!

BIND & REBIND in DB2

BIND & REBIND are the two options provided in DB2 for the re-evaluation of access path.

REBIND searches the catalog,takes the information found there and re-evaluates the access path.
BIND being more enhanced operation performs a lots of checks (correct syntax,whether all tables are existing or not ,proper grants are provided or not) and then re-evaluates the access path.

Both BIND & REBIND basically checks for the dependency of the objects referenced in the package.
In a package suppose n number of objects are referenced and any one of them is dropped,then the package will be consequently marked as invalid.
When Someone executes the package then DB2 will perform an automatic rebind and if the object is still not available then that package is marked as inoperative and no further automatic rebinds are performed for the same and the execution will fail.

A BIND permits us to use new BIND parameters but the REBIND uses the already specified parameters.

Further if you want to specify new parameters for the execution of your Package and if the whole SQL has been changed (like referencing to new objects) then a BIND is required .
And if you simply want to redo the evaluation of the access path for the package then being cost efficient and optimized,the REBIND is the best option.


Cheers!!

DSNTEP2 & DSNTEP4...

DSNTEP2-:It is a PL\1 source program which provides the functionality
of executing the sql statements from jcl in mainframe.
the sql can be executed when run under the plan DSNTEP2

DSNTEP2 DOES NOT ALLOW MUTILPLE FETCHING OF ROWS.

DSNTEP4 -: All the functions are same as of dsntep2.
it also allows the multiple fetching.



MULTIPLE FETCH -: Specify the number of rows that the
SQL is going to fetch at one select.
The default fetch amount for DSNTEP4 is 100 rows, but you can
specify from 1 to 32676 rows.

TABLESPACES IN DB2.....A Review

DB2 stores data in table spaces composed of one or many VSAM datasets.

The six type of Table Spaces that DB2 supports are-:

• SIMPLE
• SEGMENTED
• PARTITIONED
• LARGE OBJECTS
• UNIVERSAL
• XML 

Simple Table spaces

1.     Fairly Outdated 

2.     One or many tables in single tablespace

3.     Rows from different tables usually interleaves on same page so locking usually locks rows from other tables also.

4.     Creation not supported in DB2 9.1 but existing tables are supported

Segmented Table Spaces

1.     Organizes pages of the TS into pages

2.     Each segment contains rows of only one table

3.     Size is limited to 64 GB

Number of pages	SEGSIZE
28 or less	4 to 28
28 to 128	32
128 or more	64

4.     Problem of locking rows of other table (Simple Tablespace) is resolved

5.     If a mass delete is performed segment pages are available for immediate reuse.(no need to run reorg)

6.     Mass deletes are much faster

7.     COPY utility doesn’t have to copy empty pages

8.     Eg-:

Create tablespace DEVTS in DEVDB

Using stogroup DSG910

          Priqty 1000

Secqty 50

Segsize 4

Locksize table

Bufferpool bp32

Close no

Ccsid ebcidic

9.  Once set, the SEGSIZE parameter cannot be altered.

DB2 VERSUS ORACLE

Oracle’s Larry Ellison threw down the gauntlet recently when he made claims about the superiority of Oracle over DB2. IBM has, of course, responded. Below are the Oracle claims and the IBM rebuttals. Enjoy!

ORACLE: "We blew the doors off of IBM. We crushed them." [Referring to TPC-C benchmark results] In a machine that took up less than 10% the floor space, of IBM's record setting computer. We ran faster, we ran a lot faster: using a tiny fraction of the floor space, a tiny fraction of the power, cost less."

IBM: Until late last year, DB2 enjoyed a massive 49% lead over Oracle. With Oracle's most recent result, they have taken the lead by 25% (and by the way, they used more than six times as many CPU cores to do it). We are confident that DB2 will retain its lead this year. Also, remember that DB2 has dominated TPC-C performance leadership over the past seven years, with almost twice as many days of leadership as Oracle Database.

Check the link below and clear your queries-:

http://mainframeupdate.blogspot.com/2010/02/oracle-versus-ibm-and-db2.html

DB2 ON Z/OS

z/OS is the flagship IBM mainframe operating system. z/OS is the robust zSeries

mainframe operating system designed to meet the demanding QoS requirements

for On Demand Business. Here are some highlights:

1) It provides a highly secure, scalable, high-performance base for on demand

applications.

2) It can simplify IT infrastructure by allowing the integration of applications in a

single z/OS image.

Evolution of DB2

In the early 1950s, the disk drive was developed in IBM San Jose, California. In

the late 1960s, as large amounts of data were stored on disk, the research focus

turned to data management systems.

                   At that time, network and hierarchical data management systems were in use.

CODASYL and Information Management System (IMS) were state of the art

technologies for automated banking, accounting, and order processing.

Respected IBM Fellow Dr. E.F. (Ted) Codd worked on a new kind of mathematical

notation, but it took a while for it to receive the attention it deserved. Programs

that would have been five pages long if represented in CODASYL were written by

Codd in single lines. These would be queries such as “Find the employees who

earn more than their managers.”

                From Codd’s paper, a team in IBM research started building System R as part of

a program of research in the relational model of data. The objective of this

system was to handle ad hoc queries (one time) and “canned” queries (executed

many times). For the relational model to be accepted, it had to be proven by an

industrial-strength implementation. That was the goal of the System R project

begun at IBM San Jose Research in 1973.

               Codd believed that computer users should be able to work at a more natural

language level. They should not be concerned about the details of where or how

the data was stored.

At the IBM laboratory, other concepts emerged:

When querying a set of rows, lock the set.

Instead of authorizing columns of a table, make it a view of the columns and

authorize that.

Transactions, like logical units, should be all or nothing.

The research went on until Db2 was the best database to work on Mainframes making billions of transactions per second with ease.

 

Hi..

Hi..

Welcome to my blog...