Stelo Technical Documents

SQDR Plus: Using PowerShell

Product: SQDR Plus
Version: 6.2 and later
Article ID: SQV00PL078
Date: 2024/03/10

Abstract

This technical document describes Stelo Capture (SQDR Plus aka Tier 2) tasks that can be controlled programmatically using PowerShell. These tasks include listing, creating, starting, stopping, and deleting the Capture Agents that retrieve change information from source (Tier 1) databases.

See Automating SQDR tasks using PowerShell for information about using PowerShell to automate Stelo Apply (SQDR aka Tier 3) tasks such as running a snapshot subscription or pausing an incremental subscription group.

This information is subject to change in future versions of SQDR. Note that parameters such as Agent name may be case sensitive.

Solution

• Prerequisites
  
• Using the PowerShell assembly
  
• Examples
  
• Creating an Agent
  
• Modifying an Agent
  
• Output of Show-Agent(s)
  
• Remote Servers
• Troubleshooting
  
• Known Issues and Caveats

Prerequisites

• PowerShell 5.1 or later
  

Using the PowerShell assembly

1. Download, unblock, and expand CapturePowerShell.zip.
2. On Windows, create a directory called C:\sqdr
3. After expanding the CapturePowerShell.zip, take the Tools folder in the CapturePowerShell folder and move it to C:\sqdr
4. Open a PowerShell Window
5. Run Import-Module 'C:\sqdr\Tools\CapturePowerShell\CapturePowerShell.dll'
6. Run Get-Command -Module CapturePowerShell to list the cmdlets that are currently implemented

These are the cmdlets currently implemented:

• Request-Login
• Set-Agent
• Show-Agent
  
• Show-Agents
• Show-AgentProperties
• Edit-AgentProperties
• Set-AgentProperties
  
• Start-Agent
  
• Stop-Agent
• Remove-Agent
  

Use get-help to get help for any of the cmdlets e.g.:
PS> get-help Show-Agent

NAME
Show-Agent

SYNTAX
Show-Agent [[-BaseUrl] <string>] [-Name] <string> [[-JsonFileOut] <string>] [<CommonParameters>]

Cmdlets that return information such as Show-Agent, Show-Agents and Show-AgentProperties will send JSON output either to the console screen or to a specified output file.

Request-Login is required to establish connectivity.

Examples

Here is a simple example that connects to a local Stelo Capture (SQDR Plus) instance and displays basic information about all agents:

Import-Module 'C:\sqdr\Tools\CapturePowerShell\CapturePowerShell.dll'
$baseUrl = 'http://localhost:8080'
Request-Login -BaseUrl $baseUrl -UserId 'SQDR' -Password 'sqdrplus'
Show-Agents -BaseUrl $baseUrl

Show-Agent shows basic information about an individual agent; Show-AgentProperties shows all properties of an agent; both can output to a JSON file. Set-AgentProperties will totally replace the contents of the properties file and should be used only under direction of Stelo Support.

The Examples directory contains sample PowerShell scripts and JSON input files for creating, deleting, starting and stopping agents for all supported source DBMS types.

Creating an Agent

Before using a PowerShell script to create an agent, we recommend creating at least one agent using Control Center and the Add Agent Wizard, as directed by the Quick Start Guide applicable to your environment, to become familiar with the parameters that you will need to configure and to confirm that the prerequisites for the source system are met.

Copy the PowerShell script and JSON file appropriate for your DBMS type, rename them, and modify them for your environment.

In the PowerShell script:

• Change the location used by Import-Module if necessary
  
• change baseUrl if you are using a remote instance of Stelo Capture.
  
• change outDir and dataDir if the scripts if necessary.
  
• change the agentName. The default name is typically databasename%schema@host and is case-sensitive. After the agent is created, the name can be changed using the Name property; you can also specify the Name property in the JSON used when creating the agent.
  
• Change the password on the Request-Login command if necessary.
  
• Comment or uncomment the functions that you want to perform.

In the JSON input file:

• Change the source database parameters in the dbConfig block.
  
• Change the local Db2 LUW parameters in the controlDbConfig block. To use an existing control database (e.g. SQDRP3), begin the ControlDbConfig block with "controlDb":"SQDRP3". The user, password, and port parameters in this block are ignored but must be present.
  
