Maintaining VSAM KSDS Data Sources

In this section:

The Adapter for VSAM supports SQL update commands for VSAM KSDS data sources without the use of remote procedures. For example, UPDATE, INSERT, and DELETE will be translated into equivalent VSAM low-level read/write calls. No changes to the Master File are required in order to issue SQL INSERT, UPDATE, or DELETE commands against VSAM data sources.


Top of page

x
General Guidelines for Maintaining VSAM KSDS Data Sources

How to:

Reference:

The Structured Query Language (SQL) is intended to be used for access to relational tables, which are flat structures. A VSAM record, like a row in a relational table, has no hierarchical or network structure. However, unlike a relational table, a VSAM record may have repeating fields. It may also have multiple record types which require a different interpretation of the record layout from one record to another.

The adapter is designed to work against a single VSAM record. When a VSAM record can have multiple record layouts, the Master File describes the VSAM record as multiple segments, one for the fixed portion of the record (the root segment) and a separate descendant segment to describe each record layout. Similarly, when a VSAM record has repeating fields, the Master File describes this portion of the VSAM record as an OCCURS segment. However, to VSAM and SQL, these multiple segments still describe one VSAM record. Only one VSAM record can be the target of the SQL Data Manipulation Language (DML) statement. SQL set orientated behavior (multiple updates with one SQL statement) is not supported.

VSAM KSDS data sources have a unique key. To identify the record that is the target of the SQL statement, the SQL statement must supply the complete key for that record.

RECTYPEs in the Master File

When the Master File describes descendant segments with different RECTYPE values, you can insert a record without supplying a value for the RECTYPE field as long as the adapter can identify which RECTYPE is correct for the segment to be inserted. Since each RECTYPE is defined in a different segment in the Master File, if the field list for the INSERT contains a field name specific to that segment, the adapter can identify and insert the correct RECTYPE value.

OCCURS Segments in the Master File

When you want to update, insert, or delete a repeating group (OCCURS segment in the Master File), the technique you use depends on whether there are a fixed or variable number of occurrences defined in the Master File:

Note that when a fixed number of occurrences is specified in the Master File, any attempt to access an occurrence number greater than that fixed number generates an error.

VSAM records can be fixed or variable length. When you add or delete a repeating group in a fixed length record, the record length stays the same. When you add or delete a repeating group in a variable length record, the record length changes.

The WHERE clause of the SQL statement must contain the complete key for the target record. Any record referenced in the SQL query must, using the WHERE clause, be a unique occurrence of that record. If additional fields are included in the WHERE clause for any record, they will be used in conjunction with the key, to identify the segment.



x
Syntax: How to Issue an SQL INSERT Command in VSAM
INSERT INTO mfdname [(field1, field2 ...)] VALUES (value1, value2 ...)

General Rules

The following rules apply to SQL INSERT syntax:



x
Syntax: How to Issue an SQL DELETE Command in VSAM
DELETE FROM mfdname WHERE fieldname=value [AND fieldname=value ...]

General Rules

The following rules apply to SQL DELETE syntax:



x
Syntax: How to Issue an SQL UPDATE Command in VSAM
UPDATE mfdname  SET fieldname=value [SET fieldname=value...]
WHERE fieldname=value  
[AND fieldname=value]

General Rules

The following rules apply to SQL UPDATE syntax:



x
Syntax: How to Obtain the Number of Records Affected by an SQL INSERT, UPDATE, or DELETE Command

PASSRECS returns the number of rows affected by a successfully executed SQL Passthru INSERT, UPDATE, or DELETE command.

Tip: You can change this setting manually or from the Web Console by clicking Data Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the menu. The Change Settings pane opens.

*ENGINE* INT SET PASSRECS {ON|OFF}

where:

INT

Indicates that the PASSRECS setting in this command will be applied globally to all adapters that support SQL INSERT, UPDATE, and DELETE commands.

ON

Provides the number of rows affected in the application program SCB count member after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command. ON is the default value.

OFF

