Midrange News for the IBM i Community


Posted by: Chris Proctor
Programmer Analyst
Columbia Sports Company
Portland, OR
Disappearing view mystery.... :-)
has no ratings.
Published: 27 Sep 2012
Revised: 23 Jan 2013 - 4108 days ago
Last viewed on: 22 Apr 2024 (5086 views) 

Using IBM i? Need to create Excel, CSV, HTML, JSON, PDF, SPOOL reports? Learn more about the fastest and least expensive tool for the job: SQL iQuery.

Disappearing view mystery.... :-) Published by: Chris Proctor on 27 Sep 2012 view comments(10)

Good morning! I have an RPG program that is creating a view. If I call the program it creates the view. I can then go into SQL and view the data. However, once I sign off and back on the view is gone.

I didn't create the view QTEMP, so my understanding is that it's like a logical and should stay. Does anyone have any suggestions where my problem might be? Here's my create command that is being called via QCMDEXC within the RPG program:

create view jda2dts/j_invaud as select * from invaud where itrdat >= 101227

Thanks for any input!

Chris

Return to midrangenews.com home page.
Sort Ascend | Descend

COMMENTS

(Sign in to Post a Comment)
Posted by: chrisp
Premium member *
Portland, OR
Comment on: Disappearing view mystery.... :-)
Posted: 11 years 6 months 27 days 7 hours 29 minutes ago

Here's the actual code from the program, just in case I have something wrong there:

    

// create views for invaud, invcal and trafhst

 

sqlstmt = 'create view jda2dts/j_invaud as select * from invaud -

 

where itrdat >= ' + %char(datbak) + '';

 

execsqlexecuteimmediate :sqlstmt;

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Disappearing view mystery.... :-)
Posted: 11 years 6 months 27 days 7 hours 28 minutes ago

The copy took the spaces out of the "execute immediate" statment

 

Posted by: Ringer
Premium member *
Comment on: Disappearing view mystery.... :-)
Posted: 11 years 6 months 27 days 7 hours 9 minutes ago

That's bizarre. Do a DPPFD before you sign off to see if anything weird shows up. Maybe the LF (View) is based on a PF (Table) in QTEMP? Not sure!

Chris Ringer

 

Posted by: chrisp
Premium member *
Portland, OR
Comment on: Disappearing view mystery.... :-)
Posted: 11 years 6 months 27 days 6 hours 54 minutes ago

Hi Chris

Actually, I found it! The program had to be modified to do a COMMIT after the view is created, otherwise it disappears. I also compiled the program for COMMIT(*NONE). That took care of the issue. Another thing to store away in the gray matter!

Thanks for the recommendation!

Chris

Posted by: Ringer
Premium member *
Comment on: Disappearing view mystery.... :-)
Posted: 11 years 6 months 27 days 5 hours 14 minutes ago

Wow. I thought COMMIT was only for rows/records. Learned something new. Thanks.

Chris Ringer

Posted by: bobcozzi
Site Admin ****
Chicagoland
www.cozTools.com/qryfile
Comment on: Disappearing view mystery.... :-)
Posted: 11 years 6 months 27 days 3 hours 13 minutes ago

Yes, I good habit to get into is making sure your first line of "Calc's" in the SQLRPGLE source member is:

 

/free
     EXEC SQL SET OPTION COMMIT = *NONE;

 

Posted by: jjcllhn
Premium member *
Comment on: Disappearing view mystery.... :-)
Posted: 11 years 6 months 26 days 9 hours 27 minutes ago

Or you could decide to start working with commitment control, and commit the pending changes.

 

EXEC SQL COMMIT;

 

 

Posted by: Paulster
Premium member *
Sweden and The Netherlands
Comment on: Disappearing view mystery.... :-)
Posted: 11 years 6 months 23 days 19 hours 9 minutes ago

...or you just do the commit in RPG...

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Disappearing view mystery.... :-)
Posted: 11 years 6 months 23 days 13 hours 39 minutes ago

Hmm... I've never tried mixing them like that. Does SQL do it's work (COMMITS and everything else) in the same commitment definition as the program in which it's embedded?

Posted by: Paulster
Premium member *
Sweden and The Netherlands
Comment on: Disappearing view mystery.... :-)
Posted: 11 years 6 months 22 days 20 hours 46 minutes ago

Indeed, it does.