I have created the following:
CREATE SCHEMA APPLDICT
FOR NONSQL SCHEMA SYSDIRL.IDMSNTWK VERSION 1
And now I can query it using Selects like this:
SELECT * FROM APPLDICT."SA-018";
I can't query records in the thousands like:
SELECT * FROM APPLDICT."FILE-1039";
I get this error:
*+ Status = -5 SQLSTATE = 60002 Messages follow: *+ DB001031 T51475 C-5M321: DB error 1492; Tbl:?, Area:? *+ ErrSet:?, ErrTbl:?, ErrArea:?
I assume this is because I need to create another schema for NONSQL schema for the DDLCAT area. I am not sure how to do that?
Does anyone have details on what to do?
Hi Paul -
There's a couple of things going on with your query against the catalog area. First, the 1492 indicates that "The subschema specified does not contain at least one logical area that maps to a physical area defined in the DMCL when using the dbname specified." (from the Messages manual, vol 1). Your schema definition for the APPLDICT SQL schema s\pecifies DBNAME APPLDICT; so I'm guessing that if you do a DCMT DIS DBTABLE, that the APPLDICT won't include the a segment containing DDLCAT area. That would cause the 1492.
More to the point, in order to use a query like this against a network schema, you need 2 things:
1- the source for a network schema that contains the definitions of the area & all records in the area;
2- a DBName to point it to where the records exist.
In the case of the FILE-1039 record, the definitions are in the SYSTEM DBname, so if you wanted to point an SQL schema definition to the records you'd have to specify DBNAME SYSTEM. However, the true 'catch' here is that we don;t give you source for a schema that contains all of the necessary area, record, and set definitions for the DDLCAT area. IDMSNTWK has some but not all of them. That's why our utilities, like IDMSDBAN, must use schema IDMSCATZ if they want to process the DDLCAT area. However, we don;'t give you the source for IDMSCATZ, only the load module. So you can't define an SQL schema to look at it.
To get the info you're after, you could issue these commands in OCF:
CONNECT TO SYSTEM;
DISPLAY ALL FILES;
Would that approach work for you? I hope this helps,. Cal
Principal Support Engineer, Global Customer Success
The DDLCAT area is actually the SQL catalog, which is used for both SQL table definitions and for physical database definitions, so you can use SQL directly to query it. There is no need to define an SQL schema for NONSQL, you just use the SYSTEM schema. The FILE-1039 record is actually defined as the SYSTEM.FILE table, even when SQL is not "installed".
So, unless I'm missing something, you should be able to do something this:
OCF 19.0 IDMS NO ERRORS 1/19 SYSQA10SELECT SEGMENT, NAME FROM SYSTEM.FILE;*+*+ SEGMENT NAME*+ ------- ----*+ DBCRSQL ACCTSQL*+ DBCRSQL BRCHSQL*+ SYSCAT CATF1*+ SYSCAT CATF2*+ SYSCAT CATF3*+ SQLDEMO EMPLDEMO*+ SQLDEMO INDXDEMO*+ SQLDEMO INFODEMO*+ PROJSEG PROJDEMO*+ USERDB USERDB4*+ USERDB USERDB5*+ USERDB USERDB8*+ USERDB USERDB9*+*+ 13 rows processed
You can see the definition using:
OCF 19.0 IDMS NO ERRORS 1/37 SYSQA10DISPLAY TABLE SYSTEM.FILE;*+ Status = 0 SQLSTATE = 00000*+ CREATE TABLE SYSTEM.FILE*+ DATE CREATED 1990-01-01-00.00.00.000000 BY DOWDA01*+ DATE LAST UPDATED 2013-01-10-18.104.22.1681822 BY DOWDA01*+ ( SEGMENT CHARACTER(8) NOT NULL,*+ NAME CHARACTER(18) NOT NULL,*+ CTIME TIMESTAMP NOT NULL,*+ UTIME TIMESTAMP NOT NULL,*+ CRITTIME TIMESTAMP NOT NULL,*+ CUSER CHARACTER(18) NOT NULL,*+ UUSER CHARACTER(18) NOT NULL,*+ NUMFILEMAPS SMALLINT NOT NULL,*+ BLOCKSIZE INTEGER NOT NULL,*+ DDNAME CHARACTER(8) NOT NULL,*+ ACCESSMETHOD CHARACTER(8) NOT NULL,*+ VMUSERID CHARACTER(8) NOT NULL,*+ VMVIRTADDR CHARACTER(4) NOT NULL,*+ FLAG BINARY(1) NOT NULL,*+ NVSAMSET CHARACTER(18) NOT NULL,*+ DSNAME CHARACTER(54) NOT NULL,*+ DISP CHARACTER(4) NOT NULL,*+ FILLER BINARY(39) NOT NULL*+ )*+ IN SYSCAT.DDLCAT*+ TABLE ID 1039*+ NO DEFAULT INDEX*+ TABLE ID 1039*+ IX-FILE UNIQUE INDEX ON ( NAME, SEGMENT )*+ SEGMENT-FILE CONSTRAINT FOREIGN KEY ( SEGMENT )*+ REFERENCES SYSTEM.SEGMENT ( NAME )*+ REFERENCED BY SYSTEM.DMCLFILE IN CONSTRAINT FILE-DMCLFILE*+ REFERENCED BY SYSTEM.FILEMAP IN CONSTRAINT FILE-FILEMAP*+ USED IN VIEWS:*+ SYSVSYST5.DMCL_FILE*+ SYSVSYST5.VFILE_CACHE_BUF_1*+ ;
I hope this helps.
That is what I was missing. That is better. I wasn't ready to enable SQL access in production, but with this, I don't have to create the additional Schema for NONSQL to do it there.
Our CA IDMS Development Team is currently working on a new feature to limit the resources that are used by an SQL statement. This allows our customers to stop long-running SQL requests before their systems are burdened.
We are looking for customers to join our Customer Validation Program. We have monthly WebEx meetings to discuss all new features and get feedback. As a participate in the program, you get early access to the New Feature APARs. You can apply and test the APARs in your environment, and provide input to us for change. Our next call is coming up October 16th, 2018. Please use this link to register: validate.ca.com
Product Owner, IDMS