Re: Real strange RUNSQLSTM behavior

[Alan Campin <alan0307d@xxxxxxxxx>]

Sounds like you need to talk to IBM. Also make sure that you do not have
duplicate copies of the program floating around.

On Tue, Dec 22, 2009 at 4:32 PM, Tom <tomh@xxxxxxxxxxx> wrote:

> This past weekend, we upgraded from v5r2 to v5r4.  A RUNSQLSTM that we've
> been executing for years is now acting up and I'm at a loss.
>
>
>
> Here's the CL QRY003C:
>
> RUNSQLSTM  SRCFILE(RGPL/QUERYSRC) SRCMBR(ZFIELDTICK) COMMIT(*NONE)
> ERRLVL(30)
>
>
>
> Here's the SQL statements in ZFIELDTICK :
>
>
>
> INSERT INTO LOCATION/FIELDTICK
>
> SELECT
>
>  ALL       301 AS LOC,
>
>            (THTRANCN*1000000)+(THTRANMO*100)+THTRANDA+(THTRANYR*10000) AS
>
>            TICKDATE, T01.THCUSTNO, T02.CMNEWCUS, T01.THTICKNO,
> T01.THTRANCN,
>
>            T01.THTRANYR, T01.THTRANMO, T01.THTRANDA, T01.THROUTNO,
>
>            T01.THPRIRTE, T01.THRBRNCH, T01.THCBRNCH, T01.THUSERID,
>
>            T01.THCSHCHG, T01.THPURORD, T01.THCSHRCV, T01.THTKTOTL,
>
>            T01.THPOSTCN, T01.THPOSTYR, T01.THPOSTMO, T01.THPOSTDA,
>
>            T01.THSETLCN, T01.THSETLYR, T01.THSETLMO, T01.THSETLDA,
>
>            T01.THORGTOT
>
>  FROM      RAP300301/TICHIS T01,
>
>            RAP300301/CUSMAS T02
>
>  WHERE     THCUSTNO = CMCUSTNO
>
>    AND(    (THTRANCN*1000000)+(THTRANMO*100)+THTRANDA+(THTRANYR*10000) >
>
>            YEAR(CURRENT DATE-1 YEAR)*10000.+MONTH(CURRENT DATE-1 YEAR)*100.
> +
>
>            DAY(CURRENT DATE-1 YEAR)
>
>    AND     CMNEWCUS <> 0)
>
>
>
>
>
> The Goodness:
>
> If I execute the RUNSQLSTM from a command line, it runs perfect - file
> FIELDTICK has 34604 records in just a few seconds
>
> If I enter the SQL stmts into interactive SQL, it runs perfect - file
> FIELDTICK has 34604 records in just a few seconds
>
>
>
> The Badness:
>
> When I run program QRY003C, the thing runs wild - generating many, many
> thousands of duplicate entries.  It'll run for hours, until we just kill
> the
> job.
>
>
>
> Any ideas on what I should be looking at?  Is this related to our v5r4
> upgrade?
>
>
>
> Tom Hightower
>
>
>
>
> --
> This is the Midrange Systems Technical Discussion (MIDRANGE-L) mailing list
> To post a message email: MIDRANGE-L@xxxxxxxxxxxx
> To subscribe, unsubscribe, or change list options,
> visit: http://lists.midrange.com/mailman/listinfo/midrange-l
> or email: MIDRANGE-L-request@xxxxxxxxxxxx
> Before posting, please take a moment to review the archives
> at http://archive.midrange.com/midrange-l.