Cusror in DB2 ---> Part I


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

37 comments:

sandeep said...

hello, when i want to use a no of packages which are in the same list ,can i directly use the list instead of specifying all the packages separately?
..
thanks

Honey said...

Is there any advantage of Declaring the cursor in working storage section rather than in procedure division? Generally we used to declare them in working storage section.. Also waiting for your next part to be posted..

Thankyou

DADA-This Is For U..... said...

A well written document..v nice.

TANGUDU KIRAN KUMAR said...

Very Good Explanation. But a small doubt when we declare a cursor we can always either declared in Working storage or procedure division, what the basic reason behind this. I know for redability we can do this, but to be more specific please explain,

Anonymous said...

waiting for part -2 ..

Anonymous said...

Moreover, the wireless selection is normally only found on physical exercise devices costing
additional than this treadmill.
Feel free to surf my web-site ; adjustable dumbbells

Anonymous said...

Hi there! I understand this is somewhat off-topic however I had to ask.
Does building a well-established website like yours require a lot of work?

I'm completely new to writing a blog but I do write in my diary daily. I'd like
to start a blog so I will be able to share my personal experience and views online.
Please let me know if you have any ideas or tips for new aspiring blog owners.
Appreciate it!

Here is my page ... safe diets

Anonymous said...

Ӏ need to to thank you foг thіѕ very
good rеаd!! I absolutely loved every little bit of іt.
I havе уou bοok-mаrked to look at new things
уou poѕt…

Check out mу blog; chatroulett

Anonymous said...

I’m not that much of a online геader to
be hοnest but уour blogs really
nice, κeep it up! I'll go ahead and bookmark your website to come back later on. Cheers

Also visit my site ... emorroidi alimentazione

Anonymous said...

I need to to thank you for this good read!

! I definitely enjoyed every bit of it. I have you bookmarked to check out new stuff you post…

Here is my homepage; easy diets that work
Also see my website > easy diets that work

Anonymous said...

Generally I do not read post on blogs, however I wish to say
that this write-up very forced me to check out and do it!
Your writing style has been surprised me. Thank you, very
nice article.

Feel free to visit my web-site ... costing Gauteng
Also see my website: estimations Gauteng

Anonymous said...

Appreciating the commitment you put into your blog and in depth information you offer.
It's nice to come across a blog every once in a while that isn't the
same out of date rehashed material. Great read!
I've bookmarked your site and I'm including your RSS feeds to my Google
account.

Feel free to visit my web page; playgrounds Gauteng
my web page - playgrounds Gauteng

Anonymous said...

Pretty nice post. I just stumbled upon your weblog and wanted to
mention that I've truly loved browsing your weblog posts. In any case I will be subscribing on your feed and I'm hoping you write once more soon!


Take a look at my page ... Baby Colic
My web page :: baby colic

Anonymous said...

Hi there to every one, the contents existing at this
website are genuinely remarkable for people knowledge, well, keep
up the good work fellows.

my web-site; waxing Cape Town

Anonymous said...

We are a group of volunteers and opening a new scheme in
our community. Your web site provided us with
valuable information to work on. You've done an impressive job and our entire community will be thankful to you.

Check out my homepage :: newborn photography West Midlands

Anonymous said...

It's hard to find experienced people in this particular topic, but you sound like you know what you're talking about!
Thanks

My blog post hairdresser Parktown North

Anonymous said...

Excellent post. I was checking continuously this blog and I am inspired!
Extremely helpful information particularly the ultimate phase :
) I take care of such info a lot. I was seeking
this certain info for a long time. Thank you and good luck.


Feel free to visit my web-site ... halaal caterers Cape Town
My web page > halaal caterers Cape Town

Anonymous said...

Great blog! Do you have any hints for aspiring writers?
I'm hoping to start my own blog soon but I'm a little lost on everything.

Would you recommend starting with a free platform like Wordpress or go for a paid option?
There are so many options out there that I'm totally confused .. Any ideas? Bless you!

Here is my blog ... visit link

Anonymous said...

With havin so much content do you ever run into any issues of plagorism or copyright violation?

My blog has a lot of completely unique content I've either created myself or outsourced but it appears a lot of it is popping it up all over the internet without my authorization. Do you know any techniques to help stop content from being stolen? I'd definitely appreciate it.


