cics, db2, sql question
in [Cobol]
|
Prev: Cobol tutorial
Next: Visual Cobol
From: charles hottel on 15 Jun 2005 18:37 We currently use CICS, DatacommDB and COBOL. Management is contemplating moving to DB2 and Java and SQL. I have no DB2 or Java experience and only a little SQL experience. So I have been reading up on these subjects to prepare myself for the future. We have many programs called locates or browses that display N lines per screen where each line contains information from a single record. The user can scroll forward or backward. Backward scrolling is easy with DatacommDB as it has a read backward command, REDBR. From reading "DB2 for the COBOL programmer Part2 2nd ed", chapter 8 "How to browse DB2 data in a CICS Program", page 214: "when a pseudo conversational program ends DB2 drops the result table" "conflict between the operating modes of CICS and DB2 is so serious that some shops prohibit CICS/DB2 browse programs or limit their number. For applications that could involve huge numbers of rows, limiting CICS/DB2 browsing is certainly reasonable. If online tables contain millions of rows, for example, it would be unreasonable not to prohibit browse operations." The book gives 3 strategies and calls them all costly: 1. do a separate query for each execution of the program 2. do a single query and browse in conversational style 3. do a single query and save the results in a temporary area between pseudo conversational executions We have nine tables with over 1 million records. From smallest to largest they contain 4.4M, 30.8M, 47M, 51.5M, 93M, 102.7M,129M, 158M, and 207M records. We have 30 plus tables altogether and most of them have a browse function/transaction code. Strategy number 2 is out of consideration as conversational programs are not allowed. I can see how strategy number 1 works well for forward scrolling but how do you do backward scrolling with SQL? Our keys are not sequential numbers so I cannot just take the key from the top line and subtract N-1 from it to get the starting record for a scroll back. Strategy number 3 just seems to move the bottleneck to a new location. So my question is , how would you write this kind of program. The book I mentioned above is a little on the old side so I am hoping someone with more recent knowledge of DB2 and SQL can suggest a better way to implement this. Thanks in advance for your help. Charlie Hottel
From: Pete Dashwood on 15 Jun 2005 22:04 Hi Charles, good to see you are still here... :-) (excellent description of the problem, BTW...) Some comments below... "charles hottel" <jghottel(a)yahoo.com> wrote in message news:58bc3$42b0ad7f$43f2931b$2714(a)DIALUPUSA.NET... > We currently use CICS, DatacommDB and COBOL. Management is contemplating > moving to DB2 and Java and SQL. I have no DB2 or Java experience and only a > little SQL experience. So I have been reading up on these subjects to > prepare myself for the future. > > We have many programs called locates or browses that display N lines per > screen where each line contains information from a single record. The user > can scroll forward or backward. Backward scrolling is easy with DatacommDB > as it has a read backward command, REDBR. > > From reading "DB2 for the COBOL programmer Part2 2nd ed", chapter 8 "How to > browse DB2 data in a CICS Program", page 214: > > "when a pseudo conversational program ends DB2 drops the result table" > Yes, that has always been true as I recall. You can save it by creating a new temporary table with it. This will remain across dequeue boundaries (interactions with the user). > "conflict between the operating modes of CICS and DB2 is so serious that > some shops prohibit CICS/DB2 browse programs or limit their number. For > applications that could involve huge numbers of rows, limiting CICS/DB2 > browsing is certainly reasonable. If online tables contain millions of > rows, for example, it would be unreasonable not to prohibit browse > operations." > I never knew that. Doesn't sound very 'user friendly' does it? > The book gives 3 strategies and calls them all costly: > > 1. do a separate query for each execution of the program > > 2. do a single query and browse in conversational style > > 3. do a single query and save the results in a temporary area between pseudo > conversational executions > 3 is probably the one I just mentioned above... > We have nine tables with over 1 million records. From smallest to largest > they contain 4.4M, 30.8M, 47M, 51.5M, 93M, 102.7M,129M, 158M, and 207M > records. We have 30 plus tables altogether and most of them have a browse > function/transaction code. > Wow! (So you won't be running this on a desktop PC, then... ?<g>) > Strategy number 2 is out of consideration as conversational programs are not > allowed. > And for very good reasons... :-) > I can see how strategy number 1 works well for forward scrolling but how do > you do backward scrolling with SQL? Our keys are not sequential numbers so > I cannot just take the key from the top line and subtract N-1 from it to get > the starting record for a scroll back. > Check out ORDER BY and the use of cursors in your DB2 docs. RDBMS store data without any regard to sequence (it is different from ISAM/VSAM concepts, even though these access methods may be used to support the tables physically.) The idea is that STORAGE (in as efficient a manner as possible) is the responsibility of the RDBMS (it can put stuff anywhere it likes and, as users, it is none of our business how or what it does...now you start to understand why DBAs tend to have worried expressions.. :-)), but RETRIEVAL SEQUENCE is a user responsibility; so it provides ORDER BY and what you need is sequenced how you want it, at the time you retrieve it, rather than when it was stored. > Strategy number 3 just seems to move the bottleneck to a new location. > > So my question is , how would you write this kind of program. The book I > mentioned above is a little on the old side so I am hoping someone with more > recent knowledge of DB2 and SQL can suggest a better way to implement this. > Thanks in advance for your help. I can't claim recent knowledge, but I have some ideas that may help. 1. Don't retrieve all of the data. Just the Keys, and use ORDER BY to get the result set sequenced the way you want. Personally, I would use option 3 to save this result set in a temporary table. (Think of this as an index to the real data, rather than a bottleneck moved somewhere else... <g>) 2. Write a CICS program that builds one screenful of data, starting from any given key in the result set. (It would access, say, 20 rows to get the data for each row on the screen, using 20 keys in sequence from the result set, starting from the one you gave it. (if the data for each key fits on one row... it is simple to use the same concept for however many rows you need to display one item). Think of it as a 'screen builder'. I would write a callable function that takes a result set key and returns a screen data row, or several rows if it needs several rows to display the data for one key. This would be the fundamental building block for the screen builder.) To read the keys in sequence from the result set, starting at a given key, open a cursor with WHERE key-in-result-set > key-to-be-used ORDER BY key ASCENDING. Some implementations of SQL allow you to limit the number of items returned (check whether current DB2 implements TOP or SET ROWCOUNT), so you could use the number you can accommodate on one screen. You then use the cursor to fetch and build as many lines as fit on the screen. If backward browsing the underlying data is required, you will need to know the keys previous to the specified one. Use another cursor with WHERE key-in-result-set < key-to-be-used ORDER BY key DESCENDING... (You probably won't actually build the screen lines in reverse order, but this cursor lets you move 'back' as many keys as you need to, to get a screenful.) 3. Store the start key for each screenful onto a CICS TS queue. (Might be useful to add a number to them indicating the order in which the user accessed these screens. If he wants to go back 4 screens, this would facilitate it.) This is then an index to the screenfuls of data the user has accessed (a history, if you like...). If he wants to review from history, you can offer that. 4. The screen builder provides functionality to go anywhere in the result set and build a screen from that point. This would enable users to input a key and get a screenful from that point, so the functionality would not just be PREVIOUS and NEXT, but you could also add GET, with a user provided key. The backward cursor on the result set lets you decide which key to use as the screen 'root' for a 'page back' request. I don't have enough information about the details of what you are doing to offer a detailed solution so the above is mainly conceptual. Sometimes exploring ideas can generate other and better ideas. Some points to keep in mind: 1. If you only return the keys of the real tables into your result set, DB2 can do it very quickly from its own indexes. 2. To randomly access underlying data for say, 20 keys, in order to build a screen is very fast with DB2. Clustering, caching, indexing and optimization are all taken care of by modern RDBMS, based on access patterns. You should see no noticeable delay, despite the fact that the underlying data is massive. Hope this helps, Pete.
From: Joe Zitzelberger on 16 Jun 2005 02:58 IIRC the book you quote was based on DB2 v2.1 or maybe v4.x -- either is a long dead version. Current DB2 releases support cursors that can scroll backwards, return a limited number of rows (a screenful), and DB2 has ways to maintain a cursor across transactions in a pseudo-conversational mode. I've never seen a limitation on browse functionality, nor do I consider millions of rows to be a large table these days. If you are moving to Java will you be maintaining a pseudo-conversational style, e.g. 3270? Or will you be using something like an http server that can maintain those states for you? You have alot of things changing at once. It might be a good time to step back and question if some design decisions make sense in the CICS/TS environment. At the very lest, get the latest and greatest 'application programmers guide' from IBM's public book server. Many of the cool cursor features have been added since that book was written and they might solve your problem handily. In article <58bc3$42b0ad7f$43f2931b$2714(a)DIALUPUSA.NET>, "charles hottel" <jghottel(a)yahoo.com> wrote: > We currently use CICS, DatacommDB and COBOL. Management is contemplating > moving to DB2 and Java and SQL. I have no DB2 or Java experience and only a > little SQL experience. So I have been reading up on these subjects to > prepare myself for the future. > > We have many programs called locates or browses that display N lines per > screen where each line contains information from a single record. The user > can scroll forward or backward. Backward scrolling is easy with DatacommDB > as it has a read backward command, REDBR. > > From reading "DB2 for the COBOL programmer Part2 2nd ed", chapter 8 "How to > browse DB2 data in a CICS Program", page 214: > > "when a pseudo conversational program ends DB2 drops the result table" > > "conflict between the operating modes of CICS and DB2 is so serious that > some shops prohibit CICS/DB2 browse programs or limit their number. For > applications that could involve huge numbers of rows, limiting CICS/DB2 > browsing is certainly reasonable. If online tables contain millions of > rows, for example, it would be unreasonable not to prohibit browse > operations." > > The book gives 3 strategies and calls them all costly: > > 1. do a separate query for each execution of the program > > 2. do a single query and browse in conversational style > > 3. do a single query and save the results in a temporary area between pseudo > conversational executions > > We have nine tables with over 1 million records. From smallest to largest > they contain 4.4M, 30.8M, 47M, 51.5M, 93M, 102.7M,129M, 158M, and 207M > records. We have 30 plus tables altogether and most of them have a browse > function/transaction code. > > Strategy number 2 is out of consideration as conversational programs are not > allowed. > > I can see how strategy number 1 works well for forward scrolling but how do > you do backward scrolling with SQL? Our keys are not sequential numbers so > I cannot just take the key from the top line and subtract N-1 from it to get > the starting record for a scroll back. > > Strategy number 3 just seems to move the bottleneck to a new location. > > So my question is , how would you write this kind of program. The book I > mentioned above is a little on the old side so I am hoping someone with more > recent knowledge of DB2 and SQL can suggest a better way to implement this. > Thanks in advance for your help. > > Charlie Hottel
From: charles hottel on 16 Jun 2005 18:55 Pete, Thanks as always you gave me a lot to think about and helped me to clarify some of my thinking. <snip>
From: charles hottel on 16 Jun 2005 19:31
Joe, Thanks I will check out the application programmer guide. One comment below. "Joe Zitzelberger" <joe_zitzelberger(a)nospam.com> wrote in message news:joe_zitzelberger-BBD22B.02581516062005(a)ispnews.usenetserver.com... > IIRC the book you quote was based on DB2 v2.1 or maybe v4.x -- either is > a long dead version. > > Current DB2 releases support cursors that can scroll backwards, return a > limited number of rows (a screenful), and DB2 has ways to maintain a > cursor across transactions in a pseudo-conversational mode. > > I've never seen a limitation on browse functionality, nor do I consider > millions of rows to be a large table these days. > > If you are moving to Java will you be maintaining a > pseudo-conversational style, e.g. 3270? Or will you be using something > like an http server that can maintain those states for you? Actually I do not know what the exact environment will be. My best guess would be an http server. I am not 100% certain whether this will take place or not. Other scenarios have been proposed in the past ( since 1992) but they never happened. Once they were going to have a tiered system with PCs, UNIX servers, and mainframes using C++, but they finally decided that they could not afford that architecture. Don't ask how many millions were spent before they made that decision. Another proposal was to use SAP but they have abandoned that idea, at least for our stuff. SAP just does not provide the functionality of our area's applications. Some other groups will use SAP but in some cases where SAP functionality is appropriate the existing software currently processes transactions at a rate that may be a problem for SAP to match. The whole thing is a lot bigger than just the area that I work in, which is just a small part of the overall system. One goal seems to be to be able to say that we have modernized and no longer use COBOL. The guy in charge now (and their have been many others in the past) says that he asks this question: " If we were to build this system from scratch today would we use COBOL?" Of course the guy he is asking does not know COBOL so the answer is "no". I am afraid that they plan to use the existing programs as a basis for writing specifications for the new programs. In fact, that is how I know that they don't know COBOL because they want us to write the specifications for them (them is a large contractor consortium). The existing system was developed over a 20 year plus time frame but they want to replace it in a time frame much shorter than that. I often hear things like "if we had x million dollars more we could cut the development by y years". The existing system was evaluated by GAO in the past and four major deficiencies were found. All of these deficiencies were fixed during the Y2K process. This included Y2K dates and replacing an in house, unstable telecommunication facility with IBM's MQM and thus resolving issues of scalability. We are currently undergoing our second reorganization this year and that does not count becoming part of the Department of Homeland Security last year. So as you can see the situation is very fluid. > > You have alot of things changing at once. It might be a good time to > step back and question if some design decisions make sense in the > CICS/TS environment. > > At the very lest, get the latest and greatest 'application programmers > guide' from IBM's public book server. Many of the cool cursor features > have been added since that book was written and they might solve your > problem handily. > > <snip> |