Question for OVRDBF with SEQONLY parameter

Posted on 2009-05-14
Last Modified: 2012-05-07
In one of our CL program in OVRDBF command we have specified SEQONLY to *YES. The jobs are taking almost 6  to 7 hours against 2 hours and the jobs are generating QPJOBLOG spoolfiles with 1500 pages each. when i check the spool file i have got msgs SEQONLY(*YES) changes to SEQONLY(*NO). i suspect that these msgs will be logged when the program occurs duplicates in the file.  Is there any way to avoid the spool files without deleting the duplicates or changing the OVRDBF to SEQONLY to *NO.  please suggest me to overcome this issue to run the jobs normally

Question by:manjunath7782
  • 4
LVL 35

Expert Comment

by:Gary Patterson
ID: 24389806
Please supply the full message text and message ID.

My guess is that you are overriding to SEQONLY(*YES), but then doing a keyed open of the file in your program.  If you open a file SEQONLY(*YES), then you need to NOT do a keyed open in your RPG program(s) - you need to do a sequential open, and the programs need to be designed to work without performing keyed operations (CHAIN by key, READE/PE by key, SETLL/GT by key, UPDATE by key, keyed DELETE).

If you perform these operations in your program, the DMBS has to ignore your instructions to open the file for sequential access only, since it can only process these operations against a file that is opened for keyed access, and you'll get an error message when this happens.  All this message handling can hurt the performance of your program (plus all of that random IO).

- Gary Patterson
LVL 35

Expert Comment

by:Gary Patterson
ID: 24389818
Oh, and it is unlikely that this has anything to do with duplicate records.

- Gary Patterson
LVL 27

Expert Comment

ID: 24417310
Minor note...

> ...1500 pages each.

...suggests a large number of database opens.

Free Tool: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

One of a set of tools we're offering as a way to say thank you for being a part of the community.


Author Comment

ID: 24482030
In the program, we are not using READ or CHAIN operations for few files but messages are getting appeared on these files also. These messages are degrading the performance. To improve the performance can we use access path maintainance as *REBLD instead of *IMMED .  Please anyone of you  let me know whether this will improve the performance and what issues i may face if i change the access path maintainance.
LVL 35

Expert Comment

by:Gary Patterson
ID: 24482922
Changing access path maintenance may further degrade performance, depending on the cause of the problem.  Perhaps it is best to diagnose the actual cause before starting to make changes.

In the short term, I suggest that you consider removing the SEQONLY(*YES) override(s), since the DBMS is ignoring them anyway, and throwing a lot of exceptions that are hurting performance.  Of course, you need to thoroughly test your application after making this change.

Were changes recently made to add SEQONLY(*YES), or were there other changes made to the underlying programs or files that caused this to suddenly start happening?  How are the files in question being opened (show us the overrides and F-specs, at least)?  Are you using shared opens?

There are quite a number of reasons that the DBMS might make this change.  We'll need some more info.  I suggest that you post all of the following:
  1. The source code of the program that places the overrides.
  2. The source coe of the program that is throwing the errors  If the program contains confidential information, post the F-specs, KLISTS, and every line with a database file operation in it (OPEN, CLOSE, READ, READE, READPE, WRITE, UPDATE, DELETE, CHAIN, SETLL, SETGT, EXCEPT).
  3. The names of  the files that are giving you the SEQONLY message (there can be different reasons for different files)
  4. DSPFD output for each problem file (and associated logical and physical files)
  5. The specific message ID (we are assumigCPFxxxx)
  6. An example of one of the error messages for each file, including the F1 extended message description, and the F9 message information.  You will need to view the job log while the job is running to get the F9 information.  This will show what specific program is causing the message to be generated.  You may find that it is a program lower in the call stack that is causing the problem if you are doing a shared open.
The extended error information is particularly important, since it gives additional information on the cause of the problem.

- Gary Patterson
LVL 35

Accepted Solution

Gary Patterson earned 50 total points
ID: 24483409
Assuming this is CPF4028, you can see from the extended message description that there are ten different  condition codes, all describing various possible reasons that the DBMS would nake this change.

