SQL Definitions for CA IDMS Visual DBA

CA IDMS Visual DBA (VDBA) requires that SQL definitions are installed in your mainframe environment to access all of the CA IDMS mainframe objects that VDBA manages. The SQL definitions install the SQL DDL for schemas, views, functions, and table procedures, and must be installed in the SQL catalog for each VDBA dictionary. 
idms19
CA IDMS Visual DBA (VDBA) requires that SQL definitions are installed in your mainframe environment to access all of the CA IDMS mainframe objects that VDBA manages. The SQL definitions install the SQL DDL for schemas, views, functions, and table procedures, and must be installed in the SQL catalog for each VDBA dictionary. 
Installation of the SQL definitions, for use by VDBA on the mainframe, depends heavily on the mainframe environment. Before you install the SQL definitions, it is important to know which version of CA IDMS is installed in your environment. Knowing the release level determines which version of the IDMSVnnX or IDMSRnnX script you use in the instructions that follow.
2
2
About Installing the SQL Definitions
You should be familiar with the following information before installing the SQL definitions:
  • Each dictionary to be controlled must have a catalog that is assocociated with that dictionary. The catalog component of different dictionaries can be shared. If multiple dictionaries share the same catalog, only one dictionary requires that the SQL definitions installed.
  • You may need to increase the catalog size to accommodate the added views and table procedures. If you used the original installation defaults, you may need to increase the catalog size for the CATSYS segment files. You must have at least 1000 pages in your
    DDLCAT
    area for all catalogs that are used with VDBA.
  • CA IDMS Visual DBA Version 18.1 SQL definitions can coexist with definitions of earlier CA IDMS Visual DBA releases.  There is no need to uninstall the earlier SQL definition versions. However, if you prefer to uninstall earlier versions, see Uninstall and Reinstall CA IDMS Visual DBA.
The following two methods can be used for installing the CA IDMS Visual DBA SQL definitions:
Install SQL Definitions Using IDMSBCF
This option submits the SQL DDL statements directly to CA IDMS for processing.
To install CA IDMS Visual DBA SQL definitions using IDMSBCF
  1. Locate the SQL script with the definitions that are needed by VDBA for your environment. The script files are located in the
    IdmsR16X
    ,
    IdmsR17x
    or
    IdmsV18x
    release folders of the CA Visual DBA product folder. The full path of the release folders are typically named as follows:
    • C:\Program Files (x86)\CA\CA IDMS Visual DBA V18x\ IdmsR16x
    • C:\Program Files (x86)\CA\CA IDMS Visual DBA V18x\IdmsR17x
    • C:\Program Files (x86)\CA\CA IDMS Visual DBA V18x\IdmsV18x
  2. Option. To enable Enhanced Object Security and to define the names of the dictionaries that you want VDBA to manage, edit the
    vdb<x>r <x>.sql
     script file as explained in the comments. Optionally, if the name of the
    IDMSDIRL
    dictionary is not
    SYSDIRL, 
    you can change the name. The
    vdb<x>r <x>.sql
     scripts contain three predefined dictionaries:
    SYSTEM
    ,
    APPLDICT
    , and
    SYSDICT
    on three separate lines. You can change the predefined names and can add or remove lines with dictionary names.
  3. Upload the file to the mainframe using a Text File Transfer Protocol.
  4. Execute the Batch Command Facility (
    IDMSBCF
    ), using the uploaded file as input, against all dictionaries that you want VDBA to manage.
For more information about enabling Object Security, see Enable and Disable Enhanced Object Security.
Install SQL Definitions Using CA IDMS Visual DBA
You can use an SQL command console to execute the SQL DDL statements that are required for CA IDMS Visual DBA.
Even though Enhanced Object Security may be disabled, a profile for the installing user must exist on the CA IDMS Central Version (CV). The profile must contain a rule that allows the SQL command processor to execute against the target dictionary in update mode.
For example, if the target dictionary is SYSTEM, the following rule allows execution of the SQL command processor against it:
    Cmd Console SQL/SYSTEM(U)   
To Install CA IDMS Visual DBA SQL Definitions follow these steps
  1. Run CA IDMS Visual DBA. 
  2. Make the
    Node
    window visible and active. 
  3. Expand the
    root node
    ,
    Node
    , to display the available nodes that are the ODBC data sources that use a CA IDMS ODBC driver.
  4. Select the
    Data Source
    for the target dictionary. If an appropriate node (ODBC data source) to your mainframe CV/Dictionary does not exist, perform the following steps:
    1. Add a node using
      Add
       on the
      Node
      menu, or select
      Add
       on the
      Node
      window toolbar.
    2. Define the ODBC data source as a system or user ODBC data source. The dictionary that is specified in the ODBC Data Source definition is the target dictionary for the installation.
  5. Open a command console to your Central Version (CV) by one of the following methods:
    1. From the
      Node
      menu, select
      Cmd Console
    2. Select
      Cmd Console
       on the
      List of Virtual Nodes
       
      window toolbar
    3. Right-click the selected node and select
      Cmd Console
    A dialog prompts you for a userid and password to connect with the CA IDMS CV.
  6. After a successful connection, the
    Cmd Console & Result Cache
    window appears, which includes two active list boxes. 
    1. In the left list box, select the default dictionary of the node or ODBC data source.
    2. In the right list box, verify that the default processor
      SQL
      is listed. Keep this default processor.
  7. From the
    Script
    menu, select 
    Open Script
    to select the installation SQL script that corresponds to your CA IDMS release. The script files are located in the
    IdmsR16X
    ,
    IdmsR17x
    or
    IdmsV18x
    release folders of the CA IDMS Visual DBA product folder. The full path of the release folders are typically named as follows:
    1. C:\Program Files(x86)\CA\CA IDMS Visual DBA V18x\ IdmsR16x
    2. C:\Program Files(x86)\CA\CA IDMS Visual DBA V18x\IdmsR17x
    3. C:\Program Files(x86)\CA\CA IDMS Visual DBA V18x\IdmsV18x
  8. Option. To enable Enhanced Object Security and to define the names of the dictionaries that you want VDBA to manage, edit the
    vdb<x>r <x>.sql
     script as explained in the comments. Optionally, if the name of the 
    IDMSDIRL
     dictionary is not 
    SYSDIRL, 
    you can change the name. The
    vdb<x>r <x>.sql
     scripts contain three predefined dictionaries: 
    SYSTEM
    APPLDICT
    , and 
    SYSDICT
     in three separate lines. You can change the predefined names and can add or remove lines with dictionary names.
  9. Select the
    Trace
    tab to display the execution of the script progress.
  10. Select
    GO
     or press
    F5
    .
    A disabled
    GO
    button in the command console of CA IDMS Visual DBA indicates that either a profile does not exist for the user, or the profile does not allow execution of the SQL command processor against the target dictionary.
Verify Successful Installation
If the script terminates without errors, the installation is successful. Warnings are acceptable.