DB2 :Programmer's Guide

Long time no see

2018-12-12T13:36:00.002-05:00

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.

Indexes in DB2 - Part II

2017-03-29T04:27:00.001-04:00

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;

Indexes in DB2 - Part I

2016-07-13T12:02:00.000-04:00

In order to get the bottom of any topic, I always prefer getting the answer of three important questions.

What is it ?
Why we need it?
What if we don't have it?

So, today we are going to understand the topic indexes in DB2 using these questions and try to learn as much as possible.

What is it??

Index is very powerful concept , which uses the pointers to actual data to more efficiently access the specific data item.

DB2 Indexes are the DB2 objects which can be created on columns of the table to speed up the processing of SQL queries and sometimes can also help in uniquely identifying each row in a table.

To give one very practical example of what indexes are , think of indexing in books which you often see either at the start or end of the book catalog.

Why we need it?

As we mentioned above, it helps in speeding up the query processing by allowing DB2 optimizer to choose most optimized path to access the data you are looking for.

It also helps (Depending on type of index) to uniquely identify each single row in your DB2 table.

Lets understand this using our example of books.

Consider any book of 1000 pages and you don't want to read a whole book but a specific information/chapter in that book. what you will do?

Will you start looking/reading from page 1 ,each and every page/line ,until you find the information you are looking for ? If yes, what is the problem here?

Obviously, It will be very time consuming to get a small piece of information in big book. So here indexing of the books helps us , you can just look for the specific keyword in book index and that will tell you what all the pages in book have that keyword and then you just read those specific pages to find out the exact information you are looking for.

What it did, reduces a lot of time to get to the data and speed up our process of looking information.

DB2 indexes are same, book is DB2 table, pages in book are DB2 pages, lines are rows in a table and specific data you are looking is the value of columns of the DB2 tables.

What if we don't have it?

In above example of books, imaging you don't have indexes. You get the drill, right?

If there is a DB2 table with large amount of data in it and there are no indexes defined on any of the columns, and you fire any simple SELECT SQL query on it.

SELECT EMP_NAME FROM EMP_TABLE
WHERE EMP_NO = '123456'

It will start reading the each and every DB2 page sequentially until it finds the data matching to predicates in your query and it will consume lot of CPU and Elapsed time. With time comes lot of other consequence like if the query is not defined with UR(Uncommitted read) , the locks will be held on tables/pages/rows and other process/program trying to access the same table/data at around same time will have to wait and more likely the waiting process fails with TIMEOUT.

So proper indexing is very important and there are only pros of having indexes.

But in some rare cases there are times when indexing a table is not the way to go.

Consider a table with very less data.. may be 100 or even 1000 rows. Here query can get you the data faster when there are no indexes.

This is because having index is two way read process, first the indexes have to be read and then the pointers/pages mentioned by index have to be read and with table having very less rows it is rather faster to read the data sequentially than having indexes.

So consider having indexed when there are tables with large number of rows.

Now, in next post we will cover the answer of next set of questions like how it works??.How indexes work and what are the types of indexes etc.

DB2 Image Copy

2016-07-11T14:33:00.003-04:00

In a Production environment, there are constant updates happening on your DB2 tables as a result of several jobs/programs functionality. These updates are nothing but the results of the execution of Insert/Update/Delete statement on your various DB2 tables.

What if, I want to take a look at the data in tables before my today's batch pass ran ?? this may be for some analysis/understanding or worse inadvertently something went wrong and you want to recover the data back to where it was before the job ran. Is there a way? Wouldn't it be nice if some one can take a back up of my DB2 tables before the batch pass run so I can always go and get it back where we started.

Well , it's already happening the only thing is as a programmer/ developer you may not be knowing it. This is something done by using DB2 utility and by your DataBaseAdministrator .

This process of taking full back-ups of your data objects is called as DB2 full image copy. It is achieved by DB2 utility COPY.

You can make full image copies of a variety of data objects. Data objects include table spaces, table space partitions, data sets of nonpartitioned table spaces, index spaces, and index space partitions.

The following statement specifies that the COPY utility is to make a full image copy of the TABSPACE table space in database DATABASE.

COPY TABLESPACE TABSPACE.DATABASE

The COPY utility writes pages from the table space or index space to the output data sets.

So, if you want to take a look at old data before pass, all you have to do is dump that image copy dataset from COPY Utility into normal DASD file on disk.

There is also something called as incremental image copy, this as it name suggest does not take complete copy but only the records which has changed (Insert/Update/Delete) from last run .

The information of this COPY Utility can be found on the DB2 catalog table SYSIBM.SYSCOPY

More on how JCL'S for DB2 Utility looks and how it works will be covered later in DB2 utility post which I will be doing shortly.

DB2 System Catalog Tables

2016-06-30T15:57:00.002-04:00

Ever wondered, when any new DB2 object is created like Table,View,Stored Procedure,DBRM etc. where all the information is stored. Is there any place where I can look up to get all the details like all the Indexes of the table, all the columns in an index, Referential integrity on the table etc..

DB2 system catalog tables is the answer and Good Developer/programmer should know about it.

All the mainframe shops generally have tools to get this information.

These tools goes by different name , most common of those and people many know is by BMC.
BMC Change and Catalog Manager,BMC Main View for DB2 etc.

This tool will get you all this information and have easy to use User interface and navigation but this tool in background also use the information from DB2 Systems catalog table.

So, DB2 system catalog tables is the ultimate source where all information is recorded.

As the name suggest , there are set of special tables where information get recorded automatically whenever any object is created. With proper access we can just query these tables ,like we query any other tables, and get the desired information.

All catalog tables are qualified by schema SYSIBM.

Ex SYSIBM.SYSTABLES,SYSIBM.SYSKEYS etc.

Sample query -

SELECT * FROM SYSIBM.SYSTABLES
WHERE NAME = <Table name>

More about it in upcoming posts.

Cusror in DB2 ---> Part III (Multirow fetch)

2016-06-29T09:53:00.003-04:00

==> Click Here to Read Part I <==

==> Click Here to Read Part II <==

In this post ,we are going to discuss how to fetch and process multiple rows (Multirow fetch) at a time using single fetch statement in DB2 cursor.

The multirow fetch was introduced in DB2 version 8 onwards.

Why to use it ?

By fetching multiple rows at once, your request can become more efficient and it can improve the performance by reducing the CPU time.

How to use it ?

As we know with regular Cursor fetch statement we can fetch one row at a time, values will be fetched into host variables and processed later into the program.

Ex -

EXEC SQL
      FETCH CURSOR_NAME
      IN TO :A,:B
END-EXEC.

Now if we want to use Multirow Fetch feature, First of all our Cursor should be defined with something called as "With Rowset positioning".

A rowset is a group of rows that are operated on as a set. Such a cursor enables your program to retrieve more than one row using a single FETCH statement

Define Cursor:

EXEC SQL
    DECLARE CURSOR CURSOR_NAME
WITH ROWSET POSITIONING
   FOR
SELECT EMP_NAME FROM EMPTABLE
END-EXEC.

Now with this cursor when we use fetch statement we will get multiple rows at a time which normal host variable structure will not be able to handle it as it was designed to receive only one row at a time.

So, to use a multi-row fetch you must have defined the appropriate structures to receive multi-row data. This means you must defined an array of host variables into which the fetched rows can be placed. Each column fetched requires its own host variable array into which its values will be placed. If column is nullable then make sure we have separate  host variable array defined for null indicator.

Be sure to match the array size to the rowset size. This way a single FETCH statements can be written to retrieve more than a single row from the result set.

Fetch Cursor :

EXEC SQL
     FETCH ROWSET FROM CURSOR_NAME
     FOR 10 ROWS
     INTO :HOST-VAR-ARRAY
END-EXEC.

So it looks exactly like normal fetch statement except the word ROWSET and FOR 10 ROWS.

ROWSET keyword indicates that this is a cursor with Rowset positioning.
The FOR 10 ROWS clause specifies the size of the rowset to be returned. The maximum rowset size is 32,767.

Rowset cursors are very useful when you need to retrieve many rows or large amounts of data.
By retrieving multiple rows with a single FETCH, multiple trips between the application and the database can be eliminated, thereby improving network performance.

LOB Data type in DB2.

2016-06-28T14:42:00.000-04:00

We have various different type of data types in DB2. For ex- SMALL INT,INT,CHAR,VARCHAR..

These data types seems to work fine and do their job perfectly .But if you want to store large audio,video, images or any other file which has much larger size than 32kb they cant handle it.

VARCHAR ,VARGRAPHIC,VARBINARY has the storage limit of 32 KB.

For data objects that are larger than 32 KB, you have to use something called as large object (LOB) data types to store these objects.

DB2 provides three different type of LOB data types to store these data objects as strings of up to 2 GB in size:

Character large objects (CLOBs) :
: Use this data type if your data is larger (or might grow larger) than the VARCHAR data type permits. It can store up to 2GB. For example, you can store information such as an employee resume, or the text of book in a CLOB.
Double-byte character large objects (DBCLOBs): Use the DBCLOB data type to store large amounts of DBCS data, such as documents that use a DBCS character set.
Binary large objects (BLOBs): Use the BLOB data type to store large amounts of non character data, such as pictures, voice, and mixed media.

If your data does not fit entirely within a data page, you can define one or more columns as LOB columns.

Example :

CREATE TABLE EMPLOYEE_SALARY
(DEPTNO CHAR(3) NOT NULL, EMP_RESUME BLOB(1G) )

DB2 SQL query in COBOL program - Bind Warning

2016-06-24T15:15:00.000-04:00

When we write embedded SQL in COBOL-DB2 application program , we know that

It should be always within EXEC SQL END-EXEC.
If we are doing a SELECT or using a Predicate with specific value passed in variable for comparison, that variable should always be preceded by :

All the variables preceded by : called as Host Variable.

Ex :

EXEC SQL

