ADO Interface with CCA client-server products Model 204 and System 1032 Connectivity Using Microsoft ActiveX Data Object
By Steve Nelson
As the ActiveX Data Object (ADO) Interface finds its way into the mainstream of application and web development, the need to access Model 204 and System 1032 data with this API has become increasingly important. The ADO Interface is Microsoft's high-level programming model for accessing multiple data sources. Recently several customers, who use either the Connect* ODBC driver or the System 1032 ODBC Driver brought up the topic. One recurring question was: "How can we access Model 204 (or System1032) using ADO?" I would like to show you how this is done.
This first-in-a-series article will provide you with a basic understanding of how-to with a couple of simple examples using Visual Basic 5.0/6.0. Please keep in mind that accessing Model 204 and System 1032 may differ from application to application. This article shows just one of many ways to access data through the ADO Interface.
Let us first examine a simple data access scenario using the ADO Interface. The "Visual Basic example" illustrates:
Working with the ADO Interface
This basic code sample works as it is; you need only supply your own data source. The ADO Interface is a very flexible API with many properties, methods, and events. In this article we show only a small portion of its capabilities.
The example helps demonstrate Visual Basic access. The ADO Interface, version 2.5, was used in this example. Your version of the ADO Interface may or may not have the same constructs that are shown in the example. Check with your local Microsoft support personnel as to your version.
Visual Basic example
This example demonstrates three subroutines. Each routine was created by selecting the procedure drop-down list of the Project - Code section.
Private Sub Form_Load()
Set ADOConnection = New ADODB.Connection
ADOConnection.Provider = "MSDASQL"
Create an ADO connect object.
Microsoft's OLE-DB/ODBC Provider.
ADOConnection.Mode = adModeReadWrite
ADOConnection.ConnectionString = "DSN=SDNSQL;UID=ADMIN;PWD=ADMIN;"
ADOConnection.Open
ODBC Data source name.
Make a database connection.
Private Sub cmdButton1_Click()
Dim ADOResults As ADODB.Recordset
Dim ADOSQLStatement As String
Set ADOResults = New ADODB.Recordset
Set ADOResults.ActiveConnection = ADOConnection
Do Until ADOResults.EOF
ListBox1.AddItem (ADOResults("FIRST_NAME").Value & " " & ADOResults("LAST_NAME").Value)
ADOResults.MoveNext
Loop
ADOResults.Close
Set ADOResults = Nothing
Close the cursor, then
release the cursor.
ADOConnection.Close
Set ADOConnection = Nothing
Close the connection.
Release the connection.
Note: If you are using Connect* and Model 204, the ADOSQLStatement can be replaced with an RCL-User Language procedure or command.
Figure 1. Record set display
As you can see, accessing Model 204 and System 1032 is relatively easy. Our example shows how to connect to your Model 204 or System 1032 database. Be sure to create appropriate projects to run these samples.
Again, your applications may vary and constructs may be different, but the ADO Interface actions are the same.
Coming attractions
In the next CCAprint we will demonstrate Connect* and System 1032 ODBC drivers using Active Server Pages. Stay tuned!
Model 204 Part 3: Monitoring the Resident Request feature
by James Damon
In Part 1 and Part 2 of this series I introduced and reviewed the Resident Request feature, a form of in-memory data that provides for high performance in APSY systems. In Part 3, the final article of the series, I explain the parameters and statistics used to monitor this feature to determine its costs and benefits.
Looking back at Part 2
I will begin by explaining the two exceptions in the following paragraph from Part 2:
"Without the Resident Request feature, each user loads all compiler tables for a precompiled request into the current server and runs a private copy of that program. Since the QTBL and NTBL portions of a precompiled request never change regardless of the user (with two exceptions), loading this part of the request is unnecessary."
The two exceptions refer to requests that contain either of the following nonreentrant FIND statements:
Either of these FIND statements modifies QTBL during evaluation and renders QTBL nonreentrant. If the request is resident, the QTBL portion of the request must be evaluated as though it were nonresident. QTBL must be loaded into the user's server, modified there, and evaluated as a private copy by that user. Other users, who do not execute either of these two types of FIND statements (due to branching logic), continue to evaluate QTBL as a resident request.
Put another way, once a request becomes resident, it is never made nonresident. However, if either of the previous FIND statements is evaluated, the request will be evaluated as though it is nonresident. Loading the QTBL portion of a resident request into a user's server because of one of these two statement types is one type of switch.
Types of switches
A switch occurs in one of two circumstances.
1. Transferring control from a resident to a nonresident request
When you evaluate a resident request and then transfer control via the subsystem communications global variable to evaluate a nonresident request, a switch occurs. The nonresident request is either not precompilable, and therefore can never be resident, or is precompilable but is not yet resident because it has:
2. Invoking a nonreentrant FIND statement
When you evaluate a resident request and due to branching logic, evaluate a nonreentrant FIND statement, the resident request is switched to a nonresident request for only you for this evaluation.
The importance of switches
Switches are significant because they enable and disable the page release (PGRLSE) strategy employed by Model 204. When the percentage of switches from resident to nonresident mode is less than 25% of resident request evaluations, the QTBL and NTBL real-page frames in the server of users running the subsystem are released and returned to the operating system. In an Online with a large number of servers, this could result in a significant reduction in real storage utilization.
Resident request tracking parameters
In addition to the two parameters, RESSIZE and RESTHRSH, that enable the Resident Request feature, two other parameters, RESCURR and RESHIGH, provide the following information.
In Figure A, 1,000,000 bytes are available for resident requests, but only 475,136 bytes are currently in use and this is the maximum used by resident requests. When a subsystem is stopped, all resident requests are deleted from virtual storage and the value of RESCURR decreases.
Figure A. Viewing the resident request statistics
VIEW RESSIZE,RESCURR,RESHIGH,RESTHRSH RESSIZE 1000000 MAXIMUM STORAGE TO BE USED FOR RESIDENT REQUESTS RESCURR 475136 CURRENT STORAGE USED FOR RESIDENT REQUESTS RESHIGH 475136 HWM FOR STORAGE USED FOR RESIDENT REQUESTS RESTHRSH 0 SERVER WRITE THRESHOLD FOR MAKING REQUEST RESIDENT
VIEW RESSIZE,RESCURR,RESHIGH,RESTHRSH
RESSIZE 1000000 MAXIMUM STORAGE TO BE USED FOR RESIDENT REQUESTS
RESCURR 475136 CURRENT STORAGE USED FOR RESIDENT REQUESTS
RESHIGH 475136 HWM FOR STORAGE USED FOR RESIDENT REQUESTS
RESTHRSH 0 SERVER WRITE THRESHOLD FOR MAKING REQUEST RESIDENT
Monitoring resident requests
More tracking detail is provided using the command:
MONITOR SUBSYS (PROCCT) subsysname
As shown in Figure B and based on the procedure file for this subsystem and the precompilable and non precompilable prefixes defined to SUBSYSMGMT the following information is given:
Figure B: MONITOR command display of a resident request
MONITOR SUBSYS (PROCCT) PDS SUBSYSTEM NAME: PDS NUMBER OF PRECOMPILABLE PROCEDURES (SAVED): 14 NUMBER OF PRECOMPILABLE PROCEDURES (NOT SAVED): 17 NUMBER OF NON-PRECOMPILABLE PROCEDURES: 12 NUMBER OF REQUESTS MADE RESIDENT: 8 NUMBER OF ELIGIBLE REQUESTS NOT RESIDENT: 4 STORAGE USED FOR RESIDENT REQUESTS: 475136 NUMBER OF RESIDENT PROCEDURE EVALS: 283 NUMBER OF SWITCHES FROM RESIDENT MODE: 78
MONITOR SUBSYS (PROCCT) PDS
SUBSYSTEM NAME: PDS
NUMBER OF PRECOMPILABLE PROCEDURES (SAVED): 14
NUMBER OF PRECOMPILABLE PROCEDURES (NOT SAVED): 17
NUMBER OF NON-PRECOMPILABLE PROCEDURES: 12
NUMBER OF REQUESTS MADE RESIDENT: 8
NUMBER OF ELIGIBLE REQUESTS NOT RESIDENT: 4
STORAGE USED FOR RESIDENT REQUESTS: 475136
NUMBER OF RESIDENT PROCEDURE EVALS: 283
NUMBER OF SWITCHES FROM RESIDENT MODE: 78
Analyzing the benefits of resident requests
The two main benefits derived from the Resident Request feature are CCATEMP I/O reduction and a reduction in the number of bytes transferred during each server swap.
Determining CCATEMP I/O reduction
CCATEMP I/O can be determined from CCAAUDIT during system termination when CCATEMP is closed:
FILE='CCATEMP ' DKRD=3413 DKWR=413420
You should watch these statistics before and after enabling the Resident Request feature to determine CCATEMP I/O reduction.
Reducing bytes transferred during server swap
The reduction in bytes transferred during a server swap can be determined using system final statistics at the end of CCAAUDIT or, in real time, using MONITOR STAT output. The relevant statistics are SVRD, SVWR and SVPAGES.
SVPAGES is the cumulative number of 4K (4096) byte blocks transferred during all CCASERVR I/O operations. Although server reads and writes are actually full track I/Os, the number of bytes transferred is computed according to the number of 4K-byte blocks. For example, you might observe the following statistics from an Online run:
SVPAGES=39798726, SVRD=578284, SVWR=581614
You can calculate the average number of bytes transferred per server I/O as:
(SVPAGES * 4096)/(SVRD+SVWR) = 140,543
This number should decrease after you enable the Resident Request feature. It will continue to decrease as more procedures are made resident.
Notes from the field
Several larger CCA customers run their production Onlines with RESSIZE set to 20,000,000 and RESTHRSH set to 2 and enjoy substantial performance improvements. Setting RESTHRSH to 0 makes every precompilable APSY procedure resident on its first compilation, which is useful when maximum residency is desired, even for small, not-often evaluated procedures.
Summing up In-Memory advantages
Organizations are under increasing pressure to provide larger volumes of data to larger numbers of online users - both internal and external from the worldwide web. The facilities available in Model 204 to keep both data and programs (QTBL and NTBL) in-memory ensures that the high performance requirements demanded by these organizations continues to be met and that speed of access to that data from Model 204 is unparalleled.
CCA Announces the General Availability of Connect* V4R1.2!
With this release of Connect*, CCA continues to improve performance and data access for users of Connect* by introducing:
With this release of Connect* and projects planned for this calendar year, we look forward to having our customer-base take advantage of ASP development, programming in Visual Basic and VB Scripting, Visual C++ development, and VBA development!
In accordance with our standard product support policies, support for Connect* Version 2.0 and Version 4.1.0 will be discontinued as of June 30th, 2001.
This release was shipped on the 22nd of December to licensed subscribers of maintenance.
If you are interested in obtaining information on Connect* please contact your CCA account representative.
Copyright © 2008 Computer Corporation of America. All right reserved. Published in the United States of America.
Contact CCA Webmaster Copyright 2008
