* Standard COBOL (file "MPA007.SCO") calling SQL * procedures in file "MPA007.MCO". * STANDARD COBOL (file "MPA007.SCO")
**************************************************************** * * COMMENT SECTION * * DATE 1992/07/17 STANDARD COBOL LANGUAGE * NIST SQL VALIDATION TEST SUITE V6.0 * DISCLAIMER: * This program was written by employees of NIST to test SQL * implementations for conformance to the SQL standards. * NIST assumes no responsibility for any party's use of * this program. * * MPA007.SCO * WRITTEN BY: David W. Flater * * THIS PROGRAM IS PART A OF A TWO-PART PROGRAM(A & B) THAT * TESTS THE CONCURRENCY OF SQL * * REFERENCES * ANSI SQL-1992 * 4.28 SQL-transactions * ****************************************************************
DISPLAY"*** This program is part A of a two-part" DISPLAY"*** program (A & B)."
DISPLAY" This is Program A which starts first and
- " waits.... "
.
P100. * -----Initialization----- MOVE 0 TO code1 * EXEC SQL DELETE FROM MP7_AA; CALL"SUB2"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then MOVE 1 TO code1 END-IF * EXEC SQL INSERT INTO MP7_AA VALUES (1); CALL"SUB3"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then MOVE 1 TO code1 END-IF * EXEC SQL INSERT INTO MP7_AA VALUES (2); CALL"SUB4"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then MOVE 1 TO code1 END-IF * EXEC SQL INSERT INTO MP7_AA VALUES (3); CALL"SUB5"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then MOVE 1 TO code1 END-IF * EXEC SQL INSERT INTO MP7_AA VALUES (4); CALL"SUB6"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then MOVE 1 TO code1 END-IF * EXEC SQL INSERT INTO MP7_AA VALUES (5); CALL"SUB7"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then MOVE 1 TO code1 END-IF * EXEC SQL DELETE FROM MP7_BB; CALL"SUB8"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then MOVE 1 TO code1 END-IF * EXEC SQL INSERT INTO MP7_BB VALUES (6); CALL"SUB9"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then MOVE 1 TO code1 END-IF * EXEC SQL INSERT INTO MP7_BB VALUES (7); CALL"SUB10"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then MOVE 1 TO code1 END-IF * EXEC SQL INSERT INTO MP7_BB VALUES (8); CALL"SUB11"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then MOVE 1 TO code1 END-IF * EXEC SQL INSERT INTO MP7_BB VALUES (9); CALL"SUB12"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then MOVE 1 TO code1 END-IF * EXEC SQL INSERT INTO MP7_BB VALUES (10); CALL"SUB13"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then MOVE 1 TO code1 END-IF * EXEC SQL DELETE FROM MP7_NEXTKEY; CALL"SUB14"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then MOVE 1 TO code1 END-IF * EXEC SQL INSERT * INTO MP7_NEXTKEY (KEYNUM,AUTHOR) * VALUES (1,'A'); CALL"SUB15"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE NOT = 0) then MOVE 1 TO code1 END-IF
if (code1 = 1) then DISPLAY"*****Error initializing tables ****" * EXEC SQL ROLLBACK WORK; CALL"SUB16"USING SQLCODE MOVE SQLCODE TO SQL-COD GOTO P100 END-IF
* EXEC SQL COMMIT WORK; CALL"SUB17"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE NOT = 0) then DISPLAY"*********Setup will not commit ********" * EXEC SQL ROLLBACK WORK; CALL"SUB18"USING SQLCODE MOVE SQLCODE TO SQL-COD GOTO P100 END-IF
* -----Synchronization-----
.
P105. DISPLAY" PLEASE start program MPB007!!! " *pause a little *increase value of variable pauze if MPB007 has trouble * getting table MP7_NEXTKEY. MOVE 0 TO i
.
P106. * EXEC SQL UPDATE MP7_NN * SET NUMTEST = 1; CALL"SUB19"USING SQLCODE MOVE SQLCODE TO SQL-COD COMPUTE i = i + 1 if (i < pauze) then GOTO P106 END-IF *end of pause
MOVE 0 TO knum * EXEC SQL SELECT COUNT(*) * INTO :knum * FROM MP7_NEXTKEY WHERE AUTHOR = 'B'; CALL"SUB20"USING SQLCODE knum MOVE SQLCODE TO SQL-COD
DISPLAY"Transaction A Starts!! " *date_time print ACCEPT TO-DAY FROMDATE ACCEPT THE-TIME FROMTIME DISPLAY"Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME DISPLAY" " DISPLAY"IF this test lasts too long, DEADLOCK" DISPLAY"has taken place, STOP this test!"
MOVE 1 TO i
*We hired a couple of summer students to write application *programs. The first one wrote application Yook, which *only works if all the data are in table BB, and table AA *is empty. The second one wrote application Zook, which *only works if all the data are in table AA, and table BB *is empty. The table names were hard-coded everywhere, so *rather than rewrite everything we just had another summer *student add code to move the data into the right table *before each application starts up. This more or less *solved the problem, but we have to be careful not to have *two people trying to run the applications simultaneously.
.
P101. *Move all data into table BB for application Yook. * EXEC SQL DECLARE YOOK CURSOR FOR * SELECT ANUM FROM MP7_AA END-EXEC if (SQLCODE < 0) then GOTO P120 END-IF * EXEC SQL OPEN YOOK; CALL"SUB22"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then GOTO P120 END-IF MOVE 0 TO count1
.
P102. * EXEC SQL FETCH YOOK INTO :tmpint; CALL"SUB23"USING SQLCODE tmpint MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then GOTO P120 END-IF if (SQLCODE > 0) then * EXEC SQL COMMIT WORK; CALL"SUB24"USING SQLCODE MOVE SQLCODE TO SQL-COD DISPLAY count1 " rows moved from MP7_AA to MP7_BB." *date_time print ACCEPT TO-DAY FROMDATE ACCEPT THE-TIME FROMTIME DISPLAY"Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME if (SQLCODE < 0) then GOTO P120 END-IF GOTO P103 END-IF * EXEC SQL INSERT INTO MP7_BB VALUES (:tmpint); CALL"SUB25"USING SQLCODE tmpint MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then GOTO P120 END-IF * EXEC SQL DELETE FROM MP7_AA WHERE CURRENT OF YOOK; CALL"SUB26"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then GOTO P120 END-IF COMPUTE count1 = count1 + 1 GOTO P102
.
P120. DISPLAY"!+!+!+!+!+!+!+!+ DEADLOCK? !+!+!+!+!+!+!+!+!+!+!" DISPLAY"Negative SQLCODE (Deadlock?) was returned earlier" DISPLAY"!+!+!+!+!+!+!+!+ DEADLOCK? !+!+!+!+!+!+!+!+!+!+!" *date_time print ACCEPT TO-DAY FROMDATE ACCEPT THE-TIME FROMTIME DISPLAY"Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME DISPLAY"Restarting transaction ", i "A." * EXEC SQL ROLLBACK WORK; CALL"SUB27"USING SQLCODE MOVE SQLCODE TO SQL-COD COMPUTE flcnt = flcnt + 1 if (flcnt < mxfail) then GOTO P101 END-IF MOVE 0 TO flcnt DISPLAY"Wasting some time to try to end repeated
- " restarts..." MOVE 0 TO j
.
P136. * EXEC SQL UPDATE MP7_NN * SET NUMTEST = 1; CALL"SUB28"USING SQLCODE MOVE SQLCODE TO SQL-COD COMPUTE j = j + 1 if (j < pauze2) then GOTO P136 END-IF *end of pause GOTO P101
.
P103. *Move all data into table AA for application Zook. * EXEC SQL DECLARE ZOOK CURSOR FOR * SELECT BNUM FROM MP7_BB END-EXEC if (SQLCODE < 0) then GOTO P110 END-IF * EXEC SQL OPEN ZOOK; CALL"SUB29"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then GOTO P110 END-IF MOVE 0 TO count1
.
P104. * EXEC SQL FETCH ZOOK INTO :tmpint; CALL"SUB30"USING SQLCODE tmpint MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then GOTO P110 END-IF if (SQLCODE > 0) then * EXEC SQL COMMIT WORK; CALL"SUB31"USING SQLCODE MOVE SQLCODE TO SQL-COD *date_time print ACCEPT TO-DAY FROMDATE ACCEPT THE-TIME FROMTIME DISPLAY"Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME DISPLAY count1 " rows moved from MP7_BB to MP7_AA." if (SQLCODE < 0) then GOTO P110 END-IF GOTO P166 END-IF * EXEC SQL INSERT INTO MP7_AA VALUES (:tmpint); CALL"SUB32"USING SQLCODE tmpint MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then GOTO P110 END-IF * EXEC SQL DELETE FROM MP7_BB WHERE CURRENT OF ZOOK; CALL"SUB33"USING SQLCODE MOVE SQLCODE TO SQL-COD if (SQLCODE < 0) then GOTO P110 END-IF COMPUTE count1 = count1 + 1 GOTO P104
.
P166. *date_time print ACCEPT TO-DAY FROMDATE ACCEPT THE-TIME FROMTIME DISPLAY"Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME DISPLAY"Survived transaction ", i "." if (i NOT < loops) then GOTO P199 END-IF COMPUTE i = i + 1 GOTO P101
.
P110. DISPLAY"!+!+!+!+!+!+!+!+ DEADLOCK? !+!+!+!+!+!+!+!+!+!+!" DISPLAY"Negative SQLCODE (Deadlock?) was returned earlier" DISPLAY"!+!+!+!+!+!+!+!+ DEADLOCK? !+!+!+!+!+!+!+!+!+!+!" *date_time print ACCEPT TO-DAY FROMDATE ACCEPT THE-TIME FROMTIME DISPLAY"Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME DISPLAY"Restarting transaction ", i "B." * EXEC SQL ROLLBACK WORK; CALL"SUB34"USING SQLCODE MOVE SQLCODE TO SQL-COD COMPUTE flcnt = flcnt + 1 if (flcnt < mxfail) then GOTO P103 END-IF MOVE 0 TO flcnt DISPLAY"Wasting some time to try to end repeated
- " restarts..." MOVE 0 TO j
.
P137. * EXEC SQL UPDATE MP7_NN * SET NUMTEST = 1; CALL"SUB35"USING SQLCODE MOVE SQLCODE TO SQL-COD COMPUTE j = j + 1 if (j < pauze2) then GOTO P137 END-IF *end of pause GOTO P103
.
P199. *Waste time DISPLAY"MPA007 transaction completed--wasting time...." MOVE 0 TO cnt
.
P115. * EXEC SQL UPDATE MP7_NEXTKEY * SET KEYNUM = KEYNUM +1; CALL"SUB36"USING SQLCODE MOVE SQLCODE TO SQL-COD COMPUTE cnt = cnt + 1 if (cnt < pauze3) then GOTO P115 END-IF * EXEC SQL COMMIT WORK; CALL"SUB37"USING SQLCODE MOVE SQLCODE TO SQL-COD
*Finish up
*All the data must end up in one table in order for the *transactions to be serializable. It is not permissible *for a simultaneous swap of the contents of the two tables *to occur. One transaction or the other MUST go first.
.
P201. *date_time print ACCEPT TO-DAY FROMDATE ACCEPT THE-TIME FROMTIME DISPLAY"Date run YYMMDD: " TO-DAY " at hhmmssff: " THE-TIME DISPLAY" " DISPLAY"SELECT COUNT(DISTINCT ANUM) INTO :cnt1 FROM
- " MP7_AA;" * EXEC SQL SELECT COUNT(DISTINCT ANUM) INTO :cnt1 FROM MP7_AA * ; CALL"SUB38"USING SQLCODE cnt1 MOVE SQLCODE TO SQL-COD if (SQLCODE NOT = 0 ) then DISPLAY"Restarting final evaluation due to negative
- " SQLCODE." * EXEC SQL COMMIT WORK; CALL"SUB39"USING SQLCODE MOVE SQLCODE TO SQL-COD GOTO P201 END-IF DISPLAY"SELECT COUNT(*) INTO :cnt2 FROM MP7_AA;" * EXEC SQL SELECT COUNT(*) INTO :cnt2 FROM MP7_AA; CALL"SUB40"USING SQLCODE cnt2 MOVE SQLCODE TO SQL-COD if (SQLCODE NOT = 0 ) then DISPLAY"Restarting final evaluation due to negative
- " SQLCODE." * EXEC SQL COMMIT WORK; CALL"SUB41"USING SQLCODE MOVE SQLCODE TO SQL-COD GOTO P201 END-IF DISPLAY"cnt1 = ", cnt1 "; cnt2 = ", cnt2 DISPLAY"They should be equal." if (cnt1 NOT = cnt2) then MOVE 0 TO flag END-IF DISPLAY"tmpcnt = cnt1;" MOVE cnt1 TO tmpcnt
DISPLAY" " DISPLAY"SELECT COUNT(DISTINCT BNUM) INTO :cnt1 FROM
- " MP7_BB;" * EXEC SQL SELECT COUNT(DISTINCT BNUM) INTO :cnt1 FROM MP7_BB * ; CALL"SUB42"USING SQLCODE cnt1 MOVE SQLCODE TO SQL-COD if (SQLCODE NOT = 0 ) then DISPLAY"Restarting final evaluation due to negative
- " SQLCODE." * EXEC SQL COMMIT WORK; CALL"SUB43"USING SQLCODE MOVE SQLCODE TO SQL-COD GOTO P201 END-IF DISPLAY"SELECT COUNT(*) INTO :cnt2 FROM MP7_BB;" * EXEC SQL SELECT COUNT(*) INTO :cnt2 FROM MP7_BB; CALL"SUB44"USING SQLCODE cnt2 MOVE SQLCODE TO SQL-COD if (SQLCODE NOT = 0 ) then DISPLAY"Restarting final evaluation due to negative
- " SQLCODE." * EXEC SQL COMMIT WORK; CALL"SUB45"USING SQLCODE MOVE SQLCODE TO SQL-COD GOTO P201 END-IF DISPLAY"cnt1 = ", cnt1 "; cnt2 = ", cnt2 DISPLAY"They should be equal." if (cnt1 NOT = cnt2) then MOVE 0 TO flag END-IF
DISPLAY" " DISPLAY"We must also have either (cnt1 == 10 && tmpcnt ==
- " 0)" DISPLAY" or (cnt1 == 0 && tmpcnt == 10)."
MOVE 0 TO flg1 if (cnt1 = 10 AND tmpcnt = 0) then MOVE 1 TO flg1 END-IF if (cnt1 = 0 AND tmpcnt = 10) then MOVE 1 TO flg1 END-IF if (flg1 = 0) then MOVE 0 TO flag END-IF
if ( flag = 1 ) then DISPLAY" *** pass *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0507','pass','MCO'); CALL"SUB46"USING SQLCODE MOVE SQLCODE TO SQL-COD else DISPLAY" mpa007.sco *** fail *** " * EXEC SQL INSERT INTO HU.TESTREPORT * VALUES('0507','fail','MCO'); CALL"SUB47"USING SQLCODE MOVE SQLCODE TO SQL-COD COMPUTE errcnt = errcnt + 1 END-IF * EXEC SQL COMMIT WORK; CALL"SUB48"USING SQLCODE MOVE SQLCODE TO SQL-COD
******************** END TEST0507 *******************
*Empty table to prevent program MPB from jumping gun on rerun. * EXEC SQL DELETE FROM MP7_NEXTKEY; CALL"SUB49"USING SQLCODE MOVE SQLCODE TO SQL-COD * EXEC SQL COMMIT WORK; CALL"SUB50"USING SQLCODE MOVE SQLCODE TO SQL-COD
**** TESTER MAY CHOOSE TO INSERT CODE FOR errcnt > 0 STOPRUN.
* **** Procedures for PERFORM statements
¤ Dauer der Verarbeitung: 0.18 Sekunden
(vorverarbeitet)
¤
Die Informationen auf dieser Webseite wurden
nach bestem Wissen sorgfältig zusammengestellt. Es wird jedoch weder Vollständigkeit, noch Richtigkeit,
noch Qualität der bereit gestellten Informationen zugesichert.
Bemerkung:
Die farbliche Syntaxdarstellung ist noch experimentell.