Connect-Star for ODBC

Overview

Connect★ for ODBC is multithreaded capable. Connect★ provides a common memory stack that will be shared during access of a multithreaded environment or application.
Connect★ is not multithreading.

The 32-bit Connect★ for ODBC driver offers you the flexibility to use generic code with multiple ODBC-compliant database interfaces on the Windows Platform.

To develop applications, use a Windows application such as:

• Microsoft Office (Excel, Access, Word)
• Microsoft Visual Studio (Visual Basic, or any ODBC compatible language)

Setting up the Connect^★ for ODBC environment

ODBC conformance

The Model 204 32-bit ODBC driver supports conformance to ODBC Version 1.1 and runs with ODBC 2.0 and ODBC 3.0 applications.

An informational message is returned for ODBC 2.0 and ODBC 3.0 requests that are not supported.

ODBC Installation steps

Complete these steps when installing Connect★ for ODBC:

Step	Action
1	Uninstall an earlier version of Connect★ for ODBC
2	Install Connect★ for ODBC
3	Confirm the workstation-to-mainframe communication.
4	Define an ODBC data source.
5	Populate or load the SQL catalog and verify the Connect★ connection.

Uninstalling a previous version of Connect^★ for ODBC

Running the Connect★ uninstaller removes the files, program groups, program folders, and registry entries created during Connect★ installation. It does not remove work files that you add to a Connect★ workstation.

Run the uninstaller for Connect★ using the Windows Control Panel to select the Add/Remove Programs icon.

In the Add/Remove Programs Properties dialog box, select the Connect★ software. Click the Add/Remove button.

The uninstaller does not remove the following:

• any files added to the directory after the installation completed
• any items added to the program folder after the installation completed
• existing ODBC data sources

Installing the product for ODBC

1. Ensure that you have fulfilled the preinstallation requirements as described in Preparing to install.
2. Navigate to the FTP server: ftp.rocketsoftware.com
3. Enter the userid and password provided by Rocket.
4. Navigate to the ConnectStar File folder.
5. Open the ODBC folder and download the files to your PC, in binary format.
6. Run the .exe file to install the application.

Files installed

In addition to the files installed in your installation destination folder, Connect★ installs the following files in Program Files under Common Files/CCA:

Installed for TCP/IP
M204IN32.DLL
M204OD32.DLL
M204RX32.DLL
M204SC32.DLL
M204TP32.DLL
M204UT32.DLL

Installation complete

When the installation is complete, you can use the Start > Programs > Connect Star for Model 204 > ODBC program group to access one of the following:

• Audit for
• Turn logging on
• Turn logging off
• Unsupported
• CLIIVP

  <IMG SRC="03 ODBC-2.gif">

  CLIIVP uses either an SQL or an RCL ODBC data source to connect to Model 204. It accepts a file or a command-at-a-time as input. Output is returned in a results window. You can edit and save both input and output.

• DDLWIN

  <IMG SRC="03 ODBC-3.gif">

  DDLWIN runs the DDL UTILITY which accepts a file of DDL statements and updates the CCACAT Model 204 file.

• TTRW

  <IMG SRC="03 ODBC-4.gif">

  TTRW lets you select an ODBC data source and runs the SQL or RCL request you enter or provide in an input file. Output is written to dbg.txt in the ODBC > Unsupported folder.

  <IMG SRC="03 ODBC-5.gif">

• Catalog 2
  • Connect to data source, for a display of all tables
• Readme File

Defining an ODBC data source

Before executing an SQL statement, check with your Connect★ administrator to make sure that either the demonstration database is installed and has been defined to the SQL catalog file, CCACAT, or you have other available tables defined in CCACAT. (See SQL catalog population.)

Selecting an ODBC data source is required to establish an ODBC connection to the Model 204 Online.

Use the Model 204 ODBC Driver -- Configure Data Source dialog box to create data sources that can be accessed by ODBC or by other applications such as Microsoft Access, Visual Basic, or PowerBuilder.

You can define more than one data source, each identified by its data source name, with different settings in each one, to the same Model 204 Online.

To give your workstation greater flexibility, you might want to configure the same Model 204 Online more than once, as SQL and RCL (see Connection Type).

Creating a data source

To create a data source:

