A Note from us here: IDMS/SQL News 1.1 issue was published in Feb 1992 and was distributed among some of the Scandinavian client base. For the irst time, this is made available in the Web now. The copies were all lost. A warm thanks to Bjørn & Harald for making this available to us in this form.

First time ever on the Web! From the archives of 1992!

CA-IDMS Release 12.0 Available

The The IDMS Release 12.0 ESP is being distributed worldwide and is shipped
to well over 100 sites in the USA and Europe. Earlier, 17 beta sites
have done intensive testing on various features most notably the SQL
Extended Architecture . CA-IDMS DB/DC R elease 12 is delivered on
MVS/XA/ESA now. VSE, VM/XA and Siemens Versions are under beta testing
at the moment.

SQL Questions & Answers

- from an IDMS perspective

Q. What is the CA-IDMS/DB Release 12.0 SQL Option?

CA-IDMS/DB Release 12.0 SQL Option fully supports relational processing
by implementing the SQL Data Definition Language (DDL) for database
definition, SQL Data Manipulation Language (DML) for database access,
and SQL Data Control Language (DCL) for database security
administration. The important thing to remember about SQL is that it
implements relational processing and therefore all data is defined and
accessed as tables made up of rows and columns. Operations on those
tables (through DML) y ield other tables. There is no concept in SQL of
database navigation or currency as we know it in CA-IDMS today. All
data access is done by identifying the data desired and the operations
to be performed on taht data, not by where it is or how to f ind it.
The CA-IDMS/DB SQL Option implements ANSI standard SQL as defined by
ANSI 89 and FIPS, with many extensions to support most DB2 syntax and
other CA extensions to provide support for performance oriented
production applications.

Q. How many Relational Command Modules are created per pre-compiled
program? What is its name?

CA-IDMS will create one RCM per program and it is stored on the
dictionary DDLDCLOD area. The RCM name is the same as the program name
or may be specified through a pre-compiler parameter.

Q. Is Access Module creation a separate step from program compilation
or CA-ADS dialog creation?

Yes. AM creation/modification is invoked by the CREATE or ALTER
ACCESS MODULE COMMAND in the command facility (IDMSBCF in batch, OCF
online).online).

Q. Can multiple RCMs be combined into a single Access Module? Why
would you want to do that?

Yes. RCMs for multiple COBOL programs and CA-ADS dialogs may be
combined into a single application for application convenience. For
example, if an SQL application links or transfers to several
programs/dialogs in the same transaction, all the RCMs for these
programs must be in a single AM.

Q. Are there tuning options and user control for creation of SQL RCMs
and AMs?

RCMs cannot be tuned; they differ based only on syntactical differences.
Access modules are based on a combination of the SQL request, physical
database structures, and statistics. There are numerous tuning options
available for database definition such as CALC storage, indexing,
clustered storage, linked referential constraints, etc. These have the
greatest impact on AM creation. Statistics can be updated using the
UPDATE STATISTICS command, and also simulated (changed) using ESTIMATED
ROWS . The EXPLAIN provides details about the access path chosen for
an RCM(s) in an AM, and can be used to compare different syntax choices
and different tuning options.

Q. Is there security or other methods to restrict doing dynamic SQL from
a program?

For static SQL, the user is required to have execute authority for the
access module in order to run the program, but does not require access
security for each table referenced in the access module. For dynamic
SQL on the other hand, the user must h ave specific access security to
each table referenced in the SQL statement. So it is possible to set
administer security such that users can reference tables only via static
(pre-compiled) SQL.
If dynamic SQL is permitted, another way to control usage is via
resource limits.

Q. Is there a method to restrict the use of SQL?
Security can be specified on resources (tables, schemas, etc.) and
activities on those resources (UPDATE, ACCESS, etc.). Resource limits
may also be used to limit SQL activity in general. IDMS uses standard
SQL Grant/Revoke for security as well as interface to external packages.
Q. What is the result of calculations on columns which contain null
values?

