Is writing Primary Key Out of sequence & insert Possible

aakar · New User Joined: 08 Apr 2008 Posts: 36 Location: mumbai

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).

Robert Sample · Posted: Tue Nov 11, 2008 12:21 am

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):

aakar · New User Joined: 08 Apr 2008 Posts: 36 Location: mumbai

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.

Pedro · Posted: Tue Nov 11, 2008 5:06 am

There are different kinds of VSAM files. Are you sure you create KSDS? Do you still have your DEFINE CLUSTER command (show us)?

Robert Sample · Posted: Tue Nov 11, 2008 5:08 am

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.

aakar · New User Joined: 08 Apr 2008 Posts: 36 Location: mumbai

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.

Robert Sample · Posted: Tue Nov 11, 2008 5:41 am

Is ACCESS MODE DYNAMIC? If it is sequential, you'll need to write in ascending key sequence.

aakar · New User Joined: 08 Apr 2008 Posts: 36 Location: mumbai

it is dynamic...and just a correction it is open in I-O mode. but is being only written to...

dick scherrer · Posted: Tue Nov 11, 2008 6:02 am

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.

aakar · New User Joined: 08 Apr 2008 Posts: 36 Location: mumbai

here is the listcat;

Robert Sample · Posted: Tue Nov 11, 2008 6:20 am

From the Language Reference manual (emphasis added by me):

Robert Sample · Posted: Tue Nov 11, 2008 6:23 am

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.

aakar · New User Joined: 08 Apr 2008 Posts: 36 Location: mumbai

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.

Robert Sample · Posted: Tue Nov 11, 2008 7:41 am

Well, glad you got it solved!