Rajat Sharma
Member
Hello everyone, i am stuck in a problem of long transaction:
For interfacing a fie (with comma separated delimiter) we were fetching each line one by one (in a repeat loop) and storing the variables (fetched from file) into database table (with create statement) in a different .p program (.p program is being called inside repeat loop). Interfacing file is having around 4500 records (4500 lines) that we are going to interface and store into database table. Time taken for this process is around 7-9 hours (Does interfacing around 4500 records in separate transactions could cause the interfacing time as 7-9 hours or there could be anything else that I need to consider).
As per my understanding, repeat loop is causing problem there because it treats each line of interfacing file as a single transaction. Apparently for around n number for records (around 4500 in our case) we have n number of transactions and that makes this whole process very slow.
Perhaps solution of this problem is: Either can replace REPEAT loop with DO WHILE TRUE loop so that all the records are under one transaction but this could cause .BI, .AI or lock table problem or we can use DO TRANSACTION block around create statement (where we are creating DB records).
Please refer these programs as an overview of the problem:
abc.p
Using DO WHILE TRUE LOOP seems good to me (please suggest), but if I do that then do I need to increase the space for lock table entries, .AI and .BI files or I could save myself by dividing the transaction into four parts (for ex: thousand records per transaction) by using a counter variable.
Please suggest!
Thanks & Regards!
Rajat.
For interfacing a fie (with comma separated delimiter) we were fetching each line one by one (in a repeat loop) and storing the variables (fetched from file) into database table (with create statement) in a different .p program (.p program is being called inside repeat loop). Interfacing file is having around 4500 records (4500 lines) that we are going to interface and store into database table. Time taken for this process is around 7-9 hours (Does interfacing around 4500 records in separate transactions could cause the interfacing time as 7-9 hours or there could be anything else that I need to consider).
As per my understanding, repeat loop is causing problem there because it treats each line of interfacing file as a single transaction. Apparently for around n number for records (around 4500 in our case) we have n number of transactions and that makes this whole process very slow.
Perhaps solution of this problem is: Either can replace REPEAT loop with DO WHILE TRUE loop so that all the records are under one transaction but this could cause .BI, .AI or lock table problem or we can use DO TRANSACTION block around create statement (where we are creating DB records).
Please refer these programs as an overview of the problem:
Code:
INPUT FROM VALUE (something).
REPEAT: /* 1. Should I replace this with DO WHILE TRUE*/
IMPORT DELIMETER “,” /*import around 4500 lines records*/
a
b
c
d.
/* if variables (a,b,c,d) contains value zero or anything unwanted then we store them into temp files and send them towards exception report*/
RUN abc.p(…) /*passing all variable along with many other values*/
END. /*repeat end*/
INPUT CLOSE.
abc.p
Code:
/*do some checking stuff here*/
/*should I apply a DO TRANSACTION statement around create statement*/
CREATE c-data.
ASSIGN
/*2. Assigning all input parameters to table c-data*/
Using DO WHILE TRUE LOOP seems good to me (please suggest), but if I do that then do I need to increase the space for lock table entries, .AI and .BI files or I could save myself by dividing the transaction into four parts (for ex: thousand records per transaction) by using a counter variable.
Please suggest!
Thanks & Regards!
Rajat.