Maintaining IMS Data Sources (DBCTL)

In this section:

The Adapter for IMS supports SQL update commands to IMS data sources without the use of remote procedures. For example, UPDATE, INSERT, and DELETE will be translated into equivalent IMS DL/1 calls.


Top of page

x
General Guidelines for Maintaining IMS Data Sources

How to:

Reference:

The Structured Query Language (SQL) is intended to be used for access to relational tables, while IMS databases are hierarchical structures. The Adapter for IMS is designed to balance both, so that IMS data structures are treated as if they were relational tables. As this is not always possible, certain rules must be followed.

The adapter is designed to work against a single IMS segment in one path of the IMS database as the target of the SQL Data Manipulation Language (DML) statement. SQL set orientated behavior (multiple updates with one SQL statement) is not supported.

The WHERE clause of the SQL statement must contain all the key fields in the path to the target segment. If a segment does not have a key, at least one field from that segment must be specified in the WHERE clause. Any segment referenced in the SQL query must, using the WHERE clause, be a unique occurrence of that segment. For segments with unique keys, this is guaranteed by IMS, but for segments that have non-unique or no keys, additional fields must be specified in the WHERE clause to uniquely identify the segment. If additional fields are included in the WHERE clause for any segment, including uniquely keyed segments, they will be used in conjunction with the key, to qualify the segment.



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

General Rules

The following rules apply to SQL INSERT syntax:

Unique Keyed Segment

For a segment with a unique key, the keyfield value is used to insert the target segment. If any additional fieldname=value pairs are supplied for a segment in the path, they are used to qualify that path segment.

Any fields not supplied for the target default to type.

Non-unique Keyed Segment

Non-keyed Segment



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

General Rules

The following rules apply to SQL DELETE syntax:

Unique Keyed Segments

For a segment with a unique key, the keyfield value is used to delete the target segment. If you supply any additional fieldname=value pairs, they will be used to qualify the deletion.

Non-unique Keyed Segments

For a segment with a non-unique key, you must supply the keyfield. If the WHERE condition(s) for this type of segment, in the path or target, do not identify a unique occurrence, the transaction will be rejected.

Non-keyed Segments

For a segment with no key, you must supply at least one fieldname=value pair. If the WHERE condition(s) for this type of segment, in the path or target, do not identify a unique occurrence, the transaction will be rejected. The use of any IMS search fields for this type of segment is highly recommended for efficiency.



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

General Rules

The following rules apply to SQL UPDATE syntax:

Unique Keyed Segments

For a segment with a unique key, the keyfield value and any additional fieldname=value pairs are used to qualify the target segment for update.

Non-unique Keyed Segments

For a segment with a non-unique key, you must supply the keyfield. If the WHERE condition(s) for this type of segment do not identify a unique occurrence, the transaction will be rejected. If the set values would result in the updated segment matching an existing segment, the transaction will be rejected.

Non-keyed Segments

For a segment with no key, you must supply at least one fieldname=value pair. If the WHERE condition(s) for this type of segment do not identify a unique occurrence, the transaction will be rejected. The use of any IMS search fields for this type of segment is highly recommended for efficiency. If the set values would result in the updated segment matching an existing segment, the transaction will be rejected.



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

x

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 Adapters on the menu bar, clicking a configured adapter, and choosing Change Settings from the right-click 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 IMS Data Sources

The following processing is not supported:


Top of page

x
Commit and Rollback Processing in IMS

IMS data sources can participate in transactions with other types of data sources. These transactions can be coordinated automatically by the XA Transaction Management feature or can be controlled by the user application program.

Commit and Rollback Processing Without XA Transaction Management

When the Adapter for IMS is used for any modification action, it automatically issues a COMMIT after each INSERT, UPDATE, or DELETE call. If the IMS segment processing is part of a logical unit of work (LUW) that involves any relational data sources, the relational transactions should not be committed until the single IMS process has completed successfully. No user rollback of the IMS process is supported. If the IMS process fails, a ROLLBACK is issued by the adapter, and the user application must issue a ROLLBACK of the relational transactions that are part of the LUW.

Commit and Rollback Processing With XA Transaction Management

Although IMS data sources are non-XA compliant, an IMS data source can participate in a two-phase commit handled by the Transaction Coordinator component of the XA Transaction Management feature if:

For related information, see XA Transaction Management.


Top of page

x
Metadata Considerations for Maintaining IMS Data Sources

How to:

Reference:

No changes to the Master File are required for maintaining IMS data sources.



x
Syntax: How to Enable Write Access for IMS

To enable Write access for an IMS data source, specify the WRITE=YES attribute in the Access File.

PSB=psbname, WRITE={YES|NO}

where:

psbname

Is the name of the FOCPSB library member to use. This name must be identical to the name of the actual PSB that IMS will access. If the member does not exist, the following message is generated:

(EDA4261) FOCPSB MEMBER NOT FOUND: psbname
YES

Allows SQL update for this database.

NO

Does not allow SQL update for this database. This value is the default.



x
Reference: Sample IMS Master File for Maintenance Examples

The following database and segment values will be used in all examples.