Provides no information after the successful execution of an SQL Passthru INSERT, UPDATE, or DELETE command.

In addition, the adapter updates the &RECORDS system variable with the number of rows affected. You can access this variable using Dialogue Manager.



x
Reference: Processing Not Supported for Maintaining VSAM Data Sources

The following processing is not supported:


Top of page

x
Sample VSAM KSDS Master Files and Data Maintenance Examples

The following examples show how to insert, update, and delete records and occurrences of repeating groups in a VSAM KSDS data source.



Example: Inserting a VSAM KSDS Record With OCCURS=n

Consider the following Master File that describes a repeating field (FLD005 in segment R010) with OCCURS=3. Note that the Master File also describes the ORDER field, which is an internal counter for OCCURS segments:

FILE=VSMW0200          ,SUFFIX=VSAM,$
DATASET='EDAQA.VSMW0200.CLUSTER',   $
 SEGNAME=ROOT ,SEGTYPE=S0,$
 GROUP=GRPKEYA             ,ALIAS=KEY ,A8       ,A8      ,$
  FIELDNAME      =FLD000   ,          ,A8       ,A8      ,$
  FIELDNAME      =FLD001   ,          ,A2       ,A2      ,$
  FIELDNAME      =FLD003   ,          ,A4       ,A4      ,$
  FIELDNAME      =ACCOUNT  ,          ,A2       ,A2      ,$
 SEGNAME=R010 ,SEGTYPE=S0, PARENT=ROOT, OCCURS=3         ,$
  FIELDNAME      =FLD005   ,          ,A8       ,A8      ,$
  FIELDNAME      =ORDER    ,ORDER     ,I4       ,I4      ,$

The following SQL INSERT statement inserts a record with a key field of 1245678. An OCCURS segment cannot be inserted using SQL INSERT when the number of occurrences is fixed:

SQL
 INSERT INTO VSMW0200 (FLD000)
 VALUES('12345678');
END

The following SQL UPDATE statements place values in the occurrences of FLD005. The WHERE predicate in each SQL UPDATE command specifies the complete key for the record and a value for the ORDER field to identify which occurrence to update:

SQL
 UPDATE VSMW0200
 SET FLD005='00001001'
 WHERE FLD000='12345678' AND ORDER=1;
END
SQL
 UPDATE VSMW0200
 SET FLD005 = '00002002'
 WHERE FLD000 = '12345678' AND ORDER = 2;
END
SQL
 UPDATE VSMW0200
 SET FLD005 = '00003003'
 WHERE FLD000 = '12345678' AND ORDER = 3;
END

With OCCURS=n, the occurrence number referenced in the SQL statement must be less than or equal to n. If you reference an occurrence with a number greater than n, the occurrence is not updated and an error message is returned.



Example: Deleting an Occurrence of a Repeating Field in a VSAM KSDS Record

The following SQL DELETE statement deletes the first occurrence of the repeating field FLD005. The WHERE predicate identifies the complete key (FLD000='12345678') and the specific occurrence to delete (ORDER=1):

SQL
 DELETE FROM VSMW0200
 WHERE FLD000='12345678' AND ORDER=1;
END


Example: Deleting a VSAM KSDS Record

The following SQL DELETE statement deletes VSAM KSDS record whose key value is '12345678':

SQL
 DELETE FROM VSMW0200 WHERE FLD000='12345678';
END


Example: Inserting a VSAM KSDS Record With OCCURS=field

When a repeating field has a variable number of occurrences (OCCURS=field or OCCURS=VARIABLE), you can use an SQL INSERT statement to add a new record and insert data into a specific occurrence of the repeating field.

Consider the following Master File in which the number of occurrences of FLD005 is specified by the value in the ACCOUNT field:

