Portal | Manuals | References | Downloads | Info | Programs | JCLs | Master the Mainframes
IBM Mainframe Computers Forums Index
 
Register
 
IBM Mainframe Computers Forums Index Mainframe: Search IBM Mainframe Forum: FAQ Memberlist Usergroups Profile Log in to check your private messages Log in
 

 

Is writing Primary Key Out of sequence & insert Possible

 
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM
View previous topic :: :: View next topic  
Author Message
aakar

New User


Joined: 08 Apr 2008
Posts: 36
Location: mumbai

PostPosted: Tue Nov 11, 2008 12:13 am    Post subject: Is writing Primary Key Out of sequence & insert Possible
Reply with quote

Hi,
I have the following queries:
1. Is it possible that in an indexed KSDS we can write a key lower than the highest key existing in the file? So if my last record had record key as 0004 can i write a record with key 0003 and expect VSAM to arrange it correctly in order of primary key?
2. Can we perform an insert in KSDS with key 0003 and expect VSAM to arrange it correctly in order of primary key?
3. If two jobs(J1&J2) with 4 jobsteps are running simultaenously and both are supposed to update the KSDS with one record with sequence no. as primary key in the 4th step.
J1 has sequence no. 0003 & J2 has 0004, say J1 is in step2 & J2 has reached step4 and J2 makes the entry with 0004 in KSDS. Will J1 fail when it reaches step4 of updating the KSDS with RC=21 as its key value is smaller than that of last record? The Shareoption is (4,4).
Back to top
View user's profile Send private message

Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7905
Location: Bellevue, IA

PostPosted: Tue Nov 11, 2008 12:21 am    Post subject:
Reply with quote

1. Yes -- research CI splits in the manual; if you couldn't add keys lower than the current high key value, you couldn't have CI splits.
2. What is the difference between insert and write?
3. Precise results are probably going to depend on how the enqueue process is set up. If not set up, the manual says very specifically the results are unpredictable in the DEFINE CLUSTER SHAREOPTIONS discussion (emphasis added by me):
Quote:
3
specifies that the data set can be fully shared by any number of users. With this option, each user is responsible for maintaining both read and write integrity for the data the program accesses. User programs that ignore the write integrity guidelines can cause VSAM program checks, lost or inaccessible records, irreversible data set failures, and other unpredictable results. This option places heavy responsibility on each user sharing the data set.


4
specifies that the data set can be fully shared by any number of users and buffers used for direct processing are refreshed for each request. This option requires your program to use the ENQ and DEQ macros to maintain data integrity while sharing the data set. Improper use of the ENQ macro can cause problems similar to those described under SHAREOPTIONS 3.
Back to top
View user's profile Send private message
aakar

New User


Joined: 08 Apr 2008
Posts: 36
Location: mumbai

PostPosted: Tue Nov 11, 2008 4:37 am    Post subject:
Reply with quote

Thanks Robert...
1. But everytime i try writing a lower key i get Record out of sequence error, with RC=21. I haven't seen in any reference books or links that i have read
where you can write primary key out of sequence to a KSDS.
2. This question arised to me while trying to understand the purpose of FreeSpace. Most References say Freespace that is left unused is used later on
for doing insertions. So can we insert or write to this freesapce.
3.I am running a batch job and don't have any idea of how i can set ENQ/DEQ macros. Can you please provide more info or give some reference for understanding the same. Also if proper ENQ/DEQ macros have been setup then also will the resources be allocated to J1 who is the step2 only. Will VSAM queue the write statements in such a manner that the entry by J1 is made first to the VSAM and that of J2 is done after J1's entry is done.
Back to top
View user's profile Send private message
Pedro

Senior Member


Joined: 01 Sep 2006
Posts: 2019
Location: Silicon Valley

PostPosted: Tue Nov 11, 2008 5:06 am    Post subject: Reply to: Is writing Primary Key Out of sequence & inser
Reply with quote

There are different kinds of VSAM files. Are you sure you create KSDS? Do you still have your DEFINE CLUSTER command (show us)?
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7905
Location: Bellevue, IA