We need to narrow it down to the condition codes that you are experiencing - that is why it is important to see the specific extended message that your program is getting.

                         Display Formatted Message Text                        
                                                             System:   PUB1    
 Message ID . . . . . . . . . :   CPF4028                                      
 Message file . . . . . . . . :   QCPFMSG                                      
   Library  . . . . . . . . . :     QSYS                                        
 Message . . . . :   Open of member &1 was changed to SEQONLY(*NO).            

 Cause . . . . . :   Member &1 file &2 in library &3 was opened with the        
   SEQONLY(*YES) parameter specified in the program or on the Override Database
   File (OVRDBF) command. However, the parameter has been changed to            
   SEQONLY(*NO) because of condition &4 shown below:                            

     1 - The program opened member &1 for output operations only and            
   SEQONLY(*YES) was specified with the default number of records.              
   SEQONLY(*YES) was changed to SEQONLY(*NO) to allow the program to handle    
   duplicate key, conversion mapping, key mapping, and select or omit errors,  
   or both select and omit errors at the time of the output operation.          

     2 - The program opened the member for other than input or output          
   operations only.                                                            

     3 - The program opened a logical file member for output operations only    
   and that member is based on more than one physical file member.              

     4 - The record length plus the feedback information for the member is      
   greater than 32,767 bytes.                                                  

     5 - The commitment control level of *ALL is active and the member was      
   opened for input operations only, or the commitment control levels of either
   *ALL, *CS, or *CHG were specified for a distributed file that was opened for
   input operations only.                                                      

     6 - The program opened member &1 for output operations only and            
   SEQONLY(*YES) was specified with the default number of records and duplicate
   key feedback or insert key feedback was requested. SEQONLY(*YES) was changed
   to SEQONLY(*NO) to allow duplicate key feedback or insert key feedback to be
   provided on a record by record basis.                                        

     7 - The program opened a query member that contains at least one          
   subselect.  Either the outermost subselect provided a correlated reference  
   for a subquery or the outermost subselect processed a subquery with an IN, =
   ANY, or <> ALL subquery predicate operator, which is treated as a correlated
   reference. Only one record can be retrieved at a time from this type of      

     8 - The file has referential constraints, triggers, or is a distributed    
   file. SEQONLY(*YES) was changed to SEQONLY(*NO) to allow the trigger        
   programs to be called or the referential constraints to be enforced on a    
   record by record basis, or because SEQONLY(*YES) is not allowed for the      
   distributed file open.                                                      

     9 - The file is a distributed file opened for DSTDTA(*CURRENT). One or    
   more of the nodes in this file have been implemented using join logical file
   subselect.  Either the outermost subselect provided a correlated reference  
   for a subquery or the outermost subselect processed a subquery with an IN, =
   ANY, or <> ALL subquery predicate operator, which is treated as a correlated
   reference. Only one record can be retrieved at a time from this type of      

     10 - The file, or a physical file on which the file is based, contains    
   File Link Control DataLink fields. SEQONLY(*YES) was changed to SEQONLY(*NO)
   to allow the DataLink fields in each record to be processed.                

 Recovery  . . . :   For condition 1 or condition 6:                            
     -- If changing parameter SEQONLY(*YES) to SEQONLY(*NO) is not acceptable,  
   use the Override Database File (OVRDBF) command to override the parameter    
   SEQONLY(*NO) by specifying SEQONLY(*YES N) where N indicates the number of  
   records.  Then try your request again.                  

- Gary Patterson                    

Featured Post

Free Tool: Site Down Detector

Helpful to verify reports of your own downtime, or to double check a downed website you are trying to access.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Question has a verified solution.

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

Most MSPs worth their salt are already offering cybersecurity to their customers. But cybersecurity as a service is wide encompassing and can mean many things.  So where are MSPs falling in this spectrum?
The advancement in technology has been a great source of betterment and empowerment for the human race, Nevertheless, this is not to say that technology doesn’t have any problems. We are bombarded with constant distractions, whether as an overload o…
This video shows how to use Hyena, from SystemTools Software, to bulk import 100 user accounts from an external text file. View in 1080p for best video quality.
I've attached the XLSM Excel spreadsheet I used in the video and also text files containing the macros used below.…

839 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