Want to protect your cyber security and still get fast solutions? Ask a secure question today.Go Premium

x
?
Solved

embedded SQL in RPG missing results

Posted on 2009-02-22
7
Medium Priority
?
2,134 Views
Last Modified: 2013-12-06
I've been updating some old RPG programs to SQLRPGILE.  This particular program is not giving me the same results as a runsql would.

When I run the statement in an Interactive SQL session (STRSQL or cwbundbs.exe), I get the right results / # of rows.  When I run the same sql in the rpg program, I am missing about 25% of the records.

The RPG program appears to end properly with an SQLSTT of '0200'.   see code below. Any ideas would be greatly appreciated.
main calc specs:
exec sql SET OPTION datfmt=*iso,closqlcsr=*endmod,naming=*sql,commit=*none,usrprf=*user,dynusrprf=*user;
 
exec sql DECLARE c1 CURSOR FOR 
(SELECT a.fld1, d.fld1, d.fld2, b.fld6
  FROM MYLIB.fl1 a, mylib.fil2 b, mylib.fil3 c, mylib.fil4 d 
 WHERE a.fld1=b.fld2 
   AND b.fld2=c.fld1 
   AND a.fld1=d.fld1 
   AND d.code=:parm1) 
UNION 
(SELECT a.fld1, e.fld1, e.fld2, b.fld6
  FROM mylib.fil1 a, mylib.fil2 b, mylib.fil3 c, mylib.fil4 d, mylib.fil5 e
 WHERE a.fld1=b.fld1 
   AND b.fld2=c.fld1
   AND c.fld3=d.fld1
   AND a.fld1=e.fld1 
   AND d.code=:parm1) 
 ORDER BY fld2;
exec sql OPEN c1; 
if sqlstt <> SQLSuccess; 
  exec sql GET DIAGNOSTICS CONDITION 1 :outsqlmsg = MESSAGE_TEXT;
  dbg_msg = %trim(outsqlmsg);
  except @errDetail;
endif;
exec sql fetch c1 into :sqlds;
DoW SQLStt = SQLSuccess; 
  ...
  exec sql fetch c1 into :sqlds;
ENDDO
...
*INLR =*ON;

Open in new window

0
Comment
Question by:hdgh
  • 4
  • 3
7 Comments
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 23706139
You mean SQL State = '02000', I'm sure.

Basic code looks fine, but without seeing the whole program, the parms you are passing, the interactive statement, and the data, it is hard to guess what causes the difference in results.

Some thoughts:

Offhand, first thing I'd suspect is that parm1 doesn't match the value that what you are using in RUNSQL interactively, or the interactive query doesn't really match what is coded here.  Post the interactive query that you are running, and do a DUMP in the RPG immediately before your DECLARE CURSOR to verify that PARM1 really contains the same value as the interactive statement.

Are you, perhaps, doing something in your DOW loop that is causing some records to get dropped or not counted?  Again, hard to guess without seeing the code.

If you are relying on the library list in your interactive query, I'd verify that it is set properly, or, better yet, qualify your library names.

Are there file overrides in place at any time that could account for the difference?

Anyway, post more code and your parms if none of that helps.

- Gary Patterson

0
 

Author Comment

by:hdgh
ID: 23706230
Thanks for the reply Gary,

I should have mention that the system is at v5r4 with cum ptf C8183540 and db group level 18.
First of all, yes, SQL State = '02000'
Second, the Parm value is correct.  I have stepped through the debugger and the parameter values are right, however there are over 500,000 records retrieved, which makes is very tiresome to debug the whole pgm.

Inside the dow look is just a counter based on the dates from b.fld6.  I
nbrDay = %diff(%date:sqlds.fld6:*DAYS);
select
  when nbrdays >= 60;
    cnt60 = cnt60 + 1;
    total60 = total60 + 1;
  when nbrdays >= 30;
  .....

The total60 line would be missed and still contain a value of 0 ( on the first instance) and cnt60 would have a value of 1.  I found this to be VERY odd.  I have seen this behaviour before and not quite sure why it is happening.  I have verified each line ends with a semi-colon (;).

I know the counts are off because the vendor's applicaiton just gives me a total count.  The pgm is just to break down the counts based on the number of days and facility code.  The counts are correct when running the interactive SQL.

Thanks Gary
 - Jeff
0
 
LVL 36

Accepted Solution

by:
Gary Patterson earned 2000 total points
ID: 23706614
Jeff,

This is "new code".  The problem is almost certainly in the logic.

As I am sure you can appreciate as a programmer, it is really hard to do much diagnosis without seeing all the code (and its take a -lot- more back-and-forth and time, which I am now out of for today, unfortunately).

Imagine a user coming to you to diagnose what may well be a program problem, but only showing fragments of the source code program. I mean, I'm pretty good at diagnosis, but I'm not that good!  :-)