PostPosted: Tue Nov 11, 2008 5:08 am    Post subject:
Reply with quote

What language are you using? What is generating the "RC=21"? CICS? COBOL file status? IDC3009 message? You need to provide additional data to resolve your issue.

There is absolutely no problems writing primary keys in any sequence you want -- you may get additional CI or even CA splits, but you can write them in any order you want. Now, if you're reading the file sequentially and then attempting to write a record with a lower key, that could cause a problem -- but it's a program logic error, easily corrected by changing program logic.

Freespace is used when writing (or inserting) records after an initial load has occurred. If you attempt to write a record with key 12345 and the previous key is 12340, AND there is free space at least as large as the record in the CI, the freespace will be used for the record rather than writing to another CI. However, if there is no freespace then a CA split will be done to free up CI space for the record. A common mistake I've seen is to set the freespace percentage so low that a record cannot fit in the CI free space, which means nothing can be written to use the freespace.

VSAM will not queue anything -- you must do so with enqueue and dequeue because otherwise updates can be lost if J1 writes to the same CI as J2 at the same time (or worse -- when IBM says results are undefined, they mean it). If you're using a language that supports these functions, such as PL/I or Assembler, you're the one that must code for them. If you're using COBOL, though, you must write a subroutine to perform the functions since batch COBOL does not directly support them. With the ENQ/DEQ set up, your program will enqueue on the CI, do the write, and then dequeue it. The system will ensure only one job at a time has control during the enqueue / dequeue cycle. If the other job wants to write (or read) data in this CI, it'll be forced to wait by the system until the dequeue in performed.
Back to top
View user's profile Send private message
aakar

New User


Joined: 08 Apr 2008
Posts: 36
Location: mumbai

PostPosted: Tue Nov 11, 2008 5:32 am    Post subject:
Reply with quote

Hi Robert,
I am using COBOL program on a KSDS with a primary key. The KSDS has been opened in Output mode and it is only writing to the KSDS.

Quote:
There is absolutely no problems writing primary keys in any sequence you want -- you may get additional CI or even CA splits, but you can write them in any order you want. Now, if you're reading the file sequentially and then attempting to write a record with a lower key, that could cause a problem -- but it's a program logic error, easily corrected by changing program logic.


So as per your above description should or should i not get COBOL FILE STATUS CODE = 21 when i have opened the file in OUTPUT mode and am only writing records to it with primary keys out of sequence.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7905
Location: Bellevue, IA

PostPosted: Tue Nov 11, 2008 5:41 am    Post subject:
Reply with quote

Is ACCESS MODE DYNAMIC? If it is sequential, you'll need to write in ascending key sequence.
Back to top
View user's profile Send private message
aakar

New User


Joined: 08 Apr 2008
Posts: 36
Location: mumbai

PostPosted: Tue Nov 11, 2008 5:56 am    Post subject:
Reply with quote

it is dynamic...and just a correction it is open in I-O mode. but is being only written to...
Back to top
View user's profile Send private message
dick scherrer

Site Director


Joined: 23 Nov 2006
Posts: 19270
Location: Inside the Matrix

PostPosted: Tue Nov 11, 2008 6:02 am    Post subject:
Reply with quote

Hello,

