I have tried searching the forums for a relevant answer and couldn't find it. Most of the answers were very helpful, but I couldn't find a topic which deals with using a TRANS() function from within a loop. May be I missed it and would sure appreciate it if you can point me in the right direction.
Overall Requirement:
I have to load a table with one month data, wait for another process to make changes to the data and then retrieve it once that other process is done and load the target table, all within one batch job. This process should be repeating for 2 years worth of data. No manual intervention is needed.
In order to do the part where I load the table and wait for the other process, I thought of doing it this way: Have a separate job that keeps polling that table for an indicator that the process is done and once it finds it, populate (say a value '1') a hashed file (chose this because I came across the TRANS() function which might help me in reading the value) and read from it...If it is a '1' then the process is finished and I can pick up the records from the table and load it into the target table, if not wait for some more time before checking again.
I coded a dummy job to test out this functionality (as the tables are not yet built). This job populates a hashed file with three rows (say with values 'X', 'Y', 'Z') as key columns. I am passing in a parameter value for that job and all this does is load that value for all the key columns in the Hashed file.
Ex: If I passed '1' as parameter value then the Hashed file with populate as
Code: Select all
DmyVal (key) Value
X 1
Y 1
Z 1
This is what I am doing within this batch: (NOTE - our Hashed files are externally path-ed)
Code: Select all
vExecCmd = "SETFILE ":pFilePath:" ":pFilePointer:" OVERWRITING"
Call DSExecute("UV", vExecCmd, Output, SystemReturnCode)
If SystemReturnCode = 0
then
FOR vLoop = 1 to pEndVal STEP 1
vFileval = ''
....<ATTACH THE JOB THAT LOADS THE HASHED FILE>....
....<passing the parameter into the job as vLoop>....
vFileval = TRANS(pFilePointer,pRecId,pFieldVal,pErrCode)
If vLoop <= pEndVal then
If Len(Trim(vFileval)) = 0 or vFileval < pEndVal
Then
vSleepSec = pSleepSec
Call DSLogInfo('The Value of ':pRecId:' is: ':vFileval:'. We need to have ':pEndVal,'JobControl')
Sleep vSleepSec
CONTINUE
End
Else
Call DSLogInfo('SUCCESS!! The Value of ':pRecId:' is: ':vFileval,'JobControl')
EXIT
End
end
else
Call DSLogInfo('Number of iterations is up. Exiting the process','JobControl')
EXIT
end
NEXT vLoop
end
else
Call DSLogFatal('UV Command did NOT execute!','JobControl')
end
pEndVal = 3; pFilePointer = pathname & name of the Hashed file; pRecId = X; pFieldVal = 1; pErrCode = X; pSleepSec = 30; pFilePointer = fp1
I want to check if 'Z' row has a value of '3'...if so, exit the loop and declare SUCCESS else, keep checking for it (for 3 iterations) and then exit the loop.
Hashed file would look like:
Iteration 1
X 1
Y 1
Z 1
Iteration 2
X 2
Y 2
Z 2
Iteration 3
X 3
Y 3
Z 3
For first iteration, I am getting
Code: Select all
The Value of Z is: 1. We need to have 3...
Sleep executes and second run is happening...
For Second iteration: I should be getting
Code: Select all
The Value of Z is: 2. We need to have 3...
Sleep executes and third run should happen...
Code: Select all
The Value of Z is: 1. We need to have 3...
Sleep executes and third run is happening...
What I am thinking is that the the TRANS() function is reading correctly for the first time and when it comes to the next iterations, it is not at all reading from the Hashed file. I came to this conclusion, as I have checked the job (View Data on the Hashed file) between iterations and the file is being updated. But, in the log of the batch job, I am viewing that '1' is the value being read from the Hashed file irrespective of the iteration.
Am I missing something or is this how TRANS() was supposed to work? If so, can you please let me know if there are any alternatives to achieve this? I apologize for such a long post, but I thought giving all the information (including my trials) can help achieve a faster solution :D
Can you also let me know how I can delete the VOC entry created by SETFILE command?
Thanks in advance,
Vivek Gadwal.