MVSFORUMS.com

naren_ab · Beginner Joined: 07 Jan 2003 Posts: 32 Topics: 10

I have a situation where i have to write a program with 20 cursors.
which mean 20 open, and close and 20 fetches, so i want to know if i can use some thing like OPEN :ws-cursor-name.

Thanks krishna

kolusu · Posted: Mon Sep 08, 2003 3:57 pm Post subject:

Naren_ab,

Dynamic SQL will help you to achieve what you are trying to do.But because you want to avoid coding 60(20 opens+ 20 fetches + 20 closes) statements, I wouldn't recommend using dynamic sql.The primary reason being is that to access DB2 data, SQL statement requires an access path. Two big factors in the performance of an SQL statement are the amount of time that DB2 uses to determine the access path at run time and whether the access path is efficient. DB2 determines the access path for a statement at either of these times

1.When you bind the plan or package that contains the SQL statement (This combination yields the best performance because the access path is already determined when the program executes.)

2.When the SQL statement executes(For dynamic SQL statements, DB2 determines the access path at run time, when the statement is prepared. This can make the performance worse than that of static SQL statements. However, if you execute the same SQL statement often, you can use the dynamic statement cache to decrease the number of times that those dynamic statements must be prepared )

Hope this helps...

cheers

kolusu

naren_ab · Beginner Joined: 07 Jan 2003 Posts: 32 Topics: 10

Thats helps. I have figured it out to be static sql for my requirment.
I have one more question just don't want to make one more topic, so i am continuing in the same.
Which would be better(faster/ performance)
Selective Unload or full unload and syncsort? I am trying to avoid a cursor which is join of two tables with where clause getting all the rows in the key ex.., Table A, B
WHERE A.key = B.key
AND ( A.col1 != B.col1
OR A.col2 != B.2
OR A.col3 != col3 )

Once again Thanks...for timely help.

kolusu · Posted: Mon Sep 15, 2003 10:59 am Post subject:

Naren,

I would swing towards the idea of unloading the table and reading in the flat file instead of a cursor.I would also insist on selective unload as it saves the DASD space as well as the run time.

You can unload the 2 tables concurrently with an ORDER BY Key clause. The order by clause will eliminate the sort step to sort the data.

Once you have the unloaded files you can read them sequentially and perform the MATCH logic.

Hope this helps...

cheers

kolusu