1. In the Control Panel, click Administrative Tools. Double-click the Data Sources (ODBC) icon to display the ODBC Data Source Administrator dialog box.

   <IMG SRC="03 ODBC-6.gif">

2. Create a user or system data source by clicking on the Add button in the User DSN or System DSN tab.
3. Select the M204_32 driver in the Create New Data Source dialog box.

   <IMG SRC="03 ODBC-7.gif">

4. Click Finish to display the Model 204 ODBC Driver -- Configure Data Source dialog box.

   <IMG SRC="03 ODBC-8.gif">

5. Define the data source by filling in the text boxes (see Model 204 ODBC driver data source text boxes) and click the Test Connection button.

Changing a data source

To change an existing data source definition:

1. Open the 32-bit ODBC Data Source Administrator to display the ODBC Data Source Administrator dialog box.
2. Select a data source in the Data Source list box.
3. Click Configure to display the Model 204 ODBC Driver -- Configure Data Source dialog box.
4. Change the information in the text boxes and click OK.

Model 204 ODBC driver data source text boxes

This section describes the individual text boxes in the Model 204 ODBC Driver -- Configure Data Source dialog box.

Data Source Name

Each data source must have a unique name.

The name you enter is the name applications use to access the set of configuration information in the data source. This is the name that applications pass to the SQLDriverConnect function or the SQLConnect function. It corresponds to the DSN keyword.

You can enter any alphanumeric characters up to a maximum of 30 characters. Do not use blank spaces in the Data Source Name.

You can give the data source a name that is more meaningful to you than the (default) Model 204 name in the Server ID/Address field. The Data Source Name value resides on your workstation and is accessible only from your workstation. The specification for the data source name is in the ODBC.INI section of the registry.

The ODBC standard lets you create multiple data sources. If you want a default data source, you must explicitly identify the Data Source Name as "Default." You are not required to create a default data source.

Server ID/ Address

The Server ID value specifies the IP address and the port number used by the Model 204 32-bit ODBC driver to establish a connection to a Model 204 server. The IP address and port number are typically assigned by the local systems programmer. Follow these guidelines:

• The IP address must be the IP address of the IBM z/OS or z/VM mainframe system as entered in the Model 204 Online to which you are connecting. The address is defined in the Define Link LOCALID=xxxx.
• The port number must be the number specified in the Model 204 TCP/IP link defined in the Online:

  Define Link SERVPORT=xxxx

• The IP address and port number are separated by a colon.
• Two IP address formats are possible: either the domain name or the numeric IP address. For example:

M204MVS1:3001 192.207.28.129:3001

Connection Type

Use the Connection Type text box to specify the use of SQL or RCL to establish a connection (IODEV) to the mainframe:

• With SQL, you can use SQL statements as specified in the Rocket Model 204 SQL Server User's Guide.
• With RCL, you can use SOUL requests and Model 204 commands and procedures.

Auto Commit (SQL)

The default SQL_AUTOCOMMIT setting for the M204_32 Driver is ON. This default setting implies that all inserts, updates, and deletes are committed to the database immediately after the transaction is applied, without the need to issue an SQLTransact call with SQL_COMMIT.

The AUTOCOMMIT default (ON) returns SQL_SUCCESS for update or delete transactions that find no rows to update or delete in the table.

To turn the SQL_AUTOCOMMIT option OFF, select the OFF option in the text box.

RCL End-of-Line Character

If you are establishing an RCL connection, accept the end-of-line character, the semicolon (;) character in the text box.

Default User Name

The Default User Name value is used by the Model 204 32-bit ODBC driver to log on to a Model 204 server. The Default User Name must be a valid Model 204 logon name for the requested Model 204 server.

If you do not enter a Default User Name, the application prompts the user for a valid name at connection time. The Default User Name corresponds to the UID keyword in either the SQLConnect or SQLDriverConnect call.

Default Password

The Default Password value must be the valid Model 204 password for the name entered as the Default User Name in the same data source.

If no Default Password is entered here, the application prompts the user for a valid password at connection time. The Default Password corresponds to the PWD keyword in either the SQLConnect or SQLDriverConnect call.

Default Account

