StarQuest Technical Documents

Using SQDR Plus with IBM i IASPs (Independent Auxiliary Storage Pools)

Last Update: 14 February 2018
Product: SQDR Plus
Version: 4.2 & later
Article ID: SQV00PL017

Abstract

An independent disk pool, or independent auxiliary storage pool (IASP), is a collection of disk units that can be brought online or taken offline, independent of the rest of the storage on a system. IASPs may be implemented for various reasons, including workload balancing, isolation of historical and archived data, multiple application versions, and creation of test and development environments.

This technical document describes the configuration necessary for SQDR and SQDR Plus to access tables located on an IASP.

• Task performed on the IBM i host (tier 1)
• Tasks peformed with SQDR Control Center (tier 2)
• Tasks performed with Data Replicator Manager (tier 3) & StarAdmin when using StarSQL ODBC driver
• Tasks performed with Data Replicator Manager (tier 3) when using iAccess ODBC driver
• Additional Information

Solution

This procedure assumes that you have already configured a SQDR Plus agent to the IBM i source system - i.e. the agent user SQDR and agent schema SQDR already exist on the IBM i system. It is expected that the agent schema will reside in *SYSBAS rather than the IASP.

We recommend that the source tables already be journaled, as the SQDR Plus autojournaling function has not been verified in an IASP environment.

Perform the following tasks on the IBM i host:

1. Make sure that the IASP is varied on; vary it on if not.

WRKCFGSTS CFGTYPE(*DEV) CFGD(*ASP)
Opt  Description Status
_    IASP1       AVAILABLE

2. After the IASP is varied on, use DSPRDBDIRE and confirm that an RDB entry for the ASP exists:

Remote
Option Entry Location Text
_      IASP1 LOOPBACK   Entry added by system

3. Create a new job description (JOBD) and configure the Initial ASP group parameter:

CRTDUPOBJ OBJ(QDFTJOBD) FROMLIB(QGPL) OBJTYPE(*JOBD) TOLIB(SQDR)
CHGJOBD JOBD(SQDR/QDFTJOBD) INLASPGRP(IASP1)

4. Modify the agent user (SQDR) to use the new job description

CHGUSRPRF USRPRF(SQDR) JOBD(SQDR/QDFTJOBD)

5. If you plan to use the Publications feature of SQDR Plus, modify the user profile of the privileged user that will be used to create the publication:

CHGUSRPRF USRPRF(MYSECOFR) JOBD(SQDR/QDFTJOBD)

6. On the SQDR Plus machine, temporarily stop the SQDR Plus agent (with SQDR Control Center) and the SQDR-Jetty service (with the Services control panel) and confirm that there are no existing connections:

WRKOBJLCK SQDR *USRPRF

Perform the following tasks with SQDR Control Center (web-based manager for SQDR Plus):

1. Select the agent
2. Select the Configuration Settings sliding panel
3. Edit the SourceDbUrl, adding ;database name=iaspname

jdbc:as400://myas400;prompt=false;database name=IASP1

4. Select the Save icon; this will restart the agent.
   

Perform the following tasks with Data Replicator Manager & StarAdmin (using StarSQL):

Before using StarSQL to access tables on the IASP, bind StarSQL packages with StarAdmin, using a userID with sufficient authority to bind packages and grant access, and whose profile has been modified to use the new JOBD.

For instance, you may choose to use SQDRADM:

CHGUSRPRF USRPRF(SQDRADM) JOBD(SQDR/QDFTJOBD)

Use StarAdmin to bind packages in a library called SQDRIASP1; this library can exist in either *SYSBAS or the IASP.

Configure the StarSQL source with the following connection string:

HostName=myhostip;Port=446;Server=IASP1;IsolationLevel=2;PkgColID=SQDRIASP1

Or, if you are using an ODBC data source rather than a connection string, set the Database Name to IASP1 and the Package Collection to SQDRIASP1.

When creating the source in Data Replicator Manager, supply the credentials for a userID whose profile has been configured to use the new *JOBD.

Perform the following tasks with Data Replicator Manager (using iAccess ODBC driver):

Configure a SQDR source using the IBM iAccess ODBC driver, specifying the database, using either a Connection String or an ODBC data source:

• Using a Connection String:

System=MYAS400;DefaultLibraries={QGPL,SQDR};Database=IASP1

• Using an ODBC data source:

In ODBC Administrator, select the ODBC data source. On the Server panel, add SQDR to the Library list and select the checkbox Override default database with the following: and enter the database name (e.g. IASP1).

Additional Information:

Useful commands:

• WRKDSKSTS - use this command to display available disk units that can be used to create an IASP.
• CFGDEVASP ASPDEV(IASP1) ACTION(*CREATE) and use *SELECT to pick the new disk unit.
• WRKCFGSTS CFGTYPE(*DEV) CFGD(*ASP) - check the status of the IASP and vary on if necessary (e.g. after an IPL).
• DSPRDBDIRE - the name of the IASP should appear here.
• SETASPGRP ASPGRP(IASP1) - use the IASP in the current thread.
• SETASPGRP ASPGRP(*CURUSR) - use the value defined for the Initial ASP group (INLASPGRP) in the default job description of the user profile.
• CRTLIB LIB(ASPLIB) ASPDEV(IASP1) - create a library in the IASP. Note that you can create this library even if the current job is not configured to use the IASP, but it will not be visible until you invoke SETASPGRP ASPGRP(IASP1).

For further information, see the IBM Redbook SG24-7811: IBM i 6.1 Independent ASPs: A Guide to Quick Implementation of Independent ASPs.