Conversion of data & associated logic from ISAM to RDB
in [Cobol]
|
Prev: How would you do this?
Next: COBOL dynamic allocation (putenv) of output-file won't release extra space when closed
From: DaveM on 10 Apr 2007 12:44 Jimmy, Thank you for your help. Between yours and other responses I am now aware of more specific terms that we should be using e.g., 'embedded'), along with other fundamentally important pieces of information. Google/web search for 'sql-examples' found 43K hits, but most of these were about structuring the sql query itself, as opposed to the kinds of cobol-to-sql-database examples that I was looking for. However, by narrowing it down further with the added criteria "embedded" and "cobol" it gave us a much more manageable/relevant 456 hits. The first page alone gives us more than enough useful links to keep us constructively busy for a long while! NE's Database Access online book was/is quite useful, but, of course, did not contain the real-world examples we were looking for. With enlightenments since gleaned via this and other threads, I am happy to say the this book is even MORE useful now because I have gained a significantly better sense of what many of the pieces really mean and how they are applied. (I still have a long way to go, obviously, but it sure feels better to know I'm at least holding the manual right- side up, so to speak.) ESQL Assistant looks very useful, and I most certainly will also take up your suggestion of signing up for MF forum per SQL. According to one of the other writers I now owe you a consultant's fee, along with a signed affadavit proving that I have already achieved expert status in any given subject before having the audacity to ask anyone else questions about it. ;o) Again, and in all seriousness, I thank you for your help. Gratefully, Dave Miner
From: DaveM on 10 Apr 2007 12:52 Oops. Instead of posting my response to you under your recent message, I mistakenly posted it under TLEADERS's message. Sorry about that! -Dave
From: Anonymous on 10 Apr 2007 13:00 In article <1176223479.428972.39830(a)l77g2000hsb.googlegroups.com>, DaveM <renfrew76(a)xemaps.com> wrote: [snip] >According to one of the other writers I now owe you a consultant's >fee, along with a signed affadavit proving that I have already >achieved expert status in any given subject before having the audacity >to ask anyone else questions about it. ;o) Mr Miner, the concept of 'owe' can carry with it both legal and moral obligations. To the best of my knowledge there's no contractual agreement for payment between you and anyone offering responses here so - it might be concluded - that you owe twice as much as you've already been asked to pay. On the other hand... you received information which, by your own admission, has saved you time; it can be argued that you owe a portion of your savings to one who assisted you in achieving them... and if Franklin's Equivalence ('Time is Money') is valid then you owe someone cash. As for 'expert status'... that was never asked. Just think of what you might have gotten were you to have respected the subject matter sufficiently to learn some basic terminology before trotting your requests out before the world, hat in your hand and spinach on your teeth, asking for folks to Do Their Jobs For You For Free. It turns out that you got... something, sure. How you evaluate whether what you got is of value or will send you and your team rushing pell-mell down a dead end is another matter... wouldn't want to find yourself on the carpet in front of the CFO saying 'But this... guy on the UseNet told me it'd work, how was *I* to know he was fulla hooey... I'm not Technical, you know! To Julia... do it to Julia, not me!', would you? DD
From: James J. Gavan on 10 Apr 2007 20:16 DaveM wrote: > Jimmy, > > Thank you for your help. Between yours and other responses I am now > aware of more specific terms that we should be using e.g., > 'embedded'), along with other fundamentally important pieces of > information. > <SNIP> Don't know how I dreamed up 58K hits - just re-did it and came up with the same 43K hits that you did :-) Doesn't give exactly what you were immediately after but check M/F site for examples/samples - there are three 'general' SQL routines. Plus if you get into your N/E sub-folders there are also some demos of 'general' SQL solutions. Once into the M/F Forum you can search previous messages with "Database", "ODBC" and "SQL" etc. You can't respond to the next - but you can also look at old ARCHIVED messages. Like M/S, M/F under Net Express also has a KnowledgeBase. But the whole bunch above tend to be generalized. Pete Dashwood's solution and ESQL Assistant. Pete reads your COBOL file records and generates equivalent DB rows into a table. ESQL Assistant - *YOU* design the DB Table and determine what type of SQL fields/columns (Int, Char etc.), you want. Naturally the latter approach means you have to get au fait with the particular DB package, (how numerics are stored) - so it could take you a week or more to convert, depending upon the number of tables you are going to generate. There might be a nice solution using both Pete's package and ESQL Assistant. Pete will read a whole bunch of COBOL files in mere minutes and generate your DB Table formats. Now using ESQL Assistant, (the major thing being you can generate your SQL statements knowing they will be correct), when you test on a particular table the ESQL package will ask you if you want to generate a copyfile - three parts :- DB Table format DB Table NULL columns COBOL - a typical COBOL record So Pete provides the initial definition of your DB Table and from that ESQL using Pete's DB Table, generates the copyfile above which you can test your individual queries with. Obviously you are picking up stuff as you do more reading, but for a quickie comparison, and don't take this as gospel :- COBOL FILES SQL ----------- ------------- Open/Close Connect/Disconnect (Note you connect/disconnect to a specific Database which may contain 50 Tables, (equivalent of 50 COBOL files, or perhaps 30 Files, split into 50 tables) Read using PrimeKey Select Distinct Read Next Select using Cursor - This uses a number for the cursor. ESQL Assistant automatically generates the next number, so there's no confusion between different queries Rewrite Update Write Insert Delete Delete The use of the various SQL "Verbs" becomes clearer if you use ESQL Assistant to generate queries. I think what you balked at in your original message was "How to..." do equivalent of COBOL Write and Rewrite. PROCEDURAL using an ISAM (my own style - so you adapt to your way) ------------------------ Display Screen-Record/GUI Dialog Accept PrimeKey if Blank - Quit else set RecordNotFound, RecordNotChanged to true Read Record using PrimeKey (SQL - SELECT DISTINCT ) End-if if RecordFound perform DISPLAY-RECORD perform ACCEPT-CHANGES for Changed-fields set RecordChanged to true else (RecordNotFound) set RecordChanged to true perform ACCEPT-NEW-RECORD, (other than the PrimeKey) then perform ACCEPT-CHANGES (if you want to make corrections) End-if if RecordChanged perform VALIDATION-CHECKS (errors - go back to perform ACCEPT-CHANGES) End-if if ValidationOK (this is also a Level 88) if RecordFound REWRITE COBOL-RECORD else WRITE COBOL-RECORD End-if PROCEDURAL using SQL -------------------- The only real difference between this and COBOL file above is substituting :- SELECT DISTINCT = READ FILE using PrimeKey SQL-UPDATE = REWRITE COBOL-RECORD SQL-INSERT = WRITE COBOL-RECORD. REAL WORLD EXAMPLES ------------------- I don't know that M/F have anything specific, but you could ask if you join the M/F Forum I did post a message the latter part of last year - but can't locate it; e-mail me 'editing' my address above, so that I can confirm a link to you and in return I'll post the code I'm referring to. It's OO Classes but when looking at methods being invoked think of CALLING another program or performing a PARAGRAPH. I'm not recommending *mine* is the way to do it, but for a simple file it illustrates the program logic flow I illustrated above and gives you the COMPLETE SQL Statements for INSERT, UPDATE, CURSOR etc... for a specific table. (I don't mind if you call them records or rows :-) ). If/when you get a paperback, or articles on design from the Web, concentrate on the term 'Normalization' so that you have a handle on it. Jimmy
From: Pete Dashwood on 10 Apr 2007 23:59
"James J. Gavan" <jgavandeletethis(a)shaw.ca> wrote in message news:sNVSh.51648$6m4.42486(a)pd7urf1no... <snip>> > Pete Dashwood's solution and ESQL Assistant. Pete reads your COBOL file > records and generates equivalent DB rows into a table. Not quite. ISAM2RDB reads your COBOL source definitions and generates one or more TABLES into a designated DATABASE. A single ISAM file definition COULD generate several tables. Ths is because the normalization process removes repeating groups (OCCURS in COBOL) to a separate linked table for each group. (One advantage of this is that every "table" in your COBOL system now has unlimited rows (no more maintaining OCCURS items) and will only take as much space as it actually needs...) If your ISAM source definition has no OCCURS in it, then you should get a single table, with a correctly typed column for each of the fields defined in your ISAM definition. (Both groups and elements are defined, to assist with data loading and converting existing programs that may reference group fields. Unreferenced groups can be removed later.) Certainly this tool gives you a structure that is useful while you are transitioning from ISAM to RDB. However, I would not suggest that this is how Relational Databases should be designed and built :-) It is a non-relational solution forced into a relational framework and, as such, it can never be as good as a Relational solution built from scratch. Sadly few of us have the time or funding to restart from scratch when moving to new technology, so ISAM2RDB provides a helpful bridge. (It also assists with the learning process; for example, you can look at the PICTURE of a given element in your COBOL code and see what type of Database definition for the corresponding column was generated on the RDB.) ESQL Assistant - > *YOU* design the DB Table and determine what type of SQL fields/columns > (Int, Char etc.), you want. Naturally the latter approach means you have > to get au fait with the particular DB package, (how numerics are stored) > - so it could take you a week or more to convert, depending upon the > number of tables you are going to generate. > > There might be a nice solution using both Pete's package and ESQL > Assistant. Pete will read a whole bunch of COBOL files in mere minutes > and generate your DB Table formats. Now using ESQL Assistant, (the major > thing being you can generate your SQL statements knowing they will be > correct), when you test on a particular table the ESQL package will ask > you if you want to generate a copyfile - three parts :- > > DB Table format > DB Table NULL columns > COBOL - a typical COBOL record > > So Pete provides the initial definition of your DB Table and from that > ESQL using Pete's DB Table, generates the copyfile above which you can > test your individual queries with. Yes, that could be viable. I guess it depends on how many tables are involved. If it is just a few, it is probably better to simply use ESQL and learn how to define tables. If there are many, it might be useful to use ISAM2RDB and pick up some pointers on how to define and normalize tables, in passing. > > > If/when you get a paperback, or articles on design from the Web, > concentrate on the term 'Normalization' so that you have a handle on it. > A very important observation, Jimmy. I have some stuff on this somewhere... I'll see if I can post it to a web server so people can access it. Pete. |