Using CA IDMS ROWID via JDBC
Article ID: 45118
Updated On:
Products
Issue/Introduction
Introduction:
The CA IDMS ROWID is a virtual column which can be used to uniquely identify, and directly access, any row in a table. It can be used to access both SQL and network databases when using SQL DML.
Questions:
1- Is it a good practice to use ROWID to get the record from VIA table while using JDBC?
2- Will ROWID value get changed when we MODIFY(DML statements) record or it will get changed only when we run DDL statements like in Oracle?
3- 3- Is it a good idea to use ROWID to access an occurrence of a VIA record? For example, where TERMINATION is an owner record stored Calc & ROUTING is a member record stored VIA, is this query advisable?
Query: select term.* from ROUTING rout, TERMINATION term where rout.ROWID=? and "SET-NAME".
Environment:
ROWID is available in any environment running CA IDMS where SQL queries are supported. This could be because the SQL option is installed, of because a client is using the Web Connect option which is available without cost to all IDMS sites.
Answer:
1- Is it a good practice to use ROWID to get the record from VIA table while using JDBC?
A: There are pros & cons to this approach. Using ROWID is the most efficient way to access data - it's like an OBTAIN USING DBKEY network DML statement/. However, the ROWID is not necessarily a permanent value, so there may be overhead in obtaining the correct DBKey to use when specifying a ROWID value. More details about this later.
2- Will ROWID value get changed when a record is modified or will it get changed only when DDL statements are executed (like in Oracle)?
A: The best documentation on this is in the CA IDMS SQL Reference. It states the following:
"The value of ROWID is unique for each row of a base table; however, you cannot consider it to be a table's primary key because its value can change over the lifetime of the database. This could happen, for example, after an UNLOAD/RELOAD operation. ... The ROWID value is not persistent for the life of the database, but it never changes within a transaction or other controlled processes, if the row is not deleted, of course."
3- Is it a good idea to use ROWID to access an occurrence of a VIA record? For example, where TERMINATION is an owner record stored Calc & ROUTING is a member record stored VIA, is this query advisable?
Query: select term.* from ROUTING routing, TERMINATION term where routing.ROWID=? and "SET-NAME".
A: In coding a query like this, it’s important to examine how you obtain the value to use for ROWID. As noted above, this value is not guaranteed to never change. So coding in this way either means you must verify that you have the correct value at run-time, which could require overhead that would reduce the effectiveness of the actual I/O. In this example, though, the use of "SET-NAME" is kind of superfluous unless you are wanting to ensure that the ROWID you provide is actually for a record occurrence that is connected to the owner via the member set. To walk all the members of the VIOA set you could use the set name alone; to obtain a specific record occurrence you would not need to use the set name or the owner, if you have the actual ROWID.
The use of ROWID is radically expanded starting on 19.0 IR2. On that release, virtual foreign keys are implemented using ROWIDs. That means to obtain all the members of a VIA set underneath a specific owner record occurrence you can specify something like this:
select * from EXPERTISE
where FKEY_EMP_EXPERTISE =
(select ROWID
from EMPLOYEE
where EMP_ID = 1001) ;
In this example, EXPERTISE is the member record in the EMP_EXPERTISE set, and EMPLOYEE is the owner. The syntax FKEY_EMP_EXPERTISE is the way to refer to the virtual foreign key in the member EXPERTISE record for the EMP_EXPERTISE set. Coding in this way, without specifying a set name, will likely be more useful in a JDBC environment, where the use of SET-NAME as a join criteria may not be supported. The use of SET-NAME is a CA IDMS extension to the ANSI SQL standard.
Additional Information:
The use of ROWID as virtual foreign keys is documented in the 19.0.02 SQL Reference and in the 19.0.02 Release Notes.
Environment
Component:
Feedback
