Focal Point Banner


As of December 1, 2020, Focal Point is retired and repurposed as a reference repository. We value the wealth of knowledge that's been shared here over the years. You'll continue to have access to this treasure trove of knowledge, for search purposes only.

Join the TIBCO Community
TIBCO Community is a collaborative space for users to share knowledge and support one another in making the best use of TIBCO products and services. There are several TIBCO WebFOCUS resources in the community.

  • From the Home page, select Predict: WebFOCUS to view articles, questions, and trending articles.
  • Select Products from the top navigation bar, scroll, and then select the TIBCO WebFOCUS product page to view product overview, articles, and discussions.
  • Request access to the private WebFOCUS User Group (login required) to network with fellow members.

Former myibi community members should have received an email on 8/3/22 to activate their user accounts to join the community. Check your Spam folder for the email. Please get in touch with us at community@tibco.com for further assistance. Reference the community FAQ to learn more about the community.


Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL Table locked during Modify

Read-Only Read-Only Topic
Go
Search
Notify
Tools
[SOLVED] SQL Table locked during Modify
 Login/Join
 
Gold member
posted
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,


Webfocus 7.6.4
Windows 2003 Server, SQL Server 2005
Excel, HTML , JavaScript ,and PDF.
Reportcaster, BID, Tomcat
 
Posts: 79 | Registered: May 02, 2006Report This Post
Expert
posted Hide Post
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
...


Francis


Give me code, or give me retirement. In FOCUS since 1991

Production: WF 7.7.05M, Dev Studio, BID, MRE, WebSphere, DB2 / Test: WF 8.1.05M, App Studio, BI Portal, Report Caster, jQuery, HighCharts, Apache Tomcat, MS SQL Server
 
Posts: 10577 | Location: Toronto, Ontario, Canada | Registered: April 27, 2005Report This Post
Guru
posted Hide Post
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.


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
Gold member
posted Hide Post
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)


Webfocus 7.6.4
Windows 2003 Server, SQL Server 2005
Excel, HTML , JavaScript ,and PDF.
Reportcaster, BID, Tomcat
 
Posts: 79 | Registered: May 02, 2006Report This Post
Gold member
posted Hide Post
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!


Webfocus 7.6.4
Windows 2003 Server, SQL Server 2005
Excel, HTML , JavaScript ,and PDF.
Reportcaster, BID, Tomcat
 
Posts: 79 | Registered: May 02, 2006Report This Post
Guru
posted Hide Post
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


Test: WF 8.2
Prod: WF 8.2
DB: Progress, REST, IBM UniVerse/UniData, SQLServer, MySQL, PostgreSQL, Oracle, Greenplum, Athena.
 
Posts: 454 | Location: Europe | Registered: February 05, 2007Report This Post
  Powered by Social Strata  

Read-Only Read-Only Topic

Focal Point    Focal Point Forums  Hop To Forum Categories  WebFOCUS/FOCUS Forum on Focal Point     [SOLVED] SQL Table locked during Modify

Copyright © 1996-2020 Information Builders