Re: Opinions on approach, please...



--
"I used to write COBOL...now I can do anything."
"Frederico Fonseca" <real-email-in-msg-spam@xxxxxxxxx> wrote in message
news:8d9m34h0rhsnnb00r0c9t6mkv7eh3pbltk@xxxxxxxxxx
On Sun, 25 May 2008 14:41:09 +1200, "Pete Dashwood"
<dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:

I'm currently looking at automated code conversion for migrating COBOL
applications that use Indexed files to RDB.
snip

All of the singleton type things are pretty easy, but the sequential
processing is more difficult. I (think I...) need to declare and open a
cursor when a START is encountered, translate the ensuing READs into
FETCHes
and then CLOSE it when we hit EOF (AT END in the existing code).

Now the reason I am posting...

I'd like to get some other opinions on the stickier bits... :-)

Obviously START is problematic because it has a condition attached to it.
I
can parse this condition and translate it into an SQL condition to go into
a
WHERE clause, but I'm not sure whether it might be better to do this with
dynamic (PREPAREd) SQL (I could almost pick up the COBOL condition
verbatim)
or to use a static definition for each of the possible relational
conditions. Any thoughts?
As I said on another post, it is hard to give a accurate response
without know EXACTLY how this application works.


Yes, I know... :-)

I am looking for a general solution; it cannot be specific.

Nevertheless, I am finding your posts interesting and thought provoking,
Frederico.

Ill give you one example.

Table A with index as follows.
company
accounting_year
accounting_month
product_code
sequence_number

Some of your programs may access the data with a START where they
populate the first 3 fields only. e.g. return all records within a
particular company/year/month.
your SQL where clause would be
where company = :w-company
and accounting_year = :w-year
and accounting_month = :w-month

Easy enough to code staticaly.

Other programs would instead also require the product code.
your SQL where clause would then be
where company = :w-company
and accounting_year = :w-year
and accounting_month = :w-month
and product_code = :w-product-code

Easy again.

Now we can try and do a single SQL that would allow for BOTH
situations.

where company = :w-company
and accounting_year = :w-year
and accounting_month = :w-month
and (product_code = :w-product-code or 0=:w-product-ind)

On this situation, programs that do NOT require the product code to be
a specific one would set w-product-ind to "0". The others would set it
to "1", and supply the product code.
This can eventually be set by the DB IO module DEPENDING on whether
the content of the variable product_code allows for a default value
that would never be a valid content of the table. e.g. spaces on the
case of a alphanumeric field.
It is tricky depending on your current data. But it is a possibility.

Very interesting. I'll think some more on that. Thanks.

But it has a adverse effect.

While my second example will use 4 columns to access the index and
determine which records to retrieve, the 3rd one will only use the
first 3, even though product_code will be a sargable predicate. This
obviously depends on the database engine you use, and whether the SQL
is bound at runtime or at compile time, and if at compile time,
whether variables are optimized at runtime or not. (rmdb dependent).

MOST will be late bound. I never use early binding. Besides, it will be a
COM component and late binding is much better in that environment.

I think you get the general idea here. Main thing is to identify
,first of all, what are the possible START conditions for each current
index on each indexed file.

Can't do that.

Once this is done, you will have a good idea of how many cursors you
will need PER index. It may be the case that you will need to create
more than 1 per index in order to keep performance acceptable (and
don't tell me you are not considering it at the moment. You will
regret it if you do not).

It is a passing consideration at the moment, down a number of other
priorities. (Get the system to work with RDB as a data source would be the
top priority... :-))

If you need to go down the route of dynamic SQL, do so, BUT, and this
is a big BUT, code it in a way that you build your SQL using parameter
markers, and then populate them within the IO Module.

Yes, that is the templated approach. I use it a lot.

DO NOT build the
straight SQL using the supplied values.

I have to look at each case on its merits. At this point I wouldn't rule out
anything.

Reason for this is that each DBMS manager will keep a pool of
dynamically prepared statements in memory. And it will reuse them,
along with the respective access path, whenever any other process
decides to use the same SQL. This pool is memory limited, and if it
increases a lot, you will have SEVERE performance problems, including
the possibility of your database hanging.

That sounds like poor RDBMS management...




In every case MOST will return all fields (SELECT *) so the effect is the
same as COBOL record processing. (This is not intended to be an end point;
it is a start to get the existing system running against a RDB as
datasource, rather than against indexed files, and without changing the
existing logic.)

Regarding this bit, just one suggestion.
Code your SELECT by mentioning ALL the fields on it.
not as a SELECT *.

Yes, I intended to do that. It isn't a problem because the filed list is
copied into MOST anyway.


Regarding another point you mentioned on one of your posts regarding
validating if A start does return any records.
You mentioned that you intended to do a open cursor, fetch next to
determine this.
You may find that it will perform better if you code as follows.
exec sql
select coalesce(max(first_key_field),'invalid_key') into :host_var
from tblx where first_key_field = :key_field and ....
end-exec.

Interesting. I'ver never seen coalesce before and had to look it up.

Not sure about this, but that may just be my unfamiliarity with it. I'll
consider it further when I come to do the code. Thanks.
..
Under normal conditions this sql will always return 0 on SQLCODE.
If not then the error is not an indication that the key does not
exist, but rather another error on the table/database, or eventually a
-911 (record locked timeout on DB2).

If sqlcode is zero, then host_var will either contain 'invalid_key' as
text, or it will contain the value of the first key field of that
table. You are really only interested to know if it contains
invalid_key or not, and act accordingly.

Ah, I see... pretty cool.

Just a bit more food for your thoughts.

Thanks again, Frederico.

Pete.
--
"I used to write COBOL...now I can do anything."


.

Relevant Pages

  • Re: SQL
    ... persistence to RDBs in the computing space. ... SQL has already made that implementation choice. ... purpose access to persistence, much less general computing. ... implements the RDB view of persistence and only the RDB view. ...
    (comp.object)
  • Re: Searching OO Associations with RDBMS Persistence Models
    ... I agree those pesky DB language do tend to thrash a lot. ... Both SQL and C++ can be extended with custom data types. ... The RDB has no idea that this is the ... The programming language is also always a detail. ...
    (comp.object)
  • Re: SQL
    ... That let's SQL out because it depends upon the specific ... > RDB implementation of the RDM. ... > just use SQL to talk to the RDB. ... This depends on how one defines "problem space abstraction". ...
    (comp.object)
  • Re: Opinions on approach, please...
    ... applications that use Indexed files to RDB. ... All of the singleton type things are pretty easy, ... dynamic SQL ...
    (comp.lang.cobol)
  • Re: Relational database & OO
    ... The modern relational theory permits to store values of user-defined type: it does not limit itself by primitive data types. ... If the database does not offer the user-defined type, one can emulate the the type with the string datatype of the proper format: that is what one does in pratique. ... then the RDB must support ADTs and that ADT needs to be defined in the database schema. ... In most databases the DBA doesn't do that because SQL already provides a mechanism for extracting the elements. ...
    (comp.object)