Model 204 SQL Server installation: Difference between revisions

From m204wiki
Jump to navigation Jump to search
(Created page with "==Overview== Model 204 provides industry standard SQL access to Model 204 data through client-server technology. <var class="product">SQL Server overview|Model 204 SQL Serve...")
 
m (link repair)
 
(17 intermediate revisions by 2 users not shown)
Line 1: Line 1:
==Overview==
==Overview==
Model 204 provides industry standard SQL access to Model 204 data through client-server technology. <var class="product">[[SQL Server overview|Model 204 SQL Server]]</var> 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 client-server architecture allows the Model 204 SQL Server to service networked PC clients.  
Model 204 provides industry standard SQL access to Model&nbsp;204 data through client-server technology. <var class="product">[[SQL Server overview|Model 204 SQL Server]]</var> provides full SQL processing in the basic Model&nbsp;204 address space or virtual machine (ONLINE module) in the IBM z/OS, z/VSE, and z/VM operating systems. The [[Connect*|Connect<span class="superstar">&#9733;</span>]] client-server architecture allows the Model&nbsp;204 SQL Server to service networked PC clients.  


Installing <var class="product">SQL Server</var> requires the following actions: downloading the <code>M204PROC</code> file and restoring it to your environment, setting up some subsidiary <var class="product">Model 204</var> files, and verifying that the environment has the appropriate parameter settings.
Installing <var class="product">SQL Server</var> requires the following actions: downloading the <code>M204PROC</code> file and restoring it to your environment, setting up some subsidiary <var class="product">Model&nbsp;204</var> files, and verifying that the environment has the appropriate parameter settings.


Upgrades to <var class="product">SQL Server</var> can almost always be handled by restoring a fresh copy of the <code>M204PROC</code> file.
Upgrades to <var class="product">SQL Server</var> can almost always be handled by restoring a fresh copy of the <code>M204PROC</code> file.
<p class="note"><b>Note:</b>
After Model&nbsp;204 version 7.5, the next <code>M204PROC</code> 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. </p>


==Preparing for Model 204 SQL Server installation==   
==Preparing for Model 204 SQL Server installation==   
Line 14: Line 17:


To download files or maintenance, you must have a registered Rocket user ID. [[Contacting Rocket Software Technical Support|Contact Technical Support]] if you need an ID.
To download files or maintenance, you must have a registered Rocket user ID. [[Contacting Rocket Software Technical Support|Contact Technical Support]] if you need an ID.
<p>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.</p>
<p>
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.</p>


===System requirements===
===System requirements===
Line 21: Line 25:
<p>  
<p>  
In the rest of this document, "Online" refers to a region of
In the rest of this document, "Online" refers to a region of
<var class="product">Model 204</var> running an <var>ONLINE</var> 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.
<var class="product">Model 204</var> running an <var>ONLINE</var> load module containing Version 7.5 or higher. The Horizon product is also required to support connectivity from PC clients into the Model&nbsp;204 host.
</p></li>
</p></li>
</ul>
</ul>
Line 30: Line 34:
<li>Update access to the JCL or EXECs that bring up the host Online.</li>
<li>Update access to the JCL or EXECs that bring up the host Online.</li>
   
   
<li>System Manager privileges, as well as access to all of the SQL Server subsystems and the JCL or EXEC that
<li>System Manager privileges, as well as access to all of the SQL Server [[System requirements for Application Subsystems|application subsystems]] (aka APSYs) and the JCL or EXEC that
runs the Online <var class="product">Model 204</var> region. </li>
runs the Online <var class="product">Model&nbsp;204</var> region. </li>
</ul>
</ul>


===New installation or reinstallation===
===New installation or reinstallation===
<ul>
<ul>
<li>Sites that have never installed <var class="product">SQL Server</var> ''must'' use the [[#new_inst|new installation]], as there are a number of files to allocate and <var class="product">Model 204</var> parameters to update.</li>
<li>Sites that have never installed <var class="product">SQL Server</var> ''must'' use the [[#new_inst|new installation]], as there are a number of files to allocate and <var class="product">Model&nbsp;204</var> parameters to update.</li>


<li>Sites that are reinstalling the latest version of <var class="product">SQL Server</var> would use the [[#Reinstallation for existing SQL Server sites|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.</li>
<li>Sites that are reinstalling the latest version of <var class="product">SQL Server</var> would use the [[#Reinstallation for existing SQL Server sites|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.</li>
Line 42: Line 46:


===Verifying the SQL Server version===
===Verifying the SQL Server version===
You can verify the <var class="product">SQL Server</var> 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:
You can verify the <var class="product">SQL Server</var> 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&nbsp;204 audit trail, as in the example below:
<p class="syntax">M204.0060: Model 204 initialization. Version = 7.5.0H 2015/01/12 13.40,
<p class="syntax">M204.0060: Model 204 initialization. Version = 7.5.0H 2015/01/12 13.40,
RSQL version = 7.5.0H 2014/09/09 15.59</p>
RSQL version = 7.5.0H 2014/09/09 15.59</p>
<div id="new_inst"></div>


==New installation for customers who have never installed SQL Server==
==<b id="new_inst"></b>New installation for customers who have never installed SQL Server==
<p>
<p>The installation steps are as follows:</p>
The installation steps are as follows:</p>
<ol>
<ol>
<li>Allocate the space required for the <code>M204PROC</code> dump data set at 6000 pages.</li>
<li>Allocate the space required for the <code>M204PROC</code> dump data set at 9800 pages (V7.6 or higher) or 6000 pages (V7.5 or lower).</li>
 
<li>Download <code>M204PROC</code> from the Rocket website:
<li>Download <code>M204PROC</code> from the Rocket website:
<ol type="a">
<ol type="a">
<li>In the [https://m204.rocketsoftware.com/ Rocket M204 Customer Care] page of the Rocket website, go to the "Downloads and Uploads" section and select "Download SOUL files". </li>
<li>In the [https://m204.rocketsoftware.com/ Rocket M204 Customer Care] page of the Rocket website, go to the "Downloads and Uploads" section and select "Download SOUL files". </li>
<li>After providing your registered customer login information, download the latest version of the <code>M204PROC</code> file. This will be in the form of an M204PROC file dump.  
 
<li>After providing your registered customer login information, download the appropriate version of the <code>M204PROC</code> file dump:
<ul>
<li>For Model&nbsp;204 version 7.5, this is the <code>M204PROC</code> <code>V7.5</code> file found in the <code>M204PROC</code> "Product" row. </li>
<li>After Model&nbsp;204 version 7.5, this is the <code>M204PROC</code> file found in the <code>RKTools</code> "Product" row. </li>
</ul>
<p>
<p>
A "Click here" link above the product list provides Help on downloading, file formats, file sizing, uploading, and more.</p></li>
A "Click here" link above the product list provides Help on downloading, file formats, file sizing, uploading, and more.</p></li>
<li>Upload the M204PROC dump as binary into the newly allocated dump data set.</li>
 
<li>Upload the dump file as binary into the newly allocated dump data set.</li>
</ol>
</ol>
</li>
</li>


<li>Allocate the SQL Server supporting files:</li> 
<li>Allocate the SQL Server supporting files:  
<table class="thJustBold">
<table class="thJustBold">
<tr><th>M204PROC</th>
<td>9800 pages for V7.6 or higher <br>6000 pages for V7.5 or lower </td></tr>


<tr><th>M204PROC</th><td>6000 pages</td></tr>
<tr><th>TSFDATA</th>
<tr><th>TSFDATA
<td>200 pages</td></tr>
</th><td>200 pages</td></tr>


<tr><th nowrap>CCACAT</th><td>700 pages</td></tr>  
<tr><th nowrap>CCACAT</th>
<td>700 pages</td></tr>  


<tr><th>TSFTEMP</th>
<tr><th>TSFTEMP</th>
<td>300 pages</td></tr>
<td>300 pages</td></tr>
 
</table></li>  
</table>


<li>Update the Online JCL or EXEC:
<li>Update the Online JCL or EXEC:
<ul>
<ul>
<li>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. </li>
<li>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. </li>
<li>Verify that the Online environment allows the use of APSY subsystems. To use APSYs, the CCASYS file must be available and SYSOPT setting must include the 1 bit.</li>
<li>Verify that the Online environment allows the use of APSY subsystems. To use APSYs, the [[System requirements for Application Subsystems#Overview of CCASYS|CCASYS file]] must be available, and the <var>[[SYSOPT parameter|SYSOPT]]</var> setting must include the 1 bit.</li>
</ul>
</ul>


Line 85: Line 97:
<ul>
<ul>
<li>Set CCAIN server table parameters to support SQL Server processing.</li>
<li>Set CCAIN server table parameters to support SQL Server processing.</li>
<li>Increase <var>[[NFILES parameter|NFILES]]</var>, <var>[[NDCBS parameter|NDCBS]]</var>, and <var>[[NDIR parameter|NDIR]]</var> for the SQL Server files. </li>
<li>Increase <var>[[NFILES parameter|NFILES]]</var>, <var>[[NDCBS parameter|NDCBS]]</var>, and <var>[[NDIR parameter|NDIR]]</var> for the SQL Server files. </li>
<li>Verify that the <var>[[NORQS parameter|NORQS]]</var> setting is at least 5.</li>
<li>Verify that the <var>[[NORQS parameter|NORQS]]</var> setting is at least 5.</li>
<li>Review the [[#SQL Server size|server size section]] below for specific requirements of individual APSY components of SQL Server.</li>
<li>Review the [[#SQL Server size|server size section]] below for specific requirements of individual APSY components of SQL Server.</li>
</ul>
</ul>
</li>
</li>


<li>Ensure that the Dictionary files METADATA, DATALINK, and CCASYS are available to the run.</li>
<li>Ensure that the [[Dictionary/204 overview|Dictionary]] files METADATA, DATALINK, and CCASYS are available to the run.</li>
 
<li>Start the Online, using an <var>ONLINE</var> load module that contains <var class="product">Model 204 Version 7.5 or higher</var>. </li>
<li>Start the Online, using an <var>ONLINE</var> load module that contains <var class="product">Model 204 Version 7.5 or higher</var>. </li>


Line 99: Line 115:
OPEN FILE M204PROC
OPEN FILE M204PROC
RESTORE 192 FROM dumpfile
RESTORE 192 FROM dumpfile
</p>
</p></li>


<li>To install SQL Server,
<li>To install SQL Server,
open file <code>M204PROC</code> and execute the following command to define the SQL server APSYs, CCACATREPT and CCATSF:
open file <code>M204PROC</code> and execute the following command to define the SQL server APSYs, CCACATREPT and CCATSF:
<p class="code">INCLUDE SQLINST</p>
<p class="code">INCLUDE SQLINST</p>
<p>Follow the instructions presented on the installation screen.</p>
<p>
<p>If you wish, set the <var class="product">SQL Server</var> APSYs to <code>AUTOSTART</code>.</p></li>
Follow the instructions presented on the installation screen.</p>
<p>
If you want, set the <var class="product">SQL Server</var> APSYs to <code>AUTOSTART</code>.</p></li>


<li>Verify success of the installation by defining the SQL Link/ProcessGroup/Process and threads and then using the Connect<span class="superstar">&#9733;</span> product suite to connect to the ONLINE and issue SQL queries against the DEMO database. These steps are described in [[#Verifying the installation|Verifying the installation]]. </li>
<li>Verify success of the installation by defining the SQL Link/ProcessGroup/Process and threads, then using the [[Connect*|Connect<span class="superstar">&#9733;</span>]] product suite to connect to the Online and issue SQL queries against the DEMO database. These steps are described in [[#VerifyInst|Verifying the installation]]. </li>
</ol>
</ol>


==Reinstallation for existing SQL Server sites==
==Reinstallation for existing SQL Server sites==
You can verify your <var class="product">SQL Server</var> version as described in [[#Verifying the SQL Server version|Verifying the SQL Server version]].  
You can verify your <var class="product">SQL Server</var> version as described in [[#Verifying the SQL Server version|Verifying the SQL Server version]].  
<p>The reinstallation steps are as follows:</p>
<p>
The reinstallation steps are as follows:</p>


<ol>
<ol>
<li>Allocate the space required for the <code>M204PROC</code> dump data set at 6000 pages.</li>
<li>Allocate the space required for the <code>M204PROC</code> dump data set at 9800 pages (V7.6 or higher) or 6000 pages (V7.5 or lower).</li>
 
<li>Allocate space for TSFTEMP, if not already done.
<li>Allocate space for TSFTEMP, if not already done.
<p>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.</p>
<p>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.</p></li>
 
<li>Download <code>M204PROC</code> from the Rocket website:
<li>Download <code>M204PROC</code> from the Rocket website:
<ol type="a">
<ol type="a">
<li>In the [https://m204.rocketsoftware.com/ Rocket M204 Customer Care] page of the Rocket website, go to the "Downloads and Uploads" section and select "Download SOUL files". </li>
<li>In the [https://m204.rocketsoftware.com/ Rocket M204 Customer Care] page of the Rocket website, go to the "Downloads and Uploads" section and select "Download SOUL files". </li>
<li>After providing your registered customer login information, download the latest version of the <code>M204PROC</code> file. This will be in the form of an M204PROC file dump.
 
<li>After providing your registered customer login information, download the appropriate version of the <code>M204PROC</code> file dump:
<ul>
<li>For Model&nbsp;204 version 7.5, this is the <code>M204PROC</code> <code>V7.5</code> file found in the <code>M204PROC</code> "Product" row. </li>
<li>After Model&nbsp;204 version 7.5, this is the <code>M204PROC</code> file found in the <code>RKTools</code> "Product" row. </li>
</ul>
<p>
<p>
A "Click here" link above the product list provides Help on downloading, file formats, file sizing, uploading, and more.</p></li>
A "Click here" link above the product list provides Help on downloading, file formats, file sizing, uploading, and more.</p></li>
<li>Upload the M204PROC dump as binary into the newly allocated dump data set.</li>
 
<li>Upload the dump file as binary into the newly allocated dump data set.</li>
</ol>
</ol>
</li>
</li>
Line 130: Line 157:
<li>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.</li>
<li>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.</li>


<li>Ensure that the Dictionary files METADATA, DATALINK, and CCASYS are available to the run.</li>
<li>Ensure that the [[Dictionary/204 overview|Dictionary]] files METADATA, DATALINK, and CCASYS are available to the run.</li>


<li>Start the Online, using an <var>ONLINE</var> load module that contains <var class="product">Model 204 Version 7.5 or higher</var>. </li>
<li>Start the Online, using an <var>ONLINE</var> load module that contains <var class="product">Model 204 Version 7.5 or higher</var>. </li>
Line 138: Line 165:
END
END
OPEN FILE M204PROC
OPEN FILE M204PROC
RESTORE 192 FROM dumpfile
RESTORE 192 FROM dumpfile </p>
</p>
</li>
</li>


Line 145: Line 171:
<p class="code">INCLUDE SQLRINST</p>
<p class="code">INCLUDE SQLRINST</p>
This command will upgrade the SQL catalog, CCACAT, and reinstall the SQL server APSYs, CCACATREPT and CCATSF.  
This command will upgrade the SQL catalog, CCACAT, and reinstall the SQL server APSYs, CCACATREPT and CCATSF.  
<p>Follow the instructions presented on the installation screen.</p>
<p>
<p>If you wish, set the <var class="product">SQL Server</var> APSYs to <code>AUTOSTART</code>.</p></li>
Follow the instructions presented on the installation screen.</p>
<li>Verify success of the installation by defining the SQL Link/ProcessGroup/Process and threads and then using the Connect<span class="superstar">&#9733;</span> product suite to connect to the ONLINE and issue SQL queries against the DEMO database. These steps are described in [[#Verifying the installation|Verifying the installation]]. </li>
<p>
If you want, set the <var class="product">SQL Server</var> APSYs to <code>AUTOSTART</code>.</p></li>
 
<li>Verify success of the installation by defining the SQL Link/ProcessGroup/Process and threads, then using the [[Connect*|Connect<span class="superstar">&#9733;</span>]] product suite to connect to the Online and issue SQL queries against the DEMO database. These steps are described in [[#VerifyInst|Verifying the installation]]. </li>
</ol>
</ol>


==SQL Server size==
==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.
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&nbsp;204 region, and because of slight differences between <var>[[UTABLE command|UTABLE]]</var> requirements in different versions of Model&nbsp;204.


<table>
<table class="thJustBold">
<tr class="head"><th>Subsystem name</th><th>Minimum server size</th></tr>
<tr class="head"><th>Subsystem name</th><th>Minimum server size</th></tr>


<tr class="head"><th>CCACATREPT</th><td>250000</td></tr>
<tr><th>CCACATREPT</th>
 
<td>250000</td></tr>
<tr class="head"><th>CCATSF</th><td>165000</td></tr>


<tr><th>CCATSF</th>
<td>165000</td></tr>
</table>
</table>


==Verifying the installation==
==<b id="VerifyInst"></b>Verifying the installation (IBM z/OS only)==
To verify that the SQL Server installation or reinstallation was successful, enabling you to connect to the Online and access data, perform the following steps:
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.
<ol>
<ol>
<li>Define the SQL Link, ProcessGroup, and Process to the Online. <br />An example with minimal security settings is shown here:
<li>Define the SQL [[SQL network entities|Link, ProcessGroup, and Process]] to the Online. <br />An example with minimal security settings is shown here:
<p class="code">***  DEFINE SQL LINK LINKTCP ***  
<p class="code">***  DEFINE SQL LINK LINKTCP ***  
                          
                          
Line 176: Line 206:
LOGIN=NOTRUST GUESTUSER=REJECT LOOKUP=ANY       
LOGIN=NOTRUST GUESTUSER=REJECT LOOKUP=ANY       
            
            
DEFINE PROCESS CCARSQL WITH SCOPE=SYSTEM DATALEN=32752 -
DEFINE PROCESS CCARSQL WITH SCOPE=SYSTEM DATALEN=32752 FROM=(T1)         
  FROM=(T1)         
                  
                  
OPEN LINK LINKTCP</p>
OPEN LINK LINKTCP</p></li>
</li>
 
<li>Define IODEV=19, SQL threads, or IODEV=49, RCL threads.
<li>Define IODEV=19, SQL threads, or IODEV=49, RCL threads.
<p class="code" style="font-size:95%"><nowiki>****************************************************************************************
<p class="code" style="font-size:95%"><nowiki>***********************************************************************************
*  Define the SQL and RCL IODEVs                                                       *
*  Define the SQL and RCL IODEVs                                                 *
*  You may define up to 2 SQL and 2 RCL threads without any CPU authorization required.*
*  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                             *
*  Each SQL or RCL connection will use one of these IODEVs                       *
****************************************************************************************
***********************************************************************************


IODEV=19,POLLNO=1,NOTHREAD=2,TIMEOUT=0,INMRL=500,LHEAP=200000,        X
IODEV=19,POLLNO=1,NOTHREAD=2,TIMEOUT=0,INMRL=500,LHEAP=200000,        X
Line 193: Line 222:
IODEV=49,POLLNO=1,NOTHREAD=2,TIMEOUT=0,INMRL=500,DUPTERM=1 </nowiki></p>
IODEV=49,POLLNO=1,NOTHREAD=2,TIMEOUT=0,INMRL=500,DUPTERM=1 </nowiki></p>
</li>
</li>
<li>Bring up the Online and verify that the link opened.</li>
<li>Bring up the Online and verify that the link opened.</li>
<li>Install one or more of the Connect<span class="superstar">&#9733;</span> connectivity drivers:
<li>Install one or more of the Connect<span class="superstar">&#9733;</span> connectivity drivers:
<ul>
<ul>
<li>[http://m204wiki.rocketsoftware.com/index.php/Connect*_for_ODBC Connect<span class="superstar">&#9733;</span> for ODBC]</li>
<li>[[Connect* for ODBC|Connect<span class="superstar">&#9733;</span> for ODBC]]</li>
<li>[http://m204wiki.rocketsoftware.com/index.php/Connect*_for_JDBC Connect<span class="superstar">&#9733;</span> for JDBC]</li>
<li>[[Connect* for JDBC|Connect<span class="superstar">&#9733;</span> for JDBC]]</li>
<li>[http://m204wiki.rocketsoftware.com/index.php/Connect*_for_.NET_Framework Connect<span class="superstar">&#9733;</span> for .NET Framework]</li>
<li>[[Connect* for .NET Framework|Connect<span class="superstar">&#9733;</span> for .NET Framework]]</li>
</ul>
</ul></li>
<li>Install the [http://m204wiki.rocketsoftware.com/index.php/Connect*_to_Model_204_connection#Installing_the_Connect.E2.98.85_Visual_Interface_.28CVI.29 CVI].</li>
 
<li>[http://m204int.rocketsoftware.com/index.php/Downloading_and_creating_the_Model_204_DEMO_database_files Download and create] the Model 204 DEMO database files.</li>
<li>Install the [[Connect* to Model 204 connection#Installing the Connect.E2.98.85 Visual Interface (CVI)|CVI]].</li>
<li>Use the CVI to [http://m204wiki.rocketsoftware.com/index.php/Connect*_to_Model_204_connection#SQL_catalog_population 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: <br />
<li>[[Downloading and creating the Model 204 DEMO database files (IBM z/OS only)|Download and create]] the Model 204 DEMO database files.</li>
<code>C:\Program Files (x86)\CCA\Connect Star for Model 204\CVI</code></li>  
 
<li>Use the CVI to [[Connect* to Model 204 connection#SQL catalog population|populate the SQL catalog]] (CCACAT) with the tables mapping the DEMO Database.
The <code>demotab.ddl</code> file contains the DEMO database SQL table definitions. Use this file for input to the CVI. It is located in the CVI installation directory: <br />
<code>C:\Program Files (x86)\CCA\Connect Star for Model 204\CVI</code></li>
<li>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:                     
<li>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:                     
<p class="code">SELECT COUNT (*) from DEMO.CLIENTS</p>
<p class="code">SELECT COUNT (*) from DEMO.CLIENTS</p>
</li>
</li>
</ol>
</ol>
==See also==
{{Template:SQL topic list}}
[[Category:Model 204 SQL processing]]

Latest revision as of 22:29, 27 April 2018

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 nameMinimum 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:
  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

Retrieved from "https://m204wiki.rocketsoftware.com/index.php?title=Model_204_SQL_Server_installation&oldid=115052"