Tweet
RUNSQLSTM hangs in TIMW
If anyone could help with this, I would certainly appreciate it. I have a CL program that needs to copy data into a temporary table, run a couple of RPG programs to update the table, and then produce summary output using SQL (I am replacing an old RUNQRY with RUNSQLSTM). The problem is that when the job reaches this final step, it hangs in a TIMW status, where it just sits and consumes CPU until I end it.
If I submit the RUNSQLSTM as a separate job after creating and updating the input table, it runs just fine and produces the summary output as expected. I had a similar problem on a CL I did last week which began to work correctly after I specified ALWCPYDTA(*NO) on the RUNSQLSTM. However, that doesn't work with this one for some reason.
I have used just about every method one could imagine to create the temporary table, including DYNSQL (from the SEQUEL product, specifying SERVER(*LOCAL)), another RUNSQLSTM to drop, create, and insert into the table, and good old CPYF frmrcd(1). All of this works just as expected, but I get the same results from the final step of creating the summary each time.
I have commented out the RPG applications that update the data to see if that was the issue...it isn't.
The CL looks like this:
RUNSQLSTM SRCFILE(mbsrc) SRCMBR(RESENGS) +
COMMIT(*NONE) ERRLVL(20)
............Drop table mbddl/tempfile;
............Create table mbddl/tempfile like mbrock/prodfile;
............Insert into mbddl/tempfile
............select * from mbrock/prodfile;
(have also used CPYF, etc instead of the above)
...rpg applications run here to update tempfile...
RUNSQLSTM SRCFILE(mbsrc) SRCMBR(crtsumtbl) +
COMMIT(*NONE) ERRLVL(20) ALWCPYDTA(*NO)
..........drop table mbrock/sumtable;
..........create table mbrock/sumtable as (
..........select '1' as breaklvl, ' ' as overflow,
.......... t.fld01, ((t.year*100)+t.month) as proddt,
.......... sum(t.fld02) as fld0201, sum(t.fld03) as fld0301,
.......... .
.......... ...etc for about 40 more fields...
.......... .
.......... sum(t.fld43) as fld4301, sum(t.fld44) as fld4401
.......... from mbddl/tempfile t
.......... where t.fld01 = 100
.......... and t.fldxx = '2'
.......... group by t.fld01, ((t.year*100)+t.month)
.......... order by t.fld01, proddt )
.......... with data;
The SQL is long and ugly because I am feeding a data warehouse and need to use the same field names that RUNQRY has been generating. Also, I needed to substitute the field and table names in this post for client discretion.
HELP?!?!?
If I submit the RUNSQLSTM as a separate job after creating and updating the input table, it runs just fine and produces the summary output as expected. I had a similar problem on a CL I did last week which began to work correctly after I specified ALWCPYDTA(*NO) on the RUNSQLSTM. However, that doesn't work with this one for some reason.
I have used just about every method one could imagine to create the temporary table, including DYNSQL (from the SEQUEL product, specifying SERVER(*LOCAL)), another RUNSQLSTM to drop, create, and insert into the table, and good old CPYF frmrcd(1). All of this works just as expected, but I get the same results from the final step of creating the summary each time.
I have commented out the RPG applications that update the data to see if that was the issue...it isn't.
The CL looks like this:
RUNSQLSTM SRCFILE(mbsrc) SRCMBR(RESENGS) +
COMMIT(*NONE) ERRLVL(20)
............Drop table mbddl/tempfile;
............Create table mbddl/tempfile like mbrock/prodfile;
............Insert into mbddl/tempfile
............select * from mbrock/prodfile;
(have also used CPYF, etc instead of the above)
...rpg applications run here to update tempfile...
RUNSQLSTM SRCFILE(mbsrc) SRCMBR(crtsumtbl) +
COMMIT(*NONE) ERRLVL(20) ALWCPYDTA(*NO)
..........drop table mbrock/sumtable;
..........create table mbrock/sumtable as (
..........select '1' as breaklvl, ' ' as overflow,
.......... t.fld01, ((t.year*100)+t.month) as proddt,
.......... sum(t.fld02) as fld0201, sum(t.fld03) as fld0301,
.......... .
.......... ...etc for about 40 more fields...
.......... .
.......... sum(t.fld43) as fld4301, sum(t.fld44) as fld4401
.......... from mbddl/tempfile t
.......... where t.fld01 = 100
.......... and t.fldxx = '2'
.......... group by t.fld01, ((t.year*100)+t.month)
.......... order by t.fld01, proddt )
.......... with data;
The SQL is long and ugly because I am feeding a data warehouse and need to use the same field names that RUNQRY has been generating. Also, I needed to substitute the field and table names in this post for client discretion.
HELP?!?!?
Comment