Feel free to surf to my webpage :: know more

Anonymous said...

That is a good tip particularly to those fresh to the blogosphere.
Simple but very precise information… Appreciate your sharing
this one. A must read post!

Look at my web-site; website

Anonymous said...

My brother suggested I might like this web site. He was entirely right.
This post truly made my day. You cann't imagine just how much time I had spent for this info! Thanks!

Review my web blog ... click here

Anonymous said...

Hi there! This blog post could not be written much better!
Going through this post reminds me of my previous roommate!
He constantly kept talking about this. I'll forward this article to him. Pretty sure he'll
have a very good read. Many thanks for sharing!

my page website

Anonymous said...

Hi there! This blog post could not be written much better!
Going through this post reminds me of my previous roommate!
He constantly kept talking about this. I'll forward this article to him. Pretty sure he'll have a very good read.
Many thanks for sharing!

Also visit my web blog: website

Anonymous said...

Greetings, I think your blog might be having browser compatibility problems.
Whenever I look at your website in Safari,
it looks fine however when opening in Internet Explorer,
it's got some overlapping issues. I merely wanted to give you a quick heads up! Other than that, fantastic site!

my website ... graduate certificate programs online

Anonymous said...

Wow, amazing weblog layout! How long have you been running a blog for?
you make running a blog glance easy. The total look of your website
is excellent, let alone the content!

Feel free to visit my blog online airplane games

Anonymous said...

Hi there everуone, it's my first pay a quick visit at this website, and post is actually fruitful for me, keep up posting these articles.

Check out my blog hemroids-piles.com/hemroids-treatment/

Anonymous said...

Wow, thаt's what I was looking for, what a stuff! existing here at this website, thanks admin of this web site.

My blog post - taufgeschenke

Anonymous said...

you are really a excellent webmaster. The site loading velocity is incredible.
It sort of feels that you are doing any distinctive trick.

Also, The contents are masterwork. you've done a great job in this topic!

My web site ... stargames

Anonymous said...

Ηеya! I knoω this is soгt οf off-topіc but I nееԁed to ask.
Dоes running a well-eѕtablіѕhеԁ blog lіke yourѕ takе a massіѵe amount wоrκ?
Ӏ am comρletely new to opeгаting a
blοg but I do wrіte in mу journal
еveгyday. I'd like to start a blog so I can share my experience and feelings online. Please let me know if you have any ideas or tips for brand new aspiring bloggers. Appreciate it!

Here is my web-site; hemroids

Anonymous said...

Good day! I know this is somewhat off topic but I was wondering which blog platform are you using
for this website? I'm getting fed up of Wordpress because I've had issues with hackers and I'm looking at options for another platform. I would be fantastic if you could point me in the direction of a good platform.

Look into my homepage :: contact

Anonymous said...

This ρost offers clear idea dеsіgnеd foг thе new νisitors of blogging, that reаlly how tο dо blοgging and site-building.


Mу ωebsite; porte de garage sectionnelle motorisée sur mesure

Anonymous said...

I’m not that much of a internet reader to be honest
but your sites really nice, keep it up! I'll go ahead and bookmark your website to come back later. All the best

Also visit my web site :: securite fenetre pvc

Anonymous said...

Superb blog! Do you have any suggestions for
aspiring writers? I'm hoping to start my own blog soon but I'm a
little lost on everything. Would you advise starting with a free platform like Wordpress or
go for a paid option? There are so many choices out there that
I'm totally confused .. Any tips? Appreciate it!

Also visit my site :: vliegtickets

Anonymous said...

Υou hаvе madе sоme dесent points thеrе.
I checκed on the internеt tο leaгn more
abοut the іssue аnԁ found most people will go along
with youг views on this webѕite.



Feel frеe to ѕurf to my web-site; Highly recommended Webpage

Best Flight Deals said...

Thank you so much to giving the excellent information. This is great achievements. Discount Airline Tickets

sandeep kumar said...

Hi,
I have one question here. I have 2 programs, main program and sub program. I am using cursor in main program, after open the cursor I called sub program and came back to main program. In this case will my cursor still open after returning back from sub program?

Nitin Gandhi said...

@sandeep The Answer is Yes.

Post a Comment