MVSFORUMS.com

[geetha001]

I have a requirement where I need to use multiple cursors in a DB2 stored procedure because of the different sort orders columns on the front end.

The numbers of cursors to be defined can go upto 20 as there may be upto 10 columns on the page for which the user has ability to sort on.

The options to do this would be
1. Static SQL defining all the 20 cursors (at any point of time only one cursor will execute)
2. Static SQL defining one cursor with a big CASE statement in the 'ORDER BY' clause
3. One more option would be dynamic SQL

I am trying to find an answer as to what would be the best approach in this case, whether static SQL or dynamic SQL?

Also, the table I would be accessing to get the data has millions (200+ millions) of rows and that concerns me about using dynamic SQL.

Please let me know if you have opinions/answers about what would be the better of the two approaches static/dynamic SQL.

[CZerfas]

First of all: The number of rows in the table to be read is not decreasing the performance figures you could get. In fact will the elapse time of your query benefit from beeing "optimized" for every run of your program compared to the static version.

You just have to pay the price for this optimizing of the query CPU- and elapse-wise. Therefor you should be worried, if the number of executions of your program is high. Start it a couple of times a day is nothing to be considered, but start it a couple of times per second really would speak for the static version of this query.

Besides: I don't think you can use the CASE statement in the ORDER BY clause.

regards
Christian

[jsharon1248]

I have supported custom and packaged applications with high volumes of database activity that use dynamic sql exclusively. I have never experienced any performance issues associated with the prepare of the sql statements. For your situation, dynamic sql is the way to go. Think of the long term impacts of maintaining 10 or 20 statements in one or more stored procedures rather than just one statement. Think about the increased development times. Think about the app that calls the stored proc that returns up to 20 results sets, but only one of them has data. Think about what happens when someone requests a slightly different sort order. Choose the right tool for the job. There are some challenges unique to dynamic sql, but these are more than offset by the advantages.

[dbzTHEdinosauer]

extracting the data without a sort (order by) would not be as resource intensive as with an order by.

why not have the front-end do the sort?
you are returning more than a screenful of rows and the front-end could do the sort easier than the multiple variations on the mainframe.

yeah, yeah, I know- this is the way it was designed.
_________________
Dick Brenholtz
American living in Varel, Germany

[NASCAR9]

[kolusu]

geetha001,

Untested sql , but something like this might work.

[geetha001]

Thank you for all your inputs.

I am still looking at the static and dynamic SQL options, since it will be something new to my application and response times are also of a great concern.

However, I would like to try the LOCATE option mentioned by Kolusu.

Kolusu,

One question though, what If there are more than 10 columns would there be something that I can specify so the search string can look for a two digit number?

Thanks

[kolusu]

[dbzTHEdinosauer]

[jsharon1248]

I understand the need to get fancy with solutions at times, but getting fancy to avoid dynamic sql is unnecessary. Dynamic sql is an extremely valuable often underutilized feature. Why mix in trickery when a straightforward solution is available? The dynamic sql will also provide capabilities to optionally include/exclude predicates and table joins. btw, if you use SPUFI or DSNTIAUL, you're already using dynamic sql.

[kolusu]

[geetha001]

Thanks Kolusu for your suggestion of LOCATE. It worked, when I use it on one column. However, I did not try yet for more than one column.
For now, my secondary sort columns are defined, therefore LOCATE would work for me.

I thought LOCATE clause would help me resolve my issues. However, I am encountering another situation.

Since my application requires paging concept. I am trying to use ROWNUMBER OVER ORDER BY (ColA), so that I can get to the rows I need exactly and help me with my paging concept.

If my SQL is

[geetha001]

I would like make one correction of my last post