• Model 204 - Using Remote Command Line: Part 1
• System 1032 - Calling stored procedures in ODBC Driver: Part 1

Model 204
Using Remote Command Line: Part 1
by Vicky Dounias

Advantages of Remote Command Line

Using Remote Command Line (RCL) you can take advantage of the processing speed and flexibility of User Language applications, Model 204 commands, and APSY subsystems to access and manipulate data on the mainframe, while working from your PC. Existing User Language procedures which support 3270 applications can be used with few, if any, changes to drive state-of-the-art Internet access to Model 204.

This article, the first in a series of three, describes the environment and configuration requirements for using RCL connections.

Network configuration for RCL or SQL

The PC client uses Connect* Version 4.1 with a TCP/IP connection to Model 204 with Horizon, Version 4.1.1 or later.

Users of OS/390 2.5 or later must use Model 204 Version 4.2.0 with Horizon TCP/IP native version (extended sockets). See Late Breaking News, "Known Issues, Horizon TCP/IP" for details.

All other MVS and VM configurations use the Horizon TCP/IP IUCV version.

Connections from the Internet are via WebGate which incorporates Connect* and shares the same mainframe requirements.

Mainframe requirements

To support RCL connections, Model 204 uses IODEV 49 threads and CCATEMP for transient output data storage. Network definition requires the LINK, PROCESSGROUP and PROCESS commands, the same as for SQL connections. RCL requires no catalog (CCACAT) file. Consult the Model 204 SQL Connectivity Guide, Chapter 2, for a more detailed explanation.

PC client requirements

First you need an ODBC-enabled client application. Then you define an RCL-specific data source with the ODBC Data Source Administrator using the Model 204 ODBC Driver installed with Connect* as shown in Figure 1.

Following the colon (:) in the Server ID/Address field, enter the port number defined in the Model 204 LINK command. The Connection Type is, of course, RCL with the semi-colon (;) as the RCL End of Line Character. The Isolation Level, Dirty Data Treatment, and Effect of COMMIT field information is not used for RCL connections.

The relationship between RCL and SQL

RCL is an extension of ODBC provided so you can use Model 204 with SQL. RCL data, the actual Model 204 input stream, is sent in the ODBC function call SQLExecDirect. A semicolon (;) separates commands and statements and data is returned in the SQLFetch call as a one column, variable length row of up to 255 characters. Model 204 output and messages are returned to the client.

RCL and SQL connections can exist simultaneously from one client application so you can choose the most efficient method to send or retrieve information.

Anticipating Parts 2 and 3

Part 2 provides you with a Visual Basic client-server application that accepts Model 204 commands, User Language, and procedures. Part 3 covers a Web-enabled application executing Model 204 procedures.

System 1032
Calling stored procedures in ODBC Driver: Part 1
by Tym Stegner

A stored procedure in the System 1032 ODBC Driver lexicon refers to both a PL1032 procedure object and the SQL extension mechanism used to access this procedure from an ODBC-based query.

While the details of methods calling a procedure differ among the favorite ODBC-enabled client applications, the basic idea is the pass-through query.

Client application	How the pass-through works
Microsoft Excel/ Microsoft Query	ExecuteSQL option from File menu in Query (Query is the selection mechanism for Excel), or SQL Extension syntax in the SQL window.
Microsoft Access	Pass-through query.
SQL Server	SQL extension syntax against a linked server defined as the ODBC data source.
Powerbuilder	PB Stored Procedure where the SQL Extension syntax comprises the AS clause of the stored procedure.

Before you begin building a query, make sure you set up your environment as follows:

• Compile a PL1032 procedure into a library and register it in an ODBC Driver dictionary.
• Enter a data source in your host’s ODBC.INI file.
• Create a data source entry using the ODBC Administrator on your PC.
  

Building a pass-through query in Access

Take these steps in Access to build a pass-through query with a System 1032 stored procedure:

1. Start Access to bring up the Databases dialog box.
2. To create a new query, on the Databases dialog box select the Queries tab. Click New.
3. In the New Query dialog box, select Design View and click OK.
4. In the resulting Show Tables dialog box, just click Close. The stored procedure provides the table for the query.
5. On the toolbar select the Query menu. Choose SQL Specific and click Pass-Through option.
6. In the SQL Pass-Through Query data entry dialog box, enter the SQL extension syntax for executing a PL1032 stored procedure, as shown in the following example:
   { CALL FIND_JONES_HARDY() }

Note: System 1032 ODBC Driver requires parentheses () to enclose parameters, even if the stored procedure has no parameters.

Connecting Access to a data source

1. On the toolbar, select the View menu, and click the Properties option.
2. In the Query Properties dialog box, you must specify the ODBC connection string required to locate this PL1032 stored procedure.

Note: Complete the field that begins ODBC; by specifying a data source. For this example enter: ODBC; DSN=S1032_IVP;

Access prompts for the username and password when the query is executed.

Returning your data

1. The FIND_JONES_HARDY procedure returns a set of records, so enter Yes in the Returns Records field.
2. Close the Query Properties dialog box.
3. Save the query by selecting the Save option on the File menu.
4. Execute the query by selecting the Run option on the Query menu. Query results are displayed in an Access data sheet view.

You can use the pass-through approach for any stored procedure without parameters or with only static parameters. Static parameter values are specified in the Query data entry window at the time the query is defined. You can make use of the Access parameter prompting mechanism. However, this limits you to the Access prompting mechanism for your stored procedure execution.

Coming in Part 2

Part 2 discusses a more programmatic means of calling a stored procedure with dynamic or form-based parameters.

Copyright © 2008 Computer Corporation of America.
All right reserved. Published in the United States of America.

Home DownloadsownFeedback

Contact CCA Webmaster
Copyright 2008