Re: Opinions on approach, please...
- From: Frederico Fonseca <real-email-in-msg-spam@xxxxxxxxx>
- Date: Mon, 26 May 2008 22:42:39 +0100
On Sun, 25 May 2008 14:41:09 +1200, "Pete Dashwood"
<dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote:
I'm currently looking at automated code conversion for migrating COBOLsnip
applications that use Indexed files to RDB.
As I said on another post, it is hard to give a accurate response
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?
without know EXACTLY how this application works.
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.
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).
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.
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).
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. DO NOT build the
straight SQL using the supplied values.
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.
Regarding this bit, just one suggestion.
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.)
Code your SELECT by mentioning ALL the fields on it.
not as a SELECT *.
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.
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.
Just a bit more food for your thoughts.
Frederico Fonseca
ema il: frederico_fonseca at syssoft-int.com
.
- Follow-Ups:
- Re: Opinions on approach, please...
- From: Robert
- Re: Opinions on approach, please...
- From: Pete Dashwood
- Re: Opinions on approach, please...
- References:
- Opinions on approach, please...
- From: Pete Dashwood
- Opinions on approach, please...
- Prev by Date: Re: Opinions on approach, please...
- Next by Date: Re: Opinions on approach, please...
- Previous by thread: Re: Opinions on approach, please...
- Next by thread: Re: Opinions on approach, please...
- Index(es):
Relevant Pages
|
|