Please post the IDCAMS DEFINE, the SELECT/ASSIGN, the FD statement, the OPEN, and the WRITE that is failing (including the code to set up the key.
Back to top
View user's profile Send private message
aakar

New User


Joined: 08 Apr 2008
Posts: 36
Location: mumbai

PostPosted: Tue Nov 11, 2008 6:17 am    Post subject:
Reply with quote

here is the listcat;
Code:
LISTCAT    ENTRIES(MVSAM.QUAL2.SPLIT.VFILE -                             
                     MVSAM.QUAL2.SPLIT.VFILE.DATE) ALL                     
CLUSTER ------- MVSAM.QUAL2.SPLIT.VFILE                                   
     IN-CAT --- CATALOG.UCATA.DEV06                                           
     HISTORY                                                                   
       DATASET-OWNER-----(NULL)     CREATION--------2008.306                   
       RELEASE----------------2     EXPIRATION------0000.000                   
     SMSDATA                                                                   
       STORAGECLASS ----PDBASEC     MANAGEMENTCLASS--PDBASEA                   
       DATACLASS --------NONSTD     LBACKUP ---0000.000.0000                   
       BWO STATUS------00000000     BWO TIMESTAMP---00000 00:00:00.0           
       BWO---------------(NULL)                                               
     RLSDATA                                                                   
       LOG ----------------(NULL)   RECOVERY REQUIRED --(NO)     FRLOG --------(null)
       VSAM QUIESCED -------(NO)    RLS IN USE ---------(NO)                   
       LOGSTREAMID-----------------------------(NULL)                         
       RECOVERY TIMESTAMP LOCAL-----X'0000000000000000'                       
       RECOVERY TIMESTAMP GMT-------X'0000000000000000'                       
     PROTECTION-PSWD-----(NULL)     RACF----------------(NO)                   
     ASSOCIATIONS                                                             
       DATA-----MVSAM.QUAL2.SPLIT.VFILE.DATA                               
       INDEX----MVSAM.QUAL2.SPLIT.VFILE.INDEX                             
       AIX------MVSAM.QUAL2.SPLIT.VFILE.DATE                               
   DATA ------- MVSAM.QUAL2.SPLIT.VFILE.DATA                               
     IN-CAT --- CATALOG.UCATA.DEV06                                           
HISTORY                                                                         
  DATASET-OWNER-----(NULL)     CREATION--------2008.306                         
  RELEASE----------------2     EXPIRATION------0000.000                         
  ACCOUNT-INFO-----------------------------------(NULL)                         
PROTECTION-PSWD-----(NULL)     RACF----------------(NO)                         
ASSOCIATIONS                                                                   
  CLUSTER--MVSAM.QUAL2.SPLIT.VFILE                                         
ATTRIBUTES                                                                     
  KEYLEN-----------------8     AVGLRECL--------------80     BUFSPACE------------9728
  RKP--------------------0     MAXLRECL--------------80     EXCPEXIT----------(Null)
  SHROPTNS(4,3)      SPEED     UNIQUE           NOERASE     INDEXED       NOWRITECHK
  UNORDERED        NOREUSE     NONSPANNED                                       
CISIZE--------------4096
CI/CA----------------180
NOIMBED       NOREPLICAT
STATISTICS                                                                     
  REC-TOTAL--------------1     SPLITS-CI--------------0     EXCPS---------------4
  REC-DELETED------------0     SPLITS-CA--------------0     EXTENTS-------------1
  REC-INSERTED-----------0     FREESPACE-%CI---------10     SYSTEM-TIMESTAMP:   
  REC-UPDATED------------0     FREESPACE-%CA---------10          X'C33B21BC7F8AB
  REC-RETRIEVED----------1     FREESPC----------5156864                         
ALLOCATION                                                                     
  SPACE-TYPE------CYLINDER     HI-A-RBA---------5160960                         
  SPACE-PRI--------------7     HI-U-RBA----------737280                         
  SPACE-SEC--------------7                                                     
S  SYSTEM SERVICES                                           TIME: 07:49:44     
VOLUME                                                                         
  VOLSER------------CIZADG     PHYREC-SIZE---------4096     HI-A-RBA---------5160960
  DEVTYPE------X'3010200F'     PHYRECS/TRK-----------12     HI-U-RBA----------737280
  VOLFLAG------------PRIME     TRACKS/CA-------------15                         
EXTENT-NUMBER----------1
EXTENT-TYPE--------X'40'
  EXTENTS:                                                                     
   EXTENTS:                                                                   
   LOW-CCHH-----X'026D0000'     LOW-RBA----------------0     TRACKS------------105
   HIGH-CCHH----X'0273000E'     HIGH-RBA---------5160959


Program logic:
Code:

Select:
SELECT O-VSAM-FILE     ASSIGN TO OVSAM
ORGANIZATION IS INDEXED                           
ACCESS MODE  IS DYNAMIC                           
RECORD KEY   IS SEQ-NO               
FILE STATUS  IS WS-FILE-STATUS.       

File description:
FD O-VSAM-FILE                   
     LABEL RECORDS ARE STANDARD           
     DATA RECORD IS O-VSAM-REC.   
01 O-VSAM-REC       PICX(80).                   

Open:
OPEN I-O O-VSAM-FILE

Write :
WRITE O-VSAM-REC OF O-VSAM-FILE


Sorry can't post the entire JCL the DD statement is,
Code:
//OVSAM DD DSN=MVSAM.QUAL2.SPLIT.VFILE,DISP=SHR   
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7905
Location: Bellevue, IA

PostPosted: Tue Nov 11, 2008 6:20 am    Post subject:
Reply with quote

From the Language Reference manual (emphasis added by me):
Quote:
6.2.40.6 WRITE for indexed files


Before the WRITE statement is executed, you must set the prime record key (the RECORD KEY data item, as defined in the File-Control entry) to the desired value. (Note that RECORD KEY values must be unique within a file.)

If the ALTERNATE RECORD KEY clause is also specified in the File-Control entry, each alternate record key must be unique, unless the DUPLICATES phrase is specified. If the DUPLICATES phrase is specified, alternate record key values might not be unique. In this case, the system stores the records so that later sequential access to the records allows retrieval in the same order in which they were stored.

When ACCESS IS SEQUENTIAL is specified in the File-Control entry, records must be released in ascending order of RECORD KEY values.

When ACCESS is RANDOM or ACCESS IS DYNAMIC is specified in the File-Control entry, records can be released in any programmer-specified order.
Are you using MOVE / WRITE or WRITE FROM?
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7905
Location: Bellevue, IA

PostPosted: Tue Nov 11, 2008 6:23 am    Post subject:
Reply with quote

Okay, you define the record key as SEQ-NO. However, your FD does not have SEQ-NO anywhere in it. This will cause errors, and a file status 21 is definitely possible.

You do not have an option here: the RECORD KEY must -- absolutely must -- be part of an 01 under the FD.
Back to top
View user's profile Send private message
aakar

New User


Joined: 08 Apr 2008
Posts: 36
Location: mumbai

PostPosted: Tue Nov 11, 2008 7:21 am    Post subject:
Reply with quote

Thanks robert, the error is gone now. i had coded Sequential and pasted here the code of the production job. Sorry about the mistake & thanks again.
Back to top
View user's profile Send private message
Robert Sample

Global Moderator


Joined: 06 Jun 2008
Posts: 7905
Location: Bellevue, IA

PostPosted: Tue Nov 11, 2008 7:41 am    Post subject:
Reply with quote

Well, glad you got it solved!
Back to top
View user's profile Send private message
View previous topic :: :: View next topic  
Post new topic   Reply to topic    IBMMAINFRAMES.com Support Forums -> JCL & VSAM All times are GMT + 6 Hours
Page 1 of 1

 

Search our Forum:

Similar Topics
Topic Author Forum Replies Posted
No new posts Problem in writing Output file vickey_dw COBOL Programming 5 Mon Nov 14, 2016 11:14 pm
No new posts Easytrieve - Report writing - Only 1 ... Abhi Nature CA Products 2 Wed Sep 14, 2016 11:40 am
No new posts Sequence number add in SORT pshongal SYNCSORT 3 Fri Sep 02, 2016 3:32 pm
No new posts Insert Lines in JCL with Rexx after a... Willy Jensen CLIST & REXX 3 Tue Aug 30, 2016 4:18 pm
No new posts How to insert a lengthy string havin... vidyaa DB2 7 Thu Aug 25, 2016 5:20 pm


Facebook
Back to Top
 
Mainframe Wiki | Forum Rules | Bookmarks | Subscriptions | FAQ | Tutorials | Contact Us