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!!