For arithmetic operations, the answer will be null. For functions
involving calculations, the null value row will be ignored (for example
the average function will only average non-null values). If this is a
problem, either define columns that will participate in calculations to
be NOT NULL, or check the existence of valid numeric data a the column
before performing the calculation.

Q. How do you use SQL to update a member record of a network set in a
non-SQL defined database?

Assume that the structure is record type TERRITORY owns record type
CUSTOMER in set TERR-CUST. How to update all the customers in territory
A to status active?

Use a subquery to update all the customers that are found by selecting
customer from territory A as follows:

UPDATE CUSTOMER SET STATUS= 'ACTIVE'
WHERE CUSTOMER_NUMBER IN
(SELECT
CUSTOMER_NUMBER FROM CUSTOMER, TERRITORY
WHERE TERRITORY_CODE = 'A' AND "TERR-CUST");

Q. How does CA-IDMS SQL process a many to many relationship in a non-
SQL defined database?

This can be done. If foreign keys corresponding to the owner record
occurrence are embedded in the member records it can be done directly
using matching key values. If embedded foreign keys are not present, CA
Extended SQL supports using the netw ork set name as equijoin criteria
in the WHERE clause.

Q. Using CA-IDMS SQL, how do you retrieve a specific record in a
multi-member sets of a non-SQL defined database?

Just name the table (record type) you want in the SQL statement.

Q. Can you use CA-IDMS SQL to retrieve a record with a DBkey embedded
from another record in a non-SQL defined database?
No. DBkeys are not visible to SQL. SQL operates at the logical data
level only. For years (long before SQL) we have recommended against
embedding DBkeys because storing physical addresses as database
information severly limits your processing fle xibility.

Q. How do you process a bill of materials structure with CA-IDMS SQL?

This can be done with multiple cursors declared in the application
program. Two cursors are required; more can be used for performance and
convenience. There is a complete example of this in the CA-IDMS SQL
Programming Guide.

Q. When doing an area sweep of a member record in a non-SQL defined
database, how does SQL handle both MA and OM set options?

The SQL optimizing compiler determines how to get the data records based
on the SQL request. It will pick the most cost effective path to the
data including using CALC keys, indexes, and sets if they are
appropriate based on the SQL statement.
Q. When processing sorted network sets, how does SQL support the
concept of starting the set search from current member records of
set vs. always getting positioned at owner?

Again, in SQL, currency is entirely internal, and the application
program does not "start the search." You only specify what you want,
not how to get it. SQL will get the required data no matter what order
it is stored in and sort it if necessary to fulfill the request.
However, if the data is stored in the same order that the SQL statement
requests it then you will get much better performance.

Q. How can you connect and disconnect records in network sets using
IDMS/SQL?

There is no concept of connect and disconnect in SQL. However, if a
table references another table and the foreign key of the referencing
table's row is null (unknown value), then this row is analagous to "not
connected" to the referenced table, in a referential integrity sense.
In other words, if you were join the two tables on this key, the
referencing table row with the null value would not match in the join
referencing table row with the null value would not match in the join
criteria and not be accessed.

Q. What does an SQL command generate in the pre processor for
COBOL/CA-ADS?

Like native DML or LRF commands, the SQL statement is commented out and
replaced with a call to IDMSSQL passing a parameter list.

Q. What is the SQL equivalent of the navigational DML verbs ERASE,
ERASE ALL, ERASE SELECTIVE, ERASE PERMANENT?

SQL DELETE. DELETE is implemented as RESTRICT; that is there is no
cascading supported at this time. This means that deletes must be done "from the bottom up" - to
referencing rows/member record occurrences before referenced rows/owner
record occurrences.

Q. For erase and modify, does SQL automatically get current of run-
unit?

For DELETE and UPDATE you specify the row to DELETE/UPDATE directly in
the WHERE clause without having to previously access it. In an
application program where you are SELECTing data using a cursor, you may
also DELETE or UPDATE the current row of th e cursor using special
syntax:

DELETE FROM TAB1 WHERE CURRENT OF CURSOR1; or
UPDATE TAB1 SET COL1 = "A" WHERE CURRENT OF CURSOR;