The Default Account value sets the default account character string used by the Model 204 32-bit ODBC driver when establishing a connection to a Model 204 server. The Default Account value can be any account string acceptable to the requested server.

This value is optional. You can assign it to a project, for example, to record the tasks and the number of hours spent. If you omit it, the driver does not pass an Account string to the Model 204 server.

The Default Account corresponds to the ACCOUNT keyword in the SQLDriverConnect call.

Default settings installed with Connect^★ software

Technical Support recommends that non-programmers do not change the following default settings provided with the installed Connect★ software:

• Isolation Level
• Dirty Data Treatment
• Effect of COMMIT

Getting Model 204 data

Before executing an SQL statement, check with your Connect★ administrator to make sure that either the demonstration database is installed and has been defined to the SQL catalog file, CCACAT, or you have other available tables defined in CCACAT. (See SQL catalog population.)

When the Model 204 ODBC driver installation is complete, use Catalog2, an ODBC data source schema browser, to verify that the connection to Model 204 works successfully:

1. Define an ODBC user or system data source before attempting to send a query to Model 204 (see Defining an ODBC data source).
2. In the Connect Star For Model 204 program group under ODBC, click Catalog2.
3. Use File > Open to display the Select Data Source dialog box. Select a defined data source to display all the tables or views in the Model 204 SQL catalog (CCACAT). You can determine the types of tables and information included in the display by selecting Setting in the View menu.
4. Double-click a table or view to display all the columns that are included in the table and information about the columns.

ODBC third-party software

The Model 204 32-bit ODBC driver works with nearly all ODBC 32-bit applications.

Application development using ODBC

If your application requires an ODBC connection, you must register your Model 204 as a data source with the Microsoft ODBC administrator on your workstation.

Use the Microsoft 32-bit ODBC administrator to define a data source after the Model 204 ODBC driver has been installed.

For information on:

• Installing the driver, see Installing the product for ODBC
• Defining a data source, see Defining an ODBC data source

Connect★ for ODBC is multi threaded capable. Connect★ provides a common memory stack that will be shared during access of a multi threaded environment or application.
Connect★ is not multithreading.

If you are a knowledgeable programmer, you can override the default data source settings as described in this chapter.

Overriding data source settings

An ODBC application can override the isolation level by calling the SQLSetConnectOption function with the SQL_TXN_ISOLATION option and setting it explicitly.

You can also override the settings for Isolation Level, Dirty Data Treatment, and Effect of COMMIT fields by passing explicit keyword values to SQLDriverConnect at connect time.

• The ODBC driver can direct SQLDriverConnect to display the SQLDriverConnect dialog box, where the user can enter a password.
• If an ODBC application calls SQLConnect, it can pass an explicit password in the third string argument to SQLConnect. (The three string arguments to SQLConnect are interpreted as DSN, UID, and PWD, respectively.) When SQLConnect is called, the driver looks in the registry to find the Default User Name and Default Password for the specified Data Source Name.

Note: Take care in selecting Isolation Level, Dirty Data Treatment, and Effect of COMMIT. Some ODBC applications are written to expect certain server behavior that is determined by these three settings. Such applications might behave unpredictably if these expectations are not met.
Robust applications, however, usually adjust their expectations to the settings you specify in the data source, because ODBC provides a way for applications to query these settings.

Data source default parameters

SQL programmers who are knowledgeable about these settings might want to change the defaults described here (to affect all queries using the defined data source) or change the settings for individual queries.

Use the Model 204 ODBC Driver -- Configure Data Source dialog box to change data source default parameters that can be accessed by ODBC or by other applications such as Visual Basic or Visual C++.

The specifications for data source parameters are stored in your system's registry under HKEY_LOCAL_MACHINE\SOFTWARE\ODBC\OCBC.INI.

Isolation Level

The Isolation Level parameter sets the default transaction isolation level used by the Model 204 32-bit ODBC driver to establish a connection to a Model 204 server. Isolation Level corresponds to the ISOLATION keyword.

Choose...	If you want to...
Dirty Read	See another user's uncommitted updates. In this case, Model 204 does not lock the selected records.
Cursor Stability (default)	Ensure that you only see updates that have already been committed. In this case, Model 204 locks selected records when a cursor is opened.
Serializable	Have complete control over a table by enforcing strict serializability. This option locks any table that you use during an SQL transaction and prevents other users from accessing those SQL tables. Rocket Software suggests that you use this option rarely, if at all.

