CA IDMS IUA EIUA

Expand all | Collapse all

SQL query non-sql schema to get at FILE-1039

  • 1.  SQL query non-sql schema to get at FILE-1039

    Posted 01-04-2018 10:28 AM

    I have created the following:

      CREATE SCHEMA APPLDICT

      FOR NONSQL SCHEMA SYSDIRL.IDMSNTWK VERSION 1

      DBNAME APPLDICT;

    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?

     

    -Paul

     



  • 2.  Re: SQL query non-sql schema to get at FILE-1039

    Posted 01-04-2018 12:19 PM

    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  

    Cal Domingue

    Principal Support Engineer, Global Customer Success

    CA Technologies 



  • 3.  Re: SQL query non-sql schema to get at FILE-1039

    Posted 01-04-2018 01:28 PM

    Hi Paul,

    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 SYSQA10
    SELECT 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 SYSQA10
    DISPLAY 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-12.52.59.681822   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.

     

    Dave



  • 4.  Re: SQL query non-sql schema to get at FILE-1039

    Posted 01-04-2018 01:51 PM

    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.

     

    Thanks!!



  • 5.  Re: SQL query non-sql schema to get at FILE-1039

    Posted 10-09-2018 02:43 PM

    Hello All,

    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

     

    Thank you!

    Nakesha Newbury

    Product Owner, IDMS