For example, I'd really like to see the complete SELECT clause so I can see if there are any possible gaps in your SELECT block.  A common mistake with %DIFF, for example, is to forget that it can return negative numbers, and not have a clause that can catch that case (like this one will if the file has any records that contain a future date).  Is there an OTHER clause to catch any "unaccounted for" records?  Are your counters getting initialized properly, incremented properly?  Are they getting stepped on someplace?

If would be easiest if you can just download it as a text file and post it.  If we can eliminate the program, then we can move on to "less-likely" suspects.  The same exact database query engine evaluates both queries: regardless if it comes from RUNSQL or an EXEC in a program.  If the queries, parameters, and underlying data are truly identical, then I'm having trouble coming up with any scenario where you're going to get two different sets of results.

Realistically, it is -much- more likely that your new code is flawed than this is an OS or database bug.  It could happen, but in my experience, 999 times out of 1000 (and I'm probbly conservative), it is the new code.

I don't think I exactly understand what you are trying to describe when you say that the total60 line gets "missed".  Are you sure you are debugging with the correct source member?  Any chance the source and object are out of sync?  That could put the debugger "off" a line or two.  Are these counters (why are there two counters counting the same thing?) getting initialized differently and/or incremented elsewhere?  This particular behavior, if I understand it, isn't the kind of bug that a database problem would cause.  This is source-object sync issue, initialization, or assignment error.

Humor me:  comment out the whole DO loop and substitute the block below (you'll need to declare garyCount, of course).  Recompile, and run, and compare the count with the count from the query below it.  If you still have a count mismatch, then we do have something out-of-the ordinary.  Otherwised you've just got a logic bug that's causing some records not to get counted.

exec sql fetch c1 into :sqlds;
garyCount = 0;
DoW SQLStt = SQLSuccess;
  garyCount = garyCount +1;
  exec sql fetch c1 into :sqlds;
ENDDO
dump(a);

RUNSQL this (substituting the proper value for PARM1, of course):

select count(*) from
((SELECT a.fld1, d.fld1, d.fld2, b.fld6
  FROM MYLIB.fl1 a, mylib.fil2 b, mylib.fil3 c, mylib.fil4 d
 WHERE a.fld1=b.fld2
   AND b.fld2=c.fld1
   AND a.fld1=d.fld1
   AND d.code=:parm1)
UNION
(SELECT a.fld1, e.fld1, e.fld2, b.fld6
  FROM mylib.fil1 a, mylib.fil2 b, mylib.fil3 c, mylib.fil4 d, mylib.fil5 e
 WHERE a.fld1=b.fld1
   AND b.fld2=c.fld1
   AND c.fld3=d.fld1
   AND a.fld1=e.fld1
   AND d.code=:parm1))

If records are truly getting skipped somehow, run the program under debug, and pause in the middle.  Do a sysreq 3, and look at the open files to verify that the proper files in the proper libs are reall open. Open a new session, verify that startup programs aren't placing any overrides, and run the interactive query in a new session.  Compare results.

Gotta go for now. Happy hunting!

- Gary Patterson
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:hdgh
ID: 23707747
Hey Gary,
Thanks for the advise !! I've attached the pgm. It returns a count of 2772; sqlstt = '02000'

I then changed the both select statements to SELECT count(*), removed the ORDER BY clause and changed the counter to: reccnt = reccnt + %int(sqlds.fld1);
result: reccnt= 5222

Is there something wrong with the way the cursor is being declared ? Am i missing something obvious here ?

Thank Gary,
sqlrpgle-pgm.txt
0
 

Author Comment

by:hdgh
ID: 23708132
I figured it out.
I stripped out all the code and recreated bits at a time.  I also added a close cursor statement to the end, however I highly doubt that was the problem.
Thanks for all your help Gary !!!
0
 

Author Closing Comment

by:hdgh
ID: 31549804
Thanks again Gary,  it helps to get others input especially after racking my brain over this for several hours. I should have just stripped it down to the basics and started from there.  Would have saved me lots of time !!
0
 
LVL 36

Expert Comment

by:Gary Patterson
ID: 23708847
Glad you got it worked out.  Sometimes it just helps to have someone to describe the problem to.

- Gary Patterson
0

Featured Post

Nothing ever in the clear!

This technical paper will help you implement VMware’s VM encryption as well as implement Veeam encryption which together will achieve the nothing ever in the clear goal. If a bad guy steals VMs, backups or traffic they get nothing.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Just about everyone has an old PC laying around.  Ask anyone in the IT industry, whether they are a professional or play in it as a hobby.  From outdated Desktops to cheap "throwaway" laptops, they are all around and not as hard to "fix up" as you m…
As the title indicates, I have done this before. It chills me everytime I update the OS on my phone, (http://www.experts-exchange.com/articles/18084/Upgrading-to-Android-5-0-Lollipop.html) because one time I did this and I essentially had a bricked …
This is used to tweak the memory usage for your computer, it is used for servers more so than workstations but just be careful editing registry settings as it may cause irreversible results. I hold no responsibility for anything you do to the regist…
Hi friends,  in this video  I'll show you how new windows 10 user can learn the using of windows 10. Thank you.
Suggested Courses

578 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question