I have an interesting problem which I hope you can help with.
I'm using DS to create a SQL script from a OCI selection of fields. Basically each of the fields I need are concatenate to build up a whole load of SELECT statements. So what I end up with is a script that looks like this....
SPOOL tmpT22Out.csv
SET FEEDBACK OFF
SET PAGESIZE 0
SET LINES 500
SET TRIMSPOOL ON
SET ECHO OFF
SET NEWPAGE 0
SET HEADING OFF
SELECT histrelid||'*'||oldpblid||'*'||newpblid||'*'||TO_CHAR(changedate,'YYYY-MM-DD HH24:MI:SS')||'*'||crn||'*'||polychangeindicator||'*'||LEVEL||'*TF44071390*5.26291*N*12' FROM mv_tblhistoryrelation t CONNECT BY PRIOR newpblid = oldpblid START WITH oldpblid =12 ORDER BY LEVEL;
SELECT histrelid||'*'||oldpblid||'*'||newpblid||'*'||TO_CHAR(changedate,'YYYY-MM-DD HH24:MI:SS')||'*'||crn||'*'||polychangeindicator||'*'||LEVEL||'*TF41159838*4.71790753*N*76' FROM mv_tblhistoryrelation t CONNECT BY PRIOR newpblid = oldpblid START WITH oldpblid =76 ORDER BY LEVEL;
SPOOL OFF
EXIT
The final "SPOOL OFF" and "EXIT" are added through a transform stage which also calls the sql script as an after stage routine call using ExecSH. The idea is that the results from the spool file will then be parsed and processed a little more and loaded to a target table.
All this works fine with test size data but when it's run for real it aborts with a classic "Abnormal termination". I've check the &PH& file for more clues but nothing. Does anyone know if an after stage call has a problem with timeout because the problem occurs immediately after the called script has finished. I'm able to verify that the script has been created correctly and has in fact run to completion with no unexpected error's, however it appears that as soon as it's finished DS is throwing a wobbly. The script runs in about 3hours and produces an output of some 1.1m rows from a generated script of some 368k
Could you post the complete abnormal termination error? And are you certain that doing a reset doesn't give you anything along the lines of a message "from previous run"? Even though the message does come from the &PH& directory, it might have been missed. Or, as an alternative, you could clear the &PH& contents before this run and check all the files.
Also, can you go into TCL and execute your script with a SH -c "yourscript" command and have it return to the TCL level without an error?
I didn't check for messages on resetting, but I like your suggestion about the TCL command. I'm gonna give that a go and see what happens. If I get the abnormal termination I'll be sure to check for the reset log messages.
I've changed the after stage routine call to use ExecTCL with the argument of SH -c "$ORACLE_HOME/bin/sqlplus #SourceOraUser#/#SourceOraPass#@#SourceOraDB# @tmpT22Script.sql". Unfortunately I get the same abnormal termination at the same point. It seems that it runs the script being called and at the end when control should be handed back to continue the process I get the abort. I've checked the output SPOOL file created by the called script and it's definitely completing with no error at the end.
I've again checked the &PH& file but no further details are provided. The reset doesn't seem to provide any more info either.
And does the same error (or any error) happen when you call up the command manually from the TCL environment? Also, what if you change your SPOOL command to a "COMO ON {ComoFileName}" and "COMO OFF" - perhaps redirecting output to SPOOL causes some internal confusion in the job; turning on COMO should have the same effect except that it puts the output into the &COMO& directory and doesn't turn off logging to the &PH& directory.
I don't really see the point of using ExecTCL over the ExecSH I assume you were using before that, but I'll leave that between you two. I'm also wondering why you need to do this after stage? I don't really know if this would help but the more 'traditional' approach would be to call the sqlplus script after job, which typically works fine. But then I've never processed a script of some 368,000 lines, which is what it seems you are saying you are doing.
I'd suggest 'after job' and then take your 'spool file parse/process/load' into another job. They really should be two separate pieces, anyway. I mean, if there are errors in the later portions, do you really want to have to repeat the first 3 hours again? Are they even repeatable?
-craig
"You can never have too many knives" -- Logan Nine Fingers
Craig you're right it should really be 2 jobs, but I guess I was just being lazy and thinking I could get away with it. The reason being we have a DS process that maintains a summary table of load stat's which contains load start and end time, row counts, etc. But this process was put together at a time when the loads were simple and required only 1 job to load a particular table. I didn't want to have to change that too right now which is why it's all in the same job, but I guess that's what I'm going to have to do.
I'm going to try is to call it in the before stage of the next transform along. I've no idea why this should make a difference but then I've no idea why it's not working anyhow (clutching at straws!). If it fails then I'll try running the same command from TCL to see what happens. I didn't quite understand the COMO thing. I guess the suggestion is to not use the SPOOL in oracle but capture the output through set COMO on at TCL. I've never done that before so I'd have to figure out how I would then read the COMO file and if I could do this though a sequential file. If all that still fails then I'll split it out to two jobs and take it from there.
I had no joy running the sql script as a before-stage call either, same problem. I did run it in TCL an it worked so that confirmed that there was no problem with the script. So what I then did was call the sql script as a filter command in the output of a seq file. This worked like a dream and it behaved very much like a pipe.
Thanks for your input.
Regu.
Last edited by rsaliah on Thu Jul 20, 2006 8:27 am, edited 1 time in total.
Inspect the source code for ExecSH or ExecTCL. See what they do with quote characters. They are notorious for it. Clone them and make them work as you would prefer.
IBM Software Services Group
Any contribution to this forum is my own opinion and does not necessarily reflect any position that IBM may hold.
</a>
