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 - 1611 days ago
Last viewed on: 21 Jun 2017 (2937 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: 4 years 8 months 24 days 20 hours 19 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: 4 years 8 months 24 days 20 hours 18 minutes ago

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

 

Posted by: Ringer
Premium member *
Comment on: Disappearing view mystery.... :-)
Posted: 4 years 8 months 24 days 20 hours 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: 4 years 8 months 24 days 19 hours 44 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: 4 years 8 months 24 days 18 hours 5 minutes ago

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

Chris Ringer

Posted by: bobcozzi
Site Admin ****
Chicagoland
Comment on: Disappearing view mystery.... :-)
Posted: 4 years 8 months 24 days 16 hours 3 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: 4 years 8 months 23 days 22 hours 18 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: 4 years 8 months 21 days 8 hours ago

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

Posted by: DaleB
Premium member *
Reading, PA
Comment on: Disappearing view mystery.... :-)
Posted: 4 years 8 months 21 days 2 hours 30 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: 4 years 8 months 20 days 9 hours 37 minutes ago

Indeed, it does.