Midrange News for the IBM i Community


Posted by: deepthi
using files that are not journalled in SQLRPGLE program
has no ratings.
Published: 30 May 2012
Revised: 23 Jan 2013 - 1640 days ago
Last viewed on: 21 Jul 2017 (3709 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.

using files that are not journalled in SQLRPGLE program Published by: deepthi on 30 May 2012 view comments(4)

I get an error while running a SQLRPGLE program that uses SQL statements on files that are not journaled. i don't want to journal the files. Is there a way to use these files in SQLRPGLE without journalling.

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

COMMENTS

(Sign in to Post a Comment)
Posted by: neilrh
Premium member *
Jackson, MI
Comment on: using files that are not journalled in SQLRPGLE program
Posted: 5 years 1 months 21 days 12 hours 51 minutes ago

Add the following as the first sql statement in your program (I put it right after the d- or i-specs and before the Main Processing c-specs:

 exec sql set option DatFmt = *iso,     
                     Commit = *none,    
                     CloSqlCsr = *endmod;

The DatFmt makes sure that all sql workfields can contain a 4-digit year, the default is 2-digit year, which limits the allowable date range to 1940-01-01 thru 2039-12-31.  Commit gets around your issue of "needing" to have files journalled.  And the last closes open cursors when the module closes (usually end of program).

Posted by: Ringer
Premium member *
Comment on: using files that are not journalled in SQLRPGLE program
Posted: 5 years 1 months 21 days 11 hours 20 minutes ago
Edited: Wed, 30 May, 2012 at 08:41:58 (1878 days ago)

I use this. I prefer the *ENDACTGRP so the any ODP's have a chance of being reused (so runs faster) on the 3rd call.

Exec SQL
  Set Option    
    Commit = *NONE,
    Naming = *SYS,
    DLYPRP = *YES,                          
    CLOSQLCSR = *ENDACTGRP,   // Reuse ODP !
    DATFMT = *ISO,                          
    TIMFMT = *HMS,                          
    USRPRF = *OWNER,                        
    DYNUSRPRF = *OWNER ;                    

Chris Ringer

Posted by: neilrh
Premium member *
Jackson, MI
Comment on: using files that are not journalled in SQLRPGLE program
Posted: 5 years 1 months 21 days 10 hours 32 minutes ago
Edited: Wed, 30 May, 2012 at 09:29:21 (1878 days ago)

Yeah, we've not hit this with our site standards yet.  The ones I've included so far are the ones that have caused me grief (grief = helpdesk calls).  I might take your list and throw that against the wall for site standards and see what sticks.

Posted by: DaleB
Premium member *
Reading, PA
Comment on: using files that are not journalled in SQLRPGLE program
Posted: 5 years 1 months 21 days 8 hours 45 minutes ago

The key line in both of those was the Commit = *NONE, which can also be done on the CRT* command.

The other possibility is to do it on the individual statements with an isolation clause, as in UPDATE blah blah WITH NC.