Installing the Db2 drivers

Before you can use the data connector, you must make the Db2 drivers available to Tonic Structural:

1. From https://www.nuget.org/packages/Net.IBM.Data.Db2, download the correct version of the Net.IBM.Data.Db2 driver for your operating system. The correct version as of August 12, 2024 is 8.0.0.200.

2. Create a directory: <my-directory>

3. From the driver zip file:

   1. Extract lib/net8.0/IBM.Data.Db2.dll into <my-directory>/IBM.Data.Db2.dll.

   2. Extract buildTransitive/clidriver/ into <my-directory>/clidriver/.

4. Mount the created directory into the Tonic Web Server and Tonic Worker. You must map the directory you created to /data/db2 on the containers.

   1. For Docker, you place the driver files on the host machine, then share it to the containers as a volume. You must map the drivers to /data/db2 on the containers.

      To map the drivers, add the following entry into the Docker Compose file:

      Copy

      volumes:
              ...
              - /my-host-path:/data/db2

   2. For Kubernetes, you can use any volume type that is allowed within your environment. It must provide at least ReadOnlyMany access. You must map the drivers to /data/db2 on the containers. In your web server and worker deployment YAML files, the entry should be similar to the following:

      Copy

          volumeMounts:
          - name: <my-volume-name>
            mountPath: /data/db2

Required permissions (source database user)

The source database user must have the following permissions:

• On the database, DBADMAUTH and DATAACCESSAUTH

• If user is not authorized with DBADMAUTH or DATAACCESSAUTH, then Structural at minimum requires:

  • SELECT privilege on all tables

  • SELECT privilege on the following system catalog tables:

    • SYSCAT.TABLES

    • SYSCAT.COLUMNS

    • SYSCAT.KEYCOLUSE

    • SYSCAT.INDEXCOLUSE

    • SYSCAT.INDEXES

    • SYSCAT.TABCONST

    • SYSCAT.TABLESPACES

    • SYSCAT.SCHEMATA

Required permissions (destination database user)

The destination database user must have the following permissions:

• On the database, DBADMAUTH and DATAACCESSAUTH

• If user is not authorized with DBADMAUTH or DATAACCESSAUTH, then Structural at minimum requires:

  • SYSADM or SYSCTRL authority

  • SELECT, INSERT, ALTER, DELETE, INDEX, REFERENCES privilege on all tables

  • SELECT privilege on the following system catalog tables:

    • SYSCAT.TABLES

    • SYSCAT.COLUMNS

    • SYSCAT.KEYCOLUSE

    • SYSCAT.INDEXCOLUSE

    • SYSCAT.INDEXES

    • SYSCAT.TABCONST

    • SYSCAT.TABLESPACES

    • SYSCAT.SCHEMATA

  • EXECUTE privilege on the following routines:

    • SYS_PROC.DB2LK_GENERATE_DDL

    • AUTH_LIST_AUTHORITIES_FOR_AUTHID

Creating the destination databases

For Db2 for LUW, Structural does not create the destination database during data generation.

The destination database with the full schema must already be in place.

When you run the data generation, Structural removes the data from the database and populuates it with the transformed source data.

Tonic Structural supports Db2 for LUW version 11.5.

During workspace creation, under Connection Type, click Db2 for LUW.

Connecting to the source database

In the Source Settings section, you provide the connection information for the source database.

Providing the connection details

To provide the connection details for the source database:

1. In the Server field, provide the server where the database is located.

2. In the Database field, provide the name of the database.

3. In the Port field, provide the port to use to connect to the database.

4. In the Username field, provide the username for the account to use to connect to the database.

5. In the Password field, provide the password for the specified username.

6. To test the connection to the source data, click Test Source Connection.

Ensuring encryption of source database authentication

The Enable SSL/TLS setting indicates whether to encrypt source database authentication.

By default, it is in the on position. We strongly recommend that you do not turn off this setting.

Trusting the server certificate

To indicate that Tonic Structural should trust the server certificate, toggle Trust Server Certificate to the on position.

Connecting through an SSH bastion

For additional security, to connect through an SSH bastion :

1. Toggle Enable SSH Tunnel to the on position.

2. In the SSH Host field, provide the host for the SSH bastion.

3. In the SSH Port field, provide the port for the SSH bastion.

4. In the SSH User field, provide the name of the user to use to connect to the SSH bastion.

5. If you do not use a private key, then in the SSH Passphrase field, provide the passphrase to use for authentication.

6. If you do use a private key, then in the SSH Private Key field, provide the private key. If the private key uses a passphrase, then in the SSH Passphrase field, provide the passphrase for the private key.

Blocking data generation on all schema changes

By default, data generation is not blocked as long as schema changes do not conflict with your workspace configuration.

To block data generation when there are any schema changes, regardless of whether they conflict with your workspace configuration, toggle Block data generation on schema changes to the on position.

Connecting to the destination database

Note that you must create the destination database with the full schema before you run data generation.

Copying the connection and authentication details from the source database

If the destination database is in the same location as the source database, then you can copy the connection and authentication details from the source database.

To copy the connection and authentication details from the source database:

1. Click Copy Settings from Source.

2. In the Password field, provide the password.

3. To test the connection to the destination database, click Test Destination Connection.

Providing the connection details

To provide the connection details for the destination database:

1. In the Server field, provide the server where the database is located.

2. In the Database field, provide the name of the database.

3. In the Port field, provide the port to use to connect to the database.

4. In the Username field, provide the username for the account to use to connect to the database.

5. In the Password field, provide the password for the specified username.

6. To test the connection to the destination database, click Test Destination Connection.

Ensuring encryption of destination database authentication

The Enable SSL/TLS setting indicates whether to encrypt destination database authentication.

By default, the toggle is in the on position. We strongly recommend that you do not turn off this setting.

Trusting the server certificate

To indicate that Structural should trust the server certificate, toggle Trust Server Certificate to the on position.

Connecting through an SSH bastion

For additional security, to connect through an SSH bastion :

1. Toggle Enable SSH Tunnel to the on position.

2. In the SSH Host field, provide the host for the SSH bastion.

3. In the SSH Port field, provide the port for the SSH bastion.

4. In the SSH User field, provide the name of the user to use to connect to the SSH bastion.

5. If you do not use a private key, then in the SSH Passphrase field, provide the passphrase to use for authentication.

6. If you do use a private key, then in the SSH Private Key field, provide the private key. If the private key uses a passphrase, then in the SSH Passphrase field, provide the passphrase for the private key.

IBM Db2 for Linux, Unix, and Windows (Db2 for LUW) is a data management platform from IBM.

Table mode limitations

In Db2 for LUW workspaces, you can only assign the De-Identify or Truncate table modes.

No upsert

Db2 for LUW workspaces do not support upsert.

No output to container artifacts

In Db2 for LUW workspaces, you cannot write the destination data to container artifacts.

No output to an Ephemeral snapshot

In Db2 for LUW workspaces, you cannot write the destination data to an Ephemeral snapshot.