Solved

Question for OVRDBF with SEQONLY parameter

Posted on 2009-05-14
6
2,099 Views
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

0
Comment
Question by:manjunath7782
  • 4
6 Comments
 
LVL 34

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
0
 
LVL 34

Expert Comment

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

- Gary Patterson
0
 
LVL 27

Expert Comment

by:tliotta
ID: 24417310
Minor note...

> ...1500 pages each.

...suggests a large number of database opens.

Tom
0
Enabling OSINT in Activity Based Intelligence

Activity based intelligence (ABI) requires access to all available sources of data. Recorded Future allows analysts to observe structured data on the open, deep, and dark web.

 

Author Comment

by:manjunath7782
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.
0
 
LVL 34

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
0
 
LVL 34

Accepted Solution

by:
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      
   query.                                                                      

     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
   processing.                                                                  
   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      
   query.                                                                      

     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                    
0

Featured Post

What Is Threat Intelligence?

Threat intelligence is often discussed, but rarely understood. Starting with a precise definition, along with clear business goals, is essential.

Join & Write a Comment

Suggested Solutions

A procedure for exporting installed hotfix details of remote computers using powershell
A safe way to clean winsxs folder from your windows server 2008 R2 editions
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Illustrator's Shape Builder tool will let you combine shapes visually and interactively. This video shows the Mac version, but the tool works the same way in Windows. To follow along with this video, you can draw your own shapes or download the fileā€¦

708 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

Need Help in Real-Time?

Connect with top rated Experts

13 Experts available now in Live!

Get 1:1 Help Now