Dirty Data Treatment

The Dirty Data Treatment parameter controls the default behavior of SQLFetch when the data contained in a Model 204 record cannot be converted to the SQL data types mapped onto it (this is known as "dirty data"). Dirty Data Treatment corresponds to the DIRTYREC keyword.

Choose...	If you want to...
Server default (default)	Use the behavior specified in the SQLCNVER parameter of the CCAIN stream as the default for the Model 204 SQL Server being accessed.
Error	Make SQLFetch return an error whenever it encounters dirty data.
Skip record	Skip any Model 204 record containing dirty data and try the next. If one or more records are skipped, issue a warning message.

Effect of COMMIT

The Effect of COMMIT parameter controls the behavior of the SQL_COMMIT option of the SQLTransact function.

Choose...	If you want SQL_COMMIT to...
Drop (default)	Close cursors and drop statements.
Preserve	Preserve open cursors and prepared statements.

Note: SQL_ROLLBACK always closes cursors and drops statements.

Debug tracing for ODBC

You can generate a debugging trace log that provides detailed information about your program run. Turn logging on if Technical Support requests it to research a problem. Turn logging off unless you are trying to document a problem. Do not activate logging during volume testing or production runs, because it slows Connect★ significantly.

Warning: The ODBC trace log file is cumulative and can grow to be very large. The log is best used for debugging specific programs with a small sample of data. If you use the log file, periodically erase it or edit out portions that are no longer needed.

Turning logging on and off

To turn logging on and off, do one of the following:

• use the CCACHECK program
• in the Connect Star for Model 204 program group, ODBC folder, Audit subfolder, select one of the following shortcuts:
• Turn Logging Off
• Turn Logging On

Note: Each connection writes its own set of log files with a date/time stamp as part of the file name into the Windows or WINNT folder.

CCACHECK program

The CCACHECK utility logs the following information to the file named LOGFILE.LOG in the same directory where you execute CCACHECK:

• Start and stop date for the program's execution
• Windows operating-system version
• Selected system information
• File and version information for files critical to 32-bit Connect★ ODBC
• Registry keys critical to 32-bit Connect★ ODBC
• Status of logging options

Creating your own shortcuts or overriding the defaults

Turn logging off by executing CCACHECK with the following parameters:

CCACHECK ~ALOGALL ~BOFF

Turn all the logs on by executing CCACHECK with the following parameters:

CCACHECK ~ALOGMSG ~B*IT ~CLOGRX ~DLOGTCP

You can turn on any combination of options, as required. Three logs are written to your Windows or WINNT folder:

• M204OD32.LOG
• M204RX32.LOG
• M204TC32.LOG

When you use the CCACHECK utility with the options listed in the following table, the results are written to the specified logs.

CCACHECK options

CCACHECK Option	Writes the...
~ALOGMSG ~B*It	M204OD32.LOG with Connect★ ODBC trace information. Use both the ~A and ~B options.
~ALANGUSER ~BIBM1047, or ~BJAPAN, or ~BTURKISH, or ~BUS	M204OD32.LOG with the code for the character set you want to use: IBM1047: ASCII characters 0-256 JAPAN: DBCS "Kanji" TURKISH: ASCII characters 0-256, with Turkish syntax US: ASCII characters 0-128
~ATRANSTIME ~B*	M204OD32.LOG with the connection time out value. Zero is the default.
~CLOGRX	M204RX32.LOG with communications information.
~DLOGTCP	M204TC32.LOG with socket level information.

LOGMSG message types

The LOGMSG message types are described in the following table.

LOGMSG message types

e =	Entry and exit from CLI or ODBC functions
x =	Diagnostic information
p =	Parameter values
b =	Bound buffer values
f =	Fetched values
t =	Internal trace
s =	SCFE (communications layer) request block
i =	Time intervals at entry and exit to every CLI/ODBC function and SCFE call

You can also use asterisks to indicate either "log all" or "log all except" as shown in the following table.

Use of asterisks

LOGMSG=	Logs...
*	All message types
ext	Types e, x, and t
*ipf	All message types except i, p, and f