SELECT SAMPLE_DATE INTO :WS-DATE
FROM TABLE_A WHERE DAY = :TABLE_A.DAY

END-EXEC.

Here :WS-DATE ,DAY OF TABLE_A are host variable , declared somewhere in working storage section with appropriate definition.

What would be the consequence if somehow we miss this variable to precede with :(Say TABLE_A.DAY was not preceded by : in above query)

Will we able to compile the program ?? Bind it ?? What error will we get , if query is getting executed.

First of all, yes program can be compiled, and bind will be successful. But there will be a warning message in your Bind step with SQLCODE 203 and SQLSTATE 01552.

So lesson learnt is if your bind is successful not necessary mean that all is well, carefully look at if there is any warning provided by DB2 precompiler.If you miss it , you will be in for surprise Production/Test failure. Just here in this case if you just go ahead and move the code with Bind warning and when program runs and try to execute this query it will fail with SQLCODE -206

Declaring host variable (The One without DCLGEN.)

2015-10-20T14:32:00.000-04:00

DCLGEN (Declaration generator) is the utility/tool provided by IBM to create the Host Language (Cobol) compatible variable for columns in DB2 table.

It's simple ,you just provide the table name you are using in your program,DCLGEN will create the equivalent Host variable for you.

Can you, without the help of DCLGEN,create similar structure of host variable manually ??

Yes, off-course you can, Provided you are able to convert the data types and length from db2 column to that of cobol Program. For Ex. CHAR(10) to PIC X(10),SMALLINT to PIC S9(4) COMP etc.

Having said that, now consider the below scenario

I have created the host variable manually and used it in query which is embedded in my application Program.

Here it goes..

Declaration of Host variable:

01 WS-HOST
05 WS-STAT-C PIC X(1)

Usage in Query : (ACC_STAT_C is defined as CHAR(1) NOT NULL in RCAV0300)

SELECT ACC_STAT_C INTO :WS-STAT-C FROM RCAV0300
WHERE ACC_N ='1234'

Now when i execute the Program (Containing above query),i am getting the below error.

"Undefined or Unusable Host Variable WS-STAT-C".

Can anyone one tell me what is the problem here?? Why am i getting this error??

Well i have spelled, defined and used the variable WS-STAT-C correctly in my Program still i am getting the above mentioned error.

Lesson Learnt :

Whenever you define the host variable in your program(the one without DCLGEN) ,define it at 01 Level.If you define it at any sub-level (05,10 etc), you will get the error at run time as "Undefined or Unusable Host Variable", even though you have spelled,defined and used the host variable correctly in your program.

Needless to say now ,above problem can be resolved by declaring your variable in working storage section as

01 WS-STAT-C PIC X(1).

Where and how to check for Consistency token

2015-10-15T20:45:00.001-04:00

As a DB2 programmer you know what consistency token is and why DB2 uses it . If not, those famous SQLCODE -805 OR -818 error will make you know what it is. So let's not get into that.

The real question is where and how I can view this unique token generated by DB2 so that I can verify that it is in sync or if not in sync then who is at the fault and how to rectify it.

So After DB2 precompiler generates this unique consistency token, it will placed and available in OBJLIB,DBRMLIB and LOAD Module library and get matched against IBM DB2 catalog table SYSIBM.SYSPACKAGE at run time.

Checking Consistency token in DBRMLIB

Open DBRMLIB library, Put command "HEX ON " on command line and look at the position 25 to 32 at the very first record.That's your Consistency token ,

So here 19C173C91588C612 is the hex value of your contoken.

Checking Consistency token in LOADMODULE

So this token we found in DBRM lib will be present in Load module. Well , how to find it?

Unlike ,DBRMLIB there is no specific location in Load library where this token can be found.

So get the token we found in DBRMLIB ,19C173C91588C612

Now divide into half and reverse it that is 1588C61219C173C9.

Now Open Load library and put command "F X'1588C61219C173C9'". That's how you check it here.

Checking Consistency token in SYSIBM.SYSPACKAGE.

This is very straight forward simply run the below query.

SELECT (
                 SUBSTR(HEX(A.CONTOKEN),9,8) || SUBSTR(HEX(A.CONTOKEN),1,8 )

               ) AS CONTOKEN
FROM SYSIBM.SYSPACKAGE A
WHERE NAME IN ('ABCD1234')

**ABCD1234 is your program name.

Hope you got something out of this post.

Understanding Spufi output.

2013-06-17T13:38:00.000-04:00

There are various ways to execute SQL statements in a mainframe.
You can execute them through

Embedding them in Application program (<Exec sql> <end-exec>)
Using QMF (Query Management Facility)
Using any third party product(vendor) available in your shop like BMC,WINSQL etc.
Productivity aid sample programs like DSNTIAUL, DSNTEP2 or DSNTIAD etc
Using SPUFI (SQL Processing Using file input)

This post is mainly to understand the output of the query which is run through SPUFI.

First let’s see the sample spufi output.

 DSNE617I COMMIT PERFORMED, SQLCODE IS 0   DSNE616I STATEMENT

 EXECUTION WAS SUCCESSFUL, SQLCODE IS 0

---------+---------+---------+---------+---------+---------+----

 DSNE601I SQL STATEMENTS ASSUMED TO BE BETWEEN COLUMNS

1 AND 72   DSNE620I NUMBER OF SQL STATEMENTS PROCESSED IS 1

 DSNE621I NUMBER OF INPUT RECORDS READ IS 32

 DSNE622I NUMBER OF OUTPUT RECORDS WRITTEN IS 22

As you can see here,the SPUFI output contains

The executed SQL statement, copied from the input data set
The results of executing the SQL statement
The formatted SQLCA, if an error occurs during statement execution

The weird and interesting thing if you can notice here is,even though the query ran successfully and gave the proper result SQLCODE IS 100 AND NOT 0.

Why??? My query ran successfully so,I should get sqlcode 0 right??

Well to put you in more confusion, if i run the same query through application program ( Embeded in program) , it will give SQLCODE 0
The question remains the same rather more aggressive now.

The answer is

“For SELECT statements that are executed with SPUFI, the message ”SQLCODE IS 100″ indicates an error-free result. If the message SQLCODE IS 100 is the only result, DB2 is unable to find any rows that satisfy the condition that is specified in the statement.

For all other types of SQL statements (Other than SELECT)that are executed with SPUFI, the message ”SQLCODE IS 0″ indicates an error-free result. “

There is still a trick to avoid getting SQLCODE 100 in this case and you still can get SQLCODE 0

You have 5 rows in your table satisfying your condition , then set the MAXIMUM ROWS TO BE FETCHED option in the SPUFI to 5 but for this you should know in advance ,the no. of rows your query would return which is not feasible every time.
If you set to any value more than 5 it would give SQLCODE 100 at the end.
Apart from this,each SPUFI message contains the following:

The SQLCODE, if the statement executes successfully.
The formatted SQLCA, if the statement executes unsuccessfully.
What character positions of the input data set that SPUFI scanned to find SQL statements. This information helps you check the assumptions that SPUFI made about the location of line numbers (if any) in your input data set.
Some overall statistics:
- Number of SQL statements that are processed
- Number of input records that are read (from the input data set)
- Number of output records that are written (to the output data set).

Other messages that you could receive from the processing of SQL statements include:

The number of rows that DB2 processed, that either:
- Your select operation retrieved
- Your update operation modified
- Your insert operation added to a table
- Your delete operation deleted from a table
Which columns display truncated data because the data was too wide

How the DB2 Application Program runs on Mainframe

2013-06-17T13:31:00.000-04:00

This Post is to understand in detail how your application program runs (and various stages it goes through) on mainframe.

What is DB2 Application program?

The program written in any High-Level language (In this context COBOL language) which access the data stored in DB2 database.

IF we feed the DB2 application program written in COBOL to the Cobol Compiler (IGYCRCTL) it can recognize only Cobol statements in application program and cannot recognize any DB2 statement hence before feeding this program directly to Cobol compiler, we want something which can recognize DB2 statement and separate the DB2 statement from the Cobol program. This can be done by feeding the application program to DB2 Pre-compiler.

Ø DB2 Pre-compiler :

· The DB2 pre-compiler will comment out the DB2 statement in program and replace them with the CALL statement.

This commented DB2 statement will be stored separately in different dataset which is called as DBRM (Data Base Request Module).

So this pre-compiler separates out the main program in two parts

