Cobol-Oracle rdb : outline.

[nmadvan]

Hello,

I tried to use outline for a query which takes more time to run. I created an outline in sql, and then whenever I use this query interactivly, the outline gets used perfect and works very fastly. But the same query if i use it in my cobol program ( .sco, embedded with oracle rdb), it is not using the outline. It just tries to use the existing indexes, and it takes more time.

Is there anything i am doing wrong? I tried to compile the .sco program using "$sqlpre/cob". has anyone come across this type of issue? could you please suggest?

Thanks,
Madhavan

[Richard J Maher]

Hi Madhavan,

Did you use different SQL to create the outline?

Is the query used in a cursor?

Are you specifying the "optimize using "?

Cheers Richard Maher

[nmadvan]

Hi,

Thanks. I'm not using any cursor in my program. It's a simple select statement which will return 1 row. If I use the 'optimize' clause in the 'select' statement within cobol program, yes, it uses the outline.

But, my question here is, without specifying the outline clause in select statement within cobol program, i want the optimizer to use the outline which i defined.

Interectively, if I don't specify the optimize clause in select statement in SQL>, the optimizer uses the outline which i defined. the same thing I am unable to achive from the cobol program.!

Thanks for you replying and i welcome any suggestions in this regard.

N.Madhavan

[Richard J Maher]

Hi N,

I personally have never had to use an outline to encourage the optimizer to choose a certain strategy. Having said that, outlines were obviously created for a purpose and do provide stability over versions/growth etc, so to answer your question: "I don't know" :-)

It would probably help if you could post the two versions of your Select and someone might be able to spot where they are different. IIRC even white-space may throw the optimizer into a false negative so all I can suggest is to make sure *everything* is the same.

If you are not using literals in the sqlpre then *do not* use literals when creating the outline. If you alias a table as fred in the SQLPRE then do not use F as the alias in SQL.

Without seeing the two versions, all I can say is they must be different, if you can't make them sufficently similar for the optimizer to recognize the outline then just tell it. (Or do what I do and change the query so that the right strategy is chosen in the first place)

Cheers Richard Maher

[nmadvan]

Hi,

Thanks for your reply. Let me try some more testing then.

Thanks
N.Madhavan

[Richard J Maher]

Hi Madhavan,

Any results?

Not sure what your particular circumstances are, or the customer's, but you might want to also look at the Rdb logical (something like rdms$_use_old_cost_model) or Workload Statistics Collection if it's not already enabled.

Cheers Richard Maher

[nmadvan]

Hello Richard,

Thanks for your reply. I have tested in many ways by defining the "rdms$" flags (optimize, old_cost, etc...) and nothing worked out. I have to specify the outline internally in the .sco program.

Actually my situaion is, I have an .RCO program and it was compiled in DEC RDB ver 6 (almost 10 years ago). We can not specify the outline internally in the .rco program. But though we created the outline through SQL, the .rco's executable so far worked very well and automatically utilised the outline. The table which is getting accessed by this program got altered, so I had to recompile/link this program. Since now I am compiling the same program (without modification of program content) in Oracle RDB 7 version, the current executable is not utilising the outline automatically.

Now DEC RDB is desupported and the latest Oracle RDB precompiler does not utilize the outline automatically as it used to do in DEC Rdb versions. So, we took an approach of rewriting the .rco program to .sco program by specifying the outline internally in the program since the outline can not be specified internally in the .rco program.

Thank you for your interest on this, i hope this experience would be useful to others too.

Thanks again.

[Jan van den Ende]

Madhavan,

please review

http://forums1.itrc.hp.com/service/forums/helptips.do?#33

about saying "thank you" in this forum.

Proost.

Have one on me.

jpe