Q. How do you save and restore currency for paging data in SQL? (i.e.
does SQL accommodate the accept command?)
CA Extended SQL supports maintaining cursor positions across a pseudo-
converse by the SUSPEND and RESUME session statements in COBOL and CA-
ADS. This, alongwith COMMIT CONTINUE, gives complete pseudo-converse
support.

Q. If a customer does not purchase the CA-IDMS 12.0 SQL Option, what
will happen if they try to process SQL statements?

Even without the SQL Options, customers will still use SQL GRANT and
REVOKE to administer CA-IDMS security. However, all other SQL
statements will receive a syntax error when processed by the SQL parser.

Q. What is the system resource cost of referential integrity
maintenance? What about domain integrity?

Most SQL referential integrities are equivalent to network sets in a
non-SQL defined database. In SQL there is less chance for program error
because the IDMSDBMS is enforcing the integrity. Referential integrity
maintenance is "free" relative to t he same activity in a non-SQL
defined database. The referential integrity requires the same system
resource expenditure as maintaining a network set (one database call)
because in the SQL database the referenced table must have an index or
be stored CALC on the referenced key. IDMSDBMS also enforces entity
uniqueness. The logical database engine (IDMSHLDB) manages domain
integrity according to the valid values defined in the CHECK clause at
table definition.

Q. How do you update the statistics tracked for an SQL table and
database? Does UPDATE STATISTICS force an automatic regeneration
of affected access modules?
UPDATE STATISTICS for a table is to be issued as a utility statement.
This updates the statistics stored in the dictionary but does not force
an automatic regeneration. The DBA can decide whether AM regeneration
is appropriate and initiate it manua lly. This is because while
contributing valuable information to the access path strategy
evaluation, a change in statistics will not usually impact the current
strategy unless the counts have changed significantly - for example in
going from a test to production database, or during initial system
implementation where the database is growing dramatically.
Q. What are the performance implications for DML vs. SQL for online
trans vs. batch (I/O vs CPU).

In our testing we are pleased with what we see so far.
We expect that for the same unit of work, DML vs. SQL performance will
be comparable and even better in many cases.
Keep in mind that in DML, performance is dependent on application
programmer's navigation logic, while in SQL the optimization component
of the Access Module Compiler generates the database access strategy
based on evaluation of all possible access p aths, physical structures,
tuning options present, and statistics about the database.

We believe it is highly efficient and it will generate the same good
database access for rookie programmers as for veterans. SQL is powerful
in that it allows you to request data no matter what its structure. To
that extent, one can create very expe nsive requests in SQL. But that
is why we integrated SQL support into the CA-IDMS database engine,
thereby making all the advanced tuning options and database processing
techniques developed over the last 20 years available to SQL as well as
navigat ional DML.

Q. Can SQL and DML be mixed in the same program? Can both be used to
navigate a non-SQL defined database?

Yes. In fact, with an SQL statement, you can access both SQL defined
and non-SQL defined databases in the same statement. With SQL you can
also access non-SQL records from two different schemas in the same
statement, thereby overcoming a longstan ding DML limitation.

Q.Does CA have recommendations on when SQL should not be used, including
database ACCEPT db-statistics?

SQL and DML can be mixed in a program and application to suit your
processing needs. We do not have specific recommendations at this time.
We believe CA-IDMS 12.0 offers the best of both worlds by combining
navigational and SQL access in the same d atabase management system and
same applications. This is an interesting topic and surely there will
be a lot more information published
about this by customers and CA as we get experience with release 12.0 in
the real production world.

Q. Is any conversion from 10.2 to 12.0 required for SQL?

There is a dictionary migration, DMCL conversion, security conversion
and other miscellaneous conversions required to run CA-IDMS Release 12.0
and receive the full benefit of this release. These are required
whether or not you elect to use SQL. To successfully use SQL with non-
SQL defined databases there are some structural considerations which are
detailed in the IDMS Expanded Page Vol 2.1. Also see IDMS Release 12.0
Conversion Notebook.

Note: This is an edited version of the answers given by IDMS Technical
Support in Westwood, to the questions from IDMS Customers.

