The following is data migrator code for a stored proc.

You can see there is a loop
-REPEAT CSALOOP FOR &I FROM 1 TO &TOTROWS
during that loop I am selecting records that meet a certain criteria.

After the select I have a MODIFY FILE DT_RESERVE_CELL which updates column RESERVE_CELL_MISSINGYN


The problem is that during the process which takes about 1 hr the table DT_RESERVE_CELL is locked almost the entire time.

I can not execute the following is SQL:
SELECT * FROM RESERVE_CELL where ACCTG_YEAR = 2009 AND ACCTG_MONTH = 3 and reserve_cell_missingyn = 'y'



I would expect the table to unlocked during the select process but only locked for the period of time the MODIFY FILE DT_RESERVE_CELL is executing

Do you know why the lock is occurring? How do I prevent it? I am considering just writing the SQL code instead of using MODIFY_FILE

-------------------------------------------------------------------------------------------------------


-SET &SINGLE = BYTVAL('''','I3');
-SET &DOUBLE = BYTVAL('"','I3');
-SET &SPACE = BYTVAL(' ','I3');
-SET &NULL = BYTVAL('','I3');
-* Initialize local variables
-SET &RESER_CELL1 = 0;
-SET &ACCTG_YEAR1 = 0;
-SET &ACCTG_MONTH1 = 0;
-SET &RESER_TYPE1 = ' ';
-SET &TMR_DATE1 = ' ';
-SET &BLANK = ' ';
-SET &I = 0;
-SET &MISSINGYN = 'N';
-RUN
TABLE FILE WORK_CSA_LIST
PRINT RESERVE_CELL
ACCTG_YEAR
ACCTG_MONTH
RESERVE_CELL_TYPE_CODE
TRIANGLE_MAKER_DATE
COMPUTE BLANK/A1 = ' ';
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE HOLD AS CSATEMP FORMAT COMMA
END
-RUN
-SET &TOTROWS = &LINES;
-* ** ******************************************************************************
-REPEAT CSAREAD FOR &I FROM 1 TO &TOTROWS
-READ CSATEMP, NOCLOSE &RESER_CELL.&I,&ACCTG_YEAR.&I,&ACCTG_MONTH.&I,&RESER_TYPE.&I,&TMR_DATE.&I, &BLANK
-* Translate double quotes to spaces
-SET &RESER_TYPE.&I = CTRAN(&RESER_TYPE.&I.LENGTH,&RESER_TYPE.&I,&DOUBLE,&SPACE,'A&RESER_TYPE.&I.LENGTH');
-SET &TMR_DATE.&I = CTRAN(&TMR_DATE.&I.LENGTH,&TMR_DATE.&I,&DOUBLE,&SPACE,'A&TMR_DATE.&I.LENGTH');
-* Clean up any columns values by removing extra spaces, mainly leading spaces
-SET &RESER_TYPE.&I = LJUST(&RESER_TYPE.&I.LENGTH,&RESER_TYPE.&I,'A&RESER_TYPE.&I.LENGTH');
-SET &RESER_TYPE.&I = TRUNCATE(&RESER_TYPE.&I);
-SET &RESER_TYPE.&I = TRIM('B',&RESER_TYPE.&I, &RESER_TYPE.&I.LENGTH,' ',1,'A&RESER_TYPE.&I.LENGTH');
-SET &TMR_DATE.&I = LJUST(&TMR_DATE.&I.LENGTH,&TMR_DATE.&I,'A&TMR_DATE.&I.LENGTH');
-SET &TMR_DATE.&I = TRUNCATE(&TMR_DATE.&I);
-SET &TMR_DATE.&I = TRIM('B',&TMR_DATE.&I, &TMR_DATE.&I.LENGTH,' ',1,'A&TMR_DATE.&I.LENGTH');
-TYPE Processing &I &RESER_CELL.&I &ACCTG_YEAR.&I &ACCTG_MONTH.&I &RESER_TYPE.&I &TMR_DATE.&I
-CSAREAD
-CLOSE CSAREAD
-SET &TOTROWS = MAX(0,&I - 1);
-*SET ALL=ON
-* Include JOIN structure against TRANS,CONTRACT_LOB,CLIAMS and CURRENCY
-* INCLUDE ibnr_etl_dataflow/ibnr_csa_join_10
JOIN CLEAR *
JOIN
INNER DT_TRANS.DT_TRANS.CONTRACT_ID AND DT_TRANS.DT_TRANS.UNDERWRITING_YEAR
AND DT_TRANS.DT_TRANS.CONTRACT_VERSION_NUMBER
AND DT_TRANS.DT_TRANS.ACCOUNT_CODE AND DT_TRANS.DT_TRANS.VALID_FROM_DATE
AND DT_TRANS.DT_TRANS.VALID_TO_DATE IN DT_TRANS TO UNIQUE
DT_CONTRACT.DT_CONTRACT.CONTRACT_ID
AND DT_CONTRACT.DT_CONTRACT.UNDERWRITING_YEAR
AND DT_CONTRACT.DT_CONTRACT.CONTRACT_VERSION_NUMBER
AND DT_CONTRACT.DT_CONTRACT.ACCOUNT_CODE
AND DT_CONTRACT.DT_CONTRACT.VALID_FROM_DATE
AND DT_CONTRACT.DT_CONTRACT.VALID_TO_DATE IN DT_CONTRACT AS J0
END
JOIN
LEFT_OUTER DT_TRANS.DT_TRANS.TRANS_CLAIMS_ID
IN DT_TRANS TO UNIQUE
DT_CLAIMS.DT_CLAIMS.CLAIMS_ID
IN DT_CLAIMS AS J1
END
JOIN
DT_CONTRACT.DT_CONTRACT.CONTRACT_CURRENCY_CODE
AND DT_CONTRACT.DT_CONTRACT.EFFECTIVE_PERIOD IN DT_TRANS TO UNIQUE
DT_CURRENCY.DT_CURRENCY.CURRENCY_CODE
AND DT_CURRENCY.DT_CURRENCY.PERIOD IN DT_CURRENCY AS J2
END
JOIN
DT_CONTRACT.DT_CONTRACT.CONTRACT_ID
AND DT_CONTRACT.DT_CONTRACT.UNDERWRITING_YEAR
AND DT_CONTRACT.DT_CONTRACT.CONTRACT_VERSION_NUMBER
AND DT_CONTRACT.DT_CONTRACT.ACCOUNT_CODE
AND DT_CONTRACT.DT_CONTRACT.VALID_FROM_DATE
AND DT_CONTRACT.DT_CONTRACT.VALID_TO_DATE IN DT_TRANS TO MULTIPLE
DT_CONTRACT_LOB.DT_CONTRACT_LOB.CONTRACT_ID
AND DT_CONTRACT_LOB.DT_CONTRACT_LOB.UNDERWRITING_YEAR
AND DT_CONTRACT_LOB.DT_CONTRACT_LOB.CONTRACT_VERSION_NUMBER
AND DT_CONTRACT_LOB.DT_CONTRACT_LOB.ACCOUNT_CODE
AND DT_CONTRACT_LOB.DT_CONTRACT_LOB.VALID_FROM_DATE
AND DT_CONTRACT_LOB.DT_CONTRACT_LOB.VALID_TO_DATE IN DT_CONTRACT_LOB AS J3
END
JOIN
INNER DT_CONTRACT.DT_CONTRACT.BROKER_CODE IN DT_TRANS TO UNIQUE
DT_BROKER.DT_BROKER.BROKER_CODE IN DT_BROKER AS J4
END
JOIN
LEFT_OUTER DT_TRANS.DT_TRANS.CAT_CODE IN DT_TRANS TO UNIQUE
DT_CAT.DT_CAT.CAT_CODE IN DT_CAT AS J5
END

-* ** ******************************************************************************
-REPEAT CSALOOP FOR &I FROM 1 TO &TOTROWS
-* SET XRETRIEVAL=ON
TABLE FILE DT_RESERVE_CELL_WHERE_CLAUSE
PRINT
COMPUTE SINGLE_LINE/A120=
WHERE_LEFT_PAREN | (' ' | WHERE_FIELD_NAME )|| (' ' | WHERE_OPERAND ) || ( ' ' | WHERE_VALUE ) || (' ' | WHERE_RIGHT_PAREN );

BY WHERE_SEQ NOPRINT
WHERE RESERVE_CELL EQ &RESER_CELL.&I;
WHERE WHERE_SEQ EQ 1
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS INCPERIOD FORMAT ALPHA
END
-RUN

-* *** The 2nd extract all other filters, except PERIOD (seq<>1)
-* *** These filters have the AND/OR relationship coded as their attributes
-* *** and are treated as 1 long compound WHERE clause

TABLE FILE DT_RESERVE_CELL_WHERE_CLAUSE
PRINT
COMPUTE SINGLE_LINE/A120=
WHERE_LEFT_PAREN | (' ' | WHERE_FIELD_NAME )|| (' ' | WHERE_OPERAND ) || ( ' ' | WHERE_VALUE ) || (' ' | WHERE_RIGHT_PAREN )||(' '| WHERE_CONNECTOR );

BY WHERE_SEQ NOPRINT
WHERE RESERVE_CELL EQ &RESER_CELL.&I;
WHERE WHERE_SEQ NE 1
ON TABLE SET HOLDLIST PRINTONLY
ON TABLE SAVE AS INCCOMPOUND FORMAT ALPHA
END
-RUN

-IF &LINES EQ 0 GOTO CSALOOP;

DEFINE FILE DT_TRANS
D_LOB_CODE/A4=IF DT_TRANS.TRANS_CLAIMS_ID IS MISSING THEN DT_CONTRACT_LOB.LOB_CODE
ELSE DT_CLAIMS.CLAIM_LOB_CODE;
D_ISCLAIMYN/A1= IF DT_TRANS.TRANS_CLAIMS_ID IS MISSING THEN 'N' ELSE 'Y';
RECLIMIT/I4 WITH TRANS_ID=RECLIMIT + 1;
D_ISSUECO_YEAR/A10=DT_CONTRACT.ISSUE_COMPANY_CODE || '*' || (EDIT(DT_CONTRACT.EFFECTIVE_YEAR,'$9999'));
D_ISSUECO_CLASS/A7=DT_CONTRACT.ISSUE_COMPANY_CODE || '*' || DT_CONTRACT.CLASS_CODE ;
D_SUBTYPE_SUBCLASS/A9=DT_CONTRACT.SUBTYPE_CODE || '*' || DT_CONTRACT.SUBCLASS_CODE ;
END

TABLE FILE DT_TRANS
SUM
COMPUTE D_LOB_PCT/P7.4=IF D_ISCLAIMYN EQ 'N' THEN DT_CONTRACT_LOB.LOB_PCT
ELSE 1.0; AS LOB_PCT
COMPUTE DUMMY_TRANSYN/A1='N';

BY DT_TRANS.TRANS_ID
BY DT_TRANS.CONTRACT_ID
BY DT_TRANS.UNDERWRITING_YEAR
BY DT_TRANS.CONTRACT_VERSION_NUMBER
BY DT_TRANS.ACCOUNT_CODE
BY DT_TRANS.ACCTG_CODE
BY DT_CONTRACT.CONTRACT_EFFECTIVE_DATE


WHERE (DT_TRANS.TRANS_TYPE_CODE LIKE 'EST-%');


WHERE DT_TRANS.LAST_MODIFIED_TIMESTAMP GT DT(&TMR_DATE.&I);
WHERE RECORDLIMIT EQ 1
WHERE
-INCLUDE INCPERIOD

WHERE
-INCLUDE INCCOMPOUND
-
ON TABLE HOLD AS CSA_EXT FORMAT ALPHA
END
-RUN
-SET &HOLDLINE = &LINES ;
-SET &MISSINGYN = IF (&HOLDLINE GT 0) THEN 'Y' ELSE 'N';
MODIFY FILE DT_RESERVE_CELL
LOG DBMSERR MSG OFF
LOG DUPL MSG OFF
LOG INVALID MSG OFF
LOG NOMATCH MSG OFF
LOG FORMAT MSG OFF
LOG ACCEPT MSG OFF
LOG TRANS MSG OFF

FREEFORM RESERVE_CELL/11 ACCTG_YEAR/4 ACCTG_MONTH/2
FREEFORM RESERVE_CELL_MISSINGYN/1

MATCH RESERVE_CELL ACCTG_YEAR ACCTG_MONTH
ON MATCH COMPUTE RESERVE_CELL_STATUS = RESERVE_CELL_MISSINGYN;
ON MATCH UPDATE RESERVE_CELL_MISSINGYN RESERVE_CELL_STATUS
ON MATCH TYPE "Reserve_Cell:"
ON NOMATCH REJECT
DATA
&RESER_CELL.&I,&ACCTG_YEAR.&I,&ACCTG_MONTH.&I,'&MISSINGYN',$
END
-RUN
-CSALOOP
-TYPE End
FILEDEF CSAREAD CLEAR
FILEDEF CSATEMP CLEAR
FILEDEF INCWHERE CLEAR
FILEDEF CSA_EXT CLEAR
? FILEDEF
JOIN CLEAR *
-DEFAULT &ECHO=OFF
SET ALL=OFF
SET TRACEOFF = ALL
SET TRACEON = DEFAULT
SET TRACEUSER = OFF

EX IBNR_UTIL_DEL_CURRPROC

-RUN

This message has been edited. Last edited by: jammer,

I can't guarantee this will work, but a SQL Commit statement right after the END of the MODIFY command may release the db lock:

...
MODIFY FILE DT_RESERVE_CELL
...
END
-RUN

SQL SQLMSS
COMMIT;
END
-RUN
CSALOOP
...

Perhaps it's better to put this question in the Iway section. But my first idea would be to put everything in one holdfile and modify the file in one time instead of modifying record by record, record by record can result in a enormous overhead.

Francis - thanks - I will try that

Frans - I am only updating 100 records - so overhead should not be huge - but that is an idea - I can capture which records need to be updated and move modify outside loop and update all together

(I originally posted in iway section - but sometimes I can wait days for a response there! I figured this is basically WFOCUS code)

The
SQL SQLMSS
COMMIT;
END

was what was needed.

I may have also been able to use ON MATCH COMMIT but the other thing worked so I am happy!

OK basically your work should be commited with the END statement.

You could try to put this before MODIFY:

SQL SQLMSS COMMIT WORK

and put this in the modify request:
CHECK 1