FILENAME=IMSUPDDB,SUFFIX=IMS,$
$
$SEGA has a unique KEY
SEGNAME=SEGA,SEGTYPE=S0,$
  FIELDNAME=FA1    ,ALIAS=FA1KEY.KEY    ,FORMAT=A5,ACTUAL=A5,$
  FIELDNAME=FA2    ,ALIAS=              ,FORMAT=A5,ACTUAL=A5,$
  FIELDNAME=FA3    ,ALIAS=              ,FORMAT=A5,ACTUAL=A5,$
$
$SEGB has a unique KEY
SEGNAME=SEGB,SEGTYPE=S0,PARENT=SEGA,$
  FIELDNAME=FB1    ,ALIAS=FB1KEY.KEY    ,FORMAT=A5,ACTUAL=A5,$
  FIELDNAME=FB2    ,ALIAS=              ,FORMAT=A5,ACTUAL=A5,$
  FIELDNAME=FB3    ,ALIAS=              ,FORMAT=A5,ACTUAL=A5,$
$
$SEGC has a unique KEY
SEGNAME=SEGC,SEGTYPE=S0,PARENT=SEGA,$
  FIELDNAME=FC1    ,ALIAS=FC1KEY.KEY    ,FORMAT=A5,ACTUAL=A5,$
  FIELDNAME=FC2    ,ALIAS=              ,FORMAT=A5,ACTUAL=A5,$
  FIELDNAME=FC3    ,ALIAS=              ,FORMAT=A5,ACTUAL=A5,$
$
$SEGD has a non-unique KEY
SEGNAME=SEGD,SEGTYPE=S0,PARENT=SEGC,$
  FIELDNAME=FD1    ,ALIAS=FD1KEY.KEY    ,FORMAT=A5,ACTUAL=A5,$
  FIELDNAME=FD2    ,ALIAS=              ,FORMAT=A5,ACTUAL=A5,$
  FIELDNAME=FD3    ,ALIAS=              ,FORMAT=A5,ACTUAL=A5,$
SEGNAME=SEGB,SEGTYPE=S0,PARENT=SEGA,$
  FIELDNAME=FB1    ,ALIAS=FB1KEY.KEY    ,FORMAT=A5,ACTUAL=A5,$
  FIELDNAME=FB2    ,ALIAS=              ,FORMAT=A5,ACTUAL=A5,$
  FIELDNAME=FB3    ,ALIAS=              ,FORMAT=A5,ACTUAL=A5,$
$
$SEGC has a unique KEY
SEGNAME=SEGC,SEGTYPE=S0,PARENT=SEGA,$
  FIELDNAME=FC1    ,ALIAS=FC1KEY.KEY    ,FORMAT=A5,ACTUAL=A5,$
  FIELDNAME=FC2    ,ALIAS=              ,FORMAT=A5,ACTUAL=A5,$
  FIELDNAME=FC3    ,ALIAS=              ,FORMAT=A5,ACTUAL=A5,$
$
$SEGD has a non-unique KEY
SEGNAME=SEGD,SEGTYPE=S0,PARENT=SEGC,$
  FIELDNAME=FD1    ,ALIAS=FD1KEY.KEY    ,FORMAT=A5,ACTUAL=A5,$
  FIELDNAME=FD2    ,ALIAS=              ,FORMAT=A5,ACTUAL=A5,$
  FIELDNAME=FD3    ,ALIAS=              ,FORMAT=A5,ACTUAL=A5,$


Example: Inserting a Root Segment in IMS

The IMS database has one record. The following INSERT statement inserts a second root segment:

INSERT INTO IMSUPDDB (FA1,FA2,FA3) VALUES('A2','AAAAA','BBBBB')


Example: Inserting a Child Segment in IMS

The following INSERT statement inserts a new SEGB segment:

INSERT INTO IMSUPDDB (FA1,FB1,FB2,FB3) 
VALUES('A2','A2B1','AAAAA','DDDDD')


Example: Inserting Three Segments Under a Root Segment in IMS

The following INSERT statements insert three SEGD segments for root A1.

INSERT INTO IMSUPDDB (FA1,FC1,FD1,FD2,FD3)
   VALUES('A1','A1C1','C1D1','D1111','D1111')
INSERT INTO IMSUPDDB (FA1,FC1,FD1,FD2,FD3)
   VALUES('A1','A1C1','C1D1','D2222','D2222')
INSERT INTO IMSUPDDB (FA1,FC1,FD1,FD2,FD3)
   VALUES('A1','A1C1','C1D2','D1111','D1111')


Example: Inserting Two Segments on a Single Path of the Data Source in IMS

The following INSERT statement inserts a new SEGC as well as a new SEGD. Default values of blanks or zeros will be used for SEGC for any column not in the values list.

INSERT INTO IMSUPDDB (FA1,FC1,FD1,FD2,FD3)
   VALUES('A1','A1C2','C2D2','D2111','D2111')


Example: Deleting a Root and Dependent Segments in IMS

The following DELETE statement deletes the root segment A2 and all dependents.

DELETE FROM IMSUPDDB WHERE FA1 = 'A2'


WebFOCUS