IMS Connectivity in an On Demand Environment: A Practical Guide to IMS Connectivity

Chapter 21. IMS Remote Database Services 411

case-sensitive. These keywords are a subset of all SQL keywords. IMS JDBC interface

supports the following functions:

 ALL

 AND

 AS

 ASC

 AVG

 COUNT

 DELETE

 DESC

 DISTINCT

 FROM

 GROUP BY

 INSERT

 INTO

 MAX

 MIN

 OR

 ORDER BY

 SELECT

 SET

 SUM

 UPDATE

 VALUES

 WHERE

21.2.5 IMS Java SQL usage

In this section, we focus on the difference between IMS Java SQL grammar and standard

SQL grammar. We summarize the important SQL usage of the IMS JDBC interface and

provide some SQL samples to help you understand how to use it. For more information about

SQL usage, see IMS Version 9: IMS Java Guide and Reference, SC18-7821.

Sample tables for this section

Figure 21-6 on page 412 shows the sample tables for this section. We assume that SQL

samples are issued against the CustomerTable and OrderTable shown in the figure. The

CustomerTable is made up of the root segments and has the primary key field CustomerNo.

The OrderTable is made up of the dependent segments of the root and has the primary key

field OrderNo and foreign key filed, which comes from the root segment. All segment (table)

names and field (column) names are defined as different Java aliases from the original DBD

definitions.

Important: You cannot use any SQL keywords as Java aliases for PCBs, fields, or

segments. For example, if you have the segment name UNION in the DBD statement, you

have to define explicitly a different Java alias such as UnionTable by using DL/I model

utility function. Note that the SQL UNION function is currently not supported by the IMS

JDBC interface, but any use of SQL keywords for Java aliases are banned in the IMS Java

environment. For a complete list of SQL keywords, see IMS Version 9: IMS Java Guide and

Reference, SC18-7821.

412 IMS Connectivity in an On Demand Environment: A Practical Guide to IMS Connectivity

Figure 21-6 The example table for this section

Qualification rules

In an IMS Java SQL environment, the qualification rules of the column or table are very

important for clarity and performance, and especially to avoid from handling the wrong table.

In this section, we discuss the qualification rules of IMS Java SQL.

Segment qualification

For example, SQL dictates that whenever a field is common between two tables in an SQL

query, the desired field must be table qualified to resolve the ambiguity. Similarly, whenever a

field name is common in any two segments along a hierarchical path, the field might have to

be segment qualified. For example, if a PCB has two tables (segments), CustomerTable and

OrderTable, and both possess a column (field) named ID, any query referencing to the ID field

must be segment qualified (remember, the IMS JDBC interface always joins with these tables

implicitly).

Example 21-1 is incorrect because the ID field is not segment qualified.

Example 21-1 Incorrect example without segment qualification

SELECT ID

FROM CustomerDB.ORDER

WHERE ID='10'

Example 21-2 is correct because the ID field is segment qualified.

Example 21-2 Correct example with segment qualification

SELECT OrderTable.ID

FROM CustomerDB.OrderTable

WHERE CustomerTable.ID ='10'

CustomerTelNoCustomerAddrCustomerNameCustomerNo(Key)

333-3333ChibaGen000003

222-2222New Orleans Trent000002

111-1111CaliforniaJames000001

OrderNameOrderNo(Key)

Piano0000023

Guitar Pedal0000013

Sequencer0000021

Rhythm Machine0000022

Bass Guitar0000031

Guitar Pick0000012

Guitar0000011

CustomerTable

OrderTable

000001

000001

000002

000001

000002

000002

000003

CustomerNo

(Foreign Key)