1>    The modified Source code (The main program with Commented Db2 statements and corresponding replaced CALL statement.

2>    The DBRM (Contains all the DB2 statement from the source program)

The same (How the DB2-Precompiler separates out the main program in two parts) can be viewed in BATCH JCL.


                                                                                                                                                                                                                               ·         Place the timestamp token on DBRM and modified source code .
This Timestamp token will then carry forward in bot parts and will be checked at the run time when these two separated parts reunite.So at the run time the matching timestamp token will indicate that we are using the same DBRM and Modified source code which get separated at pre-compile step.

The pre-compilation timestamp token placed by DB2 pre-compiler on the DBRM can be obtained by checking the value from POS 25 through POS 32 in the DBRM header, which is the first record in the DBRM member.

The pre-compilation timestamp token placed by DB2 pre-compiler on the Modified source code can be found in variable SQL-TIMESTAMP-1 SQL-TIMESTAMP-2.

SQL-TIMESTAMP-1 (412701320) equals 18995288 in Hex which is the same as the first 8 digits the DBRM.
SQL-TIMESTAMP-2 (471453846) equals 1C19D096 in Hex which is the same as the second 8 digits the DBRM.

Ø  COBOL Compiler (IGYCRCTL):

The modified source code out of the DB2 pre-compiler will now fed to the COBOL compiler. The compiler will check the code for syntax error and creates the OBJLIB (Object Library)


Ø  Link-Edit (IEWL) :

IEWL is the program which link-edit the OBJLIB created in previous step by Cobol compiler to create a LOAD MODULE (The corresponding machine level code of our main DB2 Application program)

Ø  BIND :

             The second part created out of DB2 Pre-compiler (DBRMLIB) will now get processed.
The Bind is type of compiler to the SQL statement (DB2 statement).It will read the DB2 Statement from the DBRMLIB and check it for syntax error and create a mechanism to execute the SQL statement.
There are two types of BIND, BIND PLAN and BIND PACKAGE.

·         BIND PLAN:
This will have the one or more DBRM, Packages or the combination of DBRM and Package as an input. The BIND PLAN command will invoke the DB2 Optimizer which intern will create the PLAN containing optimized access path to execute the SQL statement inside DBRM.

The PLAN created here is an executable and will execute only along with the corresponding Load module created from modified source code through Compile-Link Edit process. None of the PLAN or Load Module can be executed alone.

·         BIND PACKAGE:

This will have only single DBRM as an input. The Package created here is not executable and need to be bind to plan again in order to get executed.
The bind process also updates the Pre-compile Timestamp token placed by the DB2 Pre-compiler on DBRM header in TIMESTAMP column in SYSIBM.SYSDBRM table .PRECOMPTS column will have the regular Timestamp. The columns PRECOMPTIME and PRECOMPDATE will have time (Resolution to 100^th of a SEC.) and Date (YYMMDD).Note the remaining flags in the DBRM header will be updated in the first row of SYSIBM.SYSSTMT (for PLAN bind) orSYSIBM.SYSPACKSTMT (for package bind). DB2A gets the details from these tables.

Ø  RUN PROGRAM:

So at the start we separated our main DB2 application program into Modified source code and DBRM which then went through two different processes to create the LOAD module and PLAN respectively. Now just before running the program we need to reunite these two and run our main DB2 application program.

While running, the Timestamp token placed by the DB2 pre-compiler on both part (Plan (fromSYSIBM.SYSDBRM) and Load module) will be checked.
We will get the SQLCODE of -818 if the Timestamp between two doesn’t match.

Note :  Click on the images to get bigger view.

Cusror in DB2 ---> Part II

2013-06-15T05:12:00.002-04:00

==> Click Here to Read Part III <==

In first part we discussed about, what the cursor is and why we need it.

In this part we will discuss about the types of cursor. Yes there are different types of cursor and they work differently depending on their type.

In normal or simple cursor the data can only be retrieved one row at a time in forward direction using "FETCH" statement.

Scrollable Cursor :-

With Scrollable cursor, you can scroll forward/backward through the data(Result set of select statement) in an application program.

Ok but before everything else how do you distinguish that the cursor defined in the program is scrollable or not.

The keyword "SCROLL" is use to specify that the cursor defined is scrollable.

Example:

Declare Cursor1 (cursor name)

SCROLL Cursor

Ok so this is how you define it.Now, how does it work? how do you move through the data?

Similar to normal cursor, Fetch statement is still used but is combined with different keyword to move the fetching in specific direction.

Example :

FIRST :- This will fetch the first row in the result set.

CURRENT :- This will fetch the current row from the result set.

Similarly there are different keywords like LAST,NEXT,BEFORE,AFTER,PRIOR etc.

So for the above defined cursor , the fetch statement will be

FETCH FIRST Cursor1 INTO .....

Fetch first statement will position the cursor to the first row of the result set and then fetch it to the defined host variable.

Scrollable cursor makes it easy to scroll through the data thus saves amount of time and effort.

There are two types of scrollable cursor.

Yes there are again types of scrollable cursor but it make sense to understand on what factor they are diffracted.

1. Sensitive      : With this type of scrollable cursor,the program can access the
   data changed /updated by the user.So at the time, the data
   being fetch from the table using the cursor if some  other

process update/delete the data from table that get reflected in
the result set.

However, Inserts will not be reflected in the result set.

2. Insensitive :   This type of scrollable cursor will not show any change that are
   made to the data at the time of fetch so the underlying data will
   not be reflected in the result set

So the definition of cursor finally would finally look like

Declare Cursor1 (cursor name)

SENSITIVE SCROLL Cursor

Thing to remember: -

Though easier to use , you should not define every cursor to be scrollable as it require more overhead to the normal/conventional type of cursor.

To be specific , if you do not need to move forward /backword through the result set do not use scrollable cursor.

Until now we saw that using cursor(be it any type) we can fetch only one row at a time.We can also fetch multiple rows using single fecth statement in a cursor.

How do you do it?? we will see it in next part.

==> Click Here to Read Part III <==

Cusror in DB2 ---> Part I

2012-07-06T08:16:00.000-04:00

==> Click Here to Read Part II <==

When we write a Sql query in an application program and if it returns more than one row (Qualifying rows are more than 1) then DB2 cannot handle it and throws the SQLCODE -811.

What if, i have to select/update/delete more than one row from the table via application program??--> DB2 cursors is all what you need.

Cursor will process all the qualifying rows one by one sequentially.

To have cursor in your program , you need to follow below four steps.

1. Declare (Define) cursor

2. Open Cursor

3. Fetch Cursor

4. Close Cursor

1. Declare(Define) Cursor:

You declare it in Working storage section with below syntax

Declare cursor for < Your SQL Query >

You can also define your cursor in Procedure division,only condition is,it must be declare before Open cursor statement.

If you want to Update/delete the qualifying rows from the cursor then you have add the "FOR UPDATE OF" clause at the end of query in Declare cursor statement.

Declare cursor for< Your SQL Query >

For Update of /For UPDATE (If not sure about the column being updated)

For update of clause will take the lock on the rows being updated so that no one can change it before your update it.If you don't add "for update of" but update the rows ...lock will not be taken and there is danger of rows being updated by other process between your select and update statement and hence result can be unpredicted.

Declare(Define) Cursor is non executable statement.

2.Open Cursor :

This is an executable statement and executes the query from declare statement.Qualifying rows will be stored in buffer and pointer will be set just before the first qualified rows.If there are no qualifying rows( The predicate in your query yields no rows) pointer will be set after last row.

3.Fetch Cursor :

Fetch will first move the pointer to next row and then fetch that row from buffer until end of rows.Once the end of row happens SQLCODE +100 will be returned.

4.Close Cursor:

This will close the cursor which you have open.

Points to remember :

1. If you want to handle more than one row in your application program, use cursor

2. If multiple rows are returned by query and you didn't use cursor ,then first row will be fetched from the table and db2 will throw SQLCODE -811.

3. If you want only one row(any) from multiple qualifying rows from your query, you can avoid using cursor in this case. Use fetch first 1 row only (Depend on your DB2 version)

4. Cursor can either be defined in working storage section or procedure division. The condition for declaring it in procedure division is it should be defined prior to open cursor statement.

5. DB2 can process a cursor in two different ways:

a> It can create the result table(all qualifying rows) during the execution of the OPEN statement.

b> It can derive the result table rows as they are needed during the execution of later FETCH statements.If the result table is not read-only, DB2 uses the latter method. If the result table is read-only, either method could be used.

6. All the Cursors used in program will be automatically closed at the end of program or at COMMIT AND ROLLBACK statement (if used in the program).

If you don't want to close the cursor at COMMIT OR ROLLBACK. Define CURSOR WITH HOLD ,it will not allow the cursor to be closed and keep the position of pointer.

In next part we will discuss the different types of cursor and there use.

==> Click Here to Read Part II <==

Checkpoint Restart in DB2 Part - II

2011-06-30T15:54:00.000-04:00

The first part : Checkpoint Restart in DB2 Part - I

In first part we understood what is check point restart and why we use it. We also covered the problem associated with Check point restart and solutions to those problems.

Now, in this post we will see the step by step implementation of check point restart logic.

CHECKPOINT/RESTART Implementation:

STEP1:

Create the CHECKPOINT-COMMIT record in the working storage section, to store

the data, which is needed for the next unit of recovery.

STEP2:

In the procedure division MAIN para:

First check the restart status flag i.e. RESTART-IND of CHECKPOINT_RESTART

table.

If RESTART-IND = ‘N’ then

if any output file exists open output file in OUTPUT mode

start the normal process

end

If RESTART-IND = ‘Y’ then

Move the SAVE-AREA information to CHECKPOINT-COMMIT record

if any output file exists

do the FILE REPOSITION:

Open the output file in INPUT mode.

Repeatedly

Read the output record and INSERT it into GLOBAL temp table

FILE_POSITION_GTT

Until the last unit of recovery write count.

Close the output file.

Open the output file in OUTPUT mode.

open a cursor for a table FILE_POSITION_GTT

repeatedly fetch a cursor and write the record information into the output file

until end of cursor

close a cursor

end

If input for the program is from cursor then skip the rows until COMMIT-KEY.

If input for the program is from file then skip the records until COMMIT-KEY.

End.

Note: For more than one output files, delete GTT after repositioning each output file.

STEP3:

Make a count for each Insert’s/Update’s/Deletes in RECORDS-PROCESSEDUOR variable.

STEP4:

Go thro’ the logic and find out the appropriate place where COMMIT WORK can be hosted. There check the frequency of COMMITS:

IF RECORDS-PROCESSED-UOR > COMMIT-FREQ

KEY (input) value of the program TO COMMIT-KEY

MOVE checkpoint-commit record length TO SAVE-AREA-LEN

MOVE checkpoint-commit record TO SAVE-AREA-TEXT

Update the CHECKPOINT_RESTART table with this information

END-COMMIT

STEP5:

Before STOP RUN statement; reset the RESTART flag of the

CHECKPOINT_RESTART table.

i.e. MOVE ‘N’ TO RESTART-IND

Update the CHECKPOINT_RESTART table with the above information.

Sample COBOL code for CHECKPOINT/RESTART Logic:

CHECKPOINT-COMMIT RECORD DEFINITION

***************************************************************

* GLOBAL TEMPORARY TABLE CURSOR DECLARATION & OPEN *

****************************************************************

EXEC SQL

DECLARE FPG-FPOS CURSOR FOR

SELECT RECORD_NUMBER ,RECORD_DETAIL

FROM FILE_POSITION_GTT

ORDER BY RECORD_NUMBER

END-EXEC.

**************************************************************

***** CHECK-POINT RESTART DATA DEFINITIONS *****

**************************************************************

01 COMMIT-REC.

02 FILLER PIC X(16) VALUE 'REC. PROCESSED: '.

02 COMMIT-KEY PIC 9(06) VALUE 0.

02 FILLER PIC X(14) VALUE 'TOTAL COUNTS: '.

02 COMMIT-COUNTS.

03 WS-REC-READ PIC 9(06) VALUE 0.

03 WS-REC-REJT PIC 9(06) VALUE 0.

03 WS-REC-WRIT PIC 9(06) VALUE 0.

03 WS-RECP-READ PIC 9(06) VALUE 0.

03 WS-RECP-UPDT PIC 9(06) VALUE 0.

01 CHKPRSL-VARS.

02 RECORDS-PROCESSED-UOR PIC S9(09) COMP VALUE +0.

**************************************************************

***** CHECK POINT RESTART LOGIC SECTION *****

**************************************************************

RESTART-CHECK.

MOVE 'XXXXXX ' TO PROGRAM-NAME.

PERFORM RESTART-SELECT.

IF RESTART-IND = 'Y'

MOVE SAVE-AREA-TEXT TO COMMIT-REC

If input is from cursor the skip until the commit-key

If input is from file then skip the records until the commit-key

END-IF.

**************************************************

***** CHECK RESTART STATUS *****

**************************************************

RESTART-SELECT.

MOVE 0 TO RECORD-PROCESSED-UOR.

EXEC SQL

SELECT RESTART_IND ,COMMIT_FREQ ,RUN_TYPE ,SAVE_AREA

INTO :RESTART-IND ,:COMMIT-FREQ ,:RUN-TYPE ,:SAVE-AREA

FROM CHECKPOINT_RESTART

WHERE PROGRAM_NAME = :PROGRAM-NAME

END-EXEC.

EVALUATE SQLCODE

WHEN 0

IF RESTART-IND = 'Y'

DISPLAY '* * * * * * * * * * * * * * * * * * * * * * * * * **********'

DISPLAY ' ***PROGRAM - ' PROGRAM-NAME ' RESTARTED***'

DISPLAY '* * * * * * * * * * * * * * * * * * * * * * * * * **********'

DISPLAY ' '

END-IF

WHEN 100

PERFORM RESTART-INSERT

WHEN OTHER

MOVE 'RESTART-SELECT ' TO WS-PARA-NAME

MOVE 'CHECKPOINT_RESTART SELECT ERR' TO WS-PARA-MSG

PERFORM EXCEPTION-ROUTINE

END-EVALUATE.

**************************************************************

***** INSERT THE NEW RESTART STATUS RECORD *****

**************************************************************

RESTART-INSERT.

MOVE SPACES TO CALL-TYPE.

MOVE SPACES TO CHECKPOINT-ID.

MOVE 'N' TO RESTART-IND.

MOVE 'B' TO RUN-TYPE.

MOVE +500 TO COMMIT-FREQ.

MOVE ZEROES TO COMMIT-SECONDS.

MOVE +4006 TO SAVE-AREA-LEN.

MOVE SPACES TO SAVE-AREA-TEXT.

EXEC SQL

INSERT INTO CHECKPOINT_RESTART

(

PROGRAM_NAME ,CALL_TYPE ,CHECKPOINT_ID ,RESTART_IND

,RUN_TYPE,COMMIT_FREQ ,COMMIT_SECONDS ,COMMIT_TIME

,SAVE_AREA

)

VALUES

(

:PROGRAM-NAME ,:CALL-TYPE ,:CHECKPOINT-ID ,:RESTART-IND ,:RUN-TYPE

,:COMMIT-FREQ ,:COMMIT-SECONDS, CURRENT TIMESTAMP ,:SAVE-AREA

)

END-EXEC.

EVALUATE SQLCODE

WHEN 0

CONTINUE

WHEN OTHER

MOVE 'RESTART-INSERT ' TO WS-PARA-NAME

MOVE 'CHECKPOINT_RESTART INSERT' TO WS-PARA-MSG

PERFORM EXCEPTION-ROUTINE

END-EVALUATE.

**********************************************************

***** UPDATE THE CHECKPOINT RECORD *****

**********************************************************

RESTART-COMMIT.

MOVE 'Y' TO RESTART-IND.

EXEC SQL

UPDATE CHECKPOINT_RESTART

SET RESTART_IND = :RESTART-IND ,SAVE_AREA = :SAVE-AREA ,

COMMIT_TIME = CURRENT TIMESTAMP

WHERE PROGRAM_NAME = :PROGRAM-NAME

END-EXEC.

EVALUATE SQLCODE

WHEN 0

EXEC SQL COMMIT WORK END-EXEC

EVALUATE SQLCODE

WHEN 0

CONTINUE

WHEN OTHER

MOVE 'RESTART-COMMIT' TO WS-PARA-NAME

MOVE 'COMMIT ERROR' TO WS-PARA-MSG

PERFORM EXCEPTION-ROUTINE

END-EVALUATE

MOVE 0 TO RECORD-PROCESSED-UOR

WHEN OTHER

MOVE 'RESTART-COMMIT' TO WS-PARA-NAME

MOVE 'CHECKPOINT_RESTART UPDATE ERR' TO WS-PARA-MSG

PERFORM EXCEPTION-ROUTINE

END-EVALUATE.

*******************************************************************

***** RESET THE RESTART FLAG AT THE END OF PROGRAM *****

*******************************************************************

RESTART-RESET.

MOVE 0 TO RECORD-PROCESSED-UOR.

MOVE 'N' TO RESTART-IND.

EXEC SQL

UPDATE CHECKPOINT_RESTART

SET RESTART_IND = :RESTART-IND ,COMMIT_TIME = CURRENT TIMESTAMP

WHERE PROGRAM_NAME = :PROGRAM-NAME

END-EXEC.

EVALUATE SQLCODE

WHEN 0

EXEC SQL COMMIT WORK END-EXEC

WHEN OTHER

MOVE 'RESTART-RESET' TO WS-PARA-NAME

MOVE 'CHECKPOINT_RESTART DELETE ERR' TO WS-PARA-MSG

PERFORM EXCEPTION-ROUTINE

END-EVALUATE.

*************************************************************

***** OUTPUT FILE REPOSITION LOGIC SECTION *****

********* * ***** ********************************************

************************************************************************

***** GLOBAL TEMPORARY TABLE CURSOR DECLARATION & OPEN *****

*************************************************************************

FPG-OPEN.

EXEC SQL

OPEN FPG-FPOS

END-EXEC.

EVALUATE SQLCODE

WHEN 0

CONTINUE

WHEN OTHER

MOVE 'FPG-OPEN' TO WS-PARA-NAME

MOVE 'GLOBAL TEMP TABLE OPEN ERR' TO WS-PARA-MSG

PERFORM EXCEPTION-ROUTINE

END-EVALUATE.

************************************************************

**** GLOBAL TEMPORARY TABLE CURSOR FETCH *****

************************************************************

FPG-FETCH.

EXEC SQL

FETCH FPG-FPOS INTO :FPG-RECORD-NUMBER ,:FPG-RECORD-DETAIL

END-EXEC.

EVALUATE SQLCODE

WHEN 0

CONTINUE

WHEN +100

MOVE 0 TO FPG-RECORD-NUMBER

WHEN OTHER

MOVE 'FPG-FETCH ' TO WS-PARA-NAME

MOVE 'GLOBAL TEMP TABLE FETCH ERR' TO WS-PARA-MSG

PERFORM EXCEPTION-ROUTINE

END-EVALUATE.

************************************************************

***** GLOBAL TEMPORARY TABLE CURSOR CLOSE *****

************************************************************

FPG-CLOSE.

EXEC SQL

CLOSE FPG-FPOS

END-EXEC.

EVALUATE SQLCODE

WHEN 0

MOVE 0 TO FPG-RECORD-NUMBER

WHEN OTHER

MOVE 'FPG-FPOS-CLOSE ' TO WS-PARA-NAME

MOVE 'GLOBAL TEMP TABLE CLOSE ERR' TO WS-PARA-MSG

PERFORM EXCEPTION-ROUTINE

END-EVALUATE.

***********************************************************

***** GLOBAL TEMPORARY TABLE INSERTS *****

***********************************************************

FPG-INSERT.

ADD 1 TO FPG-RECORD-NUMBER.

EXEC SQL

INSERT INTO FILE_POSITION_GTT

(

RECORD_NUMBER ,RECORD_DETAIL

)

VALUES

(

:FPG-RECORD-NUMBER

,:FPG-RECORD-DETAIL

)

END-EXEC.

EVALUATE SQLCODE

WHEN 0

CONTINUE

WHEN OTHER

MOVE 'FPG-INSERT ' TO WS-PARA-NAME

MOVE 'GLOBAL TEMP TABL INSERT ERR' TO WS-PARA-MSG

PERFORM EXCEPTION-ROUTINE

END-EVALUATE.

RESTART-FILE-REPOSITION.

OPEN INPUT outputfile-name.

MOVE LENGTH OF output-record TO FPG-RECORD-DETAIL-LEN.

READ output-file INTO FPG-RECORD-DETAIL-TEXT.

PERFORM UNTIL FPG-RECORD-NUMBER >= output record count of last commit

PERFORM FPG-INSERT

READ output-file INTO FPG-RECORD-DETAIL-TEXT

END-PERFORM.

CLOSE output-filename

OPEN OUTPUT outputfile-name.

PERFORM FPG-OPEN.

PERFORM FPG-FETCH.

PERFORM UNTIL FPG-RECORD-NUMBER = 0

WRITE outputfile-record FROM FPG-RECORD-DETAIL-TEXT

PERFORM FPG-FETCH

END-PERFORM.

PERFORM FPG-CLOSE.

---------skip input file until the last commit------------------

DISPLAY ' *** ALREADY ' COMMIT-KEY ' RECORDS PROCESSED ***'.

DISPLAY ' '

DISPLAY ' '.

***********************************************************

************** E X C E P T I O N R O U T I N E **************

***********************************************************

EXCEPTION-ROUTINE.

MOVE SQLCODE TO WS-SQL-RET-CODE.

DISPLAY '*************************************************'.

DISPLAY '**** E R R O R M E S S A G E S ****'.

DISPLAY '*************************************************'.

DISPLAY '* ERROR IN PARA.....: ' WS-PARA-NAME.

DISPLAY '* MESSAGES.....: ' WS-PARA-MSG.

DISPLAY '*'.

DISPLAY '* SQL RETURN CODE..: ' WS-SQL-RET-CODE.

DISPLAY '*************************************************'.

Output file Disposition in JCL:

♦ In JCL, disposition must be given as DISP=(NEW,CATLG,CATLG) or DISP=(OLD,KEEP,KEEP)

♦ Override statement is needed for the output files if job abended:

1. GDG with DISP=(NEW,CATLG,CATLG)

Override stmt:

• Change +1 generation to 0 (current) generation

• DISP=(OLD,KEEP,KEEP)

2. GDG with DISP=(OLD,KEEP,KEEP)

Override stmt:

• Change +1 generation to 0 (current) generation

Output file with Disposition MOD:

• If output file is already existing, and program is appending records to that, then the File re-positioning must be handled in different way according to the requirements.

Internal Sort:

If any Commit-Restart program has Internal Sort, remove it and have an External Sort.

POINTS TO REMEMBER

All the update programs must use COMMIT frequency from the CHECKPOINT_RESTART table only
Avoid – Internal Sorts
Avoid – Mass updates (Instead, use cursor with FOR
UPDATE clause and update one record at a time)
On-call analyst should back-up all the output files before
restart (The procedure should be documented in APCDOC)
Reports to dispatch should be sent to a flat file; send the file
to dispatch up on successful completion of the job
Save only the working storage variables that are required
for RESTART in the CHECKPOINT_RESTART table
RESET the RESTART_IND flag at the end of the program
If COMMIT-RESTART logic is introduced in an existing
program then make relevant changes to the PROCJCL.

Checkpoint Restart in DB2 Part - I

2011-04-06T01:24:00.000-04:00

>> Click here for Part II <<

Before getting into what is check point restart and how to implement it,you need to understand when and why you should go for it.

HERE’S THE SCENARIO:

Suppose, a batch program that basically reads an input file and posts the updates/inserts/deletes to DB2 tables in the database was abended before the end of the job because of some reasons; Is it possible to tell - How many input records were processed? Were any of the updates committed to the database or can the job be started from the beginning?

Assume that COMMIT logic was not coded for large batch jobs that process millions of records. If an ABEND occurs all database updates will be rolled back and the job can be resubmitted from the beginning. If an ABEND occurs near the end of the process, the rollback of all the updates is performed. Also, DB2 will maintain a large number of locks for a long period of time, reducing concurrency in the system. In fact, the program may ABEND if it tries to acquire more than the installation-defined maximum number of locks.

This involves setting up a batch-restart control table (CHECKPOINT_RESTART in our case) to store the last input record processed and other control information. The restart control table can also be used as an instrumentation table to control the execution, commit frequency, locking protocol and termination of batch
jobs.

One of the problems with restart is synchronizing DB2 tables and output files(If your program updates some output file). DB2 will rollback all work on DB2 tables to the last commit point; but for output files we have to delete all the records up to the last commit point. (One option to do this would be via a global temporary table, FILE_POSITION_GTT, See FILE REPOSITIONING section for further details.).

COMMIT Function

The COMMIT statement ends a unit of recovery and commits the relational database changes that were made in that unit of recovery. If relational databases are the only recoverable resources used by the application process, COMMIT also ends the unit of work. The unit of recovery in which the statement is executed is ended and a new unit of recovery is effectively started for the process. All changes made by ALTER, COMMENT ON, CREATE, DELETE, DROP, EXPLAIN, GRANT, INSERT, LABEL ON SQL connections are ended when any of the following apply:

► The connection is in the release pending state
► The connection is not in the release pending state but it is a remote connection and:

The DISCONNECT(AUTOMATIC) bind option is in effect, or
The DISCONNECT (CONDITIONAL) bind option is in effect and an open WITH HOLD cursor is not associated with the connection.

For existing connections,

► All open cursors that were declared with the WITH HOLD option are preserved, along with any SELECT statements that were prepared for those cursors.

► All other prepared statements are destroyed unless dynamic caching is enabled.

► If dynamic caching is enabled, then all prepared SELECT, INSERT, UPDATE and DELETE statements
that are bound with KEEPDYNAMIC (YES) are kept past the commit.
Prepared statements cannot be kept past a commit if:

      * SQL RELEASE has been issued for that site, or
      * Bind option DISCONNECT(AUTOMATIC) was used, or
      * Bind option DISCONNECT (CONDITIONAL) was used and there are no hold cursors.

► All implicitly acquired locks are released, except for those required for the cursors that were

A place to store the details (CHECKPOINT-COMMIT record) pertaining to the current execution of the program, like various counts (number of inserts/deletes/updates/selects), number of processed, processing dates, and other details which are needed in the program after a RESTART.
A reliable FILE RE-POSITIONING logic with minimal changes to the existing PROCJCL.
Flexibility, to modify the commit frequency without changing the program code.

Where we can store this CHECKPOINT-COMMIT record?

We can store the CHECKPOINT-COMMIT record, COMMIT-FREQUENCY and other relevant
information in a DB2 table .

CHECKPOINT_RESTART TABLE DESCRIPTION

FILE RE-POSITIONING
At restart, all records written to the output file since the last commit will have to be removed. To accomplish this, FILE_POSITION_GTT global temporary table is used. SQL statements that use global temporary tables can run faster because:

• DB2 does not log changes to global temporary tables
• Global temporary tables do not experience lock contention
• DB2 creates an instance of the temp table OPEN/SELECT/INSERT/DELETE stmts. only occur:

• An instance of a temporary table exists at the current server until one of the following actions occur:
• The remove server connection under which the instance was created terminates
     For ROLLBACK stmt, DB2 deletes the instance of the temporary table.
     For COMMIT stmt, DB2 deletes the instance of the temporary table unless cursor
     for accessing temporary table is defined WITH HOLD and is open.
•  The application process ends.

In next part we will discuss step by step process of check point restart installation along with sample code.

Is it a PLAN? Is it a PACKAGE? NO, It's a CONFUSION !!!

2011-03-23T06:53:00.000-04:00

No matter how long programmers have worked with DB2 for z/OS , they still have the confusion on difference between a plan and a package and what the heck is that collection anyway.

In this post i will be specific on the PLAN/PACKAGE difference and the things related to it (Such as COLLECTION and PKLIST). If you want to know from basic and detailing of what is PLAN/PACKAGE and how it get formed and why we need it then ,THIS is the great place to start up with,rather i would recommend you to read it before going further with this post.

OK so here it goes.

The PLAN/PACKAGE are nothing but the container which contains the logic of how your SQL statement in the program should get executed.[Remember when we write any query in SQL, we say what we want and NOT how it is to be done (We don't care how DB2 internally does it)].So PLAN/PACKAGE contains the LOGIC how it is to be done.Then this PLAN/PACKAGE is used with load module (For COBOL) to run your application program smoothly.

Now why two things?? PLAN and PACKAGE , if both are the same (Contains the LOGIC for executing your SQL) and what exactly is the difference between the two.

Binding DBRM to a PLAN :

The PLAN contains the (Logic of) one or DBRM OR one or more PACKAGES OR the combination of both.

The PLAN is executable.When i say executable it means ,it can be executed with COBOL load module to run your application program.

Now let's consider there are 10 programs with names A TO J. So there will be 10 DBRM ( 1 DBRM for each program). I bind all 10 DBRM's to a PLAN (with name 'PLANA').Now let's say i got a requirement to change one of the Program ( let's say Program 'B') then i NOT ONLY need to bind this DBRM again to plan PLANA but all remaining 9 DBRM too (even though they didn't went under any change) and that is the biggest disadvantage of a binding a DBRM to a PLAN.NOT only this, if in my system there are various other PLAN's which uses this DBRM (The one which corresponds to Program 'B') then all those PLAN'S need to rebind along with all the other existing DBRM'S they may be having.

There are various parameters called as Bind parameter are available, when you bind your DBRM to a PLAN. These Bind parameters helps in deciding various important aspects for you program such as when to acquire a lock for the particular resource in your program,when to release the lock,what qualifier to use for the objects refered in your application program,the owner of plan etc. So when you bind your all DBRM's (10 DBRM in this example) to PLAN you have only one set of combination of these bind parameters for all 10 DBRM (For example you can not set different isolation level for different Program/DBRM , it has to be same)

Binding DBRM to a PACKAGE:

The PACKAGE contains the (Logic of) ONLY one DBRM.It's a single bound DBRM.
The PACKAGE is not executable that means it can not be use with COBOL load module to run your Application program.In order to make your PACKAGE to be executed it has to bonded to PLAN again.So you bind your DBRM to PACKAGE and then in turn this PACKAGE to PLAN.I know what you must be thinking now,why to go this way, isn't it better to bind DBRM to straight away to PLAN.NO IT IS NOT, i will explain you why it is so.When i say you have to bind the DBRM to PACKAGE and then this PACKAGE to PLAN again,you need not to do it for ever package. What i mean is, let's say i have 50 PACKAGES to bind to a PLAN no need to BIND it 50 times instead just bind a single package to PLAN using PKLIST(Package List) that's it ,you are done and remaining 49 PACKAGES will be automatically added to your plan (For this all your 49 Packages need to be in same PKLIST what we have bonded with PLAN earlier)

Let's take the same example what we use above for PLAN. I have 10 programs A TO J with 10 DBRM. Now in case of PACKAGE , I will bind each DBRM to one PACKAGE so i will have 10 Packages then include all 10 PACKAGES to single PKLIST (Package List) bind the Package list to a PLAN.Now if one of my Program changes , then i have to rebind only the DBRM corresponding to the Program changing and NOT the remaining DBRM'S/PROGRAM (Which didn't went under the change)

Regarding Bind parameter, since i can bind 10 DBRM to 10 different PACKAGES, I can have various combination of Bind parameters for all PACKAGES.(For example Isolation level of CS for 1 PACKAGE and RR to other etc.)

Having said that, you will wonder,you have only the advantages of binding DBRM to PACKAGE and don't have any reason to go for binding DBRM to a PLAN.

That is the reason , IBM is going to remove this facility of binding DBRM to PLAN in DB2 V9 Onwards
For more detail on the same refer HERE.

JQEUME5S4HKU

Lock escalation in DB2.

2011-03-17T08:17:00.000-04:00

Lock escalation as it's name suggest is nothing but escalating the lock on the object from lower level to the higher.Lock escalation is controlled by the parameter LOCKMAX of CREATE TABLESPACE and ALTER TABLESPACE.

The purpose of lock escalation is to reduce the impact of low level locks, both in terms of CPU and storage, at the possible expense of concurrency. When the number of low level locks exceeds a specified threshold, DB2 attempts to promote the high level intent locks, either IS or IX, to S or X, respectively. If this succeeds, then the low level locks are all released and no more low level locks are requested. The lock promotion request may itself fail if other processes hold incompatible locks, either low or high level, for longer than the timeout period. If the higher level lock cannot be acquired before the timeout value has been reached, then the process times out, all updates are rolled back, and all locks are released.

LOCKMAX :

The parameter LOCKMAX of CREATE TABLESPACE and ALTER TABLESPACE controls the maximum number of page or row locks that an application process can hold simultaneously in a single table space. In segmented table spaces, a single table can be locked. Segmented table space organization allows some tables to perform page or row locking and other tables in the same table space to perform table locking. If the number of locked pages or rows for a table exceeds the limit, a table lock is acquired for that table.

Lock escalation is controlled by the LOCKMAX option. If a process requests more than the number specified in LOCKMAX, the intent lock on the table space, partition, or table is promoted to S or X and the page or row child locks are released.

Lock escalation occurs when the number of locks held by a single process exceeds the LOCKMAX value on a table space. Lock escalation involves obtaining a table or table space lock, then releasing all of the page or row locks. Lock escalation is a safety valve that DB2 provides in case an application overuses system resources. LOCKMAX 0 means that you are disabling this useful feature. Loss of concurrency is less likely to be incurred with lock escalation if the majority of the rows or pages in a table space are already locked, as long as RELEASE(COMMIT) is used. Lock escalation in such a situation reduces storage use, but more importantly, reduces the CPU time needed to traverse a lock hash synonym chain. Lock escalation also reduces the Internal Resource Lock Manager (IRLM) latch suspension time, which also reduces CPU time.

Lock escalation for a partitioned table space will cause DB2 to promote the intent locks for every partition where child locks are currently held. Any subsequent access to a new partition will cause DB2 to request gross locks on that partition from the outset. The LOCKMAX figure applies to all locks held at the table space level when it is a multi-table table space.

The column LOCKMAX on the catalog table SYSIBM.SYSTABLESPACE stores the maximum number of locks per user on the table or table space before escalating to the next locking level. A value of -1 indicates LOCKMAX=SYSTEM. Any escalation process is suspended during the execution of SQL statements for ALTER, CREATE, DROP, GRANT, and REVOKE.

Lock escalation is an expensive process, both in terms of lock management and concurrency. In today’s high availability environment, you should design the application to allow no reason for lock escalations to occur. The benefit of lock escalation is in protecting DB2 from an excessive number of locks held in the IRLM on behalf of poorly designed applications that are not releasing their locks.

Lock escalation also prevents poorly designed applications from reaching the maximum number of locks per user specified by the NUMLKUS DSNZPARM. From an individual application perspective, this may seem like a good thing, but from an overall availability and performance standpoint, it is merely rewarding bad design. The focus should be on identifying and correcting those poorly designed applications that take an excessive number of locks.

For critical large table spaces that are accessed by many application processes concurrently, choose the LOCKMAX value so that there will not be any lock escalation within a unit of work, if NUMLKTS DSNZPARM is not large enough.

Row Identifier in DB2 (V9 Onwards)

2011-03-13T13:12:00.000-04:00

I use to wonder why we don't have something called as row identifier in DB2 as it is there in oracle or MySQL.Finally IBM added it in V9 and continued in V10.

Let me explain what row identifier is and what purpose it serves.

The RID function returns the record ID (RID) of a row. The RID is used to uniquely identify a row.

The function might return a different value when it is invoked multiple times for a row. For example, after the REORG utility is run, the RID function might return a different value for a row than would have been returned prior to the REORG utility being run. The RID function is not deterministic.

DB2 might reuse RID numbers when a REORG operation is performed. If the RID function is used to obtain a value for a row and an application depends on that value remaining the same as long as the row exists, consider the following alternatives:

Add a ROW ID column to the table to provide a value that can be associated with each row, rather than invoking the RID function to generate a value for a row.
Define a primary key for the table, using the columns of the primary key to ensure uniqueness, rather than invoking the RID function to generate a value for a row.

Example : Return the RID and last name of employees who are in department '20':

 SELECT RID(EMP), LASTNAME
     FROM EMP
     WHERE DEPTNO = '20';

Let me give you the practical example of use of row identifier in my shop.

We had one table in our database which didn't had any primary key to it and hence no facility to identify any unique row in the table (Agree, the table was badly designed by my DBA)

There was one program which use to insert rows in this table, but sometimes it inserts the duplicate and we didn't had any alarm for it as there is no primary key and hence no unique index.So while fetching row from this table in the program,it used to give -811 sqlcode.So to resolve this abend we have to delete two rows(duplicate) and insert one (we can't delete only one duplicate row as we don't have anything unique to identify the row ) So here it was the RID which helped us to delete the duplicate row straight away (no need to insert two rows and then insert one).

Isolation Level in DB2.

2011-03-07T14:55:00.000-05:00

As i mentioned earlier, i wanted to take this concept in a separate post.Isolation level parameter is the one you specify while binding your Application program's DBRM in to Plan/Package.

The problem with Isolation level is no matter how many times you read it or from which source you read it you won't understand it.I mean you just read it but when you have to actually use it in you shop, you wonder how to use it and this is because you don't have any practical example of it .once you have any real world example of each isolation level, you most probably will remember and most importantly use wisely in your instances.

So let's just understand each isolation level in detail with the real world example.

First of all understand what is isolation level and why we need it??

Several different users can access and modify data stored in a DB2 database at the same time, the DB2 Database Manager must be able to allow users to make necessary changes while ensuring that data integrity is never compromised.The sharing of resources by multiple interactive users or application programs at the same time is known as concurrency.
One of the ways DB2 enforces concurrency is through the use of isolation levels, which determine how data accessed and/or modified by one transaction is "isolated from" other transactions.

DB2 recognizes and supports the following isolation levels:

Repeatable Read (RR)
Read Stability (RS)
Cursor Stability (CS)
Uncommitted Read (UR)

The Repeatable Read Isolation Level

The Repeatable Read isolation level is the most restrictive isolation level available.

When it's used, the effects of one transaction are completely isolated from the effects of other concurrent transactions.

When this isolation level is used, every row that's referenced in any manner by the owning transaction is locked for the duration of that transaction. As a result, if the same SELECT SQL statement is issued multiple times within the same transaction, the result data sets produced are guaranteed to be identical. In fact, transactions running under this isolation level can retrieve the same set of rows any number of times and perform any number of operations on them until terminated, either by a commit or a rollback operation. However, other transactions are prohibited from performing insert, update, or delete operations that would affect any row that has been accessed by the owning transaction as long as that transaction remains active.

To ensure that the data being accessed by a transaction running under the Repeatable Read isolation level is not adversely affected by other transactions, each row referenced by the isolating transaction is locked—not just the rows that are actually retrieved or modified.

Thus, if a transaction scans 1,000 rows in order to retrieve 10, locks are acquired and held on all 1,000 rows scanned—not just on the 10 rows retrieved.

Real World Example : So how does this isolation level work in a real-world situation? lets's see..

Suppose you use a DB2 database to keep track of hotel records that consist of reservation and room rate information, and you have a Web-based application that allows individuals to book rooms on a first-come, first-served basis.

If your reservation application runs under the Repeatable Read isolation level, a customer scanning the database for a list of rooms available for a given date range will prevent you (the manager) from changing the room rate for any of the room records that were scanned to resolve the customer's query. Similarly, other customers won't be able to make or cancel reservations that would cause the first customer's list of available rooms to change if the same query were to be run again (provided the first customer's transaction remained active). However, you would be allowed to change room rates for any room record that was not read when the first customer's list was produced; likewise, other customers would be able to make or cancel room reservations for any room whose record was not read in order to produce a response to the first customer's query.

The Read Stability Isolation Level

The Read Stability isolation level is not quite as restrictive as the Repeatable Read isolation level; therefore, it does not completely isolate one transaction from the effects of other, concurrent transactions.

When the Read Stability isolation level is used, only rows that are actually retrieved or modified by the owning transaction are locked.

Thus, if a transaction scans 1,000 rows in order to retrieve 10, locks are only acquired and held on the 10 rows retrieved, not on the 1,000 rows scanned.

Because fewer locks are acquired, more transactions can run concurrently. As a result, if the same SELECT SQL statement is issued two or more times within the same transaction, the result data set produced may not be the same each time.

As with the Repeatable Read isolation level, transactions running under the Read Stability isolation level can retrieve a set of rows and perform any number of operations on them until terminated. Other transactions are prohibited from performing update or delete operations that would affect the set of rows retrieved by the owning transaction as long as that transaction exists; however, other transactions can perform insert operations.

Real World Example : We will take the same example and see how read stability change it.

Now, when a customer scans the database to obtain a list of rooms available for a given date range, you (the manager) will be able to change the rate for any room that does not appear on the customer's list. Likewise, other customers will be able to make or cancel reservations that would cause the first customer's list of available rooms to change if the same query were to be run again. As a result, if the first customer queries the database for available rooms for the same date range again, the list produced may contain new room rates and/or rooms that were not available the first time the list was generated.

The Cursor Stability Isolation Level

The Cursor Stability isolation level is even more relaxed than the Read Stability isolation level in the way it isolates one transaction from the effects of other concurrent transactions.

The cursor stability isolation level only locks the row that is currently referenced by a cursor that was declared and opened by the owning transaction. (The moment a record is retrieved from a result data set, a pointer—known as a cursor—will be positioned on the corresponding row in the underlying table, and that row will be locked. The lock acquired will remain in effect until the cursor is repositioned—more often than not by executing the FETCH SQL statement—or until the owning transaction terminates.) And because only one row-level lock is acquired, more transactions can run concurrently.

The Cursor Stability isolation level is the isolation level used by default.

When a transaction using the Cursor Stability isolation level retrieves a row from a table via a cursor, no other transaction is allowed to update or delete that row while the cursor is positioned on it. Other transactions, however, can add new rows to the table as well as perform update and/or delete operations on rows positioned on either side of the locked row—provided the locked row itself wasn't accessed using an index. Once acquired, the lock remains in effect until the cursor is repositioned or until the owning transaction is terminated. (If the cursor is repositioned, the lock being held is released and a new lock is acquired for the row to which the cursor is moved.) Furthermore, if the owning transaction modifies any row it retrieves, no other transaction is allowed to update or delete that row until the owning transaction is terminated, even though the cursor may no longer be positioned on the modified row.

As you might imagine, when the Cursor Stability isolation level is used, if the same SELECT SQL statement is issued two or more times within the same transaction, the results returned may not always be the same. In addition, transactions using the Cursor Stability isolation level will not see changes made to other rows by other transactions until those changes have been committed.

Real world example: Let's reserve the hotel again by using Cursor Stability

When a customer scans the database for a list of rooms available for a given date range and then views information about each room on the list produced (one room at a time), you (the manager) will be able to change the room rates for any room in the hotel except the room the customer is currently looking at (for the date range specified). Likewise, other customers will be able to make or cancel reservations for any room in the hotel except the room the customer is currently looking at (for the date range specified). However, neither you nor other customers will be able to do anything with the room record the first customer is currently looking at. When the first customer views information about another room in the list, you and other customers will be able to modify the room record the first customer was just looking at (provided the customer did not reserve it for himself). Again, neither you nor other customers will be able to do anything with the room record at which the first customer is currently looking.

The Uncommitted Read Isolation Level

The Uncommitted Read isolation level is the least restrictive isolation level available. In fact, when the Uncommitted Read isolation level is used, rows retrieved by a transaction are only locked if the transaction modifies data associated with one or more rows retrieved or if another transaction attempts to drop or alter the table the rows were retrieved from.

This isolation level is typically used for transactions that access read-only tables and views and for transactions that execute SELECT SQL statements for which uncommitted data from other transactions will have no adverse affect.

As the name implies, transactions running under the uncommitted read isolation level can see changes made to rows by other transactions before those changes have been committed. However, such transactions can neither see nor access tables, views, and indexes that are created by other transactions until those transactions themselves have been committed. The same applies to existing tables, views, or indexes that have been dropped; transactions using the uncommitted read will learn that these objects no longer exist only when the transaction that dropped them is committed. (It's important to note that when a transaction running under this isolation level uses an updatable cursor, the transaction will behave as if it is running under the Cursor Stability isolation level, and the constraints of the Cursor Stability isolation level will apply.)

Real World example : Haven't you reserve the hotel yet?? ooh poor boy..

When a customer scans the database to obtain a list of available rooms for a given date range, you (the manager) will be able to change the room rates for any room in the hotel over any date range. Likewise, other customers will be able to make or cancel reservations for any room in the hotel, including the room at which the customer is currently looking. In addition, the list of rooms produced for the first customer may contain records for rooms for which other customers are in the processing of reserving or canceling reservations.

Choosing the Proper Isolation Level

So how do you decide which isolation level to use? The best way is to identify which types of phenomena are unacceptable, and then select an isolation level that will prevent those phenomena from occurring. A good rule of thumb is:

Use the Repeatable Read isolation level if you're executing large queries and you don't want concurrent transactions to have the ability to make changes that could cause the query to return different results if run more than once.

Use the Read Stability isolation level when you want some level of concurrency between applications, yet you also want qualified rows to remain stable for the duration of an individual transaction.

Use the Cursor Stability isolation level when you want maximum concurrency between applications, yet you don't want queries to see uncommitted data.

Use the Uncommitted Read isolation level if you're executing queries on read-only tables/views/databases or if it doesn't matter whether a query returns uncommitted data values.

Always keep in mind that choosing the wrong isolation level for a given situation can have a significant negative impact on both concurrency and performance—performance for some applications may be degraded as they wait for locks on resources to be released.

Specifying the Isolation Level to Use

Although isolation levels control concurrency at the transaction level, they are actually set at the application level. Therefore in most cases, the isolation level specified for a particular application is applicable to every transaction initiated by that application. (It is important to note that an application can be constructed in several different parts, and each part can be assigned a different isolation level, in which case the isolation level specified for a particular part is applicable to every transaction that is created within that part.)

For embedded SQL applications, the isolation level is specified at precompile time or when the application is bound to a database (if deferred binding is used). In this case, the isolation level is set using the ISOLATION [RR | RS | CS | UR] option of the PRECOMPILE and BIND commands.

DB2 Version 8.1 and later provides a WITH clause (WITH [RR | RS | CS | UR]) that can be appended to a SELECT statement to set a specific query's isolation level to Repeatable Read (RR), Read Stability (RS), Cursor Stability (CS), or Uncommitted Read (UR). A simple SELECT statement that uses this clause looks something like this:

SELECT * FROM employee WHERE empid = '001' WITH RR

I hope after reading this article you will no more have any problems on isolation level.

Understanding Locking in DB2 - IV

2011-03-05T01:09:00.000-05:00

Read Part-I

Read Part-II

Read Part-III

Lock duration

Lock Duration is a very important concept in locking when the lock states are not compatible with each other.This is the reason, you get those famous deadlock and timeout issues.So you should have sound knowledge of this concept before designing your program.

The duration of a lock is defined as the length of time a lock is held and varies according to the type of lock. Page or row locks are acquired when individual rows are accessed and are released at different times, depending on whether the data is accessed for read-only or for update and on the isolation level specified.High level locks are typically acquired implicitly, based on options specified at BIND or REBIND time.
Because DB2 does implicit high level locking, it is critical that the application designer understand the options that influence when high level locks are acquired, when they are released, and the state of the lock.

Duration of table space, partition, and table locks

Every plan executed in DB2 must acquire a high level lock that can either be a table space or partition lock and, if the table space is segmented, then a table lock as well. The exception this rule is that locks are not acquired on the table space or table if uncommitted read isolation is specified. However, DB2 does acquire a mass delete lock. A mass delete is when a DELETE statement is executed with no WHERE clause such that all rows are deleted. DB2 uses the mass delete lock to serialize a mass delete process with an uncommitted reader.

DB2 provides options to control the point at which the high level locks are requested.

The BIND and REBIND options ACQUIRE and RELEASE is used to control when the high level locks are taken and released.

The possible values for the ACQUIRE option are ALLOCATE and USE. If you use ACQUIRE(ALLOCATE), all of the needed locks on all of the table spaces, partitions, and tables used by the plan are acquired at the first SQL call. If you use ACQUIRE(USE), only the needed locks on the table spaces, partitions, and tables are acquired for a shorter time, as and when the objects are accessed.

DB2 packages and dynamic SQL always use ACQUIRE(USE).

The time when the high level locks are released is influenced by the RELEASE option. The possible values for this option are DEALLOCATE and COMMIT. If you use RELEASE(DEALLOCATE), the locks are released only when the thread terminates. If you use RELEASE(COMMIT), the locks are released at commit time unless there are held cursors.

Duration of page or row locks

The duration of page or row locks depends on whether they are acquired and released in a read-only or read and write environment.

Read-only

BIND and REBIND ISOLATION option controls when page or row locks are acquired and released in a read-only environment.

The duration and number of page or row locks depend on the nature of the process and the value for the ISOLATION option specified at BIND or REBIND. The most common ISOLATION option is cursor stability (CS), which is the default as of DB2 9 for z/OS. With cursor stability as the ISOLATION option, DB2 returns committed data and provides stability of data under updateable cursors. If a query is answered through a work file,

For example, a concurrent thread may update the data in the database that generated the data in the work
file.The repeatable read (RR) isolation retains page locks even though the process logic moves through several pages in the same table during a unit of work. If the pages are accessed again, repeatable read provides repeatability: the data cannot be changed by any other concurrent process. This is true for read stability isolation also.

Read and write

A process that uses a cursor adds another dimension to locking. Pages read acquire a U-lock, initially indicating update intent. But before a column can be updated or a row can be deleted, the U-lock on the page is changed to an X-lock and is released at commit.

Here we come to an end of this Understanding of locking series.I will explain Isolation level in detail in different post as this topic deserve it, due to its immense importance to the programmer.Then there are various other important concept in terms of locking as lock avoidance,timeout,deadlock etc. which will be covered in coming posts.

Understanding Locking in DB2 - III

2011-03-03T15:05:00.000-05:00

Read Part-I

Read Part-II

In this third installment ,we will discuss the Compatibility rules for lock states.

The major effect of the lock state is to determine whether one lock state is compatible with another. Locks of some states do not exclude all other users. Assume that process A holds a lock on a table space that process B also wants to access. DB2 requests, on behalf of process B, a lock of some particular state. If the lock mode of process A permits a request from process B, then the two lock states are said to be compatible.

If the two locks are not compatible, process B cannot proceed. It must wait until process A releases the lock.

Compatibility of page and row lock states.

I will just draw the table which will make you better understand it.

This table shows whether page or row locks of any two states are compatible (Yes) or not (No).

No question of compatibility of page lock with a row lock can arise, because a table space cannot use both page and row locks.

Two or more processes can concurrently access data by using a page or row share lock (S lock). However, two or more processes cannot concurrently access data using a page or row update-intent lock (U lock). That is, only one process can acquire a U lock on a specific page or row at any instant. Even though a page or row may be U locked, other processes can access that page or row (but for read-only) by acquiring an S lock. If the process holding the U lock wants to do an update or delete, the U lock must be promoted to X lock before this can be
done.

However, the promotion to an X lock does not occur if any other concurrent process holds an S lock on that page or row. Eventually, assuming the S locks are released before the timeout interval, the U lock is promoted to an X lock. While a page or row is U locked, if any other concurrent process wants to do an update or delete, it must first acquire a U or X lock, but has to wait and eventually either the U or X lock is acquired or a timeout occurs.

Compatibility of table space, partition, and table lock states

Compatibility for table space, partition, and table locks is slightly more complex.

Below table shows whether or not the high level locks of table space, partition, or table of any two modes are compatible.

The next and the final post on this topic will be coming soon, which will be on Lock duration.

Understanding Locking in DB2 - II

2011-03-03T03:46:00.000-05:00

This is the second installment of locking concept ( Check the first part here ).

In first part, we saw how on various objects locks can be taken and the types of lock (Lock states) for those objects.

In this part we are going to discuss in detail what are these different lock states and their meaning.In the sense when i say that there is an Intent share lock on my table then what does it exactly mean, what kind of lock my particular program holds and more importantly what kind of lock it allows to other application on the same object(resource).

Let's first discuss for all the lock states for high level lock (Table space, partition, and table).

( If you have any problem in understanding the terms here such as high level and level locks or lock state , then just check out the first part).

As discuss , there are the six possible lock states for a high level lock :

Intent share (IS)

The lock owner can read data in the table space, partition, or table, but not change it. The lock owner may acquire an S(Share) lock on the row or page containing the row. Concurrent processes may both read and change data.

Intent exclusive (IX)

The lock owner and concurrent processes can read and change data in the table space,partition, or table. The lock owner acquires a U(Update) lock or S (Share) lock on a page or row. When the data is to be changed, the U lock or S lock is promoted to an X(Exclusive) lock.

Share (S)

The lock owner and concurrent processes can read, but not change, data in the tablespace, partition, or table.There are no S locks on a page or row.

Update (U)

The lock owner can read, but not change, the locked data. However, the owner can promote the U lock to an X lock and then change the data. Processes concurrent with the U lock can acquire an S lock and read the data, but no concurrent process can acquire a U lock. The lock owner does not need page or row locks.

Share with intent exclusive (SIX)

The lock owner can read and change data in the table or table space. Concurrent processes can read the data in the table or table space, but not change it. Only when the lock owner changes data is the X lock acquired on a page or row.

Exclusive (X)

The lock owner may read and change data in the table space or table. Only the concurrent processes using uncommitted read isolation may access the table space or table.

Now we will discuss the lock states for the low level locks ( Page and row).

There are the three possible lock states (S,U AND X) for the low level lock size of a page or row.

Remember (As mentioned in previous post) these LOCKS are applicable only if there is an intent lock on the table space or partition or the table).

Share lock

The lock owner and any concurrent process can read, but not change, the locked DB2 object. Other concurrent processes may acquire share or update locks on the DB2 object. Also called an S lock.

Update

The lock owner can read the DB2 object and has the intention to change it. Concurrent processes may acquire share locks and read the DB2 object, but no other process can acquire an update lock. Update locks are promoted to exclusive locks before DB2 actually changes the DB2 object. Promotion to exclusive lock may cause a suspension if other processes are holding share locks. Also called a U lock.

Exclusive

Only the lock owner can read or change the locked data, with the following exceptions. Concurrent applications using uncommitted read isolation can read the exclusively locked data. Also, if lock avoidance techniques indicate that the exclusively locked data is require currency of data.

Fair enough, In next part we will discuss compatibility between various lock states.

Understanding Locking in DB2 - I

2011-02-28T11:07:00.000-05:00

Locking in DB2 is the least understood concept among the programmer's.
There are lots of chaos ranging from what is lock to why we need it to end up discussing what exactly is isolation level etc.and i don't blame them.

Some programmer's who have somewhat understanding about it ,they have there different set of questions to ask on it ,such as we have something called as LOCKSIZE parameter while creating tablespace, then we have bind parameter such as Isolation Level and Aquire and Release. Now what exactly the function of these parameter? do we need all of them for deciding the kind of lock DB2 will take on resource for my particular Application Program.

Well to answer these questions let me clarify first that , Locking is not as simple subject as to cover it into a single post , if one want to understand the detailing of it , it may take up reading fully dedicated book on it.

I will try to explain the overview of it in this post and will cover the various aspect of it in coming posts.

Well i will not dwell much into what is the definition of lock and why we need it . you can read it anywhere (better go for IBM manual )

So read on

There are three things which you need to understand first in order to get rid of the concept of Locking.

-- The object on which the LOCK to be taken (That is Table,Page,Row etc.).
   It can also be called as size of the LOCK.

-- The mode or type of Locking (Share,Update etc.).
    This controls the degree to which the resource can be accessed concurrently
     by multiple processes.

-- The duration of lock.This refers to the length of time that the lock is held.

Now lets discuss the above mentioned point one by one.

Lock Size

The size of the lock determines how much data is within the scope of a lock (hence the term lock size).

For example, a page lock includes all rows on a page, while a row lock just covers a single row.

The lock size is determined by the LOCKSIZE parameter of the CREATE and ALTER TABLESPACE data definition language statements. The options are ANY, TABLESPACE,TABLE, PAGE, ROW.
ANY is the default. TABLE should only be used for segmented table spaces.

DB2 uses locks of the following sizes:

– Table space

– Partition

– Table

– Page

– Row

Out of theses first three are high level locks(table space,partition,table) and the next two (page and row) are called as low level locks.The low level locks cannot be taken before taking the corresponding high level lock for them while high level locks can be taken directly ( except intent share, intent exclusive, and share with intent exclusive.) I know this statement is bit of confusing here but had to go with flow,don't worry will explain below.

Lock state or lock mode

The state of a lock, also called the mode, determines the type of access to the locked object permitted to the lock owner and to any other concurrent application processes. The lock states available for an object depend on the lock size.

There are three possible states for a low level lock:

- Share
- Update
- Exclusive

Now these three modes of locks are available for low level lock( page and row) while high level lock size have these three modes of locking with the addition of three intent locks referred to as intent share, intent exclusive, and share with intent exclusive.

The primary difference is that whenever an intent lock is held,DB2 also has to request a lock at the page or row level, hence the reference to low level locks.

The share, update, and exclusive locks at the table space, partition, and table level are also
referred to as gross locks because they do not require low level locks to be taken.

Now, i think you got what i was trying to say previously.

When to take a lock ?

The low level locks, that is page and row are acquired when necessary while a process is being executed.
When the high level locks, that is, table space, partition, or table, are acquired depends on the ACQUIRE BIND option.

For ACQUIRE(ALLOCATE), the high level locks are acquired at the first SQL call.

For ACQUIRE(USE), the high level locks are acquired at the first actual use of the table.

The ACQUIRE bind options have no influence on when high level locks are acquired for dynamic SQL, which is always when the object is first accessed.

So until now we are clear with size and modes of lock. Lets revise it.

We can have high level locks (lock size) which covers table,tablespace and partition also we have low level locks which are on row and pages.Then we have various modes of locking that is intent share,intent exclusive,share intent exclusive,share,update and exclusive.Out of theses six modes of locking , all six are applicable for high level locking while for low level locking only three modes( share,update and exclusive) are applicable.

Now,I must stop this post here so you can grasp it. Don't worry if you don't get the concept in one reading , go through the post number of times till you get clear picture out of it.

In next post i will discuss what all these six modes of locking exactly mean in detail and there compatibility with each other. Then we have the third attribute remaining to discuss that is duration of lock (May be one after the next post)

>> Read Part II

DB2 University

2011-02-27T03:26:00.000-05:00

Well well well this is very good news for the guys who want to learn and get certified in DB2.

IBM has started new program called as DB2 University.It is dedicated to helping people get educated and certified in DB2.

There are various free courses available on the site like .

SQL Fundamentals I

DB2 Essential Training II

DB2 Essential Training III

IBM Data Studio Essential training

Each of these offerings will be delivered as "instructor-led online" (ILO) courses. Instructors will monitor forums and grade assignments and a final exam. Courses are planned for 4-6 weeks, with deadlines for each assignment. Courses may be taken simultaneously. Though some courses do have pre-requisites, they will not be enforced at this point.

DB2 Essential Training I isn't considered an ILO course. The final exam will be worth 100 percent.

AND WHAT MORE

ALL courses will offer "prizes," including:

FREE IBM certification vouchers
50-percent discount certification vouchers
"Understanding DB2" book (worth $60 U.S.)

So what are you waiting for go and Register now at DB2university.com