Re: COBOL/DB2 Date edit question



"Graham Hobbs" <ghobbs@xxxxxxxxxxx> wrote in message
news:jgvub35e1pahgvi467v85db20119s42cub@xxxxxxxxxx
I hear what you say Michael. Might I scenario something (and if you
get bored with the subject I will understand) ..

My 'almost all common data types DB2 table' has 14 columns, one is a
key and in particular, three of the others are DB2 datatypes DATE,
TIME, TIMESTAMP. I populate the CICS screen from this table, any field
may be user changed, I need to edit all changes before they go back
into the table - typical CICS scenario.

My problem is to find the easiest/clearest way to edit these three
data types before update occurs. I suspect three ways are available:

1) my 'neat?' EXEC SQL.'s.
2) inline code like 'if month < 00 or >12 then error, etc'.
3) exec cics link 'editdate' with suitable commarea (and othe pgms for
TIME and TIMESTAMP) and test a return code. (your library routine)
4) some other way??
5) there is no especially favoured method.


You are suffering from the same problem many have encountered when moving to
RDB; incompatibilities between language data types and RDB data types. For
the most part, embedded SQL in COBOL does some very good data conversions
uder the covers and ensures that the data presented from the DB to the
program is in a format that the program can use, and vice versa.

However, more and more installations are moving away from embedded SQL for a
number of reasons:

1. It is still tied back into procedural programming.
2. It is not as powerful as some of the emerging methods for data
manipulation which can utilise multiprocessors and deferred execution.
(Query Expressions, Lambda functions, etc.)

Obviously, most places are not going to move overnight into new database
technology. (And it requires using a language that supports the new
functions; COBOL doesn't.)

The modern trend is to place the editing and validation into stored
procedures that are triggered automatically when the data is updated.

This makes sense insofar as the validations are stored with the data, and
are independent of the language being used. It doesn't matter if you move to
Basic, or C++ or C# or whatever, your data still gets validated by the RDBMS
back end. (It is also easier to progress from this into the new technology,
should you decide to, now or later.)

You were close to this solution when you decided to validate the date using
an RDB DATE function. All of the solutions you have outlined are workable,
it is just a question of what you prefer. Michael thinks it is "obtuse"; I
disagree. I think it is perfectly sensible. (However, I understand Michael's
objection. To a programmer, using program code to do things is the solution.
Michael is a very experienced and excellent programmer, so to him this looks
like a "sledgehammer to crack a nut'')

In fact, there is nothing wrong with using RDB functions to validate data
that is being stored on a RDB, the only difference is WHERE you use the
function. If you use it from COBOL with embedded SQL, then there is a good
argument to be made for simply using program code instead. If, on the other
hand, you stored a procedure to do it, and triggered that procedure whenever
the date was updated, you have removed the whole business from your program.
You get a return saying it worked or it didn't and you can do whatever you
want from there. You can also retrieve it, secure in the knowledge that it
is "valid" in the wider sense of not just format validation..

An important point to note here is that this "back end" validation can go
way beyond what you could easily do in program code. You can check the new
date against other, previously validated dates now stored on the database,
without having to retrieve them into a program. You have all of the
extensive SQL DATE manipulation functions immediately available, with no
program call overheads. For example, you can check whether the new date,
plus a number of days is greater, less, or equal to an existing date, which
is, in turn, so many days away from another date which it must exceed, and
so on. And that is all before you even start to include data which is NOT a
date in the validation.

Stored procedures are becoming a way of life on many sites, and embedded SQL
is showing a consequent decline.

But being out of the workforce for a few years now, on my laptop with
older IBM Cobol, CICS, DB2 and btrieve VSAM software, developing a
software package (I am one of these BT/I's), am looking for help as to
how modern installations might handle DATE, TIME, TIMESTAMP edits.

My comments above apply to all of these.

You've already given me clues. Other insights?
Anyway, thanks to date.
graham

On Sun, 12 Aug 2007 14:17:41 GMT, "Michael Mattias"
<mmattias@xxxxxxxxxxxxxx> wrote:

"Pete Dashwood" <dashwood@xxxxxxxxxxxxxxxxxxxxxxxxx> wrote in message
news:5i8ehiF3lkrtaU1@xxxxxxxxxxxxxxxxxxxxx
"Graham Hobbs" <ghobbs@xxxxxxxxxxx> wrote in message
MP!ZADATEI is a field on a CICS screen, populated from a column in a
row from a DB2 table. The user changes it and now it needs editing


Your EXEC SQL may yet be a winner, just give it a host variable... :-)
.
Isn't using EXEC SQL .... END-EXEC kind of an obtuse way to validate a
date
entered upon the screen?

No, it isn't. It is just different from how we have done it traditionally.
There is little difference between calling a data validation routine in
program code and simply calling SQL to place the date on the RDB
immediately. Both incur call overheads. But there is a case to be made for
storing validations with data and getting it out of program code (see
above).

In the old days, before RDBMS, we had no option other than to validate in
program code. Now we do :-)


Not that it can't be made to work, but generally you'd have some kind of
library routine around to handle that.

Maybe you might consider a stored procedure to be a "library routine"? It
serves many of the same functions and can be shared amongst multiple fields.

Besides, at some point it's possible (probable?) you are going to have to
reformat it anyway to do the UPDATE

Nope. Most RDBMS will accept anything that looks like a date. (It is one of
the beautiful things about OO programming, called "overloading".)

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


.