converting dynamic cursor into static cursor



IBM's flagship relational database management system

converting dynamic cursor into static cursor

Postby rupa888 » Mon Jun 17, 2013 5:36 pm

We have a requirement in our project to convert a dynamic cursor to a static cursor.

In the dynamic cursor, we have used 3 dynamic statements in the WHERE condition.

Say, the 3 fields are INP-A,INP-B,INP-C. INP-A, INP-B and INP-C are passed from the user.

IF INP-A and INP-B has values and INP-C is SPACES, then my where clause will be

Select -----
from table
where A = :WS-A
AND B = :WS-B

or IF INP-A has values and INP-B and INP-C is SPACES, then my where clause will be

Select -----
from table
where A = :WS-A

and so on.

We need to convert such a dynamic cursor into a static cursor. There is one possible solution that we tried - where 1 dynamic cursor is converted into N static cursors ( In the above example, N = 8 )

We require a solution where 1 dynamic cursor is converted to 1 static cursor.

Can you please help me out with a possible solution?
rupa888
 
Posts: 21
Joined: Tue Sep 18, 2012 5:12 pm
Has thanked: 1 time
Been thanked: 0 time

Re: converting dynamic cursor into static cursor

Postby dick scherrer » Mon Jun 17, 2013 7:12 pm

Hello,

We have a requirement in our project to convert a dynamic cursor to a static cursor.
Why is this now a requirement?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: converting dynamic cursor into static cursor

Postby rupa888 » Tue Jun 18, 2013 10:55 am

@dick...

We have some old programs which were coded many years back. One requirement was to remove these dynamic cursors and replace it with static ones. Apart from the solution to convert into N static cursors based on the input condition, can you suggest anything to achieve the same result using a single static cursor from a single dynamic one.
rupa888
 
Posts: 21
Joined: Tue Sep 18, 2012 5:12 pm
Has thanked: 1 time
Been thanked: 0 time

Re: converting dynamic cursor into static cursor

Postby dick scherrer » Tue Jun 18, 2013 8:37 pm

Hello,

I can't think of another way - if i do, i'll post more here.

I still do not understand why this is being done . . . Is there some business or technical reason or is this because "someone wants to"?
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times

Re: converting dynamic cursor into static cursor

Postby rupa888 » Wed Jun 19, 2013 10:48 am

hi dick,
We found out the solution for this.the dynamic query can be converted into static query as follows:

select *
from table name
where (inp_ a = :hostvariable.inp-a or :hostvariable.inp-a = ' ')
and (inp_ b = :hostvariable.inp-b or :hostvariable.inp-b = ' ')
and (inp_ c = :hostvariable.inp-c or :hostvariable.inp-c = ' ')
rupa888
 
Posts: 21
Joined: Tue Sep 18, 2012 5:12 pm
Has thanked: 1 time
Been thanked: 0 time

Re: converting dynamic cursor into static cursor

Postby dick scherrer » Wed Jun 19, 2013 6:43 pm

Hello,

That will run, but i wonder about performance . . . Suggest you do Thorough testing that this will work satisfactorily on queries with high volumes of data to look at to satisfy the query.

But i still do not understand Why this is being done . . .
Hope this helps,
d.sch.
User avatar
dick scherrer
Global moderator
 
Posts: 6268
Joined: Sat Jun 09, 2007 8:58 am
Has thanked: 3 times
Been thanked: 93 times


Return to DB2

 


  • Related topics
    Replies
    Views
    Last post