Wiki Admin

How To Create Locations and Channels Using Command Line - DSS 6 | Data Source Solutions Documentation

Export as HTML Export as PDF Export as DOCX
Edit

Documentation: How To Create Locations and Channels Using Command Line - DSS 6 | Data Source Solutions Documentation

How To Create Locations and Channels Using Command Line Interface

Question

How can I create locations and channels using the command line interface (CLI)?

Environment

DSS 6

Answer

The assumption is that DSS is already installed and the configuration of DSS Hub Server has been completed. The assumption is that the DSS Hub Server is configured to run on http://malta:5540.

The following example will create and activate a channel called o2pg, which has Oracle as a source and PostgreSQL as a target location with just two actions called Capture on the Oracle side and Integrate on the PostgreSQL side.

Create Authorization bearer

When running the 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 the same server where dsshubserver is running) or remotely from dsshubserver.

dsslogin -R http://malta:5540 -udssadmin

Create an Oracle location

  1. Create a JSON input file for the Oracle location. The below examples creates a JSON input for an Oracle location called oras, which connects without a DSS Agent to Oracle 18C:

    {
  "changes": [
    {
      "add_loc": {
        "props": {
          "Capture_Method": "DIRECT",
          "Class": "oracle",
          "Database_Password": "mypasswd",
          "Database_User": "hvtpcc",
          "Oracle_Home": "/distr/oracle/1800",
          "Oracle_SID": "DSS1800"
        },
        "loc": "oras"
      }
    }
  ]
}

  2. Create the Oracle location using the following command for your use case:

    If running the command locally on the hub server: dssdefinitionimport -loras hvhub6 orac.json

    If running the command from a remote machine: dssdefinitionimport -loras -R http://malta:5540 hvhub6 orac.json

Create a PostgreSQL location

  1. Create a JSON input file for the PostgreSQL location. The below example creates a JSON input for a PostgreSQL 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"
      }
    }
  ]
}

  2. Create the PostgreSQL location using the following command for your use case:

    If running the command locally on the hub server: dssdefinitionimport -lpgt hvhub6 postgres.json

    If running the command from a remote machine: dssdefinitionimport -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, adding oras and pgt as location group members, and adding Capture and Integrate actions on the location groups source(Capture) and target(Integrate).

  1. Create a JSON input file for the 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"
      }
    },
  ]
}

  2. Create the channel using the following command for your use case:

    If running the command locally on the hub server: dssdefinitionimport hvhub6 o2pg.json

    If running the command from a remote machine: dssdefinitionimport -R http://malta:5540 hvhub6 o2pg.json

Add tables to channel o2pg

The below example adds nine tables from schema hvtpcc. Use the following command for your use case:

If running the command locally on the hub server:
`dssadapt -loras hvhub6 o2pg`

If running the command from a remote machine:
`dssadapt -loras -R http://malta:5540 hvhub6 o2pg`

Create a refresh event

The below example assumes no refresh has ever been done. A refresh event is created that, if this event gets started, will create absent tables. Use the following command for your use case:

If running the command locally on the hub server:
`dssrefresh -J integ -s -r oras -l pgt -cbkr -gb -qrw hvhub6 o2pg`

If running the command from a remote machine:
`dssrefresh -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, the Capture and Integrate jobs will be running.

If running the command locally on the hub server:
`dssactivate -J cap -J refr -p2 hvhub6 o2pg`

If running the command from a remote machine: 
`dssactivate -J cap -J refr -p2 -R http://malta:5540 hvhub6 o2pg`

Additional location examples

SQL Server with dssagent allowing anonymous access

In the location where an dssagent is needed, start dssagent like this:

dssagentlistener -d <port number>,

The first time the agent is started on this port, it will be in setup mode.

In the below example, the port 55302 is used on malta:

dssagentlistener -d 55302

Get Agent_Client_Public_Certificate

dssreposconfig -R http://malta:5540

Copy the content of the Agent_Client_Public_Certificate.

Modify DSS Agent property to allow anonymous authentication

dssagentconfig -h hvhub6 -r malta:55302 -S Anonymous_Access='{"allow":"true"}' Only_From_Client_Public_Certificates='{"http://malta:5540":"<copied value from dssreposconfig>"}' Setup_Mode_Timed_Until=

Add location called sqlserver

  1. Create a JSON file for the 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"
      }
    }
  ]
}

  2. Create the location:

    dssdefinitionimport -lsqlserver hvhub6 sqlserver.json

Snowflake with dssagent using username/password authentication

In the location where an dssagent is needed, start dssagent like this:

dssagentlistener -d <port number>,

The first time the DSS Agent is started on this port, it will be in setup mode.

In the below example, the port 6343 is used on AWS EC2 instance ec2-34-243-68-170.eu-west-1.compute.amazonaws.com:

dssagentlistener -d 6343

Create agent admin user (optional)

This step is optional, but if you have at least one agent admin user, it gives the possibility to re-configure the agent when not in setup mode afterwards:

dssagentuserconfig -h hvhub6 -r ec2-34-243-68-170.eu-west-1.compute.amazonaws.com:6343 -S -c dssadmin

It will ask you to type a password for the user.

Create agent user for Snowflake location

The authentication to the dssagent will be done by using a username and password, and network traffic will be encrypted using the Agent_client_public_certificate from the hub.

dssagentuserconfig -h hvhub6 -r ec2-34-243-68-170.eu-west-1.compute.amazonaws.com:6343 -S -c dss

It will ask you to type a password for the user.

Modify agent property to allow username/password authentication

dssagentconfig -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

  1. 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": "dss",
          "Class": "snowflake",
          "Database_Host": "myorganization.myaccount.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"
      }
    }
  ]
}

The Database_Host property supports the following formats: - <em>orgname</em>-<em>account_name</em>.snowflakecomputing.com - <em>orgname</em>-<em>connectionname</em>.snowflakecomputing.com - <em>accountlocator</em>.<em>region</em>.<em>cloud</em>.snowflakecomputing.com

For more information, refer to the [Snowflake documentation](https://docs.snowflake.com/en/user-guide/organizations-connect#standard-account-urls).

  1. Run the following command:

    dssdefinitionimport -lsnow hvhub6 snowflake.json