Re: Opinions on approach, please...



"Robert" <no@xxxxxx> wrote in message
news:t5vm34t5gtsfc2mv4qce963d8nra23bcu3@xxxxxxxxxx
On Mon, 26 May 2008 22:42:39 +0100, Frederico Fonseca
<real-email-in-msg-spam@xxxxxxxxx>
wrote:

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.

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.

where company = :w-company
and accounting_year = :w-year
and accounting_month = :w-month
and product_code =
case when :w-product-code > ' ' then :w-product-code
else product_code
end

This will not work if product_code in the database is null.


OK.



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.

I second that recommendation. If you put variables in SQL as literals,
every SQL you
execute will cause a compilation.


Thanks. I never intended to code literals, but I'm certainly not going to
now...

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 *.

I second that, as well. SELECT * is a nightmare when adding a column.


Again, I already had this one covered, but it is good to find people whose
opinions I value, confirming an approach.

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.

If there are 10,000 rows containing the selected first_key_field (e.g.
company), you are
telling it to read all 10,000. A better way is:

select null from table where first_key_field = :key_field and rownum = 1

This will return SQLCODE = -100 (no data found) if no rows match.

In a where clause, EXISTS does the same -- it exits on the first matching
row:

WHERE EXISTS (select null from table where first_key_field = :key_field)

Hmmm... I need some time ot digest this and at present I simply don't have
any. I'll lok at it again when I get to coding it. Thanks.

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


.

Relevant Pages

  • Re: Opinions on approach, please...
    ... applications that use Indexed files to RDB. ... All of the singleton type things are pretty easy, ... can parse this condition and translate it into an SQL condition to go into ...
    (comp.lang.cobol)
  • 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: Opinions on approach, please...
    ... applications that use Indexed files to RDB. ... dynamic SQL ... your SQL where clause would be ... where company =:w-company ...
    (comp.lang.cobol)
  • Re: Better "Join" vs "Where" clause?
    ... running the SQL directly, in the SQL Server tools, rather than ... WHERE clause has been deprecated, ... AFAIK one cannot perform an outer join in Access without using the explicit ...
    (microsoft.public.access.queries)
  • Thanks
    ... Thanks for "With" clause, ... Why are you formatting data in the back end? ... how to use custom column heading in where clause ... This only works on SQL 2005. ...
    (microsoft.public.sqlserver.programming)