Introduction
The assumption is that HVR is already installed and the configuration of Local Processing Hub Server has been completed. The assumption is that the Local Processing Hub Server is configured to run on http://malta:5540
Below example will create and activate a channel called o2pg, which has an oracle as source and postgres as target location with just 2 actions called Capture on the Oracle side, Integrate on the postgres side.
Create Authorization bearer
When running below commands remotely from the hubserver, authorization is required otherwise when running below commands you can get error F_JW0555: Refresh token invalid or expired
all commands can be executed locally (so on same server where hvrhubserver is running) or remotely from hvrhubserver.
hvrlogin -R http://malta:5540 -uhvradmin
Create an oracle location
Create json input file for the oracle location
The below example creates a location called oras, which connects without a hvr agent to oracle 18C
json input example for an oracle locaton called oras
{
"changes": [
{
"add_loc": {
"props": {
"Capture_Method": "DIRECT",
"Class": "oracle",
"Database_Password": "mypasswd",
"Database_User": "hvtpcc",
"Oracle_Home": "/distr/oracle/1800",
"Oracle_SID": "HVR1800"
},
"loc": "oras"
}
}
]
}
Create the oracle location
local to hvrhubserver hvrdefinitionimport -loras hvhub6 orac.json
remote from hvrhubserver hvrdefinitionimport -loras -R http://malta:5540 hvhub6 orac.json
Create a postgres location
Create json input file for the postgres location
json input example for an postgres location called pgt
{
"changes": [
{
"add_loc": {
"props": {
"Class": "postgresql",
"Database_Host": "localhost",
"Database_Name": "hvcen",
"Database_Password": "mypasswd",
"Database_Port": 5463,
"Database_User": "jigsaw"
},
"loc": "pgt"
}
}
]
}
Create the postgres location
local to hvrhubserver hvrdefinitionimport -lpgt hvhub6 postgres.json
remote from hvrhubserver hvrdefinitionimport -lpgt -R http://malta:5540 hvhub6 postgres.json
Create a channel called o2pg
The below example creates a channel called o2pg , adding location groups source and target , add ing oras and pgt as location group members, as well as adding Capture and Integrate actions on the location groups source(Capture), target(Integrate)
Create json input file for channel o2pg
{
"changes": [
{
"add_channel": {
"description": "oracle 2 pg channel",
"loc_groups": {
"SRC": {
"members": [
"oras"
]
},
"TGT": {
"members": [
"pgt"
]
}
},
"actions": [
{
"loc_scope": "SRC",
"table_scope": "*",
"type": "Capture",
"params": {}
},
{
"loc_scope": "TGT",
"table_scope": "*",
"type": "Integrate",
"params": {}
}
],
"channel": "o2pg"
}
},
]
}
Create the channel
local to hvrhubserver hvrdefinitionimport hvhub6 o2pg.json
remote from hvrhubserver hvrdefinitionimport -R http://malta:5540 hvhub6 o2pg.json
Add tables to channel o2pg
Below
example adds 9 tables from schema hvtpcc
local to hvrhubserver hvradapt -loras hvhub6 o2pg
remote from hvrhubserver hvradapt -loras -R http://malta:5540 hvhub6 o2pg
Create refresh event
Below example assumes no refresh has ever been done. A refresh event is created which , if this events gets started , will create absent tables.
local to hvrhubserver hvrrefresh -J integ -s -r oras -l pgt -cbkr -gb -qrw hvhub6 o2pg
remote from hvrhubserver hvrrefresh -J integ -s -r oras -l pgt -cbkr -gb -qrw -R http://malta:5540 hvhub6 o2pg
Activate the channel
Below example will kick off the Refresh event from previous step and activate the channel. Once this is finished, Capture and Integrate jobs will be running.
local to hvrhubserver hvractivate -J cap -J refr -p2 hvhub6 o2pg
remote from hvhubserver hvractivate -J cap -J refr -p2 -R http://malta:5540 hvhub6 o2pg
Additional location examples
sqlserver with hvragent allowing anonymous access
On the location where an hvragent is needed, start hvragent like this
hvragentlistener -d <port number>, the first time the agent is started on this port, it will be in setup mode
in example port 55302 is used on malta
hvragentlistener -d 55302
Get Agent_Client_Public_Certificate
hvrreposconfig -R http://malta:5540
copy the content of Agent_Client_Public_Certificate
Modify agent property to allow anonymous authentication
hvragentconfig -h hvhub6 -r malta:55302 -S Anonymous_Access='{"allow":"true"}' Only_From_Client_Public_Certificates='{"http://malta:5540":"<copied value from hvrreposconfig>"}' Setup_Mode_Timed_Until=
Add location called sqlserver
Create json file for location e.g sqlserver.json
{
"changes": [
{
"add_loc": {
"props": {
"Agent_Host": "malta",
"Agent_Port": 55302,
"Capture_Method": "DIRECT",
"Class": "sqlserver",
"Database_Name": "tpcc",
"Database_Password": "mypasswd",
"Database_User": "sa",
"Log_Truncater": "CAP_JOB",
"SqlServer_Server": "LT-HERMAN",
"Supplemental_Logging": "CDCTAB_ARTICLE"
},
"loc": "sqlserver"
}
}
]
}
Create the location;
hvrdefinitionimport -lsqlserver hvhub6 sqlserver.json
Snowflake with hvragent using username/password authentication
On the location where an hvragent is needed, start hvragent like this
hvragentlistener -d <port number>, the first time the agent is started on this port, it will be in setup mode
in example port 6343 is used on aws ec2 instance ec2-34-243-68-170.eu-west-1.compute.amazonaws.com
hvragentlistener -d 6343
Create agent admin user
This step is optional, but having at least 1 agent admin user, gives the possibility to re-configure the agent when not in setup mode afterwards.
hvragentuserconfig -h hvhub6 -r ec2-34-243-68-170.eu-west-1.compute.amazonaws.com:6343 -S -c hvradmin
It will ask to type a password for the user
Create agent user for snowflake location
The authentication to the hvragent will be done by using a user name and password, network traffic will be encrypted using Agent_client_public_certificate from hub.
hvragentuserconfig -h hvhub6 -r ec2-34-243-68-170.eu-west-1.compute.amazonaws.com:6343 -S -c hvr
It will ask to type a password for the user
Modify agent property to allow username/password Authentication
hvragentconfig -h hvhub6 -r ec2-34-243-68-170.eu-west-1.compute.amazonaws.com:6343 -S User_Access='{"herman":{"level":"AgentAdmin"}}' Setup_Mode_Timed_Until=
Create snowflake location
Create a json file with following content
{
"changes": [
{
"add_loc": {
"props": {
"Agent_Host": "ec2-34-243-68-170.eu-west-1.compute.amazonaws.com",
"Agent_Password": "mypasswd",
"Agent_Port": 6343,
"Agent_User": "hvr",
"Class": "snowflake",
"Database_Host": "hvr_partner.west-europe.azure.snowflakecomputing.com",
"Database_Name": "hvdb",
"Database_Password": "mypasswd",
"Database_Port": 443,
"Database_Schema": "hvschema",
"Database_User": "herman",
"ODBC_Driver": "SnowflakeDSIIDriver",
"Snowflake_Role": "public",
"Snowflake_Warehouse": "hvware"
},
"loc": "snow"
}
}
]
}
then run
hvrdefinitionimport -lsnow hvhub6 snowflake.json