• Change notification parameters if desired.
• Add the name parameter to the properties block if desired.

Modifying an Agent

Edit-AgentProperties can be used to update or add properties for an existing agent.

For example, to set udbReadLogUsingSP=true (recommended for a Db2 LUW agent), change notificationLevel to INFO, and rename the agent, create a JSON file file edit-agent-properties.json containing:

[
[
"udbReadLogUsingSP",
"true"
],
[
"notificationLevel",
"INFO"
],
[
"name",
"MyAgent"
]
]

and run the following:

$baseUrl = 'http://localhost:8080'
$dataDir = 'C:\sqdr\Tools\examples\data\'
$agentName = 'mydb@myhost'

Edit-AgentProperties -BaseUrl $baseUrl -Name $agentName -JsonFile $dataDir'edit-agent-properties.json'

If the agent was running, it will be restarted.

Output of Show-Agent(s)

Most of the output of Show-Agent and Show-Agents requires no explanation.

The value for State can be one of:

1 Running
2 Stopped
3 Disabled
4 Standby

Type indicates the source DBMS type and can be one of:

0 LaunchAgent
1 Db2 for i
2 future use
3 Db2 LUW
4 future use
5 SQL Server
6 Oracle
7 Informix
8 MySQL
9 PostgreSQL

 

Remote Servers

For simplicity, the supplied examples assume that you are running the PowerShell cmdlet on the system where Stelo Capture is installed.

To access a remote instance of Stelo Capture, edit the PowerShell script and replace
$baseUrl = 'http://localhost:8080'
with
$baseUrl = 'http://remotesysname:8080'

You can perform operations on remote SQDR servers if the following prerequisites are met:

• You must have network connectivity to the listener port for SQDR Control Center (default 8080) on the remote system.
• If the Windows Firewall is enabled on the remote system, create an inbound rule for the port.
• Similarly, if Stelo Capture is running on Linux and the firewall is enabled, allow access to the defined listening port e.g.
  # firewall-cmd --add-port=8080/tcp --permanent

Using a secure (SSL/TLS) connection:

Specify the baseURL with https and the approriate port e.g.

$baseUrl = 'https://remotesysname:8443'

If SQDR Control Center (jetty) has been configured to use a third party SSL certificate, issued by either an internal or a public certificate authority (CA), as documented in SQDR Plus: Configuring SSL for Jetty (SQDR Control Center) you may need to import the certificate of the issuing Certificate Authority into the appropriate certificate store (e.g. Windows certificate store or OpenSSL certificate store). If you do not configure an SSL certificate, the Control Center uses a self-signed certificate that is automatically generated at service startup; this certificate will change every time the service is started.

You can use the TrustServerCertificate parameter to disable certificate validation.

Example:

Import-Module 'C:\sqdr\Tools\CapturePowerShell\CapturePowerShell.dll'
$baseUrl = 'https://localhost:8443'

Request-Login -BaseUrl $baseUrl -UserId 'SQDR' -Password 'sqdrplus' -TrustServerCertificate 1
Show-Agents -BaseUrl $baseUrl -TrustServerCertificate 1

Troubleshootng

Symptom: Import-Module fails with the following error:

Import-Module : Could not load file or assembly 'file:///C:\sqdr\Tools\CapturePowerShell\CapturePowerShell.dll' or one
of its dependencies. Operation is not supported. (Exception from HRESULT: 0x80131515)
At line:1 char:1
+ Import-Module 'C:\sqdr\Tools\CapturePowerShell\CapturePowerShell.dll'

+ CategoryInfo : NotSpecified: (:) [Import-Module], FileLoadException
+ FullyQualifiedErrorId : System.IO.FileLoadException,Microsoft.PowerShell.Commands.ImportModuleCommand
Solution: This error may result if the files in C:\sqdr\Tools\CapturePowerShell are marked as Blocked. Be sure to unblock the zip file before extracting its contents.

Known Issues and Caveats

Passwords (for the source database, the SQDR Control Center, and the local Db2 LUW staging database) are stored in plain text in JSON and PowerShell files. Use operating system permissions to restrict access to these files.