FILE=VSMW0201          ,SUFFIX=VSAM,$
DATASET='EDAQA.VSMW0200.CLUSTER',   $
 SEGNAME=ROOT ,SEGTYPE=S0,$
 GROUP=GRPKEYA             ,ALIAS=KEY ,A8       ,A8      ,$
  FIELDNAME      =FLD000   ,          ,A8       ,A8      ,$
  FIELDNAME      =FLD001   ,          ,A2       ,A2      ,$
  FIELDNAME      =FLD003   ,          ,A4       ,A4      ,$
  FIELDNAME      =ACCOUNT   ,          ,I2       ,I2     ,$
 SEGNAME=R010 ,SEGTYPE=S0,  PARENT=ROOT, OCCURS=ACCOUNT  ,$
  FIELDNAME      =FLD005   ,          ,A8       ,A8      ,$
  FIELDNAME      =ORDER    ,ORDER     ,I4       ,I4      ,$

The following SQL INSERT statement inserts a new record with key FLD000='12345678' and the value '00001001' in the first occurrence of FLD005. The ORDER field identifies the occurrence that is inserted, but it represents an internal counter and does not exist in the VSAM record:

SQL
 INSERT INTO VSMW0201 (FLD000,FLD005,ORDER)
 VALUES('12345678','00001001',1) ;
END

To add a second occurrence of FLD005, issue an SQL INSERT statement for key FLD000='12345678' and the value 2 for the ORDER field. Note that the occurrences must be added in order:

SQL
 INSERT INTO VSMW0201 (FLD000,FLD005,ORDER)
 VALUES('12345678','00002002',2) ;
END

If you attempt to add an occurrence that would make the record length exceed the maximum record length defined for the VSAM data source, the occurrence is not added and an error message is returned.



Example: Inserting a Record in a VSAM KSDS Record With Record Types

Consider the following Master File that describes two OCCURS segments with a variable number of occurrences. The key field is FLD000, which is followed by FLD001. Next comes any combination of the two possible layouts for the variable portion of the record. Each repeating group starts with a value that indicates the type of layout that follows. If the RECTYPE field contains the value 2, the repeating group is an instance of segment SEG002, and it contains one 8-byte field (FLD002). If the RECTYPE field contains the value 3, the repeating group is an instance of segment SEG003, and it contains two 8-byte fields (FLD003 and FLD004):

FILE=VSMW0208,          SUFFIX=VSAM,$
DATASET='EDAQA.VSMW0200.CLUSTER',   $
 SEGNAME=ROOT,SEGTYPE=S0,$
  GROUP =GRPKEY      ,ALIAS=KEY   ,USAGE=A8,ACTUAL=A8,$
   FIELD=FLD000      ,E00         ,A08     ,A08      ,$
  FIELD =FLD001      ,E01         ,A02     ,A02      ,$
 SEGNAME=SEG002,PARENT=ROOT,OCCURS=VARIABLE,SEGTYPE=S0,$
  FIELD =RECTYPE     ,2           ,A01     ,A01      ,$
  FIELD =FLD002      ,E02         ,A08     ,A08      ,$
 SEGNAME=SEG003,PARENT=ROOT,OCCURS=VARIABLE,SEGTYPE=S0,$
  FIELD =RECTYPE     ,3           ,A01     ,A01      ,$
  FIELD =FLD003      ,E03         ,A08     ,A08      ,$
  FIELD =FLD004      ,E04         ,A08     ,A08      ,$

The following SQL INSERT statement adds an occurrence of SEG002. The INSERT statement references FLD002, which identifies the INSERT as an instance of SEG002. The adapter automatically inserts the value 2 in the RECTYPE field, even though it is not specified as part of the insert statement:

SQL
 INSERT INTO VSMW0208 (FLD000,FLD002)
 VALUES('12345678','ANTIGUA') ;
END

The following SQL INSERT statement adds an occurrence of SEG003. The INSERT statement references fields FLD003 and FLD004, which identifies the INSERT as an instance of SEG003. The adapter automatically inserts the value 3 in the RECTYPE field, even though it is not specified as part of the insert statement:

SQL
 INSERT INTO VSMW0208 (FLD000,FLD003,FLD004)
 VALUES('12345678','BRAZIL','AMERICA') ;
END


Example: Updating an Occurrence of a Repeating Group in a VSAM KSDS Record With Record Types

