Managing and Monitoring the DVM server
DVM for z/OS supports four separate interfaces:
- A traditional interface in ISPF
- A graphical user interface called 'DVM Studio'
- A batch interface
- An API interface to retrieve status information about DVM
Accessing the ISPF interface
The purpose of this interface is basically to monitor and administer the main functions of the DVM Server by using the ISPF interface through TSO commands. TSO command can also be incorporated into the ISPF menu.
Example command to access the ISPF interface:
EX hlq.SAVZEXEC(AVZ)' 'SUB(dvm subsys)'
'hlq' is the high-level qualifier of the DVM libraries of your DVM subsystem and 'dvm subsys' is the name of your DVM subsystem. The command option allows access to the ISPF interface specific to the DVM server without being integrated into any menu and only users that know the exact command are allowed access.
ISPF a\ccess using a menu option
A better option is to include access to the ISPF interface into a menu option on one of the ISPF menu panels. This limits access to user ids that are allowed to use certain logon procedures. When creating a menu option in ISPF that executes a command, you can refer to the MVS manuals or consult your z/OS system programmer.
The DVM server ISPF panel is shown in Figure 1.
Figure 1 - Main ISPF menu of DVM
Switching DVM servers Throughout the main panel and the subsidiary panels, you have the possibility to switch servers for some options. In the main panel, you can do that for the Interface Facilities and the Server Trace. You switch server by simply entering the correct server name in the input field and then enter the option of your choice. This only works if the DVM Server you want to switch to is actively running, otherwise, you will get an error message.
Sections on the main panel
The main panel is divided into two sections:
- Interface Facilities
- Administration
The Interface Facilities and the Administration options are described shortly in the Administration Guide, chapter 2.
Interface facilities - VSAM/Sequential examples
The options in the upper section give you access to the features for the various data sources. For example, the VSAM/Sequential option (option 7) allows you to create and maintain data mappings for VSAM and sequential files.
Server Trace
The server trace option (B) on the main panel, gives you access to the log of the DVM server. It is shown in Figure 2.
Figure 2 - The DVM Server trace
It shows activities like logon/logoff of users, execution of queries, and their results. It also shows errors and can be helpful in problem determination.
Modifying the display of the Server Trace
The Server Trace display is regulated by a profile. You can change this profile by typing the word 'profile' in the command line and pressing it
. The screen of Figure 3 is shown (if the Y/N switches are not shown, press and go back in again with 'profile' and ). Here you can select the trace items you want to see by setting the Y/N switches, or by selecting a certain job name of user id, and so on. Figure 3 - The profile screen of the Server Trace
The data mapping module is where you can create and maintain the data mappings for the various data sources.
The monitor module (option F) gives you insight into what is happening in your DVM server. When you open option F on the main panel, the panel from Figure 4 is shown.
Figure 4 - The main menu of the Monitoring function of DVM
The first option (Interval Activity) shows CPU utilization of the DVM Server at 15-minute intervals. The information is spread over three panels, which can be shown by using PF11 (shift right) and PF10 (shift left). The only fixed column on the panels is the timestamp, which indicates the start time of each interval. Maybe the most interesting part is on the utmost right panel, which is demonstrated in Figure 5.
Figure 5 - The resume per interval of 15 mins
Figure 5 also shows how much CPU time during the interval qualified for off-loading to zIIP engines (column 'zIIP Qual CPU Time'). But it also shows how much CPU time during the interval was off-loaded to zIIP engines (column 'zIIP CPU Time'). These two columns provide to the degree the DVM Server is using zIIP engines, or whether you need more zIIP capacity. The Interval Activity also shows how many users were connected during the interval (column 'User Count') and how many SQL queries were executed (column 'SQL Count').
The second option (Remote Users) gives you the list of all user ids that are connected to the DVM Server at a point in time. See figure 6. It gives you the possibility to review which activities a user has executed (line-command T), gets details about the CPU usage by the user (line-command U), cancels a thread running on the DVM server (line-command C), or even disconnect the user altogether from the DVM server (line-command K). Other options on the Monitor-panel give you an overview of storage usage (option 4 - Storage Monitor), active tasks (option 5 - Task Monitor), and statistics about the DVM Server (option 6 - Statistics).
Creating virtual tables in the ISPF interface
In this section, we look at how to create a virtual table for a flat file when using the ISPF interface. Create the source library In DVM, the source library is the library where your copybooks are stored. If you want to create a virtual table on the non-relational data source, you need a copybook. We open option D (Data Mapping) > option S (Source Library Management) > option 1 (Create Source Library Map). We fill out the following fields:
- Name - the short name for our source library within DVM
- Description - an optional description of our copybook
- Data Set Name - the physical name of the library in z/OS. Make sure to put the name between commas
Then, press <ENTER>, and any message (failure or success) show in the upper right when done. See Figure 6. When done, we go back to the main screen by pressing PF3 three times.
Figure 6 - The source library definition panel
Entering option 7 (VSAM/Sequential) > option 2 (Extract Seq). On the first screen (see figure 8), we first fill out the following information and then press, as shown in Figure 7.
- Source Library Name is the name of our copybook library. Make sure to mention the correct member within the library
- The Start Field is the field within the data structure of your flat file where you want the data mapping to start. The mapping takes into consideration the whole data structure within the copybook until the next field at the same level as the start field unless you define another end field
- Map Name is an optional field. If you leave it blank, DVM assigns the name of the first field in the mapping as the map name.
Figure 7 - The first panel of the mapping definition
On the next screen (see Figure 8), you only fill in the following field and then press. Upon successful execution, the system returns to the previous screen with a message in the upper right of the screen. Entering DSN (R): the physical file name of the data source in z/OS (never between commas on this screen!)
Figure 8 - The second panel of the mapping definition
Return one screen by pressing PF3 and select option '5.' When pressing PF3, the list of maps in the metadata catalog is refreshed. When done, the message 'Refresh Successful' appears in the upper right. Pressing PF3 again returns to the main menu.
To test your virtual table, use option 8 (DSSPUFI). This option is similar to SPUFI. On the screen, you fill in the following information in Figure 9):
- 'Change Options?' - If you want to review or change the defaults for the execution of your query, you can put this option to 'Y'.
If not, you can put it to 'N'.
- Input Data Set - The name of a partitioned data set with a member name. The data set should exist. If the member does not exist, DVM creates it. You can use any standard 80-byte record PDS.
Enter '.'
You hit. If you put the 'Change Options?' field to 'Y', the screen with defaults displays. Review and change anything you deem necessary and hit again. If you put the 'Change Options?' field to 'No, you only need to hit once. Figure 9 - The input screen for testing virtual tables
The member you indicated on the previous screen opens for edit. You can insert your query or queries here (close each query with a semicolon). To run immediately, you can enter on the command line three semicolons and hit. A view-only temporary data set appears with the query results, as shown in Figure 10.
Figure 10 - Output of testing a virtual table in ISPF
Showing the new virtual table in the ISPF interface
The new virtual table can be visualized in the ISPF interface. Enter option 7 (VSAM/Sequential) > option 3 (Map Display). The list of existing data mappings is shown in Figure 11.
Figure 11 - The list of existing virtual tables
If you enter an 'X' in front of the map, the contents of the map display, as shown in Figure 12.
Figure 12 - Showing the contents of a virtual table
Displaying the newly created virtual table in DVM Studio
Initially, when you launch the DVM Studio the newly created virtual table doesn't appear in the list of provisioned data objects and is caused by the fact that any change made directly to the metadata in the DVM Server is not automatically propagated to DVM Studio. So, we right-click the 'Virtual Tables' > 'Refresh'. Then, our new virtual table shows up in the list.
The ISPF interface and Parallel Sysplex
The ISPF interface allows you to switch between DVM servers that are running on the same LPAR. However, it is not possible to switch between DVM servers that are running on different LPAR's. Even when these LPAR's are within a Parallel Sysplex and the DVM servers are running in Data Sharing mode.
The DVM Studio allows the user to create and manipulate virtual tables and views, create queries and generate code to embed queries into applications. Once installed, getting started with DVM Studio is really a matter of a few clicks. The interface is invoked from the desktop by clicking the DVM Studio icon. If the DVM Studio before and the DVM Server you used the previous time are up and running, the DVM Studio automatically connects to it. Upon your first use of the DVM Studio, the Server needs to be defined. Click the 'Set Server ...' button, or the 'Set Current Server' button, as shown in Figure 13. A dialog screen displays and you enter your credentials.
Figure 13 - The 'Set Server' button
The most useful widget in the DVM Studio is the Navigator, which is the default panel when starting the DVM studio. This panel is used to create and manipulate virtual tables, virtual views, API's and target systems connect source libraries, and review DVM parameters. The Navigator opens up the list of virtual tables and virtual views and accesses various data sources on Z and outside Z, by clicking the menu entry 'SQL' > 'Data'. Under it, you find two entries. The first has the name of your DVM server and the other is called 'Other Subsystems' shown in Figure 14.
Figure 14 - The SQL - Data menu of DVM Studio
The data section under the DVM server name contains all the metadata you created and stored directly on the DVM server, including virtual tables and virtual views. How to create and maintain them is being explained somewhere else in this Red Paper. The data section 'Other Subsystems' contains the connections to all relational data sources which objects do not require you to create a virtual table. For instance, Db2 subsystems. Here you can access the objects of these subsystems and create queries directly on these objects. Creating a virtual view that combines several of these objects, you even need to create a virtual table on the base objects first. Filtering data Each of the menu options, like 'SQL', 'Virtual Tables' or 'Stored Procedures' has a filtering capability, which you can access by right-clicking on the menu option and selecting the option 'Set Tree Filter', as shown in Figure 15. A dialog window displays, where you can enter your filtering criteria.
Figure 15 - Filtering tree data is available on any menu option
The next item on the main menu of DVM Studio under the section 'SQL' is the section 'Stored Procedures'. Here you can create connections to Stored Procedures on Db2. You can also generate code on these stored procedures. However, you cannot virtualize these stored procedures and hence not combine them with other objects within DVM Studio.
'Target Systems' represent virtual destinations for your query outputs. Target systems can be on a DBMS or zFS (typically mounted on a directory in Unix System Services). All queries executed in the DVM server should have a target data sourceWhenever you run a query in DVM, the query should point to a target system.
The Discovery option shown in Figure 16 enables access to IDMS data sources. Data discovery also provides integration with IBM Application Discovery and Delivery Intelligence (ADDI) and IBM Rational Rational Asset Analyzer.
Figure 16 - The Discovery menu
'NoSQL' enables users to create access to data sources using MongoDB language. This option is interesting for environments where Data Scientists prefer to use MongoDB instead of SQL. To use the 'NoSQL' option from the DVM Studio menu, it must be activated. Chapter 4 of the User's Guide explains this. Services The section 'Services' is for menu options related to API. Figure 17 provides options to create, test, and administer APIs.
Figure 17 - The Services menu
Web Services allow the ability tocreate, test, and deploy APIs, along with z/OS Connect Enterprise Edition. Any API's SOAP can be handled natively by DVM. For REST APIs, you need to interlock with z/OS Connect Enterprise Edition.
The option 'Target Systems' is used to create the Target systems you need for API executions. Most of the DVM server administration using the ISPF interface. There are a few tasks that DVM Studio can perform, as well. Figure 18 displays some of the administration tasks under the Admin selection drop-down.
Figure 18 - The Administration menu
Under the option, 'Server Parameters' you can look up the value of existing parameters of the DVM server and connect source libraries with data mapping sources. This facility allows user to define which libraries that are in use for data virtualization.
Under the option, 'Virtualization Facility' you have access to special objects of your data sources that are not common virtual tables. For instance, virtualized PARTROOT DBD and PSB definitions of IMS.
The perspectives and views of DVM Studio
The navigator is one of the many views that you can find on DVM Studio. These views are grouped into perspectives. Some views show up in more than one perspective though. Moving between perspectives The 'DV Data' perspective is by far the most important, but it is not the only one. At the upper right of your DVM Studio screen, there is a little button that gives you access to the list of perspectives, as shown in Figure 19. The perspectives that are currently open are shown to the right of the button. Clicking the perspective button opens a pop-up window where you can select other perspectives. If you want to close a certain perspective, you right-click on the button of that perspective and choose the option 'Close'.
Figure 19 - The perspectives button
Data Virtualization Data perspective
This perspective groups views needed to virtualize objects on (or off) the mainframe, develop and run queries, generate code, handle API's and so on.
The options on the Studio Navigator panel basically give focus to one of the views on the data perspective. See Figure 20.
Figure 20 - The DVM Studio navigator panel
The Data Navigator panel contains several views. The navigator view is the most important one. This is the navigator view we reviewed above. It gives you access to the virtualization features of DVM. On figure 22, this view is at the upper center.
When you generate queries or code, it appears in this panel. In Figure 21, this panel is upper right. You now see views with extensions like '.sql' (for generated SQL statements) or '.java' for generated Java code.
On the output panel, you now see the results of queries (SQL Results view) or messages when you connect to a server (Console view). In Figure 22, this panel is lower right.
Here you see any connections you have open. The panel contains the Active Connections view when you right-click on the view, you can open a new connection or close an existing one. In Figure 21, this view is the lower center.
Figure 21 - An overview of the default DV Data perspective
Working with the views, panels, and perspectives
Panels and views are flexible to work with. You can resize them and move them around. You can modify existing perspectives.
Modifying an existing perspective
You can close an existing view by simply click on the 'X' that is at the right end of the tab of the view. You can add an existing view to your perspective by 'clicking' the menu bar option 'Window' > 'Show View' > 'Other..' shown in Figure 22.
Figure 22 - Changing the contents of a perspective
Suppose you want to add the Debug view to your DV Data perspective. Clicking 'Window' > 'Show View' > 'Other...' brings up a pop-up window where you see a list of existing perspectives and views, shown in Figure 23. The Debug view is from the Debug perspective. So, you select the Debug perspective and then the Debug view.
Figure 23 - The list of available Virtual Views
When you click 'OK, the view is added to one of the panels in your current perspective (in our case the 'output' panel). If you want to preserve this new setting of your current view, you can right-click on the perspective icon in the upper right of your screen and select 'Save as'. You can give your perspective a new name and thus create a new perspective or save it under the existing name and thus replace the current perspective.
When you open DVM Studio for the first time, you notice that the 'DV Data' perspective is the default perspective. You can assign another perspective to be the default perspective by going to option 'Window' > 'Preferences'. In the pop-up window, you can select the perspective of your choice and click 'Make Default'. See Figure 24.
Figure 24 - Changing the default view
When you look at the bottom of the Studio Navigator panel, you see there is another panel available which is called Common Tools. It can be opened up by clicking the double arrow on the right side show in Figure 25.
Figure 25 - Opening the Common Tools menu
When opened up, you see the Common Tools menu in Figure 26. There are several interesting and useful tools to be found.
Figure 26 - The Common Tools menu
When you click the button, the Server Trace is opened as a view in the output panel. It opens up empty. Press the blue arrow to start the trace in Figure 27. Once started, the server trace can be viewed. The set of six arrows at the right-hand side is for scrolling. The upper and lower arrows navigate through the trace. The remaining two arrows scroll up and down page-by-page. The identical Server Trace can also display using the DVM ISPF panel.
Figure 27 - Starting the server trace
Modifying the display of the Server Trace
As with the server trace on the ISPF interface, you can modify the display. In DVM Studio by clicking on the 'Profile' and 'Display' buttons. In each case, a pop-up window appears where you can modify the display shown in Figure 28.
Figure 29 - Changing the display of the server trace
On the 'Display' screen you can add extra columns to the trace display by selecting them from the list on the left and using the 'Add' button or remove them from the list on the right by using the 'Remove' button. With the 'Up' and 'Down' buttons you can modify the sequence of the selected columns on the server display, as shown in Figure 39.
Figure 29 - The profile pop-up screen
Exporting the Server Trace
Sometimes, when things go terribly wrong, IBM might want to see the server trace. In that situation, you can export the server trace to an external file, which can then be sent to the support team. Right-click on any message and select 'Export' in Figure 30.
Figure 30 - Export the server trace
A pop-up screen appears where you can enter your selection criteria and formatting information. Click 'Finish' and you're done, as shown in Figure 31.
Figure 32 - The export pop-up screen
Another interesting item on the Common Tools menu is the 'Gather Diagnostics' button. By pressing this button, a zip file with diagnostic information is being created, which can be used for troubleshooting or problem investigation. Press the button and a dialog window appears when the process has finished. The dialog window also tells you where the file was stored.
A third item under the Common Tools menu is the 'Wizards' button. When pressing this button, a pop-up window is shown with several wizards that guide you through several tasks you can execute in DVM Studio.
There are a few other menu views available on the Studio Navigator panel. Clicking on the 'Set Up Pages' button on the upper right of the panel displays a window with other options, as shown in Figure 32.
Figure 32 - Enabling more menus
When we enable the 'Services' menu, there is a list of services that appear in Figure 33.
Figure 33 - The services menu
One of the most interesting options here is the 'z/OS Connect Configuration' option. This menu option helps you configure the z/OS Connect Enterprise Edition on DVM. It generates the necessary xml-file. When you click the menu option, a pop-up window appears, as shown in Figure 34.
Figure 34 - The z/OS Connect configuration screen
When done, you click 'Finish' and the xml-file appears in the generated objects panel in Figure 35. At the bottom of the panel, you can toggle between the design version and the source version. The source version can be copied to the configuration file you want to use.
Figure 35 - The z/OS Connect configuration file 1
The batch interface of DVM is a z/OS based set of JCL jobs that can be used for any of the following.
- Create a table mapping
- Unload/load table mappings at the DVM Server
- Query existing virtual tables and views
There are also jobs to handle the DVM configuration and metadata, which are used during upgrades or version migration. These jobs are not being reviewed here.
Creating a virtual table with the batch interface
The batch interface to create a new table mapping in the DVM Server can be used in situations where DVM Studio is not available or where there are certain connection issues. Creating a new table mapping using batch scripting with one job, hlq.SAVZCNTL(AVZMFPAR). The job can be used for any type of data mapping. The parameters needed are being explained in the comment section of the JCL.
The job to create the same virtual table we used earlier (OFFICES), looks like the JCL in Example 1. A job card (work item) needs to be added. Also, all library names, data set names and SYSIN parameters need adaption to your local DVM installation. The following SYSIN parameters are being used:
- SSID - The name of the DVM Server
- FUNCTION - The functions to execute (in our example STOD, which is the function to create a VSAM/Sequential table mapping). SOURCE: The name of the copybook library and the member for this particular mapping
- START FIELD - The field within the data structure of your flat file where you want the data mapping to start. Usually, this is the first field within the structure. The mapping takes into consideration the whole data structure within the copybook until the next field at the same level as the start field unless you define another end field.
- MAP NAME is an optional field. If you leave it blank, DVM assigns the name of the first field in the mapping as the map name.
- SEQ FILE is the physical file name of the data source you want to map.
// SET LOADLIB=DVM.V1R1.SAVZLOAD
// SET REXXLIB=DVM.V1R1.SAVZEXEC
//DMFEXTR1 EXEC PGM=IKJEFT01,PARM=('AVZMBTPA O'),REGION=0M /
/STEPLIB DD DISP=SHR,DSN=&LOADLIB /
/SYSEXEC DD DISP=SHR,DSN=&REXXLIB
//SOURCE DD DISP=SHR,DSN=WGELDER.COPYBOOK(SALESOFF) /
/SYSTSPRT DD SYSOUT=* //SYSTSIN DD DUMMY
//SYSIN DD *
SSID = AVZ1
FUNCTION = STOD
SOURCE = WGELDER.COPYBOOK(SALESOFF)
START FIELD = SALES-OFFICE
MAP NAME = OFFICES
SEQ FILE = WGELDER.SALESREP.OFFICE
/*
Example 1 - Example JCL to create a virtual table using a batch JCL
Migrate existing virtual tables with the batch interface
A batch interface can extract data mappings from a DVM Server and store them in an XML file. The file contents are uploaded into another DVM Server. It is a two-step process. The first job hlq.SAVZCNTL(AVZGNMPM):
- Generates the job that is used to load the extract into the target DVM Server.
- Creates the extracted file.
After, you run the generated job to the load on the target DVM Server. You can specify more than one map on the extract if desired. But be aware that the use of wildcards is not allowed. So, you need to list all the maps you want to migrate one-by-one in a comma-separated list using their exact matching names.
The XML file (in our example 2 'WGELDER.DVM.DVMEXTR') does not need to exist. It is created automatically if necessary. The same counts for the member name that holds the load JCL (in our example 'DVMEXTR'). This load job will be created in the JCLLIB library mentioned in the example JCL.
The extracted job looks like the JCL in Example 2. Again: a valid job card has to be added and all library names, data set names and SYSIN parameters need adaption to your local DVM installation.
The following SYSIN parameters are being used:
- SOURCE AVZ SSID is the source DVM Server from which you are extracting the data mapping.
- TARGET AVZ SSID is the target DVM Server where you want to upload the data mapping.
- TARGET LOADLIB is the load library of your target DVM Server.
- TARGET EXECFB is the EXEC library of your target DVM Server
- MAP EXPORT PDS is the library that holds your extracted maps. One member for each extracted map is created.
- JCL MEMBER NAME is the member name on the JCLLIB library that contains your load JCL.
- JCL MEMBER REPLACE - if the value is 'YES', the member with the load JCL replaces any existing member with an identical name in the JCLLIB library. If the value is 'NO' and a member with an identical name already exists in the JCLLIB library, the extract JCL fails.
- MAP is the name(s) of the maps to be extracted.
- JOBCARD is the first line of the jobcard to be added to your load JCL.
- JOBCARD1 is the second line of the jobcard to be added to your load JCL (you can specify up to three jobcard lines).
- SAVE OPTION - If 'SAVE', the new MAP is saved on the target DVM Server even if a map exists with an identical name ('REPLACE') replaces it and 'NOSAVE' will discard it).
- REFRESH OPTION - If 'YES', the map list on the target DVM Server is refreshed.
// SET LOADLIB=DVM.V1R1.SAVZLOAD
// SET REXXLIB=DVM.V1R1.SAVZEXEC
// SET SKELLIB=DVM.V1R1.SAVZSLIB
// SET ISPF='ISP'
// SET JCLLIB=WGELDER.JOBLIB
//*
//JCLBLD EXEC PGM=IKJEFT01,DYNAMNBR=200
//STEPLIB DD DISP=SHR,DSN=&LOADLIB
//SYSEXEC DD DISP=SHR,DSN=&REXXLIB
//SYSPRINT DD SYSOUT=*
//ISPPROF DD DISP=(NEW,DELETE,DELETE),DSN=&&PROF,
// DCB=(RECFM=FB,LRECL=80,DSORG=PO),UNIT=SYSDA,
// SPACE=(CYL,(1,1,2))
//ISPMLIB DD DISP=SHR,DSN=&ISPF..SISPMENU
//ISPPLIB DD DISP=SHR,DSN=&ISPF..SISPPENU
//ISPTLIB DD DISP=SHR,DSN=&ISPF..SISPTENU
//ISPSLIB DD DISP=SHR,DSN=&SKELLIB
//ISPLOG DD SYSOUT=*,DCB=LRECL=133,RECFM=FB
//ISPLIST DD SYSOUT=*,DCB=LRECL=133,RECFM=FB
//ISPFILE DD DSN=&JCLLIB,DISP=SHR <=JCL LIBRARY OUTPUT
//***************************************************************
//* SAY STATEMENTS WRITTEN TO THIS DDNAME
//***************************************************************
//SYSTSPRT DD SYSOUT=*,DCB=(RECFM=FB,LRECL=256,BLKSIZE=25600)
//SYSTSIN DD * PROFILE NOPREFIX ISPSTART PGM(AVZIMEX) PARM(PROGRAM(AVZMFMIG) ARG('O') + SUBSYS(AVZ1) MAXEDQ(1000)) /*
//SYSIN DD *
SOURCE AVZ SSID = AVZ1
TARGET AVZ SSID = AVZ2
TARGET LOADLIB = DVM.V1R1.SAVZLOAD
TARGET EXECFB = DVM.V1R1.SAVZEXEC
MAP EXPORT PDS = WGELDER.DVM.DVMEXTR
JCL MEMBER NAME = DVMEXTR
JCL MEMBER REPLACE = YES
MAP = OFFICES JOBCARD =
//WGELDERA JOB MSGLEVEL=(1,1),CLASS=A,MSGCLASS=H, JOBCARD1 =
// REGION=0M,NOTIFY=&SYSUID JOBCARD2 =
//* SAVE OPTION = SAVE REFRESH OPTION = REFRESH /*
//
Example 2 - Example JCL to migrate an existing virtual table
Querying virtual tables with the batch interface
You can also use the batch interface to query existing virtual tables and views. The query job looks like the JCL in Example 3. A job card needs to be added. Also, make sure the correct library names and DVM Server names (parm SSID) are being used. The SQL output is in the file labeled 'FMT'. The example uses the same virtual table we created in the first section of this chapter.
//STEP01 EXEC PGM=AVZXMAPD,PARM='SSID=AVZ1,MXR=0,MXP=999'
//STEPLIB DD DISP=SHR,DSN=DVM.V1R1.SAVZLOAD
//RPT DD SYSOUT=*,OUTLIM=250000 <== SUMMARY
//FMT DD SYSOUT=*,OUTLIM=250000 <== SQL RESULt
//TRC DD SYSOUT=*,OUTLIM=250000 <== TRACE
//DUMP DD SYSOUT=*,OUTLIM=250000 <== SQLCA
//IN DD DDNAME=SYSCNTL
//SYSCNTL DD *
SELECT * FROM OFFICES;
Example 3 - Example JCL to query an existing virtual table
The API interface of DVM can be used to retrieve the following type of information regarding the DVM address space:
- Status
- Control parameters
- Performance characteristics
- Resource consumption information
The purpose of the API interface DVM maintains diagnostic information regarding activities and status for active connections from JDBC, J2CA, and ODBC applications on the DVM server. This information is of two types:
- Real-time data exists while a connection is active. Much of the information (like CPU time, session elapsed time, and so on) changes continuously over time.
- Trace information is stored in an in-memory data set. The size of the data set sets the limit to the amount of trace information that is being kept.
For system management tasks relating to the current execution status of DVM, ‘getconnectioninfo’ functions returns information from the real-time information features. For system management tasks relating to the diagnosis of past systems events, ‘getmessages’ functions return information from the trace data set.
Calling the API interface
The syntax of the API interface command is:
CALL DVS_SERVER(‘parameter’,[‘optional parameters’,…])
This command returns a standard JDBC (or ODBC) result-set. In the background, each API is a stored procedure that runs on z/OS within the DVM Server. The JDBC or ODBC adapter of DVM is used to execute the API call. The following parameters are needed in the connection string for the API call:
- Hostname
- Port
- UserID
- Password
We recommend not to put more parameters on the connection string. All results are returned as string fields. There are several APIs that return information on the other APIs.
The following API's are available:
- CALL DVS_SERVER (‘ping’)
- CALL DVS_SERVER (‘getloadbalance’,’all’)
The 'all' parameter is optional and returns information on all the DVM servers within the system. When the 'all' parameter is omitted, only information on the DVM servers within the same group is returned. The 'group' in this context refers to the High Available configuration (this terminology is similar to Db2 Data Sharing).
- CALL DVS_SERVER(‘getconnectioninfo’,[‘usefieldnames’]).
In case the additional parameter 'usefieldnames' is used, the actual field names are used as column headers on the result set.
- CALL DVS_SERVER (‘getmessages’,[‘search vector(parameter)’]).
- CALL DVS_SERVER (’getevents’,[‘search vector(parameter)’]).
- CALL DVS_SERVER (‘getformat’,’cbbk’).
- CALL DVS_SERVER(‘getparametervalues’,’keyword’,’value’). The keyword is the name of the parameter.
- CALL DVS_SERVER(‘setparametervalue’,’parm’,’value’) sets the parameter 'parm' to the value 'value'.
- CALL DVS_SERVER(‘getparameterdescriptions’).
- CALL DVS_SERVER(‘getgroupnames’).
This API returns the groupname for the associated DVM Server, in case the DVM Server is part of a High Availability configuration.
- CALL DVS_SERVER(‘getdb2subsysids’).
- CALL DVS_SERVER(‘getimssubsysids’).
- CALL DVS_SERVER(‘getcicsconnectids’).
- CALL DVS_SERVER (‘getportids’).
The API interface and DVM Studio
The API interface can also be used and tested using the DVM Studio. You only need to write the CALL DVS_SERVER statement in the 'Generated.sql' view, select the statement, and press PF5 to run it. The results are shown in the 'SQL Results' view as shown in Figure 36. The 'setparametervalue' API, you can manipulate dynamically the DVM parameters from within DVM Studio.
Figure 36 - Running the API interface from the DVM Studio
The metadata for this technology is contained in the catalog of the objects you created within the DVM server. Mostly virtual tables and views. This catalog works similar to a database catalog like Db2. This means you can query this catalog like any other virtual table or view in DVM. A system or database administrator can query this metadata using the DVM Studio, the ISPF interface, or by using batch scripting, as explained in the sections above. The following metadata tables exist in the DVM Server.
- SQLENG.COLUMNS
- SQLENG.COLUMNPRIVS
- SQLENG.ERRORMSGS
- SQLENG.FOREIGNKEYS
- SQLENG.PRIMARYKEYS
- SQLENG.ROUTINES
- SQLENG.SPECIALCOLS
- SQLENG.STATISTICS
- SQLENG.TABLES
- SQLENG.TABLEPRIVS
[{"Type":"SW","Line of Business":{"code":"LOB10","label":"Data and AI"},"Business Unit":{"code":"BU059","label":"IBM Software w\/o TPS"},"Product":{"code":"SS4NKG","label":"IBM Data Virtualization Manager for z\/OS"},"ARM Category":[{"code":"a8m0z000000cxAOAAY","label":"SQL Engine"}],"Platform":[{"code":"PF035","label":"z\/OS"}],"Version":"All Version(s)"}]
