help with tables
in [Cobol]
|
Prev: oracle10g SQLBEX giving 114 with Oracle Dynamic SQL Method 4(ora9 works well)
Next: compile+link Fujitsu Linux
From: Frederico Fonseca on 31 Jan 2008 16:55 On Wed, 30 Jan 2008 10:39:28 -0700, "Frank Swarbrick" <Frank.Swarbrick(a)efirstbank.com> wrote: >>>> On 1/29/2008 at 9:37 PM, in message ><15ovp3p40dmvcovmgc3co257gi595dn3ii(a)4ax.com>, Robert<no(a)e.mail> wrote: >> If you don't like BULK COLLECT syntax, leave it out. Selecting into an >> array does the >> same thing and isn't tied to Oracle. It works on DB2 the same way. For >> example: >> >> 01 table2-area. >> 05 table2-entry occurs 1 to 100 depending on sqlerrd (3). >> 10 t2-name pic x(30). >> 10 t2-dept pic x(04). >> >> compute sqlerrd(3) = length of table2-area / length of table2-entry >> select name, department into table2-entry from table2 >> where name in (select distinct name from table1) >> display 'There are ' sqlerrd(3) ' departments.' > >This does not appear to work, at least not using the DB2 pre-processor. > >Here's my test program: > > IDENTIFICATION DIVISION. > PROGRAM-ID. tabtest. > ENVIRONMENT DIVISION. > > DATA DIVISION. > WORKING-STORAGE SECTION. > EXEC SQL > INCLUDE SQLCA > END-EXEC > EXEC SQL > BEGIN DECLARE SECTION > END-EXEC > 01 table2-area. > 05 table2-entry occurs 1 to 100 depending on sqlerrd(3). > 10 t2-name pic x(30). > 10 t2-dept pic x(04). > EXEC SQL > END DECLARE SECTION > END-EXEC > 77 ERRLOC PIC X(80) VALUE SPACES. > > PROCEDURE DIVISION. > CALL 'connect' > PERFORM get-into-table. > PERFORM DB-DISCONNECT > STOP RUN. > > > get-into-table. > compute sqlerrd(3) = length of table2-area > / length of table2-entry > exec sql > select name, dept > into :table2-entry > from test.table2 > where name in (select distinct name from test.table1) > end-exec > CALL 'checkerr' USING SQLCA ERRLOC > > display 'There are ' sqlerrd(3) ' departments.' > exit. > > DB-DISCONNECT. > MOVE 'DB-DISCONNECT' TO ERRLOC > EXEC SQL > CONNECT RESET > END-EXEC > CALL 'checkerr' USING SQLCA ERRLOC > EXIT. > > END PROGRAM tabtest. > > >And here's the output from the DB2 pre-processor: > Database Connection Information > > Database server = DB2/NT 9.5.0 > SQL authorization ID = FSWARBRI > Local database alias = TEST1 > >prep tabtest.sqb BINDFILE TARGET ibmcob CALL_RESOLUTION immediate EXPLAIN >yes EXPLSNAP yes > >LINE MESSAGES FOR tabtest.sqb >------ >-------------------------------------------------------------------- > SQL0060W The "COBOL" precompiler is in progress. > 19 SQL0008N The token "occurs" found in a host variable > declaration is not valid. > 41 SQL4943W The number of host variables in the INTO clause > is not the same as the number of items in the SELECT > clause. SQLSTATE=01503 > 41 SQL0306N The host variable "TABLE2-ENTRY" is undefined. > SQL0095N No bind file was created because of previous > errors. > SQL0091W Precompilation or binding was ended with "3" > errors and "1" warnings. > >Frank The following is allowed on V9, but on Z/OS. > WORKING-STORAGE SECTION. EXEC SQL INCLUDE SQLCA END-EXEC EXEC SQL BEGIN DECLARE SECTION END-EXEC 01 table2-area. 05 table2-entry. 10 t2-name pic x(30) occurs 100. 10 t2-dept pic x(04) occurs 100. EXEC SQL END DECLARE SECTION END-EXEC EXEC SQL DECLARE CURSOR-X CURSOR FOR select name, dept from test.table2 where name in (select distinct name from test.table1) END-EXEC. ....... PROCEDURE.... exec sql FETCH NEXT CURSOR-X into :T2-NAME, :T2-DEPT for 100 rows end-exec It seems that Windows/Unix versions do not allow multiple fetch/insert rows. Pitty. Frederico Fonseca ema il: frederico_fonseca at syssoft-int.com
From: Robert on 31 Jan 2008 18:57 On Wed, 30 Jan 2008 10:39:16 +0000 (UTC), docdwarf(a)panix.com () wrote: >In article <15ovp3p40dmvcovmgc3co257gi595dn3ii(a)4ax.com>, >Robert <no(a)e.mail> wrote: >>On Tue, 29 Jan 2008 19:05:40 +0000 (UTC), docdwarf(a)panix.com () wrote: >> >>>In article <c7sup39sjmh3d0n70m26o05ql6np5igq5r(a)4ax.com>, >>>Robert <no(a)e.mail> wrote: >>>>On Tue, 29 Jan 2008 14:51:50 +0000 (UTC), docdwarf(a)panix.com () wrote: >>>> >>>>>In article <m6bup35nfg7lbr4fmtpk0hoa08j055hu9h(a)4ax.com>, >>>>>Robert <no(a)e.mail> wrote: >>>>>>On Tue, 29 Jan 2008 08:11:32 +0000 (UTC), docdwarf(a)panix.com () wrote: >>> >>>[snip] >>> >>>>>>>Things may have changed since I last looked, Mr Wagner - I've heard tell >>>>>>>that GOBACK is more than an IBM extension! - but I believe that 'bulk >>>>>>>collect' is PL/SQL, not ANSI. >>>>>> >>>>>>That's a widely held mistaken belief, especially in Cobol circles. Bulk >>>>>>collect can be >>>>>>used with SQL embedded in Cobol, C or Java. It is not limited to PL/SQL. >>>>> >>>>>(note - '-marks used to indicate a direct text quotations) >>>>> >>>>>A programmer might read what was written carefully, Mr Wagner; I did not >>>>>say that 'bulk collect' was limited to PL/SQL, simply that it 'is PL/SQL, >>>>>not ANSI.' ('ANSI' is being used as a shorthand for 'ANSI SQL') >>> >>>[snip] >>> >>>>You are putting words in my keyboard. I did not say anything about the >>>>ANSI standarxd, I >>>>said bulk collect is available in Cobol, it is not a PL/SQL feature. >>> >>>According to >>>http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_plsql.html >>>'bulk collect' most certainly *is* available in PL/SQL: >>> >>>--begin quoted text: >>> >>>BULK COLLECT and FORALL are very helpful, not only in improving >>>performance but also in simplifying the code you need to write for SQL >>>operations in PL/SQL. >>> >>>--end quoted text >>> >>>... but what would Oracle know about the features of PL/SQL, anyhow? >> >>If you don't like BULK COLLECT syntax, leave it out. > >It may be less a matter of what *I* like, Mr Wagner, and more a matter of >what my client has asked me to do in exchange for my wages. Requests I get depend on who within the client organization is asking. Round one: applications wants a style based on the premise we might wake to find the last 25 years was a bad dream, the application has to run on a 1980's mainframe. They write VSAM record processing logic in Cobol or PL/SQL. Round two: administrators try to speed it up by adding parallelism on the server side, by partitioning tables and indexes, launching parallel processes on the client side. Round three: operations throws faster hardware at the problem. Round four: management tries to make the problem go away by questioning why we need to run the job, who authorized it, can the work be done another way, would it run faster in another language, talks to salesmen about third-party products. Round five: the client asks me to 'Make it run faster. We think all ya gotta do is optimize the database.' Instead, I rewrite logic the way it should have been written in the first place. Doing things right flies in the face of client culture, is used only as a last resort. I'm working on a weekly job that's been through rounds 1-4 and still runs longer than a week. The rewrite runs in a half hour, as a single thread. The difference is set operations rather than record processing, clean logic rather than tricks, it's written in non-procedural SQL. Round one management would reject such an approach. >>Selecting into an >>array does the >>same thing and isn't tied to Oracle. > >This seems to be a kind of admission that your first example was PL/SQL, >as was pointed out a while back and specifically denied by you in what you >quoted ('... bulk collect ... it is not a PL/SQL feature.') If ya wanna get technical, yes it runs through the PL/SQL engine on the server side, but the client program need not be written in PL/SQL, it can be written in Cobol or C. All ya gotta do is put the statement in a BEGIN .. END block. Bulk collect gets a table with a single message to the server, a single trip through the parser. Selecting into an array runs a cursor under the covers, causing a fetch for each row or buffer. It's significantly faster than explicit fetches, but still slower than bulk collect. >>I forgot to show him the MAINFRAME WAY: >> >> 3100-SEARCH-TABLE2. >>D DISPLAY 'IN 3100-SEARCH-TABLE2'. >> MOVE WS-CONST-N TO WS-IND1. >> PERFORM 5600-LOOK-IN-TABLE2 THRU 5699-LOOK-IN-TABLE2-EXIT >> VARYNG WS-COMP-SUB2 FROM WS-CONST-1 >> BY WS-CONST-1 >> UNTIL WS-IND1 = WS-CONST-Y >> OR WS-COMP-SUB2 > WS-COMP-TABLE2-SIZE >> END-PERFORM. > >That's similar to a way I have seen things done on mainframes, yes... but >a way that would not pass muster in more than a few shops where I've >worked and would have gotten you laughed out of Prod Implementation >reviews. I've never seen a Prod Implementation review. I've only seen reviews BEFORE testing began. Sounds like you're putting untested rewrites into production. >(if 3100- begins in column 8 (as indicated by the debugging line >following it) then the imperatives which follow precede column 12) My keypunch machine was down.
From: Frank Swarbrick on 31 Jan 2008 19:39 n 1/30/2008 at 2:55 PM, in message <our1q3tj98fmhasa5oituun83n8brukcd7(a)4ax.com>, Frederico Fonseca<real-email-in-msg-spam(a)email.com> wrote: > On Wed, 30 Jan 2008 10:39:28 -0700, "Frank Swarbrick" > <Frank.Swarbrick(a)efirstbank.com> wrote: > >>>>> On 1/29/2008 at 9:37 PM, in message >><15ovp3p40dmvcovmgc3co257gi595dn3ii(a)4ax.com>, Robert<no(a)e.mail> wrote: >>> If you don't like BULK COLLECT syntax, leave it out. Selecting into an > >>> array does the >>> same thing and isn't tied to Oracle. It works on DB2 the same way. For >>> example: >>> >>> 01 table2-area. >>> 05 table2-entry occurs 1 to 100 depending on sqlerrd (3). >>> 10 t2-name pic x(30). >>> 10 t2-dept pic x(04). >>> >>> compute sqlerrd(3) = length of table2-area / length of table2-entry >>> select name, department into table2-entry from table2 >>> where name in (select distinct name from table1) >>> display 'There are ' sqlerrd(3) ' departments.' >> >>This does not appear to work, at least not using the DB2 pre-processor. >> >>Here's my test program: >> >> IDENTIFICATION DIVISION. >> PROGRAM-ID. tabtest. >> ENVIRONMENT DIVISION. >> >> DATA DIVISION. >> WORKING-STORAGE SECTION. >> EXEC SQL >> INCLUDE SQLCA >> END-EXEC >> EXEC SQL >> BEGIN DECLARE SECTION >> END-EXEC >> 01 table2-area. >> 05 table2-entry occurs 1 to 100 depending on sqlerrd(3). >> 10 t2-name pic x(30). >> 10 t2-dept pic x(04). >> EXEC SQL >> END DECLARE SECTION >> END-EXEC >> 77 ERRLOC PIC X(80) VALUE SPACES. >> >> PROCEDURE DIVISION. >> CALL 'connect' >> PERFORM get-into-table. >> PERFORM DB-DISCONNECT >> STOP RUN. >> >> >> get-into-table. >> compute sqlerrd(3) = length of table2-area >> / length of table2-entry >> exec sql >> select name, dept >> into :table2-entry >> from test.table2 >> where name in (select distinct name from test.table1) >> end-exec >> CALL 'checkerr' USING SQLCA ERRLOC >> >> display 'There are ' sqlerrd(3) ' departments.' >> exit. >> >> DB-DISCONNECT. >> MOVE 'DB-DISCONNECT' TO ERRLOC >> EXEC SQL >> CONNECT RESET >> END-EXEC >> CALL 'checkerr' USING SQLCA ERRLOC >> EXIT. >> >> END PROGRAM tabtest. >> >> >>And here's the output from the DB2 pre-processor: >> Database Connection Information >> >> Database server = DB2/NT 9.5.0 >> SQL authorization ID = FSWARBRI >> Local database alias = TEST1 >> >>prep tabtest.sqb BINDFILE TARGET ibmcob CALL_RESOLUTION immediate EXPLAIN >>yes EXPLSNAP yes >> >>LINE MESSAGES FOR tabtest.sqb >>------ >>-------------------------------------------------------------------- >> SQL0060W The "COBOL" precompiler is in progress. >> 19 SQL0008N The token "occurs" found in a host variable >> declaration is not valid. >> 41 SQL4943W The number of host variables in the INTO clause >> is not the same as the number of items in the SELECT >> clause. SQLSTATE=01503 >> 41 SQL0306N The host variable "TABLE2-ENTRY" is undefined. >> SQL0095N No bind file was created because of previous >> errors. >> SQL0091W Precompilation or binding was ended with "3" >> errors and "1" warnings. >> >>Frank > > The following is allowed on V9, but on Z/OS. >> > WORKING-STORAGE SECTION. > EXEC SQL > INCLUDE SQLCA > END-EXEC > EXEC SQL > BEGIN DECLARE SECTION > END-EXEC > 01 table2-area. > 05 table2-entry. > 10 t2-name pic x(30) occurs 100. > 10 t2-dept pic x(04) occurs 100. > EXEC SQL > END DECLARE SECTION > END-EXEC > EXEC SQL > DECLARE CURSOR-X CURSOR FOR > select name, dept > from test.table2 > where name in (select distinct name from test.table1) > END-EXEC. > ...... > PROCEDURE.... > exec sql > FETCH NEXT CURSOR-X > into :T2-NAME, :T2-DEPT > for 100 rows > end-exec > > It seems that Windows/Unix versions do not allow multiple fetch/insert > rows. Pitty. Hmm, yes, that is a pity. And just recently some DB2 people have been saying "DB2 is DB2 is DB2", at least when it comes to DML. I guess not! Ah well. Looks to be a useful feature, but I'm sure I can live without it. Frank
From: Anonymous on 31 Jan 2008 20:37 In article <bav1q3t26jjt26156j9eh4lpp1ivtot9a0(a)4ax.com>, Robert <no(a)e.mail> wrote: >On Wed, 30 Jan 2008 10:39:16 +0000 (UTC), docdwarf(a)panix.com () wrote: > >>In article <15ovp3p40dmvcovmgc3co257gi595dn3ii(a)4ax.com>, >>Robert <no(a)e.mail> wrote: >>>On Tue, 29 Jan 2008 19:05:40 +0000 (UTC), docdwarf(a)panix.com () wrote: >>> >>>>In article <c7sup39sjmh3d0n70m26o05ql6np5igq5r(a)4ax.com>, >>>>Robert <no(a)e.mail> wrote: [snip] >>>>>You are putting words in my keyboard. I did not say anything about the >>>>>ANSI standarxd, I >>>>>said bulk collect is available in Cobol, it is not a PL/SQL feature. >>>> >>>>According to >>>>http://www.oracle.com/technology/oramag/oracle/04-jan/o14tech_plsql.html >>>>'bulk collect' most certainly *is* available in PL/SQL: >>>> >>>>--begin quoted text: >>>> >>>>BULK COLLECT and FORALL are very helpful, not only in improving >>>>performance but also in simplifying the code you need to write for SQL >>>>operations in PL/SQL. >>>> >>>>--end quoted text >>>> >>>>... but what would Oracle know about the features of PL/SQL, anyhow? >>> >>>If you don't like BULK COLLECT syntax, leave it out. >> >>It may be less a matter of what *I* like, Mr Wagner, and more a matter of >>what my client has asked me to do in exchange for my wages. > >Requests I get depend on who within the client organization is asking. I try to avoid 'multiple sourcing' at the client level, Mr Wagner, by referring all requests to the person who signs my timesheet. I start gently, with 'Well, I'll have to speak with Mr/s Finkerhabben about that... the one who signs my timesheet gets to tell me what to do and how to do it, you know the song'... .... and, if necessary, move to a level of 'Ms/r Oubientot, I'm sorry... but I do not believe you have the authority to command the use of my time as a Corporate Resource. Mr/s Zhardnivskov signs my timesheet and Mr/s Zhardnivskov has not told me to deal with this task, let alone what approach to take towards it. I'll be happy to turn my attentions towards this matter once I receive explicit instructions from Mr/s Zhardnivskov and I will be equally happy to put these words in writing to you and cc: it appropriately. Is there any other way I might be of assistance?' [snip] >Round five: the client asks me to 'Make it run faster. We think all ya >gotta do is >optimize the database.' Tell them to use a bulk collect... never mind that it is DB2. [snip] >>>I forgot to show him the MAINFRAME WAY: >>> >>> 3100-SEARCH-TABLE2. >>>D DISPLAY 'IN 3100-SEARCH-TABLE2'. >>> MOVE WS-CONST-N TO WS-IND1. >>> PERFORM 5600-LOOK-IN-TABLE2 THRU 5699-LOOK-IN-TABLE2-EXIT >>> VARYNG WS-COMP-SUB2 FROM WS-CONST-1 >>> BY WS-CONST-1 >>> UNTIL WS-IND1 = WS-CONST-Y >>> OR WS-COMP-SUB2 > WS-COMP-TABLE2-SIZE >>> END-PERFORM. >> >>That's similar to a way I have seen things done on mainframes, yes... but >>a way that would not pass muster in more than a few shops where I've >>worked and would have gotten you laughed out of Prod Implementation >>reviews. > >I've never seen a Prod Implementation review. I've only seen reviews >BEFORE testing began. >Sounds like you're putting untested rewrites into production. That might be due to the fact that it is something you admit to being outside of your experience, Mr Wagner; a Prod Implementation review can require a programmer to submit test results. > >>(if 3100- begins in column 8 (as indicated by the debugging line >>following it) then the imperatives which follow precede column 12) > >My keypunch machine was down. Try the ISPF editor, Mr Wagner... it's good 1970s technology. DD
From: Robert on 31 Jan 2008 22:25
On Wed, 30 Jan 2008 10:39:28 -0700, "Frank Swarbrick" <Frank.Swarbrick(a)efirstbank.com> wrote: >-------------------------------------------------------------------- > SQL0060W The "COBOL" precompiler is in progress. > 19 SQL0008N The token "occurs" found in a host variable > declaration is not valid. > 41 SQL4943W The number of host variables in the INTO clause > is not the same as the number of items in the SELECT > clause. SQLSTATE=01503 The manual says DB2 doesn't allow a simple SELECT to return multiple rows. You must do the SELECT in a cursor declaration and read the table with a single FETCH, as illustrated by Frederico. Thanks for the experiment. |