Model 204 SQL Server installation

Overview

Model 204 provides industry standard SQL access to Model 204 data through client-server technology. Model 204 SQL Server provides full SQL processing in the basic Model 204 address space or virtual machine (ONLINE module) in the IBM z/OS, z/VSE, and z/VM operating systems. The Connect★ client-server architecture allows the Model 204 SQL Server to service networked PC clients.

Installing SQL Server requires the following actions: downloading the M204PROC file and restoring it to your environment, setting up some subsidiary Model 204 files, and verifying that the environment has the appropriate parameter settings.

Upgrades to SQL Server can almost always be handled by restoring a fresh copy of the M204PROC file.

Note: After Model 204 version 7.5, the next M204PROC file upgrade is part of the RKTools version 7.7 product family, under which it is downloaded. RKTools 7.7 and higher offers access to the SQL Server DDL-generation tools CCACATREPT and CCATSF from the RKTools TN3270 main menu and from the RKWeb interface.

Preparing for Model 204 SQL Server installation

The code for the Model 204 SQL Server product resides in the file named M204PROC, which is typically downloaded from the Rocket Software website.

Product installation files

The files necessary for the Model 204 SQL Server installation are available from the Rocket M204 Customer Care area of the Rocket Software website.

To download files or maintenance, you must have a registered Rocket user ID. Contact Technical Support if you need an ID.

The website will show only products for which your organization is up to date on maintenance or rental payments. If a product does not appear on your download page and you believe it should, contact Technical Support.

System requirements

• SQL Server operates in a Model 204 Online region.

  In the rest of this document, "Online" refers to a region of Model 204 running an ONLINE load module containing Version 7.5 or higher. The Horizon product is also required to support connectivity from PC clients into the Model 204 host.

User privileges

The installing user must have:

• Update access to the JCL or EXECs that bring up the host Online.
• System Manager privileges, as well as access to all of the SQL Server application subsystems (aka APSYs) and the JCL or EXEC that runs the Online Model 204 region.

New installation or reinstallation

• Sites that have never installed SQL Server must use the new installation, as there are a number of files to allocate and Model 204 parameters to update.
• Sites that are reinstalling the latest version of SQL Server would use the reinstallation. If you are upgrading from a previous version or maintenance level, your site already has the SQL Server files that contain information about your environment.

Verifying the SQL Server version

You can verify the SQL Server version by checking the version number on the top right corner of any SQL Server APSY. The SQL version is also displayed in the text of the M204.0060 message that appears at the beginning of a Model 204 audit trail, as in the example below:

M204.0060: Model 204 initialization. Version = 7.5.0H 2015/01/12 13.40, RSQL version = 7.5.0H 2014/09/09 15.59

New installation for customers who have never installed SQL Server

The installation steps are as follows:

1. Allocate the space required for the M204PROC dump data set at 9800 pages (V7.6 or higher) or 6000 pages (V7.5 or lower).
2. Download M204PROC from the Rocket website:
   1. In the Rocket M204 Customer Care page of the Rocket website, go to the "Downloads and Uploads" section and select "Download SOUL files".
   2. After providing your registered customer login information, download the appropriate version of the M204PROC file dump:
      • For Model 204 version 7.5, this is the M204PROC V7.5 file found in the M204PROC "Product" row.
      • After Model 204 version 7.5, this is the M204PROC file found in the RKTools "Product" row.

      A "Click here" link above the product list provides Help on downloading, file formats, file sizing, uploading, and more.

   3. Upload the dump file as binary into the newly allocated dump data set.
3. Allocate the SQL Server supporting files:

   M204PROC	9800 pages for V7.6 or higher 6000 pages for V7.5 or lower
   TSFDATA	200 pages
   CCACAT	700 pages
   TSFTEMP	300 pages

4. Update the Online JCL or EXEC:
   • Add the DD cards, FILEDEFs or DLBLs for the SQL Server files and the M204PROC dump data set, or dynamically allocate the files to the run.
   • Verify that the Online environment allows the use of APSY subsystems. To use APSYs, the CCASYS file must be available, and the SYSOPT setting must include the 1 bit.
5. Update CCAIN parameters:
   • Set CCAIN server table parameters to support SQL Server processing.
   • Increase NFILES, NDCBS, and NDIR for the SQL Server files.
   • Verify that the NORQS setting is at least 5.
   • Review the server size section below for specific requirements of individual APSY components of SQL Server.
6. Ensure that the Dictionary files METADATA, DATALINK, and CCASYS are available to the run.
7. Start the Online, using an ONLINE load module that contains Model 204 Version 7.5 or higher.
8. Restore the M204PROC dump into M204PROC using the following commands:

   CREATE M204PROC END OPEN FILE M204PROC RESTORE 192 FROM dumpfile

9. To install SQL Server, open file M204PROC and execute the following command to define the SQL server APSYs, CCACATREPT and CCATSF:

   INCLUDE SQLINST

   Follow the instructions presented on the installation screen.

   If you want, set the SQL Server APSYs to AUTOSTART.

10. Verify success of the installation by defining the SQL Link/ProcessGroup/Process and threads, then using the Connect★ product suite to connect to the Online and issue SQL queries against the DEMO database. These steps are described in Verifying the installation.

Reinstallation for existing SQL Server sites

You can verify your SQL Server version as described in Verifying the SQL Server version.

The reinstallation steps are as follows:

1. Allocate the space required for the M204PROC dump data set at 9800 pages (V7.6 or higher) or 6000 pages (V7.5 or lower).
2. Allocate space for TSFTEMP, if not already done.

   TSFTEMP is a new file from version 7.5 onwards, so if SQL Server hasn't previously been installed under Model 204 version 7.5 or above, you will need to allocate TSFTEMP with 300 pages.

3. Download M204PROC from the Rocket website:
   1. In the Rocket M204 Customer Care page of the Rocket website, go to the "Downloads and Uploads" section and select "Download SOUL files".
   2. After providing your registered customer login information, download the appropriate version of the M204PROC file dump:
      • For Model 204 version 7.5, this is the M204PROC V7.5 file found in the M204PROC "Product" row.
      • After Model 204 version 7.5, this is the M204PROC file found in the RKTools "Product" row.

      A "Click here" link above the product list provides Help on downloading, file formats, file sizing, uploading, and more.

   3. Upload the dump file as binary into the newly allocated dump data set.
4. Ensure that the existing SQL Server files (M204PROC, CCACAT, TSFDATA, and TSFTEMP) are available to the run, as well as the new M204PROC dump data set.
5. Ensure that the Dictionary files METADATA, DATALINK, and CCASYS are available to the run.
6. Start the Online, using an ONLINE load module that contains Model 204 Version 7.5 or higher.
7. Restore the M204PROC dump into the M204PROC file. Because you are upgrading from a previous release, you can simply overlay your old M204PROC file. However, the default privileges for M204PROC require that you re-create the file as follows:

   CREATE M204PROC END OPEN FILE M204PROC RESTORE 192 FROM dumpfile

8. To install SQL Server, open file M204PROC, and execute the following command:

   INCLUDE SQLRINST

   This command will upgrade the SQL catalog, CCACAT, and reinstall the SQL server APSYs, CCACATREPT and CCATSF.

   Follow the instructions presented on the installation screen.

   If you want, set the SQL Server APSYs to AUTOSTART.

9. Verify success of the installation by defining the SQL Link/ProcessGroup/Process and threads, then using the Connect★ product suite to connect to the Online and issue SQL queries against the DEMO database. These steps are described in Verifying the installation.

SQL Server size

The minimum SQL Server size requirements are listed below. The actual server size requirements might vary slightly because of server table settings in the local Online Model 204 region, and because of slight differences between UTABLE requirements in different versions of Model 204.

Subsystem name	Minimum server size
CCACATREPT	250000
CCATSF	165000

Verifying the installation (IBM z/OS only)

If you are on an IBM z/OS system, you can use the following steps to verify that the SQL Server installation or reinstallation was successful, enabling you to connect to the Online and access data.

1. Define the SQL Link, ProcessGroup, and Process to the Online.
   An example with minimal security settings is shown here:

   *** DEFINE SQL LINK LINKTCP *** DEFINE LINK LINKTCP WITH SCOPE=SYSTEM TRANSPORT=TCPSE - PROTOCOL=IP CONNECTIONS=10 INBUFSIZE=4096 LOCALID=ANY - SERVPORT=nnnn LOOKUP=ANY DEFINE PROCESSGROUP T1 WITH SCOPE=SYSTEM LINK=LINKTCP - REMOTEID=0.0.0.0 MASK=0.0.0.0 INLIMIT=10 - LOGIN=NOTRUST GUESTUSER=REJECT LOOKUP=ANY DEFINE PROCESS CCARSQL WITH SCOPE=SYSTEM DATALEN=32752 FROM=(T1) OPEN LINK LINKTCP

2. Define IODEV=19, SQL threads, or IODEV=49, RCL threads.

   *********************************************************************************** * Define the SQL and RCL IODEVs * * You may define up to 2 SQL and 2 RCL threads without needing CPU authorization * * Each SQL or RCL connection will use one of these IODEVs * *********************************************************************************** IODEV=19,POLLNO=1,NOTHREAD=2,TIMEOUT=0,INMRL=500,LHEAP=200000, X SQLBUFSZ=400000,LVTBL=4000,DUPTERM=1 IODEV=49,POLLNO=1,NOTHREAD=2,TIMEOUT=0,INMRL=500,DUPTERM=1

3. Bring up the Online and verify that the link opened.
4. Install one or more of the Connect★ connectivity drivers:
   • Connect★ for ODBC
   • Connect★ for JDBC
   • Connect★ for .NET Framework
5. Install the CVI.
6. Download and create the Model 204 DEMO database files.
7. Use the CVI to populate the SQL catalog (CCACAT) with the tables mapping the DEMO Database. The demotab.ddl file contains the DEMO database SQL table definitions. Use this file for input to the CVI. It is located in the CVI installation directory:
   C:\Program Files (x86)\CCA\Connect Star for Model 204\CVI
8. Verify the installation by using the CVI to do a simple SELECT against a file, such as the CLIENTS file in the DEMO database. For example:

   SELECT COUNT (*) from DEMO.CLIENTS

See also

• SQL Server overview
• SQL Server installation
• SQL catalog
• SQL representation of Model 204 data
• SQL Data Definition Language (DDL)
• SQL DDL from the Table Specification Facility
• SQL catalog reporting and querying
• SQL Data Manipulation Language (DML)
• SQL DDL syntax
• SQL reserved words
• SQL DDL mapping of the demonstration database