System 1032
ODBC_MGMT_TOOL.COM
By Bill Hedberg
Managing an ODBC server becomes complex as time goes on. Data sources need to be created, updated, and removed; debug files are created in the ODBC manager account; and system activity must be checked. I use a DCL command file called ODBC_MGMT_TOOL.COM to help manage the ODBC servers here at CCA. By collecting commonly used commands into a file, I donít have to remember all of the specific commands. It also provides a way to document the commands so that I can reference them later or for others to use when Iím not available.
The sections of code displayed in this article are outputs of the command file. You cannot use this command file with Multinet service.
Accessing and Using the Command File
You can download the ODBC_MGMT_TOOL.COM file from the System 1032 anonymous FTP server at FOX.CCA-INT.COM, in the [.CCAPRINT] subdirectory. The code described in this article is a trimmed down version of the ODBC management command file I use here at CCA, to highlight the important points.
ODBC_MGMT_TOOL.COM is provided to you as is. Although I use it in my daily work, CCA does not support it or plan to develop it.
Defining the commands and logical names
Using the ODBC command without parameters displays the following help. After displaying help text, the command procedure checks that the system logical S1032ODBC_INIPATH is defined. If S1032ODBC_INIPATH is not defined, a message is displayed saying that ODBC is not running. S1032ODBC_INIPATH must be defined for the S1032 ODBC server to find the ODBC.INI file.
$ ODBC
ODBC_MGMT_TOOL.COM Commands are... INI,DSRC Types out ODBC.INI or Data Sources in ODBC.INI SYS SHOW SYS of ODBC processes on Cluster ENA,DSAB Enables/Disables S1032SRVMGR TCP/IP Service (UCX) NET Status of ODBC TCP/IP Services (UCX) LOGON,LOGOFF Enables/disables ODBC logging files (MGR:.ERR,.OUT)
(LNM$SYSTEM_TABLE)
"S1032ODBCSHR3" = "SYS$SYSDEVICE:[S1032.ODBC.V032.AXP]S1032ODBCSHR3_V71.EXE" "S1032ODBC_CHKOUT_DATASOURCE" = "ASP_DVL" "S1032ODBC_DBGFILES" = "FALSE" "S1032ODBC_DEMO" = "SYS$SYSDEVICE:[S1032.ODBC.V032.DEMO]" "S1032ODBC_IMAGE" = "SYS$SYSDEVICE:[S1032.ODBC.V032.AXP]" "S1032ODBC_INIPATH" = "SYS$SYSDEVICE:[ODBCMGR]" "S1032ODBC_LIB" = "SYS$SYSDEVICE:[S1032.ODBC.V032.LIB]"
The DSRC command searches S1032ODBC_INIPATH:ODBC.INI and displays a list of each data source name and its related directory file name. If you wanted to see all the detail of the data sources you could print out the ODBC.INI file
$ ODBC DSRC Data Source S1032_IVP...........DBQ=S1032ODBC_DEMO:IVP_DICTIONARY BILLS_DSN...........DBQ=BHEDBERG_DSN:BILLS_DICTIONARY TYMS................DBQ=DISK$PUBLIC2:[TSTEG.PROB]TYMS.DMB
$
The SYS command displays and identifies all active ODBC processes running on the cluster. What is displayed at your site will differ from the output shown in the following code.
$ ODBC SYS OpenVMS V7.1 on node ASP 29-SEP-2000 11:11:14.66 Uptime 9 01:49:03 AlphaServer 2100 4/275 Pid Process Name State Pri I/O CPU Page flts Pages 210012D4 s1032srv_BG1475 LEF 4 2336 0 00:00:02.14 1179 1142 N [WORK,BHEDBERG] 9136Kb
The NET command show the state of the S1032SRVMGR TCP/IP service. It shows the service details on the port, whether it is enabled, and which COM file is running.
$ ODBC NET
Service: S1032SRVMGR State: Enabled Port: 1583 Protocol: TCP Address: 0.0.0.0 Inactivity: 5 User_name: ODBCMGR Process: S1032SRVMGR Limit: 64 Active: 0 Peak: 0
File: S1032ODBC_LIB:S1032ODBC.COM Flags: Listen
Socket Opts: Rcheck Scheck Receive: 0 Send: 0
Log Opts: None File: not defined
Security Reject msg: not defined Accept host: 0.0.0.0 Accept netw: 0.0.0.0 ----------------------------------------------------------------------
Service Port Proto Process Address State
S1032SRVMGR 1583 TCP S1032SRVMGR 0.0.0.0 Enabled S1032V40 5600 TCP S1032V40 0.0.0.0 Disabled
Enabling and disabling TCP/IP services
The ENA command enables the TCP/IP service for S1032 ODBC. To get a connection you must have your TCP/IP service enabled. When you are developing applications you might want to disable and reenable TCP/IP service.
$ ODBC ENA
Service Port Proto Process Address
S1032SRVMGR 1583 TCP s1032srvmgr 0.0.0.0 ----------------------------------------------------------------------
S1032SRVMGR 1583 TCP S1032SRVMGR 0.0.0.0 Enabled S1032V40 5600 TCP S1032V40 0.0.0.0 Disabled $
The DSAB command disables the TCP/IP service for S1032 ODBC.
$ ODBC DSAB
S1032SRVMGR 1583 TCP S1032SRVMGR 0.0.0.0 ----------------------------------------------------------------------
S1032V40 5600 TCP S1032V40 0.0.0.0 Disabled $
Turning debug file on and off
The LOGON and LOGOFF commands set the S1032ODBC_DBGFILES system logical to TRUE and FALSE to enable and disable the S1032 ODBC server from outputing debugging files in the ODBC manager account directory.
$ ODBC LOGON "S1032ODBC_DBGFILES" = "TRUE" (LNM$SYSTEM_TABLE)
and
$ ODBC LOGOFF "S1032ODBC_DBGFILES" = "FALSE" (LNM$SYSTEM_TABLE)
In conclusion
This as-is tool makes the OpenVMS side of the ODBC Driver management easier. You can use this command file as a template for adding, organizing, and documenting commands related to ODBC administration. CCA does not recommend that you use this code in your production environment and such use in this environment is at your own risk. However, if you use this code in a production environment, CCA advises you to thoroughly test and modify the code to suit your environment.
Customer Profile
BOCES Builds a Virtual Data Warehouse with CCA Analytics: Part 2
By Marie Kelly
In Part 1 September 2000 edition of CCAprint, we introduced the Board of Cooperative Education Services (BOCES) in Syracuse, New York, who were looking for a data warehouse solution to share a 10-year collection of student-oriented data and research with their users
This article discusses how a virtual data warehouse became a design tool for a data warehouse at BOCES and whether you could expect similar success at your site.
Choosing CCA Analytics, a Business Intelligence Tool
After a brief review of popular business intelligence tools, BOCES chose CCA Analytics.
"CCA Analytics was attractive to us for several reasons," explains Larry Dismore, director of the Central New York Regional Information Center at BOCES. "It provided all the basic query, reporting, and analytical functions that we felt our users need. It was from our database vendor, so we knew there would be no integration problems and that the learning curve for producing custom functionality would be minimal. The performance was pretty much unbeatable. We were not naive about the risks of throwing large numbers of researchers at operational data, and we were very concerned about the performance of both the analytical and the operational applications. The indexing technology inherent to CCA Analytics made this a non-issue."
Designing the Virtual Warehouse
Like building a data warehouse, constructing a virtual data warehouse requires some understanding of the usersí informational needs. But because a virtual data warehouse consists mainly of index tables and logical views of the data, rather than physical copies of operational data, BOCES was not concerned with getting the views right the first time.
If they built a view that users didnít needóremove it. If they omitted a view, which later surfaced as a requirementóbuild it on the fly. The use of the warehouse was undisturbed.
BOCES constructed numerous views of all current databases, providing the users maximum range to analyze the data. This helped their users understand what data was available to them, which translated into more accurate user requirements for the data warehouse. In two weeks, the IT department completed and delivered the initial implementation of the virtual data warehouse.
Accessing the Virtual Data Warehouse
Working in an intuitive, Windows-based environment, pioneer users find it relatively easy to access and analyze the data, given the appropriate database view. Here are some tasks they can accomplish:
Query and Segment Data
Users can easily create simple or highly complex queries in a point-and-click environment. The values associated with each field are provided to the user upon request, helping the user find the exact data needed. Users can search the data based on the result of a macro or formula, versus a specific value. Because users donít always know what theyíre looking for when they first begin, they can iteratively refine their queries, eventually narrowing down the data in a step-by-step fashion. When the user is satisfied with the selection set of records and fields, the user can name and save the selection criteria for easy reuse later.
Summarize the Data
Summaries of the data such as counts, averages, and maximum and minimum values are easily gotten. Summaries can be grouped by any number of different fields, or even by computed values. Summaries generally appear instantaneously, regardless of database size, because computations are executed against the index table structures, rather than against the actual database records.
Display and Manipulate the Data
The virtual data warehouse comes with numerous tools for data display, including reports, charts, and graphs. These output formats are also interactive; the user can manipulate them to form new queries, drill down to underlying data, or view the data from different perspectives. When you require a more specialized view of the data, you can easily export the data into other analytical packages such as spreadsheets or OLAP tools.
Is a Virtual Data Warehouse in Your Future?
Not every application nor enterprise is a good candidate for a virtual data warehouse. BOCESí data and application has several characteristics that contributed to their swift success, including:
Clean, Usable Data
BOCESí operational data did not have serious integrity problems that prevented it from being effective for business intelligence purposes prior to cleansing. Their data did not need to be substantially transformed. Aggregations, which will likely be performed for the data warehouse, were not essential due to the ease and speed of dynamic summaries through the index tables.
Appropriate Hardware Platform
Because BOCESí operational databases are stored on their IBM OS/390™ enterprise server, they can handle the additional user load. On a smaller server, the number of users accessing the virtual data warehouse would most likely be limitedóperhaps not meeting the original requirement.
Appropriate Database Platform
The BOCES operational databases are stored in Model 204. If a significantly less robust DBMS was in use, the number of users accessing the warehouse would most likely be limited.
Network Support
The infrastructure was already in place to connect the PCs to the mainframe, thus allowing users fast access to the new virtual data warehouse.
Homogeneous Data
Although Analytics, with Model 204 structures, can analyze any data source, the BOCES project was particularly efficient since all data was in Model 204.
How did you score?
If several of the previous factors were not positive, the virtual data warehouse solution might have looked more and more like a full-blown data warehouse implementation, and possibly not worth the added effort.
A Virtual Data Warehouse for the long term
A virtual data warehouse not only quickly solves a business intelligence need, it also contributes significantly to the effective design of the data warehouseóa benefit that saves both time and money. When the data warehouse is up and running, the virtual data warehouse continues as the testing ground. Even a well-designed data warehouse needs adjustments over time. These areas can be researched by the virtual data warehouse.
And from another viewpoint, the strengths of the data warehouse will undoubtedly generate more interest in its use by other end-users, who in turn require enhancements to the data warehouse to satisfy their needs. These new demands can be immediately satisfied by the ever-flexible virtual data warehouse, always one step ahead
Correction
The following correction is necessary for Autofix, Part II in CCAprint April 2000.
The correction is in the section Object replacement under OS/390: running a relink
In the relink JCL, the two INCLUDE statements are reversed. It should read:
//STEP01.SYSLIN DD *
* Include the new object module(s) for ONLINE, e.g.:
INCLUDE OBJLIB(EVSO)
* Then include the old ONLINE module:
INCLUDE OLDLOAD(ONLINE)
ENTRY MAINTASK
NAME ONLINE(R)
/*
A Model 204 customer found this error and brought it to our attention, for which we are grateful.
Copyright © 2008 Computer Corporation of America. All right reserved. Published in the United States of America.
Contact CCA Webmaster Copyright 2008