19 Utilities disappear in R12!

There is no reason to panic. In fact, what has happened is that IDMS is
providing a powerful single utility interface to replace 18 existing
10.2 Utilities+ DMCL Compiler..
IDMSBCF is the powerful newcomer which will accept equivalent commands
to do the same functions. IDMSBCF also supports complete SQL DDL and
DML.

Here is the list of 10.2 Utilities and R12 commands.

IDMSAJNL Archive Journal
IDMSDBLU Fastload, Reload
IDMSDUMP Backup, Print Space
IDMSINIT Format
IDMSJFIX Print Journal,Fix Archive
IDMSLDEL Cleanup
IDMSPCON Restructure Connect
IDMSPFIX Fix Page, Print, Unlock
IDMSPTRE Print Index
IDMSRBCK Rollback
IDMSRFWD Rollforward
IDMSRSTR RESTORE
IDMSRSTU Restructure Segment
IDMSTBLU Maintain Index
IDMSUNLD Unload
IDMSXPAG Exapand Page
RHDCPRLG Archive Log, Print Log
IDMSRNWK, IDMSDMCL Not Needed anymore

Release 12.0 Documentation

Here is a list of important Release 12.0 Manuals. Refer to CA
Bibliography for a complete list.

R005/&12FSB Features Summary Guide
R005/&12CMB Command facility(OCF)
R005/&12MIB Conversion Notebook Rel12
R005/&12DAB IDMS/DB database admin.
R005/&12DDB IDMS/DB database design
R005/&12PGB SQL Programming Guide
R005/&12RGB SQL Reference Guide
R005/&12SEB Security administration
R005H&12NRB Data dict.ntwk. Reference
R005/&12AGB DB/DC DML Ref.Assembler
R005/&12CRB DB/DC DML Ref. cobol
R005/&12PLB DB/DC DML Ref./PL1i
R005P&12ODB DC/UCF online debugger
EM05/&12EMB Error codes and messages
R005H&12RGB IDD DDDL Reference
R105Ø#12RGB ADS Reference Guide
R005Ø#12UGB ADS User Guide R005/&12OMB Mapping facility
R005/&12P1B Navigational DML Prog
R0056&12RGB Culprit Reference Guide
EM056&12MCB Culprit message and codes
R0056&12UGB Culprit user Guide
R020NØ12SAB ICMS System administr.
R005Ø-12DOB IDMS/DDS design & Oper.
R020B&12RGB OLQ Reference
R020B&12UGB OLQ user's Guide
R005Z&12SAB Perf. monitor System adm.
R005Z&12UGB Perf. monitor user Guide
R0051(12UGB Presspack user Guide
R005/&12RPB Reports
R005P&12TOB Sys task & oper Commands
R005/&12SGB System generation
R005P&12SOB System operations
R005/&12UMB Utilities

IDMS/PC 2.4 Lan Version

IDMS/PC is a complete copy of 10.2 IDMS of today. Database, ADS Dialog
source, Maps, COBOL (Batch,DC) etc are 100% portable to mainframe.
Utility is provided for upload/download to/from mainframe. IDMS/PC can
be used for development of applications
for mainframe or for running production applications in PC/LAN.
Customers are already running very large systems with complete journal
/rollback capabilities. Ask for a brochure .

Back to the Main Page

This page at http://www.geocities.com/idmssql/idms11.htm

IDMS/SQL is published on behalf of IDMS WatchDog Group, Helsinki-Oslo for free circulation among IDMS Users Worldwide. IDMS/SQL News is not a CA publication. CA-IDMS/DB, CA-IDMS/DC and CA-ADS are registered trademarks of Computer Associates International Inc. CICS, IMS-DB/DC and DB2 are registered trademarks of IBM Corporation. Technical examples are only guidelines and modification might be required in certain situations and operating systems. Opinion expressed are those of the authors and do not represent the views of IDMS clients or related vendors. © Permission is hereby granted to use or reproduce the information, only to IDMS Customers and Consultants, provided the material is distributed free of charge.