The following SQL UPDATE statement updates an occurrence of FLD004 in the record with key '12345678'. The ORDER field is not defined in this Master File. The specific occurrence of FLD004 to update is identified by specifying a unique value of FLD003:

SQL
 UPDATE VSMW0208
 SET FLD004='S AMER'
 WHERE FLD000='12345678' AND FLD003='BRAZIL';
END


Example: Deleting an Occurrence of a Repeating Group in a VSAM KSDS Record With Record Types

The following SQL DELETE statement deletes an occurrence of FLD002 in the record with key '12345678'. The ORDER field is not defined in this Master File. The specific occurrence of FLD002 to delete is identified by specifying its unique value ('ANTIGUA'):

SQL
 DELETE FROM VSMW0208
 WHERE FLD000='12345678' AND FLD002='ANTIGUA';
END


Example: Inserting a Record in a VSAM KSDS Record With a MAPFIELD

Consider the following Master File that describes two OCCURS segments with a variable number of occurrences. The key field is a concatenation of fields FLD000 and FLAG (the MAPFIELD). If the FLAG field contains the value 2, the remainder of the record contains a variable number of occurrences of segment SEG002, which consists of one 8-byte field (FLD002). If the flag field contains the value 3, the remainder of the record consists of a variable number of occurrences of segment SEG003, which consists of two 8-byte fields (FLD003 and FLD004):

FILE=VSMW0209,          SUFFIX=VSAM,$
DATASET='EDAQA.VSMW0200.CLUSTER',   $
 SEGNAME=ROOT,SEGTYPE=S0,$
  GROUP =GRPKEY      ,ALIAS=KEY   ,USAGE=A8,ACTUAL=A8,$
   FIELD=FLD000      ,E00         ,A07     ,A07      ,$
   FIELD=FLAG        ,MAPFIELD    ,A01     ,A01      ,$
 SEGNAME=SEG002,PARENT=ROOT,OCCURS=VARIABLE,SEGTYPE=S0,$
  FIELD =FLD002      ,E02         ,A08     ,A08      ,$
  FIELD =MAPVALUE    ,2           ,A01     ,A01      ,$
 SEGNAME=SEG003,PARENT=ROOT,OCCURS=VARIABLE,SEGTYPE=S0,$
  FIELD =FLD003      ,E03         ,A08     ,A08      ,$
  FIELD =FLD004      ,E04         ,A08     ,A08      ,$
  FIELD =MAPVALUE    ,3           ,A01     ,A01      ,$

The following SQL INSERT statement adds an occurrence of SEG002. The INSERT statement must provide a value for the FLAG field because it is part of the key:

SQL
 INSERT INTO VSMW0209 (FLD000,FLAG,FLD002)
 VALUES('1234567','2','ANTIGUA') ;
END

The following SQL INSERT statement adds an occurrence of SEG003:

SQL
 INSERT INTO VSMW0209 (FLD000,FLAG,FLD003,FLD004)
 VALUES('1234567','3','BRAZIL','AMERICA') ;
END


Example: Updating an Occurrence of a Repeating Field in a VSAM KSDS Record With a MAPFIELD

The following SQL UPDATE statement updates an occurrence of FLD004 in the record with key '12345673'. The ORDER field is not defined in this Master File. The specific occurrence of FLD004 to update is identified by specifying a unique value of FLD003:

SQL
 UPDATE VSMW0209
 SET FLD004='S AMER'
 WHERE GRPKEY='12345673' AND FLD003='BRAZIL';
END


Example: Deleting an Occurrence of a Repeating Field in a VSAM KSDS Record With a MAPFIELD

The following SQL DELETE statement deletes an occurrence of FLD002 in the record with key '12345672'. The ORDER field is not defined in this Master File. The specific occurrence of FLD002 to delete is identified by specifying its unique value ('ANTIGUA'):

SQL
 DELETE FROM VSMW0209
 WHERE GRPKEY='12345672' AND FLD002='ANTIGUA';
END

iWay Software