Ian_Hill

CA Tuesday Tip: (CA IDMS) SQL Query on indexes

Discussion created by Ian_Hill Employee on Feb 5, 2013
Latest reply on Feb 13, 2013 by Ian_Hill
CA IDMS Tuesday Tip by Ian Hill, Principal Support Engineer for February 5, 2013.

For those clients who have the SQL option, the following is a useful tip for getting a quick listing of your network-defined indexes and whether they are system-owned or user-owned.

First, you would need to have an SQL schema defined for your non-SQL schema:

CREATE SCHEMA APPLDICT
FOR NONSQL SCHEMA SYSDIRL.IDMSNTWK VERSION 1
DBNAME APPLDICT
;

Then you can use this query:

[font=Courier New]SET SESSION CURRENT SCHEMA APPLDICT;
*+ Status = 0        SQLSTATE = 00000                         
SELECT S_NAM_010 AS SCHEMA,
S_SER_010 AS VERSION,
SET_NAM_046 AS SET,
SUBSTR('SYSTEMUSER ',6*SIGN(SOR_ID_046-7)+1,6) AS OWNED
FROM "S-010", "SOR-046"
WHERE SET_MODE_046=21
AND "S-SOR" ;
*+                                                            
*+ SCHEMA    VERSION  SET                               OWNED 
*+ ------    -------  ---                               ----- 
*+ EMPSCHM       100  SKILL-EXPERTISE                   USER  
*+ MAHSCHM       100  SKILL-EXPERTISE                   USER  
*+ IJHNET          1  IXRBIG                            SYSTEM
*+ IJHNET          1  IXR1                              SYSTEM
*+ EMPSCHM       100  EMP-NAME-NDX                      SYSTEM
*+ MAHSCHM       100  EMP-NAME-NDX                      SYSTEM
*+ MAHSCH1       100  EMP-NAME-NDX                      SYSTEM
*+ MAHSCH2       100  EMP-NAME-NDX                      SYSTEM
*+ EMPSCHM       100  JOB-TITLE-NDX                     SYSTEM
*+ MAHSCHM       100  JOB-TITLE-NDX                     SYSTEM
*+ IJHSCHM         1  IX-FILELINE                       SYSTEM
*+ EMPSCHM       100  OFFICE-EMPLOYEE                   USER  
*+ MAHSCHM       100  OFFICE-EMPLOYEE                   USER  
*+ EMPSCHM       100  SKILL-NAME-NDX                    SYSTEM
*+ MAHSCHM       100  SKILL-NAME-NDX                    SYSTEM
*+ IJHNET          1  IXR3                              SYSTEM
*+ IJHNET          1  IXCCODE                           SYSTEM
*+ IJHNET          1  RUOX-SET                          USER  
*+                                                            
*+ 23 rows processed [font]

Note the use of the SIGN function and some arithmetic to derive a suitable offset to be used in the SUBSTR function to produce the correct output.

Outcomes