Accessing SQL and Network CA IDMS databases in the same ADS dialog or program
Article ID: 53518
Updated On: 06-10-2024
Products
Issue/Introduction
It is possible to access both SQL and network CA IDMS network databases in the same dialog or program.
This document describes how this is managed, and the detailed steps required to implement this.
Environment
IDMS - all supported releases
Resolution
Both SQL and nonSQL data sources can be accessed within the same dialog or program. In this document, dialogs will be addressed first. In ADSC, when defining the dialog, the 'Database Specifications' panel will ask you to specify a Schema & subschema, along with an access module name. The schema and subschema are for the network database (nonSQL) access. There is no subschema in accessing an SQL database, so whenever you see an option to enter a schema along with a sub-schema, that's a sign that the schema in question is a network schema.
When embedded SQL is coded in a program or dialog, an access module (AM) is required. The Database Specifications panel is where you specify the AM name for this dialog. The default name will be the same as the dialog name. The only time or reason that you would need to change that is this: at run-time, all dialogs in a single application execution thread must use the same access module. So if you have multiple dialogs that link or transfer to one another, or that are included in the same ADSA application and use EXECUTE NEXT FUNCTION to go from one to another, in ADSC you will have to use the same AM name for all of them. Then in the CREATE ACCESS MODULE statement, you will need to specify all of the dialog names to be included. When a dialog (or program) is compiled, all SQL DML is translated to pseudo-code which is stored in a Relational Command Module (RCM). The RCM default name is the same as the dialog (or program). The CREATE ACCESS MODULE statement will optimize the logical statements contained in the RCM and store the optimized physical access path in the AM.
In ADSC there is also a Records and Tables panel. You can name any records here and a copy will be included in the dialog. You can also specify any table by using its qualified name in the format schema_name.table_name.
The above concepts apply equally to programs and to dialogs. If an override is required for a program's AM name, it is specified to the pre-compiler using the AM= <name> pre-compiler parameter. An RCM name can be similarly specified using the RCM= pre-compiler parameter, if letting it default to the same name as the program is not desired. To include a copy of a table layout in the program, the INCLUDE TABLE statement can be used; the pre-compiler will expand this into the appropriate layout for the program.
In terms of specifying the SQL Schema, that is embedded in the SQL DML, or is coded in a SET CURRENT SCHEMA statement. In a program a default schema can also be specified on the SCHEMA= pre-compiler parameter. In a dialog you can access only one network schema, but you can reference as many SQL schemas as you like; there is no restriction on this. In programs, each transaction can access only one network schema but multiple transactions could be started and managed within a single program. As with dialogs, there is no restriction to the number of SQL schemas that can be referenced within the program.
In coding the actual SQL DML, each statement must be enclosed within language-specific delimiters. These delimiters signal the pre-compiler to interpret the encapsulated statement as SQL. For ADS and COBOL these are EXEC SQL and END-EXEC; for PL/I the statement must begin with EXEC SQL and end with a semi-colon. Examples follow:
ADS:
EXEC
SQL 'INSERT INTO DEMOEMPL.DIVISION VALUES ('D01', 'LEGAL', 1234)
END-EXEC.
COBOL:
EXEC
SQL INSERT INTO DEMOEMPL.DIVISION VALUES ('D01', 'LEGAL', 1234)
END-EXEC.
*Note: The period ending this COBOL statement is optional; include it wherever you would normally terminate this COBOL statement.
PL/I:
EXEC SQL INSERT INTO DEMOEMPL.DIVISION VALUES ('D01', 'LEGAL', 1234);
Note that in all languages, it is possible to combine the delimiters and the SQL statement on the same line.
There is one related consideration. When you define an SQL schema, you can specify one that references a network schema. If that's the case, we do not advise simultaneously accessing the same network database using both SQL DML and network DML. It can be done, but doing so can cause locking or currency problems, so we do not recommend this in most cases. To maintain integrity, we create separate transactions (run-units) for SQL and network access, even when they originate from the same dialog. These can be managed separately if you wish (in terms of commits and rollbacks); we recommend that they be managed together, using task-level COMMIT TASK or ROLLBACK TASK commands.
As of R16.0, a new parameter was created called TRANSACTION SHARING. This can be specified for the entire system, a particular task, a batch job step, a SQL Routine, or dynamically at run-time. When transaction sharing is in place, it is possible for separate transactions within the same task to share the same locking structure and recovery unit. When transaction sharing is in place, task-level coordination of the transactions is not required. This feature should be used carefully to ensure the database access between the various transactions within the task is coordinated to yield the desired outcome.
Feedback
